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:
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:
