About the Series ...

This is the seventh article of the series, Introduction to MSSQL Server 2000 Analysis Services. As 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.

Introduction

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, in this instance 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 other dimensions. 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, our last article, 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 article, we will build a 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, and then we will discuss some of the challenges that accompany cube design for financial reporting.

In this two-part article, we will:

• Explore some of the challenges that accompany cube design for financial reporting, both from the outset of the lesson and at appropriate points as we progress the design and development of the Financial Reporting Cube structure;
• Create a core Financial Reporting Cube, focusing initially on the expense side of the Income / Profit and Loss Statement (in Part I), then integrating the revenue data into the design (Part II);
• Explore dimension and dimension level member properties, and practice nuances involved in leveraging their setpoints to add value and user-friendly utility to our design;
• Discuss rollup and aggregation concepts and considerations;
• Introduce methods of sign control within our calculations and presentation;
• Discuss and illustrate the provision for the storage of "footnote" and / or "information only" data for specific reporting needs;
• Address formatting and other considerations that arise as we create a cube to meet the needs of our information consumers.

Introducing the Financial Reporting Cube

The cubes with which we have worked in previous lessons have been somewhat specific, and simply constructed. They have also been built largely within function-oriented constraints, and have not generally focused upon data relating to functions or operations outside the relatively strict constraints of the specific purpose or mission detailed in the lesson scenario; for example, the warehouse cube chiefly consisted of data that might be of interest to personnel working within warehouse operations, while the sales cube dealt more with the sales function, including the stores, products and other dimensions from a sales perspective. While dimensions were shared to some extent between the two cubes, they were only used within the context of those dimensions within each functional organization. Within these limited perspectives, they allowed the manipulation of data in a way that was useful to the respective information consumers, from the standpoint of performing their daily functions and making decisions within the realm of their associated operating units.

As a recovering CPA and a business intelligence consultant / data architect, I typically come into contact with complex cube structures in working with my clients from day to day; indeed, most of us experience OLAP reporting in a world of complex table designs, multiple data sources, and a host of other complicating factors. In this lesson, we will take a look at some scenarios that draw closer to the real world, and will explore a few of the options that MSSQL Server 2000 Analysis Services offers us in handling these requirements with sophistication.

We will examine a cube that contains numerous high-level elements of what one might expect to find in standard financial reporting. While the requirements of financial reporting span a wide range of possibilities and nuances, based upon diverse industry, regulatory and other drivers, we find a few common concepts in most systems: Balance Sheets, Income (or Profit and Loss) Statements, accounts receivable and accounts payable reports, and others are probably familiar to most of us. We will take a look at the components of a cube that support such statements, focusing largely on the Income / Profit & Loss Statement, and introduce new concepts that apply, while re-tracing many of the common steps we transition in building any OLAP data source, as we construct a sample finance cube for a hypothetical financial reporting department.

In this article, Part I, we will perform general setup of the core cube, focusing first on the expense side of the Income Statement. The sample database that we will use does not include a "financial statements" fact table, per se, as it focuses more on the revenues side of the equation to illustrate the construction of the Sales cube that it presents as a simple model for tutorial and other purposes. This offers us an opportunity to demonstrate the construction of a cube in a way that reflects some basic realities that we encounter in the real world - together with a scenario where the components that we want to analyze are scattered in various tables throughout the database. We continue this theme into Part II, where we will examine ways to pull the sales data into our new financial reporting cube, and show how we can then integrate the expense and revenue data to match revenues with their related expenses to illustrate the production of meaningful statements to reflect the results of company operations.

Designing and Building the Financial Reporting Cube

Financial reporting relies heavily upon the summarization and aggregation of the contents of accounts, "containers" that hold the transactional details of an organization's daily operations. The details that reside in a given account are often summarized in hierarchical fashion in a "rollup" or parent account, but, alternatively, may be netted against rollup or peer accounts, or handled in an isolated fashion, among other possibilities. The seemingly variable manner with which these combinations, or the absence of same, occur is understandably confusing to many. For that matter, the mere assignment of debit and credit transactions to accounts, while "rational and systematic," perhaps, to an accountant, often seems arbitrary, at best, to those not familiar with accounting concepts. Suffice it to say that these and other such nuances are complicating factors in a financial reporting cube.

We will create a basic Financial Reporting Cube in much the same manner as we have created the numerous other cubes we have built within this series. We will then introduce the concepts to which we have alluded in the discussion thus far, where we can see practical applications of these ideas in action. As we learned in Lesson One, and have practiced in the creation of subsequent cubes in later lessons, we first create an OLAP database to organize cubes, roles, data sources, shared dimensions, and other objects. We will call our OLAP database FinRepCube, setting it up inside Analysis Manager, then linking a data source to our database before we start to construct our cube, as we have done previously.

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

4. Click New Database.

The Database dialog box appears.

5. Fill in the Database Name - let's make it FinRep_DB - along with a description that might be of value to a user or developer down the road.

Let's simply add "Financial Reporting Database," here - the description is optional, of course.

The dialog appears as below.

6. Click OK.

FinRep_DB appears at a point below the existing FoodMart 2000 database.

7. Expand FinRep_DB's database / cube icon by clicking in the plus (+) sign to the left of the icon.

Note that FinRep_DB appears in the left-side tree, complete with predefined, empty folders for object storage later, as shown in Illustration 3 below.

We now need to connect to the data source from which we wish to draw values.

8. Right click the folder within FinRep_DB called Data Sources, and on the context menu, select New Data Source.

The Data Link Properties dialog box appears, with its Provider, Connection, Advanced and All tabs, as shown in Illustration 4 below. Beginning with the Provider tab (where the dialog opens by default):

9. Select the Microsoft Jet 4.0 OLE DB Provider (the native MS Access OLE DB provider).

Note: For more on this, review the on-line documentation for OLE DB Providers, and data sources in general.

10. Click Next.

The focus moves to the Connection tab.

11. Select the FoodMart 2000 database in Box 1.

FoodMart 2000.mdb is located, by default (as we have noted) in the [Install Directory]:\Program Files\Microsoft Analysis Services\Samples directory; Click on the ellipses (...) button, to navigate to the .mdb as shown in Illustration 5 below.

12. Click Open, returning to the Connection tab, as we see below:

13. Insert User Name and Password information, as appropriate (the illustration displays my default settings).
14. Click the Test Connection button in the bottom right hand corner of the Connection tab.

We should get a verification dialog confirming that the source has been established in our definition (as shown in Illustration 7).

15. Click OK, and the Microsoft Data Link verification dialog box closes.

We will leave all the other Data Link Properties at their default setpoints for now.

16. Click OK on the Data Link Properties dialog.

The Data Link Properties dialog closes, and we can see that the new source appears under the Data Sources folder in the tree area, on the left side of the Management Console, displaying the actual file name, as shown below.

As we mentioned in Lesson One, and at numerous other points in the series, a simple "rename" capability is not available, so we will have to be creative here; a right-mouse click on our new data source allows a Copy action, which will serve as a workaround for renaming the object in question, to be a bit more concise.

17. Right-click the new data source.
18. Click Copy.
19. Highlight the Data Sources folder.
20. Select Paste from the popup menu.

This causes Analysis Services to indicate that a duplicate has been detected, and to prompt us for a unique name. We will respond to the new name request with FoodMartFinRep, using the dialog box that appears (as shown below in Illustration 9).

21. Type FoodMartFinRep into the Name box of the Duplicate Name dialog.

Once we click OK, the Duplicate Name dialog disappears, and the newly named data source appears under the Data Sources folder.

22. Click OK to close the Duplicate Name dialog.

All that remains is to delete the original data source, from which we cloned FoodMartFinRep.

23. Right-click the original data source, and select Delete on the popup menu
24. Click the Yes button, to confirm the deletion.

Our tree should now resemble that shown in Illustration 10.

In Lesson One, we used the Cube Wizard, together with the subsidiary specialized wizards (including the Dimension Wizard), as called by the Cube Wizard, to rapidly create a simple cube to explore the various aspects and steps of the process from a relatively high level. In this lesson, we will create a core cube from the fact table upon which we will base the rest of the cube design and construction, together with several new components and settings that illustrate the objectives of our lesson.

Our preparation for the lesson (and for the creation of any cube) is complete. We now have an OLAP database in place, linked to a valid data source (the sample FoodMart 2000 database). Next, we will initialize the Cube Editor, and begin creating our cube.

Creating the Cube - Expense Focus

The Cube Editor will first guide us through the selection of our fact table, which houses the measures upon which we seek to report. We will create our basic financial reporting cube shell by taking the following actions:

1.      Right-click the new Cubes folder under the FinRep_DB database we created above.

2.      Select New Cube from the initial shortcut menu.

3.      Click Editor, as shown in the illustration below.

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, as depicted in Illustration 12 below.

4.      Select the expense_fact table.

The list of columns in the expense_fact table appears in the Details pane on the right half of the dialog. As most of us realize, expenses, or costs, are not the only components of an Income / Profit & Loss Statement. Our plan is to add in the revenue / sales and other necessary information after we create our basic cube. We will discuss methods of accomplishing this at a later stage in our cube design and creation.

5.      Click OK.

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

6.      Click Yes.

The Cube Editor window appears, showing 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 14 below.

Click for larger image Illustration 14: The Cube Editor, with the Expense_Fact Table Schema View (Compressed)

7.      Add the following measure to the Measures folder, by dragging it from the expense_fact table, and dropping it onto the folder:

• amount

The selected measure field appears in the Measures folder (initial caps is an automatic conversion feature, as we have noted in earlier lessons) as shown below:

A cube must contain a minimum of one measure from the designated fact table; in addition, it must also contain at least one dimension that is associated with a key column in the table.

We will add dimensions next.

8.      Add the following dimensions to the Dimensions folder, by dragging each from the expense_fact table onto the folder, in the following order:

• time_id
• store_id
• category_id

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

The dimensions that we have added are private dimensions, meaning that they belong to this cube only. Now let's make some adjustments to the measure and dimension properties, to make them more useful to our model, as well as to refresh our memories, in some cases, of member properties attributes.

9.      Click the Time Id dimension to select it in the cube tree.

10.  Click Properties beneath the tree pane to display the properties of the Time Id dimension.

11.  Click the Basic tab in the Properties pane.

12.  Rename the dimension to Calendar.Time.

The Basic tab of the Properties pane for the Calendar.Time dimension appears as shown below:

13.  Click the Time Id level underneath the Calendar.Time dimension to select it in the cube tree.

14.  Ensure the Basic tab in the Properties pane is selected.

15.  Rename the level to Calendar Time (note that the delimiter "." is not allowed here).

The Basic tab of the Properties pane for the single Time level appears as shown below:

16.  Click the Calendar.Time dimension again, to select it in the cube tree.

17.  Click the Advanced tab in the Properties pane.

18.  Modify the All Caption to read All Calendar Time.

The Advanced tab of the Properties pane for the Calendar.Time dimension appears as shown below:

19.  Complete steps 9 through 18 above, for each of the other two dimensions, Store Id, and Category Id, to rename the dimensions (and their associated single underlying levels) to Store and Category, respectively, on the associated Properties panes, Basic tab.

20.  Modify the All Caption fields, on the Advanced tab in the Properties panes for Store and Category, to read All Stores, and All Categories, respectively.

At this juncture, the cube tree should appear as shown below:

Keep in mind that the dimensions we have defined are, again, private dimensions. Because private dimensions (and the associated dimension levels) are defined for the individual cube, they are created / modified, as we have seen above, using the Cube Editor. We must be "inside" the cube with the Editor to readily see the private dimensions exposed in the cube tree.

We will process the cube at this stage.

Processing the Cube

Let's process our new 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.

NOTE: We might have alternatively chosen the Process Cube button depicted in Illustration 22. Another means is right-clicking the cube, and selected Process Cube from the context menu.

The Save the Cube dialog appears as shown below.

3.      Click Yes.

The Cube: New Name dialog appears.

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

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.

6.      Click No.

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

7.      Click OK.

Cube processing begins, as evidenced in the status display of the Process dialog shown below:

The Process dialog displays status events within the cube processing evolution, and then displays a "Processing Completed Successfully" statement in green at the bottom of the dialog when processing is finished.

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.

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, Calendar.Time and Store, are represented by numbers, (the ID fields in the fact table), within the various views we can access. This is expected, as we have used only the ID keys to create the dimensions, much as we have in past lessons, to limit the initial cube build to a central fact table.

We must process the cube before browsing actual data, whenever we build a new cube and design its storage options and aggregations, or when we change a cube's structure (measures, dimensions, and so on), where we save the changes to the cube. We must also process a cube whenever we change the structure of a shared dimension (using the Dimension Wizard) used in the cube. If data in the data source (i.e. data warehouse) supporting the cube has been added or changed, processing is appropriate to furnish updated, accurate results when browsing the cube.

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 minimal cube, as well as making other enhancements to flesh out our cube model for financial reporting.

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

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.

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

We will now extend our cube to include data from the respective dimension tables, among other enhancements. First, we will add the dimension tables we require to the existing core cube, and derive more useful dimension data thereby, using the following steps.

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

4.      Right click Fin_Rptg cube, and select Edit, as shown below, from the context menu that appears.

The Cube Editor appears to display the initial expense_fact table presented 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.

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

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
• category

10.  Click Close on the Select Table dialog.

Next, we'll add the Account dimension, but we will use the Dimension Wizard for this, as it allows the creation of a parent-child dimension.

11.  Click Insert in the top Menu.

12.  Select Dimension, and then select New from the cascading menu, as shown in Illustration 34 below.

The Dimension Wizard welcome dialog appears, as shown below.

13.  Click Next.

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

15.  Click Next.

The Select the Dimension Table dialog appears.

16.  Select the account table.

The Details pane is populated with the column names for the selected account table.

17.  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:

18.  Select account_id as the member key.

We will use account_id as the index that uniquely defines our member accounts.

19.  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 later.

20.  Select account_description as the member name.

The dialog appears with our selections below.

21.  Click Next.

22.  Click Next again to skip the Select Advanced Options dialog.

The Finish the Dimension Wizard dialog appears.

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

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

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

25.  Click Finish to close the Dimension Wizard.

The dialog disappears, leaving the schema view as depicted below, after arrangement:

Note that joins between the fact table and the dimension tables are already in place, using the id keys in each. 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.

For a practical discussion surrounding, and an examination of the uses of, parent-child dimensions, see Article Four of our series, Parent-Child Dimensions.

As a part of making our cube more user-friendly for information consumers, we wish to substitute the dimension names that it currently presents with more intuitive dimension fields. Let's begin with the time_by_day table.

In a case where we may not be familiar with the nature of the data or the characteristics of the fields in a given table, such as our time_by_day table, we can browse the data to get a look at its makeup.

26.  Click the upper portion (where the name appears) of the time_by_day table to select the table.

27.  Right-click and select Browse Data from the context menu, as shown in Illustration 40.

The Browse Data Viewer appears, as shown in Illustration 41 below.

28.  Close the viewer after reviewing the data columns and the formats of their contents.

29.   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 more user-friendly field than the original key number, from the related dimension table.

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

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

32.  Select Delete from the context menu.

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

34.  Perform steps 33 through 35 for the Store and Category dimension level members, respectively.

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

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

36.  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:

At this point, we will give the Account dimension members in our cube meaningful names - names that make sense to accounting / finance knowledge workers. 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.

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

38.  Click the Advanced tab in the Properties pane.

39.  Modify the All Caption to read All Accounts.

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

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

42.  Change the Name to Account.

43.  Select the Member Name Column property.

44.  Type the following expression into the property field:

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

Note that we are simply concatenating the Account ID (an Integer) 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 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. Cube processing will fail if we do not make this conversion.

While MDX (which underlies the expressions we use in working with member properties) has no conversion functions to meet the immediate need, MDX allows us to access external functions; CStr is one of many VBA functions automatically included in MDX. (A list of available external functions can be obtained from the SQL Server 2000 Books Online, which references resources for more details within the MSDN Library and other documentation.

45.  Press the Enter key.

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

As might be expected, every dimension level comes equipped with member key and member name properties, each of which can be easily modified by the developer. More than one column name can be used, as we have seen above, to create the value for the Name Column properties. As long as the expression returns a string or a number, practically any SQL expression that is acceptable to the relational data source will work in the property value.

Having handled the Account names that will be created in our cube, let's do something similar for the Store dimension. The Category 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 Category member level.

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

47.  Right-click the store_id level that appears.

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

49.  Click the Basic tab in the Properties pane.

50.  Select the Member Name Column property.

51.  Type the following into the property field:

CStr("store"."store_number") + ' ' + "store"."store_name"

For an explanation of the CStr function, with which we have prefaced our expression above, see the immediately preceding discussion of its use within the Account Member Name Column property.

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

52.  Press the Enter key.

53.  Drag the category_id field from the category table over to drop onto the Category dimension in the cube tree.

54.  Right-click the category_id level that appears.

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

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 category description column by clicking, as shown in Illustration 46 below.

60.  Click OK.

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

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

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

62.  Click the Advanced tab of the Properties pane.

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

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

65.  Click the Advanced tab of the Properties pane.

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

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

68.  Click the Advanced tab of the Properties pane.

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

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

71.  Click the Advanced tab of the Properties pane.

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

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

74.  Click the Advanced tab of the Properties pane.

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

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.

We are now ready to process the cube to check results, so as to ensure we are progressing toward our objectives. 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.

76.  Click the Process Cube button on the top toolbar (See Illustration 22 above).

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

78.  Click No to bypass the Storage Wizard.

79.  Ensuring that the Full Process method is selected on the Select the Processing Method dialog (the default in our example at this stage), 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 depicted in Illustration 53 below.

80.  Click Close to dismiss the Process status dialog.

81.  Click the Data tab to view the results so far, shown in the illustration below.

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

83.  Expand the 4000 Total Expense level now appearing within the expanded Net Income level (to its right), as shown in Illustration 55.

Click for larger image Illustration 55: The Fin_Rptg Cube Layout - Data Tab View Expanded (Compressed View)

We see that our initial objective, to create a core cube housing the expense portion of the Income / Profit and Loss Statement, appears to have been accomplished. We also note that the Assets, Liabilities and, more significant to our upcoming focus, Net Sales, levels are unpopulated, but are included in the model for our examination in Part II of this lesson.

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

85.  In the Analysis Manager console, select Console --> Exit to leave Analysis Services.

We have achieved the design and development of our initial core cube for financial reporting, having set up the expense side of the cube to allow for analysis of the components of operational expenses. We will develop our initial core further in Part II of this lesson, then we'll bring in the sales / revenue and other information to make the Profit / Loss position of the organization complete for basic financial reporting purposes. We'll populate the Net Sales elements after discussing the options we have available within Analysis Services for doing so.

Next in Our Series ...

In this, the first segment of a two-part article, we introduced an objective that integrates many concepts we have examined individually in preceding lessons: To build a simple cube to meet some illustrative business requirements of a hypothetical corporate financial reporting function. We expanded upon many of the concepts we have considered throughout the series, focusing the kindred methods and procedures on our objective to create a Financial Reporting Cube. We built a core cube, based upon the expense fact table provided within the FoodMart 2000 sample database, to begin our efforts, discussing the realities of multiple fact tables in data sources that we often encounter in the business environment. We also explored some of the challenges that accompany cube design for financial reporting, both from the outset of the lesson, and at appropriate points through which we progressed the design and development of the Financial Reporting Cube structure.

As a part of my commitment to making lessons standalone, where members of the audience can complete each without being hamstrung by the absence of objects and structures created in earlier lessons that they did not see fit to complete, we created the basic Financial Reporting Cube (expense portion) from scratch; We created an OLAP database, to which we linked a data source, to support the creation of our new cube. We then began the cube design and development process around a single fact table, focusing on various concepts contained within the effective use of the Cube Editor, including expanding the utility of the cube through the use of various member properties setpoints. Our efforts also included the creation of a parent-child dimension, using the Dimension Wizard for this part of our project.

In our next article, Custom Cubes: Financial Reporting Part II, we will continue the design and construction of the Financial Reporting Cube, focusing our attention initially on the refinement of our Account dimension. (We will, therefore, use the data source and other objects that we have created in this lesson in Part II, both to save time and to provide continuity). Next, we will discuss options for the entrainment of the revenue data that resides in the separate Sales fact tables of the sample data source to complete the design of a cube that supports the financial reporting needs of the organization. We will introduce rollup and aggregation concepts and considerations, as well as methods of accomplishing sign control within our calculations and presentation. Moreover, we will address formatting, and other considerations that arise as we create a cube to meet the needs of our information consumers.

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

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