Optimizing Microsoft SQL Server Analysis Services: Optimization Tools: Incremental Update Wizard
Now, let’s isolate the same number for IncrUpdate, our newly updated cube.
11. Click Close to dismiss the Cube Browser for the Budget cube.
The Cube Browser closes.
12. Right-click the IncrUpdate cube.
13. Select Browse Data from the context menu that appears, as we did with the Budget cube above.
The Cube Browser indicates that data is being retrieved, then populates the default view.
14. Click the Level02 heading for the row axis, and drag it into the dimension area above it to remove it from the grid, and to swap the Time dimension into the column axis.
15. Click the down arrow in the Account selector box.
The Account hierarchy appears under the selector box, allowing us to drill down on the hierarchy.
16. Expand All Account by clicking the “+” sign to its left.
17. Expand the Net Income level underneath All Account.
18. Expand the Total Expense level underneath Net Income.
The Account hierarchy in the expanded selector, with the Lease member at the bottom, appears.
19. Click the Lease expense member to select it in the selector.
The Cube Browser, with our changes reflected, appears as depicted in Figure 28.
Figure 26: The Cube Browser with Our Settings
20. Double-click 1998 in the row axis to drill down to Quarters.
The Quarters appear to the right of 1998.
21. Double-click Q4 to drill down to its constituent months.
We see the value of $ 29,906.95, for Month 12, 1998, appear, as shown in Figure 29.
Figure 29: The Balance for 12-1998 Lease Expense –Incrementally Updated Cube
The difference between the Lease expense for Month 12, 1998 ($ 29,906.95) in the Incrementally Updated IncrUpdate cube, and the same value for the original Budget cube ($ 20,117.95) is $ 9,789.00, the amount we typed into the special “new value” table we created in the FoodMart 2000 warehouse (see Figure 15). This confirms our understanding of the operation of the incremental update process.
22. Click Close to dismiss the Cube Browser for the Budget cube.
The Cube Browser closes.
23. Delete the practice cube, IncrUpdate, as desired.
24. Delete the “add” table we created, PostAdditions, in the FoodMart 2000 MS Access database, IncrUpdate, as desired.
25. Close all applications, as appropriate.
In this article, we introduced the MSAS Incremental Processing Wizard, whose role is to enable us to perform Incremental Updates of our cubes, while helping us to avoid double-counting data, among other dangers that are inherent with Incremental Processing. We first explored processing options in general, and then focused on Incremental Updates within the context of MSAS cubes. Next, we performed a hands-on exercise where we incrementally updated a copy of the MSAS Budget sample cube with a data value that we placed in a special table in the source warehouse, for purposes of our exercise.
We examined the options that are available to us, as we proceeded through the guided steps of the Wizard. Finally, we proved the results we obtained by showing that the magnitude of the increase in a balance in the Incrementally Updated cube equaled exactly the value we established to be added to our original cube, via the Incremental Update process.
Copyright 2004 by the author.