Preparation for Creating a Dimension from the FoodMart 2000 Database
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.
- 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
- Click OK.
- 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.
-
Right click the folder within
MyCube2 called Data Sources, and on the popup shortcut menu,
select New Data Source.
The
Data Link Properties dialog box
appears, with its
Provider,
Connection,
Advanced and
All
tabs, as shown in
Illustration 4 below. Beginning with the
Provider
tab (where the dialog opens by default):
-
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
-
Click Next.
The focus moves to the
Connection tab.
- 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
-
Click Open, returning to the Connection
tab, as we see below:
Illustration 6: The Data Link Properties Dialog -- Connection Tab
-
Click the Test Connection button in the bottom
right hand corner of the Connection tab.
Page 4: Preparation for Creating a Dimension (Continued)