By: Siddharth Mehta
Overview
A SSAS multidimensional data model is composed of different database objects like dimensions, measures, data source, aggregations, perspectives, etc. In this chapter we will review all the major database objects that get developed in SSAS database as a part of the data model. Also we will look at tools that are used to develop these database objects.
Explanation
SQL Server Data Tools (SSDT) is the IDE used to develop SSAS solutions. You can create new projects as well as open SSAS databases already hosted on SSAS instance. For the purpose of this discussion, our intention is to open the Adventureworks SSAS database that we have hosted on our SSAS server. The steps to open an existing SSAS database in online mode can be read from here. Once you open it, SSDT should look as shown below.
1) Data Source and Data Source Views: The above screenshot shows different elements of the solution in the Solution Explorer window (on the right side), like Data Source, Data Source View, Cubes, Dimensions, Mining Structures, Roles and Assemblies. Development of a new project generally starts by creating a new data source which points to the AdventureWorks DW. You can learn more about data sources in multidimensional models from here.
Mostly we do not need all the data objects from the entire data warehouse as the dimensional model can be considered as a data mart which is a limited domain of a data warehouse. So we select the required database objects to create a data source view which is an insulation layer over the actual source data. The above screenshot has the data source view open in the middle of the screen, with the Diagram Organizer on the left. You can read more about data source view in multidimensional models from here.
2) Dimensions: Dimensions are of two types from a SSAS Multidimensional Data Model perspective - Database dimension and Cube dimension. From a data source view, we can create dimensions which contains attributes and hierarchies. These dimensions are known as database dimensions. In a cube the primary database objects are dimensions and measures. The dimensions created in a cube are instances of the database dimensions. For example, there is a single Date dimension, but in the cube we can have multiple instances of the same dimension for analytical purposes like Data, Ship Date, Delivery Date, etc. In the below screenshot, we have the AdventureWorks cube open. The Solution Explorer lists all the database dimensions and the cube structure pane (on the left side) lists all the cube dimensions. You can read more about dimensions in multidimensional models from here. Also consider reading this tip about using the dimension wizard and dimension designer to create dimensions and hierarchies.
3) Cube Structure: A cube contains data in a multidimensional structure composed of dimension and measures. Numerical data is fact tables is aggregated and measures are created out of the same. In order to analyze this numerical data by different entities (dimensions), cube dimensions are created to slice and dice data to analyze data from different angles. The most common way of developing a cube is by using a cube wizard. You can read this tip to understand an example of how to use this wizard for creating cubes. Measures groups and measures are defined while creating a cube in the cube wizard. Generally all the fields in a fact table are selected as measures ideally, and one measure group is created per fact table. You can read more about measures and measure groups in multidimensional models from here. In this chapter we will be exploring the AdventureWorks cube that can be seen in the Cubes folder of Solution Explorer pane. You can learn more details about cubes in multidimensional models from here.
4) Dimension Usage: In a cube designer, the second tab is the dimension usage tab where one can define which dimensions are measure groups are related and the type of relationships between them. Consider reading more about dimension relationships from here.
5) Calculations: Multidimensional Expressions (MDX) is the query language of SSAS. The Calculations tab can be used to develop calculated members, named sets (stored queries) and other calculations using MDX. This can be considered similar to views and stored procedures in the SQL Server Database Engine. Consider reading more about calculations in multidimensional models from here.
6) KPIs: Key Performance Indicators often known as KPIs are used to develop one another importance analytical construct. KPIs are based on measures and have goal, status, trends, indicators, and are defined using MDX. Consider reading more about KPIs in multidimensional models from here.
7) Actions: This tab is used to define actions on a cube where a certain slice of a cube is selected. Actions are user initiated and defined in a cube using this tab to provide an enhanced browsing and analytical capability to the user. You can read more about actions in multidimensional models from here.
8) Aggregations: Aggregations forms the core of multidimensional data models. All the SSAS data models can be simulated in relational models as well to an extent. The real power of analytical models comes with aggregation designs where data is pre-aggregated as required which enables data analysis, slicing-dicing, drill-down and drill-through in a matter of milliseconds. Aggregations can be designed using aggregation tab. Aggregations work for MDX like an index for a T-SQL query. You can read more about aggregations and aggregation designs from here.
9) Perspectives: Perspectives work like views in SQL Server and can be designed using the Perspective tab as shown below. You can read more about Perspectives in multidimensional models from here.
10) Translations: Localization and Globalization are important for any multi-national business. Providing translations in local language becomes an important requirement in such cases, which can be supported by defining translations for database object names using the translations tab as shown below. You can read more about translations in multidimensional models from here.
Additional Information
- Consider exploring each tab and object in the AdventureWorks solution and use the reference links provided about to learn about each object in detail.
