SSAS: Using fake dimension and scopes for dynamic ranges

Filed under:
|
|
ssas

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?

I 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 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.

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

To 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.

© ASP.net Weblogs or respective owner

• SQL SERVER – Concat Strings in SQL Server using T-SQL – SQL in Sixty Seconds #035 – Video

as seen on SQL Authority - Search for 'SQL Authority'
Concatenating  string is one of the most common tasks in SQL Server and every developer has to come across it. We have to concat the string when we have to see the display full name of the person by first name and last name. In this video we will see various methods to concatenate the strings. SQL… >>> More

• SQL SERVER – Concat Function in SQL Server – SQL Concatenation

as seen on SQL Authority - Search for 'SQL Authority'
Earlier this week, I was delivering Advanced BI training on the subject of “SQL Server 2008 R2″. I had great time delivering the session. During the session, we talked about SQL Server 2010 Denali. Suddenly one of the attendees suggested his displeasure for the product. He said, even though… >>> More

• How can I detect which version of SQL (eg SQL 2008 or SQL Azure)

as seen on Stack Overflow - Search for 'Stack Overflow'
I need to detect which version of SQL I am dealing with to perorm various tasks, I need specifically detect if I am on SQL 2008 or SQL Azure. How can I do this with detection code written in SQL? >>> More

• Nested SQL Select statement fails on SQL Server 2000, ok on SQL Server 2005

as seen on Stack Overflow - Search for 'Stack Overflow'

• Putting data from local SQL database to remote SQL database without remote SQL access enabled (PHP)

as seen on Stack Overflow - Search for 'Stack Overflow'
Hi, I have a local database, and all the tables are defined. Eventually I need to publish my data remotely, which I can do easily with PHPmyadmin. Problem however is that my remote host doesn't allow remote SQL connections at all, so writing a script that does a mysqldump and run it through a client… >>> More

• Oracle?BI??????

as seen on Oracle Blogs - Search for 'Oracle Blogs'
Oracle????? BI?????????????? Oracle BI Oracle Essbase Oracle BI Standard Edition (Oracle Discoverer) Oracle BI Applications   ?????????????????????   ?1. Oracle BI??????(?)Oracle BI 11g????!????????????????????Siebel???????11g????Oracle????????????? Oracle BI?????????… >>> More

• I'm Not Bi-Polar, I'm Bi-Winning

as seen on Oracle Blogs - Search for 'Oracle Blogs'
On March 1st, Charlie Sheen joined Twitter and was able to amass 1M followers in 25 hours and 17 minutes, setting an official world record.  So why does it take your brand so long to collect followers?  Easy: you're brand isn't a train wreck.Wouldn't it be great if your customers we chatting… >>> More

• Information Builders lance WebFocus Mobile BI, sa solution BI pour Smarphones et tablettes

as seen on Developper.com - Search for 'Developper.com'
Information Builders lance WebFocus Mobile BI Sa solution BI pour Smarphones et tablettes Information Builders annonce le lancement de WebFOCUS Mobile BI, sa nouvelle solution de Business Intelligence qui permet de bénéficier des fonctionnalités analytiques sur mobiles via n'importe quel terminal… >>> More

• UPDATE FOR BI PUBLISHER ENTERPRISE 10.1.3.4.1 MARCH 2010

as seen on Oracle Blogs - Search for 'Oracle Blogs'
Latest roll up patch for 10.1.3.4.1 is now out in the wild. Yep, there are bug fixes but the guys have implemented some great enhancements. I'll be covering some of them over the coming weeks, from collapsing bookmarks in your PDFs to better MS AD support to 'true' Excel templates, yes you read that… >>> More