Accommodating Deleted Columns in Databases


Purpose


This tutorial covers how to modify Interactive Reporting data models to accommodate deleted columns in databases.

Time to Complete


20 minutes

Topics


This tutorial covers the following topics:

Overview
Scenario
Prerequisites
Synchronizing Data Models with Databases
Promoting Topics to Meta Topics
Renaming Topics and Meta Topics
Adding Computed Items to 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 columns are deleted. To do this, you synchronize the existing data model with the database to remove the invalid columns from the data model. Next, you promote topics that had columns removed to meta topics. You then rename meta topics so that they have the original topic names. Lastly, you add computed items to meta topics to represent the deleted columns.

You configure the computed items that represent the deleted columns to equal constant values. For example, you can configure string computed items to return n/a, and numeric columns to return 0. These replacements enable reports to continue working and display the constant values (for example, n/a and 0) for the deleted columns.

Back to Topic List

Scenario


Recently, the database administrator deleted the column TRNADMIN.STORESREGIONS.STORE_CODE. This column is present in the BMV data model and is used by a number of reports. Therefore, you need to make updates to the data model in BMV Datamodel.bqy. Later, you can use Impact Manager to update all affected Interactive Reporting documents with the new data model.

Back to Topic List

Prerequisites


Before starting this tutorial, you should:

1.

Ensure that Interactive Reporting Studio is installed on your computer.

 

2.

Ensure that you have access to the Bookmart sample data in Oracle. To connect to this data source, use the Bookmart Sample Database.oce and provide the user name trnadmin and the password oracle.

3.Ensure that you have access to BMV Datamodel.bqy, which you modified in OBE 3: Accommodating Denormalization of Database Tables.
4.In Oracle, delete the column TRNADMIN.STORESREGIONS.STORE_CODE.

Back to Topic List

Synchronizing Data Models with Databases


You begin the task of updating the data model by synchronizing it with the database. The synchronization task detects that TRNADMIN.STORESREGIONS.STORE_CODE is no longer available in the database and the Store Code item is automatically removed from the Storesregions topic in the data model.

To synchronize the data model with the database, perform the following steps:

1.

Open BMV Datamodel.bqy.

The data model is displayed in the Query section. Notice that Store Code is listed as an item in the Storesregions topic.
 

Open Browser

 

2.

Select DataModel > Sync with Database.

The Data Model Synchronization dialog box is displayed, indicating that there are changes made.

 

3.

Select Show Detail Information to view the list of changes. Store Code is listed as a deleted item.

 

4.

Click OK.

Store Code is no longer listed as an item in the Storesregions topic.

Enable Harvesting

 

Back to Topic List

Promoting Topics to Meta Topics


In this topic, you promote a a data model topic to a meta topic. This operation is required so that you can later add a computed item to the meta topic to represent the deleted column in the database. You cannot add computed items to regular topics.

To promote a topic to a meta topic, perform the following steps:

1.

Right-click the Storesregions topic title.

 

2.

From the shortcut menu, select Promote to Meta Topic.

Select scheduling option

A meta topic named Meta Storesregions is created.

Meta Storesregions meta topic

 

Back to Topic List

Renaming Topics and Meta Topics


You need to rename the meta topic Meta Storesregions as Storesregions so that the meta topic can take the place of the original topic. Because you cannot have two topics named the same, you must first rename the Storesregions topic as something else, and then rename Meta Storesregions.

To rename topics and meta topics, perform the following steps:

1.

Right-click the Storesregions topic title, and select Properties.

The Topic Properties: Storesregions dialog box is displayed.

 

2.

In the Topic Name box, enter Storesregions topic, and click OK.

Note: You can change the name to any name that you want.

 

3.

Right-click Meta Storesregions, and select Properties.

The Topic Properties: Meta Storesregions dialog box is displayed.

 

4.

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

Note: You must name this meta topic Storesregions.

rename Meta Storesregions

 

Back to Topic List

 

Adding Computed Items to Meta Topics


The last step in masking the deleted column in the database is to add a computed item to the Storesregions meta topic. You need to give the computed item the same name as the deleted database column. Define the computed item with a string or numerical value, depending on the data type of the deleted column. In this case, the deleted column Store_Code had string values. Therefore, define the computed item with a string value, such as n/a .

To add a computed item to a meta topic, perform the following steps:

1.

Select the Storesregions meta topic title.

manage task list1

 

2.

Select DataModel > Add Meta Topic Item > Server.

The Modify Item dialog box is displayed.

filterlist

 

3.

In the Name box, enter Store Code. In the Definition box, enter 'n/a'.

Be sure to include single quotation marks around n/a.

navigate list

 

4.

Click Options. In the Datatype list, select String.

navigate list

 

5.

Click OK.

Store Code is added to the Storesregions meta topic.

navigate list

The BMV data model is now updated to accommodate the deleted Store Code column in the database. After you update BMV data models with Impact Manager, reports that reference Store Code will display n/a instead of store codes.

 

Back to Topic List

Summary


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

Synchronize data models with databases
Promote topics to meta topics
Rename topics and meta topics
Add computed items to meta topics

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:

OBE 1: Harvesting Meta Data in Documents
OBE 2: Accommodating Database Table and Column Name Changes
bulletOBE 3: Accommodating Denormalization of Database Tables
bulletOBE 4: Accommodating Deleted Columns in Databases (Currently displayed)
bulletOBE 5: Identifying Impacted Documents
bulletOBE 6: Replacing Data Models in Impacted Documents

Back to Topic List