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.

Introduction

In a recent post, I started investigating around a new Analysis Services 2008 feature: writeback partition with MOLAP storage.

Unfortunately, I got stopped by a bug in SQL Server 2008 CTP 5 which disallowed me from committing my updates.

SQL Server 2008 CTP 6 is now available. I can now continue from where I stopped last time.

Committing updates

After our UPDATE CUBE MDX command from SSMS, let's run a COMMIT TRAN statement to validate our transaction.

This step works perfectly with CTP 6. We can see records inserted by SSAS into the writeback table.

What's behind the scene?

Processing

When we fully process a MOLAP writeback partition, we notice an extra item (compared to when processing a ROLAP one) in the Process Progress dialog box. This item shows the SQL query to the writeback table used to feed MOLAP writeback partition.

Profiling

If we activate SQL Server Profiler during COMMIT TRAN operation, we can see the following actions.

With ROLAP writeback:

With MOLAP writeback:

Extra WriteData events are issued, notifying SSAS 2008 feed of the MOLAP partition with writeback table data. This incremental feeding seems to be done by processing a temporary partition (named here WriteTable_Budget_INCR_UPDATE_TEMP_epn1o_), then merging it with the writeback partition.

A very interesting point is that if we profile relational database during commit operation, we can see the INSERT BULK T-SQL statement into writeback table, but no SQL query for processing temporary incremental writeback partition. It seems that SSAS takes in memory session updates and "Direct data load" them in this partition, as SQL Server Integration Services (SSIS) does with Partition Processing Data Flow items.

Performance gain

What improvement can we expect from a MOLAP writeback partition?

Test 1 (query)

To apprehend performance gain, we can do a simple test: in both cases (ROLAP and MOLAP), query the same cell, and see what's being done in SQL Server Profiler.

Test sequence:

1. Clear cube cache.
2. Query cell.
3. Query cell again (to check cache effect).

First query: data is read from measure group partition. With the ROLAP writeback partition, an extra ExecuteSQL event is triggered that reads writeback table data for corresponding cell. Data is cached.

Second query reads data from cache

The need to issue a SQL statement at query time makes MDX query processing longer. This extra step will dramatically increase query execution in situations where cube cell reading relies on many other cells (in case of calculations for example).

Test 2 (update)

The second test intends to observe cube cache consistency after committing updates.

Test sequence:

1. Clear cache.
2. Query cell twice.
3. Update cell
4. Commit transaction.
5. Query cell.

In ROLAP mode, the UPDATE CUBE statement triggers 2 ExecuteSQL events to read writeback table data. The commit step doesn't show a big difference between to two modes (see Profiling section earlier in the article).

In both bases, the commit step clears cube cache. As a consequence, the next cell read won't be able to rely on cube cache.

That would be nice if SSAS could handle 2 caches: one for the fact data, and one for the writeback data. This would reduce the effect of committing updates.

Which method is faster?

I wrote a VB Script (see previous article on performance) that executes steps of update test (test 2).

I have stressed SSAS with this simple benchmark (running it 10 times), for both ROLAP and MOLAP writeback modes.

The results are shown in table below (times are in seconds):

Querying and updating is faster with MOLAP writeback (mainly because no ExecuteSQL event is required to query writeback partition).

Committing is much slower with MOLAP writeback (probably because of the time needed to process/merge temporary partition). This shouldn't be a major issue, as committing is usually done after a series of queries/updates, like saving a document.

Conclusion

MOLAP Writeback is a promising feature for a read/write OLAP context.

In a budgeting application, where users (sometimes called contributors) submit updates (i.e. commit changes) in a cycle way, query performance decreases, as SSAS permanently issues SQL statements to query writeback table. This won't be the case anymore with MOLAP Writeback. We can expect here a huge performance improvement.

Introduction

Since version 7 (OLAP Services), a client must use an OLEDB for OLAP (ODBO) provider to connect to an Analysis Services database.

Microsoft provides a client setup which embeds this provider plus extra related components, such as local cube generation layer. This setup is called Pivot Table Services, or OLE DB Provider for Analysis Services, depending on the SSAS version it corresponds to.

Until SSAS 2005, ODBO provider was "talking" directly with OLAP server. Then SSAS 2005 came with a new XML for Analysis protocol (XMLA). ODBO is still the way to query SSAS 2005. For more details, check this link.

With the coming of SSAS 2008, let's have a look at potential changes for connecting.

Connecting

An easy way to test connection to SSAS is by using Microsoft Data Links.

A Data Link can be defined by creating an empty .txt file, and changing its extension to .udl.

In the provider list, we notice a new item "Microsoft OLE DB Provider for Analysis Services 10.0".

Does this mean we'll need a new a new provider to connect to SSAS 2008?

If we look at the connection string generated by the Data Link (once server and database have been set), we don't see any difference compared to a SSAS 2005 connection string.

[oledb]

; Everything after this line is an OLE DB initstring

Provider=MSOLAP;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Adventure Works DW;Data Source=localhost

Looking for provider details

A deep search in Windows registry helps getting more information on MSOLAP CLSID:

• HKEY_CLASSES_ROOT\MSOLAP key has (default) value "MSOLAP 10.0 OLE DB Provider".
• HKEY_CLASSES_ROOT\MSOLAP\CurVer has (default) value "MSOLAP.4", which is the exact CLSID for SSAS 2008 ODBO provider.

Lets retrieve HKEY_CLASSES_ROOT\MSOLAP.4\CLSID default value ({10154F28-4979-4166-B114-3E7A7926C747}) and use it to jump to HKEY_CLASSES_ROOT\CLSID\{10154F28-4979-4166-B114-3E7A7926C747}\InprocServer32 key: default value is C:\Program Files\Microsoft Analysis Services\AS OLEDB\10\msolap100.dll.

The new SSAS 2008 OLEDB for OLAP provider is hosted by DLL msolap100.dll.

If we explore C:\Program Files\Microsoft Analysis Services\AS OLEDB\10 folder, we will find other related DLLs.

Note the msmdlocal.dll file grew up from 15 Mb to 22 Mb. Maybe this CTP still includes debugging information that will be removed for RTM.

Compatibility

There is still one big question: will we need to deploy a new ODBO provider to access SSAS 2008?

The coming of a dedicated provider (version 10) tends to confirm this.

The best way to be sure is by trying to connect to SSAS 2008 by using SSAS 2005 ODBO provider. We'll do this from a separate PC to insure no favorable interference between 2005 and 2008 providers.

The test is successful: we can connect using ODBO for SSAS 2005, which is a good point.

Connection properties

If we compare connection string properties exposed by SSAS 2005 and SSAS 2008 providers, we notice a new property for SSAS 2008: Update Isolation Level.

Here are some quotes from BOL about this property:

• "Specifies whether the cells in a single UPDATE CUBE statement are isolated from each other. The default is not isolated." (taken from AdomdConnection.ConnectionString Property).
• "When updated cells do not overlap, the Update Isolation Level connection string property can be used to enhance performance for UPDATE CUBE" (taken from Write-Enabled Partitions).

So, this connection property is dedicated to writeback, one of my favorite subjects. I suppose ODBO provider for SSAS 2008 will be required to benefit this new property. I will not say more on it, as I think it deserves a full article on its own…

Schema rowsets

Book Online doesn't mention new OLEDB schema rowsets. A new feature, metadata rowsets, allows query of metadata without using the object model. This very good article from Vidas Matelis deals with this subject.

Conclusion

Good news:

• The object model for querying didn't change, so an application developed for SSAS 2005 will work with SSAS 2008.
• SSAS 2005 ODBO provider is compatible with SSAS 2008, avoiding deployment of a new ODBO provider, and making server migration transparent on client side.

Update (24/02/2008)

Darren Gosbell as written an article on SSAS 2008 CTP 6 schema rowsets. It seems that there a few new things.

In my recent post about SSAS 2008 MOLAP writeback, I couldn't commit UPDATE CUBE changes on a cube using a writeback partition defined with a MOLAP storage mode.

The feedback I posted on MS Connect got a quick response from Microsoft: the problem has been fixed (case closed).

This means we will be able to investigate further on the new feature in next SQL Server 2008 CTP.

Now I'm looking forward this next CTP…

Introduction

Analysis Services introduced the capability to define a writeback partition with a MOLAP storage (the only choice until now was ROLAP).

You can check this whitepapers for a full list of enhancements: http://www.microsoft.com/sql/techinfo/whitepapers/sql_2008_bi.mspx

This new feature should help getting performance gains, as described by Microsoft:

"MOLAP Performance (FITS): New MOLAP-enabled write-back capabilities in SQL Server 2008 Analysis Services remove the need to query ROLAP partitions. This provides users with enhanced writeback scenarios from within analytical applications without sacrificing the traditional OLAP performance."

Configuring MOLAP writeback

My tests were done using SQL Server 2008 November CTP virtual machine.

CTP can be downloaded from https://connect.microsoft.com/SQLServer/content/content.aspx?ContentID=5395.

VHD can be downloaded from http://www.microsoft.com/downloads/details.aspx?familyid=6a39affa-db6e-48a9-82e4-4efd6705f4a6&displaylang=en.

Writeback can be enabled from both SSMS and SSBI Development Studio.

SSMS offers a straight forward way of configuring writeback via a wizard, while SSBI Development Studio allows defining partition properties (like error configuration).

Direct XMLA definition is also available for ASSL specialists.

From SSMS, writeback can be enabled by expanding cube measure group, and right-clicking on Writeback folder item.

This gives access to a wizard. We immediately notice the new storage mode option, giving access to MOLAP (default), or ROLAP choice.

Even if storage mode is MOLAP, writeback mechanism will store updates in a writeback table, which guarantees data persistence even if cube is unprocessed.

This results in the creation of a writeback partition:

Writeback table will be created during writeback partition process if it doesn't exit.

The interesting point here is that it seems it will be possible to change the storage mode of an existing writeback partition (by disabling/enabling writeback), thus allowing easy optimization of an existing cube with pre-filled writeback data.

How does it work?

I suppose SSAS 2008 will transparently keep synchronized an invisible duplicate MOLAP partition that stores writeback table data, therefore reducing the amount of SQL queries against relational data source (where writeback table is stored).

Using MOLAP writeback

Once our cube is write-enabled, let's run an UPDATE CUBE MDX command from SSMS.

Then, let's run a COMMIT TRAN statement to validate our transaction. SSAS will insert new records to the writeback table as part of the execution.

That's where things get complicated, as SSAS 2008 returns the following error:

"Executing the query ...

Server: The operation has been cancelled.

Errors in the metadata manager. The write enabled 'WriteTable_Budget_INCR_UPDATE_TEMP_kf5kj_' partition must have table binding.

Execution complete"

I tried different workarounds to make it work (i.e. commit), unfortunately with no success.

I checked on the Microsoft Connect web site, but nobody seems to have reported this issue yet.

What's next?

Hopefully, Analysis Services development team will correct this problem for the next CTP.

So I will be patient, and wait for it to continue my investigations:

• Performance tests (compared to ROLAP writeback).
• Cube cache behavior on commit
• Etc.

See you soon.