Grouping Dimension Values

I have a question about grouping values in a dimension.

I have a dimension that relates to the age of a person. When I run my ETL we bring in the age as it was at the time of the load. When I display that age to my users in a report, I would like to group the ages according to internal usages.

Example: Age 16, 17, 18 would be grouped into a group of 16 to 18, etc....

This provides to us a larger statistic sample set, plus it makes the reports much easier to read.

I am using Reporting Services to generate reports, but I was looking to move the grouping logic away from the client to the Analysis Services server.

I actually have several dimensions like this where I would like to perform grouping.

I was considering using a Calculated Member to solve this, but wasn't sure if there was a cleaner solution.

Thanks in advance.

Bill

One solution is to do this by a named calculation in the data source view and create your groups with TSQL-CASE. In this way you will have full control over the groups.

The other way is to use the discretization method property of this attribute/column in the dimension editor. With this method you let SSAS create the groups with a little less flexibility than using a named calculation.

HTH

Thomas Ivarsson

|||

Thomas,

Thanks for the response.

I should have stated in my initial post that I am using AS 2000 not SSAS 2005.

Sorry for that.

Any ideas as to how to fix this solution into AS 2000?

Thanks in advance

Bill

|||

Use TSQL Case when you update your dimension table. Books On Line have good examples regarding case.

You can also wrap the TSQL case in you key and name columns, for the dimension level, in the dimension editor. This more of a dirty hack than fixing this in the dimension and your data source.

You can use views against the source table as well.

HTH

Thomas Ivarsson