Introduction to SQL Server 2000 Analysis Services: Creating Our First Cube - Page 7
September 3, 2002
We saw, in the earlier section, Creating a Dimension from a Snowflake Schema: Multiple, Related Dimension Tables above, that the Wizard provided a step in our creation of the Product Dimension (see Illustration 24 in the section concerned) that it did not provide us in the design steps of our first dimension (Region, in the Creating a Dimension from a Star Schema: A Single Dimension Table section). Since the Star Schema option typically applies to a single table, relationships / joins don't factor in as a consideration -- at least not at the time of specification. The issues arise when the Wizard attempts to process the cube and determines that no "obvious" (typically consisting of like--named keys in table pairs) joins exist between the specified dimension and fact tables. We are being told that we will need to manually designate the join(s) so that processing may continue. We will do so, but let's save our work first.
We handle this by first acknowledging the message and clicking OK. Doing so takes us to the Finish the Cube Wizard Dialog Box, as shown below (Illustration 35). We input the name of the cube (for purposes of the session, and to save the model we have created). The save takes place when we click Finish, and then we are met with the Cube Editor dialog.
Illustration 35: The Finish the Cube Wizard Dialog Box, where we Save Our Work
The Cube Editor dialog provides us the opportunity to wrap up a couple of loose ends -- specifically to take care of the missing relationship of which we were warned in the error message just before we saved the cube (see Illustration 34). We'll add the missing information by performing a simple join between the only isolated table (region) and the fact table (sales_fact_1998), as they appear in Illustration 36 below-- Or will we?
Illustration 36: The Cube Editor -- Where we will add the Necessary Join(s)
Adding a join is very simple under normal circumstances. But, as anyone familiar with the nature of proper joins knows, we must have a matching key in both tables (to put it simply) to perform the appropriate association between the tables. A closer examination reveals that no common key appears to exist between the sales_fact_1998 and the region table. We will need to define and use a "bridge" or intermediate table to accomplish a logical association.
To do this, we will need to add a new table to the group already selected; we will accomplish this with the Insert Table icon in this instance, as shown below in Illustration 37. This resurrects the Select Tables dialog that we visited earlier, where we will select the store table (by either highlighting it and clicking Add, or by double-clicking it).
Illustration 37: The Insert Table is a Means of Adding Tables to a Cube
Next, we will close the Select Tables dialog box (see Illustration 38) (it remains open in case multiple selections need to be made -- actually quite convenient for those that use it a great deal). At this point, we see the store table has appeared alongside the other selected tables (see Illustration 39), complete with a wizard-induced join between the region and store tables (the wizard makes "obvious" choices by default. We always need to examine its accuracy, but in this case the association of region_id is certainly correct.
Illustration 38: The Select Tables Dialog, where we Add the store Table
Illustration 39: Viewing the Newly Added store Table in the Cube Editor
Now we need to handle the other side of the "bridge" to sales_fact_1998. We do so by locating the appropriate key pair (again, an easy choice, as both tables contain store_id), highlight store_id in the store table, and drag over with the mouse to the sales_fact_1998 table, where we drop onto the corresponding store_id field. (Should you accidentally connect the wrong two fields, deleting the bad join is as simple as highlighting it by clicking directly on it with the mouse, then right-clicking for a Remove option in the pop-out shortcut menu. Clicking this puts us back to the original state, giving us an opportunity to make the corrections we need.)
We then close the Cube Editor by selecting Exit from the File menu item. We are prompted to save the cube after our changes, to which we respond by clicking Yes. We are then prompted to Design Storage Options in the Design Storage dialog. At this point, we can either save and come back later, or click Yes to design storage now. We will continue to design storage by clicking Yes.
Page 8: Designing Storage and Processing the Cube