Dimension Range

I have a simple cube based on just 2 DB tables. I have 1 measure and 5 Dimensions. I need to create some ranges out of 2 numeric Dimensions. Can anyone suggest how to go about it. It may be a simple thing- but I am just a learner...I have tried to use DiscretizationMethod but my ranges are very specific so it does not help.

What are the numeric dimensions, and what are the ranges you want to create on the numeric dimensions? Your answers will point us in the direction of a solution. An example would be very helpful.

PGoldy

|||

The Ranges I want to create for the numeric Dimensions are something like this:

< 0, 0-300, 300-500, 500-520, 520-540, 540-560,....640-660, 660-680, 680-700, 700-900, >900

And for another Dimension:

<0, 0-70, 70-80, 80-90, 90-100, 100-125, > 125.

Thanks.

|||

Have you tried the TSQL CASE in the data source view.

Regards

Thomas Ivarsson

|||

Hi.

Thomas has a good suggestion (thanks Thomas). I'll give you a little more detail.

In the Data Source View (DSV) within BI Studio you can create a "named query". The named query allows you to define any valid SQL statement. Think of it like using a view in the SQL db, but it's part of the project in BI Studio so you're not bothered with maintaining a view in the SQL db. To create a named query open the DSV in Bi Studio. Right click in the layout pane and select New Named Query. The Create Named Query dialog lets you pick tables, columns, or enter your own SQL. Your choice. I recommend you enter your own SQL with a CASE statement which defines the dimension buckets you want. As an example (this code works on Adventure Works DW):

SELECT
CASE
WHEN SalesAmountQuota > 0 AND SalesAmountQuota < 5000 THEN '0-4999'
WHEN SalesAmountQuota >= 5000 AND SalesAmountQuota < 10000 THEN '5000-9,999'
WHEN SalesAmountQuota >= 10000 AND SalesAmountQuota < 20000 THEN '10000-10,999'
ELSE '20000+'
END AS SalesAmountBucket
FROM FactSalesQuota

The above statement generates the "keys" into your buckets for each record in the fact table. Don't forget to include the rest of your fact table columns in the SELECT and you get a new fact table as a named query.

The new column you've generated in the named query also serves as the attribute hierarchy for your range.

Good Luck.

PGoldy

|||

Thanks Paul for guiding with more details than in my Zen approach.

That is a good explanation of CASE.

Kind regards

Thomas Ivarsson

|||Thanks a TON to PGoldy and Thomas. Yeah, it was really that simple...can't believe it.sql