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) 的 DirectQuery 之間有許多重要差異。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 中有一組查詢 (如 [Get Data](取得資料) 或 [查詢編輯器] 對話方塊中所定義),這些查詢會以邏輯方式定義欄位清單中可用的資料。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. 連接到 SAP BW 等 OLAP 來源時則「不同」。This is not the case when connecting to an OLAP source such as SAP BW. 相反地,使用 [Get Data](取得資料) 連接到 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.

此外,「請務必」了解 Power BI 不支援 SAP BW 的許多功能,而且由於 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.


在 Power BI Desktop 的 2018 年 3 月更新之前,在 SAP BW 中使用 DirectQuery 的功能都是預覽版。The ability to use DirectQuery over SAP BW was in preview until the March 2018 update to Power BI Desktop. 在預覽期間,意見反應和建議改善事項會提示對使用預覽版建立之報表有影響的變更。During the preview, feedback and suggested improvements prompted a change that impacts reports that were created using that preview version. 既然已發行 SAP BW DirectQuery 正式發行版本 (GA),您「必須」捨棄任何使用 SAP BW DirectQuery GA 預先版本建立的現有 (預覽式) 報表。Now that General Availability (GA) of DirectQuery over SAP BW has released, you must discard any existing (preview-based) reports using DirectQuery over SAP BW that were created with the pre-GA version. 在使用 SAP BW DirectQuery GA 預先版本建立的報表中,這些 GA 預先版本報表在叫用重新整理時,會因為嘗試重新整理基礎 SAP BW Cube 已變更的中繼資料而發生錯誤。In reports created with the pre-GA version of DirectQuery over SAP BW, errors will occur with those pre-GA reports upon invoking Refresh, as a result of attempting to refresh the metadata with any changes to the underlying SAP BW cube. 請使用 GA 版本的 SAP BW DirectQuery,以空白報表重新建立這些報表。Please re-create those reports from a blank report, using the GA version of DirectQuery over SAP BW.

其他模型限制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. 根據 SAP BW 等 OLAP 來源的本質,無法透過 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. 例如,假設有依國家/地區顯示銷售量的橫條圖並在圖例中含有類別,您無法選取 (USA, Bikes) 和 (France, Clothes) 點。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). 同樣地,您無法選取 (USA, Bikes) 點並將它從視覺效果中排除。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 Analyzer 等工具顯示的數字。Local calculations defined in a BEX Query will change the numbers as displayed through tools like Bex Analyzer. 不過,這些計算不會反映在 SAP 透過公用 MDX 介面所傳回的數字中。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 查詢連接到查詢 Cube 以設定要累積的彙總 (也就是變動總合) 時,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 Analyzer 中會顯示為 "*",但 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 任何貨幣格式 (例如 $2,300 或 4000 AUD) 都不會反映在 Power BI 中。Any currency formatting (for example, $2,300 or 4000 AUD) is not reflected in Power BI.
測量單位Units of measure 測量單位 (例如 230 公斤) 不會反映在 Power BI 中。Units of measure (for example, 230 KG) are not reflected in Power BI.
索引鍵與文字 (短、中長、長)Key versus text (short, medium, long) 對於 SAP BW 特性 (例如 CostCenter),欄位清單會顯示單一資料行 [成本中心]。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 Analyzer 等工具中,當查詢中包含一個特性時,使用者可以選取要使用的階層。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 Analyzer 將自動套用這些篩選。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 Analyzer 中。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 支援根據 Cube 中保留的速率進行貨幣轉換。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 在 [Get Data](取得資料) 中,會同時顯示特性/量值名稱 (描述) 和技術名稱。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.
客戶結束變數Customer Exit Variables 公用 API 不會公開「客戶結束」變數,因此 Power BI 不支援。Customer Exit variables are not exposed by the public API, and are therefore not supported by Power BI.
特性結構Characteristic Structures 基礎 SAP BW 來源中的任何特性結構,會導致在 Power BI 中公開的量值「爆發」。Any Characteristic structures in the underlying SAP BW source will result in an ‘explosion’ of measures being exposed in Power BI. 例如,有 Sales 與 Costs 兩個量值,以及一個包含 Budget 和 Actual 的特性結構,則會公開四個量值:Sales.Budget、Sales.Actual、Costs.Budget、Costs.Actual。For example, with two measures Sales and Costs, and a characteristic structure containing Budget and Actual, four measure will be exposed: Sales.Budget, Sales.Actual, Costs.Budget, Costs.Actual.

後續步驟Next steps

如需 DirectQuery 的詳細資訊,請參閱下列資源:For more information about DirectQuery, check out the following resources: