This tutorial covers how to modify Interactive Reporting data models to accommodate deleted columns in databases.
This tutorial covers the following topics:
|Synchronizing Data Models with Databases|
|Promoting Topics to Meta Topics|
|Renaming Topics and Meta Topics|
|Adding Computed Items to Meta Topics|
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.
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.
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.
Before starting this tutorial, you should:
Ensure that Interactive Reporting Studio is installed on your computer.
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.|
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:
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.
Select DataModel > Sync with Database.
The Data Model Synchronization dialog box is displayed, indicating that there are changes made.
Select Show Detail Information to view the list of changes. Store Code is listed as a deleted item.
Store Code is no longer listed as an item in the Storesregions topic.
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:
Right-click the Storesregions topic title.
From the shortcut menu, select Promote to Meta Topic.
A meta topic named Meta Storesregions is created.
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:
Right-click the Storesregions topic title, and select Properties.
The Topic Properties: Storesregions dialog box is displayed.
In the Topic Name box, enter Storesregions topic, and click OK.
Note: You can change the name to any name that you want.
Right-click Meta Storesregions, and select Properties.
The Topic Properties: Meta Storesregions dialog box is displayed.
In the Topic Name box, enter Storesregions, and click OK.
Note: You must name this meta topic Storesregions.
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:
Select the Storesregions meta topic title.
Select DataModel > Add Meta Topic Item > Server.
The Modify Item dialog box is displayed.
In the Name box, enter Store Code. In the Definition box, enter 'n/a'.
Be sure to include single quotation marks around n/a.
Click Options. In the Datatype list, select String.
Store Code is added to the Storesregions meta topic.
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.
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|
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|
|OBE 3: Accommodating Denormalization of Database Tables|
|OBE 4: Accommodating Deleted Columns in Databases (Currently displayed)|
|OBE 5: Identifying Impacted Documents|
|OBE 6: Replacing Data Models in Impacted Documents|