Project Botticelli

Cubes, Measures and Measure Groups Purchase this course

30 April 2015 · 9 comments · 1695 views

SSAS Cube Design Series

Chris Webb shows Dimension-Measure Relationships and Measure Groups

Following on from the module on designing dimensions, Chris Webb teaches how to build cubes, measures, and measure groups, in this 1-hour 10-minute, in-depth, hands-on tutorial. Building SQL Server Analysis Services cubes using the New Cube wizard is straightforward: you choose the tables you want to use as the basis of your measure groups, you choose the columns you want to use as your measures, you choose which of your existing dimensions you want to use, and you’re done!

The first important question you have to ask when designing cubes is should you have one big cube or lots of small cubes. The one big cube approach should probably be your default choice because that’s the only option that allows you create calculations and queries that span all of your data. That said, there are some scenarios where it makes sense to create many smaller cubes.

Once you have created your cube, you will need to set various properties—especially on your measures. For example, you will want to set the FormatString property on your measures and organise them into Display Folders. You may also want to change how a measure aggregates up by changing the AggregateFunction property. Most measures are Sums, but you can also create measures that calculate Distinct Counts or perform semi-additive aggregation such as finding the last non-empty value in the current time period.

Setting up the relationships between your measure groups and dimensions is also important. Most dimensions have regular relationships with measure groups or no relationship at all. Fact relationships are for dimensions that are created from the same fact table that has been used to create the measure group. Referenced relationships allow you to join a dimension to a measure group through another measure group; they can either be materialized or non-materialized. Finally, many-to-many relationships allow you to model scenarios such as a bank account being associated with multiple customers, and a customer being associated with multiple customers. Many-to-many relationships are extremely powerful but they do carry a penalty in terms of query performance. Measure expressions can be used with many-to-many relationships to perform simple multiplication or division calculations (such as those needed for currency conversion) before measure aggregation takes place.

Other features you can add to your cube include creating Actions, which can allow your users to click on data from your cube and do things like open a web page or drill down to low-level data. Perspectives allow you to make a single large, complex cube look like multiple smaller, simpler cubes. Translations allow you to display the names of dimensions, hierarchies and members in different languages.

Log in or purchase access below to the premium version of this content.

Purchase This Course or Full Access Subscription

Single Course

$250/once

Access this course for its lifetime*.
Purchase
Subscription Best Value

$480/year

Access all content on this site for 1 year.
Purchase
Group Purchase

from $480/year

For small business & enterprise.
Group Purchase

  • Redeem a prepaid code
  • Payment is instant and you will receive a tax invoice straight away.
  • Your satisfaction is paramount: we offer a no-quibble refund guarantee.

* We guarantee each course to be available for at least 2 years from today, unless marked above with a date as a Course Retiring.

Comments

goranbanjac · 2 November 2015

Hi Chris,
I have another question. What is a best way to show what didn’t happen for the given month?
For example, I need to show count of video views for given month and together with that list of all other videos that haven’t been view that month but belong. For example:
Video1 5
Video2 0
Video3 2
I have created second fact table and loaded with all video views and rooms and using MDX like:
IIF(
IsEmpty (Measures.[Fact 1]),
IIF(IsEmpty([Measures].[Fact 2]),null,0),
Measures.[Fact 1]
)
I was wondering if there is better way to show what I need.

Chris Webb · 6 November 2015

No, that's a perfectly OK way of solving the problem

goranbanjac · 10 November 2015

Showing over period of 48 moths is not a problem since I have 60,656 videos * 48 moths is 2,911,488 rows in fact 2 table. But they want to show it over a time by unit which is 2100 unique units. I can’t follow approach from above (2,911,488 * 2100). How I design around this so I can get:
Month = 11
Unit = 4E
Video 1 5
Video 2 0
Video 3 2

Thanks!

Chris Webb · 11 November 2015

Is this an "events in progress" problem? http://blog.crossjoin.co.uk/2011/01/21/solving-the-events-in-progress-problem-in-mdx-part-1/

goranbanjac · 11 November 2015

I don’t need calculate between dates.
In my case, given hospital wants to see all videos (watched and not watched) for given month grouped by Units.
They also want to see new video they added to video library (DimVideo) that hasn’t been watched yet (FactVideoUsage doesn’t have a row yet for given video.) I have DimVideo, DimUnit, DimTime and FactVideoUsage.

darlove · 6 December 2015

Hi there. Great videos but... something is not right with this one. In certain places it stops unexpectedly. Just wanted to let you know.

Rafal Lukawiecki · 9 December 2015

Hi Dariusz, thanks for contacting us. This video and the content delivery network are operating well for me and other customers. I would like to troubleshoot your situation, so I will contact you directly by email. I hope you get to enjoy Chris's lesson as it was intended to be, soon.

gregb · 3 February 2016

I have 2 dimensions TIME and Loads. how do I link the two together so the time filters work against the data in excel ?

Chris Webb · 3 February 2016

Can you give me some more details about what you want to do here?

Online Courses