Microsoft SQL Server Analysis Services allows the developers to provide a unified access of data for analytical analysis and reporting. Developers can manage and aggregate the queries from multiple subject areas. This can be achieved by creating a cube in Visual Studio 2008. This post defines and explains the terms used in creating an analysis project in VS 2008.
1. Create an analysis project by opening the BIDS from SQL server 2008
2. You will get the following dialogue box after clicking the BIDS from above screen
3. Create the project with name Analysis Service AW and the the project structure will look like the following
4. Adding a Data Source
We can add a Data Source by defining the connection string information that we used to connect the Data Source.
Right click the Data Sources Folder and then say New Data Source
Click Next in the above wizard to see the following screen
Here I am using the existing connection string for AdventureWorks2008 database for connecting the Data Source. Click the Finish button then you can see the chosen Data Source in Data Source folder.
5. Creating a Data Source View
Data Source View is set of related tables, queries and views from one or more data sources. DSVs works without a active connection to the Data Source. It is basically based on OLAP model where Meta Data contains from different data sources.
Right Click the Data Source Views and say New Data Source View then you see the following wizard.
6. Click the Next button this will bring the following screen which lists all tables and queries in the selected Data Source.
I have added the SalesOrderHeader Table and then clicked on the Add Related Tables button, it shows the related tables in above dialogue box.
7. Click on finish button then it will add Adventure Works.dsv to the project folder.
8. Right Click the Cubes folder and then say New Cube you will see the following dialogue there you select the DSV and click next button
9. Select the Measure Group tables from the following screen after clicking the next button from above dialogue
Select the Data Source required DSV and select one measure table from DSV then click Suggest button it then checks the relevant tables for the Cube and click next button to see the following screen
Click Next and then say finish. You will see the cube in project folder
10. Now you can deploy the cube to SQL Server Analysis Services as follows
11. After Deploying the solution, you will see the following screen
|Share this post :|