Kyle Hale on 02 Sep 2016 21:23:39
In SAP BW, there is a concept called a navigational attribute. It is essentially an attribute referenced by (i.e. “navigated to”) a dimension but whose values are stored outside of the dimension and retrieved at runtime.
SAP native tools handle these attributes as dimensional attributes of their parent dimension when designing queries and reports.
Using the MDX MDProvider BAPIs provided by SAP (i.e. the APIs used by PowerBI to interface with SAP BW and extract metadata on cubes), however, the way these navigational attributes are exposed is through the properties of the proper dimensions.
For example:
If you have an SAP Dimension called 0LOCATION and you have a navigational attribute associated with it called 0VENDOR, you can retrieve the value of this attribute for a given dataset by calling
Select
{Measures.Measure}
on COLUMNS,
{0LOCATION}
PROPERTIES MEMBER_CAPTION, 20LOCATION_0VENDOR
on ROWS
from CUBE
How do you know the name of this property? To retrieve this particular attribute’s property name (and any other navigational attribute property names) with BAPI, you must call
BAPI_MDPROVIDER_GET_PROPERTIES (API info)
However, the PowerBI SAP BW connector doesn’t call this API method at all and so never surfaces these attributes in the data source navigator.
All of the other major BI tools (Qlik, Tableau, Spotfire, Cognos) currently surface these “hidden” dimensional attributes in their SAP interfaces.
It is *extremely critical* that the PowerBI SAP connector supports these attributes as they are (despite the inherent performance flaw of adding runtime lookups to a preprocessed analytical engine!) a mainstay of SAP BW development and so will be a major feature gap between you and other visualization products.
The current workaround is to write custom MDX, but obviously from an end-user standpoint that’s not sustainable.
I found this article ( https://wiki.scn.sap.com/wiki/display/BI/MDX+syntax+and+concepts+(6)+-+Properties )
extremely helpful in determining the root cause and suggesting how to use the BAPI methods to retrieve the navigational attributes, make them available in the data source navigator, and generate the proper MDX to bring them into PowerBI.