Accommodating Database Table and Column Name Changes
Purpose
This tutorial covers how to create a replacement data model for use in Impact Manager when database table and columns names change.
Time to Complete
15 minutes
Topics
This tutorial covers the following topics:
 | Overview |
| Scenario |
| Prerequisites |
| Changing Physical Table Names |
| Synchronizing Data Models with Databases |
| Changing Item Names |
| 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 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.
Scenario
The BMV 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.
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, 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. |
| 3. | Have access to BMV Datamodel.bqy. This is the original data model. Save a copy of BMV Datamodel.bqy to your desktop. |
Changing Physical Table Names
In this topic you change the physical table name for the Products topic in the BMV 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. 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:
| 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. | In the content pane, right-click the Products topic title, and select Properties.
|
| 5. | In the Physical Name box, change hyperion.products to hyperion.BMV products, and click OK.
|
Synchronizing Data Models with Databases
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 BMV Data model.
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:
To perform a Sync with Database operation:
| 1. | 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.
|
| 2. | Select Show Detail Information to view the synchronization details.
|
| 3. | Click OK. Notice that the Products topic name is still the same. This is because topics are a semantic layer. Also notice that the columns Product Line and Product Family are removed and two new columns, Bmv Product Line and Bmv Product Family, are displayed.
|
Changing Item Names
The item names that were once available in the original BMV 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 BMV 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:
| 1. | In the Products topic, right-click Bmv Product Line, and select Properties.
|
| 2. | Change the item name to Product Line, and click OK.
|
| 3. | In the Products topic, right-click Bmv Product Family, and select Properties.
|
| 4. | Change the item name to Product Family, and click OK.
|
| 5. | Save BMV Datamodel.bqy. You successfully updated the original BMV data model. You can now use it in Impact Manager as a replacement data model for other Interactive Reporting documents stored in Workspace.
|
Summary
In this tutorial, you learned how to perform these tasks:
| 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 |
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 Denormalization of Database Tables |
Printer View