Problem using Copy Activity to Access SAP BW Queries with mandatory variable

[fillipebs]

Hello,

I'm using Copy Activity to Access SAP BW Queries, but almost every query requires me to fill up a set of mandatory variables.

Trying to access the same Query on PowerBI, it shows a new screen with the mandatory fields listed, so i'm able to select values and execute the process to access BW.

On Azure Data Factory, when I create a pipeline copy activity using a BW data set, I don't know where I need to put the values to set the mandatory values.

Could you please help me with this issue?

Thanks!

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: 782aef48-1f8e-9f56-f3bb-5885e40047b8
• Version Independent ID: 876ce81e-e760-45d9-8f51-d33b401ae1fe
• Content: Copy data from SAP BW using Azure Data Factory
• Content Source: articles/data-factory/connector-sap-business-warehouse.md
• Service: data-factory
• Product: unspecified
• GitHub Login: @linda33wj
• Microsoft Alias: jingwang

[vasivara-MSFT]

@fillipebs Thanks for the feedback! I have assigned the issue to the content author to evaluate and update as appropriate.

[wenbof-zz]

@fillipebs SAP BW via MDX connector of ADF cannot directly accept variables at present. ADF has a new connector focusing on more flexbile filtering on large volume of data which you may be interested. https://docs.microsoft.com/en-us/azure/data-factory/connector-sap-business-warehouse-open-hub

If you have to workaround the current issue, given you are also using PBI, here is one way with detailed steps as below. Hope this helps.

Enable PBI log

1. Close PowerBI if it’s running.
2. Create a new environment variable: a. Name: PBI_EnableSapBwTracing b. Value: true (this is via the steps Windows Explorer -> This PC ->  Properties -> Advanced System Settings -> Environment Variables… -> System Variables -> New)
3. When this advanced tracing is activated, an additional folder called SapBw will be created within the traces folder.
4. Open Power BI Desktop.
5. Clear the cache before capturing (In Power BI desktop,  go to menu item File -> Options and Settings -> Options, then under Global settings choose Data Load, and Clear Cache)
6. Enable Tracing (In Power BI desktop,  go to menu item File -> Options and Settings -> Options, then under Global settings choose Diagnostics, and Enable Tracing )
7. Reproduce the issue (open the navigator and expand the affected query)
8. Once done, close Power BI, so the logs are flushed to disk.
9. Make sure you deactivate this advanced tracing once you’re done.   Find the MDX query You need to look in the Microsoft.Mashup.*.log traces. Just look for MDX and you’ll find it.   For example: Engine/IO/SAP Business Warehouse/Command/ExecuteDbDataReader {"Start":"2018-04-04T14:04:32.0086579Z","Action":"Engine/IO/SAP Business Warehouse/Command/ExecuteDbDataReader","HostProcessId":"11012","CommandText":"EXECUTE MDX |*WITH MEMBER [Measures].[Microsoft.Mashup.Engine.unscaled.0] AS([Measures].[00O2TNIVJ7JAOL2HWAA88SA2B] 1.0)\r\nMEMBER [Measures].[Microsoft.Mashup.Engine.unscaled.1] AS([Measures].[00O2TNIVJ7JAOL2HWAA88PN8Z] 1.0)\r\nMEMBER [Measures].[Microsoft.Mashup.Engine.unscaled.2] AS([Measures].[00O2TNIVJ7JAOL2HWAA88QP6B] 1.0)\r\nSELECT\r\n    {\r\n        [Measures].[Microsoft.Mashup.Engine.unscaled.0],\r\n        [Measures].[Microsoft.Mashup.Engine.unscaled.1],\r\n        [Measures].[Microsoft.Mashup.Engine.unscaled.2]\r\n    }ON 0,\r\n    NON EMPTY{\r\n        [0CALDAY].[20160227],\r\n        [0CALDAY].[20151107] r\n    }\r\n    PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON 1\r\nFROM [Y6JPDCP10/ZZ_Y6JPDCP10Q_RMS] WHERE\r\n    CROSSJOIN(\r\n        [ADDFA].[YRNA001701M8400AHOUSACTA],\r\n        {\r\n            [ADDFA].[YRNA001701MS.000027],\r\n            [ADDFA].[YRNA001701MS.000025]\r\n        }\r\n    )\r\nCELL PROPERTIES VALUE SAP VARIABLES [!V000002] INCLUDING [DDAGR].[YRNA091701] [!V000003] INCLUDING [TIMEREF].[01W]**| WITH-OPTIONS(IgnoreEmptyResultset=true, USEFLATTENING=TRUE);","Behavior":"Default","ProductVersion":"2.57.0.0 (Main)","ActivityId":"b7006827-9092-4551-8f67-d2558ebd9788","Process":"Microsoft.Mashup.Container.NetFX40","Pid":9236,"Tid":1,"Duration":"00:00:14.5255060"}

[linda33wj]

please-close

[VarunBhandary]

Hi All, Can this be then added to the official documentation please? As figuring this out via forums is quite painful :). It would help others taking the same direction on what the recommendation, limitation is.