We need to create a query that will provide the data we need in a
layout that will be sufficient to provide a basis for creation of our cubes.
Queries can be selected in the Cube Editor just as tables can, so we do not
need to create a special table for this purpose. In addition, other benefits
of using the query approach are that 1) the existing database schema remains
intact and 2) the database suffers no appreciable increase in size; both of
these considerations are meaningful in many client environments where altering
the source database tables is undesirable and space is a constantly monitored statistic.
- Select Queries in the Objects pane on
the left side of the Database view.
The queries that exist in the sample database appear as shown in Illustration
5: The Queries View of the FoodMart 2000 Database
- Click the New button in the toolbar atop the
- Select Design View.
The New Query dialog appears as shown
6: The New Query Dialog, Design View
- Click OK.
The Design View appears for the New
Query, with the Show Table dialog having the focus, and displaying
the tables available for selection within FoodMart, as depicted below.
Illustration 7: Show Table Dialog
- Click Close to dismiss the Show Table
We will be creating a UNION ALL query to handle the following
concurrent objectives (explained above):
Selection of the fact and
dimensional data that we will need as a basis for the development of the Revenue
Separation of the assigned store_sales
and store_cost into discreet rows that simultaneously retain all other
associated data fields that we require;
Assignment of the term "amount"
to the store_sales and store_cost measures;
Assignment of the appropriate account_id's,
3100 (Gross Sales) and 3200 (Cost of Goods Sold),
to the store_sales and store_cost measures, respectively;
Assignment of the name "account_id"
to the newly added store_sales and store_cost account_id
We will need to type the query directly, from Design Query: SQL
View in MS Access.
- Click the SQL View button, shown in Illustration
8 below, in the upper left corner of the toolbar of Design View.
Illustration 8: SQL View Toolbar Button
The Select Query (SQL View) dialog appears, as shown below.
Illustration 9: Select Query: SQL View
- Type the following query into the Select Query
(SQL View) dialog:
SELECT product_id, time_id, store_id, store_sales AS [amount], 3100 AS
UNION ALL SELECT product_id, time_id, store_id, store_cost AS [amount], 3200 AS
Note: We use the UNION ALL statement to retrieve all
records, including duplicates. The effect of a simple UNION statement is to
eliminate what appear to be duplicates. Our query may appear to return
duplicates simply because we selected only a subset of the sales_fact_1997
table, which, for the purposes of this exercise, we assume to be correct and to
contain only intended data. A complete population of the data should,
therefore, be preserved intact.
The Select Query
(SQL View) dialog should appear as depicted in Illustration 10.
Illustration 10: The UNION ALL Query as Entered in Design Query