Preparation for Creating the Parent-Child Dimension

To begin the steps of the lesson, we will create a new database/data source in Analysis Manager for the FoodMart 2000 database. While many of us may have already set the database up from the previous lesson(s) in our series, we will perform setup again quickly for the benefit of anyone who has not. We will give the database the same name as we used in the previous lesson; if we already have a database with that 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 has been 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, to see the context menu shown below:

Illustration 5: Right-Click on the Analysis Server

2. Click New Database.

The Database dialog box appears.

3. 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 6: The Database Dialog Box

4. Click OK.

MyCube2 appears just below the existing FoodMart 2000 Database.

5. Expand MyCube2's database/cube icon, (clicking on 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 7 below.

Illustration 7: The New Database with Directory Structure

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

6. Right click the folder within MyCube2 called Data Sources, and on the context menu, select New Data Source.

The Data Link Properties dialog box appears, with its Provider, Connection, Advanced and All tabs, as shown in Illustration 8 below. Beginning with the Provider tab (where the dialog opens by default):

7. 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 8: The Data Link Properties Dialog-Provider Tab

8. Click Next.

The focus moves to the Connection tab.

9. Select the FoodMart 2000 database in Box 1.

FoodMart 2000.mdb is located, as we have noted, 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 9 below.

Illustration 9: Selecting the FoodMart 2000 Database

10. Click Open to return to the Connection tab, as we see below:

Illustration 10: The Data Link Properties Dialog-Connection Tab

11. Insert User Name and Password information, as appropriate (the illustration displays default settings).
12. Click the Test Connection button in the bottom right hand corner of the Connection tab.

We should get a verification dialog confirming that the source has been established in our definition (as shown in Illustration 11).

Illustration 11: Verification of the Connection to the FoodMart 2000 Database

13. Click OK, and the Microsoft Data Link verification dialog box closes.

We will leave all the other Data Link Properties at their default setpoints for now.

14. Click OK on the Data Link Properties tab.

The Data Link Properties dialog closes, and we can see that the new source appears under the Data Sources folder in the tree area, on the left side of the Management Console, displaying the actual file name, as shown below.

Illustration 12: Initial View of Our Newly Created Data Source

Page 4: Preparation for Creating P-C Dimension Continued

See All Articles by Columnist William E. Pearson, III