Setting up the Database and Data Source
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 MyFirstCube (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 sourceinto our database before we start to construct our cube.
We right-click on the Analysis Server name (shown as MOTHER above). As shown in Illustration 2below, the dialog box appears, and we fill in the Database Name, MyFirstCube for this session, and a description that might be of value to a user down the road. Let's simply add "Initial exploration of cube creation in MSSQL Server 2000 Analysis Services," here -- it is certainly optional.
Illustration 2: The Dialog Box for Naming Our Database
Click OK, and note that MyFirstCube 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 -- a simple act if you've ever dealt with data sources in Windows before -- if not, we'll get there together in short order. The data source will need to be available via the OLE DB providers that are offered, as we shall see -- with the Microsoft OLE DB Provider for ODBC Driversacting as a possible choice for older systems that might not have had the benefit of OLE DB's debut.
We will at this point expand MyFirstCubes database folder, (clicking on the plus (+) sign to the left of the folder will do the trick), right click the folder within MyFirstCube called Data Sources, and on the popup shortcut menu, we will select New Data Source.
Next, we see the Data Link Properties dialog box appear, with its Provider, Connection, Advanced and All tabs, as shown in Illustration 3 below. We will select the Microsoft Jet 4.0 OLE DB Provider (the native MS Access OLE DB provider). Keep in mind that, while OLE DB Drivers for ODBC will often provide a secondary means of obtaining a connection, the OLE DB Driver (assuming one exists) is certainly typically the more optimal choice from a performance standpoint. (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
If we consider what a cube is doing, organizing and storing data for analysis, it becomes obvious that the cube needs to get its data (typically values from a fact table in a star schema data warehouse / data mart arrangement) from somewhere. Our data source will be the FoodMart 2000 sample .mdb provided with Analysis Services.
Click Next, and the focus moves to the Connection tab. We will select the FoodMart 2000.mdb database, located by default in the [Install Directory]:\Program Files\Microsoft Analysis Services\Samples directory, by clicking on the ellipses (...) button, as shown in Illustration 5below, then navigating to the FoodMart 2000.mdb database. We then highlight FoodMart 2000.mdb, and click Open, returning to the Connection tab.
Illustration 4: The Data Link Properties Dialog - Connection Tab
Illustration 5: Selecting the FoodMart 2000.mdb Database
Next, leaving the other setpoints at their defaults for this session, we can 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). Click OK, and the verification (Microsoft Data Link) dialog box closes.
Illustration 6: Data Source Verification Dialog
Now, when we click OK on the Data Link Properties tab, the Properties dialog box closes, and we can see that the source appears under the Data Sources folder in the tree area on the left side of the management console, probably displaying the actual file name.
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 a similar looking, but functionally different, Windows Explorer scenario. A right-mouse click on our new data source will, however, allow a Copy action. We will right-click our new data source, click Copy, highlight the Data Sources folder, and then right-click the MyFirstCube database folder we created earlier. Next, well select Paste from the popup menu, causing 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 MyFoodMart, 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, does our bidding and adds the newly named data source under the data sources folder. We have only to delete the original data source (right-click it, 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 next two steps in our exploration will be to introduce the Cube Wizardand to design a simple cube.
Page 3: Designing Our Cube