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

Back to Topic List

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:

Open Browser

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.

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

Back to Topic List

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 .

Select scheduling option


 

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.

 

Back to Topic List

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:

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:

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.

 

Back to Topic List

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.

manage task list1

 

2.

Change the item name to Product Line, and click OK.

filterlist


 

3.

In the Products topic, right-click Bmv Product Family, and select Properties.

navigate list

 

4.

Change the item name to Product Family, and click OK.

navigate list

 

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.

 

Back to Topic List

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

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:

Harvesting Meta Data in Documents
Accommodating Denormalization of Database Tables

Back to Topic List