Power BI consuming DWC Data error

[showmethebugs]

We are trying to pull data from DWC to Power BI for some reporting needs. We are using the SAP Data Warehouse Cloud-Data 1.40 Beta connector from GitHub to make the connection to DWC using OData. After previewing the data and clicking Load to push the data to a Power BI page we are getting a formatting error. We are able to pull data using Power Query and again when pushing the data to a Power BI page the same formatting error occurs. Attached are screenshots of the error.

We have also tested using the views using API OData calls via MS Edge. Those work without issue.

Opened SAP case 217744 / 2023 it has led us to open issues here on this GitHub. Dev side has shared : - The error message talks about ODBC/SQL: PowerBi uses a local sql server to store the replicated data. The insertion shows the error message of expected content. This would match the idea of having DWC Odata returning values/characters that are not accepted by the PowerBi data base. => Root cause for error is a specific record or data set in the returned data.

[ghost]

Let me rephrase your current sitatuation to check if I understand it correctly:

• the dialog for tenant, space and name shows up
• the authentication, where typically an intermediate browser windows open, works without an issue
• PowerBI opens the data previews and shows a data sample => This intermediate step indicates, that we can connect and retrieve data from the SAP Data Sphere.

The next step of importing the data into MS PowerBI fails with the error message of an "unexpected charactor" followed by ODBC error details.

At this point of time, the custom connector is not involved any more. The task of the connector is to perform the authentication and provide this - together with the service URL - back to PowerBI. For all subsequent steps (e.g. data preview and import of data) only native standard PowerBI and the SAP Data Sphere OData API is involved.

The ODBC Error: PowerBI uses a local client side ODBC database to store imported data. I assume that the ODBC error message is triggered by the PowerBI ODBC database and not by an error message of the OData request. Reading that you tried the OData call from the browser itself without an issue reconfirms this.

Proposed Next Steps Elaborate, if the OData response of SAP Data Sphere is somehow technically incorrect or if the PowerBI local database has an issue accepting correct values. For the latter, incorrect encoding could explain such errors.

[ghost]

Some hints for response verification:

• Call the OData API via browser and download response. Open it in a json editor and check if malformed json is detected.

Filter response before data import

• The preview dialog offers the option e.g. to configure the columns returned and the number of rows returned. Select just columns with normal ascii content and check if the import works. If yes, add additional columns until the error occurs - then you have found the column of concern.

[bweberKS]

The OData API calls work via a browser and the json looks good. Formatted 150,000 rows of text without issue.

Using Power Query in Power BI, it works but when converting the query to a Power BI table we get the error above.

If I copy a DWC view that is causing an error in Power BI, the copied view works but the original view still is generates error.

We can create stories in SAC of the views generating errors in Power BI without issue.

The views that are in DWC are unaltered data from our MS SQL Data Warehouse, no transformation is happening in DWC except for being in DWC vs. MS SQL. The MS SQL DW works without issue in Power BI and has been for 4+ years.

[ghost]

Hi bweberKS,

Some question to better understand your problem description: ad 1.) Does "json looks good" mean that you used a json tool/editor to validate the format of JSON? ad 2.) In your previous problem description you mentioned "clicking Load" on the preview screen triggers the error - in the previous summary you describe it as "convert query to a Power BI table". Does this refer to the same step?

Towards resolution: ad 3.) Do I get it right that you have a procedure in PowerBI to remove the error? I am referring to the following description: "If I copy a DWC view that is causing an error in Power BI, the copied view works but the original view still is generates error.". Could you please elaborate on this procedure and describe the steps you are performing? Reading the short phrase you provided I conclude:

• You connect to the OData API of DWC, preview the data and click load
• The loading steps creates the error message and no data is replicated
• As a result a data source (without data) is part of the PowerBI Document
• You copy this data source
• You trigger a refresh on this copy and it retrieves the data without any issue.

ad 4.) You didn't provide feedback to the proposed approach of adding columns one-by-one to check if there is specific column causing the error message in PowerBI.

Best regards, Olaf

[ghost]

As there was no feedback for the past 3 three I will close the issue. Please re-open if still relevant.