The MSU allows you to link BW data and non-BW data in a universe and accessing it by many of the BusinessObjects tools. In this blog, I look at the benefits and limitations of this interface and provide some guidelines on when it is an appropriate to deploy it.
By Dr. Berg
The MSU is basically a business layer that allows you to link BW and non-BW sources together. It can be used to link data marts and data warehouses with the SAP BW solution and thereby reducing the need for loading all data into a single warehouse. It basically relies on the data federator, but you can even add your own SQL when needed.
Other great features include the ability to create your own calculations, adding your own objects and also the capability to connect to InfoProviders in BW such as DSOs, InfoCubes etc.
Multi-Sourced Universes (MSU) are developed using the Information Designer tool in BusinessObjects BI 4.0. It basically creates a business layer and a data foundation layer that makes the BW sources appear relational and from here you can join other sources such as flat files and other relational databases. In other words, you can start to create a federated data warehouse (FDW).
For the MSU, the underlying BW tables are basically accessed through a relational connection and Data Federator ‘converts’ the InfoCube into a relational structure. This 'new' schema exists only in DF memory and it is queried via SQL. Let us look at an example:
First, we see a BW system that was access
through RSA1 (Admin Workbench). As you can see, this is a basic InfoCube.
Figure 1: The Basic InfoCube
(example provided thanks to S. Knezevic at SAP Labs, Nov. 2011)
Then we go to the Information Designer (assuming connections are already setup). Here we can select the InfoCube and DSOs as needed.
Figure 2: The BW InfoProviders
We can also explore how the BW schema looks in the Information Designer Tool (IDT) . As we can see in figure 3, the dimensions and fact table are now represented as relational tables and the tables are joined for us.
Figure 3: The Information Designer Tool
In the Business Layer view in IDT was can look at the objects in a folder view and we can also add our own calculations and even code our own SQL as needed (figure 4).
Figure 4: The Business Layer
Once the Multi-Sourced Universe has been completed, you simply publish it to the repository. Since the connections are already there, you only need to publish the business layer. Once published, the embedded data foundation, connections and the business layer all become part of the UNX file in the repository.
While MSU is great as a start, there are several limitations to with the MSU. Since we are bypassing the BI Analytical engine in BW, features such as Restrictive Key Figures (RKF), Calculated Key Figures (CKF) are not available. However, as we see in figure 4 above, CKFs can be added in the layers.
More importantly, BW masterdata hierarchies are not available. A work-around is possible if you flatten the hierarchy and store this in a BW dimension (IDT can then read the fields from the dimension as a regular relational table). Display attributes are also not available, but you fix this by using joins from the InfoCube to a masterdata table in a universe (you have to manually set this up in the MSU design).
However, features such as variables, exceptions, conditions, structures, non-cumulative key figures are not available and currency and unit
conversions have to be added. The latter may be done by adding conversion rate tables in the Universe Designer and join to this in the data federator.
Another consideration is performance. If you link to a non-BW data source and merge the result with a BW data source, the merge process will take place once the slowest provider of data has executed. So, make sure you are performance tuning not only the SAP systems, but also non-SAP sources.
Thanks to SAP’s excellent team for assistance with this blog: Amzal Mokrane, Sinisa Knezevic. Henry Banks, Tobias Kaufmann, Elizabeth Imm and also to Ashwani Sharma of Accenture.
MORE IDEAS FOR DATA FEDERATOR
In addition to BICS, LiveOffice, OLAP universes (MDX), MSU etc., you can also use the Data Federator interface in NetWeaver 7.01 service pack 3. So let us end by looking at some non-MSU options:
Accessing BEx Queries as InfoProviders in Data Federator (non MSU)
BEx Queries are also not currently available in an MSU. However, using the data federator interface in NetWeaver 7.0.1 SP-3, even queries can be used as InfoProviders. You simply set the property of the query in RSDB (query monitor)/ activate query as InfoProvider. After this you can take advantage of the BI Analytical engine in BW including standard aggregations (max, min, sum), units and currency translations and formulas with exception aggregation and basic formulas that are summed before aggregation.
There are limitations though. You cannot use the query as an infoprovider if the technical name is longer than 20 characters, or if it was built with any input ready variables. Nor can you use it if the query was based on an aggregation level or transient provider. In addition, it cannot be used if a temporal hierarchy joi
n was enabled or the query has multiple structures. Despite this there are clearly some benefits. Once used, the query will provide features such as local aggregations and also elimination of internal business volume.
Accessing InfoSets in Data Federator (non MSU)
Unfortunately, the InfoSet InfoProviders are all deactivated by default. So, if you want to use them, you have to activate them. This is currently done by adding these two entries: CONFIG_ID = 'SUBTYPES' and CONFIG_VALUE = ‚CUBE;ODSO;IOBJ;MPRO;HYBR;LPOA;VIRT;ELEM; ISET’ to the RSDRI_DF_CONFIG table.