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.
As the last task in this lesson we will generate CSV extracts with dimensions and facts data.

Lesson assumptions and objectives:

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

    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 */ proc sql; CREATE TABLE CognosBI.Sales_Facts AS SELECT 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, Products prd, ProdAge pa, ProdType pt  WHERE fm.idc=cus.idc   AND fm.idp=prd.idp   AND fm.prod_age=pa.age_id   AND fm.prod_type=pt.type_id ORDER BY DT; quit;  /* we also need a customer table joined with countries */ proc sql; CREATE TABLE CognosBI.Dim_Customers AS 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  WHERE cus.CUST_COUNTRY_ID=reg.COUNTRY_ID;  /* products dimension table can be copied easily using a SAS dataset */ data CognosBI.Dim_Products; set products(drop=idp); * drop the idp column which will not be used ; run;    /* 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; run;   procexport data=CognosBI.Dim_Customers outfile='D:\business_scenario\dim_customers.csv' dbms=csv replace; run;   procexport data=CognosBI.Dim_Products outfile='D:\business_scenario\dim_products.csv' dbms=csv replace; run;

    Randomly generate star schema fact table in SAS:
    Randomly generate star schema fact table in SAS