Historical Storage

Introduction

The application offers a historical storage with which the content of the data can be reproduced at any point in time. Only the documents and tables for technical administration (the data dictionary) are not included.

The historical storage is used for reporting. Depending on the selected reporting date in My Preferences the historical situation in the past will be displayed. On top of that the historical layer can be used in own reports in order to perform historical analyses on the real estate projects.

This chapter describes the functioning and the use of the historical layer.

Historical table

Every table of the application with current information starts with the characters ‘bubs_’. Next to that, every table has a historical table with an identical name, for which the underscore ‘_’ has been replaced by the character ‘h’. The historical table for ‘bubs_codes’ is thus ‘bubshcodes’.

The historical table has the same columns as the current table, plus three extra historical columns in order to register the moment of visibility (‘insight history’):

•h_event: the event stores whether the last action was an addition (‘I’) or a mutation (‘U’). Deleting cannot be explicitly shown and can only be traced by viewing h_date_end.

•h_date_start: the starting date describes the point in time from which the other data in the row became valid.

•h_datum_eind: the end date describes the point in time to which the other data in the row were valid (the end date is included in the validity). In case data is still valid, the value ‘31-dec-9999’ is stated.

Every piece of data has the same technical key during its life which is stored in the field ID. All historical changes of a piece of data are stored in the historical table. The historical data can be sorted on start date. Then, the end date of the previous situation will also be the start date of the consecutive situation. A gap between the end date and the start date will only be created in case a piece of data is (has been) deleted.

Because of performance related reasons, the historical table has identical indexes as the current table, extended with the two historical date columns.

Example

With the below query the history of the user ‘system’ is requested:

 

select h_event

,      h_datum_start

,      h_datum_einde

,      datum_bijgewerkt

,      naam

from   bubshgebruikers

where  aanmeld_code='system'

order

by     h_datum_start

The result is:

Next, the user gets a different name with the statement below:

 

begin

 --

 -- Aanmelden.

 --

 bubs_session.set_session_info

 ( 'bubs_seed.sql'

 , 'install'

 , 'system'

 , 'various'

 , sys_context('userenv', 'ip_address')

 , sys_context('userenv', 'host')

 , 'n/a'

 , to_char(sysdate,'yyyymmddhh24miss')

 )

 ;

 --

 -- Gebruiker system wijzigen.

 --

 update bubs_gebruikers_v

 set    gbr_naam='Voorbeeld historie'

 where  gbr_naam='system'

 ;

 commit;

end;

/

 

The result is:

The second row now has a different end date and the third row is added to the historical table with the current situation.

Historical Reconstruction

You can directly build reports based on the historical tables. You can use the following construction:

 

--

-- Naam van de gebruiker op iedere eerste van de maand.

-- Er worden alleen gegevens getoond vanaf het moment dat de

-- gebruiker is aangemaakt.

--

select klr.dag_datum

,      gbr.naam

from   bubs_kalender klr

join   bubshgebruikers gbr

on     klr.dag_datum between gbr.h_datum_start and gbr.h_datum_einde

where  gbr.aanmeld_code='system'

and    klr.dag_in_maand_nummer = 1

and    klr.jaar_nummer = 2006

order

by     klr.dag_datum

The result is:

dag_datum        naam

1-9-2006        Voorbeeld historie

1-10-2006        Voorbeeld historie

1-11-2006        Voorbeeld historie

1-12-2006        Voorbeeld historie

 

Point in Time Views

It is often wanted to perform a query for a certain historical point in time. For your convenience ‘point in time views’ are already available for all regular views and tables.

These views can be recognized by their name. For every view or table with the name ‘bubs_’ there is a point in time view that starts with ‘bubsp’. For instance, for the view ‘bubs_fpt_r’ with the most current situation of the project there is a point in time view4 ‘bubspfpt_r’.

First, you need to log in to the application by:

 

begin

 --

 -- Aanmelden en tijdstip instellen conform instellingen in

 -- het scherm 'Mijn voorkeuren' of view bubs_context_instellingen_v.

 --

 bubs_session.set_session_info

 ( 'bubs_seed.sql'

 , 'install'

 , 'system'

 , 'various'

 , sys_context('userenv', 'ip_address')

 , sys_context('userenv', 'host')

 , 'n/a'

 , to_char(sysdate,'yyyymmddhh24miss')

 )

 ;

end;

/

Next you need to set a point in time which will be used for all queries on point in time views with:

 

begin

 --

 -- Kies tijdstip 1 uur geleden.

 --

 bubs_session.set_point_in_time(sysdate-1/24);

end;

/

Finally, you can perform the query:

 

select *

from   bubspfpt_r

Extraordinary situations

The point in time views are meant to perform a query against a historical point in time. However, this leads to strange effects if it would simply be applied: not only historical financial figures are used, but also historical context settings and users. It would for instance be impossible for an employee who recently started working to request a historical report for a point in time at which he was not registered as a user yet. That is why several point in time views deviate, which makes that always the current data is used. This concerns:

•BUBS_CONTEXT_SETTING

•BUBS_PROJECT_AUTHORISATIONS

•BUBS_ROLE_AUTOHRISATIONS

•BUBS_ROLES

•BUBS_SETTINGS

•BUBS_USER_ROLES

•BUBS_REPORT_TIME_UNITS

•BUBS_REPORTING_UNITS

•BUBS_LANGUAGES

Moreover, the user list always contains the currently logged in user.

Even with these modifications it is obviously still possible to have some confusion, for example, by selecting in your context settings a project that was not there at the historical point in time for which the report is executed. This underlines the importance of a good understanding of how to execute reports against history.