SAP BW Dataflow Connector

[Anonymous]

Currently running into some difficulties editing dataflows within the PBI Service that connect to SAP BW:

• Partitioning the data pull due to SAP BW memory issues 
• Able to set it all up within PBI Desktop and refresh it manually, however unioning the data can sometimes fail due to memory issues
• Moved all of the above to be dataflows
• Able to create the dataflow initially and refresh it via PBI Gateway - seems to work so far

Started to receive an error when pulling this data into PBI desktop around "there were more columns in the result than expected" - unsure why so following advice from a different post: https://community.powerbi.com/t5/Service/DataFlow-Refresh-Error-and-Fix-for-added-Fields-in-Dynamics...

However, I've found that I am unable to edit the entities within the dataflow due to the following error and unclear why (especially since I am able to create the dataflow and refresh in the first place):

DataSource.MissingClientLibrary: This error could've been the result of provider-specific client software being required, but missing on this computer. To download client software for this provider, visit the following site and choose the 64-bit (x64) version of, at minimum, 'SAP .NET Connector 3.0.0.42': https://go.microsoft.com/fwlink/?linkid=872300

Details

ClientLibraryName = SAP .NET Connector 3.0.0.42

DownloadLink = https://go.microsoft.com/fwlink/?linkid=872300

Should also point out, the dataflow still appears to refresh.

[Boon_OTML]

Hello there,

We are also getting the same error message this week.  Seems to be a very recent change in the PBI Service that caused this error now.  There is a SAP BW connector dataflow that is still running OK today, in fact, and last week we were able to edit the dataflow without any issues.  However, today, the dataflow editor for this connector is now broken when just opening up the editor with this greeting:

"DataSource.MissingClientLibrary: This error could've been the result of provider-specific client software being required, but missing on this computer. To download client software for this provider, visit the following site and choose the 64-bit (x64) version of, at minimum, 'SAP .NET Connector 3.0.0.42': https://go.microsoft.com/fwlink/?linkid=872300

Details

ClientLibraryName = SAP .NET Connector 3.0.0.42

DownloadLink = https://go.microsoft.com/fwlink/?linkid=872300"

Appreciate a quick resolution, or any workarounds, as we need the BW dataflow editor to be actually reliable.

Thanks.

[v-chuncz-msft]

@Anonymous 

There are several known issues in editing dataflows. You may keep an eye on the Awareness.

[KenSkinner]

Yes, same issue with my dataflows.  I too ran into the memory error message when refreshing my PBIs and was going down this path for the exact same reason.  So I decided to build separate dataflow for each year of my sales data. My problems started with not being able to edit the data flow.

I noticed that if I tried to edit the dataflow and then add a field from BW, I would not get the gear next to the step name so I could not pull up the data source and select a new field or change existing ones.  And if I right clicked the step name the "Edit Settings" would be grey.  But the data would pull for the preview.

As I had already built the one for 2021, I used the export JSON feature to export that one, import it as a new dataflow and rename it. Then I would open the advanced editor and manually edit the month/year selections for the year I needed.  This worked until this week when it suddenly wouldn't pull the preview data and then wouldn't let me save it since it detected an error.  I had already built 2019-2021 but this week discovered I needed 2018 and 2017 as well.

I was lucky the only thing I had to change was the year number for the filter and the various name references.  To work around the editing issue I exported the JSON file for 2021, opened it in a text editor, replaced "2021" with "2017" and saved it as a new JSON file.  Then I imported that as a new dataflow and was able to refresh with no problems.

I did notice the problem of getting the "DataSource.MissingClientLibrary" error only happens when I try to edit an existing dataflow.  I've been able to create a new one from scratch with no problems.  The data preview works okay and I can save it.  I can also refresh existing dataflows with no problem.  If you were desperate enough I guess you could export a JSON file for one you wanted to change and then manually edit it and re-import it to make any changes but that would be a fair amount of work as well.

So I guess that could be a workaround but definitely look forward to having them fix the real issue.

[Anonymous]

@v-chuncz-msft Ah thanks for flagging - hoping this can be resolved alongside the issue mentioned in Awareness.

@KenSkinner that's a good point - I was hoping to steer clear of modifying the JSON directly as the changes were quite large (still in development phase so a lot of modifications being done).

I guess a combination of writing a custom function for the batches (that can be modified within the JSON if a change is required) and creating parameters for the dataflow IDs within the model is probably the best way forward right now.

[Anonymous]

Same issue here - we have multiple reports that query several dataflows that have several BW queries hooked up so having to rebuild and reconnect all of the underlying datasources in all of the reports would require A LOT of effort - is there a fix being worked on currently and what is the ETC, thanks.

[Edit 2 hrs later]:

I did some testing by trying the JSON export/import option suggested above - this one didn't work for me because I have a broken query (my data source has changed) which I cannot modify so the moment I go into dataflow edit mode I continue to receive the same "DataSource.MissingClientLibrary..." error. 

Also tried creating a blank dataflow and copied over some of the BW queries from the "problematic" dataflow - this workes only if you don't have to edit the dataflow once saved & closed... the moment you open up the dataflow for editions it throws back the same SAP.NET Connector library error and you are back to square one... 

Frankly, this is a major issue for our organization - really frustrating if I have to rebuild the whole dataflow model and all connections flowing from it in order to be able to bring back organizational reporting back to production, before MS releases a fix which looks like at this point is not even even acknowledged by MS as a problem... very disappointing...

[Boon_OTML]

Hi all,

So here's an emergency workaround after MS Support responded about this known bug:

Entering Dataflow editor now automatically reset the on-prem gateway config to "(none)" in

• Home > Options > Project Options
  You have to re-configure the settings in that pop-up window all over again.

But we will wait for MS to resolve (ETA 23-Feb) so that we don't waste project budget to reconfigure the dozens of BW dataflows. Just edit those that we must edit for this week so as not to chase our tails.

• So NOT looking forward to that dreaded "Validating queries" yellow spin of death by thousand cuts!!😱

They indicated this bug is related to this particular alert on their main PBI Support site:

"Power BI customers editing existing dataflows that use the on-premises gateway client may see the message, "Please specify how to connect". Any changes made will not be saved. Engineers have identified the root cause and a fix is expected to be deployed by end-of-day 02/22/2021"

By the way, anyone on PBI Premium got sucker-punched by this new bug - or it just affects shared capacity?

[KenSkinner]

Thanks @Boon_OTML , that works great as a temp fix.  Just curious, but do you think the fix that will deploy on 2/22 will also fix the missing "Edit settings" option for the query steps?  I can no longer go back into the selection choices for the SAP BW connection and add/change/delete fields.  As I mentioned above there's no gear to click on and the edit settings option is grey.

[Anonymous]

@Boon_OTML  - thank you as well - this worked for me too.

Our Org. is on Premium capacity, so yes Premium is affected too, to answer your question.

I also got a support call from MS yesterday that also mentioned possible relation between the "Please specify how to connect" issue and this one..

@KenSkinner  - for what it's worth, I gave up modifying BW queries from within the dataflow editor long ago - just using regular PBI desktop query editor for any major change needed and then just copying over the complete query text from desktop advanced editor into dataflow advanced editor.  

[KenSkinner]

@Anonymous  - thanks, that's probably easier than trying to manually update the query directly in the dataflow advanced editor.

Hope MS irons out some of these issues (especially memory) as it's a major stumbling block to my company effectively using PBI with SAP BW.  The other alternative is to use the SAP Analytics Cloud dashboard which in my opinion isn't as good as PBI.  But since it's going on years and not just months I'm beginning to wonder if it's time to trade in my PBI sports car for an SAP tractor 🙂

[buschbob1974]

We have moved on to using the Theobald SAP dataconnector Xtract Universal with both PBI and SQL destinations.  The SAP BW connector has been a challenge.