Replace Multi Source Universe (MSU) with Webi Data Mode

Multi Source Universe

Introduction

A universe is a semantic layer between Webi documents and the underlying data source.
Very much comparable with an SQL generator, when the underlying data source is a relational database or data warehouse. Depending on the objects (Dimensons, Measures, Attributes) selected in Webi query panel, the corresponding SQL statement is generated. In this way the data source executes the SQL and sends the data back to the Webi data provider.
Until Business Objects XI3.1, a universe could connect to only one single data source. It was called a Single Source Universe (SSU)
The Multi Source Universe (MSU) was introduced with the release of Business Objects BI4.0 back in 2012. The MSU allowed building a semantic layer (data foundation) connecting to several data sources.

The picture below shows a data foundation (semantic layer) built on an Oracle and SQL Server database.

The MSU contains two relational connections. One to Oracle and another to SQL Server. The green tables are Oracle Tables, while the blue table is a SQL Server table.
Oracle table is joined with an Inner join to the SQL Server table:

trim(@catalog(‘JDEORACLE’).”PRODDTA”.”F4101″.”IMLITM”)=trim(@catalog(‘SQL_DW_REL_OBJMOO_DWH’).”OBJMOO_DWH.dbo”.”BMVIEW_INFO_****** “.”SALT_ITEM”)

As of the new release of Business Objects BI2025, MSU will be replaced by using the advanced modeling capabilities in SAP Datasphere (Data warehouse Cloud) and Web intelligence Data Mode (since SAP BI4.3 SP3)
As of the new release of Business Objects BI2025, MSU will no longer be supported.
If there are MSU in use, we need to start thinking how we can replace them.

In this blog we will investigate the “new” modeling possibility in Webi Data Mode.

Webi document with MSU

The join in the MSU universe:

trim(@catalog(‘JDEORACLE’).”PRODDTA”.”F4101″.”IMLITM”)=trim(@catalog(‘SQL_DW_REL_OBJMOO_DWH’).”OBJMOO_DWH.dbo”.”BMVIEW_INFO_KAASBAKLOTEN”.”SALT_ITEM”)

Is translated in the SQL as:

“SQL_DW_REL_MOO_WINCC_M00DB01”.”OBJMOO_DWH.dbo”.”BMVIEW_INFO_KAASBAKLOTEN” = Table__1

and

“JDEORACLE”.”PRODDTA”.”F4101″ = Table__2

-> trim(Table__2.”IMLITM”)=trim(Table__1.”SALT_ITEM”)

Splitting the MSU in multiple SSU

A new universe UNX (Business Layer + Data Foundation + Connection) is created containing only the Oracle tables:

A new universe UNX (Business Layer + Data Foundation + Connection) is created containing only the SQL Server tables:

Adapting the data providers in Webi

In a new Webi document, two separate queries are added. One on the new SSU SQL Server UNX, and a second one on the new SSU Oracle UNX

The SQL SRV query contains only objects from the SQL SRV business Layer:

The Oracle query contains only objects from the Oracle business Layer:

Webi Data Mode

To switch Webi to “Data” mode, select “Data” in the drop-down menu in the Webi toolbar:

 

This mode contains two new components:

  • The “Dataset View” where the dataset can be seen as a table or through facets.
  • The “Graph Panel” that displays a graph of the document’s data sources, queries and cubes.

In the Graph Panel View:

The available data sources are displayed: Universes with their corresponding data providers, the number of records and the refresh times

In Dataset View, the data providers can be seen as a table or through different facets.

  • SQL Server:

  • Oracle:

When you select a cube in Webi Data Mode > Show Document dictionary (on the right) or click in the Graph Panel (at the bottom), a tab opens in the Dataset View to display the selected cube’s dataset.

Clicking another cube opens a new tab if it is not yet open (or changes the focus). Click the Close icon
on the tab to close this tab.

You may maximize the Dataset View display and hide the Graph Panel by clicking the Maximize button ( ).

In a tab, the dataset is displayed as a table or through facets, depending on the Facets view toggle button ( ).
In the pictures below, the data providers are displayed as facets:

Synchronizing Cubes

(FYI: https://www.youtube.com/watch?v=uobt9EJqyjcCombine)

You may need to synchronize cubes/datasets in these two cases:

  • Left join: To extend a dataset with columns from another dataset. For example, a dataset containing only sales facts might be extended with details of the buyers.
  • Append: To append the rows of a dataset after the rows of another one. For example, if a query retrieves a dataset for a given week whereas another query retrieves a dataset for another week. Appending these two datasets generates a single dataset for the two weeks.

The Data mode allows you to perform these two use cases through the Combine Cubes features. In the two cases, you need to define a primary and a secondary cube and a set of objects to map between the two cubes.

To edit data columns:

  • Upper/Lower case
  • Replacing characters
  • Trimming leading and trailing blanks
  • Grouping

Trimming column “IMLITM” on both leading and trailing Blanks:

 

Trimming column “SALT ITEM” on both leading and trailing Blanks:

 

To combine cubes:

  1. Select two nodes in the graph and click Combine in the toolbar.
  2. In the Create cube dialog box, add a Name and if needed, a Description for the new cube.
  3. Select the operator to apply: Left join or Append.

Remark:

Left Join:

Append:

  1. Click the Add keys button to open a dialog box where you can select the objects used as keys for the operation. By default, objects with the same name are used as keys.
  2. Click OK.
  1. New data provider has been added in Data Mode:

-> Only merged dimensions visible in Design Mode:

Combined data in Webi Report:


What to do if the Combine, Edit and Delete buttons are hidden in Webi Data mode

 (SAP case number 6210670/2023)

https://launchpad.support.sap.com/#/notes/3270462

To explicitly enable the Combine feature in Web Intelligence through SAP BI launch pad:

  1. Stop Tomcat
  2. Go to the folder:
    1. Windows : E:\Program Files (x86)\SAP BusinessObjects\tomcat\webapps\BOE\WEB-INF\eclipse\plugins\webpath.AnalyticalReporting_NG\web\webiNG\wise-app
    2. Unix: <InstallDir>/sap_bobj/tomcat/webapps/BOE/WEB-INF/eclipse/plugins/webpath.AnalyticalReporting_NG/web/webiNG/wise-app/
  3. Edit the config.json file and add “activateCombineCube”:true to the list of parameters
  4. Go to the folder “SAP BusinessObjects\tomcat\”
    1. Windows : “<InstallDir>\tomcat\work\Catalina\localhost”
    2. Unix: <InstallDir>/sap_bobj/tomcat/work/Catalina/localhost
  5. Delete the folder “BOE”
  6. Restart Tomcat and refresh the web browser

To explicitly enable the Combine feature in Web Intelligence Rich Client

  1. Quit Web Intelligence Rich Client
  2. Go to the folder ” SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win64_x64\wise\wise-app
  3. Edit the config.json file and add the line “activateCombineCube”:true to the list of parameters
  4. Start Web Intelligence

When you log in to Web Intelligence, in the Data mode, the CombineEdit and Delete buttons are displayed in the Graph toolbar, allowing you to create and manage combined cubes.

config.json:

Jan De Vilder

SAP consultant @ Cubis