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 the FactFinance table in to the Included Objects list. It is the only table related to DimAccount. 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.