This tutorial covers how to create a replacement data model for use in Impact Manager when database table and columns names change.
This tutorial covers the following topics:
|Changing Physical Table Names|
|Synchronizing Data Models with Databases|
|Changing Item Names|
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 table and column names change. To do this, you must make three updates to the original data model. First, you must change the physical table names behind the topics in the data model that are affected. Second, you must synchronize the data model with the database. Lastly, you must change display names for items in the data model that are affected.
The data model is stored in the Interactive Reporting document named BMV Datamodel.bqy. It consists of five topics that represent daily store sales for BMV products:
The Products topic represents the PRODUCTS table in the database. Recently, the PRODUCTS table in the database was renamed as BMV_PRODUCTS. Also, two columns in the PRODUCTS table were renamed: PRODUCT_LINE was renamed as BMV_PRODUCT_LINE and PRODUCT_FAMILY was renamed as BMV_PRODUCT_FAMILY. You need to update the original data model to accommodate these changes. In the future, you plan to replace data models in affected Interactive Reporting documents in Workspace with this updated data model.
Before starting this tutorial, you should:
Install Interactive Reporting Studio on your computer .
Create a user in an Oracle database named trnadmin (password = oracle). From the command line, run IMP, and load BMV.DMP into Oracle to create Bookmart sample data. Be sure to connect as trnadmin (password=oracle) when loading the data. Next, create a connection file named Bookmart Sample Database.oce to connect to the Bookmart data in Oracle. Specify trnadmin as the username and oracle as the password. In the database, change the PRODUCTS table name to BMV_PRODUCTS. Rename two columns in the PRODUCTS table, PRODUCT_LINE and PRODUCT_FAMILY, as BMV_PRODUCT_LINE and BMV_PRODUCT_FAMILY.
Save a copy of BMV Datamodel.bqy to your desktop.
In this topic you change the physical table name for the Products topic in the data model. You need to match the physical table name to the new table name in the database, which is BMV_PRODUCTS. Currently, the Products topic points to a database table that no longer exists (PRODUCTS). If left unchanged, queries that contain items from the Products topic will no longer work.
To change the physical table name behind the Products topic:
In Interactive Reporting Studio, open BMV Datamodel.bqy.
On the Status bar, double-click the connection icon .
In the Host User box, enter trnadmin. In the Host Password box, enter oracle. Click OK.
You are now connected to the Bookmart Sample Database and the Query section is displayed. The data model is displayed in the content pane.
In the content pane, right-click the Products topic title, and select Properties.
In the Physical Name box, change TRNADMIN.PRODUCTS to TRNADMIN.BMV_PRODUCTS, and click OK.
Interactive Reporting Studio provides a Sync with Database option that enables you to automatically update a data model in a document with current database table and column information. The operation detects inconsistencies with the database, updates the data model, and provides an itemized list of the changes made. You need to perform a Sync with Database operation to update the data model in BMV Datamodel.bqy.
Items are listed in data model topics and represent columns in database tables. Item names are purely semantic in the Query section and do not need to match the physical column names in the database. You cannot manually edit physical column names for items, like you can with topics. That is one of the key reasons why you need to perform a sync with database operation.
It is important to note these facts:
|The underlying physical column names for items are not simply renamed during synchronization. Items are removed and new items are added, giving the appearance that items are renamed.|
|When you select the Sync with Database command from the DataModel menu, there is no Cancel option. Therefore, before you select, you should save a backup copy of your data model.|
To perform a Sync with Database operation:
With BMV Datamodel.bqy open and the Query section displayed, select DataModel > Sync with Database.
The synchronization operation is executed and the Data Model Synchronization dialog is displayed.
Select Show Detail Information to view the synchronization details.
Notice that the Products topic name is still the same. This is because topics are a semantic layer. Also notice that the Product Line and Product Family items are removed and two new items, Bmv Product Line and Bmv Product Family, are displayed.
The item names that were once available in the original data model must be available in the updated data model. This is because the original item names may be referenced in queries in other documents that use the data model. If the items were missing, you would not be able to complete a data model update in Workspace with Impact Manager. Therefore, in the Products topic, you must rename Bmv Product Line as Product Line, and rename Bmv Product Family as Product Family.
To change item names:
In the Products topic, right-click Bmv Product Line, and select Properties.
Change the item name to Product Line, and click OK.
In the Products topic, right-click Bmv Product Family, and select Properties.
Change the item name to Product Family, and click OK.
Save BMV Datamodel.bqy.
You successfully updated the original data model. You can now use it in Impact Manager as a replacement data model for other Interactive Reporting documents stored in Workspace.
In this tutorial, you learned how to perform the
|Change physical table names behind topics in Interactive Reporting data models|
|Synchronize data models with databases with Interactive Reporting Studio|
|Change item names in Interactive Reporting data models|
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 (Currently displayed)|
|OBE 3: Accommodating Denormalization of Database Tables|
|OBE 4: Accommodating Deleted Columns in Databases|
|OBE 5: Identifying Impacted Documents|
|OBE 6: Replacing Data Models in Impacted Documents|