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.
