Building a Custom Writeback Application

Article
06/04/2012

NOTE This is part of a series of entries on the topic of Building Writeback Applications with Analysis Services. If you have not read the other posts in this series, you will want to read A Technical Review of the Simple Demonstration before proceeding.

Once you have a write-enabled cube in place, you then need a writeback application to interact with it. As shown in an earlier demonstration, Excel 2010 has writeback functionality built into its PivotTable feature. However, if you need another means to interact with your cube, you can build a custom writeback application by following a relatively simple pattern.

The pattern consists of connecting to the Analysis Services cube, issuing statements to update the cube, and then committing or rollingback these updates. Yes, there are other parts to this such as the retrieval and presentation of the data, the handling of data entry, etc. but those are presentation details I’ll assume most application developers are familiar with. (That said, I will address a few presentation details in my next post highlighting a pattern used by Microsoft IT for building writeback applications within our company.)

To establish a connection to Analysis Services, use a standard library such as ADO or ADO.Net. Analysis Management Objects (AMO) and ADOMD can also be used but these build off of ADO.NET and don’t really add much in the context of most writeback applications.

Minimally, the connection string employed should specify the MSOLAP provider and identify the Analysis Services instance and database. Here is a sample connection string that gets the job done:

Provider=MSOLAP;Data Source=localhost;Initial Catalog=Writeback Demo;

NOTE Once the connection is established, don’t close it until you are ready to commit or rollback your updates. Closing the connection will cause the writeback cache to be discarded, effectively rolling back any outstanding transactions.

With the connection established, you can initialize the writeback cache by issuing a BEGIN TRANSACTION statement. Excel 2010’s PivotTable does this but it’s not actually necessary. If you don’t initialize the cache explicitly, an implicit transaction is created with your first data update.

NOTE If you are familiar with Transact-SQL (T-SQL), you probably know you can manage transactions using BEGIN TRAN, COMMIT TRAN, and ROLLBACK TRAN or BEGIN TRANSACTION, COMMIT TRANSACTION, and ROLLBACK TRANSACTION. The MDX language used by Analysis Services does not accept the shortened form of the transaction keyword. You have to spell it out in full or you will receive an error.

Updates are submitted over the connection using the UPDATE CUBE statement. The statement is pretty straightforward in that you identify the cube you wish to update and assign values one or more tuples (cells) in a comma-delimited list. If you are assigning values to a nonleaf tuple, you can identify an allocation method as the last part of the statement. (I’ll cover allocation in much more depth in a later post.) Here is a sample UPDATE CUBE statement that assigns values to two leaf-level tuples:

UPDATE CUBE [Project Scorecard]
SET
(
[Project].[Project].[Project 01],
[Objective].[Objectives].[Objective A.1],
[Measures].[Score]) = 5,
(
[Project].[Project].[Project 01],
[Objective].[Objectives].[Objective A.2],
[Measures].[Score]) = 3;

Whether you explicitly initiated a transaction or allowed the UPDATE CUBE statement to implicitly create one for you, the final step to the writeback application pattern is to either commit or rollback that transaction. Committing the transaction with the COMMIT TRANSACTION statement forces Analysis Services to write the writeback cache to the writeback partition (table) created at cube design time. If the writeback table is not accessible due to either the relational database being offline or a permissions issue, an error will be generated. To roll back the transaction, the ROLLBACK TRANSACTION statement can be used or the connection to Analysis Services can simply be broken.

The following VBA code sample puts this all together for us. Why VBA, you ask? Because most writeback applications are written as Office applications. Keep in mind that most writeback applications are targeted at a small set of (typically expert) users for whom Office is preferred and VBA is familiar. Here’s the code sample:

'Add reference to Microsoft ActiveX Data Objects 6.0 Library

'Update Statement to Submit
Dim UpdateStatement As String = _
"update cube [Project Scorecard] " + _
"set ([Project].[Project].[Project 01]," + _
"[Objective].[Objectives].[Objective A.1]," + _
"[Measures].[Score])=4;"

Dim CommitStatement As String = "commit transaction;"

'1. Establish Connection to Analysis Services Database
Dim cn As New ADODB.Connection
cn.Open "Provider=MSOLAP;Data Source=localhost;Initial Catalog=Writeback Demo;"

'2. Execute Command & Commit Transaction
cn.Execute UpdateStatement
cn.Execute CommitStatement

'3. Wrap Up
cn.Close
Set cn = Nothing

To make this a bit more interesting, here is a variant of this pattern using VB.NET, ADO.NET and the Command object:

'Update Statement to Submit
Dim UpdateStatement As String = "update cube [Project Scorecard] " + _
"set ([Project].[Project].[Project 01]," + _
"[Objective].[Objectives].[Objective A.1],[Measures].[Score])=4;"
Dim CommitStatement As String = “commit transaction;"

'1. Establish Connection to Analysis Services Database
Dim cnstr As String = "Provider=MSOLAP;Data Source=localhost;Initial Catalog=Writeback Demo;"
Dim cn As New Data.OleDb.OleDbConnection(cnstr)
cn.Open()

'2.Set Up Command
Dim cmd As Data.OleDb.OleDbCommand = cn.CreateCommand
cmd.CommandType = CommandType.Text

'3. Execute Command & Commit Transaction
cmd.CommandText = UpdateStatement
cmd.ExecuteNonQuery()
cmd.CommandText = CommitStatement
cmd.ExecuteNonQuery()

'4. Wrap Up
cn.Close()

And here is a final variant, again using VB.NET, ADO.NET and the Transaction object. The Transaction object doesn’t really add much to the sample but I’ve put it here for completeness:

'2. Set Up Command & Its Transaction
Dim trn As Data.OleDb.OleDbTransaction
Dim cmd As Data.OleDb.OleDbCommand = cn.CreateCommand
cmd.CommandType = CommandType.Text
cmd.CommandText = UpdateStatement

'3. Execute Command & Commit Transaction
trn = cn.BeginTransaction()
cmd.Transaction = trn
cmd.ExecuteNonQuery()
trn.Commit()

'4. Wrap Up
cn.Close()

Building a Custom Writeback Application

Additional resources