Creating an Analysis Services Project (SQL Server Video)

Video Summary

This video demonstrates how to create an Analysis Services project, define a data source, and define a data source view. You will also learn how to change the value of the FriendlyName property for objects in the data source view to increase the user-friendliness of their names.

Video Transcript

In this video, I’ll show you how to create an Analysis Services project.

• Launch SQL Server Business Intelligence Development Studio which is the program used to develop OLAP cubes and data mining models.

• Click File, New, Project to create a new Analysis Services project.

  I’ll name my project “Analysis Services Tutorial”. The new project is shown in Solution Explorer.

The next step is to define a data source which defines the connection string to the data source:

• Right-click Data Sources, and then click New Data Source.

• When the Welcome to the Data Source Wizard page opens, click Next.

  When creating a connection, there are many providers to choose from. For best performance, you should typically use the Microsoft OLE DB Provider for SQL Server or the SQL Server Native Client Provider. I’m connecting to a data source on my local computer so I type localhost. When possible, you should use Windows Authentication. The other option is provided for backward compatibility.

• Select AdventureWorksDW2008.

  I downloaded this sample database from the CodePlex website. Click Test Connection. As you can see, the test connection succeeded.

• Select Use the service account to use the security credentials associated with the Analysis Services service.

  I’ll name the data source, “Adventure Works DW.” The new data source is shown in Solution Explorer.

Next, I’ll define a data source view for the project. A data source view is a unified view of the metadata from the data source. Storing the metadata in the view lets you work with the metadata during development without having an open connection to the data source.

• Right-click Data Source Views, and then click New Data Source View.

• Re-size the window to make it easier to see the list of available objects.

• Hold down the CTRL key and select DimCustomer, DimDate, DimGeography, DimProduct, and FactInternetSales.

• Click the arrow button to add the selected tables to the included objects list.

  I’ll name the data source view, "Adventure Works DW." The new data source view appears in the Solution Explorer. It is also shown in the Data Source View Designer. This designer contains a diagram pane, a tables pane, a diagram organizer pane, and a toolbar.

• Click the zoom icon to view the tables in the diagram pane at 50 percent.

  This hides the column details of each table. To hide Solution Explorer, click the Auto Hide button. To pin it open again, click the Auto Hide button again.

You can change the value of the FriendlyName property for objects in the data source view to increase the user-friendliness of their names. This way, users will see the more readable “friendly name” in reports and applications that use the data source view.

• You can open the properties window by clicking the properties tab or from the tables pane.

• In the Tables pane, right-click the FactInternetSales table, and then click Properties.

• Change the FriendlyName property to InternetSales.

• Click the Auto Hide button to pin this window open.

  I’m changing the friendly name of each table to remove the "Dim" and "Fact" prefixes from the table names.

• Change the FriendlyName of DimProduct to Product, DimGeography to Geography, DimDate to Date, and DimCustomer to Customer.

• Click Save All and you’ve saved your Analysis Services project.