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 2022
Tools
Dimension
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.
#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.”
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:
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.
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.
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.
#1
We see the name of the dimension up here and the word design inside a bracket that specifies its mode.
#2
This environment has four tables:
Dimension Structure
Attribute Relationships
Translations
Browse
#3
The first tab, which is Dimension Structure, includes three columns:
Attributes
Hierarchies
Data Source View
#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.
#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.
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.
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.
#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.
#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.
#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:
#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.”
You can see the dimension prefix, “dim.”
You can see The dimension symbol on its left indicates this is a dimension.
Written by: Moslem Afrashteh
Get Exclusive SSAS Tips
That I Only Share With
Email Subscribers