How to create data sets for a multi-dataset cube
In this procedure, you create two data sets:
A fact data set, SalesTotal, to retrieve the data for calculating the sales totals
A dimension data set, Productlines, to retrieve data about the product lines
Note that we are not creating a separate data set for the year dimension, as is typical in a star schema. It is sometimes too complicated to create a pure star schema design when working with data stored in an OLTP system.
1 Create a new data set named SalesTotals. Create the following query:
select CLASSICMODELS.ORDERDETAILS.PRODUCTCODE,
CLASSICMODELS.ORDERS.SHIPPEDDATE,
CLASSICMODELS.ORDERDETAILS.QUANTITYORDERED * CLASSICMODELS.ORDERDETAILS.PRICEEACH as "EXTENDED_PRICE"
from CLASSICMODELS.ORDERDETAILS, CLASSICMODELS.ORDERS
where CLASSICMODELS.ORDERS.ORDERNUMBER = CLASSICMODELS.ORDERDETAILS.ORDERNUMBER
and CLASSICMODELS.ORDERS.STATUS = 'Shipped'
The query does the following:
Retrieves PRODUCTCODE data because it is the key to later link to the PRODUCTLINE dimension
Retrieves SHIPPEDDATE data to use for the year dimension
Creates a calculated column, EXTENDED_PRICE, used to aggregate values to calculate the sales totals
Creates a join between the Orders and OrderDetails tables to get all the necessary data about the orders. Because the data set is retrieving data from an OLTP database, joins are unavoidable
Defines a filter condition to retrieve order data for orders that have been shipped, and therefore, that have been paid
2 Create a new data set named ProductLines. Create the following query:
select CLASSICMODELS.PRODUCTS.PRODUCTLINE,
CLASSICMODELS.PRODUCTS.PRODUCTCODE
from CLASSICMODELS.PRODUCTS
The query does the following:
Retrieves PRODUCTLINE data to use for the PRODUCTLINE dimension.
Retrieves PRODUCTCODE data because it is the key that the SalesTotals data set will need to reference.