DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones AWS Cloud
by AWS Developer Relations
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones
AWS Cloud
by AWS Developer Relations
Securing Your Software Supply Chain with JFrog and Azure
Register Today

Trending

  • Part 3 of My OCP Journey: Practical Tips and Examples
  • Revolutionizing Algorithmic Trading: The Power of Reinforcement Learning
  • Seven Steps To Deploy Kedro Pipelines on Amazon EMR
  • How To Use Geo-Partitioning to Comply With Data Regulations and Deliver Low Latency Globally

Trending

  • Part 3 of My OCP Journey: Practical Tips and Examples
  • Revolutionizing Algorithmic Trading: The Power of Reinforcement Learning
  • Seven Steps To Deploy Kedro Pipelines on Amazon EMR
  • How To Use Geo-Partitioning to Comply With Data Regulations and Deliver Low Latency Globally
  1. DZone
  2. Data Engineering
  3. Databases
  4. SSAS: Using fake dimension and scopes for dynamic ranges

SSAS: Using fake dimension and scopes for dynamic ranges

Gunnar Peipman user avatar by
Gunnar Peipman
·
Dec. 10, 10 · News
Like (0)
Save
Tweet
Share
6.92K Views

Join the DZone community and get the full member experience.

Join For Free

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.

Dimension (data warehouse) Object (computer science) Database

Published at DZone with permission of Gunnar Peipman, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Trending

  • Part 3 of My OCP Journey: Practical Tips and Examples
  • Revolutionizing Algorithmic Trading: The Power of Reinforcement Learning
  • Seven Steps To Deploy Kedro Pipelines on Amazon EMR
  • How To Use Geo-Partitioning to Comply With Data Regulations and Deliver Low Latency Globally

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com

Let's be friends: