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 SubmitDim 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 DatabaseDim cn As New ADODB.Connectioncn.Open "Provider=MSOLAP;Data Source=localhost;Initial Catalog=Writeback Demo;"
'2. Execute Command & Commit Transactioncn.Execute UpdateStatementcn.Execute CommitStatement
'3. Wrap Upcn.CloseSet 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 SubmitDim 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 DatabaseDim 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 CommandDim cmd As Data.OleDb.OleDbCommand = cn.CreateCommandcmd.CommandType = CommandType.Text
'3. Execute Command & Commit Transactioncmd.CommandText = UpdateStatementcmd.ExecuteNonQuery()cmd.CommandText = CommitStatementcmd.ExecuteNonQuery()
'4. Wrap Upcn.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:
'Update Statement to SubmitDim UpdateStatement As String = "update cube [Project Scorecard] " + _ "set ([Project].[Project].[Project 01]," + _ "[Objective].[Objectives].[Objective A.1],[Measures].[Score])=4;"
'2. Set Up Command & Its TransactionDim trn As Data.OleDb.OleDbTransactionDim cmd As Data.OleDb.OleDbCommand = cn.CreateCommandcmd.CommandType = CommandType.Textcmd.CommandText = UpdateStatement
'3. Execute Command & Commit Transactiontrn = cn.BeginTransaction()cmd.Transaction = trncmd.ExecuteNonQuery()trn.Commit()