dcsimg

Introduction to MSSQL Server 2000 Analysis Services: Custom Cubes: Financial Reporting - Part II - Page 12

February 17, 2003

Finally, we need to take steps to ensure that the totals are accumulated correctly to arrive at accurate subtotals and, ultimately, a correctly presented Net Income amount. A review of our account table reveals that it not only provides the hierarchy of rollups through the inclusion of account "parentage" information, but its design also supports accurate accumulation of the rollups through its provision of the account_rollup column. (See the table again, if this was not evident in our visit to the FoodMart 2000 database earlier in the lesson).

The account_rollup column counts among its residents several operators that dictate the proper treatment of each account in its intended accumulations. We have only to tell Analysis Services how to find this information to ensure that the proper conventions are followed. We do this within the Advanced Properties for the Account level, conveniently enough.

  1. Select the Unary Operators property (defaulted to False at present) on the Advanced tab.
  2. Click the ellipsis (..) button to the right of the property box.

The Define Unary Operator Column dialog (similar in many respects to the Define Custom Member Column dialog pictured above) appears.

  1. Click to place a check in the box to the left of Enable Unary Operators.
  2. Click-select the Use an Existing Column radio button.
  3. Using the dropdown Existing Column selector, choose account_rollup.
  4. Click OK to save the settings and dismiss the dialog.

We are returned to the Cube Editor, where we see that the Custom Members and Unary Operators properties indicate True, as shown below.


Illustration 54: The Advanced Tab in Properties Reflects Our Modifications

We will once again process the cube to enact our modifications, and to determine our progress toward our objectives with the Financial Reporting cube.

11.  Click the Process Cube button on the top toolbar.

12.  Click Yes, when asked if saving the cube is desired.

13.  Click No to bypass the Storage Wizard.

14.  Ensure that the Full Process method is chosen on the Select the Processing Method dialog.

15.  Click OK to begin processing.

Processing occurs, ending with the green Processing Completed Successfully message appearing at the bottom of the dialog, once again.

16.  Click Close to dismiss the Process status dialog.

17.  Click the Data tab to view the results of our work after processing.

18.  Expand the 5000 Net Income level of the Account dimension, by double-clicking the "+" sign to the left of the description on the Data tab.

19.  Expand the 3000 Total Sales level now appearing within the expanded Net Income level (to its right), comparing the results to those depicted in Illustration 55.


Illustration 55: The Effects of the Newly Enabled Custom Members

A quick review of the totals contained in the sales_fact_1997 table reflects the accuracy of the summary data presented for Gross Sales and Cost of Goods Sold. Moreover, the rollup totals appear correct (reflecting, unfortunately, a loss for the organization during the reporting period under consideration).

We have accomplished our goal of combining the data contained in the Revenue and Expense cubes that we have created. Moreover, our summary and rollup totals appear to be correct, based upon a review of our source data and a knowledge of the presentation conventions found in typical income statements.

The steps we have taken to accomplish our goals have admittedly been circuitous, and perhaps a bit overwhelming. My intent was, again, to show as many nuances as possible within the confines of a relatively short lesson. Obviously, every environment, coupled with the specific business requirements of the design and implementation effort, will determine the best approach to take to its realization. Whatever the circumstances, MSSQL Analysis Services stands ready to provide options to help us to get the job done.

Next in Our Series ...

In this two-part article, we set out to build a simple cube to meet some illustrative business requirements, revolving around basic Income Statement Financial Reporting. We expand upon many of the concepts we have introduced at some level in earlier lessons, involving the integration of cubes as well as a host of information about cube components and general cube design and creation. We discussed some of the challenges that accompany cube design for financial reporting, and explored the use of Custom Members as an alternative approach to "merging" cubes, using cubes that we created under the scenario of a realistic business constraint - the absence of a single fact table that contained all that we needed to meet the objectives of the cube's design.

We traversed the process of Parent-Child dimension creation to practice the steps, and introduced various new concepts that we have not encountered in the series up to this point, including the use of Custom Members and the handling of rollup and aggregation considerations. Among other concepts we discussed and put into action, we made use of a UNION ALL query to prepare a "virtual" fact table for more effective cube creation, introduced methods of sign and data type control within our presentation, and addressed formatting and other presentation considerations as we created a Financial Reporting cube that focused upon the Income Statement.

In our next article, Drilling Through to Details: From Two Perspectives, we will explore executing drillthrough statements on multidimensional cubes. First, we will discuss scenarios where drillthrough from summary cube data to the underlying details might be valuable to information consumers. Next, we will examine strengths and weaknesses of the capability in MSSQL 2000 Analysis Services. We will discuss the steps that need to be taken to implement drillthrough, then set up a sample drillthrough in the Cube Editor, so as to focus on concepts in an introductory fashion.

We will then practice the creation and use of an MDX query that uses the DRILLTHROUGH statement to retrieve the source data for a cube cell. We will explore the syntax for the DRILLTHROUGH statement, and discuss options and parameters that surround this functionality and its potential uses, as we practice the new concepts with hands-on exercises.


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