Skip to Content

Time is a key dimension of data, and integral to its meaning and importance.  Any data that changes has a temporal quality – taking on different values at  different times.  It is often necessary to be able to look at a snapshot of what the world looked like at some point in the past, and take action based on that state.  Consider these kinds of scenarios:

 

  • An internal audit requires a company to report on changes made to financial data during the past five years.
  • A pending lawsuit prompts a hospital to review a patient’s medical data just before a new treatment was ordered.
  • A client challenges an insurance agency’s resolution of a claim involving a car accident. The agency needs to determine the policy’s terms in effect when the accident occurred.
  • A retailer needs to ensure that no more than one discount is offered for a given product during any period of time.
  • A customer inquiry reveals a data entry error involving a three-month introductory interest rate on a credit card. The bank needs to retroactively correct the error.

These are critical questions, and require the ability to reach back in time and query data as it existed in the past.   How do you easily maintain snapshots of data in SAP IQ, so that you can answer questions based on date and time?  SAP’s own Mark Mumy from our Global Center of Excellence, recently shared the following approach that he has used in real world implementations.

 

Suppose you want to find out the state of data at a particular point in time.  For database tables that store changing data, do the following:

  

Imagine that you have this data for table “insurance_policy”:

policy_id policy_details effective_date end_date
11111 Policy terms A …. 2001-01-01 2002-12-31
11111 Policy terms B…. 2003-01-01 2003-06-30
11111 Policy terms C … 2003-07-01 2004-03-31
11111 Policy terms D … 2004-04-01 2007-07-31
11111 Policy terms E … 2007-08-01 9999-12-31

So let’s say you are investigating a policy claim made in 2005, and want to know what the associated insurance policy terms were at that time.  You would issue the following query:

 

SELECT policy_details

FROM insurance_policy

WHERE policy_id = ‘11111’ and ‘2005-01-01’ between effective_date and end_date

    

This would pull the fourth row of data, because 2005-01-01 is between the effective_date and end date of this row.  This gives you the details of the insurance policy as of January 1, 2005.

 

So that’s it – a simple and low overhead way to represent and query temporal data in SAP IQ.

You can check out Mark Mumy’s very informative blogs about SAP IQ here: http://scn.sap.com/people/markmumy/blog

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply