OBIEE Case study --- How to ensure reporting the right data that is time sensitive part 3

The second approach that I talked about earlier was about adding the history records into some of the fact tables that are updating at daily level. That means the below model will have to change




To this:

From OBIEE side, we remove the join between Date and LSP PWE Config fact, and snowflake the model in BMM Layer. From the reporting side, we will have 2 filters:

1. Date = date prompt value and hour = hour prompt value (These values will come from presentation variables) or Date is between date prompt values

2. LSP PWE Config start date < = date prompt value and end date > = date prompt value. This ensures only the 'current' record from this table will be returned at any chosen date in the past or present.

One thing to keep in mind is that, when it comes to reporting the historical trend or plotting the performance overtime, the second filter will have to change, here is why:

When user are selecting a range of days, such as date is between Nov 1st to Nov 20th and let's say we do have data in the Pseudowire fact table from these days, we are going to display the results correctly. Lets say all of the records in LSP PWE Config fact and other dimension tables also start on Nov 1st and no end date,  the report is working fine. However, when user select from Oct 31st to Nov 20th, they would naturally expect the same result even if nothing occurs on Oct 31th. However, the report will go blank because the second filter won't work here. On Oct 31th, there is no record is nothing was ''Current" at that time.

In order to fix it, we will then have to change the second filter to go between the day range. Which means:

2. LSP PWE Config start date >= date prompt value and end date <= date prompt value. This ensures only the 'current' record from this table will be returned at any chosen date in the past or present.

Thanks

Until next time