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.
- Select the Unary Operators property (defaulted to False at present) on the Advanced tab.
- 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.
- Click to place a check in the box to the left of Enable Unary Operators.
- Click-select the Use an Existing Column radio button.
- Using the dropdown Existing Column selector, choose account_rollup.
- 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.
Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.