Introduction to MSSQL Server Analysis Services: Mastering Enterprise BI: Relative Time Periods in an Analysis Services Cube, Part II - Page 4

July 11, 2005

Modify the Database to Support a Relative Time Dimension in the Cube

Let's create a couple of objects within our new MS Access database to support relative time periods within the sample cube we will use later in our exercises. Again, we are doing this in MS Access to leverage existing samples and save time – the concepts are similar, although methods vary somewhat, in the more robust MSSQL Server environment.

1.  Open the RelativeTimesample.mdb in MS Access (I'm using Access 2003 in my illustrations, but Access 2000 and beyond will work, and will be very similar in operation).

2.  Get to the Database View for the database.

3.  Click the New button atop the view, as depicted in Illustration 8.

Click for larger image

Illustration 8: Adding a Table in the Database View – FoodMart 2000.mdb

4.  Select Design View from the New Table dialog that appears, as shown in Illustration 9.

Illustration 9: New Table Dialog: Select Design View

5.  Click OK.

Design View for the new table opens.

6.  Type the information presented in Table 1 into the designated places in the Design View.

Field Name

Data Type





Table 1: Creating a New Table - Design View

Design View for the new table appears as partially depicted in Illustration 10.

Illustration 10: The New Table in Design View (Partial View)

7.  Select View --> Datasheet View from the main menu, as shown in Illustration 11.

Illustration 11: Shift to Datasheet View

A dialog appears, stating that we must save the table first.

8.  Click Yes to save the table, as depicted in Illustration 12.

Illustration 12: Notice to Save the Table First ...

9.  Type the following into the Save As dialog that next appears:


The completed Save As dialog for the new table appears, as shown in Illustration 13.

Illustration 13: Saving the New Table in the Sample Database

10.  Click OK to save the table and close the Save As dialog.

11.  Click No on the next dialog to appear, to pass on creating a Primary Key in this practice instance, as depicted in Illustration 14.

Illustration 14: Just Say "No" to Creating a Primary Key ...

The table opens in Datasheet View.

12.  Type the information assembled in Table 2 into the designated columns of the table within Table View.






Table 2: Inserting Data into the Table - Datasheet View

NOTE: The "0" appears in the second row when we type the "1" into the first row.

Datasheet View for the table appears as shown in Illustration 15.

Illustration 15: The New Table in Datasheet View (Partial View)

13.  Select File --> Close from the main menu to close the table and return to Data View.

The new relativetime table appears among the other tables, in Data View for the database, as depicted in Illustration 16.

Illustration 16: The New Table Appears in Database View