Creating an Analysis Services Cube with Visual Studio 2008 - Part 1

An ideal book and eBook for trainers who want to teach an introductory course in SQL Server Integration Services or, to those who want to study and learn SSIS in a little over two weeks.

 

Reviewing Jayaram's other OLAP related articles may greatly help in understanding this article.

Installing SQL Server Analysis Services

SQL Server Analysis Services (SSAS) gets installed during the SQL Server 2008 (February 2008 CTP) installation. At that time the Service Accounts for the various components are also set up as shown in this screen shot taken during a typical installation. For full details review the following article on SQL Server 2008 installation.

Creating an Analysis Services Cube with Visual Studio 2008 - Part 1

The Analysis Services is also configured during the same installation as shown displaying the default directories for Data, log file, backup etc.

Creating an Analysis Services Cube with Visual Studio 2008 - Part 1

Starting and Stopping the SSAS

The Analysis Services can be started and stopped from the Services in the local machine which can be accessed from All Programs | Control Panel | Administrative Tools | Services.

Creating an Analysis Services Cube with Visual Studio 2008 - Part 1

Connecting to SSAS from SQL Server Management Studio

File | Connect Object Explorer... in SQL Server Management Studio allows you to access the UI shown below where you can provide login information and get connected to the Analysis Services as shown.

Creating an Analysis Services Cube with Visual Studio 2008 - Part 1

Out of the box, the SSAS comes up with a simple folder structure as shown. It does not come with databases. You may create a new database (this is not to be confused with databases on the Database Engine of SQL Server 2008). The database you create here will have the same folder structure as the Analysis Services Project folder structure when you start with VS 2008.

Creating an Analysis Services Cube with Visual Studio 2008 - Part 1

Creating an Analysis Services Project in VS 2008

File | New | Project... opens the New Project window. Choose Business Intelligence Projects and from the Visual Studio installed templates choose Analysis Services Project. Change the default name of project to Nwind2008. This creates an empty project with a number of folders as shown. Although this is an empty structure you can deploy it on the server. To deploy right click with Nwind2008 highlighted and choose to deploy. After the processing is successfully completed you will see that Nwind 2008 is now available on the localhost.

Creating an Analysis Services Cube with Visual Studio 2008 - Part 1

Adding a Data Source

Right click on Data Sources folder and this will display the menu item New Data Source... as shown.

Creating an Analysis Services Cube with Visual Studio 2008 - Part 1

Click on New Data Source... to open the Data Source Wizard as shown in the next figure. Read carefully the instructions on this page. In particular note that in order to change features of the data source you need to call up the next wizard - The Data Source View Wizard.

Creating an Analysis Services Cube with Visual Studio 2008 - Part 1

Click on the Next button. This opens the page "Select how to define the connection" of the wizard as shown in the next figure. Here you can either create a data source based on an existing connection or use a new connection, or create a data source based on another object. The window also displays the existing connections under Data Connections and for each highlighted connection displays the properties on the right side. The New... button allows you to create a new connection by guiding you with more interactive windows.

Creating an Analysis Services Cube with Visual Studio 2008 - Part 1

Click on Hodentek2.Northwind (<Server Name>.<database name>) (this will be different in your case) choose this data source and click on the Next button. This opens the Impersonation Page of the Data Source Wizard as shown with all text boxes empty, but with the Windows user name and password as default choice. Northwind database is not installed when you install SQL Server 2008. You may follow the procedure described in these articles to bring the Northwind database to SQL server 2008. Copying a Database from SQL Server 2005 to SQL Server 2008 using the Copy Database Wizard and Moving a Database from SQL Server 2005 to SQL Server 2008 in Three Steps.

Creating an Analysis Services Cube with Visual Studio 2008 - Part 1

Change it to Use the service account and click on the Next button. This displays the Completing the Wizard page as shown where a Data source name and its connection information are displayed.

Creating an Analysis Services Cube with Visual Studio 2008 - Part 1

Click on the Finish button. This adds the chosen data source to the Data Sources folder as shown. Northwind.ds is the file that is added and any changes to the source can be made by double clicking this file and applying the changes.

Creating an Analysis Services Cube with Visual Studio 2008 - Part 1

 


Creating a Data Source View

The Data Source View in SSAS is a logical model of related tables, views, and queries from one or more data sources. The OLAP and data mining projects in SSAS are based on this model. The Data Source View contains the Meta data from the selected data source(s) and any relationships that are defined for them. This cached Meta data allows working with SSAS projects without a continuous active connection to the data source. The Data Source View is created by calling up the Data Source View Wizard that helps in creating the Data Source View.

Right Click Data Source Views folder and click on New Data Source View... menu item. This opens the Data Source View Wizard window as shown. The data source view is a more specialized view of data coming from the relational tables and views.

j

Click on the Next button. The Select Data Source page of the Data Source Wizard shows up. Here you use an existing data source, or create a new data source. Since in the earlier step you chose Northwind it shows up here as an existing data source. Here the Northwind will be the only source used in this tutorial.

Creating an Analysis Services Cube with Visual Studio 2008 - Part 1

Click on the Next button. This brings up the Select Tables and Views page of the Data Source View Wizard displaying all the existing Tables and Views in the Northwind data base. Using the > and >> arrows you can transfer objects from the Available Objects area in the left to the Included Objects area in the right. The objects transfered to right will be used in the projects. You may also use the < and << arrows to remove them as well. You need to highlight the table, or view in the left and use the > arrow to transfer. The Orders table has been transferred as shown in the next figure. You also have the option to filter the objects although no filtering is used for this article.

Creating an Analysis Services Cube with Visual Studio 2008 - Part 1

Highlight the chosen table in the right, the Add Related Tables button gets enabled if it is not. Click on the Add Related Tables button. A number of other tables related to the Orders table gets added to the project as shown.

Creating an Analysis Services Cube with Visual Studio 2008 - Part 1

Click on the Next button. This takes you to the final step in creating a Data Source View of the Northwind as shown in the next figure.

Creating an Analysis Services Cube with Visual Studio 2008 - Part 1

Click on the Finish button. This adds the Northwind.dsv file to the Data Source Views folder. New windows Tables and Diagram Organizer window gets displayed as shown. The various relationships between the related tables gets displayed in the Northwind.dsv[Design] pane as shown in the following figures.

Solution Explorer

Creating an Analysis Services Cube with Visual Studio 2008 - Part 1

Tables and Diagram Organizer

Creating an Analysis Services Cube with Visual Studio 2008 - Part 1

Northwind.dsv [Design]

Creating an Analysis Services Cube with Visual Studio 2008 - Part 1

The designer is very flexible in that you can add and remove tables from this diagram retaining what you want for your use as well as carry out many other tasks. It will be instructive to try them out. If you make a right click in an empty area in the above figure in the designer you will display the following drop-down menu from which you can choose the required item.

Creating an Analysis Services Cube with Visual Studio 2008 - Part 1

For example to delete the Employee table after making a right click on the table you can display a choice to remove the table as shown. This removes the Employee table from the DSV (Data Source View) file.

Creating an Analysis Services Cube with Visual Studio 2008 - Part 1

You get a chance to delete it or not as shown in the next figure. If you choose to delete by clicking on Yes, it will be removed from the DSV.

Creating an Analysis Services Cube with Visual Studio 2008 - Part 1

In this manner only the following structure for the Data Source View was retained for Part 2.

Creating an Analysis Services Cube with Visual Studio 2008 - Part 1

Summary

The article described features of the SQL Server Analysis Services that can be installed from Microsoft SQL Server 2008. Steps involved in creating a data source and a data source view were also described. These will be needed in Cube design. The VS 2008 IDE is very flexible and allows you to go and make changes in several places quite easily. The wizards delineate steps which are easy to follow and unambiguous.

Books to Consider

comments powered by Disqus