Posted by: Charles Maitland | Monday 3 July, 2006

WriteBacks to SSAS 2005

We have a client that required the ability to writeback expenditure forecasts to their Analysis Services cube. Nothing fancy but it had to be very simple to use as this forecasting was being rolled out to fairly junior managers.
My first plan was to use the Microsoft Excel addin for Analysis Services tool. This installs as an addin and allows users to build reports based on a cube and then writeback data to the cube.
Unfortunately I found it to be very cumbersome to use to write the report and also very unreliable. Sometimes drill downs would simply not work. It also failed the simplicity test required for the end users. In fact there were
The result of spending a day wrestling with it was that I ditched it, pulled out my VBA archived brain and wrote my own addin to do it.
In actual fact the critical core of code was not as painful as I had expected.
First I build an update string:
UpdateString = "UPDATE CUBE [Dev Cube] SET ("
UpdateString = UpdateString & "[Dim Accounts].[AccountFull].[Department Full].&"
UpdateString = UpdateString & "[P].&"
UpdateString = UpdateString & "[PB].&"
UpdateString = UpdateString & "[PB00].&"
UpdateString = UpdateString & "[PB8010].&"
UpdateString = UpdateString & "[K].&"
UpdateString = UpdateString & "[K0500]"
UpdateString = UpdateString & ",[Dim Fiscal Periods].[Hierarchy].[Fiscal Year].&[2006/2007].&[4],"
UpdateString = UpdateString & "[Measures].[Forecast Amount]) = (500 )"

Then I execute it as an ADO connection command

ConnectionString = "Provider=MSOLAP;Connect Timeout=30;Client Cache Size=25;Auto Synch Period=1000;Safety Options=2;Data Source=srv01;Initial Catalog=DEVCubes;"

Set objdbConnection = New ADODB.Connection
objdbConnection.Mode = adModeReadWrite
objdbConnection.CursorLocation = adUseServer
objdbConnection.IsolationLevel = adXactReadCommitted

objdbConnection.Open sConnectString

objdbConnection.BeginTrans
objdbConnection.Execute strUpdate
objdbConnection.CommitTrans

If Not objdbConnection Is Nothing Then
‘ The connection exists, so check it’s state
If objdbConnection.State = adStateOpen Then
‘ Close the connection
objdbConnection.Close
End If
End If

The fun parts were interacting with Excel to loop through the cells building the Update string and generally manipulating them into an intuitive solution

Its just a pity they are too early to go for PerformancePoint as this seems to rock at doing this very type of thing.

Advertisements

Responses

  1. Hi, thanks for valuable information.

    I’ve got a urgent question.

    when I update the cube (cell) , are there changes per cell that realted to updated cell ? or do I have to update it manually?

    thanks in advance.

  2. Hi if change a value in cel in Excel. Let’s say a dimension name, How can i see the Update in my cube?

    What’s the best option for write back functionality between Excel 2007 and MSAS 2008? Help..

  3. While the method you have described would work for smaller sets of data, the inherent capability for writeback is not very well supported by Microsoft.

    Performance Point Server was a good way forward but Microsoft seems to have made a U turn on the positioning of the product šŸ˜¦

    We needed to implement dynamic writeback for large sets of data and ended up building a custom solution to handle the same, you could have look here …

    http://piglings.blogspot.com/2009/08/excel-2007-olap-writeback-ssas-2008.html


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: