Mastering Enterprise BI: Time Intelligence Pt. I - Page 5

February 12, 2007

Examine the Structure of the New Time Dimension

Having created our cube, we can overview the Time dimension we have added, to ascertain that the structure appears to meet our needs.

1.  Within the Dimensions pane (in the lower left corner of the Cube Structure tab), expand the Date 1 dimension (named Date 1 by default, since a dimension named Date already exists within the UDM), by clicking the “+” sign to its immediate left.

Here we can see both the “cloned” and alternate Date hierarchies, as before, as depicted in Illustration 22.

Illustration 22: The Date Dimension (“Date 1”) in the Dimensions Pane ...

2.  Click Edit Date 1, which appears just under the expanded Date 1 dimension.

The Dimension Structure tab for the new Date dimension opens, and appears similar to that shown in Illustration 23.

Illustration 23: The New Date Dimension within the Dimension Structure Tab (Compressed View)

Here we can see details about the makeup of the dimension, including Attributes, Hierarchies and Levels, and so forth. We can also examine, and modify as desired, the Properties for each respective object. We will not make any changes for the purposes of our practice exercise; we will leave everything as defined at this stage.

Generate the Underlying Schema for the Cube Model

Because we have designed our cube in “top-down” fashion, it has no underlying relational database, and, therefore, no underlying data. The next step in a typical design and development evolution (that is, when using the “top-down” approach) would be to generate the underlying relational schema, and then to populate it with data (which would usually be loaded via extraction, transformation, and loading (ETL) processes) from one or more operational / transactional / other external data sources.

We will generate a schema with the rudimentary cube that we have, as a means of illustrating the process; in some cases, as we have noted, this might provide a quick and easy means of generating a Time dimension table which is based upon a structure we have designed within the cube model. This “push-down” method of table construction is not only quick and easy; it assists us in building a table that is guaranteed to support our dimensional design, once populated appropriately (and, as we shall see, population can be set to occur automatically). Once we had generated a table to support the Time dimension, say in a target database created especially to contain such a table, we could move the table(s) to another place, delete the empty tables, etc., as our needs dictated.

1.  Within the Cube Structure tab, click the link Click here to generate data source view, appearing in the middle of the Diagram pane, as depicted in Illustration 24.

The initial page of the Schema Generation Wizard appears, as shown in Illustration 25.

Illustration 25: The Schema Generation Wizard Initializes ...

2.  Click Next.

3.  On the Specify Target page that appears next, click New.

The first page of the Data Source Wizard (titled Welcome to the Data Source Wizard) opens next, and appears as depicted in Illustration 26.

Illustration 26: The Data Source Wizard is Launched ...

4.  Click Next.

5.  Click New on the Select how to define the connection page that appears next, as shown in Illustration 27.

Illustration 27: Create a Data Source Based upon a New Connection

The Connection Manager appears.

6.  Type / select the correct server / instance name into the Server name box of the dialog.

7.  Ensure that authentication settings are correct for the local environment.

8.  In the Connect to a database section, under Select or enter a database name, select DBJ_Schema_Target, the target database we created in our preparation section earlier.

The Connection Manager appears, with our input, as depicted in Illustration 28.

Illustration 28: The Connection Manager with Our Input

9.  Click the Test Connection button.

A message box appears, indicating that the Test connection succeeded, assuming that our changes (or lack of same, as appropriate) are appropriate. The message box appears as shown in Illustration 29.

Illustration 29: Testing Positive for Connectivity ...

10.  Click OK to dismiss the message box.

11.  Click OK to accept changes, and to dismiss the Connection Properties dialog.