MSBI # 19 – SSAS # 5 – How to Defining a Data Source and Data Source View within an SQL Server Analysis Services

Hi folks ,

Continuing from my last post MSBI # 18– SSAS # 4 – Building a Cube and Dimension Using SSMS–SSAS Wizards

Today we are going to understand how we are going to define Data Source View in Analysis service

Defining a Data Source 

After we have create a Microsoft SQL Server Denali Analysis Services (SSAS) project, on our last post we can generally start working with the project by defining one or more data sources that the project will use. When you define a data source, you are defining the connection string information that will be used to connect to the data source.

In the following task, you define the AdventureWorksDW sample database as the data source for the Analysis Services Tutorial project. While this database is located on your local computer for the purposes of this tutorial, source databases are frequently hosted on one or more remote computers.

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

The Data Source Wizard opens.

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

The Select how to define the connection page appears. On this page, you can define a data source based on a new connection, based on an existing connection, or based on a previously defined data source object. A previously defined data source object is an existing data source definition within the current project or within another project in the current solution. In this tutorial, you will define a new data source based on a new connection.

3.On the Select how to define the connection page, verify that Create a data source based on an existing or new connection is selected and then click New.

The Connection Manager dialog box appears. In this dialog box you define connection properties for the data source. A connection manager is a logical representation of the connection that will be used at run time. For example, a connection manager includes a connection string property that you set at design time; at run time, a physical connection is created by using the values in the connection string property.

4.In the Provider list, verify that Native OLE DB\SQL Native Client is selected.

Analysis Services also supports other providers, which are displayed in the Provider list.

5.In the Server name text box, type localhost or your Server Name.

To connect to a named instance on your local computer, type localhost\<instance name>. When you deploy the project to a particular instance of Analysis Services, the Analysis Services engine will connect to the specified database in the default instance of Microsoft SQL Server on the computer where the instance of Analysis Services resides. If you specify a particular computer name or IP address when you define a data source, the project or the deployed application connect to the specified computer instead of to the local computer. The Analysis Services Deployment Wizard lets you specify the actual server name for the source data at deployment time.

Verify that Use Windows Authentication is selected. In the Select or enter a database name list, select AdventureWorksDW.

The following image shows Connection Manager with the settings that you have defined up to this point.

6.Click OK, and then click Next.

The Impersonation Information page appears. On this page of the wizard, you define the security credentials for Analysis Services to use to connect to the data source. Impersonation affects the Windows account used to connect to the data source when Windows Authentication is selected. Analysis Services does not support impersonation for processing OLAP objects and the service account is typically used for connecting to data sources when Windows Authentication is selected.

7.Select Use the service account, and then click Next.

The following image shows the Completing the Wizard page that appears.

8.On the Completing the Wizard page, click Finish to create the new data source with the name Adventure Works DW.

The following image shows the new data source in the Data Sources folder in Solution Explorer.

Defining a Data Source View 

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.

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.

If you really don’t wan to see this screen again please select highlighted Check Box

The Select a Data Source page appears. Under Relational data sources,

the Adventure Works DW data source is selected.

NB : 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.

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:

o A Diagram pane in which the tables and their relationships are represented graphically.

o A Tables pane in which the tables and their schema elements are displayed in a tree view.

o A Diagram Organizer pane in which you can create subdiagrams so that you can view subsets of the data source view.

o 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.

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.

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.

Modifying Default Table Names 

The metadata for the tables and views in a data source view are derived from the metadata for these objects in the underlying data source. Business Intelligence Development Studio uses the metadata for the objects in the data source view to define dimensions, attributes, and measure groups. However, BI Development Studio uses the FriendlyName property of an object instead of its Name property. You can change the value of the FriendlyName property for objects in the data source view, to increase the user-friendliness of the names of the dimension and cube objects that are created from the data source view. You can also change the names of these objects after you define them.

In the following task, you will change the friendly name of each table in the Adventure Works DW data source view by removing the "dim" and "fact" prefixes from these tables. This will increase the user-friendliness of the cube and dimension objects that you will define in the next lesson.

To modify the default name of a table

In the Diagram pane of Data Source View Designer, right-click the FactInternetSales table, and then click Properties.

The hidden Properties window appears, displaying the properties for the FactInternetSales object in the Adventure Works Tutorial data source view.

1.Click the Auto Hide button on the title bar of the Properties window so that this window will remain unhidden.

It is easier to change the properties for each table in the data source view when the Properties window remains open. If you do not pin the window open by using the Auto Hide button, the window will close when you click a different object in the Diagram pane.

2.Change the FriendlyName property for the FactInternetSales object to InternetSales.

When you click away from the cell for the FriendlyName property, the change is applied. In the next lesson, you will define a measure group that is based on this fact table; the name of the fact table will be InternetSales instead of FactInternetSales because of the change you made in this lesson.

3.Click dbo.DimProduct in the Tables pane, click DimProduct in the Diagram pane, or select dbo.DimProduct DataTable in the list box of the Properties window, and then change the FriendlyName property to Product.

In the next lesson, you will define a dimension that is based on the DimProduct table; the name of this dimension will be Product instead of DimProduct, because of the change you made in this procedure.

4.Change the FriendlyName property of each remaining table in the data source view in the same way, to remove the "Dim" prefix.

5.When you have finished, click the Auto Hide button to hide the Properties window again.

The following image shows the data source view in Data Source View Designer, with the newly modified object names.

6.On the File menu, or on the toolbar of BI Development Studio, click Save All.

This saves the changes you have made to this point in the Analysis Services Tutorial project, so that you can stop the tutorial here if you want and resume it later.

You have successfully changed the default table names in the data source view to increase the user friendliness of the dimensions and measure groups that you will define based on these tables.

Hope this helps !!

If you really Like reading  my post,then please don’t forget to subscribe my blog !!

For more interesting links and daily update please subscribe our link resource website

Todays link are follows:

Link Resource # 14 : August 8 – August 10 « Dactylonomy of Web Resource