SSAS - Creation Of First Cube

SQL Server Analysis Services:

Open SQL Server Business Intelligence Development Studio.


This is the software to create ETL packages, Cubes etc. We have to create a new project here. Types of projects can be seen in the below window.
Click on Analysis Services Project and create a new project. We can create a folder in local system and create a directory for the project.



The project window appears as below. In the Solution Explorer we can see the structure which contains Data Sources etc. We can create cube, dimension, connection to data sources etc.
Creating a Data Sources:
Right click on Data Sources, create a New Data Source

Click Next and select New, here we can define the connection of data base

Here we can see where the data located, you can give server name.
Here I selected local machine server name: BLRDXP-SGOURISH
Specify the Authentication and select the database name (select sample database name)
AdwentureWorkDW.

Test the connection then click ok.

Here we can see the data source name then click on Next.














You can define what credentials Analysis Service will use to connect to the data source.
Select Use the service account.

Click on Next
Here we can give the data source name. (I given SSAS_DS)
Click on Finish button.
Create new Data Source View:
In Data source view, we can add tables which can be used for development. We need to pull the tables from the database and put them in the database view. We can connect to multiple data sources and pull the tables, for that we need to add new data sources to the project.










In solution explorer right click on the Data Source Views and select New Data Source View.


We will get Data Source View Wizard window
Click on Next




Select the created Data Souce.Then click on Next
You can see all the tables and views in selected data source.
Select required tables in to included objects window then click on Next


Here I selected five tables then click on Next then give the data source name.
Then click on Finish button.

After this you can see the diagram which is having the relation ship between tables.



The arrow marks in the design window represents the relationships. Double click on the arrow mark to see the relationship window




Right click on a table and click on explore data to see the data present in the database for that table














Here we can create additional table, logical relationships and new named queries.

Here we can customize the DSV in two ways
1. Create calculated columns.
2. Replace with Named query.

Named query is better because it more flexible adding joins and filters.
When convert to named query, doesn’t bring calculated columns among.
Create Dimensions:
Right click on the dimensions and select new dimension




The new dimension wizard will appear


Select the Built method

When u building a cube we have an option auto build
It will built attributes and hierarchies automatically.
But here I uncheck the auto build check box then click on Next





Here we have an option selecting the dimension type, and select standard dimension

Standard dimension, example: product, employee.
Time dimension, we have year, qtr, month from a database table
Server time dimension: based on the server date we can create.

Select the key column and select the member description for the key.
Click on Next

We can figure out here related tables will appear.

Select the required columns (attributes) in that dimension.
Click on Next
We need to specify the dimension type, select the Regular dimension
Click on Next
Define if any Parent Child relationship inside this tables
Click on Next
Here we can give the Name of the Dimension and click on Finish button.

In solution explorer we can see the created dimension




After this we will get window which is having dimensions, attributes and hierarchies.
All the dimensions are in blue since attributes are selected one from each dimension table. We can rename the attributed in the Attributes list.





In the Hierarchies window we can specify the hierarchy.

Click on save all icon.
No actual data is populated yet. Now we need to create Meta data into the dimensions.
For that, Right click on the Project Name (SSAS_Project) and select properties.
Give the server name then click OK.
Then right click on the dimension click Process.

We will get

Click on Yes.
















If you go and check in Management studio
It will ask to connect to server-- Analysis server
Click on connect,
You can see in the databases we will have our project SSAS_Project
Here right click on the dimension and select browse then you will not get any data because the data is not populated yet.
Go back to BIDS (Business Intelligent Development Studio)

Now we are populating the data into dimension.
Click on Run

You can see the Process progress like succeeded. Then click on close.
Again close the window.

In output window you can see the message.
Now you can test the data by right clicking on the Product dimension and select Browse.

As per the created hierarchy you can see the data.

Building the Cube:
In the Solution explorer Right click on the cube folder click on create New cube

Here we can see the same options.
Click on next
Then select Data Source View
Click on next
It will detect all the tables and relationships
Click on next
Here we can find out fact and dimension tables.

We can define the time dimension table ,

Move the available dimensions in to cube dimension



Specify the time table columns like Calendar year, Calendar Quarter and month name etc.
Click on next




Here you can select measures that you want to include in the cube.
Click on next


Here I can see the time dimension and hierarchies and attributes.

Click on Next
Give the Cube name and select finish button.
We can see the Time dimension also in Dimension folders.
The facts will appear in yellow color in the Data Source View window.

Now we can see in Management studio, right click on the project and click on refresh.
In cube folder we still not yet get the build cube.
Again go back to BIDS; here we can deploy the cube.
For that right click on the project click on the Process
Click on yes.


Deployed successful complete.
Again go back to Management Studio and check the deployed cube is available or not.
In BIDS,
Click on Run then Process Progress window will appear here you can see the Status.
Click on close button.
In the Measures window we can see the selected measure.
We can define the properties also. For that right click on measure select properties.

Below are the options available for modifying, adding new cube features like measure, attributes hierarchies and levels.




To browse the data use the browser. We will see a cross tab like structure and we can see the data there.
We can check the same data in Management Studio also. Right click on the cube select browse option.