Archive for year: 2008

Scenario: You need to personalize the captions of calculated members or/and KPIs of an SSAS cube. For example, a solution vendor may need to deploy a cube to multiple clients. Instead of maintaining separate cube definitions, the vendor may prefer to change the captions of certain calculated members and KPIs per client. There wasn’t a good story in SSAS 2005 about such requirements. In SSAS 2008, there are at least two techniques to tackle cube personalization.

First, SSAS 2008 introduces a new Caption property to calculated members and KPIs, which Chris Webb has already written about. Vidas Matelis has provided great examples of using the Caption property. Thanks to this enhancement, you can keep the names of calculated members and KPIs the same but change only their captions. This is the good news.

What if you need to make definition changes programmatically, such as to check the type of the client and change captions accordingly? Well, unfortunately the Caption property is not available in the AMO library as it has not been added to the KPI class. What’s worse is that the CREATE KPI statement doesn’t actually add the KPI definition to the KPIs Collection so you cannot programmatically change the caption by enumerate the AMO Kpis collection and update the KPI definition. To make the story short, the only way I’ve found to update the KPI caption is to update the cube MDX script (Cube.MDXScripts AMO collection). This forces you to resort to search and replace text inside the cube script. Sigh… The following code shows how to use AMO to connect to the cube and change the cube MDX script.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using Microsoft.AnalysisServices;

namespace AMO

{

class
Program

{

static
void Main(string[] args)

{

Server server = new
Server();

server.Connect(“<server>”);

Database database = server.Databases[“<database name”];

Cube cube = database.Cubes[“<cube name>”];

        // assume KPIs are defined in the cube default script

MdxScript mdxScript = cube.MdxScripts[0];

Command command = mdxScript.Commands[0];

// search and replace captions in the command.Text

        // command.Text = …

cube.Update(UpdateOptions.ExpandFull);

}

}

}

The second personalization option is the new Personalization Extension mechanism in the SSAS 2008, which I personally haven’t tried out. Microsoft has provided a sample here. A custom personalization extension is a .NET code that is deployed to the server. It lets you create user-specific session-level calculated members and KPIs without having to change the cube definition. The extension changes are transient and disappear when the user’s session ends.

There is a nasty known bug with SSAS 2008, where if a query that requests two measures with Sum and LastNonEmpty aggregate functions side by side and the query uses NON EMPTY (which all OLAP clients use by default), the server forces the measure with the SUM function to display empty.

Steps to repro:

Use the Analysis Services Tutorial cube (Lesson 10) which is included in the SSAS samples. Don’t use the Adventure Works cube because it has measure expressions for certain measures, so changing a measure aggregation function to LastNonEmpty and deploying the cube results gives the error “Errors in the OLAP storage engine: The metadata for the statically linked measure group, with the name of ‘Internet Sales’, cannot be verified against the source object.” That’s a different issue that has been around since SQL Server 2005 but apparently didn’t get fixed.

1.    Open the Analysis Services Tutorial project from Lesson 10

2.    Open the Analysis Services Tutorial.cube in the Cube Designer.

3.    In the Cube Structure tab, expand the Internet Sales measure group and select the Internet Sales-Order Quantity measure.

4.    In the Properties pane, change the AggregateFunction to LastNonEmpty.

5.    Deploy the cube.

6.    Browse the cube with the cube browser or Excel. Create a report that requests Internet Sales Amount and Internet Sales-Order Quantity measures sliced by the Subcategory attribute of the Product dimension.

Notice that the query forces Internet Sales Amount to empty. While waiting for an official hotfix from Microsoft, you can use one of the following workarounds meanwhile:

1. Don’t use the NON EMPTY clause in the query

or

2. Change the following property in the \Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Config\msmdsrv.ini file.

<DisableCalcExpressNonEmpty>1</DisableCalcExpressNonEmpty>

Not sure what DisableCalcExpressNonEmpty does as it is not documented but appears to work.

UPDATE 8/18/2008

DisableCalcExpressNonEmpty is a new property in AS 2008. Severe performance degradation is likely to result when set to 1, but it should be comparable to AS 2005 performance if not a little better. A hotfix better come out quickly [:(]