Gunnar Peipman's ASP.NET blog

ASP.NET, C#, SharePoint, SQL Server and general software development topics.

Sponsors

News

 
 
 
DZone MVB

Links

Social

SSAS: Using fake dimension and scopes for dynamic ranges

In one of my BI projects I needed to find count of objects in income range. Usual solution with range dimension was useless because range where object belongs changes in time. These ranges depend on calculation that is done over incomes measure so I had really no option to use some classic solution. Thanks to SSAS forums I got my problem solved and here is the solution.

The problem – how to create dynamic ranges?

SSAS cube structure in problem scopeI have two dimensions in SSAS cube: one for invoices related to objects rent and the other for objects. There is measure that sums invoice totals and two calculations. One of these calculations performs some computations based on object income and some other object attributes. Second calculation uses first one to define income ranges where object belongs.

What I need is query that returns me how much objects there are in each group.

I cannot use dimension for range because on one date object may belong to one range and two days later to another income range. By example, if object is not rented out for two days it makes no money and it’s income stays the same as before. If object is rented out after two days it makes some income and this income may move it to another income range.

Solution – fake dimension and scopes

Thanks to Gerhard Brueckl from pmOne I got everything work fine after some struggling with BI Studio. The original discussion he pointed out can be found from SSAS official forums thread Create a banding dimension that groups by a calculated measure.

Solution using scope, fake measure and fake dimensionSolution was pretty simple by nature – we have to define fake dimension for our range and use scopes to assign values for object count measure.

Object count measure is primitive – it just counts objects and that’s it. We will use it to find out how many objects belong to one or another range.

We also need table for fake ranges and we have to fill it with ranges used in ranges calculation. After creating the table and filling it with ranges we can add fake range dimension to our cube.

Let’s see now how to solve the problem step-by-step.

Solving the problem

Suppose you have ranges calculation defined like this:


CASE 
WHEN [Measures].[ComplexCalc] < 0 THEN 'Below 0'
WHEN [Measures].[ComplexCalc] >=0 AND 
[Measures].[ComplexCalc] <=50 THEN '0 - 50'
...
END

Let’s create now new table to our analysis database and name it as FakeIncomeRange. Here is the definition for table:


CREATE TABLE [FakeIncomeRange]
(
    [range_id] [int] IDENTITY(1,1) NOT NULL,
    [range_name] [nvarchar](50) NOT NULL,
    CONSTRAINT [pk_fake_income_range] PRIMARY KEY CLUSTERED 
    (
        [range_id] ASC
    )
)

Don’t forget to fill this table with range labels you are using in ranges calculation.

To use ranges from table we have to add this table to our data source view and create new dimension. We cannot bind this table to other tables but we have to leave it like it is. Our dimension has two attributes: ID and Name.

Fake dimension in SSAS cube

The next thing to create is calculation that returns objects count. This calculation is also fake because we override it’s values for all ranges later. Objects count measure can be defined as calculation like this:


COUNT([Object].[Object].[Object].members)

Now comes the most crucial part of our solution – defining the scopes. Based on data used in this posting we have to define scope for each of our ranges. Here is the example for first range.


SCOPE([FakeIncomeRange].[Name].&[Below 0], [Measures].[ObjectCount])
    This=COUNT(
           FILTER(
               [Object].[Object].[Object].members, 
               [Measures].[ComplexCalc] < 0
         )
    )
END SCOPE

SCOPE definitions in SSAS cubeTo get these scopes defined in cube we need MDX script blocks for each line given here. Take a look at the screenshot to get better idea what I mean.

This example is given from SQL Server books online to avoid conflicts with NDA. :)

From previous example the lines (MDX scripts) are:

  1. Line starting with SCOPE
  2. Block for This =
  3. Line with END SCOPE

And now it is time to deploy and process our cube. Although you may see examples where there are semicolons in the end of statements you don’t need them. Visual Studio BI tools generate separate command from each script block so you don’t need to worry about it.

Posted: Dec 11 2010, 02:21 AM by DigiMortal | with 5 comment(s)
Filed under: , ,

Comments

Narasimha Murthy said:

Hi, Article was good. I have similar sort of Logic to be done  I need out put in this way

> FactTable:- Cusotomer Count

> Dimension CustomerTable:- {Customer ID,Name,Age,Gender}

> Dimension RangeTable:- {RangeID, StartRange, EndRange}

? Now i need output in such a way ...

------------------------------------------------

StartRange|EndRange|(FactTable)CustomerCount

------------------------------------------------

0         |15      |25

16        |30      |45

.

.

.

.

. so on ...

------------------------------------------------

How to Do such sort of Representation.

# April 29, 2011 6:56 AM

Mike said:

Thank you very much to share this approach. I have a similar problem, but there is one thing that I can't solve.

Here you have to "hardcode" the ranges and descriptions in your mdx code. Do you think it's also possible to get those values from the underlying database?

The second thing then is, that the ranges are historized with valid_from and valid_to dates and can change over time.

Do you have any idea what I could do?

Thanks!

# January 20, 2012 2:52 PM