Before building Analysis Services cubes we must first learn why and when cubes are a better
alternative than data warehouses. This introduction also discusses the difference between the
tabular and the multi-dimensional version of Microsoft Analysis Services.
- The need for OLAP and Analysis Services
- Business Intelligence in SQL Server
- Tabular versus multi-dimensional
Loading the relational data
An Analysis Services cube pulls its data from relational data sources. This modules shows how we
can connect to those sources, how we can specify which tables should be accessible, and how we can
make modifications to the source tables via data source views. Al this is done via the Visual Studio
add-in SQL Server Data Tools (SSDT), which is introduced in this module as well.
- Getting started with SQL Server Data Tools (SSDT)
- Data Sources and Impersonation
- Data Source Views
- Exploring the data
Building and deploying dimensions
Dimensions are the most important building blocks for creating cubes. Dimensions consist of all sorts of objects
such as attributes and hierarchies. This module first introduces that terminology, then creates basic dimensions
via the wizard and then goes and refines these via the editor.
- Dimension terminology
- Building dimensions with the wizard
- Fine-tuning dimensions in the editor
- Handling attribute-relationships
Building and deploying cubes
Cubes are the only objects that are directly queried by the users. This makes them the most important
object in the Analysis Services product. In this module we first learn about cube specific terminology, such as
measures and measure groups, then we build a basic cube via the wizard and we see some fine-tuning of these
cubes with the editor. All the more advanced cube features are discussed in the following modules.
- Cube terminology
- Using the cube wizard
- Additive, semi-additive and none-additive measures
- Building and fine-tuning cubes in the cube editor
- Deploying cubes and dimensions
Browsing the data
Before we start enriching our cube with more advanced features we learn in this module how cubes can
be accessed from typical business intelligence tools such as Excel, Power BI and Reporting Services.
- Browsing from within SSDT and Management Studio
- Browsing from Excel
- Using cubes in Power BI
- Browsing with Reporting Services
- Access cubes from a custom .Net application
Processing Cubes and Dimensions
Cubes can be seen as a sort of cache on top of a data warehouse. But when the data warehouse changes the
cache needs to be refreshed. This is what processing does. But when the cube needs to be processed frequently
or the data volumes grow large we cannot simply reload all the data each time. That's where this module kicks in:
it shows the different options available for refreshing a subset of the data in the most optimal way.
- Different types of processing
- Processing dimensions
- Processing cubes
Aggregations are to a cube roughly what indexes are to a relational databases:
They can speed up the querying... if the right aggregations are made. But without
aggregations or with the wrong aggregations even simple queries can become horribly slow.
But before this module dives into the details of how to setup aggregations it first covers two related topics:
How is Analysis Services storing its data (ROLAP, HOLAP and MOLAP) and how can we store the factual data in
smaller units (partitioning)?
- MOLAP, ROLAP and HOLAP storage modes
- Partitioning the cube
- Creating aggregations
- Usage based aggregation design
- Pro-active caching
Multi-Dimensional Expressions (MDX)
MDX is the language used by Analysis Services to extend the model (e.g. with a year to date measure) as well as
to query the model (e.g. when building a PivotTable report in Excel). In this module the basics of MDX is introduced,
such that you get a basic understanding of what is possible with the language as well as getting the basics of writing
your own MDX expressions. For a more in-depth discussion on MDX we refer to our
- MDX expressions and queries
- Calculated members
- CurrentMember and MDX navigtion functions
- Working with tuples
- Named sets
- MDX scrips
- Adding Business Intelligence
Key Performance Indicators (KPI)
KPIs allow us to compare an actual value with a target or goal, and derive from this a status: Are we doing well or not. By defining these in the cube instead of in a report we can encode more complex logic, and reuse it in different reporting tools.
- What are KPIs
- Designing KPIs
- Using the KPI browser
By adding translations to the cube users will get the meta-data (such as measure names) as well as data (such as product names) translated in their reports, without the need to do anything special in the reporting tool.
- Dimension translations
- Cube translations
- Testing translated cubes
Analysis Services is not an island but part of a bigger BI ecosystem. By creating actions we can allow cube users to drill through, not only in SSAS, but also into other reporting tools, websites etc.
- Types of actions
- Creating Actions
- Using Actions
Cubes easily become large, with many measures and attributes. This can be frustrating for some users as they have to spend some time searching through all the meta-data for their measures and attributes of interest. This problem can be solved by creating perspectives, which can filter down the meta-data for the cube users, as if they have their own mini-cube.
- The need for perspectives
- Creating perspectives
- Browsing perspectives
In this module more advanced relationships between the fact and the dimension tables are covered. You will learn amongst others how to setup cubes where different fact tables link to the same dimension at different grains.
- Degenerated dimensions
- Referenced dimensions
- Many-to-many dimensions
- Other types of dimensions
As any server, also SSAS needs backups, security configuration, performance monitoring etc.
- Installing Analysis Services
- Backup and Restore
- Securing Analysis Services
When analyzing business data, two challenges pop up frequently: the
complexity of writing and maintaining queries which retrieve the proper data,
and the performance issues which might surface when querying large amounts of
data. OLAP (OnLine Analytical Processing) cubes provide
fast aggregation querying over large amounts of data in a user-friendly
way. In this course, you learn how to build, maintain and query OLAP cubes with
Microsoft SQL Server Analysis Services.
This course focusses on the multi-dimensional cubes and the MDX language.
Since SQL Server 2012, Analysis Services contains an additional tabular engine
and the DAX language (based on the ideas of Power Pivot), but this aspect of
Analysis Services is not discussed in detail in this course. Please, see the
Microsoft SQL Server Analysis Services Tabular course for more details on the Tabular modeling.
Analysis Services does also contain a data mining engine. This part of Analysis Services
is not covered in this course. Check out the Microsoft SQL Server Data Mining course for
a detailed discussion of the data mining aspects of Analysis Services.
After completing the course, students will be able to create and manage Analysis Services cubes, use
features such as translations, Key Performance Indicators and calculated
members. They will also be able to process cubes, design aggregations, decide upon
which type of storage is optimal.