Data Source View
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.
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.”
#2
The data source view wizard opens where you should click next.
#3
You should introduce the data source you created in the previous step to the DSV. We hit “next.”
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.
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.”
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.
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.
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.
Now, we can move on to implement the third step.
Ready?
Let’s go.
Written by: Moslem Afrashteh
Get Exclusive SSAS Tips
That I Only Share With
Email Subscribers