Preparing to Work with Dimensions - Creating a Database
To begin the steps of the lesson, we will create a new database/data source for the FoodMart 2000 sample provided with the Typical MSSQL 2000 Analysis Services installation. As part of the preparation for the coming lessons, we will replicate the process found in our first lesson.
As we learned in Lesson One, before we can design a new cube, we need to set up a database -- more specifically, in Analysis Manager, we need to set up an OLAP database. The OLAP database we create will organize cubes, roles, data sources, shared dimensions, and other objects that we will cover in later segments. 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. We start Analysis Manager, then right-click on the Analysis Server name (shown as MOTHER in Illustration 1 below).
Click to Enlarge
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 "Lesson 2 Cube creation with MSSQL Server 2000 Analysis Services," here - the description is optional, of course. The dialog appears as below.
Illustration 2: The Database Dialog Box
2. Click OK, and note that MyCube2 appears in the left-side tree, complete with predefined, empty folders for object storage later.
We will need to connect to the data source from which we wish to draw values.
3. Expand MyCube2's database / cube icon, (clicking on the plus (+) sign to the left of the icon will do the trick), right click the folder within MyCube2 called Data Sources and select New Data Source on the popup shortcut menu.
The Data Link Properties dialog box appears, with its Provider, Connection, Advanced and All tabs, as shown in Illustration 3 below. Beginning with the Provider tab we will 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 3: The Data Link Properties Dialog - Provider Tab
4. Click Next.
The focus moves to the Connection tab.
5. We select the FoodMart 2000 database, located by default in the [Install Directory]:\Program Files\Microsoft Analysis Services\Samples directory, by clicking on the ellipses (...) button, to navigate to the .mdb as shown in Illustration 4 below.
Illustration 4: Selecting the FoodMart 2000 Database
6. Click Open, returning to the Connection tab, as we see below:
Illustration 5: The Data Link Properties Dialog - Connection Tab
7. Leaving all other Data Link Properties setpoints at their defaults for now, test the connection by clicking 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 6).
Illustration 6: Verification of the Connection to the FoodMart 2000 Database
8. Click OK, and the Microsoft Data Link verification dialog box closes.
9. 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 mentioned in Lesson One, we might want to make this a more intuitive - or at least shorter - name in order to keep a tidy appearance. A simple "rename" capability is not in the cards - unlike circumstances in the similar looking, but functionally different, Windows Explorer scenario. A right-mouse click on our new data source will, however, allow a Copy action.
1. Right-click the new data source.
2. Click Copy.
3. Highlight the Data Sources folder.
4. Right-click the MyCube2 database folder we created earlier.
5. Select Paste from the popup menu,
This causes Analysis Services to indicate that a duplicate has been detected, and to prompt us for a unique name to rectify the confusion. We will respond to the new name request with MyFoodMart2, using the dialog box that appears (as shown below in Illustration 7).
Illustration 7: Changing the Name of the Newly Copied Cube as a Means of Renaming
The Duplicate Name dialog thus acts as our agent of change, and, once we click OK, adds the newly named data source under the data sources folder. All that remains is to delete the original data source, from which we cloned MyFoodMart2.
6. Right-click the original data source, and select Delete on the popup menu, then click the Yes button, to organize our new data source folder.
We now have an OLAP database in place, linked to a valid data source (the FoodMart 2000 database). The next step in our exploration will be to introduce the Dimension Editor, and to design a single-table dimension.
Page 3: Building and Managing a Single-Table Dimension