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.
The Analysis Services is also configured during the same installation as shown displaying the default directories for Data, log file, backup etc.
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.
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.
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 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.
Adding a Data Source
Right click on Data Sources folder and this will display the menu item New Data Source… as shown.
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.
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.
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.
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.
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.
