MDX: FAQ for Power BI accessing BW or BW/4HANA [SAP Note 2777473]

Publicado porfelipelamounierPublicado emEN version, Power BI (PBI), SAP BW

Welcome!
By Felipe Lamounier, state of Minas Gerais, Brazil – powered by 🙂My Easy B.I.

📑 Table of Contents:

  1. Q1: How can issues with Power BI and BW Queries be analyzed?
  2. Q2: Extract from BW in Power BI runs into memory problems. What to do?
  3. Q3: Performance issues in Power BI Extracts. What to do?
  4. Q4: Why are zero records in Power BI unexpectedly displayed?
  5. Q5: Why do results in Power BI differ to results from RSRT or BEx?
  6. Q6: Why is Power BI not able to connect to CDS views?
  7. Q7: Power BI problems after upgrade due to changing UIDs:  ‘The key didn’t match any rows in the table’
  8. Q8: Sessions aren’t closed on BW side, how can they be closed?
  9. See Also

🔭 See also Related Posts:

🔭 See also Pages:
🏠Home Page
💡Blog


🎓🚀Training&Education
🖼️Site Image Gallery

Q1: How can issues with Power BI and BW Queries be analyzed?

Power BI uses the MDX interface↗ and general MDX troubleshooting steps can be followed.

  1. Check if the frontend vendor can provide the MDX statement. Go with it to transaction MDXTEST and test the issue there.
  2. If this is not possible you can analyse what the frontend is requesting from the BW backend by analysing the RSTT trace↗ (see SAP Note 899572 on details of this trace).
  3. From the RSTT trace the MDX statements can be extracted and viewed in transaction MDXTEST (See SAP Community Wiki: Extracting MDX Statements from RSTT Trace↗).
  4. The query used in the MDX statement can be checked in transaction RSRT↗.

Check the SAP Community Wiki on the MDX interface↗ for details.

The generation of the MDX statement is done by Power BI. If you identify any problems with the syntax or the semantics of the statement contact Power BI support.

Anúncios

Q2: Extract from BW in Power BI runs into memory problems. What to do?

  • Review general SAP Note: 1381821↗ Memory and Performance Analysis for MDX and
  • Ensure that latest “Performance and Memory” optimizations are applied on your system.
    You can find a list of available improvements in collective SAP Note: 1142664↗ – MDX: Composite SAP Note about performance improvements

When receiving memory errors when running the reports, these are in 99% of cases due to the size of the requested resultset.

Generally, the MDX interface is not meant to be used for mass data extraction (also see SAP Note 1968598↗).

There are now two options:

  1. Increase the memory configuration in your system with your basis team (check the parameters in transaction ST22)
  2. Decrease the size of the requested result set. The main problem in the generated MDX statements are:
    1. Missing filters.
    2. Number of dimensions requested.
    3. High number of dimension properties.
    4. The NON EMPTY statement is NOT used in the MDX generated by Power BI (see MDX: Empty and NON Empty↗).
    5. Avoid sorting escpecially for characteristics with high granularity if you do not need sorted data (see SAP Note 2575837↗ – Runtime improvement: Avoiding sorting)

Q3: Performance issues in Power BI Extracts. What to do?

See Q2.

Q4: Why are zero records in Power BI unexpectedly displayed?

The BW Query property Zero suppression is ignored the MDX processor. Instead the statement NON EMPTY decides if NULL records are displayed.

See for more details the SAP Community: MDX: Empty and NON Empty↗.

Q5: Why do results in Power BI differ to results from RSRT or BEx?

  1. For BW Queries with customer exit variables:
       Test the behaviour with and wihout the RSADMIN parameter MDX_GET_MEMBERS_NO_UEXIT_VARS.
       The RSADMIN  parameter can be set with the report SAP_RSADMIN_MAINTAIN (the parameter is documented in SAP Note 2378998↗). 
  2. Review the MDX restrictions:
        See SAP Note: 820925↗ MDX – Restrictions

Q6: Why is Power BI not able to connect to CDS views?

Implement the following SAP Note: 2647905↗ – BAPI GetHierarchies: Error message RSSH_GET_HIERARCHIES_OF_IOBJNM-1

Q7: Power BI problems after upgrade due to changing UIDs:  ‘The key didn’t match any rows in the table’

  1. Check if the following SAP Note can help to overcome this error:
        2658221↗ – BAPI GetMeasures: Changes resulting from SAP Note 2569461
  2. After applying the SAP Note you will also need to set the RSADMIN parameter as described in the same.
  3. Also regenerate the BW query in transaction RSRT (Generate Report).
Anúncios

Q8: Sessions aren’t closed on BW side, how can they be closed?

Session management is done by the frontends. So any frontend deciding on creating multipe RFC sessions is also responsible for closing these RFC sessions again.

RFC connections must be explicitly closed by the same application which opened the connection. See the general SAP Notes:

  • 568271↗ Lifetime of an RFC connection
  • 780817↗ Analyse der RFC User * SM04

See this described for several other frontend tools as an example:

  • 2177121↗ Common issues with RFC sessions and time out handling in BI Java Web
  • 2115095↗ The RFC session does not get closed immediately after the data refreshing or cl
  • 2020608↗ What is the lifetime of connections to SAP BW BEx queries via BICS when a Web I

The BW/NetWeaver backend will not randomly stop RFC sessions if the RFC client is still keeping it open.

Please, check for any session timeout parameters in the frontend.

Please, also limit the number of sessions the frontend tool is allowed to create.

Please, then check with the support of “Power BI” team. SAP Support cannot help with the session management of the frontend tool.

See Also

SAP Community Wiki: MDX Interface/MDX Processor↗

Keywords: SAP BW; SAP NOTE 2777473 – MDX: FAQ for Power BI accessing BW or BW/4HANA; Performance issues SAP BW x PBI; How to increase query performance between SAP BW and Power BI PBI; Complete Guide to MDX for Power BI: Accessing SAP BW and BW/4HANA; Mastering MDX in Power BI: Everything About Accessing SAP ; BW and BW/4HANA; MDX Power BI SAP BW; MDX BW/4HANA Query; How to use MDX in Power BI for SAP BW; Troubleshooting MDX in Power BI for BW/4HANA; The Ultimate Guide to MDX for Power BI; How to use MDX in Power BI to access BW or BW/4HANAp; Step-by-step guide to creating MDX queries for Power BI

Did you like the content? Want to get more tips? Subscribe ↗ for free!

Follow on social media:

Deixe um comentário