MSSQLTips

This post is related to this tip: How To Build a Cube From an Existing Data Source Using SQL Server Analysis Services

http://www.mssqltips.com/tip.asp?tip=1532

I am having some annoying issues with Excel 2007...the main one is that I am able to do a "between" filter on an attribute, but when I move that attribute to the "Report Filter" area, I am only able to filter by checkbox, which doesnt work really well when you have 10,000 values :)

Has anyone else seen this issue and found a resolution for it? Thanks in advance

I have a suggestion that could help out.  You can group members in a dimension so that instead of having 10,000 discrete values you have a much smaller number of values which are actually ranges of values.  

There are 2 attribute properties that you use to set this up: DiscretizationBucketCount (i.e. how many) and DiscretizationMethod (Automatic, EqualAreas, Clusters).

You can find an example in AdventureWorks  - take a look at the YearlyIncome attribute in the Customer dimension. It has values like 10000 - 30000, 40000 - 70000, 80000 - 90000, etc.

Thanks for the advice Ray...I appreciate it.

 I thought about using the discretization buckets, but I was not able to find a way to specify custom buckets. My understanding is that SSAS decides how to divide the data depending on the bucket count you choose...I need to be able to specify 0-20, 20-350, 350-10000, for example.

 Do you know any way to do this? Do I need to get out my MDX book? :)

 I'm not sure how to do it in SSAS but you could:

- Do it in the Data Source View using a named query; e.g. add in a CASE statement to map the ranges to text columns like '0 - 20'

- Create a view in the Data Source and use CASE

- Do it as part of your ETL process

 

 

Great ideas, Ray. Thanks again!