Home » SSAS Tutorial For Intermediate
5 / 5

Steps to Do an SSAS Project

data-source-i

Data Source

data-source-view-i

DSV

dimension-i

Dimension

fact-i

Fact

cube-i

Cube

deploy-i

Deploy

connect-olap-to-app-i

Interface

Is This Valuable Training For Me?

Level of Education

  • From Intermediate To Advanced

The Amount of Coverage

  • From Defining The Data Source

  • To Deploying The Final Output

Type of Training

  • Step By Step

  • In The Form Of Infographics

  • In The Practical Environment

  • Based On Real Examples

This is a Complete Guide to SQL Server Analysis Services (SSAS) For Intermidiate | 2022.

So let’s go!

Data Source

data-source-in-ssas

Entry Point

Welcome to The Second Phase Of the SSAS Tutorial

We Read The Data From Various Sources

Up to here, you have gotten familiar with the main SQL Server Analysis Services (SSAS) concepts.

Now let me do the whole project process and work in the SSAS Cube environment in 6 efficient steps based on these concepts.

This practical section is equal to reading a 400-page book in the field of Microsoft Analysis Server, which I have prepared smartly and usefully in the form of these six steps!

I promise you that you will learn all the parts necessary for every Analysis Services project up to the point of delivering an actual industrial project.

Goal

Read initial data for analysis of them

Data Type

OLTP or Data Warehouse

The data source can either be OLTP data that we receive directly but must clean before analysis or data from a clean data warehouse.

Another point is that these data are not necessarily all from one place and may come from various sources.

Data Reading Environment

SQL Server 2019

We will read the data from here and store the final results of the analysis here.

Database Name

AdventureWorksDW2019

It was the AdventureWorksDW2019 we downloaded together in the last segment (SSAS Tutorial) and restored in the SQL server environment;

Now, we should read it here.

Data Analysis Environment

Visual Studio 2019

We are going to perform analyses on data.

Tools

Data Sources

Theory Prerequisites

Concepts: Architecture | OLTP | Data Warehouse

Create Data Source

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

Do you remember what we meant to do in the first step?

In this step, we will read the data from various sources.

We should specify the source of the data for the visual studio.

What was the source of our data in this training?

It was the AdventureWorksDW2019.

So we specify the data source at first.

So let’s go!

#1

First, Right-Click on “Data Sources” and select the “New data source” option.

create-data-source-in-ssas-01

#2

Click next In the Data Source Wizard window.

create-data-source-in-ssas-02

#3

Select the second option and click on the “New” button in this section.

We want to create a new connection.

create-data-source-in-ssas-03

#4

The Connection Manager window opens;

This window is where you need to specify your server name to connect.

  • Click the “Server Name” to see the list of servers.

  • Click the “Connect to a Database” to see the list of databases on that server.

  • Click the “Test Connection” to ensure that the connection between the data and server works properly.

create-data-source-in-ssas-04

#5

If everything is alright and the connection is made, we get a message indicating that the link has been created successfully.

Click on “ok” and hit “ok” again.

create-data-source-in-ssas-05

#6

You see here that the name of my server and its database appears.

Click next.

create-data-source-in-ssas-06

#7

Select the option of “use this service account” and click “Next.”

create-data-source-in-ssas-07

#8

Here I should specify a name for the data source, and I enter “AdventureWorksDW2019”. Then,

I hit “finish.”

We need this name in the next step.

create-data-source-in-ssas-08

Summary

Let’s summarize our discussion up to here:

What was the output of this step?

Specification of the source of the data we will perform analysis on, which was AdventureWorksDW2019 in our case.

First, we specified which server this database is on, then selected our desired database from that server.

#Final_Result

What you see right now is what you see by the end of this step in this section.

create-data-source-in-ssas-final-result

Data Source View

data-source-view-design

Entry Point

Now we get to the part where we should define a principled map for our project.

We Design a Map for Our Project.

In this step, we should answer the following questions:

  • Which tables do the data we have include?
  • Which tables are dimensions, and which tables are facts?
  • Which facts and dimensions does our project need?
  • Are the available tables enough to create our facts and dimensions?
  • And other questions of this sort.

Goal

We determine the project map or the schema.

(Connecting Facts and Dimensions)

Data Source

Dimensions and Facts defined in step 1

The Theoretical Concept

One of the Popular Schemas

Such as Star | Snowflake | etc.

Schema Name

Star-Schema

The reason for its name is that it has a Fact table in its center with various Dimensions connected to it directly.

Environment

Visual Studio 2019

Tools

Data Source View (DSV)

Theory Prerequisites

Concepts: Schema

What is Data Source View?

The data source view (DSV) is where we add the desired facts and dimensions based on the schema and specify the relationships.

What is Star-Schema?

The star schema resembles a star, as I already mentioned.

Its name is because it has a facts table in its center with various dimensions connected to it directly.

Create Data Source View

So let’s go and create this schema using the DSV.

So we will implement the map based on the star schema and the DSV on the data source we specified.

#1

First, Right-Click on the “Data Source View” and select the “New Data Source View.”

create-data-source-view-in-ssas-step01

#2

The data source view wizard opens where you should click next.

create-data-source-view-in-ssas-step02

#3

You should introduce the data source you created in the previous step to the DSV. We hit “next.”

create-data-source-view-in-ssas-step03

If you remember, I chose the data source name the same as the database name (AdventureWorksDW2019), which I decided on in this section.

#4

We should select the tables and views we need and add them to our project.

Select the tables I need and click on the button specified in the image below (02) to move them to the right.

Finally, I click Next.

create-data-source-view-in-ssas-step04

This window is where we should select the dimensions and facts based on which we design the schema.

Among the available tables, I need three dimensions and two facts. The dimensions I need are:

  • DimDate
  • DimProduct
  • DimsalesTerritory

The facts that I need are:

  • FactInternetSales
  • FactResellerSales

I need these items, which I choose by clicking on them. To select multiple tables simultaneously, all you should do is hold down the CTRL key and select your desired tables. Now we should add these selected tables to our project.

#5

We consider a name for our DSV here; I choose “ADventureWorksDW2019” again and click “finish.”

create-data-source-view-in-ssas-step05

Summary

Let’s summarize our discussion up to here:

What was the output of this step?

We specified the project map or the schema.

Here we can see the dimensions, facts, and how they are connected and change them based on project requirements.

We can add new fields and even new tables whenever we need to.

#Final_Result_01

So as you can see in the figure, my DSV is created under the name I chose.

create-data-source-view-in-ssas-final-result1

All I have to do is double-click on it and see the output.

#Final_Result_02

You see here that DSV is nothing but a graphic representation of the existing objects.

create-data-source-view-in-ssas-final-result2

As mentioned earlier, the star schema structure connects the dimensions to the facts directly.

If you pay attention, this structure resembles a star which is the reason why this schema has been named after stars.

star-schema

Now, we can move on to implement the third step.

Ready?

Let’s go.

Dimension

dimension

Classification of Dimensions

First of all, let me make a general classification for the types of dimensions.

We talk about each of them, how to create them, and their differences.

We have two types of dimensions in general:

  • Database Dimension

The dimensions we define here at the solution level.

  • Cube Dimension

The dimensions that we create in the cube.

Now, what do these mean?

Let’s go and do the work in practice so you can learn.

In the following, we will create the dimensions based on the three-dimension tables we defined in our schema.

To create the first dimension (SalesTerritory), follow the following stages:

Goal

Making Dimensions

Data Source

Dimensions Defined in Step 2

Environment

Visual Studio 2019

Tools

Dimension

Theory Prerequisites

Dimension & Hierarchy

Create Dimension

So let’s go and create the first Dimension.

So we will implement the map based on the star schema and the DSV on the data source we specified.

#1

We right-click on the dimensions and select “New dimension” from the open menu to create a Dimension.

create-dimension-in-ssas-step01

#2

Then, the dimension wizard opens. We hit “next” and move forward.

#3

Among the options, this one called “use and existing table” will be used for our work.

We hit “Next.”

create-dimension-in-ssas-step03

More information

In this window, we see four options telling us how the dimension can be created, and we should choose amongst them.

The reason for choosing the first option is that we already have the dimension tables in our schema, and we will use them.

Just as easy.

#4

I should specify four types of information for the wizard:

create-dimension-in-ssas-step04

More information

  • The name we chose for the DSV was Adventure Works DW2019, which I choose here.
  • Since I want to make a dimension out of the DimSalesTerritory table with the same name, I select the DimSalesTerritory table from the list.
  • The SalesTerritoryKey is the primary key in the DimSalesTerritory table, so I consider this column the primary key here.
  • We consider the SalesTerritoryRegion as the Name Column here.

We set all the features we wanted available in the dimension and will hit next to carry on with the work.

#5

We should specify the list of the attributes that need to be displayed in the dimension.

create-dimension-in-ssas-step05

More information

  • Tables are made out of several attributes, and here you should specify which of the features from the list displayed here must be available in the dimension.
  • This column entitled “Enable Browsing” has been selected by default;

    if you unselect it, it will no longer be visible to the end-user when they see the dimension.

  • The attribute type column’s task is evident from its name.

    If you want to change a specific column, you can do it using this.

    Of course, this tool tries to show the correct values based on the type of the column, and you do not have to make the adjustments yourself most of the time.

    So I do not change anything in this section either.

#6

We select a name for our dimension. I have chosen “Dim Sales Territory” for it, and you can use any name you want. And we hit the finish in the end. 

create-dimension-in-ssas-step06

Dimension Design Environment

So let’s go and work whit this Environment.

Now if you pay attention here, you can see that every time we open a dimension by double-clicking on it, the design environment opens on the left which is the exclusive environment for this dimension where we do the respective work on it.

Let’s have a look at this environment.

We see the name of the dimension up here and the word design inside a bracket that specifies its mode.

#0

Let’s have a look at this environment.

dimension-design-environment-00

#1

We see the name of the dimension up here and the word design inside a bracket that specifies its mode.

dimension-design-environment-01

#2

This environment has four tables:

  • Dimension Structure

  • Attribute Relationships

  • Translations

  • Browse

dimension-design-environment-02

#3

The first tab, which is Dimension Structure, includes three columns:

  • Attributes

  • Hierarchies

  • Data Source View

dimension-design-environment-dimension-structure

#4

Attributes indicate the attributes existing in this specific dimension.

You see here that this dimension includes three columns we specified when we created it.

dimension-structure-attributes-tab

#5

The second column is called hierarchies and specifies the hierarchies, one of the critical topics in dimensions we will discuss.

Here we can specify the hierarchy for any dimension that needs hierarchies.

For instance, we can make hierarchies for dates.

dimension-structure-hierarchies-tab

We will talk more about this and how to create this later.

#6

The third column, Data Source View, displays the main table based on which we created the dimension.

Here you can see the DimSalesTerritory with all its attributes.

dimension-structure-data-source-view-tab

Now to carry on with the dimension, there is a fundamental concept called hierarchy you should familiarize yourself with and learn how to create.

After understanding this concept, we will implement it in the dimension we just created.

Create Hierarchy in Dimension

Suppose the customer should not have to select the Date generally. He wants to divide the Date into periods such as year, semester, season, month, week, and day.

What is Hierarchy?

Suppose the nature of this need is that the customer should not have to select the date generally when an analysis is going to be performed.

What does this mean?

It means that:

  • The customer wants to be able to divide the Date into periods such as year, semester, season, month, week, and day.

  • He wants to make complex decisions and have access to this capability to drill down.

  • Ha wants 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 daily sales and so on.

This is called a hierarchy and needs to be implemented in many analytic projects.

When Do We Use Hierarchy?

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.

What is User Defined Hierarchy?

As we said, this is called hierarchy in the SSAS 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, in fact, a group of features connected logically.

Now let’s go and learn how to implement it practically.

#0

To create a hierarchy or any other change and adjustment to the dimensions, you should enter the dimension’s design environment.

The same thing you are seeing right now and I said you can open it by double-clicking on any dimension.

dimension-design-environment-00

#1

In this environment, you see several tabs, one being the “Dimension Structure.”

By selecting this tab, you see three windows among which we need the “Hierarchy Pane” to build a hierarchy in this section.

dimension-structure-hierarchies-tab

#2

As you can see, it tells you here to drag an attribute to create a hierarchy.

So that is what we do.

I drag the three attributes we need from the left section and drop them right.

create-hierarchy-in-dimension-step01

#3

We should take care not to forget the hierarchy.

So we want to have the group in the highest level followed by country and then region or the Sales territory key.

So I come and place them in the same order.

Here you can use their names or right-click each attribute and change their terms by clicking the “rename” option; I use the exact words.

So we created a hierarchy for these three attributes:

create-hierarchy-in-dimension-step02

#4

If you pay close attention, it shows me a warning.

It means some part of the job has remained undone.

It means some part of the job has remained undone.

What do you guess it is?

We should explicitly introduce these relationships to this tool.

If you remember, we already discussed the relationship between these three attributes but have not specified it here yet.

So, where should I do this?

In this section, attribute relationships.

This is what this tab does.

It asks us to determine the relationship between the attributes to know who is connected to whom and how. Then it will implement the hierarchy correctly.

So we come and specify the same thing we said about the relationship between attributes here too.

Let’s review that relationship again.

Each group contains several countries, and each country includes several regions.

Let’s tell the same thing to the tool.

#5

We right-click in the empty space of this section and select “new attribute relationship.”

#6

I say that every group includes several countries.

I start from the right.

Every group contains several countries, so I placed the group on the right and the country on the left.

#7

We also said that each country contains several regions.

So just like before, the country will be on the right, and the region will be on the left.

So I right-click again and select the first option.

Then say that each country contains several regions here and click ok.

#8

This is all the relationships we needed to introduce to the tool are implemented here as you can see.

Read it from the right to the left:

  • Each Group includes several Countries

  • Each Country has several Regions

#9

Now let’s go back to the dimension structure tab; we see that the warning sign is gone.

This indicates that our hierarchy has been correctly specified and implemented.

Practice 

We created the hierarchy for the dimension so far and I told you everything you needed to know about it.

Now to make sure that you learned it right, make a hierarchy for the Date dimensions with this hierarchies:

Years> semester> quarter> month

Summary

Let’s summarize our discussion up to here:

So we implemented one of the three dimensions on the map.

We learned that all we had to do was to request the creation of a dimension from the “Dimension” section.

  • Then specify which DSV we want to use,
  • Which tables from this DSV do we need,
  • Which fields of the tables do we want it to fetch for us,
  • And eventually, choose a name for it.

We can now see the created dimension if we go to the dimensions section.

#Final_Result

Look here; this is the dimension we created under “dim sales territory.”

create-dimension-in-ssas-final-result1

You can see the dimension prefix, “dim.”

create-dimension-in-ssas-final-result2

You can see The dimension symbol on its left indicates this is a dimension.

create-dimension-in-ssas-final-result3

Cube

Entry Point

Well, we have everything up to here:

  • We read the data

  • We designed the schema.

  • We also implemented the dimensions based on the schema

  • And we eventually created the required hierarchies inside the dimensions

And our work is clean and perfect since we designed the map correctly from the beginning and followed it.

Now we should place them in a specific OLAP database.

This means that we are going to implement the second component of the third step which is the cube.

Now you realize why we determined some steps for SSAS projects and said we should move forward based on them.

We said that each step has a specific goal you should understand first and then do everything required in that step correctly so that the work moves forward professionally and smoothly.

So let’s have a review:

In the previous section, we created the three dimensions we needed and applied the hierarchies the project required.

Now we should place them in an OLAP database.

Remember that the cube acts as an OLAP database.

Since we are going to make a cube in the following, we should specify the list of the facts and dimensions that fall into this cube.

The process of the work is just as simple as the rest of it.

Now let’s create our cube.

Goal

We should place Dimensions & Facts in an OLAP database (Cube).

Data Type

OLAP Database

Data Analysis Environment

Visual Studio 2019

We are going to make a cube in the following.

we should specify the list of the Facts and Dimensions that fall into this cube.

Tools

Cubes

Theory Prerequisites

Concepts: Cube

Create Cube

To create a cube, you should go through the following steps:

#1

Just like before, the first thing we do is that we find the object we need.

Since we will create a cube here, we should take action from the part called cubes.

We should right-click on it and select the “new cube” option from the open menu.

create-cube-in-ssas-part1

#2

The cube wizard then opens where all you should do is hit next and go to the next step.

create-cube-in-ssas-part2

#3

In this window, three options ask us to specify how the cube will be created.

Since we have created the dimensions and facts already and have them, we select the first option, “use existing tables,” for making the cube.

create-cube-in-ssas-part3

#4

Now we reach the stage where we should specify the facts and dimensions.

It asks us in this window and the next one to determine the Facts, and then we should define the Dimensions for it.

So what did it ask of us in the first part of this window?

It tells us to specify the data source.

This refers to the map we created in the second step.

All I should do here is select our DSV.

I select the DSV here.

Now, what is it showing me down here?

It shows me the list of the dimensions and facts defined in this data source.

What should I select in this section?

I should select the facts.

So I selected these two facts, including Factinternetsales and Factresellersales, as the project’s facts.

create-cube-in-ssas-part4

#5

The following window concerns this fact table and shows the fields of the fact table you selected.

Since we selected both fact tables in the previous step, it now shows both fact tables and the fields in each of them.

Here we should specify which measures of each fact we need.

Each measure does something special, and you should select the calculations you need here based on your work requirement.

Now I will check all of them and go to the next stage.

create-cube-in-ssas-part5

#6

You should select the dimensions that you need in the next section.

We created three dimensions, and I will choose all three of them and hit next.

create-cube-in-ssas-part6

#7

Looking at the title of the next window, you will see that new dimensions are discussed.

Here it is saying that you can create new dimensions from the tables in the DSV if you want to.

Since we have created all the dimensions we need in the previous step, we do not need to make any new Dimensions here.

So we deselect them and click next.

create-cube-in-ssas-part7

#8

And now we get to the final window in the creation of a cube where we should select a name for our cube, I choose Adventure Works DW2019 again.

create-cube-in-ssas-part8

Let’s look at the dimensions and facts that we have too.

You see here that the connection between the Dimensions and Fact has been made through the Primary key and Foreign key (The primary key in the Dimension and its repetition in the Fact as a foreign key).

For example, the ProductKey field is the primary key in DimProduct, and the same field is in the Factinternetsales; this has made the connection between the two tables.

Now before anything else, let’s take the fourth step and store the result of the work.

Deploy

deploy-in-ssas

Entry Point

Remember that I said we keep switching between the visual studio and SQL server environments?

And I said that the reason was that each of the environments is meant for a specific goal.

  • We create and implement components such as the schema, dimensions, hierarchies, facts, and the cube in the visual studio.

  • After doing these, we should take the cube, our database, and all the dimensions and facts in it to the SQL Server.

By doing this, we create an OLAP database, and whenever we need the data from this database, all we need to do is to connect to the database and make our requests.

What is Deploy?

The act of taking the cube from the visual studio to the SQL server is called deployment.

Doing this creates an OLAP database in the SQL server under a specific name.

Goal

Taking the Cube from the Visual Studio to the SQL Server

Data Type

OLAP Database

Data Analysis Environment

Visual Studio 2019 & SQL Server

Doing this creates an OLAP database in the SQL server under a specific name.

Tools

Deploy

Deploying the Cube

The following steps must be taken for deployment:

#1

We should right-click on the name of the project and select the last option, which is called properties.

deploy-project-in-ssas-part1

#2

A window then opens with multiple tabs on its right.

  • We go to the Deployment tab, where we should choose the name of the SSAS server.

  • Since I am doing the work on my computer, I consider the “Localhost” value for it.

  • In the Database section, I should specify a name for the OLAP database, which I have chosen to be Nill2Bill.

  • After making the initial settings, I hit ok.

deploy-project-in-ssas-part2

#3

Now everything is prepared for the deployment.

All I have to do is right-click on the name of the project again and click deploy.

deploy-project-in-ssas-part3

The operations above lead to the deployment.

I am updating the article… If you want to be informed about the update of the article, please enter your email in the box below:

Written by: afrashteh

Get Exclusive SSAS Tips

That I Only Share With

Email Subscribers

Leave A Comment