Introduction to MSSQL Server 2000 Analysis Services: Performing Incremental Cube Updates - An Introduction - Page 6

November 8, 2004



Verify the Operation of the Incremental Update



In drawing our exercise to a close, let's verify the results of our Incremental Update. This should be easy to do, in light of the fact that we know we only applied an added month, the final month (12) of the 1998 operating year, to a single account that we can also easily locate (Account 4400, the Lease Expense account in the chart of accounts used in the Budget cube, from which we cloned the IncrUpdate cube.)


Conveniently enough, we have a copy of our cube just before its Incremental Update - the Budget cube itself - which we can browse for a total Lease expense for the period (12-1998) under examination. We can then compare the balance we obtain there to the balance for Lease expense, for period 12 of 1998, for the IncrUpdate cube, and see that the difference between the two is the amount of the incremental addition we have performed.


Let's perform a couple of quick browses at this point, to prove our understanding of the Incremental Update process.


1.  Right-click the Budget sample cube.


2.  Select Browse Data from the context menu that appears, as shown in Illustration 24.



Illustration 24: Select Browse Data from the Context Menu

The Cube Browser indicates that data is being retrieved, and then populates the default view.

3.  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.

Year appears as the new heading.

4.  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.

5.  Expand All Account by clicking the "+" sign to its left.

6.  Expand the Net Income level underneath All Account.

7.  Expand the Total Expense level underneath Net Income.

The Account hierarchy in the expanded selector, with our ultimate target of the Lease member at the bottom, appears as depicted in Illustration 25.


Illustration 25: The Expanded Account Hierarchy

8.  Click the Lease expense member (which equals Account 4400 in the Budget cube- and therefore in its clone, IncrUpdate) to select it in the selector.

The Cube Browser, with our changes reflected, appears as depicted in Illustration 26.


Illustration 26: The Cube Browser with Our Settings

9.  Double-click 1998 in the row axis to drill down to Quarters.

The Quarters appear to the right of 1998.

10.  Double-click Q4 to drill down to its constituent months.

We see the value of $ 20,117.95, for Month 12, 1998, appear, as shown in Illustration 27.


Illustration 27: The Balance for 12-1998 Lease Expense - Before Incremental Update.

Now, let's isolate the same value within 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, and 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. (Drop 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 Illustration 28.


Illustration 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 Illustration 29.


Illustration 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 Illustration 15). This confirms our understanding of the operation of the incremental update process.

22.  Close the Cube Browser for the IncrUpdate 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.

Summary ...

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 an account balance, within the Incrementally Updated cube, equaled exactly the value we established to be added to our original cube, via the Incremental Update process.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

Introduction to MSSQL Server Analysis Services Series
Introduction to Security in Analysis Services
Cube Storage: Planning Partitions from a SQL Server Management Studio Perspective
Cube Storage: Planning Partitions (Business Intelligence Development Studio Perspective)
Cube Storage: Introduction to Partitions
Introduction to Cube Storage
Attribute Discretization: Customize Grouping Names
Attribute Discretization: Using the "Clusters" Method
Attribute Discretization: Using the "Equal Areas" Method
Attribute Discretization: Using the Automatic Method
Introduction to Attribute Discretization
More Exposure to Settings and Properties in Analysis Services Attribute Relationships
Attribute Relationships: Settings and Properties
Introduction to Attribute Relationships in MSSQL Server Analysis Services
Attribute Member Values in Analysis Services
MSSQL Analysis Services - Attribute Member Names
Attribute Member Keys - Pt II: Composite Keys
Attribute Member Keys - Pt 1: Introduction and Simple Keys
Dimension Attributes: Introduction and Overview, Part V
Dimension Attributes: Introduction and Overview, Part IV
Dimension Attributes: Introduction and Overview, Part III
Dimension Attributes: Introduction and Overview, Part II
Dimension Attributes: Introduction and Overview, Part I
Dimensional Model Components: Dimensions Part II
Dimensional Model Components: Dimensions Part I
Manage Unknown Members in Analysis Services 2005, Part II
Manage Unknown Members in Analysis Services 2005, Part I
Alternatively Sorting Attribute Members in Analysis Services 2005
Introduction to Linked Objects in Analysis Services 2005
Distinct Counts in Analysis Services 2005
Positing the Intelligence: Conditional Formatting in the Analysis Services Layer
Administration and Optimization: SQL Server Profiler for Analysis Services Queries
Mastering Enterprise BI: Time Intelligence Pt. II
Mastering Enterprise BI: Time Intelligence Pt. I
Design and Documentation: Introducing the Visio 2007 PivotDiagram
Actions in Analysis Services 2005: The URL Action
Actions in Analysis Services 2005: The Drillthrough Action
Mastering Enterprise BI: Introducing Actions in Analysis Services 2005
Mastering Enterprise BI: Introduction to Translations
Mastering Enterprise BI: Introduction to Perspectives
Introduction to the Analysis Services 2005 Query Log
Mastering Enterprise BI: Working with Measure Groups
Mastering Enterprise BI: Introduction to Key Performance Indicators
Mastering Enterprise BI: Extend the Data Source with Named Calculations, Pt. II
Mastering Enterprise BI: Extend the Data Source with Named Calculations, Pt. I
Process Analysis Services Objects with Integration Services
Usage-Based Optimization in Analysis Services 2005
Introduction to MSSQL Server Analysis Services: Named Sets Revisited
Introduction to MSSQL Server Analysis Services: Migrating an Analysis Services 2000 Database to Analysis Services 2005
Introduction to MSSQL Server Analysis Services: Introducing Data Source Views
Introduction to MSSQL Server Analysis Services: Reporting Options for Analysis Services Cubes: MS Excel 2003 and More ...
Introduction to MSSQL Server Analysis Services: Mastering Enterprise BI: Create Aging "Buckets" in a Cube
Introduction to MSSQL Server Analysis Services: Mastering Enterprise BI: Relative Time Periods in an Analysis Services Cube, Part II
Introduction to MSSQL Server Analysis Services: Mastering Enterprise BI: Relative Time Periods in an Analysis Services Cube
Introduction to MSSQL Server Analysis Services: Process Analysis Services Cubes with DTS
Introduction to MSSQL Server Analysis Services: Presentation Nuances: CrossTab View - Same Dimension
Introduction to MSSQL Server Analysis Services: Point-and-Click Cube Schema Simplification
Introduction to MSSQL Server 2000 Analysis Services: Manage Distinct Count with a Virtual Cube
Introduction to MSSQL Server 2000 Analysis Services: Distinct Count Basics: Two Perspectives
Introduction to MSSQL Server 2000 Analysis Services: Semi-Additive Measures and Periodic Balances
Introduction to MSSQL Server 2000 Analysis Services: Performing Incremental Cube Updates - An Introduction
Introduction to MSSQL Server 2000 Analysis Services: Partitioning a Cube in Analysis Services - An Introduction
Introduction to MSSQL Server 2000 Analysis Services: Basic Storage Design
Introduction to MSSQL Server 2000 Analysis Services: Derived Measures vs. Calculated Measures
Introduction to MSSQL Server 2000 Analysis Services: Creating a Dynamic Default Member
Introduction to MSSQL Server 2000 Analysis Services: Another Approach to Local Cube Design and Creation
Introduction to MSSQL Server 2000 Analysis Services: Introduction to Local Cubes
Introduction to MSSQL Server 2000 Analysis Services: Actions in Virtual Cubes
Introduction to MSSQL Server 2000 Analysis Services: Putting Actions to Work in Regular Cubes
Introduction to MSSQL Server 2000 Analysis Services: Reporting Options for Analysis Services Cubes: ProClarity Part II
Introduction to MSSQL Server 2000 Analysis Services: Reporting Options for Analysis Services Cubes: ProClarity Professional, Part I
Introduction to MSSQL Server 2000 Analysis Services: Using Calculated Cells in Analysis Services , Part II
Introduction to MSSQL Server 2000 Analysis Services: Using Calculated Cells in Analysis Services, Part I
Introduction to MSSQL Server 2000 Analysis Services: MSAS Administration and Optimization: Toward More Sophisticated Analysis
Introduction to MSSQL Server 2000 Analysis Services: MSAS Administration and Optimization: Simple Cube Usage Analysis
Introduction to MSSQL Server 2000 Analysis Services: Build a Web Site Traffic Analysis Cube: Part II
Build a Web Site Traffic Analysis Cube: Part I
Reporting Options for Analysis Services Cubes: Cognos PowerPlay
Reporting Options for Analysis Services Cubes: MS FrontPage 2002
Reporting Options for Analysis Services Cubes: MS Excel 2002
Introduction to MSSQL Server 2000 Analysis Services: Drilling Through to Details: From Two Perspectives
Introduction to MSSQL Server 2000 Analysis Services: Custom Cubes: Financial Reporting - Part II
Introduction to MSSQL Server 2000 Analysis Services Custom Cubes: Financial Reporting (Part I)
Introduction to SQL Server 2000 Analysis Services: Exploring Virtual Cubes
Introduction to SQL Server 2000 Analysis Services: Working with the Cube Editor
Introduction to SQL Server 2000 Analysis Services: Parent-Child Dimensions
Introduction to SQL Server 2000 Analysis Services: Handling Time Dimensions
Introduction to SQL Server 2000 Analysis Services: Working with Dimensions
Introduction to SQL Server 2000 Analysis Services: Creating Our First Cube








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers