Dimension Writeback

When you think of analytic applications, you might automatically think of analyzing historical data. However, many analytic applications such as bud-geting and planning applications make excellent use of OLAP technology by analyzing future data. In SQL Server 2000, Microsoft added several analytic features that make Analysis Services a better tool than it was in SQL Server 7.0 for these planning applications. Analysis Services lets you allocate a new value to all cube cells that contribute to the target cell. For example, if you enter a forecasted amount for product sales for a given quarter, Analysis Services can allocate that value among all the months in that quarter based on a weighting formula that you specify. And in addition to letting you write back to cube cells, an ability that SQL Server 7.0 provided, SQL Server 2000 lets you modify a dimension's structure. This ability is called dimension writeback.

The ability to write back to a dimension (i.e., change the structure of a dimension) is fundamental to bud-geting and planning applications because users need to repeatedly create, delete, and restructure elements to model business plans. A typical budgeting application lets users enter expense amounts for different ledger accounts, time periods, and projects. Or users might need to create and reorganize accounts and projects in the application for different hypothetical scenarios. Another important feature of a budgeting and planning application is the ability to revise plans. Just as you can make dimensions for accounts, time, and projects in a cube, you can make each plan revision a dimension. Analysis Services' dimension-writeback feature lets you build this type of analytic application.

What You Need to Know

The first thing you need to know about dimension writeback is that only SQL Server 2000 Enterprise and Developer editions support it. If you have the Standard or Personal edition, you have to upgrade your SQL Server edition to get dimension-writeback capability. The second thing you need to know is how to find dimension-writeback documentation. SQL Server 2000 Books Online (BOL) tells you how to write-enable a dimension. But the information you need to programmatically access dimension writeback is harder to locate because none of BOL's indexed sections for dimension writeback links to the programmer's documentation. The MDX section doesn't include the information you need, either. To find the documentation, look in BOL's "PivotTable Service Programmer's Reference" section. In this section, you'll find a subsection called "Data Definition Language" that contains documentation about the ALTER CUBE syntax that Analysis Services uses for all dimension-writeback commands. If you want more information about Analysis Services' dimension-writeback features, I recommend two books: Fast Track to MDX by Mark Whitehorn et al. (Springer-Verlag, 2003) and MDX Solutions with Microsoft SQL Server Analysis Services by George Spofford (John Wiley & Sons, 2001). Each of these books dedicates part of a chapter to the dimension-writeback feature. Fast Track to MDX explains how you use the writeback capability in Analysis Manager, whereas MDX Solutions with Microsoft SQL Server Analysis Services explains the MDX syntax an application developer needs to create a planning application.

Dimension writeback requires that the write-enabled dimension be a parent-child dimension. A parent-child dimension is a dimension whose structure is defined by parent-child relationships rather than named levels. (Dimensions that have named levels always have the same depth, and members at any given depth are of the same type. For example, in a Time dimension, level 1 might contain years, level 2 might contain quarters, and so on.)

At first glance, this requirement seems limiting because not all dimension types can use writeback, but parent-child dimensions are the best match for the requirements of dimension-writeback applications. When you're adding, moving, updating, and deleting members of a dimension, you need flexibility to arrive at any tree structure in the dimension without needing to have specific member types at certain dimension tree levels. Non–parent-child dimensions have named levels, which means that all the dimension members that are the same depth from the root of the tree are similar items (e.g., cities in a geographic dimension) and are the same type. Parent-child dimensions have less rigid structures. So, for example, a chart of accounts might have dimension members organized so that areas that require more detailed tracking information are nested together, and some areas of the tree might be deeper than others.

The Data Definition Language (DDL) that Analysis Services supports takes advantage of the flexibility of parent-child dimensions and gives you features such as the ability to delete a dimension member in the middle of the tree structure and have the entire group of child members move up to fill the gap. Also, you can move a member to a new location in the tree and choose to either bring the member's children with it or leave the children behind. When you use DDL to manipulate parent-child dimension members, you can also change the members' property values.

Try Your Hand

To experiment with dimension-writeback DDL statements, I created a cube similar to the Budget cube in the FoodMart 2000 sample database. I copied the FoodMart 2000.mdb file to a new location, used Analysis Manager to create a new database, and used the Jet 4.0 OLE DB driver to add the copy of the FoodMart 2000 .mdb file as a data source. Then, I created shared dimensions that match those of the FoodMart 2000 database's Budget cube: Account, Category, Time, and Store. The Category dimension was the only place I created settings different from the settings in the FoodMart 2000 database. I configured the Category dimension as a parent-child dimension (the dimension already had a category_parent column for a parent-child relationship), and I enabled dimension writeback.

You enable dimension writeback through the Dimension Editor's Advanced Properties tab. If you've used cell-level writeback in the past, you know that Analysis Services creates a separate writeback table for the changes, and you have to periodically refresh the cube's fact table to avoid performance degradation. But when you use dimension writeback, the changes go directly into the dimension table, where they're visible to all other users.

After I enabled writeback on the shared Category dimension, I created a new cube that I modeled after the FoodMart 2000 expense_fact fact table and included all four of the shared dimensions. The only measure in the FoodMart 2000 expense_fact table is Amount, so I included only that measure in my cube. I structured the cube as Multidimensional OLAP (MOLAP), then processed it. Then, I was ready to test the cube's dimension-writeback capabilities.

You can use Analysis Manager to alter a write-enabled dimension by right-clicking the shared dimension and selecting Browse, which takes you to the Dimension Browser. There, you can view the dimension as a tree and modify it. You can right-click any dimension member to delete it, change it, or add a new member, or you can drag a member to a new location. All the Dimension Browser's features are also available through the DDL statement ALTER CUBE.

You can experiment with the ALTER CUBE statement by entering commands in the MDX Sample Application. If you do this, be aware that the dimension structure in the MDX Sample Application doesn't automatically reflect the changes from your ALTER CUBE commands. To refresh the dimension metadata, you have to change the current database to another database, then switch back again. Nevertheless, the MDX Sample Application is a good tool for learning the syntax of ALTER CUBE commands and verifying that your cube is configured correctly for dimension writeback.

Listing 1 shows ALTER CUBE commands for creating and moving dimension members. Enter each command into the MDX Sample Application, and click the green Run Query toolbar button for each one. To verify that the commands worked, open your copy of the FoodMart 2000 .mdb file in Microsoft Access and inspect the Category dimension table. After you execute all four statements, the .mdb file contents should match Figure 1. Be aware that even though the DDL immediately makes these changes in the dimension table, if you edit the table in Access, your changes won't show up immediately in the MDX Sample Application or any other front-end application. In fact, the MDX Sample Application will return an error because Analysis Services' cache no longer matches the underlying table. The errors will stop after you use Analysis Manager to reprocess the dimension and the cube.

The four commands in Listing 1 demonstrate how you might use the Category dimension to track multiple versions of a budget. I created two dimension members, Versions and OldVersions, that I could use to categorize different budget versions. Then, I created a dimension member representing a budget version called Budget v1. When I'm ready to create a new version of the budget, I can move (i.e., retire) Budget v1 to the OldVersions category, as the fourth command, MOVE DIMENSION MEMBER, shows. The sequence could continue if I created a new version, Budget v2, in the Versions category member.

Dimension writeback is a big part of Analysis Services' budgeting and planning capabilities. The abilities to track multiple revisions of a budget or plan and to reorganize the business entities in a plan are core features of a planning application and would be difficult to implement in an OLAP database without dimension writeback. If you're thinking about implementing a budgeting or planning application, be sure to take a serious look at this functionality.