This page covers SQL Server Analysis Services, also called SSAS, topics along with MDX queries. It includes creating a cube, accessing cube data, and writing queries against the OLAP cube.
What is SSAS?
SSAS is the shorter form of SQL Server Analysis Services, which is a multidimensional online analytical processing server (or OLAP server) and an analytics engine used for data analytics and data mining.
Analysis Services creates an extra layer on top of an OLTP system (relational databases or data warehouse) with information about the fact tables, dimensions, aggregations, hierarchies, common calculations, and so on.
SSAS may have one or more models, and users write queries against these models instead of a traditional underlying database. Every model has business logic. It improves the overall performance.
Need for SSAS: Managing a large volume of data (in terabytes) in an Online Transaction Processing (OLTP) system and analyzing its data is the hardest part. To work with large amounts of data, OLAP is the best possible solution. For example, when we run a query to retrieve sales information for a country (USA), it displays the USA sales. However, we can drill down to California sales, and then further down to Los Angeles sales.
TIP: OLAP stores data in binary format, so it can access information faster and requires smaller files to store.
SSAS Model Types
When you install Analysis Services for the first time, you have three options to choose from: Multidimensional, Tableau, and PowerPivot for SharePoint.
Tabular Model
As the name suggest, it follows the same architecture as the relations dataase. One instance of Analysis Services contains many databases, and each has its own business solution. A single database handles all solutions for one particular business, and we must design that way.
Similar to relational databases, a table in the tabular model is used to load data from a single SQL Server database table or from a SELECT query. The table structure is the same as OLTP systems, with fixed columns and their data types and auto-incremented rows.
We have specified the relationship between two tables at design time because the query won’t allow any table joins. It supports a one-to-many relationship between tables and uses one column to define the relationship.
Tabular Model uses a memory-based engine and stores a copy of model data on disk. So, it avoids any data loss when the service restarts.
Multidimensional Model
In the Multidimensional model, data is organized into cubes and dimensions. Each cube has a measure group and dimension groups where measures point to fact tables, and dimensions point to dimension tables. In other words, each Measure group may have many individual measures, and each measure points to a fact table (example: FactInternetSales). Similarly, each Dimension table will be grouped under the Dimensions section (example: DimCustomer).
A cube has a special dimension called Measure Dimension; it has a combination of all measures from the measure group and dimensions. Apart from this, SSAS automatically creates date hierarchies that we can use for drill-down reports. For example, drag order date to see the sales by year, drill down to see month, and so on.
A multidimensional model can store data in
- Multidimensional OLAP (MOLAP): Data is stored inside Analysis Services’ own disk-based storage format. MOLAP is a widely used multidimensional model to store data because of faster query processing and caching.
- Relational OLAP()ROLAP): No data is stored in Analysis Services. SQL queries run against the relational databases.
- Hybrid OLAP(HOLAP): Same as ROLAP, but aggregated values are stored in MOLAP.
SQL Server Analysis Services workflow
A common workflow includes
Step 1: Download and install SQL Server Analysis Services. Please refer to the SQL Server Installation article to see how to install and configure Analysis Services.
Step 2: Open SQL Server Data Tools (SSDT) or BIDS and use the menu to create a new SQL Server Analysis Services project. Here, select a multidimensional or tabular model template to store data.
Step 3: Use the folder in the Solution Explorer to create a data source, OLAP cube, mining structures, etc.
Step 4: The best part is when you try to create anything (data source, cube, dimension, roles, etc.), it opens a wizard where you need to select the required fields.
Step 5: Data from the relational databases, usually from a data warehouse, is loaded into this tabular or multidimensional model.
Step 6: Deploy the project into a server instance (SQL Server). So it is accessible to other applications.
Step 7: Open SQL Server Management Studio (SSMS) and write MDX queries to work on the model.
SSAS Architecture key components
The key components in Analysis Services are:
- Data Source: We have to specify the source of the data from which we import or parse data into cubes. IT may be an Excel file or a relational database (SQL Server).
- Data Source view: Logical view of the underlying data, same as a dataset. Here, we can see the tables, their relationships, views, etc.
- Cube: It is a collection of data.
SSAS Tutorial
- Create a New Project
- How to Create Data Source
- Create Data Source View
- Add or Remove Tables from Data Source View
- Difference between Star Schema and Snow Flake Schema
- Create Dimensions
- Creating OLAP Cube
- How to Create Cube Dimensions
- Difference between Cube Dimension and Database Dimension
- How to Create Named Calculations
- Creating Measure Groups
TIP: We can use the SSIS ETL tool to transform the data and load into the OLAP cube. Next, use the SSRS reporting tool to design reports from cube dimensions and measure groups.
Where does SSAS fit in Business?
A typical high-level business architecture follows the following model.
- Data coming from different sources (Excel, Files, Databases, etc.).
- Use ETL tools like SSIS or Informatica to transform the Step 1 data and load it into a data warehouse.
- If the data is not that huge, you can slice and dice the data here. However, if the data is huge, load it into the SSAS cubes. When the data is huge, always prefer to load into an SSAS model.
- Use any reporting tool, including SSRS, Power BI, or Tableau, to design reports based on this data.
Advantages of SSAS
- Since the OLAP system (SSAS) is designed for data analysis, it supports faster data analysis because it is read-only.
- SSAS supports partitioning cubes to improve performance and make them more manageable. Each partition works independently, and we can query it.
- Because of pre-aggregated data, more complex calculations are lightning fast.
- When designing reports, it is easy to use these dimensions and measures because the data is pre-aggregated; reports render data quicker and more effectively. It avoids human errors because there are no manual joins and aggregations.
- SSAS is compatible with reporting or visualization tools including Power BI, EXCEL, Tableau, and so on.
What is Multidimensional Expressions (MDX)?
MDX is short for Multidimensional Expressions, a powerful query language for working with OLAP cubes (SSAS data). MDX is the standard expression language and the most widely accepted query language for multidimensional queries and calculations.
If your goal is to work with the SSAS cube Measures and Dimensions, you must rely on these MDX queries. For example, you can find the average sales of all members or siblings. Although Microsoft introduced DAX to support Power BI, MDX remains a powerful, preferred language for working with multidimensional and tabular models.
TIP: The first step is to open SQL Server Management Studio and connect to Analysis Services using localhost or the named instance. Next, write a query against the OLAP cube.
How to work with MDX?
As we all know, a cube holds multidimensional data, including columns, rows, pages, sections, and chapters. Generally, we use SSMS or BIDS to write and execute MDX queries, and they have only an x-axis and a y-axis. So, when we query an OLAP cube, we must explicitly specify what we want to display in the result set.
For example, Sales and Profits (Measures) in the Columns and Country or Category (Dimensions) on the Rows. By this, the MDX query shows two columns (sales and profits) and one row field (category or country). Similarly, we can specify the other three axes.
TIP: We can use numbers where 0 = Columns, 1 = Rows, etc., but the order should be maintained. It won’t allow skipping any number.
This MDX, or multidimensional expressions, page shows the list of available functions to work with the cube. For the query demonstration, we use the Adventure Works cube.
MDX Functions list
- Ascendants
- AVG
- BottomCount
- BottomPercent
- BottomSum
- Children
- ClosingPeriod
- Cousin
- FirstChild
- FirstSibling
- Head
- LastChild
- LastSibling
- LastPeriods
- Lag
- Lead
- NextMember
- Non Empty
- NonEmpty Function
- OpeningPeriod
- ParallelPeriod
- Parent
- PeriodsToDate
- PrevMember
- Siblings
- Tail
- TopCount
- TopPercent
- TopSum
TIP: If you are working with Power BI tool, instead of MDX, you need DAX functions. So, Please refer to the Power BI tutorial to understand the DAX methods.
Comments are closed.