It is a Microsoft Business intelligence tool to creating Online analytical processing and data mining functionality. We can create an OLAP (online analytical processing) cube using SQl server analysis system.
Looking for top jobs in Business Intelligence ? This blog post gives you all the information you need!
Why do we use SSAS:
It is a technology that stores the data in optimized way to easily fetched from different types of complex queries by using various measures and dimensions. We can develop the OLAP cube using the BIDS (business intelligence development studio)
Follow the below steps to run the query in SSMS (SQL Server management studio)
OLAP Cube developing:
Step 1: Start Business intelligence development studio environment.
Start menu –> Microsoft SQL Server 2008 R2 –>Select SQL Server BIDS
Step 2: Start analysis services project:SSAS can create and occupy a physical table in the data source it will use to occupy the dimension maintained in the SSAS databases ,On the specify source information.
File–> New –> Project –> Business Intelligence projects –> select Analysis service project –> give project name –> Click OK .
Step 3: Creating New data source
Right click on Data source –> Click New Data Source
Click on next new button.Create the new connection
Select Connection created in Data connections –>next –> select option Inherit –> next –> assign data source name –> finish
Step 4: Creating New data source view: We create a data source view (DSV) as an abstraction of the tables from the data source. In solution Explorer, right click on the Data Source view then click new data source view to create the new one data source view,after that click on next and select relational data source then next.
Right click on Data Source view –> New data Source view –> next –> Select Relational data source which we have created previously i.e., Sales_DW –> next
shift Fact Table from available objects to Included objects
Select FactProductSales –> Add related tables –> next –> next –> Assign Name (SalesDW DSV) –> Finish
Now data Source view is ready to use.
Step 5: Creating New Cube
In solution Explorer –> Cube (Right click) –> New cube –> Next –>.Select Use existing tables –> Next –> FactProductSites –> Next –>Choose measure–>Next –> Select all the Check box in select new dimensions –>Next –> Assign cube name –> Finish.
Now Cube is ready
Step 6: In Solution Explorer, Click on dimension DimProduct à drag and drop the product name
Step 7 :Deploy the Cube
In the Solution Explorer, right click on project name –> properties –> In configuration properties, Select Deployment –> Assign SQL Server Instance Name –> Deploy All –> Do not process –> OK.
Right click on Project Name –> Click Deploy
We can see the message Deployment Completed in properties
Step 8: Process the Cube : Right click on Project Name –> Process
Click on Run to process the cube after completing the process , we can see the status as process succeed –>close .
Step 9: Browse the cube for analysis
Right click on the cube name à browse
Follow the steps to browse our cube
Download Interview Questions asked by top MNCs in 2019?