Create costs fact tables

In this lesson we will create a fact table with costs.
Costs are provided on a monthly basis (variable cost) and yearly basis (fixed cost).
Our sales data is grouped on the day detail level and this makes this data model an example of a Fact Constellation Schema.

A result of the learning steps will be two CSV extracts with the costs data.

Lesson assumptions and objectives:

  • The costs data will be randomly generated, using revenue total as a base variable
  • Costs are divided into fixed and variable costs and allocated on a different date detail level
  • We will create two separate SAS tables and CSV extracts for each of the cost values

    Please refer to the comments (in green) included in the SAS program attached below.

    * CALCULATE FIXED COST; * create a temporary table with total revenue which will be an input to the fix cost calculation; proc sql; create table fixcost as select min(year(dt)) as yr, sum(revenue) as tot_revenue from cognosbi.sales_facts; quit;   /* populate the fix costs which would be around 40 percent of all sales, growing each year  */ data cognosbi.fixcosts(keep=yr fixcost); set fixcost;   obs_to_populate=3; * we will populate three years of data; format fixcost 8.2; * fixed cost base record taken from the first record beeing 40% of the revenue; base_fixcost = 0.4 *(tot_revenue/obs_to_populate); *total revenue divided by number of years ;   fixcost=round(base_fixcost,500); * round the result to 500 ; output; * output the first base record ;  * populate data for the following years  ; DO i=2 TO obs_to_populate;       yr+1; /* increase year */  *calculate a random cost increase ranging from 0 to 15 percent ;       costincr=1+0.15 *ranuni(i);   *calculate the final fix cost ;       fixcost=round(fixcost*costincr,500);        OUTPUT; * write to the output table; END;   run;   * CALCULATE VARIABLE COST ; * create a temporary varcost table ; proc sql; create table varcost as select month(dt) as mth, year(dt) as yr, sum(revenue) as mth_revenue from cognosbi.sales_factsgroup by mth, yr; quit;   /* generate random variable costs which will be around 20 percent of monthly sales */ data cognosbi.varcosts(keep=newdt varcost); set varcost;   * format the date to a mm-yyyy format; format newdt mmyyd7.; format varcost 8.2;  * create a date which is the first day of each month ; newdt=mdy(mth,1,yr);   * delta which is based on a normal distribution random number generator (can be positive or negative); delta = rannor(0)*mth_revenue*0.05;  * calculate the final variable cost ; varcost=0.2*mth_revenue+delta;  run;   /* generate costs extracts */ proc export data=CognosBI.fixcosts  outfile='D:\business_scenario\f_fixcost.csv'  dbms=csv replace; run;   proc export data=CognosBI.varcosts  outfile='D:\business_scenario\f_varcost.csv'  dbms=csv replace; run;

    Costs fact tables generated in SAS:
    Costs fact tables generated in SAS