Introduction to SQL Server 2000 Analysis Services: Handling Time Dimensions - Page 3

To begin the steps of the lesson, we will create a new database/data source for the FoodMart 2000 database. While many of us may have already set the database up from the previous lesson, we will perform setup again quickly for the benefit of anyone who has not. We will call the database the same name as we used in the previous lesson, so if we already have a database with the same name, and we perhaps want to recreate it for review purposes, we'll need to either delete the existing database or simply call the new database something else.

As we learned in Lesson One, the OLAP database we create will organize cubes, roles, data sources, shared dimensions, and other objects that we will cover in this and later lessons, in addition to the objects that we are currently using. We will call our OLAP database MyCube2 (be careful here -- you cannot rename a database in the Analysis Services console once it is created), setting it up initially by right-clicking on the Server we see at the left of the console. Keep in mind that, among other objects, the database can contain multiple cubes, each with a single data source. In addition to setting up our database, we will link a data source to our database before we start to construct our cube.

1. Start Analysis Manager, then right-click on the Analysis Server name (shown as MOTHER in Illustration 1 below).

Illustration 1: Right-Click on the Analysis Server

We click New Database, and the Database dialog box appears. We fill in the Database Name, MyCube2, and a description that might be of value to a user or developer down the road. Let's simply add "Tutorials Database," here -- the description is optional, of course. The dialog appears as below.

Illustration 2: The Database Dialog Box

2. Click OK.
3. Expand MyCube2's database/cube icon, (clicking in the plus (+) sign to the left of the icon will do the trick).

Note that MyCube2 appears in the left-side tree, complete with predefined, empty folders for object storage later, as shown in Illustration 3 below.

Illustration 3: The New Database with Directory Structure

We will need to connect to the data source from which we wish to draw values.

4. Right click the folder within MyCube2 called Data Sources, and on the popup shortcut menu, select New Data Source.

5. Select the Microsoft Jet 4.0 OLE DB Provider (the native MS Access OLE DB provider). (For more on this, review the on-line documentation for OLE DB Providers, and data sources in general).

Illustration 4: The Data Link Properties Dialog -- Provider Tab

6. Click Next.

7. Select the FoodMart 2000 database.

FoodMart 2000.mdb is located, by default, in the [Install Directory]:\Program Files\Microsoft Analysis Services\Samples director; Click on the ellipses (...) button, to navigate to the .mdb as shown in Illustration 5 below.

Illustration 5: Selecting the FoodMart 2000 Database

8. Click Open, returning to the Connection tab, as we see below:

Illustration 6: The Data Link Properties Dialog -- Connection Tab

9. Click the Test Connection button in the bottom right hand corner of the Connection tab.

Page 4: Preparation for Creating a Dimension (Continued)