In this article we will start with CUBE create a new SSAS project on Visual Studio
Links to other Labs of Learn MSBI Step by Step.
Learn MSBI Part 1:- Creating SQL tables, Practicals of SSIS-ETL(Extraction, Transformation & Loading)
Learn MSBI Part 2:- Performing Validation on Records
Learn MSBI Part 3:- SCD (Slow Changing Data)
Learn MSBI Part 4:- Fact and Dimension.
Learn MSBI Part 6:- Displaying reports using SSRS
Learn MSBI Part 7:- DQS (Data Quality Services)
Learn MSBI Part 8:- Data Profiling Task
Learn MSBI Part 9:- Pivot, UnPivot and Aggregation
Learn MSBI Part 10:- Execute SQL Task component (SSIS)
Learn MSBI Part 11:- Send Mail Task & Script Task Component
Learn MSBI Part 12:- CDC Concept using SSIS
Learn MSBI Part 13:- Asynchronous, Synchronous, Full-blocking, Semi-blocking & No-blocking
Learn MSBI Part 14:- Understanding Page Split and maintaining SSIS Performance
Learn MSBI Part 15:- Debugging in deployment – Instrumentation using Data Taps
Learn MSBI Part 16:- Using Multi-threading and its performance in SSIS program
In order to start with CUBE create a new SSAS project on Visual Studio or do right click on Solution Explorer --> click on Add which will expand its inner option from here select --> New Project.
Adding New Project screen will open where from left side selection Analysis Services option available under Business Intelligence. Once Analysis Services option is selected there are various option available to create project in Analysis. Here in this project select very first option of “Analysis Services Multidimensional and Data Mining” to create SSAS project.
Give a suitable name to the project "SSAS Customer" and then select desired location to save the project finally click on OK.
Once project is created it will be now available under Solution Explorer of Visual Studio as shown in the image below. Once SSAS Customer project is expanded under it there are many other essential folder which are needed to be filled in order to complete SSAS project. Next in this article all project folders will configured one by one and step by step.
Starting with Data Sources
To start filling Data Sources just a right click on "New Data Source" and then select the database server on which SSAS has to be done.
Under this Data Sources define RDBMS the server name by following wizard screen. If you wish to skip welcome screen next time check on the check box.
SSAS will get data from following selected database to do SSAS and run analysis services. To get Server Name go to SQL Server Management Studio as shown in the below image. Copy server name from here and then get back to Visual Studio.
After coming on Visual Studio to define connection click on “New” which will open Connection Manager screen here paste the name of the server and then select the table on which analysis services will be carried out as shown in the image below. Also if wish just test the connection by clicking on “Test Connection” button.
Once found things all correct click on OK to move to the next wizard screen of Data Sources.
Next screen is to give credentials i.e. User ID and Password of windows to analysis services in order to access data source.
Once that is set then click on Next to go to following wizard screen.
Now comes the final screen of the Data Sources wizard just do review that whether it is the same table on which analysis services are going to run.
And then finally click on “Finish” to complete and close the Data Sources wizard screen.
Now under Data Sources of Solution Explorer there is a filled Data Sources which is configured and it is saved with name “Customer Data Ware House.ds”
Data Source Views
It will help to select tables which will be selected or unselected to carry out analysis service. Under Data Source View options are available for selection of tables.
Next folder is to fill up “Data Source Views”. For that do right click on Data Source Views to configure it which will again open wizard welcome screen.
Click Next to move to following screen of Data Source Views wizard.
On this screen if data source is seen then do nothing and just click next on this wizard screen.
Once the Data Sources are selected now select and unselect tables on which analysis is going to be carried out as shown in the following image screen.
Now all tables are selected then those selected tables will move from “Available objects” to “Included objects”.
After that just go through it once for all selected objects and then click on “Next” to move to following screen.
Next will be screen to do a Final preview for all selected tables for performing SSAS on it.
Finally click on Finish to close the Data Source View wizard screen.
A Customer Data Ware House.dsv design page will open which will show tables of Fact and Dimensions along with established relation using primary and foreign key as shown in the image below.
Filling the CUBE:
Once Data Source Views is successfully created next is to create CUBE for SSASCustomer project. For that do right click on Cubes and click on New Cube.
It will open CUBE creation welcome wizard screen which again can be omitted by clicking on tick mark and can be avoided from opening it next time. Now click on Next.
In the following screen here do select CUBE creation method which will be done by doing selection of “Use existing tables”. So in this project CUBE creation is going to be done with the help of existing Fact and Dimension tables.
Once “Use existing tables” option is selected now click on Next to go to following screen.
As CUBE understands in terms of Fact and Dimension so in this screen is from our existing selected table now select the Measure table and then proceed by clicking Next option available at the bottom.
After doing selection for Measure table now next is to mark Measure which will be included in CUBE.
Once selection of Measure is completed next is to select Dimensions from this wizard screen. Just remove Fact table which we had earlier selected in Measure screen and keep marked others as they are Dimension tables.
To move ahead click on Next.
Now its time to have quick preview of what has been done in this cube creation wizard. After having a look on selected tables and its Fact and Dimensions next is to click on Finish to close the window screen on cube wizard.
After finished is clicked then on Solution Explorer and on it Design will see following Cubes and Dimension being created.
Now to process CUBE click on icon as shown in the image below it will prompt to do “Yes” or “No” whether it is being processed for the first time.
This is warning to make in sync RDBMS database and data of SSAS database to process it successfully.
Once clicked “Yes” it will complete deployment successfully. Next it will open screen to process cube as shown in below image snapshot.
Click on “Run” button available at the bottom to start processing of Cube.
Here is the Process Progress screen running as shown in the below image snapshot with Cube process status of succeeding it.
Next is to see how CUBE looks like for that need to go on to Browser as shown the following image if want to see total sales then drag and drop Fact Customer = ”Customer Amount” it will show total amount and if want to see total customer and product wise from Dimension drag and drop CustomerId and ProductId, once that is done it will show Customer Amount total as per Country and Product wise.
Till now CUBE is working very much fine each and every ID are getting displayed successfully on CUBE but still here Country Name, Product Name, States Name and Sales Person Name are not available on Dimension. For that there is a need to configure dimension again to get name in each dimension.
In order to do the same each dimension has to be configured and for that start doing one by one: -
- Go to Solution Explorer and double click on “Dim Sales Person.dim”
- Next is to do drag “SalesPersonName” from Data Source View and drop it on Attributes as shown in the image below.
- To save it click OK.
With the above same steps next configuring of other dimension would be done for Country Name, Product Name and States Name.
Once configuring is done there is again need to process the CUBE by clicking the “process” button.
It will prompt for build and deploy project here click on “Yes” in order to do the same.
After build and deploy project is successfully done will show screen of Fact and Dimension processed as shown in the image below.
Next is to
- Click on the Browser to see the result of the CUBE produced by each dimension.
- Next is drag and drop down “Customer Amount” from Measures.
- Followed by now drag and drop one by one each dimensions as shown in the image below and get to see changing the Fact values of it accordingly.
In this manner with different combination results can be seen on CUBE.
Also view below video on Learn MSBI Project Series.