Tag Archive for: Analysis Services

Here is a great tip thanks to Greg Galloway who pointed me to the Steve Novoselac’s excellent blog. By default, Excel 2007 PivotTable stacks (hierachizes) the attributes on the report even if they are from the same dimension. For example, let’s say the user wants to slice by the product hierarchy but want also some additional product attributes, such as the product color, size, etc. By default, Excel will produce the following layout:

Needless to say, this is hardly what the users want to see as they would prefer the product attributes to be displayed in a tabular format. Luckily, this is easy albeit not very intuitive.

1. Before you drop the first field on PivotTable, click the Design main menu on the ribbon (the one next to Options menu).

1. Expand the Report Layout drop-down menu and select Show in Tabular Form. Again, make sure that that PivotTable is empty before you do this.
2. If you want to remove subtotals, expand the Subtotals drop-down menu and select Do Not Show Subtotals.
3. Finally, to remove the +/- indicators, right-click on PviotTable and select PivotTable Options. On the Display tab, uncheck the Show Expand/Collapse Buttons.

Then, you can proceed to laying out the report as usual. Now you have a cool-looking tabular report.

I had a head-scratcher the other day. All of a sudden, SQL Server Management Studio stopped showing the cubes in the dropdown of the MDX Query tab although MDX queries and reports would execute just fine. The Reporting Services MDX Query Designer would complain with “No cubes found” error when I attempted to launch it from the dataset properties. This strange behavior coincided with installing SQL Server 2008 SP1 CTP, so I was quick to find a culprit. My suspicion was that the SSAS Windows authentication was failing was some reason although the SQL Server Profiler would show that the right Windows identity was connecting to the database.

To troubleshoot this further, I issued the following schema rowset query when connected to the SSAS database in SSMS:

select * from $system.mdschema_cubes

Interestingly, the query would return a list of the database dimensions (SSAS treats dimensions as cubes and prefix the dimension name with $) but it wouldn’t return the cube name itself. Then, I had a Eureka moment. I went to the Cube Structure tab in the BIDS Cube Designer, and I realized that that somehow I managed to set the cube Visible property to False. Not sure why you would ever want to make the cube invisible and why this property is there to start with but there it was. Flipping it back to True and redeploying the cube fixed the mysterious No Cubes Found error.

I had to tackle an interesting requirement the other day. A cube had a number of KPIs defined but the threshold values that were used to define the “good”, “bad”, and “so-so” status ranges had to be defined as configurable values at any level of the organization dimension and KPIs. I settled on implementing a measure group that intersects the organization dimension and a new KPI dimension that lists the KPI names.

For example, as the screenshot shows, each KPI has two threshold values (Threshold Value1 and Threshold Value2) which can be defined at any level of the organization hierarchy. Note that the parent values read the setting values directly from the fact table instead of being derived by rolling up from the children.

The first implementation approach that comes to mind is to use the None AggregateFunction function for the measure group so you read the measure values associated with the member directly from the fact table. Some experimenting revealed the following “noble” truths about the None AggregateFunction:

1. BOL is wrong. I refer to the following statement:
   (None AggregateFunction) No aggregation is performed, and all values for leaf and nonleaf members in a dimension are supplied directly from the fact table for the measure group that contains the measure. If no value can be read from the fact table for a member, the value for that member is set to null.

Specifically, When None is used, the server loads the fact data into the measure group leaves (just like with other aggregate function) but it doesn’t roll up the values to the dimension leaves. See this Mosha’s post to understand the difference between the dimension and measure group leaves.

1. The way it stands, the None function brings a questionable value. A narrow case exists where you may want to perform assignments or calculations in the measure group leaves as the above post demonstrates. Why you would want to do this instead performing them at the fact table level (or as named calculations) for obvious performance advantages is beyond me.
2. There is no way to bring the measure leaf values to the dimension leaves. Shoot me a note if you manage to come with some clever hack to do so.

Note: It would have been great if the None function behaves as described in the documentation. Even better, it could be nice if the server automatically brings up the fact value when the cube slice results in one fact record, i.e. the user has drilled down to measure group grain. This will make it possible to implement text-based measures, such to capture comments, or other text-based attributes, that don’t justify moving them to a dimension. If you agree, vote for this enhancement on connect.microsoft.com.

So, how do we get around this predicament? One option is define a custom ~ operator for the Organization parent-child dimension so the server doesn’t roll up the measure. However, the custom operator will be applied to all measures sliced by the Organization dimension which more than likely will be an issue. Of course, a trivial workaround is to duplicate the Organization dimension but this leads to other disadvantages. IMO, a better solution might be to use the DataMember property and a scope assignment:

SCOPE (

MeasureGroupMeasures(“KPI Configuration”),

[Organization].[Organization Hierarchy].Members,

[KPI].[KPI].[KPI]

);

this = ([Organization].[Organization Hierarchy].CurrentMember.DataMember);

END SCOPE;

I scope at the KPI Configuration measure group where it intersects with the members of the Organization and KPI dimensions. The AggregationFunction property of the measures in the KPI Configuration measure group is now set to SUM. The tuple on the right side of the this assignment returns the measure value associated directly with the current data member. The new result is that this assignment overwrites the SUM function with the measure leaf value.