In this article, I will show you how to create a data source view in SSAS.
Before starting this, let’s have a look on What is Data Source View in SSAS?
A data source view contains the logical model of the schema which is used by Analysis Services multidimensional database objects — namely cubes, dimensions, and mining structures. A data source view is the metadata definition, stored in an XML format.
A Data Source View:
- Contains the metadata that represents selected objects from one or more underlying data sources, or the metadata that will be used to generate an underlying relational data store
- Can be built over one or more data sources, letting you define multidimensional and data mining objects that integrate data from multiple sources.
- Can contain relationships, primary keys, object names, calculated columns, and queries that are not present in an underlying data source and which exist separate from the underlying data sources.
- Is not visible to or available to be queried by client applications.
1. First of all open Visual Studio 2010 and open any SSAS project.
2. In solution explorer, right click on Data Source Views and select New Data Source View.
3. A Data Source View Wizard window opens. In that check Don’t show this page again and click on Next button.
4. In next step, select existing data source if your already created it otherwise create New Data Source. Here we select existing Data Source. Then click on Next Button.
5. Now Select Tables And Views page appears. It will shows lists of Available Objects and Included Objects. Now Double-Click on DimAccount to add it to the Included Objects list.
Now Select Tables and Views page should look like the following:
6. Now Select the DimAccount table and then click on Add Related Tables.
It will analyzes the foreign key relationships in the source database and adds theFactFinance table in to the Included Objects list. It is the only table related toDimAccount. Now Select Tables and Views page should look like the following:
7. In the Filter box, type dim and click the filter button. It will filter the Available Objects list to find the tables easier. After that click on >> button to add all tables into Included Objects.
8. Similarly type fact into filter box and the click on filter button. After that click on >> button to add all tables into Included Objects. Now your page should look like following.
Then click on Next button.
9. In next step, you will see the summary of Data Source View. Also give name of data source view. Here we give AdventureWorksDW2012 as name.
Then click on Finish Button.
10. Now your data source view design should look like following screen shot :
So we have done with Data Source View in SSAS.
If your data source view contains too many tables, you will not be able to see all the tables in the diagram pane. Following steps shows you how to browse the contents of a data source view.
- Click and hold the four-headed arrow button at the lower-right corner of the data source view in diagram pane. The table locator looks like the following screen shot.
- Now drag your mouse around the table locator. The diagram pane scrolls to match your movements.
- Otherwise click a table name in the Tables pane. The diagram pane scrolls so that it is centered on the table you selected.
Note: Microsoft Books online is a default reference of all articles.
Before starting this, let’s have a look on What is Data Source View in SSAS?
A data source view contains the logical model of the schema which is used by Analysis Services multidimensional database objects — namely cubes, dimensions, and mining structures. A data source view is the metadata definition, stored in an XML format.
A Data Source View:
- Contains the metadata that represents selected objects from one or more underlying data sources, or the metadata that will be used to generate an underlying relational data store
- Can be built over one or more data sources, letting you define multidimensional and data mining objects that integrate data from multiple sources.
- Can contain relationships, primary keys, object names, calculated columns, and queries that are not present in an underlying data source and which exist separate from the underlying data sources.
- Is not visible to or available to be queried by client applications.
1. First of all open Visual Studio 2010 and open any SSAS project.
2. In solution explorer, right click on Data Source Views and select New Data Source View.
3. A Data Source View Wizard window opens. In that check Don’t show this page again and click on Next button.
4. In next step, select existing data source if your already created it otherwise create New Data Source. Here we select existing Data Source. Then click on Next Button.
5. Now Select Tables And Views page appears. It will shows lists of Available Objects and Included Objects. Now Double-Click on DimAccount to add it to the Included Objects list.
Now Select Tables and Views page should look like the following:
6. Now Select the DimAccount table and then click on Add Related Tables.
It will analyzes the foreign key relationships in the source database and adds theFactFinance table in to the Included Objects list. It is the only table related toDimAccount. Now Select Tables and Views page should look like the following:
7. In the Filter box, type dim and click the filter button. It will filter the Available Objects list to find the tables easier. After that click on >> button to add all tables into Included Objects.
8. Similarly type fact into filter box and the click on filter button. After that click on >> button to add all tables into Included Objects. Now your page should look like following.
Then click on Next button.
9. In next step, you will see the summary of Data Source View. Also give name of data source view. Here we give AdventureWorksDW2012 as name.
Then click on Finish Button.
10. Now your data source view design should look like following screen shot :
So we have done with Data Source View in SSAS.
If your data source view contains too many tables, you will not be able to see all the tables in the diagram pane. Following steps shows you how to browse the contents of a data source view.
- Click and hold the four-headed arrow button at the lower-right corner of the data source view in diagram pane. The table locator looks like the following screen shot.
- Now drag your mouse around the table locator. The diagram pane scrolls to match your movements.
- Otherwise click a table name in the Tables pane. The diagram pane scrolls so that it is centered on the table you selected.
Note: Microsoft Books online is a default reference of all articles.
SSAS Data Source View
by suresh
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.
by suresh
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.
Click Next
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 next
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
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.
Click Next
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 next
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
