How to avoid reporting based on a staging table?

Question

Hi,

In order to satisfy a self-serviced reporting requirement, customer based on a suggestion by a consultancy wants to use one of the staging tables in Phyisical Layer (Oracle 11g, OBI EE 11g).

This staging table is populated with in excess of 2 million rows per day, and needs to be remain performant, optimised for writing so has limited indexing.

We need to check previous row (complex logic) as part of the requirement in order to facilitate self-serviced BI.

Our thoughts and responses (which are in double quotes) are as under.

* We already have a processed table in data warehouse. "No. We will implement the logic in the RPD and / or in an Analysis."
* Our data warehouse table is properly indexed in order to read data. "Performance is not an issue."
* In order to consider previous row, we need to use EVALUATE function. There could be security issues. "Why would there be any security issues? This table will be exposed to the users anyway for the self-serviced BI."

We would like to convince to use the data warehouse table. I do not have a proper reason with regards to avoiding EVALUATE function to be used. Are there any real security concerns in using the EVALUATE function? If yes, what could they be when the table will be exposed to the users through a Subject Area?

Thank you.

Regards,

Manoj.

John_K · Answer

Why do you need to use Evaluate to consider the previous row? I presume you mean previous as in chronologically previous? If so then that's what the "Ago" function is for.

Do they wish to use the staging table because it only contains changed rows (i.e. incremental) rather than the full dataset?

Manoj Dixit · Answer

Hello Christian and Thomas,

Thank you for the responses.

While I am not 100% familiar with the staging table and its processing logic, I can give an overall and bit simplified concept here.

We have an 'Element' hierarchy where Element is the lowest level. Then above that, there is Element Group, Element Area and Element System. While an element can move from one system to another or within a system from one group to another, we want to report number of times an element has moved distinctly. So, first time an element moves from a group to another group within the same system, that will be counted as 1. This is true if the element moves from one group to another group that belongs to a different system. Next time, when the element moves within the same system it will not be counted. But when it moves to a different system it will be counted. For this we need to know the previous system. While the data warehouse table need not consider the previous system as the job is done by the ETL, based on the new requirement, OBI EE needs to derive values for the previous system element.

I have a feeling that the user wants to know more granular data like time stamps, ID values, etc. which are available in the staging database table. And in order to calcuate the count (self service) we need to display relevant previous element system values.

Thank you.

Regards,

Manoj.

Thomas Dodds · Answer

what is in the stage table that is missing from the presentation warehouse table?

Use drive physical design, so perhaps a second presentation table generated from the stage is required for that use -- I agree not to open up access to your processing layer.