April 30, 2009 at 4:46 am
Hi Just a quick question to get something right in my head.
Say I have business questions..
Number of registrations by Area by Month
Number of Registrations by Area By Year
And A time Dimension with the hierarchy of Year, Month within it.
This means that I can make the above business questions into 1 question and keep them both in the same fact table even though they are different levels of granularity?
I originally had a fact table for all the questions for month and another one for year but I dont have to do this I dont think?
Thanks for any help.
Debbie
April 30, 2009 at 7:12 am
This means that I can make the above business questions into 1 question and keep them both in the same fact table even though they are different levels of granularity?
Yep, the grain for your fact is/should be Month (unless of course you have day level). The hierarchy in the dimension table (and subsequently the dim in olap) will let you answer both questions easily.
Steve.
April 30, 2009 at 7:15 am
Fantastic. Thats just what I thought.
I was wondering how percentages dealt with.
Percentage of children with registrations by month
Rolls up to
Percentage of children with registrations by year
With this being none additive Im not quite sure how it should be dealt with.
Thanks for the advice 🙂
Debbie
March 26, 2010 at 8:47 am
Just having a quick look at this again and Im thinking percentages are always created as calculated measures in Reporting Services which will then always calculate correctly no matter where you are in the hierarchy
March 26, 2010 at 1:23 pm
You should be ok creating the calc member in the cube. Either it will work naturally, that is, you have a measure call # of children and another called '# kids reg'. When you look at these, and filter by the Time dim, it will show the differing values (e.g. the reg count for th emonth wil be different to the (summed up) reg count for the year). So the calculation, of 'reg kids / kid count' should reflect the correct result at each level of that time dim.
If you were to find i didn't, depending on your version of AS, you could either use solve order to force one calc to resolve prior to another (e.g. always sum then divide the two summed values) or the script order in the MDX script to ensure resolve order.
the reason for suggesting this, is it's often better to have these things in the cube, so you can 'build once, use many times' rather than having to recreate it in every report (at least until R2 hits the shelves and we get our item library!)
Steve.
March 29, 2010 at 2:12 am
Brilliant, thanks for that. Its all becoming a little more logical now 🙂
Debbie
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply