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

February 17, 2003

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.

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

11. Click the New button in the toolbar atop the Database view.
12. Select Design View.

The New Query dialog appears as shown below:

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

14. Click Close to dismiss the Show Table dialog.

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 cube;
• 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 fields.

We will need to type the query directly, from Design Query: SQL View in MS Access.

15. Click the SQL View button, shown in Illustration 8 below, in the upper left corner of the toolbar of Design View.

The Select Query (SQL View) dialog appears, as shown below.

16. Type the following query into the Select Query (SQL View) dialog:

SELECT product_id, time_id, store_id, store_sales AS [amount], 3100 AS 
	[account_id]
FROM sales_fact_1997
UNION ALL SELECT product_id, time_id, store_id, store_cost AS [amount], 3200 AS 
	[account_id] 
FROM sales_fact_1997;

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.