How to create a SAP BO Explorer Information space using a SAP BW Bex Query as a datasource
Applies to: SAP BW 7.3 SP8, SAP BusinessObjects BI Platform 4.1 SP2, SAP BusinessObjects Explorer 4.1 SP2
SAP BusinessObjects Explorer is a very catchy BI tool, allowing users to access data with a BI self-service approach. Further, BO Explorer is available within the standard SAP BI 4.1 license, whereas other SAP Analyses tools – such as Visual Intelligence/Lumira – require further licensing. SAP BO Explorer however does not support OLAP connections, which makes it a challenge to integrate into an existing SAP BW landscape. SAP suggests the following connectivity options (check this document page 17):
- SAP HANA via JDBC
This requires HANA which is for many companies is not in place
- SAP BW Bex Query via an XI 3.1 OLAP Universe (.unv)
This method is available as of BO Explorer 4.1. However XI3.1 unv universes must be used with the known disadvantages compared to BO4 unx.
- SAP Infoprovider via a BI 4.x Relational Universe (unx)
This does not allow to directly consume a Bex Query. However the use of Bex Queries as a datasource is required as for most SAP BW scenarios, as the Bex Query contains the business logic behind the data.
This document suggests combining the advantages of the second and third method as follows:
- Load a standard DSO in BW 7.3 with a Bex query as a datasource
- Create a .unx on top of the DSO
- Create the Business Explorer information space on top of the .unx
Here is how
Create Infoobjects, DSO and Bex query in SAP Netweaver BW 7.3
1. Prepare the Bex Query with all required characteristics in the Rows and all required key figures in the columns. Only use variables filled by replacement path or user exit. Don’t use variables that require user interaction.
2. Create one BW Keyfigure Infoobject foreach keyfigure in the query
3. Create a standard DSO in BW. Add all the keyfigure infoobjects created in the previous step into the data fields of the DSO. For the key fields, a maximum of 16 infoobjects are allowed. If your query has more than 16 characteristics, you can add them to the DSO data fields as well. As the datasource will be a Bex query, all characteristic combinations will be unique anyway.
4. Now load the DSO with the Bex query as an infoprovider as described in this article from Martin Grob. The described procedure won’t be repeated here.
Create Relational Connection in SAP BO IDT 4.1
5. In the SAP BO Information Design Tool, create a Relational Connection.
Choose SAP NetWeaver BW as a datasource
Maintain the connection parameters and specify the DSO you created in step 3 as an Infoprovider.
Create Data Foundation and Business Layer in SAP BO IDT 4.1
6. Go to “File –> New –> Data Foundation” and create a Multisource-Enabled Data Foundation. Make sure to check the option “Automatically create tables and joins”.
7. Go to “File –> New –> Business Layer”. Choose “Relational Data Foundation” as a data source.
Make sure to check the option “Automatically create folders and objects for SAP NetWeaver BW connections”
8. You now see the data model of your DSO in star scheme format. As BO does not always take over the data formats as you are used to in BW, you may have to change the coding of some dimensions and measures. Some simple coding examples:
- Bring BW infoobject 0calquarter from format 20143 to format 2014.Q3:
left(tostring(@catalog(‘CONNECTION TO DSO HE_PL01′).”PUBLIC”.”IHE_PL01″.”0CALQUARTER”),4)+’.Q’+right(tostring(@catalog(‘CONNECTION TO DSO HE_PL01’).”PUBLIC”.”IHE_PL01″.”0CALQUARTER”),1)
- Show for BW infoobject 0customer the customer ID plus the name in the format “23885 : Novartis AG”
ltrim(@catalog(‘CONNECTION TO DSO HE_PL01′).”PUBLIC”.”IHE_PL01″.”0CUSTOMER”,’0′)+’: ‘+”_T_IHE_PL01_0CUSTOMER”.”0TXTMD”
9. In the properties of the business layer you find some settings to limit the query result. Make sure to take them out, SAP activates them by default.
10. Perform an integrity check and publish the BI 4.x Relational Universe to a repository.
Create an information space with SAP BO Explorer
11. Launch SAP BO Explorer from the BO Launchpad. Choose “Manage Spaces”, choose your .unx and press “Information Spaces –> New”
12. You will find the original Bex characteristics infoobject in the “Key Part” and the original Bex Key figures in the “Measures” Part of the universe. Add them to the information space as required, schedule the indexing and save.
13. Now go and impress your users!