In this new guide, I am going to introduce:

  • Step 1: How To Download SSAS, 

  • Step 2: How To Install SSAS,

  • Step 3: How to Learn SSAS (Self Study)

  • Step 4: How to Use SSAS Projects (Case Studies)

  • Step 5: How to Find the Best and Latest SSAS Training Course

  • Step 6: How to Find the Best and Latest SSAS Job Opportunities

Indexes

SSAS Tutorial
Chapter 3

SSAS Tutorial (self study)

SSAS Projects (Case Study)
Chapter 4

SSAS Case Study

SSAS Course
Chapter 5

SSAS Course

SSAS Job Opportunities
Chapter 6

SSAS Jobs

Contents

SQL Server Analysis Services Download

Step 1 To become a SQL Server Analysis Services specialist, you must first know all the tools you need to do this and download them in order.

Note: SSAS cannot be downloaded separately but is a service installed on SQL Server and Visual Studio.

Here are the tools you need to work with SQL Server Analysis Services and download them first:

SQL Server Download

[Step 1]

SSMS Download

[Step 2]

Visual Studio Download

[Step 3]

SQL Server Analysis Services Install

Step 2 To become a specialist in SQL Server Analysis Services is to install the software that you downloaded in step 1 in order and in principle on your system.

In the continuation of this article, I want to teach you very simply and fluently how to install the tools you downloaded in the form of a checklist.

And if you want to learn the installation process of these tools in detail, you can also use the following articles:

SQL Server Install

[Step 1]

SQL Server Install

[Step 1]

SSMS Install

[Step 2]

Visual Studio Install

[Step 3]

Step 1SQL Server 2019 Install

14 Steps to Install SQL Server 2019:

  • Installation > New SQL Server stand-alone installation

  • Product Key > Enter the 25-character key

  • License Terms > I Accept the License Terms

  • Global Rules > Next

  • Microsoft Update > Use Microsoft Update to Check for Updates > Next

  • Install Setup Files > Next

  • Install Rules > Next

  • Feature Selection > Select The Components That You Want To Install > Next

  • Instance Configuration > Select Either Default Instance or Named Instance

  • Server Configuration > Determine The Usernames and Passwords | Choose Your Desired Language Next

  • Database Engine Configuration > Choose the authentication mode > Specify locations for storing files

  • Ready to Install > See a Summary of All SQL Server Components > Install 

  • Installation Progress > Next

  • Install Completion > Close

If you want to see how to install SQL Server 2019 in full detail, I have prepared a detailed article in this regard for you, which you can read through the following link:

Step 2SQL Server Management Studio (SSMS) Install

3 Steps to Install SQL Server Management Studio (SSMS):

  • Double-click on the downloaded file

  • Change the SSMS installation path if necessary > Install 

  • Close

If you want to see how to install SQL Server Management Studio in full detail, I have prepared a detailed article in this regard for you, which you can read through the following link:

Reference Article: How To Install SSMS 2019

Step 3Visual Studio 2022 Install

6 steps to install Visual Studio 2022:

  • Visual Studio Installer > Continue

  • Choose the components you need

  • Specify the installation location

  • Install

  • Please Waite …

  • Finish

If you want to see how to install Visual Studio 2022 in full detail, I have prepared a detailed article in this regard for you, which you can read through the following link:

Step 4SQL Server Analysis Services (SSAS) Install

As we said before, SSAS is a service that we have to install on SQL Server and Visual Studio (during installation or after on the existing version).

Adding SSAS To An Existing SQL Server Installation      

To add SSAS to an existing SQL Server installation, follow three steps when installing it on the Feature Selection page:

  • Check the Analysis Services > Set Instance root directory> Next

  • Server Configuration > Service Accounts tab >Set Account Name > Startup: Automatic > Next

  • Analysis Services Configuration > Server Configuration tab > Select one of the modes (Multidimensional and Data Mining or Tabular) > Add Current User > Next

  • Reay to Install > Install

  • Complete > Close

Adding SSAS To An Existing Visual Studio 2022 Installation      

At this moment, Visual Studio 2022 does not include extensions for Analysis Services, Integration Services, or Reporting Services projects.

However, it is still supported in Visual Studio 2019 and Visual Studio 2017.

To add SSAS to an existing Visual Studio 2019 installation, follow three steps:

If you want to see how to install SQL Server Analysis Services in full detail, I have prepared a detailed article in this regard for you, which you can read through the following link:

  • Click To Extensions > Manage Extensions.

  • Look For “Analysis Services.”

  • Add The Extension “Microsoft Analysis Services Projects.”

SQL Server Analysis Services Tutorial

Step 3 To become a SQL Server Analysis Services Specialist is to learn it. So far, you have downloaded and installed the required software using steps 1 and 2, and now you need to learn it.

In step 3, we are going to learn SSAS professionally in a very simple language, away from complex aspects.

If you remember, this was supposed to happen to you as a self-study. So I have considered all aspects to prepare the training for you in a way that is very easy for you to learn.

Reading Data
[Step One]

Design
[Step Two]

Construction
[Step Three]

Deploy
[Step Four]

Debugging
[Step Five]

Interface
[Step Six]

Step 0SSAS Concepts

SSAS Architecture      

Tell me the beginning and the end of the movement.

If you understand the SQL Server Analysis Services architecture, which is very simple, you will fully understand all my talk about SSAS below.

That’s why I chose it as the first part for you to enter the world of SSAS.

Architecture tells you where to go, what to do along the way, and what tools to use to get to our final destination (technically) and the customer (in terms of requirements).

So we start with the same mentality.

Figure 1: SSAS Architecture

As you can see in figure 1, the SSAS architecture contains four layers:

  • Layer 1: there is initially some data that we will analyze. This section is known as a data source.
  • Layer 2: This data could be in various formats (including Excel, Access, SQL Server, Oracle, etc.). So they first need to be cleaned up using specialized ETL tools (including SSIS, which is similar to SSAS, another Microsoft business intelligence service) and put in place called a data warehouse (DW).
  • Layer 3: Data from several sources should be combined and analyzed. As a result, this data will store in a database known as an analytical database. “cube” is the name for this type of database. This layer and the next layer are the subjects of all of the tutorials in this Article.
  • Layer 4: Everything is now in place for customers to use specialized applications (Including POWER BI, mobile application, website, etc.) to access this type of analytical database.

OLTP Concepts      

It all starts here!
Our entry point based on the introduced architecture is OLTP data. For convenience, consider a hypothetical organization that collects data daily through its SQL Server (or any other database management system similar to Oracle).

This data has been stored in this place over time, and as mentioned, due to the type of business they have, the data may not necessarily be in the same place and may be scattered in different areas.

Now that the data has been analyzed, better judgments can be made due to those analyses.

So here, you need to be a little bit familiar with these concepts as far as T-SQL language, table, and database are concerned (familiarity only).

If you have worked with relational databases and are familiar with the concept of a table, you know that a table consists of some rows and columns.

The data will be stored in this table in the form of rows and columns. That is, the column becomes the name of our fields (such as name, surname, phone number, etc.), and for each person, these columns are completed, the sum of which for each person forms a row. Now for the next person, the columns related to that person are completed the same way, and the information about that person includes another row.

As an SSAS specialist, almost everything you need to know about the databases world is what we learned together.

Almost everything is defined and stored in the form of these concepts. 

Data Warehouse      

OLAP Concepts      

The term “online analytical processing” refers to analytical data sources and analysis systems.

The basic notion and expectation connected with the term OLAP is that it contains multidimensional data and the environment in which the data is hosted.

Cube

in the OLTP world, what was the first word we spoke?

It was the “database.”

Well, we also have a concept in the OLAP world called Cube. So consider Cube as a database, but this time as an analytics database.

Dimension

In the OLTP world, we said that tables should be created within the database based on the needs of the project.

In the OLAP world, we have the same tables. Only here the concepts have changed.

In other words, they divided the tables into two general categories:

  • Dimension
  • Fact.

But in the end they are the same tables.

MDX

But we come to the last case, the MDX language.

In OLTP, we needed a language that could save, retrieve, and so on data stored in tables.

In OLAP, after analyzing the data, we need to use the outputs of these analyses. For example, suppose we need to fetch the result of an analysis and display it to the end customer in an application. All this is done using the MDX language.

So T-SQL is a query language for OLTP data, and MDX is a query language for OLAP data.

Now let’s summarize so that we can talk here:

In the OLTP world, we had a concept called a database, and within that database were several tables, each tasked with storing specific data.

In the OLAP world, we had a concept called a cube, and inside this cube were two sets of tables (Dimension and Fact), each of which was intended to analyze specific cases.

And in the OLTP world, we had a language that we used to query data.

In the OLAP world, we have a dedicated language called MDX.

This summarizes the basic SSAS theory concepts that I have tried to present in straightforward language.

Now we are going to go into the exciting part of the article and learn how to do the whole process of doing an SSAS project in six steps (The most important and beautiful part of this article.)

So let’s go!

So that you are familiar with the basic concepts of SSAS, let me explain the whole process of doing a project and working in the SSAS environment in 6 steps and in an efficient way, based on these concepts.

After learning these six basic steps, I will teach you how to implement them in practice (in the SSAS environment).

When you want to do an analytical project with SSAS (both for practice and real industrial projects), you need to do the following six steps in order:

Step 1Read The Data From Different Sources

In general, when we talk about data analysis, it means that there is a set of data before which we will perform a series of examinations.

For what purpose?

To be able to intelligently know the current state of the customer’s business, work a little more professionally, and predict the future (or both).

Tip: SSAS can provide us with intelligent reports on the current situation and talk about the future. This is fabulous.

So when we are going to do some analysis on data, we conclude that there has to be data already, and we have to get that data in some way in the first place.

Step 2Design A Map For It

There is an accurate plan behind each surviving building. Do you agree with me?

For our work to be conscientious from the beginning, we need a plan that connects the components of our project (Dimensions and Facts) morally and correctly, and based on that plan; we can do the following work with a better view.

So in the second step, we have to connect this data intelligently to each other.

What does it mean?

If you have been following this from the beginning, we said above that in the OLAP (SSAS) world, we have the same tables; we only give meaning to these tables and divide them into Dimension and Fact.

After this, these Dimensions and Facts must communicate with each other in some way.

There are several popular ways to communicate between them, including Star, Snowflake, etc. The names given to these schemes are based on how the Dimensions and Facts relate.

For example, what does a star mean?

This means that the relationship between Dimensions and Facts is similar to that of a star. That is, consider a Fact that is logically at the center and some Dimensions are connected to it, which creates a connection between them.

Now what dimensions relate to what facts depends on the purpose of the project.

Star Schema: Dimensions relate directly to Facts.

This step is called Schema, which is required in all SSAS projects, and you should be familiar with popular schemas and how to work with them. For example, the type of star we mentioned earlier is called star-schema.

Step 3We Start Building According To The Plan

In the third step, we have to practically implement the Dimensions, Facts, and Cubes in the Visual Studio 2019 environment based on your designed scheme.

In the following, I have prepared a complete and checklist of implementation steps in the Visual Studio 2019 environment for you.

You must read after completing this section and implement it on your system.

This practical part is equivalent to reading a 400-page book in the field of SSAS, which I prepared cleverly and adequately in the form of a small article!

Step 4Save The Created Project Somewhere

In the fourth step, you need to save the result somewhere.

In the third step, you did the implementation work in the Visual Studio 2019 environment. You probably know that the Visual Studio 2019 environment is used for implementation and not storage.

So here, we need a storage environment that does both the storage work and the object type is OLAP.

I guess so far, your mind is busy with SQL Server, and now you better understand why in SQL Server environment there are both OLTP and OLAP modes!

Here, we have to take the analysis done in the Visual Studio 2019 environment into SQL Server 2019.

But which SQL Server environment do we need? OLAP environment.

So to summarize: in Visual Studio 2019, we do the implementation work and get the result in the SQL Server environment.
Now here, if you have designed an application in which we will display the output of tasks, with whom should this application communicate?

Yes, you guessed it, it needs to communicate with SQL Server.

This process is called Deploying. Straightforwardly, we transfer the result from the Visual Studio environment to the SQL Server environment.

Step 5Get The Final Leaks Done

We have to catch the leaks in the one-step left to the final step and delivery the final project to the customer (step five).

Each technical project may have problems while working and receive errors from different parts.

I made this one of your first steps to becoming a SQL Server Analysis Services expert, not to worry if you get multiple errors while working. This is obvious and will happen to everyone involved in the project (at any level).

You should know that mistakes and warnings are evident in all projects and will happen constantly.

As a SQL Server Analysis Services expert, once you are familiar with this, not every mistake will worry you, and worse, it will not disappoint you.

What is the solution now?

First, you need to know that there is an error.

Then you should be familiar with some of the common mistakes that occur during projects.

There are some limiting errors that you will experience while working.

I named this step “Project Leak Detection,” You, as a SQL Server Analysis Services expert, need to consider this in your estimates.

Step 6Connect The Final Result To The Client Application

In the last step (six), the result must be provided to the end customer.

The issues we have raised so far are specific to you as an SSAS expert. This means that the client neither knows Visual Studio nor SQL Server nor is willing to do so.

 The customer expects to be provided with a simple program that can be informed of the current state of his business by choosing several options. For example, by selecting a specific date, one of the particular branches, and establishing a particular product, the total amount of sales can be viewed graphically and beautifully.

Or, if it has the possibility of forecasting, a list of offers will be given to the customer by selecting a series of products by the customer. In this way, the customer will have more purchases, and such a business will be profitable. Easily implemented with SSAS features – you’ve probably seen this in the Amazon Store a lot).

The final output in the interface phase depends on the purpose of our project.

We can use specialized software in this area (such as Power BI) and create output in applications that the customer has (such as a website). Both of these modes are enabled by SSAS features.
The exciting and great point is that you can easily do these things in the SSAS environment from zero to one hundred and almost as a wizard.

Do you think this is not wonderful?

We talked about the 6 steps to becoming a SQL Server Analysis Services specialist in the previous section.

If you have not read this part, I suggest you go back and read it first, then read and enjoy the continuation of the discussion, which is the implementation of these six items, with better understanding.

Otherwise, read on to continue the discussion, which is very exciting, and we are going to implement everything together in a very clean checklist.

So let’s go, my friend!

Step 0Have you taken everything?

In 6 cases to become a SQL Server Analysis Services expert, we learned that the first step is to get the data of the project we are going to work on and in our own technical environments (ie SQL Server and Visual Studio) To use.

Well, I know you are learning SSAS, and you do not have the data. You also understand that it is impossible to continue working and implementing the following steps without the initial data.

We are also aware that some friends are beginners and do not know how to set up SSAS and create a project.

So I’m paying attention to these friends too, so in step zero, we will provide all the necessary tools to start learning SSAS first, and then we will go and do the rest.

In this section, we intend to:

  • Download some free data (AdventureWorksDW2019 database),
  • Restore data to our system,
  • Let’s talk a little about the tables in this database (Dim and Fact)
  • Learn how to create and run an SSAS project.

In the following, we will work on the same project and the same data as presented in the previous six steps.

So what is the solution now?

Do not worry. As I promised, I will be with you until the end of the road, and I will do everything for you. So trust me in all parts. OK, my friend ?!

So now, what do we need to do to continue our learning and to take step zero?

Download AdventureWorksDW2019 Database      

Microsoft has provided sample databases for different versions of SQL Server and other purposes. These free samples are pre-filled with data, and you can easily download these samples based on the type of work you have (OLTP or OLAP and a Lightweight version).

You can also download your SQL Server version for free from the Microsoft website.

The version we need is AdventureWorksDW2019, which is the perfect version for SSAS tasks.

 This version has Dimension, and Fact tables, whose Dimension tables are prefixed with Dim and Fact tables are named with Fact prefix. This version is very suitable for the learning process of SQL Server Analysis Services, and we can meet almost all the data needs we need at the beginning of learning through this data.

So by clicking on the link below, download it first:

Download AdventureWorksDW2019 Database

[Step 1]

Restore the AdventureWorksDW2019 Database      

After downloading this database, if you look at the end of its name, it has a .bak extension.

This extension indicates that this file is a BackUp version and must be restored in the SQL Server environment.

Use the checklist below to restore the SQL Server database.

By doing this, the entire database with all the tables inside it is restored and ready to use.

8 steps to restore AdventureWorksDW2019 database:

  • Right-Click on the Databases Folder > Select The “Restore Database…” Option

  • Select The Device Option > Click On The “…” Button To Choose Your Backup File

  • Select the Backup File > Click On The Add

  • Determine The Backup’s Specific Location > Select The Backup File > Click On OK

  • Click On OK

  • Click On OK Again

  • You Will See “The Restored Database Will Add To The Available Databases In SQL Server”

As you can see, the names of the tables in this database are prefixed with Dim and Fact, which are suitable for Dimension tables with Dim prefix, and Fact tables with the prefix Fact are appropriate.

If you want to see how to restore the database in SQL Server 2019 in full detail, I have prepared a detailed article in this regard for you, which you can read through the following link:

Create and Launch an SSAS Project      

Before we do anything in Visual Studio, we must first create a SQL Server Analysis Services project.

Then open the Visual Studio environment and create a SQL Server Analysis Services project using the checklist below:

3 Steps to Create and Launch an SSAS Project:

  • File > New > Project

  • Analysis Services Multidimensional And Data Mining Project

  • Project Name > Create

Step 1Read The Data From Different Sources

Now that we have everything ready, it is time to start our work.

Do you remember what we were supposed to do in the first step?!

In this step, we are going to read the data from different sources.

Why?

Because we are going to analyze them.

In what environment?

In the Visual Studio environment.

7 Steps to Specify the Data Source in SSAS (Checklist):

  • Right-Click On The Data Sources Folder > New Data Source…

  • Next

  • Select “Create a Data Source based on …” Option > New

  • Determine Server Name > Select Database (AdventureWorksDW2019) > Test Connection > Ok

  • Next

  • Select “Use The service account” > Next

  • Determine Data Source Name > Finish

7 Steps to Specify the Data Source in SSAS (Images):

Step 2Design A Map For It

Now we come to the part where we talk about the middle ground.

In this section, we have to answer the following questions:

  • Determine what tables the data we have included?
  • Which tables should be selected as Dimension and which one as Fact.
  • What dimensions and facts does our project need (according to our perception of the project business).
  • Are the available tables sufficient to create our Dimensions and Facts or not?

In the Visual Studio environment, we have to create the design of our project map based on the Dimensions and Facts in the Data Source View environment, using the Star-Schema, which is suitable for our work.

5 Steps to Specify the Data Source View (DSV) in SSAS:

  • Right-Click On The Data Source View >Select New Data Source View

  • Next

  • Select The Data Source We Just Created > Next

  • Select Tables That We Intend To Use In Our Solution > Next

  • Determine Data Source View (DSV) Name > Finish

Step 3We Start Building According To The Plan

In this part, we have to create our dimensions based on the stellar sketch structure we created.

As we saw in the previous section, we have five tables, of which three are Dimensions, and two are Facts. So we need to create three Dimensions separately.

So we need to create three Dimensions here (Product, Date, and SaleTerritory).

To create the first Dimension (SaleTerritory), perform the following steps in the Visual Studio environment:

5 Steps to Specify the Data Source View (DSV) in SSAS:

  • Right-click the Dimensions folder > New Dimension

  • Select “using an existing table” > Next

  • Select the DSV we created earlier in the DSV selection > select table > Determine key column > Next

  • Select attribute > Next

  • Determine Dimension Name > Finish

SQL Server Analysis Services Project

Step 4 To become a SQL Server Analysis Services Specialist is

Written by: afrashteh

Get Exclusive SSAS Tips

That I Only Share With

Email Subscribers

Thank you for your message. It has been sent.
There was an error trying to send your message. Please try again later.

Leave A Comment