Adjustments from two sources...

[deepu_sree]

I've a requirement of pulling the same data from two sources (say X and Y) which might not match..The granularity of X is at a lower level than granularity of Y...X - day level, Y - month level. In a monthly report, which has data from Y (as the high level number), I need to have an adjustment column which holds the difference of summing up the day level data from X and the monthly level data from Y.

Can we define something on the dimension?

--Month1
--------D1
--------D2
--------D3
--------D4
--------Day Adj

where Day Adj = Y - Sum(Daily data from X)...

Please reply asap as we need this to be done in a few days...

[Martin Ryan]

You could do it in the date dimension, but then you'd either have consolidations that held differences, or you'd have to have alternate hierarchies and it'd get a bit messy.

I'd have another dimension, say "Source", with the elements "X", "Y" and "Difference". Difference being a consolidation of X and Y with one of them having a weighting of negative one.

Then in your time dimension you'll have items like

Year
- Jan
-- Jan 1 (for source X)
-- Jan 2
...
--Jan 31
-- Jan Entry (for source Y)

and so on for each month. Source X would write to the day and the element 'X' in the "Source" dim. Source Y would write to the "Month Entry" (e.g. "Jan Entry") element in the month dim and element 'Y' in the "Source" dim. This means there would be a valid value for either source in the monthly consolidation and you could compare the difference in the "Difference" consolidation in the "Difference" column. That difference will also aggregate all the way up the hierarchies.

HTH,
Martin

[Steve Vincent]

I've a requirement of pulling the same data from two sources (say X and Y) which might not match..The granularity of X is at a lower level than granularity of Y...X - day level, Y - month level. In a monthly report, which has data from Y (as the high level number), I need to have an adjustment column which holds the difference of summing up the day level data from X and the monthly level data from Y.

Can we define something on the dimension?

--Month1
--------D1
--------D2
--------D3
--------D4
--------Day Adj

where Day Adj = Y - Sum(Daily data from X)...

Please reply asap as we need this to be done in a few days...

Different situation but that is how we have done it in the past. The key is making sure you don't get duplicate data, ie for any other combination of elements you don't get data in the Dx and Day Adj elements. Easily done if you have something like a version or scenario dim where the X and Y datasets get seperated.