Accommodating Denormalization of Database Tables


Purpose


This tutorial covers how to create a replacement data model for Impact Manager when database tables are consolidated.

Time to Complete


15 minutes

Topics


This tutorial covers the following topics:

Overview
Scenario
Prerequisites
Adding Consolidated Topics to Data Models
Creating Meta Topics
Summary
Related information

Viewing Screenshots


Click icon to show all screenshots Click icon to show all screenshots

Note: Alternatively, you can click an individual icon (or image) associated with each step to view (or hide) the screenshot associated with that step.

Overview


This tutorial covers how to create a replacement data model, for use by Impact Manager, for Interactive Reporting documents in Workspace when database tables are consolidated. To do this, you add the new consolidated tables to the original data model. You also add meta topics to represent the tables that no longer exist in the database.

Back to Topic List

Scenario


The BMV data model continues to be used in your organization. Recently, the database administrator consolidated tables. Two of the tables used in the BMV data model are affected by this change - the Stores and Regions tables. Now there is only one table, named Storesregions, instead of two:

Open Browser

You must update the BMV data model to accommodate this table denormalization. To do this, you first need to add the Storesregions topic to the BMV data model and update the joins. Second, you need to create two meta topics from the Storesregions topic to represent the former Stores and Regions tables.

Back to Topic List

Prerequisites


Before starting this tutorial, you should:

1.Have Interactive Reporting Studio installed on your computer.
2.Have access to the MS SQL Server database named Bookmart Sample Database through the connection file named Bookmart Sample Database.oce. The user name is analyst and the password is password. In the database, create a table named analyst.storesregions that combines the data from the Stores and Regions tables.
3.Have access to BMV Datamodel.bqy. This is the updated data model that you modified in OBE 2: Accommodating Database Table and Column Name Changes. Save a copy of BMV Datamodel.bqy to your desktop.

Back to Topic List

Adding Consolidated Topics to Data Models


The first step in updating data models after table consolidation in the database is to add topics to the original data model. For the BMV data model, you need to add the Storesregions topic. After you add the Storesregions topic, you need to configure the joins between it and the Sales Fact topic.

If you have the Auto Join feature enabled in Interactive Reporting Studio, you should disable it while you add the consolidated table. This will prevent several unwanted joins that you would have to manually remove one by one.

To add the Storesregions topic to the data model:

1.

In Interactive Reporting Studio, open BMV Datamodel.bqy.

 

2.

On the Status bar, double-click the connection icon .

Select scheduling option

 

3.

In the Host User box, enter analyst. In the Host Password box, enter password. Click OK.

You are now connected to the Bookmart Sample Database.

 

4.

Next to Tables in the Table catalog, click the plus sign to view the list of database tables.

 

5.

Scroll down the list of tables, right-click storesregions, and select Add Selected Items.

 

6.

In the Content pane, join Store Key in the Storesregions topic to Store Key in the Sales Fact topic.

 

Back to Topic List

 

Creating Meta Topics


Meta topics are a semantic layer in a Query section that represent data model topics. In Interactive Reporting data models, data model developers often use meta topics to mask the complexity of joins and topics for users. In this case, you need to use meta topics to continue a semantic layer defined in other documents, from which queries and reports are built.

When Impact Manager updates Workspace documents with an updated data model, it confirms that all original topics and items are still defined in the new data model. If some are missing, it informs you that it cannot proceed with the update operation. Therefore, you need to create two meta topics to represent the former Stores and Regions topics. You populate the meta topics with items from the new Storesregions topic.

Although it is not necessary, you can keep the original topics in the data model until after you have created the meta topics. It makes populating the meta topics easier because you have the original topics as a reference. You do, however, have to provide alternative meta topic names because you cannot have the meta topics and the original topics sharing the same names. After the original topics are removed from the data model, you can rename the meta topics to match the original topic names. In the case of the BMV data model, you can remove the original topics (Stores and Regions) prior to adding the meta topics because the meta topic items are easy to identify in the Storesregions topic.

To create the Stores and Regions meta topics:

1.

In the Content pane, right-click the Stores topic, and select Remove.

The Stores topic is removed from the BMV data model.

 

2.

In the Content pane, right-click the Regions topic, and select Remove.

The Regions topic is removed from the BMV data model.

 

3.

In Interactive Reporting Studio, select DataModel > Add Meta Topic.

The Topic Properties dialog is displayed.

 

4.

In the Topic Name box, enter Stores, and click OK.

An empty Stores meta topic is displayed in the Content pane.


 

5.

Select DataModel > Add Meta Topic.

The Topic Properties dialog is displayed.

 

6.

In the Topic Name box, enter Regions, and click OK.

An empty Regions meta topic is displayed in the Content pane.

 

7.

In the Storeregions topic, select all items from Store Key down to Sqft, and drag the items to the Stores meta topic.

The Stores meta topic is populated with the items from the Storesregions topic.

 

8.

In the Storesregions topic, select all items from Region Key down to Country, and drag the items to the Regions meta topic.

The Regions meta topic is populated with the items from the Storesregions topic.

You have completed updating the BMV data model to accommodate the denormalization of the Stores and Regions tables in the Bookmart Sample Database.

 

Back to Topic List

Summary


In this tutorial, you learned how to perform these tasks:

Add consolidated topics to existing data models
Create meta topics to represent deleted database tables

Back to Topic List

Related Information

To learn more about Impact Manager, you can refer to other OBEs in this series on the OTN Web site:

Harvesting Meta Data in Documents
Accommodating Database Table and Column Name Changes

Back to Topic List