We are now ready to
test the query to ascertain that it produces the correct results.
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
Select File ->
Close from the top menu.
We are asked if we wish
to save the query.
The Save As dialog
Type revenue_fact_Query into
the dialog box, as shown below.
Illustration 13: Save the Query as 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.
Click the Tables icon in
the Objects pane to display the FoodMart 2000 database tables.
the account table to open it in Table view.
window to be able to see the table completely.
width of the right-most column, labeled Custom Members.
observe the following expression, containing a LookupCube function, in
the Custom Members field for Account 3100:
(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 ...).
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
[Account].[All Accounts].[5000 Net Income].[3000 Net Sales].[3100 Gross
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+",
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
Select File ->
Exit to close and leave MS Access,
saving modifications if prompted.