Previous Year Totals
-
This is my first project in Data Warehousing using the star schema, and I have been doing well up until now.
I was asked to add new information on the reports, which are really just views of the Cubes through BIPortal. Sounded fine until they gave me a list of what they are asking for. They want a report that shows sales, EBIT, EBITA, Fees Etc for the Current Period (easy), YTD (I am using the YTD function) and Previous Year Period, and Previous Year YTD.
I know I could so a subselect in the DTS to get the Previous Year Period, which I am not really found of as it already takes hours to run on the 6 billion rows we are transforming, but I wanted to see if anyone here had any better thoughts on the process.
THANKS!
Tim Blum
Senior Manager, Outsourced Data Services
Learn2Live at Ureach.com -
I am new to Data Warehousing myself, so I am just quoting from Ralph Kimball "The Data Warehouse Toolkit"
"Designers are often tempted to store to-date columns in fact tables. Thet think that it would be helpful to store quarter-to-date or year-to-date totals on each fact row so that users do not need to claculate them. We need to remember that numeric facts must be consistant with the grain. To-date fields are not true to the grain and are fraught with peril. When fact rows are queried and summarized in arbitrary ways, these untrue-to-the-grain facts produce nonsensical, overstated results. They should be left out of the relational schema design and calculated in the data access application instead"
Francis
-
Have you looked at the parallelperiod function? Using appropriate params you can use this to pick out 'same month last year', or even 'same month next year', s you really just add some lag (either positive or negative).
From BOL, here is what they say parallelperiod fn is equivalnet to :Cousin(Member,Lag(Ancestor(Member,Level),Numeric Expression).
You could/would also use this (or even the straight 'lag' function with a neg number) with your YTD to get the YTD for last year.
Steve.
-
Thanks stevefromOZ.
I am attempting to use this, and figure I will be able to get it to work, but as in all things - its never quite as simple as it seems. There is no true date data, just and accounting period dimension, which I suppose Might be usuable in some way as it does have an accounting period start date and an accounting period end date, which would all be fine and good, but they are not consistant throughout years. For example the Feb-05 accouting period runs January 30, 2005 to February 26, 2005.
So I decided to kinda pull a mid date for each period, and add it into the dimension. Therefor, Feb 05 would always be 2/15/2005, march would be 3/15/2005, etc...
That's my plan at least - we shall see...
Tim Blum
Senior Manager, Outsourced Data Services
Learn2Live at Ureach.com -
Assuming (which is always bad
) that your periods belong to years, can't you set the level (and dim) types so that the year is of type year and the period is of type month? The fact that they may be 4-4-5 (or alternates), or that the year starts in Feb should make no difference?Steve.
-
Ok - now I have the Monthly Totals, YTD totals, and Previous Year Monthly Totals, and Previous Year YTD Totals, all except the monthly totals are calcualted members. Yes, I did use the ParallelPeriod function.
Thank you stevefromOZ!
This has taken my fact table down from having 152 fields and the accompanying data transformation that populated this fact table took 5 hours, down to 47 fields (the rest are calculated members) and it now takes a little over 45 minutes to process. And now I see a whole bunch of other things that are being processed into the tables (like percents) that will most likely be moved out to the caculated members rather than the data trasnformation.
One question remains, is there a "lid" to the number of calculated members you can add to a cube?
Tim Blum
Senior Manager, Outsourced Data Services
Learn2Live at Ureach.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply