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

Monday Feb 17th 2003 by William Pearson

Author Bill Pearson returns in the second half of his Custom Cubes: Financial Reporting article. This lesson presents hands-on practice in building core cube structures from multiple data tables, then combining cubes containing specialized financial data into a central, custom Financial Reporting cube.

About the Series ...

This is the eighth article of the series, Introduction to MSSQL Server 2000 Analysis Services. As I stated in the first article, Creating Our First Cube, the primary focus of this series is an introduction to the practical creation and manipulation of multidimensional OLAP cubes. The series is designed to provide hands-on application of the fundamentals of MS SQL Server 2000 Analysis Services ("Analysis Services"), with each installment progressively adding features designed to meet specific real - world needs. For more information on the series, as well as the hardware / software requirements to prepare for the exercises we will undertake, please see my initial article, Creating Our First Cube.


In the first article of the series, we used the Cube Wizard to build an initial cube with the assistance of the Dimension Wizard. We progressed through subsequent articles, creating similar dimensions to those we built with the Wizard, focusing largely in our second article on using the Dimension Editor to illustrate options for building a more customized cube. We continued this examination of dimensions in Article Three, where we recreated the calendar time dimension, this time focusing on the process through which the Dimension Wizard converts existing time / date fields to a time dimension, along with its hierarchy of levels and members. Article Three also exposed ways to customize the predefined, time-related properties that the wizard establishes in building the time dimension, suggesting options for customization of these properties to enhance the cube, from the dual perspectives of user-friendliness and the reporting needs of the organization. We created an example of an alternate time dimension for fiscal time reporting, and then we discussed some of the considerations surrounding the simultaneous housing of both hierarchies in the same OLAP cube structure.

In Article Four, we examined another special type of dimension, the Parent - Child dimension, and explored the attributes that make it different from a regular dimension. We discussed the considerations that surround Parent-Child dimensions, such as the recursive nature of their data sources, and various actions that must be handled differently in their creation and maintenance. We created a parent-child dimension using the Dimension Wizard, within which we worked with levels and properties. Finally, we enabled values at the parent level of our newly created parent-child dimension. In Article Five, Working with the Cube Editor, we reviewed, summarized and integrated many of the concepts and components that we had previously constructed individually in earlier lessons. We undertook a complete cube build "from scratch," pulling together all that we had learned, to demonstrate the assembly of a cube more sophisticated than the cube we generated in our first lesson with the Cube Wizard.

In Article Six, Exploring Virtual Cubes, we introduced the concept of virtual cubes, and practiced their creation and use. We discussed the options that virtual cubes provide, from the often-intermingling perspectives of consolidation of multiple data sources, presentation enhancement and control, and other functionality. Through the use of hands-on illustrations, we demonstrated some of the options that virtual cubes offer to extend the functionality and capabilities of individual OLAP cubes

In this two-part article, we will build a simple cube to meet some illustrative business requirements of a hypothetical corporate financial reporting function. We will expand upon many of the concepts we introduced in earlier lessons, where we used virtual cubes to merge the two into a single reporting "view," then we will discuss some of the challenges that accompany cube design for financial reporting, and explore an alternative approach to "merging"cubes.

In Part One of this article, we:

  • Created a core financial cube, focusing on the expense side, and based upon the expense fact table provided within the FoodMart 2000 sample database;
  • Discussed some of the realities of multiple fact tables in data sources that we often encounter in the business environment;
  • Explored some of the realities that accompany cube design for financial reporting.

In this half of the article, we will:

  • Develop our core cube further, bringing in sales / revenue and other information to make the Profit / Loss position of the organization complete for basic financial reporting purposes;
  • Explore and practice the use of a UNION ALL query to prepare a "virtual" fact table for more effective cube creation;
  • Discuss rollup and aggregation concepts and considerations;
  • Explore "merging" cubes using Custom Members;
  • Introduce methods of sign and data type control within our presentation;
  • Address formatting and other considerations that arise as we create a cube to meet the needs of our information consumers.

We will develop our initial core further in Part II of this lesson, then we will populate our model with the Revenue / Sales elements of a simple Income Statement, after discussing the options we have available within Analysis Services for doing so.

Picking Up Where We Left Off: The Financial Reporting Cube

As we recall, we set out in Part I of this article to build a basic Financial Reporting cube. Along the way, we discussed some of the high-level requirements one would expect to find in standard financial reporting, including balance sheets, income statements, accounts receivable and accounts payable reports. Beginning with the creation of a core Expense cube, we looked at the components of a cube that could be used to partially support a simple Income Statement, introducing new concepts to our growing knowledge about Analysis Services as they became useful. Throughout the lesson, we re-traced many of the common steps we transition in building any cube, as we began the construction of a sample finance cube for our hypothetical financial reporting department.

In Part I we performed setup of the core cube, focusing first on the expense side of the Income Statement. As we noted, the sample database that we used does not include a "financial statements fact table;" it focuses more on the revenues side of the equation in the construction of the ubiquitous Sales cube that it presents (and that virtually all subject matter authors select) as a handy model for tutorial and other purposes. Our focus is the construction of a cube in a way that reflects reality - and that presents scenarios where the data that we want to analyze are scattered in various tables throughout the database.

As we begin the second half of the lesson, our next step is to pull the sales data into our Financial Reporting cube, and show how we can then integrate the expense and revenue data in a way that revenues are matched with the direct costs of generating those revenues. (Sales are matched with the costs of sales, i.e. the "direct" costs of goods that have been sold, etc.), and netted together to result in a Gross Margin amount. Other Expenses (often called "fixed" expenses) then need to be subtracted out of the Gross Margin to come to the overall Net Profit or Loss.

Our objective is to illustrate the construction of a basis, in the form of an OLAP cube, for the production of meaningful statements that reflect an organization's operating results. There are many approaches available to us to accomplish our objective within MSSQL Server 2000 Analysis Services, using only the FoodMart 2000 database provided as a sample in implementation of MSSQL Services Analysis Services. The route we take, while perhaps circuitous from the perspective of many, will represent an attempt to cover various aspects of one general approach. There are certainly other, perhaps more efficient / performance oriented methods for combining data from diverse sources, some of which we will mention as we progress.

Adding the Revenue Component

As we have mentioned above, financial reporting often pairs Revenues / Sales with the associated Cost of Goods Sold, to arrive at a Gross Margin. As many of us learned in school, an Income Statement, upon which this article will focus, displays the income (in this case, used synonymously with the term "Gross Margin"), expenses, and net earnings / net loss for a particular period of time, such as a three-month period (a fiscal quarter) or an annual period (a fiscal year). While income statements can become quite detailed and sophisticated, depending upon the needs and reporting requirements that drive their production, we will use a simple format within this article, so as to focus more upon the OLAP aspects, and less upon accounting terminology and nuances.

To simplify, the Gross Margin represents the gross profit from sales activity. This gross profit then has operating expenses (the "fixed" costs of operating the business) applied. After subtracting these operating expenses (which include overhead, among many other possible costs) from the contribution made by the Gross Margin, we can determine the Net Income / Net Loss of the operation. In our simple income statement structure, the Gross Margin will be assembled from data in the sales fact table (in the form of store sales and store costs - assumed here to be directly related, and appropriately matched from an accounting perspective, for the sake of simplicity). We will then derive Net Income from the combination of the Gross Margin and the Expenses that we intend to derive from the Expense cube we created in Part I of this lesson.

Suffice it to say that these, as well as far more complex, accounting treatments can be complicating factors in the creation of a financial reporting cube. Our case, however, is a great illustration of the steps we need to take to assemble a reporting cube from multiple tables - a scenario that is pervasive in the business world. Many large clients with whom I have acted as a consultant, such as brokerages and large manufacturing concerns, maintain extensive sales data in one or more data stores and detailed operating expenses in completely separate stores, for both analysis and operational purposes. The complexity only grows when multiple operations are combined to consolidate financial operations on a yet higher level, and so forth. While centralized data sources (such as warehouses or data marts) are typically constructed to provide a more unified reporting platform, as often as not, I come across scenarios much like that in our lesson, where parts of the reporting whole must be drawn from multiple sources into an OLAP cube. MSSQL Server 2000 Analysis Services provides excellent options for handling these scenarios, as we will see in this article and throughout this series.

Creating the Cube - Revenue Focus

Because our intent is to "marry" the Revenue and Cost of Goods Sold (and the Gross Margin that results from the combination of the two) with the Operating Expense that is summarized in the Expense cube, we need to plan a cube structure for revenues that mirrors the structure, at least with regard to the elements upon which we seek to report, of the Expense cube. When we review the Expense cube as we created it in Part I, we see the following structure:

Illustration 1: The Schema for the Expense Cube

Now, let's take a look at how sales_fact_1997, the table containing the revenue data from which we will build the core Revenue cube, stacks up to the structure of the Expense cube, from the perspective of dimensions and measures, by reviewing the table structure, as displayed in Illustration 2 below.

Illustration 2: The Design View of the sales_fact_1997 Table (FoodMart.mdb)

It becomes readily apparent that there are differences that must be overcome in creating a Revenue cube that closely resembles the Expense cube. This is probably why most of the articles and books that are readily available to us, at this youthful point in the life of Analysis Services, tend to supply surrogate databases with tables that make objectives like our own easier to accomplish, or to simply focus on simplistic objectives that can be accommodated conveniently by the existing FoodMart 2000 tables. The fact is that the requirement we have identified in this comparison, a requirement to align divergent sources, is a reality that most of us face everyday in the real world, for the reasons we have noted earlier, and a host of other drivers.

My focus in this and other articles is to provide practical options for dealing with requirements that approach reality - while constraining the lessons to the sample databases and other objects that are common to anyone who would be taking the time to join our sessions - not to simply teach what is "easily possible" within the FoodMart sample, or to require anyone who participates to purchase or otherwise obtain a database that has been, in effect, created to produce the desired results easily. Limitations of the samples aside, it is still possible to illustrate many concepts in a realistic manner without reliance upon outside tools or artificial environments.

To handle the situation confronting us, we might take numerous approaches, but one that I found both straightforward and "self-contained" within the FoodMart 2000 database and Analysis Services will be presented. To restate the detailed objectives in building the Revenue cube, we need to create a cube whose structure resembles the Expense cube with regard to any dimensions or measures that will exist in the ultimate central cube. The Revenue cube would, on the surface, appear to need the following fields to mirror the Expense cube enough to align itself for "apples to apples" reporting purposes:

  • account_id
  • amount
  • category_id
  • store_id
  • time_id

We can safely forego further consideration of currency_id, as we will assume, for the sake of our lesson, that both tables are denominated in the same currency. The data fields above will be useful to us in Revenue cube design and creation, however, and would appear to need to be addressed. Let's discuss each item briefly to understand the nature of the road ahead more fully.

First, the account_id field simply does not exist in the sales_fact_1997 table. However, we know that all the dollar amounts contained therein either represent sales, or the costs of the sales (Costs of Goods Sold, in our earlier discussion). The Expense cube relies upon the accounts table in the FoodMart 2000 database to provide the data used in the definition of its account dimension. The simple chart of accounts used in the company's reporting is represented in the table view of the account table, as partially pictured below:

Illustration 3: Table View of the account Table (FoodMart2000.mdb)

We can reasonably assume that the accounts assigned to the summarized cost of sales for a given date-row is matched to the corresponding sales from the same date. We can also see, from the chart of accounts in the account table above, the account to which the sales are assigned (account_id 3100, Gross Sales), together with the account to which Costs of Sales are assigned (account_id 3200, Cost of Goods Sold). We therefore know the account_id to assign to each value. However, herein lies our next complication.

The values themselves appear on the same row within the table, which means we cannot assign a single account_id to each line to allow us to become comparable with the layout of the Expense cube, which is generated from a fact table where one account_id exists per row. A breakout of the data into a more accommodating fact table design would be desirable, of course, but perhaps not be an option. I encounter more complicated renditions of this issue within many client engagements, particularly where the source system happens to be a relational or hybrid database. Often these clients do not want to hear that they "need a data warehouse." Whatever the scenario within which this complication is found, we will explore one way of approaching it, realizing that many others are within reach, but with an eye toward working within the constraints and data structures that I have outlined earlier.

Next stop on the list of alterations for alignment of the Revenue cube to the Expense cube is the assignment of the name "amount" to the measure, versus the currently assigned "store_sales" or "store_cost." Once the values are rearranged in separate lines, we will see that this is simple enough.

Category_id does not exist in the sales_fact_1997 table, although this would be simple enough to add. We will forgo this item, however, as we can see (through a review of the category table) that, although it appears to have been designed to contain ACTUAL, BUDGET, and other such classifications (and probably does for a year with which we are not concerned for our present purposes), a quick review of the expense_fact table reveals that only ACTUAL appears to be used anyway. As we don't stand to gain much from adding this to our Financial Reporting Cube (keep in mind that an added dimension often means an exponential increase in processing requirements, etc., especially as the warehouse / database grows over the years), we will simply pass on adding it to the modifications list we are compiling. Finally, the store_id and time_id requirements can be obtained from the existing sales_fact_1997 table without further manipulation.

Preparing the Source Data for the Revenue Cube

In order to meet our outlined objectives of working within the existing FoodMart 2000 database, we will visit the Access environment briefly to establish a means of creating a suitable "virtual" fact table to support the desired Revenue cube design.

As most of us are aware by this point in the series, FoodMart 2000.mdb is located, by default in the [Install Directory]:\Program Files\Microsoft Analysis Services\Samples directory; Once we have located the database on our PC's, we will navigate to it, and open it in MS Access, where we see the layout shown in Illustration 4 below.

Illustration 4: Selecting the FoodMart 2000 Database

We need to create a query that will provide the data we need in a layout that will be sufficient to provide a basis for creation of our cubes. Queries can be selected in the Cube Editor just as tables can, so we do not need to create a special table for this purpose. In addition, other benefits of using the query approach are that 1) the existing database schema remains intact and 2) the database suffers no appreciable increase in size; both of these considerations are meaningful in many client environments where altering the source database tables is undesirable and space is a constantly monitored statistic.

  1. Select Queries in the Objects pane on the left side of the Database view.

The queries that exist in the sample database appear as shown in Illustration 5 below.

Illustration 5: The Queries View of the FoodMart 2000 Database

  1. Click the New button in the toolbar atop the Database view.
  2. Select Design View.

The New Query dialog appears as shown below:

Illustration 6: The New Query Dialog, Design View

  1. Click OK.

The Design View appears for the New Query, with the Show Table dialog having the focus, and displaying the tables available for selection within FoodMart, as depicted below.

Illustration 7: Show Table Dialog

  1. Click Close to dismiss the Show Table dialog.

We will be creating a UNION ALL query to handle the following concurrent objectives (explained above):

  • Selection of the fact and dimensional data that we will need as a basis for the development of the Revenue cube;
  • Separation of the assigned store_sales and store_cost into discreet rows that simultaneously retain all other associated data fields that we require;
  • Assignment of the term "amount" to the store_sales and store_cost measures;
  • Assignment of the appropriate account_id's, 3100 (Gross Sales) and 3200 (Cost of Goods Sold), to the store_sales and store_cost measures, respectively;
  • Assignment of the name "account_id" to the newly added store_sales and store_cost account_id fields.

We will need to type the query directly, from Design Query: SQL View in MS Access.

  1. Click the SQL View button, shown in Illustration 8 below, in the upper left corner of the toolbar of Design View.

Illustration 8: SQL View Toolbar Button

The Select Query (SQL View) dialog appears, as shown below.

Illustration 9: Select Query: SQL View

  1. Type the following query into the Select Query (SQL View) dialog:
SELECT product_id, time_id, store_id, store_sales AS [amount], 3100 AS 
FROM sales_fact_1997
UNION ALL SELECT product_id, time_id, store_id, store_cost AS [amount], 3200 AS 
FROM sales_fact_1997;

Note: We use the UNION ALL statement to retrieve all records, including duplicates. The effect of a simple UNION statement is to eliminate what appear to be duplicates. Our query may appear to return duplicates simply because we selected only a subset of the sales_fact_1997 table, which, for the purposes of this exercise, we assume to be correct and to contain only intended data. A complete population of the data should, therefore, be preserved intact.

The Select Query (SQL View) dialog should appear as depicted in Illustration 10.

Illustration 10: The UNION ALL Query as Entered in Design Query

We are now ready to test the query to ascertain that it produces the correct results.

1.      Click the Run button (illustrated below) to execute the new query.

Illustration 11: The Run Button atop the Design Query Toolbar

After a moment or so, the query results appear as partially depicted in Illustration 12 below.

Illustration 12: The UNION ALL Query Result Set (Partial)

We note that 173,674 records are returned. This is exactly double the number of rows in the sales_fact_1997 table (86,837), and acts as a quick confirmation that the population returned is complete.

2.      Select File -> Close from the top menu.

We are asked if we wish to save the query.

3.      Click Yes.

The Save As dialog appears.

4.      Type revenue_fact_Query into the dialog box, as shown below.

Illustration 13: Save the Query as revenue_fact_Query

Revenue_fact_Query appears among the queries that existed upon our arrival, as shown in Illustration 14. The icon to the left of the query identifies it as a UNION query.

Illustration 14: Revenue_fact_Query Appears

Before we leave MS Access, we will perform an insertion to the account table to provide for a need we will explain later in the lesson.

5.      Click the Tables icon in the Objects pane to display the FoodMart 2000 database tables.

6.      Double click the account table to open it in Table view.

7.      Maximize the window to be able to see the table completely.

8.      Adjust the width of the right-most column, labeled Custom Members.

We observe the following expression, containing a LookupCube function, in the Custom Members field for Account 3100:

LookUpCube("[Sales]","(Measures.[Store Sales],"+time.currentmember.UniqueName+","
	+ Store.currentmember.UniqueName+")")

(This expression exists as a part of the scenario set underpinning the samples that accompany MSSQL Server 2000 Analysis Services. Its purpose is to illustrate the use of an expression in this context for the entrainment of data from the Sales cube, clearly the most famous of the bundled samples (most Analysis Services writers never discuss any of the other cubes that are distributed with Analysis Services - which possibly accounts for the relative absence of practical business guidance out there ...).

9.      Saving the expression to a text or other file, if you wish, or, perhaps better, backing up the table to be able to access it later (make a copy of the table, and rename the copy account_bak, or some such, for an easy "restore" after the lesson is done), replace the above expression with the following expression:

[Account].[All Accounts].[5000   Net Income].[3000   Net Sales].[3100   Gross 
	"+ Store.currentmember.UniqueName+")")

10.  We will add the following additional expression to the row immediately below the one to which we have added the last expression (again, in the Custom Members field, this time for Account 3200):

[Account].[All Accounts].[5000   Net Income].[3000   Net Sales].[3200   Cost 
	of Goods Sold],"+calendar.time.currentmember.UniqueName+",
	"+ Store.currentmember.UniqueName+")")

Note: If the above expressions present a challenge to type correctly, because of the newness to some of us of MDX (syntax, spacing, etc.), we can simply cut and paste the expressions, removing the "9" character first. (The notation is an indicator that the line is broken due to margin constraints of the document, and does not represent a "natural" break in the syntax). Leaving the character in place while pasting will cause issues later on, when we attempt to work with the expressions above.

We will revisit the above expressions in the final section of the lesson, explaining their purposes and uses in helping us to achieve our objectives of creating an integrated Financial Reporting cube.

11.  Select File -> Exit to close and leave MS Access, saving modifications if prompted.

Meanwhile, Back at Analysis Services ...

Let's get started with the addition of Revenue / Gross Margin information, by preparing to create the Revenue cube, which we will later introduce into the Financial Reporting cubes.

  1. Start Analysis Manager.
  2. Expand the Analysis Servers folder by clicking the "+" sign to its immediate left.
  3. Expand the Analysis Server name (mine is MOTHER, in this example), to see the tree shown below:

Illustration 15: Expand the Analysis Server

  1. Expand the FinRep_DB database that we created in the first half of this lesson, by clicking the "+" sign to its immediate left. (For the detailed steps involved in creating a database, see Custom Cubes: Financial Reporting - Part 1.)
  2. Expand the Data Sources folder to expose the FoodMartFinRep data source we created in the first half of this lesson, by clicking the "+" sign to its immediate left.
  3. Right-click the FoodMartFinRep data source, and select Edit from the context menu that appears.
  4. Click the Provider tab, and select Microsoft OLE DB Provider for ODBC Drivers, as shown in Illustration 16 below.

Illustration 16: Select Provider

  1. Click the Connection tab, and select FoodMart 2000 as data source name from the dropdown selector, as shown below.

Illustration 17: Select FoodMart 2000 Data Source Name

  1. Click Test Connection to ascertain a connection to the data source.

A Microsoft Data Link dialog should appear, confirming that the "Test Connection Succeeded."

NOTE: If any other response is obtained, or if the need exists to set up the data source for the first time, please refer to the procedure in Part 1, in other lessons of the series, or from the MSSQL Server 2000 Books Online and / or other appropriate documentation.

  1. Click OK to close the Data Link Properties dialog, and to return to the Analysis Manager console.

Once initialized, the Cube Editor will first guide us through the selection of a fact table, and the measures upon which we seek to report. We will create a cube shell for the Revenue cube, much as we did for the Expense cube, by taking the following actions:

1.      Right-click the Cubes folder under the FinRep_DB database.

2.      Select New Cube from the initial shortcut menu.

3.      Select Editor from the context menu that appears.

The Cube Editor appears, beginning with the Choose a Fact Table dialog, providing us with an opportunity to select a fact table for our cube.

4.      Select Revenue_fact_Query.

The Choose a Fact Table dialog appears, with our selection indicated, as shown in Illustration 18 below.

Illustration 18: The Choose a Fact Table Dialog

The list of columns in Revenue_fact_Query appears in the Details pane on the right half of the dialog. We see that the query we created within the FoodMart 2000 database appears to be fulfilling the objectives for which it is intended, acting as a "virtual" fact table, similar, in some respects, to a view at the RDBMS level.

5.      Click OK.

The Fact Table Row Count message box appears, asking if we want to count fact table rows.

6.      Click Yes.

The Cube Editor window appears, presenting the cube tree (top) and properties pane (bottom) on the left side of the window, and the fact table schema (the Schema tab view) on the right, by default. The window should appear as depicted in Illustration 19 below.

Illustration 19: The Cube Editor, with Revenue_fact_Query selected- Schema View (Compressed)

7.      Add the amount measure to the Measures folder, by dragging it from Revenue_fact_Query, and dropping it onto the folder.

The selected measure fields appear in the Measures folder (initial caps is an automatic conversion feature, as we have noted in earlier lessons). Let's adjust a couple of setpoint for our new measure.

8.      Select the new Amount measure by clicking it.

9.      Click the Advanced properties tab.

10.  Click the Data Type property.

11.  Select Currency from the dropdown selector.

12.  Click the Display Format property.

13.  Select Currency from the dropdown selector.

The new measure, together with the property settings we have put in place, appears as shown below.

Illustration 20: The Newly Added Amount Measure, with Advanced Property Settings

We will add dimensions next.

14.  Add the following dimensions to the Dimensions folder, by dragging each from Revenue_fact_Query onto the folder, in the following order:

  • time_id
  • store_id
  • product_id

The selected dimension key fields appear in the Dimensions folder (sans underscores, and with initial caps, once again) as shown below:

Illustration 21: The Dimensions Folder with Newly Added Dimensions

We will process the core cube at this stage.

Processing the Cube

Let's process our new Revenue cube, and then take a look at the results via the Preview pane on the Data tab.

1.      Select Tools from the main menu.

2.      Click Process Cube, as shown below.

Illustration 22: Select Tools -> Process Cube

NOTE: We might have alternatively chosen to right-click the cube, and to have selected Process Cube from the context menu. We could also have chosen the Process Cube button on the toolbar.

The Save the Cube dialog appears as shown below.

Illustration 23: The Save the Cube Dialog

3.      Click Yes.

The Cube: New Name dialog appears.

4.      Name the cube Fin_Rptg_Rev, as shown in Illustration 24.

Illustration 24: The Cube: New Name Dialog

5.      Click OK.

A message box (shown below) appears, warning that the cube has no aggregates designed, and asking if we want to design aggregations prior to processing the cube.

Illustration 25: Warning Message Box: No Aggregates Designed

6.      Click No.

The Process a Cube dialog appears, as shown in Illustration 26. The Full Process option is selected by default, since this is the first time the cube has been proposed for processing.

Illustration 26: The Process a Cube Dialog

7.      Click OK.

Cube processing begins, as evidenced in the status display of the Process dialog that appears. The Process dialog displays status events as the cube processes, and then displays a "Processing Completed Successfully" statement in green at the bottom of the dialog. The dialog appears as shown below at completion of processing.

Illustration 27: The Process Dialog, Displaying Status

8.      Click Close.

9.      Click the Data tab.

After a "Retrieving Data" message briefly appears, we see the actual data presented in the Preview pane, as shown below.

Illustration 28: Actual Data in the Preview Pane (Partial View)

If we switch out the Calendar.Time dimension with the others appearing in the upper pane of the data tab, we see that the dimensions, currently displaying as Product Id and Store Id, are represented by numbers (the ID fields in the fact table) within the various views we can access. This comes as no surprise, as we have used only the ID keys to create the dimensions, much as we did in the previous lesson and others, to limit the initial cube build to a central fact table.

As we learned in Part I, we must process the cube before browsing actual data anytime we are submitting a new cube for processing, alter a previously processed cube's structure, or undertake material data changes in the source data.

10.  Select File --> Exit to leave the Cube Editor for now, and to return to the Analysis Manager console.

Enhancing the Basic Cube with The Cube Editor

We will now expand our cube to include data from the individual tables associated with the dimensions we have already selected for our basic Revenue cube, as well as making other enhancements consistent with those we made to the Expense cube. These modifications will help to flesh out our cube model for its intended use for financial reporting.

1.      From the Analysis Manager console, expand the Analysis Servers folder (as appropriate), and then expand the Analysis Server name (shown as MOTHER), to see the underlying databases, as illustrated below.

Illustration 29: Expand the Analysis Server

NOTE: Factors specific to our individual environments, such as the presence of additional Analysis Servers, as well as other databases, will mean the appearance of varying objects here.

  1. Expand the FinRep_DB database to see the underlying folders.
  2. Expand the Cubes folder, as displayed in Illustration 30 below.

Illustration 30: Expand the FinRep_DB Cubes Folder

We will now extend our new Revenue cube to include data from the related dimension tables, among other enhancements. First, we will add the dimension tables we require to the existing core cube, from which we will be able to derive more useful dimension data, using the following steps.

3.      From the cube tree, click the new Fin_Rptg_Rev cube to select it.

4.      Right click Fin_Rptg_Rev cube, and select Edit from the context menu that appears.

The Cube Editor presents itself once again; we see Revenue_fact_Query in the data tab, just as we left it above.

5.      Click Insert from the Main Menu.

6.      Click Tables from the menu that appears, as shown.

Illustration 31: Insert -> Tables from the Main Menu

The Select Table dialog appears.

7.      Select the store table by clicking / highlighting it.

The Detail section of the dialog becomes populated with the columns of the store table. The Select Table dialog now appears as shown in Illustration 32.

Illustration 32: The Select Table Dialog, Store Table Selected

8.      Click the Add button (once ...).

The store table appears on the schema tab, while the Select Table dialog remains open.

9.      Add the following additional tables, after the manner of the store table insertion above:

  • time_by_day
  • product

10.  Click Close on the Select Table dialog.

We note that joins between the fact table and the dimension tables are already in place, using the id keys in each. As many of us have come to realize, this will often not be the case in a real world design effort, where the appropriate joins (perhaps not as straightforward as those found in our model) would need to be created.

As a part of making our cube user-friendlier for information consumers, we wish to substitute the dimension names that it currently presents with more intuitive dimension fields. Having browsed the data in our time_by_day table in the first half of the lesson, we know that it contains fields whose date related information would serve our users well. (To browse the data to get a look at its makeup, review the relevant steps in Part 1). We will use these fields in our model for a more user-friendly appearance in the cube we ultimately produce.

11.   In the Cube Tree, expand the existing Calendar.Time dimension by clicking the "+" sign to its immediate left.

Beginning with the Calendar.Time dimension, and proceeding to the remaining dimensions, we will substitute a user-friendlier field than the original key number, from the related dimension table.

12.  Select the Calendar Time dimension level member (the single level under the Calendar.Time dimension), as shown below:

Illustration 33: Calendar.Time Dimension as Currently Constructed

13.  Right-click the Calendar Time dimension level member.

14.  Select Delete from the context menu.

15.  Click Yes at the Confirm Level Delete dialog to delete the member.

16.  Perform steps 13 through 15 for the Store and Product dimension level members, respectively.

We should now have each dimension in place, with no levels under each of the Calendar.Time, Store and Product dimensions. We will add dimension levels from the dimension tables in the next steps.

17.  Drag the following fields from the time_by_day table over to drop onto the Calendar.Time dimension in the cube tree, in the order shown:

  • the_year
  • quarter
  • the_month
  • the_date

18.  Right-click on each of the above newly added dimension levels, and rename each, respectively, to the following:

  • Calendar Year
  • Calendar Quarter
  • Calendar Month
  • Date

Dragging the fields onto the folder in the order above, then changing their names, will result in their alignment as shown below:

Illustration 34: Calendar.Time Dimension with New Member Additions / Modifications

Now we'll make a few changes to the Calendar.Time dimension to more precisely establish it as a time dimension.

19.  Click the Calendar.Time dimension to highlight / select it.

20.  Click the Advanced tab of the Properties pane.

21.  Change the Type field for the Calendar.Time dimension to Time, as shown below.

Illustration 35: The Calendar.Time Dimension Advanced Properties

22.  Click the Calendar Year level to highlight / select it.

23.  Click the Advanced tab of the Properties pane.

24.  Change the Level Type field for the Calendar Year level to Years, as shown in the following illustration.

Illustration 36: The Calendar.Time Dimension, Calendar Year Level Advanced Properties

25.  Click the Calendar Quarter level to highlight / select it.

26.  Click the Advanced tab of the Properties pane.

27.  Change the Level Type field for the Calendar Quarter level to Quarters, as shown in the Illustration 37 below.

Illustration 37: The Calendar.Time Dimension, Calendar Quarter Level Advanced Properties

28.  Click the Calendar Month level to highlight / select it.

29.  Click the Advanced tab of the Properties pane.

30.  Change the Level Type field for the Calendar Month level to Months, as shown in the following illustration.

Illustration 38: The Calendar.Time Dimension, Calendar Month Level Advanced Properties

31.  Click the Date level to highlight / select it.

32.  Click the Advanced tab of the Properties pane.

33.  Change the Level Type field for the Date level to Days, as shown in the following illustration.

Illustration 39: The Calendar.Time Dimension Date Level Advanced Properties

I typically style the lowest level in the Date hierarchy as Date; calendar, fiscal, and perhaps other date hierarchies would typically share Date as a common lowest level, and thus I give it a name that will serve all equally well.

Having handled the Calendar.Time member properties that will be created in our cube, let's do something similar for the Store dimension. The Store dimension consists of member naming that makes intuitive sense, as we will see upon our initial cube generation, so we will simply swap the name for the id number that previously occupied the Store member level.

34.  Right-click the store_id dimension.

35.  Select Rename from the context menu, and type in Store.

36.  Click the Advanced tab in the Properties pane.

37.  Select the All Caption property.

38.  Change the All Caption property to read "All Stores."

39.  Drag the store_id field from the store table over to drop onto the Store dimension in the cube tree.

40.  Right-click the store_id level that appears.

41.  Select Rename from the context menu, and type in Store.

42.  Click the Basic tab in the Properties pane.

43.  Select the Member Name Column property.

44.  Click the ellipses button ("..").

The Select Column dialog appears.

45.  Select the store_name column by clicking.

46.  Click OK.

47.  Press the Enter key.

The Properties pane for the new Store level, with the modification we have put into place, should appear as shown below:

Illustration 40: The Modified Properties, Store Level Member of the Store Dimension

At this stage, let's focus upon the Product dimension. As we determined the case to be for the Store dimension, member naming for the Product dimension will be quite straightforward; we will simply swap the already present Product name for the Product ID that appears in the Member Name Column property.

48.  Right-click the product_id dimension.

49.  Select Rename from the context menu, and change the label to Product.

50.  Click the Advanced tab in the Properties pane.

51.  Select the All Caption property.

52.  Change the All Caption property to read "All Products."

53.  Drag the product_id field from the product table over to drop onto the Product dimension in the cube tree.

54.  Right-click the Product_id level that appears.

55.  Select Rename from the context menu, and type in Product.

56.  Click the Basic tab in the Properties pane.

57.  Select the Member Name Column property.

58.  Click the ellipses button ("..").

The Select Column dialog appears.

59.  Select the product_name column by clicking, as shown in Illustration 41 below.

Illustration 41: Select product_name for the Member Name Column Property

60.  Click OK.

The Properties pane for the new Product level, with the modification we have enacted, should appear as shown below:

Illustration 42: The Modified Properties, Product Level Member of the Category Dimension

Finally, one step remains to make the Revenue cube identical to the Expense cube in all significant respects: the addition of an Account dimension. Next, we'll add the Account dimension, using the Dimension Wizard as we did for the Expense cube, as it allows the creation of a parent-child dimension. Creating the dimension a second time within this lesson will also afford us some more practice with the process.

61.  Click Insert in the top Menu.

62.  Select Dimension, then select New from the cascading menu, as shown in Illustration 43 below.

Illustration 43: Select Insert --> Dimension --> New from the Top Menu

The Dimension Wizard welcome dialog appears, as shown below.

Illustration 44: The Dimension Wizard Welcome Dialog

63.  Click Next.

64.  Choose Parent-Child at the Choose How You Want to Create the Dimension dialog that appears next, as shown in Illustration 45 below.

Illustration 45: Selecting the Parent-Child Radio Button

65.  Click Next.

The Select the Dimension Table dialog appears.

66.  Select the account table.

The Details pane is populated with the column names for the selected account table, as depicted below.

Illustration 46: The account Table Columns in the Detail Pane

67.  Click Next.

The Select the Columns that Define the Parent-Child Data Hierarchy dialog appears. We will "fill in" the selector boxes in the following steps:

68.  Select account_id as the Member Key.

We will use account_id as the Index that clearly identifies our member accounts.

69.  Select account_parent as the Parent Key.

The account parent is the account to which the account "rolls" in summary fashion, and is identified in the account table to provide for hierarchical design just such as this. The parent key acts as the "pointer" to guide rollups of data, as we shall see in short order.

70.  Select account_description as the Member Name.

The dialog appears with our selections below.

Illustration 47: The Completed Select the Columns that Define the Parent-Child Data Hierarchy Dialog

71.  Click Next.

72.  Click Next again, to skip the Select Advanced Options dialog.

The Finish the Dimension Wizard dialog appears.

73.  Type the word Account into the Dimension Name box.

74.  Uncheck the checkbox (click it once) for Share This Dimension with Other Cubes.

The dialog appears (with expanded Preview), with our selections as shown below.

Illustration 48: The Completed Finish the Dimension Wizard Dialog

The Preview provides a scrollable view of the hierarchy that our selections will generate.

75.  Click Finish to close the Dimension Wizard.

The dialog disappears, leaving the view of the Schema tab as depicted below, after arrangement:

Illustration 49: Cube Editor - Schema View

Now let's take a couple of small steps to refine the new Account dimension before processing our enhanced Revenue cube.

At this point, we will give the Account dimension members in our cube meaningful names - names that make sense to accounting / finance knowledge workers. As we mentioned in Part 1, while many accountants and / or their counterparts in finance know the chart of accounts by account codes, from daily usage and coding conventions, other information consumers will need to know the account names. To strike a useful compromise, let's give them both, by using the Member Name Column property of our new Account dimension.

76.  Click the Account dimension, to select it in the cube tree.

77.  Click the Advanced tab in the Properties pane.

78.  Modify the All Caption to read All Accounts.

79.  Click the Account_Id level that appears below the Account dimension to select it.

80.  Select the Basic tab, then the Name property.

81.  Change the Name property field to Account.

82.  Select the Member Name Column property.

83.  Type the following expression into the property field:

Cstr("account"."account_id")+ '   ' +"account"."account_description"

As we discussed in Part 1, we are simply concatenating the Account ID and the Account Name, either or both of which we have determined might be helpful to information consumers in the use of the cube we are designing. The CStr function in the expression, as we noted, allows us to combine different data types in the concatenation, converting the Account ID (an integer data type) to a string to make it compatible with the string data type of the Account Description. This conversion must be made or attempts at cube processing will result in failure.

A discussion of MDX (upon which the expression above is based), and its use of external functions, can be found in Part 1.

84.  Press the Enter key.

The Properties pane, with the modifications, should appear as shown below:

Illustration 50: The Modified Properties, Account Level Member of the Account Dimension

As we noted in the first half of this article, every dimension level comes equipped with member key and member name properties, each of which can be easily modified by the developer. Please see Part 1 and preceding lessons for more details on member properties.

I like to check my calculations before kicking off cube processing whenever possible, to ensure that I have not made any blunders that would simply waste time with a failed processing event. A quick way to do this in the current example is to take the following steps:

85.  Click the Data tab.

We are duly warned, both in the fleeting message that appears when we initially select the tab, as well as with the static warning message at the bottom of the Cube Editor, that we are viewing sample data, and that the cube has not been processed. The sample data provided, however, does provide us a prospective view of the account names, which acts to confirm the accuracy of our calculation in this case. The Data tab information should resemble that presented in Illustration 51 below.

Illustration 51: A Quick Visit to the Data Tab Confirms the Correct Account Name Display (Compressed View)

We are now ready to process the cube, and to ensure we are progressing toward our objectives with the overall Financial Reporting cube project. Involved cube designs often require several such "updates;" the ease with which Analysis Services facilitates processing a cube is certainly a pronounced (and appreciated) benefit.

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

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

88.  Click No to bypass the Storage Wizard.

89.  Ensuring that the Full Process method is selected on the Select the Processing Method dialog, click OK to begin processing.

The Process status dialog appears, and details the progression of the processing steps as they are accomplished. The end result is the green Processing Completed Successfully message at the bottom of the dialog, as we have seen earlier.

90.  Click Close to dismiss the Process status dialog.

91.  Click the Data tab to view the results so far.

92.  Ensure that the Account dimension is in the row axis of the data tab.

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

94.  Expand the 3000 Total Sales level now appearing within the expanded Net Income level (to its right), as shown in Illustration 52.

Illustration 52: The Fin_Rptg Cube Layout - Data Tab View Expanded (Compressed View)

We see that our initial objective, to create a core Revenue cube to complement the Expense cube we created in Part I, appears to have been accomplished.

95.  After examining the expanded Data tab view, click File --> Exit from the top menu to close the Cube Editor.

We have achieved the design and development of our second core cube for financial reporting, having set up the revenue side of the end model, to complement the Expense cube we created in Part I, and to allow for analysis of the components of Sales, Cost of Goods Sold, and Net Sales. Our next objective is to "marry" the two cubes to complete the creation of the Financial Reporting cube.

The most straightforward way to combine the data within the cubes, now that their structures are in alignment from the perspective of dimensions that will prove useful in our objective, will be to bring the data from the smaller (in our case the Revenue cube) to the larger (the Expense cube). As the Revenue cube occupies the least number of accounts and levels in the structure (it is isolated, effectively, to the 3000-series accounts), we will entrain the data in the Revenue cube into the Expense cube to derive an integrated cube for Income Statement Financial Reporting.

Completing the Financial Reporting Cube

If we redirect our focus at this point to the Expense cube, we can see readily that the 3000-series accounts are unpopulated. Having built both the Expense and Revenue cubes, we realize that no single fact table existed that housed the data that comprises both cubes' accounts (the main reason we needed two cubes in the first place). To illustrate this once again, and to explore a means of integrating the data from both cubes, we will turn to the Expense cube via the Cube Editor.

1.      Within the Analysis Manager console, and from the cube tree, click the new Fin_Rptg cube (our Expense cube) to select it.

2.      Right click the Fin_Rptg cube, and select Edit from the context menu that appears.

The Cube Editor opens. We now need to adjust some settings to allow us to entrain the data from the Revenue cube, Fin_Rptg_Rev.

We will recall that, in our visit to the FoodMart 2000 database earlier in the lesson, we made some alterations to the Custom Members fields in the account table. Specifically, we replaced one expression that already existed for Account 3100, Gross Profit, and subsequently added an expression into the field directly below it. We placed these expressions in a column aptly named Custom Members.

While we won't get into the details of MDX at this point, suffice it to say that the Custom Members column acts to house expressions that enable us to entrain the values they specify from another cube. They act to redirect the cube from the fact table as the source of the data for the member with which they are associated, and perform as "pointers" to the alternative source, in our case the Revenue cube.

We have determined in earlier sections why we needed to look beyond the expense_fact table to achieve our objectives of entraining revenue data. Our lesson involves the combination of the Expense and Revenue cubes we have created in this lesson, because the Expense cube houses operating expense data, while the Revenue cube contains Sales / Revenues data, as well as Cost of Goods Sold data. The primary objective in creating our ultimate Financial Reporting cube is to provide information consumers a means of performing Income Statement reporting, based upon the data as it exists in the database, much as we would be expected to do in many real-world scenarios.

Each of the two expressions we added contains a LookupCube function; the two arguments laid out in the function specify the targeted cube (Fin_Rptg_Rev), as well as directing the values in the cube that we wish to entrain from the targeted cube. (For more information on the LookupCube function, as well as upon MDX in general, see the MSSQL Server 2000 Books Online or other comparable sources.)

The expressions are, as we are aware, already in place. Our next action will be to "activate" those expressions by enabling Custom Members for the Fin_Rptg cube. We do this by taking the following steps:

  1. Within the Cube Editor, select the Account level immediately under the Account dimension in the cube tree.
  2. Click the Advanced Properties tab.
  3. Select the Custom Members property.
  4. Click the ellipsis (..) button to the right of the property box.

The Define Custom Member Column dialog appears.

  1. Click to place a check in the box to the left of Enable Custom Members.
  2. Click-select the Use an Existing Column radio button.
  3. Using the dropdown Existing Column selector, choose Custom Members.

The Define Custom Member Column dialog appears, with our new settings, as shown in Illustration 53.

Illustration 53: The Define Custom Member Column Dialog with Settings

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.

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved