Solving Operational Business Intelligence with InfoSphere Warehouse Advanced Edition

Chapter 4. Data modeling: End to end 107

process for performing this action is to create a new physical data model, which

in turn can be utilized to create a DDL file that can be used to build the new

database.

Perform these steps to create the DDL file that represents the dimensional

logical model:

1. From within InfoSphere Data Architect, select and right-click our dimensional

logical model IND_CST_DYN_FACT.ldm to bring up the option menu. Select

Transform to Physical Data Model.

In the wizard, make sure the Create new model option is selected, then click

Next.

2. In the next panel, specify the version of DB2 or other relational database, that

will host this database structure. In our example, we use DB2 10.1. Click

Next.

3. In the final panel, you can change the schema name to an appropriate name.

We use CSTINSIGHT_NEW. Click Finish to save the new model

IND_CST_DYN_FACT_D.dbm.

From this new physical model you have to create a DDL file, which can be used

to create the new schema within your InfoSphere data warehouse. Perform

these steps:

1. Select and right-click the new physical model IND_CST_DYN_FACT_D.dbm to

bring up the option menu. Select Generate DDL.

2. Within the wizard, accept the default database elements that must be

created. Click Next until at the last panel, then Finish. The wizard then

creates the DDL file for you.

This DDL file can then be implemented on your database server to create the

database structure as defined within your model.

4.4 OLAP modeling and cubes

We have demonstrated that you can create an efficient database structure best

geared toward OLAP queries, and ultimately, toward answering business

intelligence questions by using one of the following methods:

򐂰 Using the derived logical dimensional model and its associated physical data

model

򐂰 Taking advantage of the InfoSphere Warehouse Insight Packs included within

InfoSphere Warehouse Advanced Enterprise edition

108 Solving Operational Business Intelligence with InfoSphere Warehouse Advanced Edition

After the data is populated, this new structure is ready for use by the BI

applications, such as IBM Cognos. Although these BI applications can directly

access the data within the database, there is still a more efficient and effective

mechanism that can be introduced to perform this task. OLAP modelling

provides a method by which these BI applications can communicate with the

users in a dimensional language closer to the way humans think, while at the

same time providing a business abstraction layer between the data warehouse

and these BI applications.

Within InfoSphere Warehouse, OLAP modeling is defined as a cube model,

which can then be implemented as a cubing service. Although a star schema can

begin to provide an abstraction to a relational database, it lacks the depth and

richness of OLAP objects such as hierarchies, levels, and derived measures and

attributes. OLAP cubes also provide an enhanced ability to capture business

rules and logic, which is not as easy or intuitive within a relational database.

You might ask, why is this called a cube model?

Questions asked by business analysis such as “what is the sum of all sales” or

“how many stores are there” can easily be answered by a normal SQL query of a

relational database. When taken in context of your logical data model, these

types of questions only relate to one dimension within the model. Business

questions, however, are generally of the form that ask about something (a

dimension)

by something (another dimension), and might be even by something

again. This form of multidimensional analysis is best suited to a cube model,

where data can be analyzed from multiple perspectives.

Consider that you have to analyze the sales data for a number of products in a

range of countries that you have stores, for all periods of time. To help visualize

the data from these multiple perspectives, think of a cube with each of the axes

representing a dimension. For example, consider country, product, and time.

There is also an implicit fourth dimension measure, which is the sales amount.

Figure 4-15 on page 109 depicts this cube, which itself has then been divided

into a number of mini-cubes. Each mini-cube contains a value (or measure) for

that specific intersection of the country, product, and time. As an example, the

darker mini-cube (shown in blue) represents the sales for the first quarter of 2008