Period based graph with age blocks using SSAS

Question

I am just starting to use SSAS and I'm trying to produce a data cube that allows me to produce a chart of the number of people in a particular age range per quarter spanning a number of years. So for example:

                 |   $        @$           $
Number of people | # $        @$         #@$
                 | #@$       #@$         #@$
                 +------------------------------
                  2010 Q1   2010 Q2 ... 2014 Q1

where # are people aged 10-20, @ are people aged 20-30 and $ are people aged 30-40.

The problem I'm finding is that in 2010 someone might have been 29 and so would fall into the 20-30 age range, but in 2014 I want that same person to be counted in the 30-40 age range because he would now be 33. I don't know how to (or if it is even possible to) create a dimension that would be date and time sensitive?

How do you get the age information? Do you have a person dimension containing a birthday or (assuming privacy issues with that) a birth year from which you can calculate the (estimated) age at a certain date? — FrankPl, Jul 15 '14 at 17:19
Hi, yes, I have a date of birth with which to calculate it from. — Anupheaus, Jul 16 '14 at 10:41

FrankPl · Accepted Answer · 2014-07-18T16:19:34.247

Build it into your model

Just model it correctly: Assuming you have a fact table containing the measures you analyze and foreign keys to a person dimension as well as a date dimension, add a new table for the "age group" dimension containing the ranges you want to analyze. This table would contain e. g. one record "0-9", one "10-19", etc.

Then add a foreign key column referencing the age group dimension to the fact table. Setting the value of this foreign key would then be a SQL update statement on the fact table that calculates the difference between the date referenced by the foreign key to the date dimension and the birth date of the person referenced by the foreign key to the person dimension. This difference is the age at that date. From this, determine the age group and use its primary key as the value of the foreign key column.

And finally, just make an attribute from your age group column of the age group dimension.

Calculate it on the fly

If you really want to calculate that on the fly, I am not sure the performance is good, but you could try it as follows: You would need the "age group" dimension as well. But there would be no need to link it to your fact table. Then define a calculated member like this:

member [Measures].[Age at Date] AS
       DateDiff('yyyy',
                [Person].[BirthDate].CurrentMember.Properties("Key0", TYPED),
                Measures.[Date]
               )
Member [Measures].[Person Count per Age Group] AS
       CASE
           WHEN [Age Group].[Age Group].CurrentMember is [Age Group].[Age Group].[0-9] THEN
                Filter([Person].[Person Id].[Person Id].Members,
                       [Measures].[Age at Date] >= 0 AND [Measures].[Age at Date] < 10
                      ).Count
           WHEN [Age Group].[Age Group].CurrentMember is [Age Group].[Age Group].[10-19] THEN
                Filter([Person].[Person Id].[Person Id].Members,
                       [Measures].[Age at Date] >= 10 AND [Measures].[Age at Date] < 20
                      ).Count
           ...
       END

This assumes that you have the date of each record in a measure named Measures.[Date] directly in your fact table. If that would not be the case, you could get that date as a date data type possibly from the current member of the date dimension similarly how the birth date is derived via the Key0 property. I am assuming that you really use a Date data type in the dimension for the birth date attribute key, otherwise, a type conversion might be necessary. For purposes like that, there are many VBA functions available in Analysis Services (like the DateDiff that I used above), as documented here.

I was hoping that SSAS would do this on the fly rather than producing the data in a very, very large fact table. The issue is that there are a lot of employees and years worth of data. This fact table would be huge! — Anupheaus, Jul 17 '14 at 08:51
@Anupheaus I would not think you need too much increase in table size. As you surely have less than 255 age groups, you could use a primary and foreign key column of type tinyint. And even for huge companies, I would assume you have less than a million employees. So for 50 years of data, you would have 200 quarters, times 1 million, you get 200 Megabytes (plus some overhead depending on storage details). Even if you would use int instead of tinyint, you would have an increase of four times 200 MB, i. e. roughly 1 GB, which I would not assume huge. — FrankPl, Jul 17 '14 at 13:26
I need to be able to drill down to months though. Ultimately, if SSAS can't do this with some computed calculation, then I'll mark this as the answer (since it can't otherwise be done) unless you know of a calculation that could allow SSAS to do this on the fly? — Anupheaus, Jul 18 '14 at 15:48
@Anupheaus I added a description how this could be done on the fly to my answer. However, I would doubt that for a large cube, it would perform fast enough. — FrankPl, Jul 18 '14 at 16:21

Period based graph with age blocks using SSAS

1 Answers1