A client was changing a process key to their business; and changing the applications that they use to support this process. During the change over support, and therefore reporting, could not be interrupted; and moving forward data from both systems needed to be displayed seamlessly.
If you have played with the union of queries in obiee 10g then you will know that one ‘feature’, now known to Oracle as an enhancement request, is that for a union-ed request, any passing of filters from the unioned request to another fails. The target request will disregard all filters.
To get around this we need to either perform this union in the rpd using Fragmentation, or during the load process. If it is possible to resolve the issue during the load process then this will improve performance of the application, but failing that it is possible to fragment a fact or dimension table in the logical layer. For this client, time does not afford changing the load process.
The Physical Layer
In the Physical layer, the table structures and joins should be created for both databases as normal. The diagram below shows an example that I have completed recently. In this example both sources existed in the same physical schema.
The Logical Table
We create a single logical dimension table that is conformed between the two fact tables. And we create a single logical fact table; it will contain two logical sources; we create the logical joins as shown below.
For a logical table, be it fact or dimension, you can add multiple sources. By default obiee will pick one source or the other, based upon what the server perceives is the most efficient query. That is not what we want to achieve here; we have two physical table in the logical fact and rather than use one or the other we want to use both and union the result. For each logical table source, in the Content tab, we can mark the source to be combined with other sources. This is shown in the diagram below and should be marked for each of our table sources.
When we select that a table source should be combined then we must provide the conditions under which it should be included in the combination; even if it should occur in all cases. In this example our switch over occurred on the 2nd January 2010; and we define that the legacy reporting database should be used where the date is less than the switch over date.
You can see below that the other fact table is defined in a similar way; although it is configured to be included only when the date is on or later than the specified date.
To summarise
I have seen this used now for a number of clients and for numerous reasons. The criteria for a table source joining the combination could be based upon anything reportable, but is usually, as expected, based upon time.
In this example both table sources come from the same physical schema; in this case the request will be posted to the database as a SQL union request; and the processing will be performed by the database. If different physical schemas were used then the queries would be run on their respective data sources and the union performed later by the BI Server; this will perform less well and should be avoided if possible.
And it may be that if using multiple sources that dimensions, as well as facts, need to be fragmented in order to achieve conformity; this can be achieved in much the same way.
If you have played with the union of queries in obiee 10g then you will know that one ‘feature’, now known to Oracle as an enhancement request, is that for a union-ed request, any passing of filters from the unioned request to another fails. The target request will disregard all filters.
To get around this we need to either perform this union in the rpd using Fragmentation, or during the load process. If it is possible to resolve the issue during the load process then this will improve performance of the application, but failing that it is possible to fragment a fact or dimension table in the logical layer. For this client, time does not afford changing the load process.
The Physical Layer
In the Physical layer, the table structures and joins should be created for both databases as normal. The diagram below shows an example that I have completed recently. In this example both sources existed in the same physical schema.
Physical Diagram
We create a single logical dimension table that is conformed between the two fact tables. And we create a single logical fact table; it will contain two logical sources; we create the logical joins as shown below.
Logical Diagram
Legacy System Table Source Definition
You can see below that the other fact table is defined in a similar way; although it is configured to be included only when the date is on or later than the specified date.
Table Source for new source system
I have seen this used now for a number of clients and for numerous reasons. The criteria for a table source joining the combination could be based upon anything reportable, but is usually, as expected, based upon time.
In this example both table sources come from the same physical schema; in this case the request will be posted to the database as a SQL union request; and the processing will be performed by the database. If different physical schemas were used then the queries would be run on their respective data sources and the union performed later by the BI Server; this will perform less well and should be avoided if possible.
And it may be that if using multiple sources that dimensions, as well as facts, need to be fragmented in order to achieve conformity; this can be achieved in much the same way.
No comments:
Post a Comment