Open Hub Destination with BEx Query as an InfoProvider
A SAP BW system is mostly known to consolidate data to give reporting or analytics capabilities to your business. However, there could be occasions where you want to transfer the data. One of the options to do this is to make use of Open Hub Destination. This was in the past a part of the InfoSpoke, but separated since SAP BW 7.X. The Open Hub Destination allows you to distribute data in database tables or flat files in a fully integrated and controlled SAP BW data flow:
The case that we will describe in this article will be an Open Hub Destination based on a BEx Query. The BEx query will act as a data source. The reason to do this is that the BEx query itself contains calculated key figures that are not stored physically in the system. They are calculated on the fly when the BEx query will be launched in a report.
With this setup, we do not need to create additional infoProviders to store the data and we can use the available features integrated in SAP BW to create a flat file for data transfer.
Step by step
- Create a SAP BEx Query that you want to use as data source. The technical name is limited to 20 characters. For other limitations, go to the Limitations-paragraph.
- Via transaction code RSRT, search for your created SAP BEx Query. Click on Properties and tick the checkbox for Query is used as InfoProvider:
- For the ease of maintenance, we create a Logical File Name for where the file that we will generate with the Open Hub Destination will be written. Via transaction code File, create a new entry of a logical file path (note: this is cross-client):
- Enter a name for your Logical File Path and press Select your newly created entry and double click on Assignment of Physical Paths to Logical Path:
- Create a new entry for your Logical File Path, choose as syntax group UNIX and define the Physical path where you want the file to be pointed to (note: do not enter a file name, but define <FILENAME> in your path as placeholder for the actual file name that we will create in the next steps):
- Create a new Logical File Name by selecting it and create a new entry. Fill in Physical file the name as name for your file and for the Logical Path, use the one you created in step 7:
- Now we will create the Open Hub Destination. Via transaction code RSA18, choose an infoArea where you want to create a new Open Hub Destination:
- With this step, choose as ObjectType Query and find back your created BEx Query. Leave the checkbox Create Transformation and DTP ticked as per default to let the system generate them for you:
- Choose destination type File to create a flat file. For the type of file name, choose Logical file name. Search for the Logical file name that you have created in step 6:
- In the tab Field Definition, you can change the setting for the key figures to External to let the format of it to correspond to that of the external system, e.g. minus-sign in front of the figure instead of behind:
- When you activate the Open Hub Destination, transformation and DTP will be generated and it will be ready to use. Of course, you can adapt the transformation and DTP to your own requirements.
The possibility to use a BEx query as infoProvider opens up possibilities; however, there are some limitations in case you want to use it:
The following functions are supported:
- Standard aggregations (SUM, MIN, MAX)
- Formulas that are totalled before aggregation (+, -, constant factor)
- Unit and currency translations
- Constant selection
- Formulas with quotients, like prices
- Formulas with exception aggregation
In the following cases, it is not possible to activate a query as an InfoProvider:
- The query is input-ready (for BW-integrated planning).
- The query has input-ready required variables.
- The query contains non-cumulative key figures.
- Temporal hierarchy join is enabled.
- The query has more than one structure.
- The technical name of the query is more than 20 characters in length.
- The InfoProvider is a TransientProvider or an aggregation level.
The following settings are not taken over from the query:
- Text fields (only the key fields are transferred)
- Set hierarchies
- Local aggregations
- Elimination of internal business volume