Monday, August 29, 2011

Adding two Filter conditions in MDX WHERE statement

I wanted to add two conditions which contains different dimensionality in MDX WHERE statement. After trying several syntax I was able to find the way.

SELECT {[Measures].[Sales]} ON ROWS, {[Store—Store].[Store].ALLMEMBERS} ON ROWS
FROM [SalesCube] WHERE {[Customer].[All Customer].[CustomerName]} * {[Store—Region_Store].[All Store—Region_Store].[RegionName]}

If you entered the WHERE statement incorrectly, you will get an error message as
The function expects a tuple set expression for the 1 argument. A string or numeric expression was used.

Wednesday, August 17, 2011

MDX to get the parent level summation for a Measure with LastNonEmpty as aggregation

I faced an situation recently related to Inventory Calculations. In that we keep average inventory value in the Item level. It's parent level is category level. Aggregate function for average inventory measure is LastNonEmpty. In the report we have to show the summation of average inventory measure group by category level.
But the issue is if we take only the category dimension hierarchy and the average inventory measure, the value is incorrect. Because the aggregate function of the average inventory measure is LastNonEmpty, not the Sum.
We can resolve that issue by just adding the Item dimension hierarchy to the report data set and show the summation of average inventory measure value in the report using expressions.
But this will increase the report execution time if there are large number of items exist in the cube. Therefore in such situation report will take more time to load.

The best solution is add a calculated member to the cube which stores the summed value of items relevant to the category.
Just add a calculated member and add the Expression as

Sum(([Item—Category].currentmember.children), [Measures].[AverageInventory])

In this [Item—Category] is the hierarchy in the cube.

This resolves the issue with high report performance.

Friday, August 5, 2011

Filter SSAS Cube Data for a Date Range

When we retrieve cube data for SSRS reports, we may want to filter those data for a particular date range. In the below example I filter the Sales data of Stores for a Month range.

SELECT [Measures].[Sales] ON COLUMNS,
[Stores].[Store].members on ROWS from [Sales]
WHERE ([Date—Year_Month_Day].[All Date—Year_Month_Day].[" & Parameters!FromYear.Value & "].[" & Parameters!FromMonth.Value & "]:[Date—Year_Month_Day].[All Date—Year_Month_Day].[" & Parameters!ToYear.Value & "].[" & Parameters!ToMonth.Value & "])

That's all. It takes the From and To Year, Month values from those parameters and filter the Sales measure value. In this example I used a created hierarchy([Date—Year_Month_Day]) for the Date dimension.

Thursday, August 4, 2011

Two sets defined in the function have different dimensionality

When I tried to write a custom MDX query to retrieve Cube data, I got an Error message saying "Two sets defined in the function have different dimensionality".

The reason was we have to keep the same order of the Measures and Dimensions in the tuples, otherwise we will get the above error message