MSAS : Setting up the Database in Analysis Server

This tutorial explains Setting up the Database in Analysis Server and how to connect and test the connectivity.

Setting up the Database in Analysis Server

The first step in creating a cube is the setting up of the Database. Double click on the server and connect to it. Once the connection is established, the user can proceed to create the database. Right click on the server node and select ‘New Database’. On clicking on “New Database” a tiny window opens prompting the user to enter the name of the database and a description of it. The database has been named Sample and the description entered as “sample cube”.

Descriptions of the database are generally useful when there are a large number of databases and data sources for the user to deal with and he must choose his database before beginning his operations on it.

Once ‘OK’ has been clicked the database appears on the left pane of the Analysis server, complete with all the empty folders for object storage.

The first folder under the Sample database is the data source folder. Every cube draws its data from a particular data source. This folder will store the details of the data source that will provide the cube with its data. The data source can be set up in two ways. 

1.  Right click on data source folder and select New Data Source 
2. Right Click on the Cube folder. Select New Cube. In the cube wizard that opens select Next and then click on New Data Source button.
3. Either of these operations will navigate the user to the Data link properties dialog box.

Connecting to the Data Source

Now Click OK to complete the process. Note that the Data source is displayed as ‘sample’ in the left pane of the Analysis server under data sources.

Tip: Renaming the data source is not possible in Analysis server. There is no simple “rename” option. The user however can get round this difficulty with a little bit of ingenuity. Right click on the data source name and click copy. Then click on the data source and click paste. A small dialog box will pop up warning you that the data source already exists in the database

However it gives the user the option of renaming the copy and pasting it into the data source folder. Enter the new name in the dialog box and click OK. Then delete the original data source name. The data source has been renamed to suit the user’s taste.