SSAS Architecture
OLTP
Data Warehouse
OLAP
Interface
Everything Starts Here!
The Garbage Must Be Put Away
This Is The Real Deal
Everything Does End Here!
Launch SSAS
Tools Download
Tools Install
Initial Setup
Project Creation
This is a Complete Guide to SQL Server Analysis Services (SSAS) For Beginners | 2022.
This is a Complete Guide to SSAS for Beginners | 2022.
So let’s go!
SSAS Tutorial For Beginners
This tutorial will teach you all about SSAS from scratch to the final project delivery in an application format (such as mobile, web, etc) and with no prerequisites.
In this SSAS training reference article (SSAS Tutorial For Beginners), we will get familiar with the specialized SSAS expressions here.
Topics that you should learn step by step as a beginner are:
- SSAS Architecture
- OLTP
- Data Warehouse
- OLAP Cube (All details step by step and practical)
- Interface (How to connect the built model to any type of application)
- Download All the Necessary Tools (SQL Server, SSMS, Visual Studio, and SSAS Extension)
- Installation and configuration of tools (Analytical Services and SSAS Extension)
- Preparing The Ingredients (Such as Downloading & Restoring some free data, Discussing the tables of this database, etc)
- Establishment & Creation Of the first SSAS Industrial Project
If you have an interest in becoming a specialist in the SSAS field, I would like to tell you that you have chosen the right job because it is one of the most popular jobs in the field of business intelligence.
In addition, there are a couple of thousands of jobs for this position in the United States and other countries.
According to Talent, the average income of an SSAS specialist in the United States is as follows:
SSAS is a great way to learn how to manage your data!
SQL Server Analysis Services (SSAS) gives you in-depth visibility into your business. It integrates data from multiple data sources and provides insights that are personalized and tailored to organizational goals.
I will try to tell you about them extremely easily and quickly.
I have taken advantage of my experience of many years here to discuss everything and be easy and enjoyable for you.
Most importantly, everything is based on a framework to not lose your path. So that every time I am talking about an expression, you can quickly figure out where this concept is used, not just having learned the word and not knowing where it is used.
So, what did I do, and where did I start to become a researcher in this field?
I start with the architecture.
Do you agree that when you know the architecture of something, you find it easy to recognize where it starts and ends? Because that is what architecture tells us. It is saying that this concept starts here, these things are happening inside it, and it should eventually end here.
So I will tell you about the SSAS architecture first.
Then I will explain the components of the architecture in the order they are placed in architecture.
There, you will understand that the SSAS architecture is made up of four general parts in the same order mentioned in the picture.
- OLTP
- Data Warehouse
- OLAP Cube
- Interface
And based on these four items, we reach the 6-step (SSAS Tutorial in 6 + 1 Steps) standard for our work. This standard will act as the basis for our learning and carrying out the projects in this field. We are moving from the whole to details to get to our standard. This is why I am saying that if you want to get the right outputs, you should learn all the lessons well and in the proper order. After you understand the architecture, you will realize that the process of moving in the heart of this architecture is that:
- We get the data from somewhere (OLTP)
- We clean them (Data warehouse)
- We design them to be prepared for our analysis
- Save the final result in a database with an analytical nature
- Finally, we deliver the final output to the customer in a beautiful interface
Each of these has details of its own which we will learn in order. By the end of this article, you will understand the concept behind what I am saying, friends. So let’s see the first part of the article together.
Architecture
SSAS Gateway
The gateway to SSAS is to understand its architecture.
Tell me the beginning and the end of the trip.
The architecture tells you:
#1
Where the starting point is?
#2
What is done during the journey?
#3
Which tools do we take advantage of to reach the final desired destination for us and the customer?
You will understand everything we talk about in the following regarding SSAS if you only understand the architecture of SQL Server Analysis Service which is pretty easy.
This is why is chose it in the first part for your entrance to the world of SSAS.
As you can see, this architecture is made up of four parts:
OLTP
Data Warehouse
OLAP
Interface
It starts from the data source.
Some tasks are carried out on the way
Delivering the project to the customer.
We will talk about each of these layers separately in the following:
OLTP
Definition
There is a series of data we are going to conduct some analysis on in the beginning. This part is called the data source (OLTP).
Tools
SQL Server
Oracle
Access
Excel
So on
Data Warehouse
Definition
The data must be cleaned using specialized ETL tools and be put in a place called a data warehouse (DW).
Tools
SSIS
Note: SSIS tool is another commercial intelligence service from Microsoft similar to the SSAS tool.
OLAP
Definition
The data collected from other sources must be analyzed. So these data will be put in a type of database called the analytical database.
This type of database is called an OLAP Cube.
Tools
SSAS
Interface
Definition
Now everything is ready for the customer to access this type of analytical database through specialized applications (Interface).
Tools
Power BI
Windows Application
Web Application
Mobile Application
This part is no trouble for us and is easy to carry out.
We will talk about all these.
Do not worry and move forward step by step with me until the end of the training.
Are that okay friends?
Note
Customer applications such as what?
Customer applications such as:
Power BI
PowerBi connects to our analytical database which is the cube created in the previous step.
It reads the data from there and displays them to the user in the form of beautiful management dashboards.
Windows Application
We can connect a Windows application to it and display the data in the form of a Windows application.
Web Application and Mobile Application
We can display the output in the form of a web application, mobile application, or any other thing the customer has asked for.
Summary
Let’s summarize our discussion up to here:
So this is the whole SSAS architecture and we figured out what needs to be done in an SSAS project.
What is the point of entry?
Where should we start the work?
What should we do at the heart of the work?
Where should we store the results?
And what should we eventually deliver to the customer?
Now let’s go and learn about each part of this architecture in detail.
Are you ready?
So let’s go.
OLTP
Entry Point
According to the introduced architecture, our entry point is data of the OLTP nature.
Everything starts here!
Assume a hypothetical organization that collects daily data through its SQL Server (or any other database management system similar to Oracle).
#1
These data have been stored in this place over time.
#2
The data may not even be in one place and be scattered in various points
#3
Now, some analyses are going to be conducted on these data based on which better decisions are to be made.
#4
The OLTP is our point of entry for analysis.
#5
The data we collect from the customer in projects to analyze are usually OLTP data collected over the years.
#6
Now we receive these data from a specific customer and implement the analysis using analytical tools such as SSAS based on them.
Elements
In the world of OLTP, there are five basic concepts that we will learn in detail below:
Database
Table
Field
Record
T-SQL
So let’s get familiar with these concepts as much as we need.
Database
Definition
Suppose you want to record the data on a specific university somewhere.
You will definitely say that you should design and implement a database for this system in the beginning.
Why?
Because you know that a database is similar to a box containing the personal items of each student inside it.
This box prevents these items from being mixed up with other people’s items.
Example
For instance, we have a database called University:
University
Table
Definition
Now you should put the relevant items inside the box.
So, we design some tables for the hypothetical university, and each table considers several needs.
Example
Inside this database, we have three tables called:
Professor,
Course,
Student.
Field
Definition
It is probably needless to say that these tables have some fields. The fields store certain information on each student, course, and professor.
This section is called by various names such as:
Field
Trait
Feature
Example
For instance, “Student” table is made up of the four fields:
StudentNo,
FirstName,
SurName,
IDCardNo.
Record
Definition
For each field, we have entered some values, and the combination of these values makes up a row.
So when we complete all the fields, we are, in fact, completing the information on one person, the sum of which becomes a record.
Example
For instance, “Student” table is made up of the four fields:
StudentNo,
FirstName,
SurName,
IDCardNo.
T-SQL
Definition
Now the database and the respective table are made.
We must perform some operations on them.
Functions such as:
Storing some new data in these tables
Fetching some values from the tables
Or other operations of this kind.
Well, you may have guessed the solution.
Here we need a specialized database language to perform these operations on the objects inside the database, such as the table.
For example, working in the SQL Server environment will know that the specialized language to do so is T-SQL.
Example
Three examples of the T-SQL statement:
Select Surname From Professor
Select StudentNo From Student
Select * From Course
Golden Tip
We, Gotta, Use “Tables” For Our Case and Nested Case.
Other Data Sources
Are the data delivered to me as input for the analysis always this kind of data (OLTP)?
The answer is No.
There might be a business that has obtained the data from these sources and stored them in a data warehouse.
They are now asking you to analyze them.
They are clean and ready.
When we are talking about data warehouse, it means:
#1
We have practically passed the OLTP data through some filters
Filtered Data
#2
We Cleaned them based on some parameters.
Purified Data
#3
We have brought and stored the cleaned results in another database.
Data Ready for Analysis
This data is much more valuable to work with than the OLTP data from the beginning.
But I must say that most of the works available are OLTP, a large portion of which are dirty and require you to do the cleaning initially.
You will figure out later that in an analytical project, the greatest time has to be devoted to this part which is cleaning the data.
Enter dirty data into the analysis stage without cleaning them. You will get an unfavorable output regardless of how professionally you have done the rest of the job.
You know what they say: garbage in, garbage out.
FAQ
Introducing Resources
Summary
Let’s summarize our discussion up to here:
This is the world of the relational database.
Almost everything is defined and recorded within this form.
Data Warehouse
Entry Point
The second layer you saw in the architecture is the data warehouse.
The Garbage Must be Put Away.
Now you may have two questions:
Question #1
Why we talked about this in the previous section?
Answer #1
The reason is that some data must be available for us to be able to start the analysis.
The data are usually a series of OLTP data collected over months or years.
But we should note that a large part of this data might be dirty.
For example,
The one who has been in charge of data entry may have entered some wrong values.
We may have some missing values since the user had entered no value or other issues of this kind.
These are user errors, but we must admit that the data are not always clean, and such problems might exist.
There are often a lot of such issues in large projects.
Even if we take the following steps with extreme caution and professionally, our final output will still not be suitable if we fail to resolve this issue in the first place.
So what was the solution?
We should clean the data.
Sometimes we might have to spend 60% or more of the project time on doing this.
Some projects already have data from which the organization might have decided to make a data warehouse.
They have cleaned that data using filters and specialized tools to store the clean data.
Now they deliver those data to you for analysis.
Your work would be more accurate, and your time will not be wasted on cleaning anymore.
But a few projects are actually like this.
So the fact that we talked about this concept in the first section which was about the data sources was because there might be a data warehouse as well.
Golden Tip
We, Gotta, Use “Fact” and “Dimension” For Our Case and Nested Case.
Question #2
Why do we see it in the second section after in OLTP in the architecture?
Answer #2
When we got the project and our data were OLTP, we said there was a high possibility of dirty data.
You should make sure to clean them before entering the analysis stage.
Now, what is the solution?
It is to clean them using specialized tools in this field, such as SSIS.
Store the results in a data warehouse.
Use these new data and not the old ones in the following steps when performing analyses.
Golden Tip
We, Gotta, Use “Table” For Our Case and Nested Case.
Summary
So I think you got it.
Now you understood why I said: “you should get rid of the garbage” in the beginning.
So this is the concept of DW that lies in the second layer of SSAS architecture.
Now let’s go to the third architecture layer.
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
We will talk about each of these elements separately in the following:
Cube
Definition
The cube is the basic storage and analysis unit in Analysis Services.
A cube is a grouping of data that has been consolidated to allow queries to return results fast.
OLAP Cube is a cube that has dimensions and measures.
Dimensions categorize the data by time, geography, product lines, etc.
Measures are numeric values that can be aggregated and calculated over the different dimensions of the cube.
How to Identify
Cubes are classified according to their dimensions and measurements.
#1
Dimensions are derived from dimension tables
#2
Measurements are derived from fact tables
Dimension
Definition
Each cube has one or more dimensions. Each of dimension is determined by one or more dimension tables.
A dimension typically has a natural hierarchy, allowing lower results to be “rolled up” into higher outcomes.
In a geographical level, for example, city totals may be aggregated into state totals, or state totals could be aggregated into country totals.
How to Identify
Suppose you are going to launch a Microsoft Analysis Server project for a store.
The first thing you need to do is imagine in your mind what is happening in the real world (away from the IT world) of this store.
Example
Specify the dimensions of a chain store:
You agree that:
#1
A Customer Enters a Store
#2
On a specific Date
#3
Buys Products
#4
While Walking Through The Store
So We Have 4 Dimensions:
FAQ
No, this is not always the case. Sometimes it is necessary to fetch data from several tables and define the result in a new table.
In the SSAS environment and using a concept called Named Query.
Specify Facts
Fact
Definition
A fact table comprises the fundamental facts that you want to summarize.
This might include:
Order Detail Information,
Salary Records,
Medicine Effectiveness Data,
Or anything else that can be Summed and Averaged.
How to Identify
Any table you have used in a totals query with a Sum or AVG function is a fair chance to be a fact table.
We need a table containing the list of the transactions in the business.
Here, the table type is different since we discuss detailed transactions.
The concept of dimensions is no more involved.
Here we need another concept that can give us an output based on our conditions (dimensions).
This table is computational.
We are talking about the detailed transactions of the business.
So the nature of the customer’s need in this section is computational.
Example
Specify the Fact of a chain store:
Do you agree that one of the transactions of this business is that each product sold on a specific date and in a particular store is available on a table?
So we need at least three fields in the table containing the list of the transactions:
#1
Product Field
The field that specifies which product has been sold.
#2
Date Field
This field Specifies on which date the sales have taken place.
#3
Store Field
This field specified where the sales had taken place.
#2
Other Fields
And other fields that the table must have based on project requirements.
The additional fields that the table needs would be the computational fields that must be available based on our needs and sometimes we should design them ourselves.
For example, suppose the customer has asked to total sales, so total sales will be another field of the table.
This will be the computational field in our table which is called “measure” and we will learn about it later.
This table will be our fact table.
You see here that the table contains all three necessary fields and some other computational fields inside it:
FAQ
If you remember, we had them in the dimension table too.
Right?
But they were the primary keys there.
Now all the four primary keys in the dimensions have been repeated here but as foreign keys.
Because we should eventually make connections between dimensions and facts.
Through the primary and foreign keys.
Ok?
Yes.
You must connect them through one of the available schemas (including Star Schema).
In the following, we will learn the types of schema and how to connect dimensions and facts.
Dim & Fact Summary
Let’s summarize our discussion up to here:
If you remember, we had three primary keys in the dimension table.
Right?
Now all the three primary keys in the dimensions have been repeated here but as foreign keys.
Now we should eventually make connections between dimensions and facts.
We do this through schemas.
In the following, we will learn the types of schema and how to connect dimensions and facts.
But before that, let’s look at a professional 7-step process for identifying dimensions and facts in a project.
How to Determining Fact and Dimension?
#1
Project Definition
Our project is an Online Shop (E-Shop).
Suppose you have gotten a project in the field of online shops.
Goal
Determining the Type of Project
Tools
Talking to the Customer
#2
Customer Request
The customer tells you he wants to have a dashboard that displays specific information such as total sales after selecting a particular date, product, and store.
Of course, this is a straightforward example just to understand the concept better.
Well, here we want to know the facts and dimensions that we need.
You should do the exact same thing in the second step.
Consider the customer’s request once again:
he wants to see total sales after selecting a specific date, product, and store.
Goal
Obtain Project Requirements
Tools
Talking to the Customer
#3
Separation of Dimensions and Facts
As a Microsoft SQL Analysis Services Specialist, I should quickly analyze his request in my head.
It does not require you to do anything special Just check two items:
Dimension
Which Parts of His Request are Conditions?
Fact
Which Parts of His Request Involve Calculations?
This is a trick I have learned based on experience, so make sure to pay close attention.
He has given me three conditions and one piece of work involving calculations.
Goal
We divide the available data into two categories:
- Dimension
- and Fact
Tools
Based on the answers given to the two questions mentioned
#4
Design and Implement Dimensions
What Were The Conditions?
He says I want something to happen when I enter a specific:
Date
Product
Store
So these three conditions will be my dimensions.
I should have three dimensions here:
DimDate
DimProduct
DimStore
We can consider another dimension for the customer if he wants to engage in decision-making.
Goal
We design and implement the detected Dimensions.
Tools
MS SSAS Environment | Dimension’s Design Environment
After downloading and installing SSAS Extension on Visual Studio
#5
Design and Implement Facts
What Did He Say Afterward?
He said that he wanted to see total sales after entering these three.
Here, he is talking about computation.
This will be one of the fields in my fact table.
So here, I know that these total sales will be one of the fields of my fact table.
As he goes on talking, I discover which other dimensions and facts he needs.
Just as easy as that.
Ok?
So let’s move on.
Goal
We design and implement the detected Facts.
Tools
MS SSAS Environment | Fact’s Design Environment
After downloading and installing SSAS Extension on Visual Studio
#6
Specify the Hierarchy of Dimensions
So far, we have both the necessary dimensions and the facts.
The two dimensions of Date and Product will be the options he selects.
FactInternetSales calculates the value of the computational field inside it based on the selections in these two dimensions and presents the output to the manager.
Now suppose the nature of this need is that the customer should not have to select the Date generally.
What does this mean?
It means that the customer wants to be able to divide the Date into periods such as year, season, month, week, and day.
It means he wants to make complex decisions and have access to this capability to drill down.
And to be able to top-down and make decisions at the monthly level, for example, as well.
He wants to know how many sales they have had per month.
Or move to the lower layer and see weekly sales and so on.
This is called a hierarchy and needs to be implemented in many analytic projects.
If we notice such a need in the project or the customer asks for it, we figure that we should make some changes to that specific dimension and then prepare it for these operations.
As we said, this is called hierarchy in the Analysis Services world, and if we make the changes manually, it will be called “user-defined hierarchy.”
So I think with the explanations, you figured out the fundamental concept of hierarchy and the fact that hierarchy is a group of features connected together logically
Goal
Create Conditions for More Complex and Accurate Decisions (Going to lower levels, some fields, including the Date field).
Tools
SSAS Environment | Dimension’s Design Environment | Hierarchy Pane
After downloading and installing SSAS Extension on Visual Studio
#7
Specify The Primary and Foreign Keys
First, you need to know that the Primary Keys are in the dimension tables.
Foreign keys are also in the Fact tables.
Now, using these keys, the connection between them is made.
Just as easily.
The Primary Keys are:
DateKey for The DimDate
ProductKey for The DimProduct,
StoreKey for The DimStore
CustomerKey for The DimCustomer.
The same fields (Primary keys) are repeated as Foreign Keys in the Fact table.
These will be the part where the customer will pick their values.
Let’s not forget that dimensions were the same as tables to which we merely assigned concepts here in the Analysis Services world.
So we should say that we need three tables;
for example, the date table which includes all the dates.
For instance, the exact data must be precise, and all the times to the point of hours and minutes must be specified too.
We will even make decisions up to the layer of minutes if the project needs us to.
We will learn this later, which is called the hierarchy, and it should be implemented on that specific dimension if need be.
Goal
Specify the primary and foreign keys for the Dimension and Fact tables.
We use these keys to communicate between dimensions and facts.
Tools
SSAS Environment
After downloading and installing SSAS Extension on Visual Studio
#8
Communicating Between Dimensions and Fact
We need to make a logical connection between the dimensions and the fact.
We implement this connection based on a logical concept called the schema and the identified primary and foreign keys.
Goal
Establish a logical connection between dimensions and facts based on the detected primary and external keys.
Tools
SSAS Environment & a logical concept called Schema | DSV’s Design Environment
After downloading and installing SSAS Extension on Visual Studio
#9
Provide Solutions for Data Deficiencies
Sometimes, we lack a specific table we need for our work.
This is where we need to make it ourselves.
For example, the date table is not as detailed as the customer needs it to be based on the data he has given us.
The good news is that these types of tables are common among most projects, and you can find and download them on the internet and customize them based on your project.
For instance, another table contains all the business products and has these data stored in the product table.
And another table will include the list of stores; suppose they are in the store table.
Sometimes we have to join data from other tables together.
Put the results in a new table (tailored to the project needs).
We do this using a concept called Named Query.
Goal
Create new tables based on joining between existing tables.
What is the purpose of this?
Existing business data with their current structure can not meet customer needs.
So we put their output data in a new table.
Tools
SSAS Environment & a concept called Named Query | DSV’s Design Environment.
After downloading and installing SSAS Extension on Visual Studio
Summary
Let’s summarize our discussion up to here:
Table 2: Comparison of OLTP and OLAP
| OLTP | OLAP |
| Database | Cube |
| Table | Fact & Dimension |
| T-SQL | MDX |
This section summarizes the main theoretical concepts of Microsoft SSAS, which I tried to explain most simply.
Connect App to
OLAP
Entry Point
Do you agree that we do all these tasks to design and implement what the customer has asked for?
And everything must be delivered to them in a clean and visually pleasant framework?
Everything Does End Here.
#1
The customer does not know anything about the cube, dimension, fact, SSAS, or any other specialized terms.
#2
He has no interest in knowing anything about them and does not even have to know any such thing.
#3
He says that we have a series of data (the input according to the architecture), and I want an output (the output based on the architecture).
#4
For example, he says that I want to know about the current status of the business but in the form of a clean graphic dashboard.
#5
The customer asks for some useful outputs to be displayed on the page when he selects the year, store, and product.
#6
The customer asks that “when people enter my website and select two types of clothes, show them some other products at the bottom of the page which they would like and find close to what they need. So they become likely to shop more as a result of which my profits will increase.”
This is called the interface.
It means that
we should make ourselves familiar with the available and practical interfaces at this layer.
We should know how to connect to them and display our final output to the customer.
We will learn all about that.
We will move forward together little by little.
Elements
Connect PowerBI to SSAS
Connect Win App to SSAS
Connect Web App to SSAS
Connect Mob App to SSAS
Connect PowerBI to SSAS
Road Map
To connect the PowerBI to SSAS, you need to work according to the following roadmap:
Tools
Power BI
Type
Desktop & Mobile
Connect
Run The Software
Get Data
Select “SQL Server Analysis Services Database” > Connect
Specify The Server and Database > Choose “Import” Option > Ok
Select Facts > Select Measures
Select Dimensions
Load
Run The Software
Get Data
Select “SQL Server Analysis Services Database” > Connect
Specify The Server and Database > Choose “Import” Option > Ok
Select Facts > Select Measures
Select Dimensions
Load
Connect Win App to SSAS
Road Map
To connect the Windows application to SSAS, you need to work according to the following roadmap:
Language
C#
Type
Windows App
Download & Install Library
There is one better way to add this assembly to your library: using NuGet, the installation process you can do through the visual studio environment. Just remember that when you do this and install the assembly, you can use namespaces.
Install ADOMD via NuGet
Connect to the internet
Enter the Visual Studio
Right-click on your project
Select “Manage NuGet Packages….”
Select the Browse tab
Type “ADOMD”
Choose Microsoft.AnalysisServices.AdomdClient
Click on install
Connect to the internet
Enter the Visual Studio
Right-click on your project
Select “Manage NuGet Packages….”
Select the Browse tab
Type “ADOMD”
Choose Microsoft.AnalysisServices.AdomdClient
Click on install
Connect Web App to SSAS
Road Map
To connect the Web application to SSAS, you need to work according to the following roadmap:
Summary
Entry Point
This Was The Whole SSAS Architecture.
Now you understood the concept of the architecture, which shows show the start and finish points of the movement.
You have also understood the overall process of an SSAS analytical project, including where it should start and where it ends.
You saw here that the architecture is generally made up of four parts, inside which there are a series of concepts you should familiarize yourself with.
OLTP
Data Warehouse
OLAP Cube
Interface
Tools Needed to Set Up
Entry Point
This Is The Beginning of Our Journey.
Did You Take Everything?
We all know that when it comes to taking a trip, the first thing that comes to mind is packing for the trip.
We should prepare the supplies for the trip.
We need a series of tools in our journey which we need to get to know first and prepare afterward.
So let’s see
Elements
SQL Server
SSMS
Visual Studio
#1
Which tools do we need through this journey
#2
Where we should get them from.
Before anything, we need to download some tools.
I am saying some tools here.
This means that we need a series of tools and not just one for our work.
In this section, we discuss what these tools are, why we should have them, and where we should download them from.
Then, I will download them one by one from the main and original resources.
Here, we are going to learn about SASS. You probably think it is the software you should download and install o your system.
Golden Tip
But I should tell you that SSAS is a service that installs on two software.
Question #1
Now, what are that two software?
#1
SQL Server
#2
Visual Studio
We need a database management system on one side to manage the data for us (for instance, so we can read and store data and run queries on them), and this place is called the SQL Server.