Create sales fact table in a star schema Datawarehouse
In this lesson we will create a fact table in star schema datawarehouse architecture. Also, we will produce a statistics summary of newly generated data and perform some validations and checks. To create the final sales fact table, it is necessary to get information from 5 tables.
The sales figures will be stored on a day detail level
SAS merge function might be used for this but we will use PROC SQL procedure as it will be simplier. Here you can see the power of SAS processing, where different data handling techniques can be used together.
The fact table will have 8 columns - 5 dimensions and 3 measures.
We will also create a CSV files with facts and dimensions data which will serve as an input for the Cognos PowerPlay model or any other reporting tool. The data might be also analyzed in SAS analysis tools.
As the last task in this lesson we will generate CSV extracts with dimensions and facts data.
Lesson assumptions and objectives:
Please refer to the comments (in green) included in the SAS program attached below.
/*we can use SQL procedure to join the tables together using inner joins and create a final fact table */
CREATE TABLE CognosBI.Sales_Facts AS
fm.DT as DT, cus.cust_id as CUST_ID, prd.PROD_ID as PROD_ID, pa.age_name as PROD_AGE, pt.type_name as PROD_TYPE, fm.PRICE, fm.QUANTITY, fm.REVENUE FROM
facts_measures fm, Customers cus,
fm.prod_type=pt.type_id ORDER BY DT;
/* we also need a customer table joined with countries */
CREATE TABLE CognosBI.Dim_Customers
SELECT cus.CUST_ID, cus.CUST_NAME, cus.CUST_GROUP, cus.CUST_SEGMENT, cus.CUST_COUNTRY_ID, reg.COUNTRY_TEXT, reg.REGION_TEXT FROM
Customers cus, Countries reg
/* products dimension table can be copied easily using a SAS dataset */
* drop the idp column which will not be used ;
/* extract the dimensions and facts tables into csv files which will be used for processing*/
proc export data=CognosBI.Sales_Facts outfile='D:\business_scenario\sales-cognosbi.csv' dbms=csv replace;
procexport data=CognosBI.Dim_Customers outfile='D:\business_scenario\dim_customers.csv' dbms=csv replace;
procexport data=CognosBI.Dim_Products outfile='D:\business_scenario\dim_products.csv' dbms=csv replace;
Randomly generate star schema fact table in SAS: