SapBusinessWarehouse.Cubes

Updated on

SapBusinessWarehouse.Cubes is a Power Query M function that returns a table of InfoCubes and queries from an SAP Business Warehouse instance. The function returns a table of InfoCubes and queries grouped by InfoArea.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

SapBusinessWarehouse.Cubes(
   server as text,
   systemNumberOrSystemId as text,
   clientId as text,
   optional optionsOrLogonGroup as any,
   optional options as nullable record,
) as table
ArgumentAttributeDescription
serverA text string specifying the server.
systemNumberOrSystemIdThe system number or system Id provided as text.
clientIdA text string representing the Client ID.
optionsOrLogonGroupoptionalYou can specify an
optionsoptional

Description

The SapBusinessWarehouse.Cubes function connects to an SAP Business Warehouse, retrieving a table of InfoCubes and queries categorized by InfoArea. It requires the server address, system number or system ID, and Client ID for access.

Users can specify optional parameters to control execution modes and other settings. The function supports different execution modes for MDX query execution, each suited for varying query needs. Additional options like EnableStructures and BatchSize allow further customization of query execution and data retrieval, enhancing functionality and performance.

An optional record parameter, optionsOrLogonGroup, may be specified to control options.

  • ExecutionModThis specifies the MDX interface used to execute queries on the server. You can specify any of the available SapBusinessWarehouseExecutionMode.Type:
  • 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.
  • 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 dataset. A large number of rows may improve performance but could cause memory issues on the SAP BW server. The default value is 50000 rows.

This is a brief list, but for more explanation please read the implementation details.

Examples

An example of how to use the SapBusinessWarehouse.Cubes function is:

SapBusinessWarehouse.Cubes (
    "sapbwtestserver", 
    "00", 
    "837", 
    [ ExecutionMode = SapBusinessWarehouseExecutionMode.BasXml, 
      Implementation = "2.0" ]
  )
BI Gorilla Youtube Channel

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/sapbusinesswarehouse-cubes