Creating an Analysis Services Project

In the following task, you use Business Intelligence Development Studio to create a new Microsoft Analysis Services project named Analysis Services Tutorial, based on the Analysis Services Project template. A project is a collection of related objects. Projects exist within a solution, which includes one or more projects. For more information, see Defining an Analysis Services Project.

To create a new Analysis Services project

1.      Click Start, point to All Programs, point to Microsoft SQL Server 2008, and then click SQL Server Business Intelligence Development Studio.

The Microsoft Visual Studio development environment opens.

2.      On the File menu of Visual Studio, point to New, and then click Project.

3.      In the New Project dialog box, select Business Intelligence Projects in the Project types pane, and then select Analysis Services Project in the Templates pane.

Notice the default project name, the default solution name, and the default project location in the bottom of the dialog box. By default, a new directory will be created for the solution.

4.      Change the project name to Analysis Services Tutorial, which also changes the solution name, and then click OK.

You have successfully created the Analysis Services Tutorial project, based on the Analysis Services Project template, within a new solution that is also named Analysis Services Tutorial.

Defining a Data Source

After you create an Analysis Services project, you 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. For more information, see Defining a Data Source Using the Data Source Wizard (Analysis Services).

In the following task, you define the AdventureWorksDW2008 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.

To define a new data source

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

2.      On the Welcome to the Data Source Wizard page, click Next to open the Select how to define the connection page.

3.      On the Select how to define the connection 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. In this tutorial, you define a data source based on a new connection. Verify that Create a data source based on an existing or new connection is selected and then click New.

4.      In the Connection Manager dialog box, you define connection properties for the data source. In the Provider list, verify that Native OLE DB\SQL Server Native Client 10.0 is selected.

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

5.      In the Server name text box, type localhost.

To connect to a named instance on your local computer, type localhost\<instance name>. To connect to the specific computer instead of the local computer, type the computer name or IP address.

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

7.      Click Test Connection to test the connection to the database.

8.      Click OK, and then click Next.

9.      On the Impersonation Information 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. Select Use the service account, and then click Next.

10.  On the Completing the Wizard page, type the name Adventure Works DW and then click Finish to create the new data source.

After you define the data sources that you will use in an Analysis Services 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 the 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 Designing 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.

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

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

4.      On the Select Tables and Views 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.

5.      In the Available objects list, select the following objects. You can select multiple tables by clicking each while holding down the CTRL key:

·         DimCustomer (dbo)

·         DimDate (dbo)

·         DimGeography (dbo)

·         DimProduct (dbo)

·         FactInternetSales (dbo)

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

7.      Click Next.

8.      In the Name field, type Adventure Works DW and then click Finish to define the Adventure Works DW data source view.

The Adventure Works DW data source view appears in the Data Source Views folder in Solution Explorer. The content of the data source view is also displayed 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.

9.      To maximize the Microsoft Visual Studio development environment, click the Maximize button.

10.  To view the tables in the Diagram pane at 50 percent, click the Zoom icon on the Data Source View Designer toolbar. This will hide the column details of each table.

11.  To hide Solution Explorer, click the Auto Hide button, which is the pushpin icon on the title bar. To view Solution Explorer again, position your pointer over the Solution Explorer tab along the right side of the development environment. To unhide Solution Explorer, click the Auto Hide button again.

12.  If the window is not hidden by default, click Auto Hide on the title bar of the Properties window.

You can now view all the tables and their relationships in the Diagram pane. Notice that there are three relationships between the FactInternetSales table and the DimDate 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.

Modifying Default Table Names

You can change the value of the FriendlyName property for objects in the data source view to increase the user-friendliness of their names. 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.

1.      In the Tables pane of Data Source View Designer, right-click the FactInternetSales table, and then click Properties to display the properties for the FactInternetSales object in the Adventure Works Tutorial data source view.

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

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.

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

4.      Click DimProduct in the Tables pane. In the Properties window, change the FriendlyName property to Product.

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

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

7.      On the File menu, or on the toolbar of BI Development Studio, click Save All to save the changes you have made to this point in the Analysis Services Tutorial project. You can stop the tutorial here if you want and resume it later.

	Mr. Ravi Krishna - Senior Software Engineer I am a Mocrosoft ASP.net Developer and MCP Certified professional. I have overall 5 years of experience in IT Industry,in that 3 years experience in Microsoft BI(SSAS,SSIS,SSRS). I have experience on various business domains like Automation and Chemical. https://sites.google.com/site/rkkumardotnet/ Read more