In my previous post I discussed using the SCOPE statement in a cube. I use it quite extensively in my designs and last week I came accross an unexpected issue with it.
I have a cube containing many measures for which I require different functionality under certain conditions. Those conditions can actually be described in subcubes, and therefore the SCOPE statement addresses this requirement.
The functionality I want is the same for several measures and therefore I put them together in the same SCOPE block:
SCOPE([Measures].[Area1],[Measures].[Area2]); THIS=(0); END SCOPE;
Where Area1 and Area2 are both measures defined in the cube. The above scope would set them both to zero, but this is just for illustration.
The issue arises if the 2 measures above, Area1 and Area2, are actually calculated measures. If that is the case then when we deploy this MDX we get the following error:
"A set has been encountered that cannot contain calculated members."
This can be confusing as the error message does not actually identify the SCOPE as the source of the issue. But you can be fairly sure that that is where the problem is.
For some reason using more than one calculated measure in a single scope statment is a problem for SSAS. If you have very few relevant measures then you could split them into individual scope statments, but this would be laborious if you have several. The solution is to use an assignment script instead. This is as simple as:
({[Measures].[Area1],[Measures].[Area2]}) = 0;
Type this in the calculation script and, hey presto, the error disappears and the MDX works as expected.
Chris Webb also covers this here.
Thursday 29 August 2013
Wednesday 28 August 2013
Analysis Services MDX - SCOPE Statement
A question was put to me recently on how to setup a measure that performs differently based on different member selections in the cube.
One option is to use lots of nested IF statments. This may well work, but is quite untidy and is known to be a little slow.
Wherever you are considering using IF type logic in your calculation script, it would be a very good idea to consider using the much faster SCOPE statement instead. The SCOPE statment is clearer and significantly more performant.
As an example of how to use the scope statment i'll use a geometry based illustration: My cube contains a dimension called Shape that contains 2 members: Square and Circle. I then have a number of measures: Length and Radius. I create a calculated measure called Area. The calculation for area depends on what shape i have selected. If i have selected square then Area = (Length)^2, while for Circle it would be Area=Pi*(Radius)^2.
Circle and Square can be thought of as subcubes. I can therefore define the scope statement as follows:
SCOPE([Measures].[Area],[Shapes].[Circle]); THIS=(Pi*[Measures].[radius]*[Measures].[radius]);
END SCOPE;
SCOPE([Measures].[Area],[Shapes].[Square]); THIS=([Measures].[Length]*[Measures].[Length]);
END SCOPE;
Note - I would still need to have defined the Area measure in some manner elsewhere in my script. The above statments only refer to the measure, they dont actually create it.
That is essentially an introduction to how to use the SCOPE statment. Next post will be on issues to watch out for when using it!
One option is to use lots of nested IF statments. This may well work, but is quite untidy and is known to be a little slow.
Wherever you are considering using IF type logic in your calculation script, it would be a very good idea to consider using the much faster SCOPE statement instead. The SCOPE statment is clearer and significantly more performant.
As an example of how to use the scope statment i'll use a geometry based illustration: My cube contains a dimension called Shape that contains 2 members: Square and Circle. I then have a number of measures: Length and Radius. I create a calculated measure called Area. The calculation for area depends on what shape i have selected. If i have selected square then Area = (Length)^2, while for Circle it would be Area=Pi*(Radius)^2.
Circle and Square can be thought of as subcubes. I can therefore define the scope statement as follows:
SCOPE([Measures].[Area],[Shapes].[Circle]); THIS=(Pi*[Measures].[radius]*[Measures].[radius]);
END SCOPE;
SCOPE([Measures].[Area],[Shapes].[Square]); THIS=([Measures].[Length]*[Measures].[Length]);
END SCOPE;
Note - I would still need to have defined the Area measure in some manner elsewhere in my script. The above statments only refer to the measure, they dont actually create it.
That is essentially an introduction to how to use the SCOPE statment. Next post will be on issues to watch out for when using it!
Monday 19 August 2013
SSAS: Setting the Default Measure Using MDX
I recently used a nifty bit of code to set the default measure for a cube using MDX.
The most obvious way of setting the default measure for a cube using is relatively easy. In the cube editor, under the Cube Structure tab, highlight your cube (at the top of the measure group tree). In the properties on the right of the screen (shortcut F4) there is a property called DefaultMeasure. Click the dropdown and select a measure from the list.
On closer examination, however, none of the measures in the drop down are calculated measures. So what if you want the default measure to be just that? The answer is that the default measure can be set using MDX in the Calculations tab of the cube editor. The code do it is as follows:
ALTER CUBE CURRENTCUBE UPDATE DIMENSION Measures, DEFAULT_MEMBER = [Measures].MyDefaultMeasure;
That's all there is to it. Put whatever measure you like here, calculated or not.
Additionally, if you now want to change the default measure to be a different one, that would previously have required changing the DefaultMember property mentioned earlier and then redeploying the entire cube (using the Deployment Wizard, direct from BIDS etc), which can have other consequences. However, by assigning the default measure in the calculations tab it has the added benefit that making a change only requires deployment of the MDX script and not the rest of the database. Using a tool like BIDS Helper (from codeplex) means this can be done in a single click from the Calculations tab with no impact to the rest of the cube design.
The most obvious way of setting the default measure for a cube using is relatively easy. In the cube editor, under the Cube Structure tab, highlight your cube (at the top of the measure group tree). In the properties on the right of the screen (shortcut F4) there is a property called DefaultMeasure. Click the dropdown and select a measure from the list.
On closer examination, however, none of the measures in the drop down are calculated measures. So what if you want the default measure to be just that? The answer is that the default measure can be set using MDX in the Calculations tab of the cube editor. The code do it is as follows:
ALTER CUBE CURRENTCUBE UPDATE DIMENSION Measures, DEFAULT_MEMBER = [Measures].MyDefaultMeasure;
That's all there is to it. Put whatever measure you like here, calculated or not.
Additionally, if you now want to change the default measure to be a different one, that would previously have required changing the DefaultMember property mentioned earlier and then redeploying the entire cube (using the Deployment Wizard, direct from BIDS etc), which can have other consequences. However, by assigning the default measure in the calculations tab it has the added benefit that making a change only requires deployment of the MDX script and not the rest of the database. Using a tool like BIDS Helper (from codeplex) means this can be done in a single click from the Calculations tab with no impact to the rest of the cube design.
Thursday 8 August 2013
SSAS - ProcessAdd explained
Further to my previous post on creating multiple partitions and multiple processing commands in a single xmla script (here and here) I thought I share one more idea around SSAS Processing tasks.
When looking the different types of processing options available in SSAS you tend to get the same list over and over:
ProcessFull,
ProcessDefault,
ProcessData,
ProcessIndexes,
ProcessUpdate,
ProcessIncremental,
ProcessClear,
ProcessAdd
I will not explain here the differences between each of these types as this info is readily available on plenty of other sites (such as http://technet.microsoft.com/en-us/library/ms174774(v=sql.105).aspx).
However if you try to use each of these processing types from management studio all will go well until you try and process a dimension using ProcessAdd. It may sound like a useful option (faster than ProcessUpdate, less impact than ProcessFull etc) but when you try and use it you will find it is not as straightforward as the others. If you right-click the dimension in SSMS and select Process you might think to just select it in the drop down. But take a close look at that drop down - ProcessAdd is not there!
But the documentation says it is definately an option. Script a different processing option to XMLA and change the process type to Add and it may work, but if you havent specified which records to add the results could be misleading. Confusing, right?
In order to use this processing option you have to actually stipulate quite a lot of details. This can be done with an XMLA script, but the process of building this script is quite tricky. Therefore i shall do my best to explain how to do it here :) (Greg Galloway gives a good overview of this here
http://www.artisconsulting.com/blogs/greggalloway/Lists/Posts/Post.aspx?ID=4, and the steps below were based on following his method)
1 - Use Management Studio (SSMS) to script out to XMLA a ProcessUpdate command. (I recommend editting the error settings to ignore/report duplicates before scripting)
2 - In BIDS in the Solution Explorer right click on the dsv (the dsv file that the relevant dimension is build from, if you have more than one) and select View Code. Copy the entire code and paste it into a new window in SSMS to allow us to edit it.
3 - We should now reduce this dsv copy down to only the relevant part. We only actually need the
<
xs:element name for the particular query that is relevant to our dimension. Tip - quick way to find this element would be to search for a unique string within the query that might single it out from the rest! Keep all data and tags within this particular tag and delete everything outside this (tags at the same level), but keep all the parent tags. SSMS should make it easy to expand and collapse the tags in order to determine was tags are above or below, and what ones are at the same level.
4 - Copy what remains and paste it into your original process script from step 1. The code should be copied immediately before the closing Process tag.
5 - Now modify the actual query to limit it to only new data. For example add a where clause that limits the results to be after a certain date.
6 - Voila - you now have your ProcessAdd script. It should look something like this the code below and ready to execute.
<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"><!--
Error Cofig--><
ErrorConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200"><
KeyErrorLimit>-1</KeyErrorLimit></
ErrorConfiguration>
<!--
Processing details--><
Parallel><
Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200"><
Object><
DatabaseID>MyDB</DatabaseID><
DimensionID>MyDim</DimensionID></
Object><
Type>ProcessAdd</Type><
WriteBackTableCreation>UseExisting</WriteBackTableCreation>
<!--
Copy from dsv goes here--><
DataSourceView xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0" dwd:design-time-name="4be9af80-0ae0-4f8a-936d-2103b944155f" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"><
ID>MyDSV</ID><
Name>MyDSV</Name><
DataSourceID>MyDS</DataSourceID><
Schema><
xs:schema id="MyDS" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:msprop="urn:schemas-microsoft-com:xml-msprop"><
xs:element name="MyDS" msdata:IsDataSet="true" msdata:UseCurrentLocale="true" msprop:design-time-name="2557b54c-2a08-4e49-ab45-fdd94dfb6dee"><
xs:complexType><
xs:choice minOccurs="0" maxOccurs="unbounded"><!--
Query to be modified is here--><
xs:element name="MY_DIM" msprop:QueryDefinition="SELECT 'HELLO' AS PHRASE FROM MY_TABLE
WHERE DATE_KEY > 20130801"
msprop:DbTableName="MY_DIM" msprop:QueryBuilder="GenericQueryBuilder" msprop:IsLogical="True" msprop:FriendlyName="MY_DIM" msprop:design-time-name="c4b7ad24-101e-4d41-bc04-7ac2b605cd7b" msprop:TableType="View"><
xs:complexType><
xs:sequence><
xs:element name="MY_ATTRIBUTE" msprop:design-time-name="1e60f905-098a-45f2-b5be-c34364bdf328" msprop:DbColumnName="PHRASE" minOccurs="0"><
xs:simpleType><
xs:restriction base="xs:string"><
xs:maxLength value="100" /></
xs:restriction></
xs:simpleType></
xs:element></
xs:sequence></
xs:complexType></
xs:element></
xs:choice></
xs:complexType></
xs:element></
xs:schema><
diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1" /></
Schema></
DataSourceView><!--
End of copy from dsv--></
Process></
Parallel>
</
Batch>
When looking the different types of processing options available in SSAS you tend to get the same list over and over:
ProcessFull,
ProcessDefault,
ProcessData,
ProcessIndexes,
ProcessUpdate,
ProcessIncremental,
ProcessClear,
ProcessAdd
I will not explain here the differences between each of these types as this info is readily available on plenty of other sites (such as http://technet.microsoft.com/en-us/library/ms174774(v=sql.105).aspx).
However if you try to use each of these processing types from management studio all will go well until you try and process a dimension using ProcessAdd. It may sound like a useful option (faster than ProcessUpdate, less impact than ProcessFull etc) but when you try and use it you will find it is not as straightforward as the others. If you right-click the dimension in SSMS and select Process you might think to just select it in the drop down. But take a close look at that drop down - ProcessAdd is not there!
But the documentation says it is definately an option. Script a different processing option to XMLA and change the process type to Add and it may work, but if you havent specified which records to add the results could be misleading. Confusing, right?
In order to use this processing option you have to actually stipulate quite a lot of details. This can be done with an XMLA script, but the process of building this script is quite tricky. Therefore i shall do my best to explain how to do it here :) (Greg Galloway gives a good overview of this here
http://www.artisconsulting.com/blogs/greggalloway/Lists/Posts/Post.aspx?ID=4, and the steps below were based on following his method)
1 - Use Management Studio (SSMS) to script out to XMLA a ProcessUpdate command. (I recommend editting the error settings to ignore/report duplicates before scripting)
2 - In BIDS in the Solution Explorer right click on the dsv (the dsv file that the relevant dimension is build from, if you have more than one) and select View Code. Copy the entire code and paste it into a new window in SSMS to allow us to edit it.
3 - We should now reduce this dsv copy down to only the relevant part. We only actually need the
<
xs:element name for the particular query that is relevant to our dimension. Tip - quick way to find this element would be to search for a unique string within the query that might single it out from the rest! Keep all data and tags within this particular tag and delete everything outside this (tags at the same level), but keep all the parent tags. SSMS should make it easy to expand and collapse the tags in order to determine was tags are above or below, and what ones are at the same level.
4 - Copy what remains and paste it into your original process script from step 1. The code should be copied immediately before the closing Process tag.
5 - Now modify the actual query to limit it to only new data. For example add a where clause that limits the results to be after a certain date.
6 - Voila - you now have your ProcessAdd script. It should look something like this the code below and ready to execute.
<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"><!--
Error Cofig--><
ErrorConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200"><
KeyErrorLimit>-1</KeyErrorLimit></
ErrorConfiguration>
<!--
Processing details--><
Parallel><
Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200"><
Object><
DatabaseID>MyDB</DatabaseID><
DimensionID>MyDim</DimensionID></
Object><
Type>ProcessAdd</Type><
WriteBackTableCreation>UseExisting</WriteBackTableCreation>
<!--
Copy from dsv goes here--><
DataSourceView xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0" dwd:design-time-name="4be9af80-0ae0-4f8a-936d-2103b944155f" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"><
ID>MyDSV</ID><
Name>MyDSV</Name><
DataSourceID>MyDS</DataSourceID><
Schema><
xs:schema id="MyDS" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:msprop="urn:schemas-microsoft-com:xml-msprop"><
xs:element name="MyDS" msdata:IsDataSet="true" msdata:UseCurrentLocale="true" msprop:design-time-name="2557b54c-2a08-4e49-ab45-fdd94dfb6dee"><
xs:complexType><
xs:choice minOccurs="0" maxOccurs="unbounded"><!--
Query to be modified is here--><
xs:element name="MY_DIM" msprop:QueryDefinition="SELECT 'HELLO' AS PHRASE FROM MY_TABLE
WHERE DATE_KEY > 20130801"
msprop:DbTableName="MY_DIM" msprop:QueryBuilder="GenericQueryBuilder" msprop:IsLogical="True" msprop:FriendlyName="MY_DIM" msprop:design-time-name="c4b7ad24-101e-4d41-bc04-7ac2b605cd7b" msprop:TableType="View"><
xs:complexType><
xs:sequence><
xs:element name="MY_ATTRIBUTE" msprop:design-time-name="1e60f905-098a-45f2-b5be-c34364bdf328" msprop:DbColumnName="PHRASE" minOccurs="0"><
xs:simpleType><
xs:restriction base="xs:string"><
xs:maxLength value="100" /></
xs:restriction></
xs:simpleType></
xs:element></
xs:sequence></
xs:complexType></
xs:element></
xs:choice></
xs:complexType></
xs:element></
xs:schema><
diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1" /></
Schema></
DataSourceView><!--
End of copy from dsv--></
Process></
Parallel>
</
Batch>
Friday 2 August 2013
SSAS: How to Create multiple partitions in a single XMLA script
This piece was originally posted last week, 25th June, prior to the post on multiple processing scripts in an xmla command: http://sqlbanana.blogspot.co.uk/2013/07/ssas-how-to-run-multiple-xmla-scripts_6877.html
For some reason the post was not published and lost, so i am reposting it here.
------------------
I was recently hit with a fairly simple problem, but it took me a while to figure out how to do it, so I thought I'd share it to save others some time.
I wanted create a series of partitions on the measure groups of my SSAS 2008R2 database. I needed a partition for each month, for 5 years. For each measure group this corresponds to 60 partitions, and I did not want to have to run them one at a time or in multiple windows.
The solution is to create a single xmla command, that will create all the required partitions in a single batch. Here's how...
First create your Batch tags. We want all our partitions to be created in a single batch. Having multiple batch tags in a single session will throw an error, but multiple Create scripts inside a single batch is perfectly fine.
Batch tags:
<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" Transaction="true">
.
.
.
</Batch>
Then put all your create scripts for each partition inside these start and end tags.
<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
.
.
</Create>
Thus the entire script to create the partitions all in a single command will look something like this:
<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" Transaction="true">
<
Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"><
ParentObject><
DatabaseID>MyDB</DatabaseID><
CubeID>MyCube</CubeID><
MeasureGroupID>MyFACT</MeasureGroupID></
ParentObject><
ObjectDefinition><
Partition xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200"><
ID>FACT_20130301</ID><
Name>FACT_20130301</Name><
Source xsi:type="QueryBinding"><
DataSourceID>MyDS</DataSourceID><
QueryDefinition>SELECT COL1 FROM FACT
WHERE (DATE_KEY = 20130301)
</QueryDefinition></
Source><
StorageMode>Molap</StorageMode><
ProcessingMode>Regular</ProcessingMode><
Slice>[Dates].[Date].&[20130301]</Slice><
ProactiveCaching><
SilenceInterval>-PT1S</SilenceInterval><
Latency>-PT1S</Latency><
SilenceOverrideInterval>-PT1S</SilenceOverrideInterval><
ForceRebuildInterval>-PT1S</ForceRebuildInterval><
Source xsi:type="ProactiveCachingInheritedBinding" /></
ProactiveCaching></
Partition></
ObjectDefinition></
Create>
<
Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"><
ParentObject><
DatabaseID>MyDB</DatabaseID><
CubeID>MyCube</CubeID><
MeasureGroupID>MyFACT</MeasureGroupID></
ParentObject><
ObjectDefinition><
Partition xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200"><
ID>FACT_20130302</ID><
Name>FACT_20130302</Name><
Source xsi:type="QueryBinding"><
DataSourceID>MyDS</DataSourceID><
QueryDefinition>SELECT COL1 FROM FACT
WHERE (DATE_KEY = 20130302)
</QueryDefinition></
Source><
StorageMode>Molap</StorageMode><
ProcessingMode>Regular</ProcessingMode><
Slice>[Dates].[Date].&[20130302]</Slice><
ProactiveCaching><
SilenceInterval>-PT1S</SilenceInterval><
Latency>-PT1S</Latency><
SilenceOverrideInterval>-PT1S</SilenceOverrideInterval><
ForceRebuildInterval>-PT1S</ForceRebuildInterval><
Source xsi:type="ProactiveCachingInheritedBinding" /></
ProactiveCaching></
Partition></
ObjectDefinition></
Create>
</
Batch>
We can extend this now to include all 60 partitions in a single file/Batch. If you ever need to rerun, or modify the scripts, you now have them all in a single place, instead of 60 different places.
Hope this helps :)
For some reason the post was not published and lost, so i am reposting it here.
------------------
I was recently hit with a fairly simple problem, but it took me a while to figure out how to do it, so I thought I'd share it to save others some time.
I wanted create a series of partitions on the measure groups of my SSAS 2008R2 database. I needed a partition for each month, for 5 years. For each measure group this corresponds to 60 partitions, and I did not want to have to run them one at a time or in multiple windows.
The solution is to create a single xmla command, that will create all the required partitions in a single batch. Here's how...
First create your Batch tags. We want all our partitions to be created in a single batch. Having multiple batch tags in a single session will throw an error, but multiple Create scripts inside a single batch is perfectly fine.
Batch tags:
<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" Transaction="true">
.
.
.
</Batch>
Then put all your create scripts for each partition inside these start and end tags.
<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
.
.
</Create>
Thus the entire script to create the partitions all in a single command will look something like this:
<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" Transaction="true">
<
Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"><
ParentObject><
DatabaseID>MyDB</DatabaseID><
CubeID>MyCube</CubeID><
MeasureGroupID>MyFACT</MeasureGroupID></
ParentObject><
ObjectDefinition><
Partition xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200"><
ID>FACT_20130301</ID><
Name>FACT_20130301</Name><
Source xsi:type="QueryBinding"><
DataSourceID>MyDS</DataSourceID><
QueryDefinition>SELECT COL1 FROM FACT
WHERE (DATE_KEY = 20130301)
</QueryDefinition></
Source><
StorageMode>Molap</StorageMode><
ProcessingMode>Regular</ProcessingMode><
Slice>[Dates].[Date].&[20130301]</Slice><
ProactiveCaching><
SilenceInterval>-PT1S</SilenceInterval><
Latency>-PT1S</Latency><
SilenceOverrideInterval>-PT1S</SilenceOverrideInterval><
ForceRebuildInterval>-PT1S</ForceRebuildInterval><
Source xsi:type="ProactiveCachingInheritedBinding" /></
ProactiveCaching></
Partition></
ObjectDefinition></
Create>
<
Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"><
ParentObject><
DatabaseID>MyDB</DatabaseID><
CubeID>MyCube</CubeID><
MeasureGroupID>MyFACT</MeasureGroupID></
ParentObject><
ObjectDefinition><
Partition xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200"><
ID>FACT_20130302</ID><
Name>FACT_20130302</Name><
Source xsi:type="QueryBinding"><
DataSourceID>MyDS</DataSourceID><
QueryDefinition>SELECT COL1 FROM FACT
WHERE (DATE_KEY = 20130302)
</QueryDefinition></
Source><
StorageMode>Molap</StorageMode><
ProcessingMode>Regular</ProcessingMode><
Slice>[Dates].[Date].&[20130302]</Slice><
ProactiveCaching><
SilenceInterval>-PT1S</SilenceInterval><
Latency>-PT1S</Latency><
SilenceOverrideInterval>-PT1S</SilenceOverrideInterval><
ForceRebuildInterval>-PT1S</ForceRebuildInterval><
Source xsi:type="ProactiveCachingInheritedBinding" /></
ProactiveCaching></
Partition></
ObjectDefinition></
Create>
</
Batch>
We can extend this now to include all 60 partitions in a single file/Batch. If you ever need to rerun, or modify the scripts, you now have them all in a single place, instead of 60 different places.
Hope this helps :)
Thursday 1 August 2013
SSAS - DATAMEMBER: Parent Member should not be the sum of it's children, but has it's own value
A common problem that I've had to deal with is how to handle the sales of a parent member in a hierarchy. Under normal SSAS conditions the parent will be the sum of the children. However this is often not the case. A manager of a team may make sales in his own right and we may want to see his own sales, and not the sum of his team members' sales.
In SSAS every non-leaf member on a parent/child hierarchy has an extra system-generated child called a datamember. Assuming the fact table contains values for a non-leaf member on a parent/child hierarchy, then these values will in fact be assigned to the member's datamember.
We can then use the MDX DATAMEMBER function. This function causes the relevant measure to display the measure value associated datamember, and not aggregate the values of the children.
Using this within a SCOPE statment in the cube calculation script will define this functionality for the specified measure:
SCOPE([Measures].[Sales]);
THIS=[Employee].[Employees].CURRENTMEMBER.DATAMEMBER;
END SCOPE;
Now the Sales measure will always show the value assigned to the datamember, instead the aggregated value.
More detail, and a good example of this can be found here: http://www.packtpub.com/article/measures-and-measure-groups-microsoft-analysis-services-part1
In SSAS every non-leaf member on a parent/child hierarchy has an extra system-generated child called a datamember. Assuming the fact table contains values for a non-leaf member on a parent/child hierarchy, then these values will in fact be assigned to the member's datamember.
We can then use the MDX DATAMEMBER function. This function causes the relevant measure to display the measure value associated datamember, and not aggregate the values of the children.
Using this within a SCOPE statment in the cube calculation script will define this functionality for the specified measure:
SCOPE([Measures].[Sales]);
THIS=[Employee].[Employees].CURRENTMEMBER.DATAMEMBER;
END SCOPE;
Now the Sales measure will always show the value assigned to the datamember, instead the aggregated value.
More detail, and a good example of this can be found here: http://www.packtpub.com/article/measures-and-measure-groups-microsoft-analysis-services-part1
Subscribe to:
Posts (Atom)