Creating an Analysis Services Project
About This Video:
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.
This video available with closed captions. To view closed captions, click CC on the video control bar.
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.
To create a new Analysis Services project click File, New 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 my data source on my local computer, so I type Localhost
When possible, you should use Windows Authentication.
Select AdventureWorksDW2008. I downloaded this sample database from the CodePlex website.
Click Test Connection to see that 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.
Resize the window to make it easier to see the list of available objects.
Hold down the control key and select DimCustomer, DimDate, DimGeography, DimProduct and FactInternetSales.
Click the arrow key 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 at 50%.
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 FriendlyName 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 select Properties.
Change the FriendlyName property to InternetSales.
Click the Auto Hide button to pin this window open.
I'm changing the FriendlyName of each table to remove the Dim and Fact prefixes from the table names.
I'm changing 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.
Presented by: Mary Brennan