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

February 17, 2003

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:

4. 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.)
5. 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.
6. Right-click the FoodMartFinRep data source, and select Edit from the context menu that appears.
7. Click the Provider tab, and select Microsoft OLE DB Provider for ODBC Drivers, as shown in Illustration 16 below.

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

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

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

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.

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