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.
The queries that exist in the sample database appear as shown in Illustration 5 below.
The New Query dialog appears as shown below:
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.
We will be creating a UNION ALL query to handle the following concurrent objectives (explained above):
We will need to type the query directly, from Design Query: SQL View in MS Access.
The Select Query (SQL View) dialog appears, as shown below.
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.