View your Time Series
Embedded Files
Now access your Time Series
Now access your Time Series
EMP-DEPT
EMP-DEPT
We will use the EMP and DEPT tables, joined by business keys, to show how Entity Life Histories (at known transaction times) and Audits of Corrections (at known valid times) can be accessed as a Time Series.
Notice how the time context is set before the Select statement.
Entity Life History (at known transaction times)
Entity Life History (at known transaction times)
Select
Select
set TRANS_TIME();
select
e.name
,e.job
,e.sal
,e.comm
,e.soe as hire_date
,e.sov as from_date
,e.eoe as terminate_date
from EMP_Y e
where e.id = 123;
<Fred, Sales Intern, Luke, 30000, 500, Sales, Manchester, soe3, soe3, eoe2>
<Fred, Salesman, Luke, 100000, 2000, Sales, Manchester, soe3, sov1, eoe2>
<Fred, Salesman, Luke, 100000, 2000, Sales, Manchester, soe3, sov3, eoe2>
<Fred, Technical Support, John, 130000, 0, IT, London, soe3, sov4, eoe2>
set TRANS_TIME(t3);
select
e.name
,e.job
,e.sal
,e.comm
,e.soe as hire_date
,e.sov as from_date
,e.eoe as terminate_date
from EMP_Y e
where e.id = 123;
<Fred, Salesman, Luke, 100000, 2000, Sales, Manchester, soe2, soe2, eoe1>
<Fred, Technical Support, John, 130000, 0, IT, London, soe2, sov3, eoe1>
Select
Select
set TRANS_TIME(t2);
select
e.name
,e.job
,e.sal
,e.comm
,e.soe as hire_date
,e.sov as from_date
,e.eoe as terminate_date
from EMP_Y e
where e.id = 123;
<Fred, Salesman, Luke, 100000, 2000, Sales, Manchester, soe1, soe1, eoe1>
Select
Select
set TRANS_TIME(t1);
select
e.name
,e.job
,e.sal
,e.comm
,e.soe as hire_date
,e.sov as from_date
,e.eoe as terminate_date
from EMP_Y e
where e.id = 123;<no rows>
<no rows>
Audit History (at known valid times)
Audit History (at known valid times)
Select
Select
set TRANS_TIME(t4);
select
e.name
,e.job
,e.sal
,e.comm
,e.soe as hire_date
,e.sov as from_date
,e.eoe as terminate_date
from EMP_X e
where e.id = 123;
<no rows>
Select
Select
set TRANS_TIME(t3);
select
e.name
,e.job
,e.sal
,e.comm
,e.soe as hire_date
,e.sov as from_date
,e.eoe as terminate_date
from EMP_X e
where e.id = 123;
<Fred, Technical Support, John, 130000, 0, IT, London, soe2, sov3, eoe2>
<Fred, Technical Support, John, 130000, 0, IT, London, soe2, sov3, eoe2>
<Fred, Technical Support, John, 130000, 0, IT, London, soe2 sov3, eoe2>
<Fred, Technical Support, John, 130000, 0, IT, London, soe3 sov3, eoe2>
<Fred, Technical Support, John, 130000, 0, IT, London, soe3 sov4, eoe2>
Select
Select
set TRANS_TIME();
select
e.name
,e.job
,e.sal
,e.comm
,e.soe as hire_date
,e.sov as from_date
,e.eoe as terminate_date
from EMP_X e
where e.id = 123;
<Fred, Salesman, Luke, 100000, 2000, Sales, Manchester, soe1, soe1, eoe1>
<Fred, Technical Support, John, 130000, 0, IT, London, soe1, sov3, eoe1>
<Fred, Technical Support, John, 130000, 0, IT, London, soe2, sov3, eoe1>
<Fred, Technical Support, John, 130000, 0, IT, London, soe2, sov3, eoe2>
<Fred, Technical Support, John, 130000, 0, IT, London, soe2, sov3, eoe2>
<Fred, Technical Support, John, 130000, 0, IT, London, soe2, sov3, eoe2>
<Fred, Technical Support, John, 130000, 0, IT, London, soe3, sov3, eoe2>
<Fred, Sales Director, null, 130000, 0, Sales, Manchester, soe3, sov3, eoe2>
Select
Select
set TRANS_TIME(t2);
select
e.name
,e.job
,e.sal
,e.comm
,e.soe as hire_date
,e.sov as from_date
,e.eoe as terminate_date
from EMP_X e
where e.id = 123;
<Fred, Salesman, Luke, 100000, 2000, Sales, Manchester, soe2, soe2, eoe1>
<Fred, Salesman, Luke, 100000, 2000, Sales, Manchester, soe2, soe2, eoe2>
<Fred, Sales Intern, Luke, 30000, 500, Sales, Manchester, soe2, soe2, eoe2>
<Fred, Sales Intern, Luke, 30000, 500, Sales, Manchester, soe2, soe2, eoe2>
<Fred, Sales Intern, Luke, 30000, 500, Sales, Manchester, soe3, soe3, eoe2>
<Fred, Sales Intern, Luke, 30000, 500, Sales, Manchester, soe3, soe3, eoe2>
Select
Select
set TRANS_TIME(t1);
select
e.name
,e.job
,e.sal
,e.comm
,e.soe as hire_date
,e.sov as from_date
,e.eoe as terminate_date
from EMP_X e
where e.id = 123;
<no rows>
Insulate from
Insulate from
Complexity
Complexity
The approach described below could not make time series reporting any easier. Specify the required point in time and execute the sql query. Choose another point in time and execute the same sql query. Choose another point in time and execute the same sql query. Choose another point in time and execute the same sql query. I think my granny would enjoy time series reporting!
Elevate to
Elevate to
Current View
Current View
When reporting is driven by setting the time coordinates of a time machine and where only the relevant data for that point in time is presented for the report output, one may genuinely feel elevated to a new level of reporting.
Page updated
Google Sites
Report abuse