OLAP
Entry Point
Now we want to enter the OLAP world from the OLTP world.
This Is The Real Deal.
We want to use OLTP concepts and their relations with OLAP to better understand the SSAS concepts.
Elements
In the world of OLAP, there are four basic concepts that we will learn in detail below:
Cube
Dimension
Fact
MDX
Now We Create An Analytical Database.
Analytical Database
An OLAP Database uses a multidimensional structure to facilitate rapid analysis and retrieval of data.
The term multidimensional refers to combining multiple Dimensions and Measures into a single database that can be queried and analyzed.
What Is an OLAP Database?
OLAP is short for Online Analytical Processing, a type of database management in which data is stored in a multidimensional structure instead of a tabular format.
This type of database is called a Cube and contains two main features (Fact & Dimension) I will explain below.
Example
AdventureWorksDW database is a Microsoft product sample for a Cube database.
AdventureWorks Database
Dimension
Now we have to determine from what Dimensions we will analyze the business.
For example, we want to know the number of sales of a particular product on a specific date.
So here, Product and Date are our two main dimensions.
What is a Dimension?
Dimensions are the most common way of organizing data. They provide large amounts of rich information about a specific area of interest.
Example
Suppose you are going to launch an SSAS project for a store.
You first need to imagine what is happening in this Store’s real world (away from the IT world).
You agree that:
A Customer enters the Store on a specific Date and buys Products.
Other customers behave the same way. Their data is stored in a database (by the software already written for this purpose).
So you probably guessed that we have Four Tables, each responsible for keeping the above information.
- A table for storing Customer information,
- a table for the Date,
- a table for Store maintenance,
- And a table for Products.
Note: Suppose the Store is a chain, and we have several stores.
As you might have guessed, to analyze this particular example in detail, we need to look at it from four dimensions.
So we need Four Dimensions.
DimCustomer,
DimDate,
DimStore,
DimProducts.
Fact
Now we have to determine our intended output which has a computational nature.
In this example, we wanted to get the sales amount of a product as an output. Therefore, we consider the total amount of sales as a Measure. Now, these measures (Calculation Fields) may be many. Finally, these fields are placed in a table called Fact.
What is a Fact?
A Measure is a calculation tool in the cube. Each of these measures performs a calculation task.
In an analytical project, we may have a number of these measures that together form a measure group.
These measure groups are included in a term called Fact. Since there is no difference between these terms, they can be used interchangeably.
Example
“Sales Amount” is a measure that calculates the total amount of sales in the store.
SalesQuantity and SalesPrice are separate Measures, each of which performs a particular calculation task.
The sum of these measures together forms a Measure Group (Fact).
Therefore, we can conclude that in this example, we have a Fact named:
- FactSales
Measure Types
So far, you are familiar with Measures.
Now we must have a classification of the measures themselves.
In general, we have two types of Measure:
Base Measures
These types of measures are created directly from the fields of the Fact table. In other words, the measures we had so far were of this category (for example, Sales Amount).
Calculated Measures
The second category of measures is Calculated Measures.
Sometimes it is necessary to create a series of custom calculation fields based on business needs and use them in different parts.
Therefore, it is necessary to add some logic to the primary measures and create a new measure (Calculated Measure) according to the new requirement.
Microsoft SQL Analysis Services provides us with a tool for this purpose; with the help of this tool (Calculations) and MDX language, we can quickly create any Calculated Measure.
Of course, it should be noted that you don’t need to code with MDX in this section. It provides you with a visual environment where you can add measures by Drag & Drop and apply the required formula to them.
Behind the scenes, this tool itself generates MDX codes for you.
MDX
So far, we have determined the analytical database, the dimensions required for the analysis, and the computational work.
Now, whenever we need to access this output, we have to send a request to this database. So here, we need a Query Language that is specific to this type of database.
This language is called MDX, and we send all our requests to Cube through this language.
What is an MDX?
MDX (Multidimensional Expressions) is a query language for working with OLAP Databases (similar to T-SQL language in OLTP Databases).
All the requests that we need to be made on this type of database are done with the commands of this language.
Example
Cube = Adventure Works
Measures = Sales Amount & Tax Amount
Dimensions = Date & Sales Territory
SELECT
{
[Measures].[Sales Amount],[Measures].[Tax Amount] } ON 0,
{
[Date].[Fiscal].[Fiscal Year].&[2002],[Date].[Fiscal].[Fiscal Year].&[2003] } ON 1
FROM [Adventure Works]
WHERE ( [Sales Territory].[Southwest] )
Visualization
In the last step, we have to show this final output to the user differently.
Example
The Final Output in the previous example is usually a number determining the Business’s Total Sales (for example, $17,659,000).
This number should be displayed to the customer as a user-friendly output.
To show this result to the customer, we can use the following tools:
PowerBI Dashboard
- Mobile Application
Web Application
FAQs
SQL Server Analysis Services (SSAS) is an OLAP tool that you can use to build Cubes from data stored in OLTP Databases or data warehouses.
This lets you perform faster analyses of your business data.
To do this, it’s necessary to carry out a process from the first stage (data collection) to the last step (showing the final result to the user).
1. It is a tool for analyzing businesses from several dimensions (using the concept of OLAP Database)
2. It is a data mining tool.
These two applications make the business both aware of its current situation and able to know about the future of its business. Based on this knowledge, better decisions are made.
#1 Understanding The Tools Required To Set Up SSAS
#2 Knowing The Primary Sources Of Data For Analysis (OLTP And DW)
#3 Mastering How To Implement The Analytical Database (Cube)
#4 Mastering How To Implement Dimensions And Facts In The Cube
#5 Mastering Analytical Database Query Language (MDX)
#6 Finally, Mastering How To Connect The Final Result To The Interface
#1 We Download And Install The Required Tools.
#2 We Prepare The Data Source For Analysis (OLTP Or DW)
#3 We Create An Analytical Database (Cube)
#4 We Implement Dimensions And Facts In The Cube
#5 We Send Business Requests To The Cube (MDX)
#6 We Connect The Final Result To A Beautiful Interface (Powerbi, Web, Mobile, Etc.)
Cube = AdventureWorks
Measures = Sales Amount & SalesQuantity & SalesPrice
Dimensions = Date & Customer & Store & Products
Multidimensional & Tabular
SSAS is used for business analysis. Therefore, it needs a concept that can examine that business from several dimensions.
The cube can do this because of its components (Fact and Dimension).
No.
A data warehouse is one of the data sources that SSAS can use (similar to OLTP).
The difference between this data source and the OLTP type is that the data is clean because the ETL process is performed on this type of data.
Yes.
SQL Server has several services, each of which has a specific use.
One of these services is Analysis Services, which causes SSAS to be installed.
The schema defines how dimensions and facts are logically related.
Famous examples of the schema are:
- Star
- Snowflake
OLAP Summary
What we reviewed together as a summary of OLAP components.
The whole process of an SSAS project is like this. Each of them has its details and a different way of implementation.
Before entering the intermediate stage of SSAS training, you must familiarize yourself with these concepts. Therefore, I suggest that you first read the following sources.
I carefully designed these resources to help you master this part of the training.
Introducing Resources
Written by: Moslem Afrashteh
Get Exclusive SSAS Tips
That I Only Share With
Email Subscribers