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.
- Right click on data source folder and select New Data Source
- 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.
- Either of these operations will navigate the user to the Data link properties dialog box.
Connecting to the Data Source
Right click on Data source and Click New Data Source. The Data link Properties dialog box opens. Select Microsoft OLE DB Provider for ODBC drivers as we propose to use an Access Database called FoodMart2000.mdb as our data source. This is the default database that ships with Microsoft SQL server 2000. Click ‘Next’ or Click the connection tab to establish the connection with the data source. In the Connection tab enter the name ‘Sample’ in the data source name text box. Note that this dialog box prompts the user to provide Logon information for log on to the server. It also prompts the user to enter details regarding the catalog to be used. Since these settings are not currently required, we will leave them be. However, we need to test whether the database has been connected before we proceed. Click on Test connection button to test the connection.
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.
Now we have the Analysis Server database in place linked to a valid data source. The next step would be to use the Cube Wizard to create the cube.
