Effective End Date from data source

[Lurch2k2]

We wish for data manager to update a record's effective end date with an imported dynamic date. I can probably explain better with an example:



Below is the current table (A):


Currency     |  Rate  |   Effective Start Date   |   Effective End Date  |  Date Updated       |           Date Created
USD           | 1.43    |  01/02/2010 11:00       |   31/12/9999 00:00   | 18/03/2010 13:19  |   18/03/2010 13:19

The effective start date comes from an incoming file, whilst the end date is a static value for new records. Below is an example of an incoming data file (B).

Currency     |  Rate  | Effective Start Date     |
USD           | 1.58   | 02/02/2010 11:00         |


It is desired that the file (B) is appended to the table (A), with Effective end date of the current record becoming equal to the effective Start Date of the new file data minus a second (B). Such as below:


Currency    |  Rate   | Effective Start Date     | Effective End Date |  Date Updated         |  Date Created
USD           |  1.43   | 01/02/2010 11:00        | 02/02/2010 10:59  | 18/03/2010 13:19   |  19/03/2010 13:18
USD           |  1.58    | 02/02/2010 11:00        | 31/12/9999 00:00  | 19/03/2010 13:18    |  19/03/2010 13:18



Values changed or added



Is there any way data manager can handle this? We understand there is effective end date functionality in the SCD options, however this uses the system date and therefore not appropriate for the above example.


Furthermore, can data manager handle missed uploads such as the example below.

Currency    |   Rate  | Effective Start Date  | Effective End Date |    Date Created    |   Date Updated
USD           |   1.33  |  01/02/2010 11:00    | 03/02/2010 10:59  |18/01/2010 13:19  |   20/01/2010 13:18
USD           |   1.48  |  03/02/2010 11:00    | 31/12/9999 00:00  |20/01/2010 13:18  |   20/01/2010 13:18

New data (giving rates for the 2nd Jan)

Currency  |  Rate   | Effective Start Date  |
USD         | 1.58   |  02/02/2010 11:00     |

Table A is updated to:

Currency    |   Rate  | Effective Start Date  | Effective End Date |    Date Created    |   Date Updated
USD          |   1.33   |  01/02/2010 11:00    | 02/02/2010 10:59 |18/01/2010 13:19  |   20/01/2010 13:18
USD           |    1.58  |  02/02/2010 11:00    | 03/02/2010 10:59  |21/01/2010 13:18  |   21/01/2010 13:18
USD          |   1.48   |  03/02/2010 11:00    | 31/12/9999 00:00  |20/01/2010 13:18  |   20/01/2010 13:18


Therefore late arriving data files can be uploaded into the table, and the consistency of the effective end and start dates can be kept. Is this possible in data manager?

To add more complication, if the rate did not change then a new record is not added.

Any help or general ideas would be gratefully received!

[MFGF]

Hi,

The Effective End Date of a row is set based on the Effective Start Date of the next row - either exactly the same date/time or the Effective Start Date -1 second.  This can be configured in the properties of the output template in the dimension build - on the Effective Date Options tab.  So if you use the date from file B as the Effective Start Date, the Effective End Date of the previous row will be set based on this (which I think is what you need).

The Effective Start Date can either be automatically generated based on the run date/time of the build, or you can read it in to the hierarchy feeding the dimension build and deliver this as the Effective Start Date in place of the auto generated one (this will be the date from B in your case).  If you take this route, make sure that the date attribute is set both with a behaviour of Effective Start Date in the input template for the hierarchy and with a behaviour of Effective Start Date in the output template used in the build.

The second question relates to Late Arriving Dimensions.  Dimension builds in Data Manager cannot automatically load these into the dimension, as there are huge consequences to doing this if you have fact tables utilising the surrogate keys in your dimension.  What it can do is to detect and reject Late Arriving Dimensions to a file so you can sort them out manually.  This can be configured on the Dimension History Options tab of the dimension table.

Best regards,

MF.