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

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 .
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.