Introduction

SQL Server Analysis Services 2008 introduces a new feature called Personalization Extensions.

As said in book online, "Analysis Services personalization extensions enable developers to create new Analysis Services objects and functionality and to provide these objects and functionality dynamically in the context of the user session".

What's behind this functionality?

Running the sample

The first step to have a better understanding is obviously running the sample provided by Microsoft to illustrate this feature.

The sample can be downloaded here (SqlServerSamplesAS.msi).

Once downloaded and installed, the project dedicated to Personalization Extensions is located in folder \Programmability\Extensibility\PersonalizationExtensions.

It contains a C# project and a ReadMe.htm file, which contains a very precise step-by-step procedure to setup the sample.

Step-by-step guide

While there are few mistakes in the ReadMe.htm file regarding context verification for each user (which items are visible or not), I have to say the guide is very precise and straight forward: we immediately understand the overall implementation of Personalization Extensions.

Notes

• I deployed AdventureWorks for SQL Server 2005 to my SQL Server 2008 as I didn't manage to restore SQL Server 2008 samples (SSQL didn't recognize FILESTREAM activation).
• Metadata ISV_1 folders were supposed to expand to sub-folders such as Common, Finance, and so on. This didn't work in my case. Instead, I got ISV_1/Common, ISV_1/Finance, etc. Fortunately, this didn't disturb the good working of the sample.

Debugging

Debugging the Personalization Extension (PE) sample really helps understanding its mechanism.

Everything works via event registration. There are two levels of event registration:

• Server: when PE is "mounted", SSAS calls it, and exposes a context object (class Microsoft.AnalysisServices.AdomdServer.Context) containing a reference to the server (Microsoft.AnalysisServices.AdomdServer.Server). The server object exposes two public events: SessionOpened and SessionClosing.
• Session: when a user connects, SessionOpened event is called (SessionClosing on disconnect). The context object contains a reference to the current connection (class Microsoft.AnalysisServices.AdomdServer.AdomdConnection) which exposes two public events: CubeOpened and CubeClosing.

Subscribing to the four events allows us to trigger MDX commands such as ALTER CUBE, CREATE SET, CREATE KPI, etc.

Usage scenarios

Personalization Extensions will give us the ability to extend a cube, depending on the user context, without updating the cube itself.

A question that comes immediately to my mind is: can it be used for security?

Can it be used for security?

SSAS authentication strongly relies on Windows accounts. If we want to browse the same cube with two security strategies, we will need to have two different Windows accounts.

This can be annoying, for example in a Budget and Planning application, where a same user can have different positions in the company:

• As a head of the IT department, he will need to see IT related data for all the branches of the enterprise.
• As a head of a branch, he will need to have only access to his branch's data.

Those requirements could of course be combined into one security strategy, but user may want to only see data related to the position he currently "incarnates" when he connects to a cube.

As we've seen before, we can trigger MDX commands from a PE at connection time:

• CREATE SUBCUBE MDX statement can be used to alter cube space, and hide unwanted dimension member. This would make a good candidate for dimension security substitution.
• CREATE CELL CALCULATION statements can be used to change/hide cell data (even if it's not its primary purpose). This would make a good candidate for cell Read security substitution, but not for Read/Write security, which is used in a Budget and Planning scenarios.

Feature request

Back to the OLAP Services 7 and Analysis Services 2000 times, cell security was evaluated on client side.

While messing with cell security, I discovered it was applied at connection time by Pivot Table Services, via GRANT CELL statements.

The syntax looked like: GRANT CELL READ|WRITE ON CURRENTCUBE FOR '<MDX formula returning 0 or 1>'.

Of course, those commands were reserved to PTS, in a way that it wasn't possible to execute them once connected.

That would be nice if we could be able to execute such statements in the context of a PE.

Conclusion

Even if Personalization Extensions are not an obligatory step in OLAP design, they can be very handy in situations where user oriented customization is necessary, without the need to update cube structure.