Issue loading MDX Query into PowerBI when it loads into SSMS with no problems

Question

I am trying to create a dashboard in PowerBI that refreshes with the information in a Pivot Table by connecting directly to a SSAS Cube. I retrieved the MDX query from the Excel file and the query runs perfectly in SSMS. However, when I import the query to PowerBI, the numerical data loads in, but the categorization data (text) loads in as [Plant].[Plant].[All] rather than the actual Plant name. Any insight into why this is happening?

SELECT 
  CrossJoin(
    {[Measures].[Closing Balance Amount], 
    [Measures].[Closing Balance Quantity]}, 
    Hierarchize(
      {DrilldownLevel(
        {[Product SKU - Material].[Material Type].[All]}
      ) }
    )
  ) DIMENSION PROPERTIES PARENT_UNIQUE_NAME, 
  HIERARCHY_UNIQUE_NAME ON COLUMNS, 
  NON EMPTY CrossJoin(
    CrossJoin(
      CrossJoin(
        Hierarchize(
          {DrilldownLevel(
            {[Profit Center].[Profit Center].[All]}
          ) }
        ), 
        Hierarchize(
          {DrilldownLevel(
            {[Product Hierarchy].[L11 BFPP].[All]}
          ) }
        )
      ), 
      Hierarchize(
        {DrilldownLevel(
          {[Product Hierarchy].[L14 SKU].[All]}
        ) }
      )
    ), 
    Hierarchize(
      {DrilldownLevel({[Plant].[Plant].[All]}) }
    )
  ) DIMENSION PROPERTIES PARENT_UNIQUE_NAME, 
  HIERARCHY_UNIQUE_NAME ON ROWS 
FROM 
  (
    SELECT 
      (
        {[Product SKU - Material].[Material Type].& [1U] & [ROH], 
        [Product SKU - Material].[Material Type].& [1U] & [PACK], 
        [Product SKU - Material].[Material Type].& [1U] & [HALB], 
        [Product SKU - Material].[Material Type].& [1U] & [FERT]}
      ) ON COLUMNS, 
      (
        {[Profit Center].[Profit Center].& [1U] & [ULAM] & [0000100058], 
        [Profit Center].[Profit Center].& [1U] & [ULAM] & [0000100075], 
        [Profit Center].[Profit Center].& [1U] & [ULAM] & [0000100022], 
        [Profit Center].[Profit Center].& [1U] & [ULAM] & [0000100090], 
        [Profit Center].[Profit Center].& [1U] & [ULAM] & [0000100091], 
        [Profit Center].[Profit Center].& [1U] & [ULAM] & [0000900015]}
      ) ON ROWS 
    FROM 
      [Stock Balance]
  ) 
WHERE 
  (
    [Reporting Currency].[Reporting Currency].& [USD], 
    [Segment].[Segment].& [1U] & [0000001010], 
    [Company Code].[Company].[All], 
    [Time].[Month].& [2019] & [4]
  ) CELL PROPERTIES VALUE, 
  FORMAT_STRING, 
  LANGUAGE, 
  BACK_COLOR, 
  FORE_COLOR, 
  FONT_FLAGS

I would like to see the Plant name and SKU names rather than just the references.

Do you only get this behaviour with [Plant] or does it also happen with the other hierarchies? — whytheq
hmmm ... I added an 'answer' but sorry it is not much of a help. We have very mature OLAP cubes but we are currently porting over to Tabular so we can get the most out of Power BI ... — whytheq

whytheq whytheq · Accepted Answer · 2019-04-24T11:48:44

I have the following against Adventure Works:

1.

SELECT
   [Measures].[Internet Order Count] on 0,
   [Customer].[Customer].[Customer] on 1
FROM [Adventure Works]

Then I import this into Power BI via "Import" and the above MDX script.

The only slight oddity in Power BI is that the column headers/names are the full addresses:

This is trivial to change though - very easy to adjust the column headers.

In the table the member names look fine:

2.

If I include the ALL member then things get strange. So the script I use is now this:

SELECT
    [Measures].[Internet Order Count] on 0,
   {
    [Customer].[Customer].[All Customers],
    [Customer].[Customer].[Customer]
   } on 1
from [Adventure Works]

All Customers does not now appear in the visualization - definitely not good:

3.

So last I tried a live connection - now there is no ALL member at all, in the fields area, but at least the column headers are clean:

So I'd suggest either using a live connection to your cube OR start exploring Tabular cubes and get a plan to move over to Tabular as that is the flavour of SSAS which Power BI likes best.

Issue loading MDX Query into PowerBI when it loads into SSMS with no problems

1 Answers