Imagine this scenario: you have customer delivery data in your SAP BW system and quality control data is contained in nine separate location specific Oracle databases. You need to pull the quality data associated with products that have been delivered to a customer over the last several months and will want to continue ongoing monitoring.
This would normally turn into a huge manual effort on the part of a business analyst who would pull the data from both systems and merge it together for the historical data along with a ongoing manual effort. Meanwhile, IT would put together a project to extract the data from one system into the other, or both into a separate system as an automated procedure. This type of project normally takes weeks, if not months, to complete once the funding has been secured and the project has been added to the release schedule. All this considered it could easily take six months to a year to complete. I just built an automated solution for this problem in two days using SAP BusinessObjects BI4 and the Information Design Tool Common Semantic Layer.
The best part is we now have a semantic layer that will let the business users build future reports as needs arise.
I created a new data foundation using a relational connection to the BW cube allowing it to automatically create tables and joins. The result looks messy, but if you allow the business layer to automatically create classes and objects it does a pretty good job of giving you something you can start from (much better than what the legacy Universe Design Tool builds from a Bex Query).
The quality data was already in an existing universe, which used nine Oracle connections to the different databases with the same table structures. To make reporting across systems possible I used derived tables to union the nine systems together. All I had to do was copy and paste the derived tables in the data foundation and then copy the classes and objects out of the existing business layer to my new universe. Lastly, I made a few minor alterations to the business layer and did some clean up to get a new universe that joined with the cube.
Once all the universe work was done the report development was pretty straight forward, pulling data from both database sources without anyone being the wiser. Surprisingly, performance is also pretty good. Crossing all the Oracle connections (against live, operational databases) and the SAP BW source, the report refreshes in about two minutes pulling a month’s worth of data on demand.