PBI Service not retaining mapped data source when there are multiple data sources available

[ckavaslar]

Hi all,

One of our data sources for PBI dataset and dataflows is an Oracle database with multiple users having access to different scemas and tables on the database.  I've been observing this issue after we started to add new datasets and dataflows to refresh with different user accounts.  Below you'll see the issue with screen captures, in summary PBI service does not retain the mapped data source when there are multiple available for the same database connection.

I am wondering if anyone else has observed this issue and found a fix, looking forward to hear from you.

Thanks,

Cuneyt

----->>

Same Oracle database has multiple schemas and different user accounts able to access to different tables.  We need to use separate users for different reports sourced by different tables.

Therefore, we created 3 different data sources on our PBI gateway, each data source using a different credentials to connect to the same database.

Problem definition:

For dataset (and dataflow) refreshes when the gateway is selected and data source connection is mapped to use a second or third data source, PBI service doesn’t retain the mapping and automatically switches to the 1st available data source in the drop down.

We select the DaaS… as the mapping and then click on Apply.  Received the confirmation pop up window and see the selection show properly.

Then, we go back and refresh the data, it fails.

Error: Encountered user gateway exception: '[DM_ErrorDetailNameCode_UnderlyingErrorCode]=-2147467259 [DM_ErrorDetailNameCode_UnderlyingHResult]=-2147467259 [Microsoft.Data.Mashup.ValueError.DataSourceKind]=Oracle [Microsoft.Data.Mashup.ValueError.DataSourcePath]=qmskpide [Microsoft.Data.Mashup.ValueError.ErrorCode]=-2147467259 [Microsoft.Data.Mashup.ValueError.Message]=ORA-01031: insufficient privileges [Microsoft.Data.Mashup.ValueError.Reason]=DataSource.Error [InnerType]=MashupValueException Oracle: ORA-01031: insufficient privileges [GatewayPipelineErrorCode]=DM_GWPipeline_Gateway_MashupDataAccessError [ErrorShortName]=GatewayClientErrorResponseException[GatewayId=2860079]/MashupDataAccessValueException[ErrorCode=-2147467259 HResult=-2147467259]/Wrapped(MashupValueException)[ErrorCode=-2147467259 HResult=-2147467259] [ExceptionErrorShortName]=GatewayClientErrorResponseException[GatewayId=2860079]'. RootActivityId = bd2c82f6-b807-4f6c-b90e-5f82d4105057.Param1 = [DM_ErrorDetailNameCode_UnderlyingErrorCode]=-2147467259 [DM_ErrorDetailNameCode_UnderlyingHResult]=-2147467259 [Microsoft.Data.Mashup.ValueError.DataSourceKind]=Oracle [Microsoft.Data.Mashup.ValueError.DataSourcePath]=qmskpide [Microsoft.Data.Mashup.ValueError.ErrorCode]=-2147467259 [Microsoft.Data.Mashup.ValueError.Message]=ORA-01031: insufficient privileges [Microsoft.Data.Mashup.ValueError.Reason]=DataSource.Error [InnerType]=MashupValueException Oracle: ORA-01031: insufficient privileges [GatewayPipelineErrorCode]=DM_GWPipeline_Gateway_MashupDataAccessError [ErrorShortName]=GatewayClientErrorResponseException[GatewayId=2860079]/MashupDataAccessValueException[ErrorCode=-2147467259 HResult=-2147467259]/Wrapped(MashupValueException)[ErrorCode=-2147467259 HResult=-2147467259] [ExceptionErrorShortName]=GatewayClientErrorResponseException[GatewayId=2860079] Request ID: 54afeed4-8a46-f2d4-a0b3-13630ccebdb3.

When we go back to the settings again, we see the data source mapped is changed to the 1st available data source on the Gateway for the same Oracle DB connection.  Refresh fails since the user in the first data source available to map does not have access to the table used in dataset (or dataflow).

Issue could be repeated by taking the same steps shown above.

[ckavaslar]

Hi Ailsa, that's right.  3 data sources with different names and DB credentials point to the same Oracle database and gateway dataset mapping seems to pick up the first data source.  I change it, apply the change and when I go back I see that it mapped to the first one.

Thanks,

Cuneyt

[Anonymous]

Hi @ckavaslar 

You mean, when you create 3 identical data sources (credentials) are not the same, then the dataset always maps the first data source?

Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.