DirectQuery 和 SAP Business Warehouse (BW)DirectQuery and SAP Business Warehouse (BW)

可以使用 DirectQuery 直接连接到 SAP Business Warehouse (BW) 数据源。You can connect to SAP Business Warehouse (BW) data sources directly using DirectQuery. 鉴于 SAP BW 的 OLAP/多维特性,SAP BW 的 DirectQuery 与 SQL Server 等关系源之间存在许多重要区别。Given the OLAP/multidimensional nature of SAP BW, there are many important differences between DirectQuery over SAP BW versus relational sources like SQL Server. 这些区别总结如下:These differences are summarized as follows:

  • 在关系源的 DirectQuery 中,有一组查询(在“获取数据”或“查询编辑器”对话框中定义)从逻辑上定义字段列表中的可用数据。In DirectQuery over relational sources there are a set of queries (as defined in the Get Data or Query Editor dialog) that logically define the data that is available in the field list. 连接到 OLAP 源(如 SAP BW)时不是这样的情况。This is not the case when connecting to an OLAP source such as SAP BW. 当使用“获取数据”连接到 SAP 服务器时,只选择 Infocube 或 BEx 查询。Instead, when connecting to the SAP server using Get Data, just the Infocube or BEx Query is selected. 所选 Infocube/BEx 查询的所有键数字和维度都将在字段列表中可用。Then all the Key Figures and dimensions of the selected Infocube/BEx Query will be available in the field list.
  • 同样,当连接到 SAP BW 时,没有“查询编辑器”。Similarly, there is no Query Editor when connecting to SAP BW. 可通过选择“编辑查询>数据源设置”更改数据源设置(例如,服务器名称)。The data source settings (for example, server name) can be changed by selecting Edit Queries > Data source settings. 可通过选择“编辑查询>编辑变量”更改任何变量的设置。The settings for any Variables can be changed by selecting Edit Queries > Edit Variables.
  • 鉴于 OLAP 源的唯一特性,除为 DirectQuery 规定的正常限制以外,还应用其他限制(用于建模和可视化)。Given the unique nature of OLAP sources, there are additional restrictions (for both modelling and visualizations) that apply, in addition to the normal restrictions imposed for DirectQuery. 本文后面部分将介绍这些限制。These restrictions are described later in this article.

此外,非常重要的是要了解 SAP BW 的许多功能在 Power BI 中不受支持,并且由于 SAP BW 的公共接口的性质,在某些重要情况下,通过 Power BI 看到的结果与使用 SAP 工具看到的结果不匹配。In addition, it is extremely important to understand that there are many features of SAP BW that are not supported in Power BI, and that because of the nature of the public interface to SAP BW, there are important cases where the results seen through Power BI will not match those seen when using an SAP tool. 本文后面部分将介绍这些限制。These limitations are described later in this article. 应仔细查看这些限制和行为差异,确保正确解释通过 Power BI 看到的结果(由 SAP 公共接口返回)。These limitations and behavior differences should be carefully reviewed, to ensure that the results seen through Power BI, as returned by the SAP public interface, are interpreted correctly.

其他建模限制Additional Modelling Restrictions

在 Power BI 中使用 DirectQuery 连接到 SAP BW 时的主要其他建模限制如下所示:The primary additional modelling restrictions when connecting to SAP BW using DirectQuery in Power BI are the following:

  • 不支持计算列:创建计算列的功能处于禁用状态。No support for calculated columns: The ability to create calculated columns is disabled. 这也意味着创建计算列的“分组”和“聚类分析”功能不可用。This also means that Grouping and Clustering, which create calculated columns, are not available.
  • 针对度量值的其他限制:对可在度量值中使用的 DAX 表达式规定有其他限制,以反映 SAP BW 提供的支持级别。Additional limitations for measures: There are additional limitations imposed on the DAX expressions that can be used in measures, to reflect the level of support offered by SAP BW.
  • 不支持定义关系:关系是外部 SAP 源中固有的,并且无法在模型中定义其他关系。No support for defining relationships: The relationships are inherent in the external SAP source, and additional relationships cannot be defined in the model.
  • 没有数据视图:数据视图通常显示表中的详细信息级别数据。No Data View: The Data View normally displays the detail level data in the tables. 鉴于 OLAP 源(如 SAP BW)的性质,此视图不适用于 SAP BW。Given the nature of OLAP sources like SAP BW, this view is not available over SAP BW.
  • 列和度量值详细信息固定:字段列表中所示的列和度量值列表由基础源固定,不能修改。Column and measure details are fixed: The list of columns and measures seen in the field list are fixed by the underlying source, and cannot be modified. 例如,不能删除列,也不能更改其数据类型(但是,可以将其重命名)。For example, it is not possible to delete a column, nor change its datatype (it can, however, be renamed).
  • DAX 中的其他限制:DAX 中存在可在度量值定义中使用的其他限制,以反映源中的限制。Additional limitations in DAX: There are additional limitations on the DAX that can be used in measure definitions, to reflect limitations in the source. 例如,不能在表中使用聚合函数。For example, it is not possible to use an aggregate function over a table.

其他可视化效果限制Additional Visualization Restrictions

在 Power BI 中使用 DirectQuery 连接到 SAP BW 时,可视化效果中的其他主要限制如下所示:The primary additional restrictions in visualizations when connecting to SAP BW using DirectQuery in Power BI are the following:

  • 没有列聚合:不能更改视觉对象上的列的聚合;它始终为不汇总No aggregation of columns: It is not possible to change the aggregation for a column on a visual; ;it is always Do Not Summarize
  • 禁用度量值筛选:禁用度量值筛选以反映 SAP BW 提供的支持。Measure filtering is disabled: Measure filtering is disabled to reflect the support offered by SAP BW.
  • 多重选择和包括/排除:如果点表示来自多个列的值,则禁用在视觉对象上多重选择数据点的功能。Multi-select and include/exclude: The ability to multi-select data points on a visual is disabled if the points represent values from more than one column. 例如,给定的条形图显示按国家/地区划分的销售额,图例中含有类别,则将不能选择表示(美国,自行车)和(法国,服装)的点。For example, given a bar chart showing Sales by Country, with Category on the Legend, it would not be possible to select the point for (USA, Bikes) and (France, Clothes). 同样,不能选择表示(美国,自行车)的点并将其从视觉对象中排除。Similarly, it would not be possible to select the point for (USA, Bikes) and exclude it from the visual. 这两个限制都是为了反映 SAP BW 提供的支持。Both limitations are imposed to reflect the support offered by SAP BW.

对 SAP BW 功能的支持Support for SAP BW features

下表列出了使用 Power BI 时不完全支持,或行为方式不同的所有 SAP BW 功能。The following table lists all SAP BW features that are not fully supported, or will behave differently when using Power BI.

功能Feature 说明Description
本地计算Local calculations BEX 查询中定义的本地计算将更改通过 Bex 分析器等工具显示的数字。Local calculations defined in a BEX Query will change the numbers as displayed through tools like Bex Analyzer. 但是,它们不会反映在通过公共 MDX 接口从 SAP 返回的数字中。However, they are not reflected in the numbers returned from SAP, through the public MDX interface.

在这种情况下,Power BI 视觉对象中显示的数字不一定与 SAP 工具中相应的视觉对象的数字匹配。As such, the numbers seen in a Power BI visual will not necessarily match those for a corresponding visual in an SAP tool.

例如,当从将聚合设置为累积(即运行总和)的 BEx 查询连接到查询多维数据集时,Power BI 将返回基数,忽略该设置。For example, when connecting to a query cube from a BEx query that sets the aggregation to be Cumulated (i.e. running sum), Power BI would get back the base numbers, ignoring that setting. 然后,分析师当然可以在 Power BI 中本地应用运行总和计算,但如果不执行此操作,则需要在解释这些数字时保持谨慎。An analyst could certainly then apply a running sum calculation locally in Power BI, but would need to exercise caution in how the numbers are interpreted if this is not done.
聚合Aggregations 在某些情况下(尤其是在处理多种货币时),SAP 公共接口返回的聚合数字与 SAP 工具显示的数字不匹配。In some cases (particularly when dealing with multiple currencies), the aggregate numbers returned by the SAP public interface do not match those shown by SAP tools.

在这种情况下,Power BI 视觉对象中显示的数字不一定与 SAP 工具中相应的视觉对象的数字匹配。As such, the numbers seen in a Power BI visual will not necessarily match those for a corresponding visual in an SAP tool.

例如,不同货币的总计将在 Bex 分析器中显示为 "*",但总计将由 SAP 公共接口返回,而没有表示此类聚合数字无意义的任何信息。For example, totals over different currencies would show as "*" in Bex Analyzer, but the total would get returned by the SAP public interface, without any information that such an aggregate number is meaningless. 因此 Power BI 将显示该数字(聚合,例如 $、EUR 和 AUD)。Thus the number (aggregating, say, $, EUR, and AUD) would get displayed by Power BI.
货币格式Currency formatting Power BI 中不会反映任何货币格式(例如,$2,300 或 4000 AUD)。Any currency formatting (for example, $2,300 or 4000 AUD) is not reflected in Power BI.
度量单位Units of measure Power BI 中不会反映度量单位(例如,230 KG)。Units of measure (for example, 230 KG) are not reflected in Power BI.
键与文本(短、中、长)Key versus text (short, medium, long) 对于如 CostCenter 等 SAP BW 特性,字段列表将显示单个列“成本中心”。For an SAP BW characteristic like CostCenter, the field list will show a single column Cost Center. 使用此列将显示默认文本。Using that column will display the default text. 通过显示隐藏的字段,还可以看到唯一的名称列(返回由 SP BW 分配的唯一名称,并且是唯一性的基础)。By showing hidden fields, it will also be possible to see the unique name column (that returns the unique name assigned by SP BW, and is the basis of uniqueness).

键和其他文本字段不可用。The key and other text fields are not available.
特性的多个层次结构Multiple hierarchies of a characteristic 在 SAP 中,某一特性可以具有多个层次结构。In SAP, a characteristic can have multiple hierarchies. 在 BEx 分析器等工具中,当特性包含在查询中时,用户可以选择要使用的层次结构。Then in tools like BEx Analyzer, when a characteristic is included in a query, the user can select the hierarchy to use.

在 Power BI 中,可以在字段列表中将各种层次结构视为同一维度上的不同层次结构。In Power BI, the various hierarchies can be seen in the field list as different hierarchies on the same dimension. 但是,从位于同一维度的两个不同层次结构中选择多个级别将导致 SAP 返回空数据。However, selecting multiple levels from two different hierarchies on the same dimension will result in empty data being returned by SAP.
不规则层次结构的处理Treatment of ragged hierarchies
缩放系数/反转符号Scaling factor/reverse sign 在 SAP 中,键数字可以具有定义为格式选项的缩放系数(如 1000),这意味着所有显示都会按照该系数进行缩放。In SAP a key figure can have a scaling factor (for example, 1000) defined as a formatting option, meaning that all display will be scaled by that factor.

同样,它可以具有反转符号的属性设置。It can similarly have a property set that reverses the sign. 在 Power BI 中使用此类键数字(在视觉对象中或作为计算的一部分)将导致使用未缩放的数字(并且不会反转符号)。Use of such a key figure in Power BI (in a visual, or as part of a calculation) will result in the unscaled number being used (and the sign is not reversed). 基础缩放系数不可用。The underlying scaling factor is not available. 在 Power BI 视觉对象中,轴(K、M、B)上显示的缩放单位可作为视觉对象格式的一部分进行控制。In Power BI visuals, the scale units shown on the axis (K,M,B) can be controlled as part of the visual formatting.
级别动态显示/消失的层次结构Hierarchies where levels appear/disappear dynamically 最初连接到 SAP BW 时,将检索关于层次结构级别的信息,从而导致字段列表中出现一组字段。Initially when connecting to SAP BW, the information on the levels of a hierarchy will be retrieved, resulting in a set of fields in the field list. 将缓存这些字段,如果级别设置发生更改,那么在调用“刷新”之前,这组字段的设置不会更改。This is cached, and if the set of levels changes, then the set of fields do not change until Refresh is invoked.

这仅在 Power BI Desktop 中可行。This is only possible in Power BI Desktop. 发布后,无法在 Power BI 服务中调用反映级别更改的此类“刷新”。Such Refresh to reflect changes to the levels cannot be invoked in the Power BI service after Publish.
默认筛选器Default filter BEX 查询可以包含默认筛选器,SAP Bex 分析器将自动应用该筛选器。A BEX query can include Default Filters, which will be applied automatically by SAP Bex Analyzer. 这些不会公开,因此 Power BI 中的等效用法默认不会应用相同的筛选器。These are not exposed, and hence the equivalent usage in Power BI will not apply the same filters by default.
隐藏的键数字Hidden Key figures BEX 查询可以控制键数字的可见性,隐藏的数字不会显示在 SAP BEx 分析器中。A BEX query can control visibility of Key Figures, and those that are hidden will not appear in SAP BEx Analyzer. 这不会通过公共 API 反映,因此此类隐藏的键数字仍将显示在字段列表中。This is not reflected through the public API, and hence such hidden key figures will still appear in the field list. 但是,它们可以隐藏在 Power BI 中。However, they can then be hidden within Power BI.
数字格式Numeric formatting 任何数字格式(小数位数、小数点等)都不会在 Power BI 中自动反映。Any numeric formatting (number of decimal positions, decimal point etc.) will not automatically be reflected in Power BI. 但是,可以在 Power BI 中控制此类格式。However, it is possible to then control such formatting within Power BI.
层次结构版本控制Hierarchy versioning SAP BW 允许维护层次结构的不同版本,例如 2007 年与 2008 年的成本中心层次结构。SAP BW allows different versions of a hierarchy to be maintained, for example, the cost center hierarchy in 2007 versus 2008. 由于公共 API 未公开关于版本的信息,所以 Power BI 中仅最新版本可用。Only the latest version will be available in Power BI, as information on versions is not exposed by the public API.
时间相关层次结构Time dependent hirarchies 使用 Power BI 时,会在当前日期计算时间相关层次结构。When using Power BI, time dependent hierarchies are evaluated at the current date.
币种转换Currency conversion SAP BW 基于多维数据集中保留的汇率支持币种转换。SAP BW supports currency conversion, based on rates held in the cube. 公共 API 未公开此类功能,因此 Power BI 中此类功能均不可用。Such capabilities are not exposed by the public API, and are therefore not available in Power BI.
排序顺序Sort Order 可以在 SAP 中定义特性的排序顺序(按文本或按键)。The sort order (by Text, or by Key) for a characteristic can be defined in SAP. Power BI 中不会反映此排序顺序。This sort order is not reflected in Power BI. 例如,月份可能显示为 “April”、“Aug” 等。For example, months might appear as “April”, “Aug”, and so on.

在 Power BI 中无法更改此排序顺序。It is not possible to change this sort order in Power BI.
技术名称Technical names 在“获取数据”中,可以同时看到特性/度量值名称(说明)和技术名称。In Get Data, the characteristic/measure names (descriptions) and technical names can both be seen. 字段列表将仅包含特性/度量值名称(说明)。The field list will contain just the characteristic/measure names (descriptions).
属性Attributes 无法访问 Power BI 中特性的属性。It is not possible to access the attributes of a characteristic within Power BI.
最终用户语言设置End user language setting 用于连接到 SAP BW 的区域设置设置为连接详细信息的一部分,并且不会反映最终报表使用者的区域设置。The locale used to connect to SAP BW is set as part of the connection details, and does not reflect the locale of the final report consumer.
文本变量Text Variables SAP BW 允许字段名称包含变量(例如“$YEAR$ Actuals”)的占位符,占位符会随后替换为所选值。SAP BW allows field names to contain placeholders for variables (for example, "$YEAR$ Actuals") that would then get replaced by the selected value. 例如,如果为该变量选择 2016 年,则在 BEX 工具中该字段显示为“2016 Actuals”。For example, the field appears as "2016 Actuals" in BEX tools, if the year 2016 were selected for the variable.

Power BI 中的列名称不会随变量值而改变,因此将显示为“$YEAR$ Actuals”。The column name in Power BI will not be changed depending on the variable value, and therefore would appear as "$YEAR$ Actuals". 但是,以后可以在 Power BI 中更改列名称。However, the column name can then be changed in Power BI.

限制和注意事项Limitations and considerations

下表列出了 SAP BW 连接器的 Beta 版的限制。The following table lists limitations of the beta release of the SAP BW connector.

限制Limitation 说明Description
无刷新No Refresh “刷新”按钮处于禁用状态,无法刷新视觉对象/元数据。The Refresh button is disabled, and visuals/metadata cannot be refreshed.

后续步骤Next steps

有关 DirectQuery 的详细信息,请查看以下资源:For more information about DirectQuery, check out the following resources: