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

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.

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

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.

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.

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:

LookUpCube("[Fin_Rptg_Rev]","(Measures.[Amount],
[Account].[All Accounts].[5000   Net Income].[3000   Net Sales].[3100   Gross 
	Sales],"+calendar.time.currentmember.UniqueName+",
	"+ 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):

LookUpCube("[Fin_Rptg_Rev]","(Measures.[Amount],
[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.