Actual Vs Budget Amount in Sales DW

View previous topic View next topic Go down

Actual Vs Budget Amount in Sales DW

Post  aseem.sb on Thu Oct 28, 2010 1:55 am

Hello All,

I have DW for Sales. Currency the design is as follow:
Dim Tables:
1. DimCustomer
2. DimProduct
3. DimDate

Fact Table:
1. FactSales - Sales QTY Sales Amout etc. The granularity of this table is per product, per customer, per order and per day.

I have new requirement that, I need to incorporate Budgeted and Forecast sales in DW.

Currently I am thinking to create new fact table to store all product wise Actual, Budgeted and Forecast sale using scenario dimension. The granularity of this table will be month wise.

For above approach, I have to populate new fact table with using aggregation of actual sale at the time of ETL.

Please suggest me is this design will cater the requirement or suggest me any alternative design for such scenario.

Regards,

Asim

aseem.sb

Posts : 6
Join date : 2010-07-12

View user profile

Back to top Go down

Re: Actual Vs Budget Amount in Sales DW

Post  Jeff Smith on Thu Oct 28, 2010 9:43 am

I have to report actual, budget and forecast. I created an aggregate of the actual so that it was at the same level as the Budget.

In my organization, forecast figures can change frequently and often the changes are out of sync with the load schedule. It's not uncommon for the forecast figures to change on the 1st of each month after the actual numbers have been released. To accommodate the need to update the forecast figures, I created a table in a reference database and gave the sales analyst the rights up update the table. In the DW, I created a view that looks like a fact table but is really the data in the Reference table. The report pulls the Actual and Budget figures from the aggregate table and the Forecast figures from the view.

The Forecast table is quite small. This allows sales to make changes to the forecasted amounts without anybody on the IT side having to get involved.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Actual Vs Budget Amount in Sales DW

Post  hang on Thu Oct 28, 2010 5:11 pm

In general, Jeff's approach is on right track. However I would consolidate the measures from three different sources into one fact table with sales on monthly aggregate level and other two on their original monthly level.

I have experienced exactly the same situation as Jeff mentioned in regards to forecast. But in my case, I still bring across the forecast data from reference database into dimensional data store (DDS), so that I can have a self contained dimensional model and extra protection on BI data quality and allow no direct manual data entry in DDS area, only automated ETL load.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Actual Vs Budget Amount in Sales DW

Post  Jeff Smith on Mon Nov 01, 2010 11:27 am

I don't bring in the Forecast data because the schedule for it to be updated is not on the same schedule of the rest of the database being updated. We would need to load the Forecast figure as soon as it was updated. I'm not even sure the forecast figures even qualify as a fact. They are closer to being dimensional information. In any case, if I brought the Forecast figure into the same fact table as the the budget and actual data, I'd be having to update the fact table.

In any case, the forecast data is very small so using views to the reference table doesn't cause any performance issues.

In DW, anything that reduces the number of "can you just" requests from Sales should be considered kosher - in my opinion.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Actual Vs Budget Amount in Sales DW

Post  hang on Mon Nov 01, 2010 9:58 pm

Whether a table is a dimension or fact is not determined by the size. It depends on the nature of data in the table. If the data provides the context for measurements of your fact, then the table should be a dimension. To be more specific, the context for the measurements is the header description of rows and columns on a typical end-user report while the intersecting cells contain the measurements that are facts.

So by definition, forecast data should belong to the fact table. In general, all high level aggregate fact tables are very small compared to their base fact tables, so performance is not an issue.

To me it doesn’t matter whether the forecast fact is stored in a consolidated fact table, or in a separate fact table and let view consolidate the facts if necessary, as long as it is stored in the same database as part of the dimensional schema. However it is important to have a self contained dimensional data store. In a sound and clear dimensional model, I should see all the relevant tables live in the same database. Data objects in external database should only be accessed in ETL process, maybe by means of synonyms and views. Since the aggregate fact table is really small, updating or delete-appending the table should be no concern to the performance.


Last edited by hang on Thu Nov 04, 2010 8:52 am; edited 1 time in total

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Actual Vs Budget Amount in Sales DW

Post  ngalemmo on Wed Nov 03, 2010 7:23 pm

I have to populate new fact table with using aggregation of actual sale at the time of ETL.

Why?

Just have one fact table for actual sales and another fact table for forecasts and budget. Combine facts as needed during report time.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Actual Vs Budget Amount in Sales DW

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum