A Data Source View in SSAS is a set of tables or views from the database that is required to design the cube (We can call 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 essential properties of SSAS Data source view are:
- One data source view can pull data from only one data source.
- Analysis services allow us to create multiple data source views from a single data source.
- Analysis services allow us to create data source views from multiple data sources.
- Any changes made in the Data Source View such as changing table names, removing a 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, the data source view provides space to add those 20 tables.
TIP: One data source view can pull data from only one data source in SSAS.
NOTE: Any changes made in the Data Source View such as changing table names, removing a 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 opens the Data source view wizard with the welcome page. If you don’t want to see this welcome page again, checkmark the Don’t show this page again option below.
If you already created a data source, then it will display like above, or you have to click on the New Data Source button to create one. Please refer SSAS Data Source article to understand how to create a 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. It adds those tables to included objects.
For deleting the unwanted tables from Included Objects, select the table and click on the < 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 relevant to that table using primary and foreign key relationship. This button does the trick for you.
Here I am selecting some random tables
Click finish and see the newly created data source view in the SSAS solution explorer
Double click on the newly created SSAS data source view show the selected tables, views, and relationships, as shown in the figure below.