I used C# with ASP.NET to build the browser-based application that Figure 2 shows. If you're using the .NET languages for development, you know that the .NET Framework supports data access through ADO.NET, which is a native SQL driver and a native OLE DB driver. But in the .NET Framework, no ADO MD equivalent exists for accessing multidimensional data sources. Microsoft hopes to close this gap with ADOMD.NET, a new .NET API that the company announced when I was writing this article. ADOMD.NET, which should be in beta by late summer, won't require that you have Microsoft Data Access Components (MDAC) and PivotTable Service loaded on the client computer; instead ADOMD.NET will be based on XML for Analysis. (For more information about this Web service API, see Mastering OLAP, "XML for Analysis," April 2001, InstantDoc ID 19846.) With ADOMD.NET, you'll be able to develop analysis applications by using only .NET, which means the applications you create will be much smaller and easier to deploy.
Until ADOMD.NET is available, you have a couple of other choices for accessing multidimensional data sources. You can use the native OLE DB driver, which lets you execute MDX queries and retrieve the results as recordsets (rather than the multidimensional cellsets available in ADO MD). Or you can access the COM-based ADO MD through .NET's COM Interop capability. Although C# and the other .NET languages include excellent support for COM, accessing ADO MD is tricky because some of the ADO MD methods (e.g., OpenSchema) require parameter types that don't have exact .NET equivalents. For the time-tracking application I was working on, I decided to use the native .NET OLE DB driver because it was easier.
If you choose to use OLE DB to access Analysis Services, Listings 2, 3, and 4 will help you. The code in Listing 2 shows how to establish a connection to an Analysis Services database. Listing 3 shows code that executes an MDX query and retrieves the name of a dimension member from the resulting columns. Listing 4 demonstrates how to execute an MDX UPDATE statement to change a value in the cube.
You should know a few things about using the MDX UPDATE statement. First, only SQL Server 2000 Analysis Services supports this statement. If you're using SQL Server 7.0 OLAP Services, the MDX UPDATE statement will give you a syntax error. Even if you have Analysis Services, you might get a syntax error because you might be using the SQL Server 7.0 version of PivotTable Service. If you uninstall and reinstall the .NET Framework (which includes MDAC 2.7), the default version of PivotTable Service switches to SQL Server 7.0. This switch can happen even if you've never installed OLAP Services because the basic SQL Server 2000 components include both the SQL Server 2000 and 7.0 versions of PivotTable Service. You can fix this bug by changing the HKEY_CLASSES_ROOT\MSOLAP\Clsid registry subkey value to {a07ccd0c-8148-11d0-87bb-00c04fc33942}.
Second, the MDX UPDATE statement always updates leaf-level cells regardless of whether you specify a mid-level dimension member. A leaf-level dimension member is a dimension member from the lowest level in the dimension. A leaf-level cell is a cell identified by leaf-level dimension members from every dimension in the cube. If you don't specify a leaf-level cell in an MDX UPDATE statement, Analysis Services splits the UPDATE statement you provided into individual leaf-level UPDATE statements for all the cells that contribute to the aggregated value of the cell you specified. Sometimes this automatic allocation is exactly what you want—but not often. Notice that in Listing 4's UPDATE statement, I used dimension members from both the Time.Project and Time.Fiscal hierarchies of the Time dimension that Figure 1 shows. If I'd specified a dimension member from only one of the Time hierarchies, Analysis Services would evenly distribute the UPDATE value I specified across all the leaf-level members of the hierarchy that I didn't specify. This distribution happens because Analysis Services treats different dimension hierarchies as if they were different dimensions. Thus, Analysis Services would write back about 700 fractional numbers to the cube, one for each day in the Time dimension.
Finally, remember that it's appropriate to use transactions with the MDX UPDATE statement. When you use a transaction to group UPDATE statements between a BeginTransaction and a Commit statement, either all the updates work or none work. Listing 5 shows an example of how to structure a transaction using the OLE DB connection object. You can start a transaction with the BeginTransaction method of the OLE DB connection object. The BeginTransaction method returns a transaction object that you can use when you execute MDX UPDATE statements. When the transaction is complete, you can issue either a Rollback or Commit method on the transaction object. I used the transaction object to combine all the updates associated with a page of data—such as the page that Figure 2 shows—into a single Commit. When you use transactions, you guarantee that all employee changes will be made unless an error occurs—in which case no changes are made.
Unfortunately, I can't include all the source code for this application because it includes some proprietary information. However, the instructions I've presented in this article should be enough to help you implement an equivalent application for your business. The key parts of creating the application are structuring the cube, moving records from the write-back table to the fact table, and using MDX to perform updates. Unless you have specific analysis requirements that require you to implement your own project time-tracking application, you might prefer to buy an application rather than build it. But you can use the write-back capabilities of Analysis Services to develop many other custom applications such as a custom forecasting application or an application that explores your organization's "what-if" business scenarios.