Unable to connect Excel Power Query to SAP BW OLAP

[QC]

Hi team, 

I am trying to connect Excel/PQ to SAP BW multidimentional data source. I have installed the latest version of SAP BW ODBO driver (64 bit). Using Excel Get Data > From OLEDB option with the following connection string, I kept getting the following error.

(side note: I am able to connect Power BI to the same SAP BW server using SAP BW connector, and Excel using SAP AoF plug-in, both run ok, I just can't connect OLEDB)

Connection string: 
provider=MDrmSap.2;data source="_ xxx SAP - Business Warehouse (BW) Production";mode=Read;extended properties="CONNECTION_TYPE=0;SFC_CLIENT=xxx;SFC_LANGUAGE=EN;";mdx compatibility=0;subqueries=0

Appreciate your thoughts!

Best regards,

[ToddChitt]

Honestly, sounds like you are attempting something that is not supported in Excel. Even though Power Query in Excel is pretty close to Power Query in Power BI, it is not EXACTLY the same. You may have found one of those differences. 

Question back to you: is your 'Business Warehouse" connumicating with T-SQL language, or something else maybe, like MDX?

[QC]

Hi Todd, I was able to use MDX query in Power BI to pull data from our BW, so I was attemping to do the same with Excel using SAP ODBO driver ... not sure I am doing it correctly as I am new to this 😄

I found some old articles that seem it is feasible: 

https://supportuae.wordpress.com/2013/05/14/connecting-to-sap-bw-with-microsoft-excel/

https://help.sap.com/doc/saphelp_nw73ehp1/7.31.19/en-US/3a/431e02a4939240b510e851df092c50/content.ht...

Thanks!