December 2, 2010 at 6:58 am
Hi
I am currently working on the build/design of a series of data marts to cover a number of aspects of a client's business.
Currently one of the data schemas I have includes a fact table containing forecast values stored at a yearly level. 😎
I have now built an OLAP cube from the fact table and related dimensions. The client will be reporting from this data using EXCEL pivot tables (though also has SSRS and Crystal Reports which they use infrequently)
The question I have is that the forecasts will get run on a monthly basis. There is a requirement down the line to be able to compare different monthly versions of yearly forecast data. I have suggested to the client that they can schedule EXCEL reports (with pivots) to run on a monthly basis and therefore could keep monthly versions ie forecast Jun 2011 report, forecast Jul 2011 report.. The client wants to be able to compare former monthly forecast versions in the same report though.. which presumedly would mean they would need to either perform some manipulations in EXCEL or choose a different approach.
The client has suggested that they want the fact table, instead of being drop and reload, to accumulate snapshots of data over a monthly period. This means that I would essentially copy across the same number of records each month with the addition of a version date. :w00t:
I am not totally sure of this. The volumes of data the client has are small but overall letting a fact table double its size each month still seems a little wasteful.
I just wanted to know what the opinion is and whether there is an alternative approach that I perhaps haven't considered.
🙂
December 3, 2010 at 4:26 am
I am concluding that the client would be able to get monthly snapshot Excel reports simply by scheduling the ETL monthly and versioning the EXCEL reports and that this should be recommended?
Overall I feel that storing monthly versions of the same data seems very wasteful - especially since by reporting on data you are essentially storing static copies. 😎 Also it is not a flexible solution. What if they wanted to change the versioning to weekly or yearly?
The issue is that the client will want to display forecast from the different monthly versions side by side. Does anyone know if there are tools that will allow versions of the same report to be merged? Or even if this can easily be achieved in EXCEL programatically? 🙂 Otherwise they can just use cut and paste and I still personally think this is better . 😛
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply