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.
- Start Analysis Manager.
- Expand the Analysis Servers folder by clicking
the "+" sign to its immediate left.
- Expand the Analysis Server name (mine is MOTHER,
in this example), to see the tree shown below:
Illustration 15: Expand the Analysis Server
- 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.)
- Expand the Data Sources folder to
expose the FoodMartFinRep data source we created in the first half
of this lesson,
clicking the "+" sign to its immediate left.
- Right-click the FoodMartFinRep
data source, and select Edit from the context menu that appears.
- Click the Provider tab, and select
Microsoft OLE DB Provider for ODBC Drivers, as shown in Illustration
Illustration 16: Select Provider
- Click the Connection tab, and select FoodMart
2000 as data source name from the dropdown selector, as shown
Illustration 17: Select FoodMart 2000 Data Source Name
- 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.
- 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
Right-click the Cubes
folder under the FinRep_DB database.
Select New Cube from the
initial shortcut menu.
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.
The Choose a Fact
Table dialog appears, with our selection indicated, as shown in Illustration
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.
The Fact Table Row
Count message box appears, asking if we want to count fact table rows.
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
Illustration 19: The Cube Editor, with Revenue_fact_Query selected- Schema View (Compressed)
Add the amount measure
to the Measures folder, by dragging it from Revenue_fact_Query, and dropping it onto the folder.