Bitemporal modeling with InfoSphere Data Architect, Part 1: Modeling application period temporal requirement

Industry regulations and competitive pressures are prompting IT departments to maintain more data for longer periods of time and to provide better ways for business users to analyze past, current, and future events. In order to achieve these goals, firms are developing time-aware applications and queries with temporal databases. Beginning with IBM® InfoSphere® Data Architect version 7.6, you can model relational databases as well as temporal relational databases. This two-part series will demonstrate how you can accelerate temporal data modeling with a DB2 for z/OS version 10 database by adopting InfoSphere Data Architect 7.6.

Balaji Kadambi (bkadambi@in.ibm.com), Advisory Software Engineer, IBM

Photo of Balaji KalambiBalaji Kadambi is an Advisory Software Engineer at the IBM lab in Bengaluru, India. He is currently part of the InfoSphere Data Architect development team, and was involved in developing the bitemporal feature in InfoSphere Data Architect V7.6.0. His areas of interest includes Data modeling and Databases. Balaji has over twelve years of experience working in the commercial software industry.



Rahul Jain (rahjain8@in.ibm.com), Staff Software Engineer, IBM

Photo of author Rahul JainRahul Jain is a Staff Software Engineer at the IBM lab in Bengaluru, India. He is currently part of the InfoSphere Data Architect development team. His areas of expertise includes Data modeling and Eclipse platform technologies. Rahul has a master's degree in Information Technology from IIIT, Bengaluru, India.



17 November 2011

Introduction

This article series is presented in two parts. This article (Part 1) describes application-period temporal data modeling and shows how you can leverage InfoSphere Data Architect 7.6 to perform the following tasks.

  • Create a temporal logical data model.
  • Transform the temporal logical data model into a temporal physical data model for DB2® for z/OS® version 10 (New-Function mode).
  • Generate DDL with temporal-specific syntax for DB2 for z/OS version 10 (New-Function mode) that you can run on the database server.

Part 2 in this series describes bitemporal modeling that requires modifications to an existing model, and shows how you can use InfoSphere Data Architect version 7.6 to add bitemporal support to a physical data model.


Problem description

You are a member of the IT division of the Sample Bank. Your team wants to develop an application for the home loan department. The home loan department has specified the following requirements that the application must account for.

  • The bank offers home loans to borrowers at variable interest rates that are linked to a benchmark rate that is specified by the Central Bank for a loan duration that the customer specifies.
  • The bank revises the home loan interest rates every time that they receive notification that the benchmark rate will be modified.
  • The notification for the benchmark rate change is issued by the Central Bank two weeks before the actual date that the rate change is effective. The home loan interest rate is then calculated based on the new benchmark rate and is manually entered into the system.
  • The monthly installment bill is based on the loan amount and interest rate at that time.
  • The interest rate changes must be tracked over the duration of the loan.

Model design

As shown in Figure 1, you create a simplified version of the logical data model called Loan_Model.ldm, that contains entities, attributes, and relationships that model the non-temporal requirements that are listed in the previous problem description.

Figure 1. Simplified logical data model of the loan system
model shows loan with relationship to borrower and loan interest

The LOAN entity models information about a particular loan, including the following.

  • LOAN ID: unique identifier code for a loan disbursed by the bank.
  • BORROWER ID: unique identifier code for the borrower.
  • LOAN AMOUNT: amount of the loan disbursed by the bank to the borrower.

The BORROWER entity models information about a borrower, including the following.

  • BORROWER ID: unique identifier code for the borrower.
  • BORROWER NAME: name of the borrower.
  • BORROWER ADDRESS: address of the borrower.

The LOAN INTEREST entity models the information about the interest rate and expected installment payments of a borrower towards a loan, including the following.

  • LOAN ID: unique identifier code for a loan disbursed by the bank.
  • INTEREST: variable interest rate for the loan that is computed based on the benchmark rate.
  • INSTALLMENT AMOUNT: the installment amount that the borrower pays, based on the variable interest rate.

Creating application-period temporal attributes in an entity

The current design does not allow the changes in the interest rate to be tracked. In order to track the changes in interest rate for a loan, you decide to add business-period attributes to the LOAN INTEREST entity. The attributes model a way to track the changes to the interest rate over a period of time that is relevant to the business. You are sure to specify that there is no overlap between the business periods, because there is only one interest rate that is associated with a loan during this business period.

Note: The terms application period and business period are used interchangeably in this article. Both terms are phrases that describe how to model changes over a period of time that is relevant to the business.

This can be achieved by performing the following steps.

  1. Select the LOAN INTEREST entity in the Data Project Explorer. The properties of the entity display in the Properties view.
  2. Create business-period attributes in the entity by performing the following steps.
    1. Select the Business time period check box to add a business period.
    2. Select the Business period without overlap option to specify that there is no overlap of business periods for the selected LOAN INTEREST entity, as shown in Figure 2.
    Figure 2. Adding business-period attributes to the LOAN INTEREST entity
    This screen shows the adding of attributes for business-period to the loan interest entity
    When you selected these options, the APPLICATION_START_TIME and APPLICATION_END_TIME attributes were added to the LOAN INTEREST entity that model the ability to track and query changes to the interest rate.

    Note: You cannot delete the application-period columns. The Delete icon is disabled for application period attributes, as shown previously in Figure 2. When you disable the business-period attributes, the relevant attributes are automatically removed.
  3. The updated logical data model with business-period attributes is shown in Figure 3.
    Figure 3. Including temporal features in the updated logical data model
    This screen shows the temporal features that are included in the logical data model that was updatedNote: The icon for the temporal entity is different from the icon that denotes a standard entity. The temporal entity icon contains a clock decorator, as seen on the LOAN INTEREST entity previously shown in Figure 3.

Transform the logical data model to a physical data model

You have approved the logical data model design with the home loan department of the Sample Bank. In order to model the physical storage, you must transform the temporal logical data model into a temporal physical data model for the DBMS that you use, DB2 for z/OS version 10 (New-Function mode).

Perform the following steps to transform the model.

  1. Right-click the logical data model, then select Transform to Physical Model from the context menu, as shown in Figure 4.
    Figure 4. Transforming to a physical data model
    This screen shows Loan_Model.ldm being right-clicked and then selecting Transform to Physical Data Model.
  2. Select the Create new model option on the Target Physical Data Model page.
  3. From the Physical Data Model File page, specify a file name. Then, select DB2 for z/OS and V10 (New-Function Mode) as shown in Figure 5.
    Figure 5. Specify database, version, and location for transformation
    This Physical Data Model File screen has the Database: DB2 for z/OS and Version selected.Note: As of version 10, DB2 for z/OS supports temporal models in New-Function mode. As of version 7.6, InfoSphere Data Architect supports modeling for this database type.
  4. From the Options page, specify LOAN_SCHEMA in the Schema name field, as shown in Figure 6. Keep all of the other default options.
    Figure 6. Transform options page
    Image shows the transformation options that are available
  5. From the Output page, view the transformation status, then click Finish to generate the physical data model. The physical data model Loan_Model.dbm is created from the logical data model.

Generated temporal physical data model

Inspect the physical data model in the Data Project Explorer, as shown in Figure 7.

Figure 7. Viewing the physical data model
This screen shows the LOAN_SCHEMA highlighted, representing the physical data model

Upon inspecting the physical data model, you find that the following items are true.

  1. The LOAN_SCHEMA schema is created.
  2. Three tables, LOAN_INTEREST, BORROWER, and LOAN are created with the columns that were transformed from the attributes of the logical data model.
  3. The LOAN_INTEREST table is a temporal table.
  4. Two foreign-key relationships, LOAN_BORROWER_FK and LOAN_INTEREST_LOAN_FK, are created.

A diagram view of the loan physical data model is shown in Figure 8.

Figure 8. Loan physical data model
This screen shows the diagram view of the loan model, with Loan_Borrower_FK and Loan_Interest_Loan_FK

You can select the LOAN_INTEREST table in the Data Project Explorer to display the properties of the table in the Properties view. From here, you can verify that the Business time period and Business period without overlap options are selected, as shown in Figure 9.

Figure 9. Properties view of LOAN_INTEREST table
This screen shows the Loan_Interest table properies view, including Business time period and Business period without overlap.

Now that you've verified the model, you should analyze the model, correct any errors, and then generate the DDL script that you can share with the database administrator in order to deploy your changes.


Analyze the model

Before the DDL is generated, use the Analyze Model wizard to analyze the physical data model for common design errors. Because InfoSphere Data Architect version 7.6 supports DB2 for z/OS version 10 (New-Function Mode) temporal modeling, the workbench can analyze models for common temporal design errors.

To analyze the model, perform the following steps.

  1. Right-click the location (database) node of the physical data model, then select Analyze Model as shown in figure 10 to open the Analyze Model wizard. Because you selected a physical data model, the options for physical data models are selected by default.
    Figure 10. Analyze Model
    This screen shows the Loan_Schema right clicked from the Analyze model.
  2. Analyze the temporal properties by clearing the Physical Data Model check box.
  3. Select the Temporal Check option. Click Finish.

Generate DDL

Now that you have validated your model, perform the following steps to generate the DDL script to deploy the model, then share the script with the database administrator to run the script on the database server.

  1. Right-click the LOAN_SCHEMA schema in the Data Project Explorer.
  2. Select Generate DDL as shown in Figure 11.
    Figure 11. Generate DDL for the new schema
    This screen shows the Loan_Schema right-clicked, and Generate DDL selected
  3. Complete the following pages of the Generate DDL wizard.
    • Do not change the default settings on the Options page.
    • Do not change the default settings on the Objects page.
    • On the Save and Run DDL page, as shown in Figure 12, specify a file name. Keep the file name in mind so that you can easily share the file with a database administrator so that they can deploy your changes.
      Figure 12. Save and Run DDL page
      Figure 12 shows the Save and Run DDL screen with 'Run DDL on server' check box selected.
  4. If your organization allows you to deploy data models directly to the server, you can select the Run DDL on Server option before you click Next.
  5. On the Select Connection page, select an existing connection as shown in Figure 13, or specify a new connection by clicking New.
    Figure 13. Selecting a connection
    This screen shows the Select Connection page for running the DDL script
  6. Verify the options that you selected on the Summary page, and click Finish to generate the DDL.

Inspect the deployed model in the database server

After the database administrator runs the DDL script, you can inspect the deployed changes in the Data Source Explorer by expanding the nodes down to the LOAN_SCHEMA schema. Upon viewing the model, verify that the BORROWER, LOAN, and LOAN_INTEREST tables were added under the LOAN_SCHEMA schema, as shown in Figure 14.

Figure 14. Viewing the deployed loan model
This screen shows the Loan_Schema hightlighted on the Data Source Explorer page

You can further verify some of your changes. From the Properties view of the workbench, select the LOAN_INTEREST table to display its properties. When you open the Columns tab, you see that the LOAN_INTEREST table is a business-period table that doesn't allow for overlapping time periods, as shown in Figure 15.

Figure 15. Verifying updates for the LOAN_INTEREST table
This screen shows Business period without overlap checked

Conclusion

In this article, you learned how to fulfill application-period temporal requirements for a home loan department of a bank. You learned how InfoSphere Data Architect version 7.6 and DB2 for z/OS version 10 (New-Function Mode) can provide a convenient way to model temporal data. With little effort, you transformed a logical data model that included a temporal entity into a physical data model with a temporal table. Then, you analyzed the model and generated the DDL script that is required to deploy those changes to the target database.

In Part 2 of this series, you will learn how InfoSphere Data Architect version 7.6 helps you model bitemporal requirements for data.


Acknowledgements

Thanks to Erin Wilson for the valuable suggestions and the review of this article.

Resources

Learn

Get products and technologies

  • Build your next development project with IBM trial software, available for download directly from developerWorks, or spend a few hours in the SOA Sandbox learning how to implement Service Oriented Architecture efficiently.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=774982
ArticleTitle=Bitemporal modeling with InfoSphere Data Architect, Part 1: Modeling application period temporal requirement
publish-date=11172011