Defining a Data Source View

  • Article

After you define the data sources that you will use in a Microsoft SQL Server 2005 Analysis Services (SSAS) project, the next step is generally to define a data source view for the project. A data source view is a single unified view of the metadata from specified tables and views that the data source defines in the project. Storing the metadata in the data source view enables you to work with the metadata during development without an open connection to any underlying data source. For more information, see Working with Data Source Views (Analysis Services).

In the following task, you define a data source view that includes five tables from the Adventure Works DW data source.

To define a new data source view

  1. In Solution Explorer, right-click Data Source Views, and then click New Data Source View.

    The Data Source View Wizard opens.

  2. On the Welcome to the Data Source View Wizard page, click Next.

    The Select a Data Source page appears. Under Relational data sources, the Adventure Works DW data source is selected.

    Tip

    To create a data source view that is based on multiple data sources, you first define a data source view that is based on a single data source. This data source is then called the primary data source. You can then add tables and views from a secondary data source. When designing dimensions containing attributes based on related tables in multiple data sources, you may need to define a Microsoft SQL Server data source as the primary data source in order to use its distributed query engine capabilities.

  3. Click Next.

    The Select Tables and Views page appears. On this page, you select tables and views from the list of objects that are available from the selected data source. You can filter this list to help you in selecting tables and views.

  4. In the Available objects list, select the following tables by holding down the CTRL key to select multiple tables:

    • dbo.DimCustomer
    • dbo.DimGeography
    • dbo.DimProduct
    • dbo.DimTime
    • dbo.FactInternetSales

  5. Click > to add the selected tables to the Included objects list.

    The following image shows the Select Tables and Views page after you have added tables to the list of included objects.

    Select Tables and Views page of wizard

  6. Click Next, and then click Finish to define the Adventure Works DW data source view.

    The data source view Adventure Works DW appears in the Data Source Views folder in Solution Explorer. The contents of the data source view also displays in Data Source View Designer in Business Intelligence Development Studio. This designer contains the following elements:

    • A Diagram pane in which the tables and their relationships are represented graphically.
    • A Tables pane in which the tables and their schema elements are displayed in a tree view.
    • A Diagram Organizer pane in which you can create subdiagrams so that you can view subsets of the data source view.
    • A toolbar that is specific to Data Source View Designer.

    The following image shows the Adventure Works DW data source view in Data Source View Designer.

    Adventure Works DW data source view

  7. Click the Maximize button to maximize the Microsoft Visual Studio development environment.

  8. On the toolbar across the top of Data Source View Designer, use the Zoom icon to view the tables in the Diagram pane at 50 percent. This will hide the column details of each table.

  9. Click the Auto Hide button, which is the pushpin icon, on the title bar of Solution Explorer.

    Solution Explorer minimizes and changes to a tab along the right side of the development environment. To view Solution Explorer again, position your pointer over the Solution Explorer tab. To unhide Solution Explorer, click the Auto Hide button again.

  10. Click Auto Hide on the title bar of the Properties window, if the window is not hidden by default.

    You can now easily view all the tables and their relationships in the Diagram pane. Notice that there are three relationships between the FactInternetSales table and the DimTime table. Each sale has three dates associated with the sale: an order date, a due date, and a ship date. To view the details of any relationship, double-click the relationship arrow in the Diagram pane.

    The following image shows the Diagram pane in Data Source View Designer.

    Diagram pane of Data Source View Designer

You have successfully created the Adventure Works DW data source view, which contains the metadata from five tables in the Adventure Works DW data source. In the next lesson, you will define the initial version of the Analysis Services Tutorial cube from these five tables.

Tip

To add tables to an existing data source view, right-click the Diagram pane or the Tables pane, and then click Add/Remove Tables. For simplicity, add only the tables and views to the data source view that you intend to use in the project.

Next Task in Lesson

Modifying Default Table Names

See Also

Other Resources

Working with Data Source Views (Analysis Services)
Working with Data Source Views How-to Topics (SSAS)

Help and Information

Getting SQL Server 2005 Assistance