Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
After you connect to your SAP Business Warehouse (BW) instance, the Navigator dialog shows a list of available catalogs in the selected server.
One catalog folder with the name $INFOCUBE is displayed. This folder contains all InfoProviders in the SAP BW system.
The other catalog folders represent InfoProviders in SAP BW for which at least one query exists.
The Navigator dialog displays a hierarchical tree of data objects from the connected SAP BW system. The following table describes the types of objects.
| Symbol | Description |
|---|---|
|
Your SAP BW server |
|
Catalog—either $INFOCUBE or an InfoProvider |
|
InfoCube or a BEx Query |
|
Key figure |
|
Characteristic |
|
Characteristic level |
|
Property (Attribute) |
|
Hierarchy |
Note
The navigator shows InfoCubes and BEx queries. For BEx queries, you might need to go into Business Explorer, open the desired query, and check Allow External Access to this Query: By OLE DB for OLAP for the query to be available in the navigator.
Note
In Power BI Desktop, objects below an InfoCube or BEx Query node, such as the key figures, characteristics, and properties are only shown in Import connectivity mode, not in DirectQuery mode. In DirectQuery mode, all the available objects are mapped to a Power BI model and are available for use in any visual.
In the navigator, you can select from different display options to view the available query objects in SAP BW:
Only selected items: This option limits the objects shown in the list to just the selected items. By default, all query objects are displayed. This option is useful for a review of the objects that you included in your query. Another approach to viewing selected items is to select the column names in the preview area.
Enable data previews: This value is the default. This option allows you to control whether a preview of the data should be displayed on the right-hand side in the Navigator dialog. Disabling data previews reduces the amount of server interaction and response time. In Power BI Desktop, data preview is only available in Import connectivity mode.
Technical names: SAP BW supports the notion of technical names for query objects, as opposed to the descriptive names that are shown by default. Technical names uniquely identify an object within SAP BW. With the option selected, the technical names appear next to the descriptive name of the object.
Characteristic hierarchies
A characteristic always has at least one characteristic level (Level 01), even when no hierarchy is defined on the characteristic. The Characteristic Level 01 object contains all members for the characteristic as a flat list of values.
Characteristics in SAP BW can have more than one hierarchy defined. For those characteristics, you can only select one hierarchy or the Level 01 object.
For characteristics with hierarchies, the properties selected for that characteristic are included for each selected level of the hierarchy.
Measure properties
When you pick a measure, you can optionally select the units/currency, formatted value, and format string. In the following screenshot, it's useful to get the formatted value for COGS. This helps you follow the same formatting standard across all the reports.
Note
Measure properties aren't available in Power BI Desktop in DirectQuery mode.
Flattening of multi-dimensional data
Based on the selected objects and properties in the navigator, Power Query constructs an MDX statement that's sent for execution to SAP BW. The MDX statement returns a flattened data set that can be further manipulated using the Power Query editor.
Power Query uses a newer interface that's available in SAP BW version 7.01 or higher. The interface reduces memory consumption and the number of cells doesn't restrict the result set.
The flattened data set is aggregated in SAP BW at the level of the selected characteristics and properties.
Even with these improvements, the resulting data set can become very large and time-consuming to process.
Performance recommendation
Only include the characteristics and properties that you ultimately need. Aim for higher levels of aggregation. For example, do you need Material-level details in your report, or is MaterialGroup-level enough? What hierarchy levels are required in Power BI? Try to create smaller data sets, with higher levels of aggregation, or multiple smaller data sets, that can be joined together later.
Query parameters
Queries in SAP BW can have dynamic filters defined that allow you to restrict the data set returned by the query. In the BEx Query Designer, this type of dynamic filter can be defined with what's called a Characteristic Restriction and assigning a Variable to that restriction. Variables on a query can be required or optional, and they're available to the user in the navigator.
When you select an SAP BW query with characteristic restrictions in the Power Query navigator, the variables are displayed as parameters above the data preview area.
Using the Show selector, you can display all parameters that are defined on the query, or just the required ones.
The query shown in the previous image has several optional parameters, including one for Material Group. You can select one or more material groups to only return purchasing information for the selected values, that is, casings, motherboards, and processors. You can also type the values directly into the values field. For variables with multiple entries, comma-separated values are expected, in this example it would look like [0D_MTLGROUP].[201], [0D_MTLGROUP].[202], [0D_MTLGROUP].[208].
The value # means unassigned; in the example any data record without an assigned material group value.
Performance recommendation
Filters based on parameter values get processed in the SAP BW data source, not in Power BI. This type of processing can have performance advantages for larger data sets when loading or refreshing SAP BW data into Power BI. The time it takes to load data from SAP BW into Power BI increases with the size of the data set, for example, the number of columns and rows in the flattened result set. To reduce the number of columns, only select the key figures, characteristics, and properties in the navigator that you eventually want to see.
Similarly, to reduce the number of rows, use the available parameters on the query to narrow the data set, or to split up a larger data set into multiple, smaller data sets that can be joined together in the Power BI Desktop data model.
In many cases, it might also be possible to work with the author of the BEx Query in SAP BW to clone and modify an existing query and optimize it for performance by adding extra characteristic restrictions or removing unnecessary characteristics.
Loading SAP data into Power Query
Once you select the SAP data set you want in the navigator, you can import the data into the Power Query editor. Select Transform Data to launch the Power Query editor, where you can perform other data transformation and filtering steps.
In the previous example, a parameter is used to only bring back records with a Material Group of casings, motherboards, and processors.
In Power Query Desktop, you can also select Load to bring the entire data set from SAP BW into Power BI Desktop. Power BI Desktop takes you to the Report view where you can begin visualizing the data or make further modifications using the Data or Relationships views.