Implementation details

This article describes conversion information and specific features available in Implementation 2 of the Power Query SAP Business Warehouse connector.

New options for Implementation 2.0

Implementation 2.0 supports the following options:

• ExecutionMode specifies the MDX interface used to execute queries on the server. The following options are valid:

  • SapBusinessWarehouseExecutionMode.BasXml

  • SapBusinessWarehouseExecutionMode.BasXmlGzip

  • SapBusinessWarehouseExecutionMode.DataStream

    The default value is SapBusinessWarehouseExecutionMode.BasXmlGzip.

    Using SapBusinessWarehouseExecutionMode.BasXmlGzip may improve performance when experiencing high latency for large data sets.

• BatchSize specifies the maximum number of rows to retrieve at a time when executing an MDX statement. A small number translates into more calls to the server while retrieving a large data set. A large number of rows may improve performance, but could cause memory issues on the SAP BW server. The default value is 50000 rows.

• EnableStructures indicates whether characteristic structures are recognized. The default value for this option is false. Affects the list of objects available for selection. Not supported in Native query mode.

The ScaleMeasures option has been deprecated in this implementation. The behavior is now the same as setting ScaleMeasures to false, always showing unscaled values.

Additional improvements for Implementation 2.0

The following list describes some of the additional improvements that come with the new implementation:

• Improved performance.
• Ability to retrieve several million rows of data, and fine-tuning through the batch size parameter.
• Ability to switch execution modes.
• Support for compressed mode. Especially beneficial for high latency connections or large data sets.
• Improved detection of Date variables.
• Expose Date (ABAP type DATS) and Time (ABAP type TIMS) dimensions as dates and times respectively, instead of text values. More information: Support for typed dates in SAP BW
• Better exception handling. Errors that occur in BAPI calls are now surfaced.
• Column folding in BasXml and BasXmlGzip modes. For example, if the generated MDX query retrieves 40 columns but the current selection only needs 10, this request will be passed onto the server to retrieve a smaller data set.

Changing existing reports to use Implementation 2.0

Changing existing reports to use Implementation 2.0 is only possible in import mode. Follow these steps:

1. Open an existing report, select Edit Queries in the ribbon, and then select the SAP Business Warehouse query to update.

2. Right-click the query and select Advanced Editor.

3. In the Advanced Editor, change the SapBusinessWarehouse.Cubes call as follows:

   Determine whether the query already contains an option record, such as the following example.

   

   If so, add the Implementation 2.0 option, and remove the ScaleMeasures option, if present, as shown.

   

   If the query doesn't already include an options record, just add it. For the following option:

   

   Just change it to:

   

Every effort has been made to make Implementation 2.0 of the SAP BW connector compatible with version 1. However, there may be some differences because of the different SAP BW MDX execution modes being used. To resolve any discrepancies, try switching between execution modes.

Support for typed dates in SAP BW

Implementation 2.0 of the SAP BW connector includes support for typed dates and times. If you query a report that has dimensions with ABAP types, DATS, or TIMS, they can now be output as dates instead of text.

The limitations for using this functionality are:

• Only available in Implementation 2.0 of the SAP BW connector.
• Only available in Import mode.
• The account used to connect to the SAP BW server should have enough permissions to call BAPI_IOBJ_GETDETAIL.

let
   Source = SapBusinessWarehouse.Cubes("sapbwtestserver", "00", "837", [ExecutionMode=SapBusinessWarehouseExecutionMode.BasXmlGzip, Implementation="2.0"]),
   #"$INFOCUBE" = Source{[Name="$INFOCUBE"]}[Data],
   #"$0D_DECU" = #"$INFOCUBE"{[Id="$0D_DECU"]}[Data],
   #"Added Items" = Cube.Transform(#"$0D_DECU",
   {
      {Cube.AddAndExpandDimensionColumn, "[0CALDAY]", {"[0CALDAY].[LEVEL01]"}, {"Calendar day.Calendar day Level 01"}},
      {Table.AddColumn, "Calendar day.Calendar day Level 01.Key", each Cube.AttributeMemberProperty([Calendar day.Calendar day Level 01], "[20CALDAY]")},
      {Cube.AddMeasureColumn, "Billed Quantity", "[Measures].[0D_INV_QTY]"}
   }) 
in
     #"Added Items"

You'll need to add the key in to access the typed date. For example, if there's a dimension attribute called [0CALDAY], you'll need to add the key [20CALDAY] to get the typed value.

In the example above, this means that:

• Calendar day.Calendar day Level 01 [0CALDAY] will be text (a caption). (Added by default when the dimension is added.)
• Calendar day.Calendar day Level 01.Key [20CALDAY] will be a date (must be manually selected).

To manually add the key in Import mode, just expand Properties and select the key.

The key column will be of type date, and can be used for filtering. Filtering on this column will fold to the server.

Support for SAP BW features

The following table lists all SAP BW features that aren't fully supported or behave differently when using the Power Query SAP BW connector.

Performance Considerations

The following table provides a summary list of suggestions to improve performance for data load and refresh from SAP BW.

Comparison to Analysis for Office (AFO)

There are fundamental differences between the Analysis for Office (AFO) tool and the Power Query SAP Business Warehouse connector, so the outputs of data might differ. AFO doesn't use MDX, but instead uses a proprietary protocol developed by SAP that isn't available for 3rd parties, such as the Power Query SAP Business Warehouse connector. SAP only certifies connectors that use the MDX interface. AFO uses a multidimensional querying strategy, which navigates the data differently whereas the Power Query SAP Business Warehouse connector needs to flatten the data so it can be represented as a table. So even though it's the same data, it's queried, represented, and ultimately outputted differently.

See also

• SAP Business Warehouse Application Server
• SAP Business Warehouse Message Server
• Import vs. DirectQuery for SAP BW