Continue from my previous blog, MDX #11 – How to get number of cars each customer owned using Properties() function?.
Without the TYPED flag, Employee ID is no longer a number, but a text field in Excel
A couple of months ago, I completed a small project to create a calculated measure from the Employee dimension member property Employee ID. A couple of weeks later, a user reported that the Excel PivotTable he has been using to access the cube stopped working because the Employee ID is no longer a number, but a text field now.
Without the TYPED flag, the return value is always a string
In the MSDN MDX reference library, Properties (MDX), in the Remarks section, it has clearly stated:
If the property type is intrinsic, the function returns the original type of the member.
If the property type is user defined, the type of the return value is the same as the type of the return value of the MemberValue function.
By default, the value is coerced to be a string. If TYPED is specified, the return value is strongly typed.
Although the TYPED flag is optional, in my case, I should have used it.
Here is the syntax:
To return the Number of Cars Owned as a number, I would add the TYPED flag to the properties function.
The original data type of Number of Cars Owned is number
Looking at the Number of Cars Owned attribute, the original data type is UnsignedTinyInt.
In the Adventure Works cube, this is a simple query to get just 10 customers and their Internet Sales Amount.
Dimensions can have many attributes (or attribute hierarchies) and user hierarchies
The Customer dimension has many attributes (or attribute hierarchies) and one user hierarchy, the Customer Geography. This is what you can see in the dimension editor.
One of the attribute is Number of Cars Owned. Can we add the Number of Cars Owned to the above query so that for each customer we can see how many cars they owned?
Properties() function can turn dimension attribute into measures
If you haven’t written enough MDX queries yet, you might be tempted with many possibilities, until you come across the properties() function.
Here is the query that will work.
It did work, but there are a couple of unanswered questions.
First, what are “properties”?
In the BIDS, under the Attribute Relationships, we can see two types of relationships.
- attribute –> properties
- user-defined hierarchies
- This screenshot shows that the Customer attribute has many properties, and one of them is Number of Cars Owned.
In the WITH statement, did we just create a calculated measure using a dimension property?
Yes. Calculated members do not need to be always from measures.
I worked on a SSIS package a few months back to retrieve data from a SSAS cube with MDX queries. A co-worker recently is developing a SSIS package to perform a similar task, but couldn’t get the SSIS package to work. The data flow was lost somewhere and the package could never finish the data loading.
This is what it will look like in the Data Flow task. The OLE DB Source will stay yellow forever.
I couldn’t remember right away how my SSIS package was configured to make the data flow work. I had to find my old SSIS package and open it to refresh my memory.
Need to set Format=Tabular in the Data Link Extended Properties
After choosing the OLE DB Provider for Analysis Services 10.0 as the provider for the connection manager, we need to go to the All tab to set Format=Tabular in the Data Link Extended Properties.
There are many blogs on this little “secret” developers discovered. Some claimed that this is a fix for a bug in SSIS 2005 SP1. I had to do this in both SSIS 2005 and 2008 to make it work. So I am not sure if it is caused by a bug or a designed “feature” in SSIS.
Neither can I find more information about what this property value means. You are welcome to share if you have more insight on this.
The happy green color is what you will see once you set Format=Tabular in the Data Link Extended Properties.
A couple of notes:
1. About the data type. Whenever you try to access the column information, you will get a “nice” warning from SSIS that data type DT_WSTR (nvarchar) will be used instead. Again if you have more insight on this, you are welcome to share.
2. I used a Data Conversion transformation to convert the DT_WSTR to DT_STR or DT_NUMERIC accordingly.
I had a post quite a while ago on how to use FOR XML clause with PATH mode, which is available since SQL 2005, to concatenate row values into columns.
We can achieve the same in SSRS with a wonderful aggregate function, RunningValue() function.
But to make it work, it can take more than a few tries.
In this example, I am going to use the MDX query from my post MDX #10 – Top 5 Resellers in Each Country.
Once I have the MDX query ready, I want to concatenate the top 5 resellers into a column, and the result will look like this:
A great post on beyondrelational.com has inspired this post. I forgot the link, but still want to attribute to the original blogger.
First add some custom code to the report
The first we need to do is to add some custom code to the report by going to the Report Properties.
In the Code section, create a custom function ConcatenateString() to concatenate resellers for each country. It will take Country and Reseller as 2 parameters.
Next create a row group
Next we need to create a row group, named “Group_Country”. In the group property, make sure that the group by column is [Sales_Territory_Country].
Use RunningValue in the expression
In the Top 5 Resellers expression, enter:
=RunningValue(Code.ConcatenateString(Fields!Sales_Territory_Country.Value, Fields!Reseller.Value), Max, "Group_Country")
- The ConcatenateString() function take 2 parameters, Fields!Sales_Territory_Country.Value and Fields!Reseller.Value.
- The aggregate function Max is the second parameter for the RunningValue() function.
- The last parameter is the name of the row group.
- The RunningValue() function iterates through each [Sales_Territory_Country] in the row group, finds each reseller, concatenates them, until it finds all the resellers in each [Sales_Territory_Country].
Concatenating row values into column can be easily done in TSQL, and also in SSRS.
I had a post, MDX #7 – Get only 10 elements from a MDX query, which showed an example of using the TopCount() function to get the top 10 customers who have the most Internet Sales Amount.
What can we do to get the top 5 resellers in each region/country?
Many of us probably know how to write a decent TSQL code to the top N records in a group (without using a cursor). What can we do in MDX query?
Try to get all the resellers in each country, and order them by the sales amount
Let’s first try to get all the resellers in each region/country, then worry about the top 5 next.
1. In order to make the Reseller Sales Amount show as a column, we used the measure [Measures].[Reseller Sales Amount] on the 0 axis.
2. In order to help to get the top 5 resellers in each country, let’s order the reseller first by creating a SET with the ORDER() function, [Measures].[Reseller Sales Amount] as the measure and BDESC flag to break the hierarchy.
3. In order to make all the region/countries AND the resellers show as rows, we used the CROSSJOIN of the dimension [Sales Territory].[Sales Territory Country] and the order set [Ordered Resellers].
Use Generate() function to evaluate Top 5 Resellers over a set of [Sales Territory Country]
Now we need to worry about how to only show the top 5 resellers in each region/country. TopCount() will be a good function to start with. In addition, we will also use the Generate() function to combine with the TopCount() function to get the top 5 in each country.
Here is the link to the reference of how the Generate function can be used: http://technet.microsoft.com/en-us/library/ms145526.
We are going to use the set expression syntax:
Generate( Set_Expression1 , Set_Expression2 [ , ALL ] )
The 3rd parameter ALL is optional.
In this example, we will only use the first 2 set parameters.
1. The first SET parameter are all the members from dimension [Sales Territory].[Sales Territory Country].
2. The 2nd SET parameter takes the dimension [Sales Territory].[Sales Territory Country] again, but with the CurrentMember function which returns the current member along the [Sales Territory Country] hierarchy during the iteration of each country. You can tell that the use of the CurrentMember function is the key here.
3. The TopCount() function is straightforward. It returns the 5 top resellers (based on measure [Measures].[Reseller Sales Amount] ). Together with the CROSSJOIN with each country during the iteration, it produces what we exactly needed.
To make sure that we got the result correct, compare the above result with the result from our first query.