PerformancePoint gives you the flexibility to have multiple assumption models to use as you please in your main models. Although this is great, I've found a problem when the two assumption models have different member sets for the same dimension, and so in an extension to my last assumption model post, this post provides a workaround for the issue.
Consider the following example. I've got a main model where I want to use 2 assumption models, namely:
- HR Assumptions - Uses the HR member set from the BusinessDriver dimension;
- Strategic Assumptions - Uses the Strategic member set from the BusinessDriver dimension.
If you go and add the two assumption models to the main model at the same time, then everything looks normal, as shown in the screen shot below:
Once you deploy successfully, you will of course want to write a business rule to pick up the assumption model data. However, when writing the rule and trying to pick from the BusinessDriver member selector, you will see that you can unfortunately only select from one member set, as shown below:
If you need to write rules that reference specific members in both member sets, then you will be out of luck. It's not even possible in any kind of native MDX rule, as the main model cube that gets created in Analysis Services only contains the dimension that has been created from the 'HR' member set. It would seem that PerformancePoint just picks the member set that is first alphabetically.
The workaround for this issue is simply to create a single member set that combines the two original member sets. Therefore, each assumption model will contain more members than required, but that's far better than not being able to write the rules that you need.
So just something to be aware of, and catch, at design time - rather than in the middle of your build.
Microsoft have just released Cumulative Update 8 for SQL Server 2005 Service Pack 2.
Amongst the changes are a few fixes for small problems in Analysis Services, such as:
- Infinite recursion occurs in the CalculationPassValue function in SQL Server 2005 Analysis Services;
- A data source view that connects to an Oracle database generates incorrect relationships when you develop a Microsoft SQL Server 2005 Analysis Services project by using BIDS;
- The MeasureGroupMeasures function does not work correctly in SQL Server 2005 Analysis Services;
- All the MDX queries that are running on an instance of SQL Server 2005 Analysis Services are canceled when you start or stop a SQL Server Profiler trace for the instance.
Details the above fixes and many more can be found here, as well as details of how to download the update. As usual, you can only apply the hotfix if you have Service Pack 2 installed.
Details of all SQL Server 2005 builds released after Service Pack 2 can be found here.
XLCubed Version 4 was recently released, adding support for increased data visualisation via MicroCharts, as well as enhanced web publishing.
I've been using XLCubed since version 1 and have always found it both powerful and easy to use. All the standard functionality that you'd expect from an OLAP/Analysis Services Excel add-in exists and is incredibly intuitive, yet there's plenty more if you want to dive deeper. In particular, I've always liked the following features:
- Wealth of right click functionality, such as remove only, keep only, propagate across sheets;
- Drag and drop pivoting - alter your report quickly without having to open toolbars or menus;
- Converting grids to formula - Allows you to easily create disjoint reports using Excel formulas;
- Linking grids - Means that an action in one grid (such as a drill down on a dimension) can automatically perform the same operation on multiple linked grids.
As mentioned, version 4 sees the inclusion of the interesting MicroCharts feature to the XLCubed grids. This essentially means that you can choose to display just numbers, a graphical MicroChart visualisation of the numbers, or both (see below). The MicroCharts do not hinder the core functionality, such as drill down or pivoting, and can be turned on for any of the measures.
There's a lot more functionality worth looking at, you can find out more at www.xlcubed.com.