A Data Source View in SSAS is a set of tables or views from the data base that are required to design the cube (We can simply called this as data set). Analysis services can only access the tables or views inside the Data Source View, anything outside the Data Source View is not accessible. Some important properties of analysis services data source view are:
- One data source view can pull data from only one data source.
- Analysis services allows us to create multiple data source views from single data source.
- Analysis services allows us to create data source views from multiple data source.
- Any changes made in the Data Source View such as changing table names, removing few unwanted columns will not reflect the underlying database.
For example, If the data warehouse contains 100 tables and we want only 20 tables to design the cube then there is no point in adding 100 tables. Instead of that, data source view provides space to add those 20 tables.
TIP: One data source view can pull data from only one data source.
NOTE: Any changes made in the Data Source View such as changing table names, removing few unwanted columns will not reflect the underlying database.
Creating Data Source View in SSAS
To create data source View, Right click on the Data Source View folder present in the solution explorer and select the New Data Source View option from the context menu
It will open the Data source view wizard with the welcome page. If you don’t want to see this welcome page again check mark the Don’t show this page again option below.
If you already created data source then it will be displayed like above or you have to click on New data Source button to create new one. Please refer SSAS Data Source article to understand how to create data source. For this example, Let me select the existing one.
To add data in DSV we have to select the required tables from Available Objects to Included Objects
For adding, select the required tables in Available Objects and click on > button. This will add those tables to included objects
For deleting the unwanted tables from Included Objects, select the table and click on < button from the below GUI form.
<< and >> buttons are used to add or remove all the tables from Included Objects.
There is one more button called Add related Tables. If we select one table in Included Objects and want to add all the tables which are related to that table using primary and foreign key relationship then this button do the trick for you.
Here I am selecting some random tables
Click finish and see the newly created data source view in the solution explorer
Double click on the newly created data source view will shows the selected tables, views and the relationships as shown in the below figure.
Thank you for Visiting Our Blog