I'm a newcomer to AS, but familiar with traditional SQL/relational stuff. I'm getting used to dimensional modeling techniques, and have come across a question. We have a situation where users can specify age in different units, for example if the age is 10 months, the the age is marked as 10, the age "class" is months. Or it may be 10 for age, and the "class" is years. The classes can range from minutes --> hours --> up to years. The age quantity is dependant on the value provided for the age class. For example, if they provide the "months" class, then the age must fall between 1 & 12.
I'm wondering what the best way to model this both in the dimensional relational database that will feed AS, and is there any AS tools (calculations/hierarchies, etc.) that would make this easier. And if so - can someone point me in the right direction.
I can think of two possible options.
1) store the data in one single format and then use calculations to display the different classes.
2) create a different measure for each class type.
I would not mix different units of data in the one measure. It means you cannot aggregate the data and will lead to lots of head aches down the track. Option 1) might loose a little bit of accuracy if you need to span time frames between years and seconds, option 2) would use more storage space, but you could keep the orginal fidelity of the data.
|||Darren - thanks for the response. A follow up question....don't know much about measure groups (as you may see in my question) - but in our situation the age is not the measure, so sure not your suggestion will do. For instance our users aren't querying average age by City. They're querying # of events by age. Isn't age then a dimension (or part of one?). Then how do you do a measure group or measure on age?
I guess I'm a little lost when it comes to measure groups. Actually I'm a little lost when it comes to anything that ventures outside of the wizards in AS.
|||If you want to filter and slice other measures by age, then you are right, you would model it as an attribute, not as a measure. I think this makes it even more important to normalize the ages. I am thinking that if one user enters "120 minutes" and another "2 hours", then you want both of these ages to come up as being equivalent.
It probably depends more on what type of analysis your users will be doing. Often in this type of situation you would set-up an age dimension with some useful breakdowns. You can then do your breakdowns at any granularity that makes sense and group things together into hierarchies.
eg. Your could even do something like the following
AgeId
Then as you populated your fact table you would insert the appropriate AgeID into it.
