Forum Discussion
Community ChampionAzure Analysis Services and Files on SharePoint Online
Hi -
I have a data model that lives in AAS. There are 40 something tables whose data source is a table in SQL Server. There is an automated process in Azure that refreshes the data model every hour. This worked great...until I needed to start pulling in data for the accountants and they love their Excel spreadsheets. Those Excel spreadsheets are located in a folder on SharePoint Online. When processing or deploying the model in Visual Studio, I can use my user credentials for the Excel files on SharePoint to retrieve data. Which works...almost. The problem is that after deployment the user credentials expire in 90 minutes on SharePoint so eventually the Azure auto refresh process fails.
So the key seems to be to make sure that the service principal for the Azure automation process has the ability to access the files on SharePoint. And the second part is how to deploy the model with the correct impersonation configured (maybe???). Been Googling all afternoon and haven't been able to find much guidance on either front. If anyone could offer some assistance or point me in the right direction I would certainly appreciate it!
This wasn't a "how do I" kind of post. I was looking for ideas on possible solutions from some of the more experienced members.
Anyway...after ten days of trying to figure this out (and of having to manually process and deploy the database) the solution I went with is as follows:
- Take the data from SharePoint and drop it into SQL Server
- Created Azure Logic App for each data source and whenever the Excel file was updated, truncate the existing data in the database and repopulate with new data from the spreadsheet.
Thanks for the advice though!
5 Replies
- littlemojopuppy
For about an hour after manually deploying the model from Visual Studio, I can manually process it from SSMS with no issues. But after the credentials expire, this is the error message I get...
Failed to save modifications to the server. Error returned: 'An error occurred during On-Premise Gateway related activity. Additional error details: DM_GWPipeline_Gateway_MashupDataAccessError
Received error payload from gateway service with ID 1536613: Async operation 6b32693e-6bb7-442b-b155-275176e2f8ee is faulted with [0]Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.MashupDataAccessValueException: Mashup expression evaluation error. Reason: .GatewayPipelineErrorCode=DM_GWPipeline_Gateway_MashupDataAccessError
GatewayVersion=3000.51.7
Reason= --->
[1]Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.GatewayPipelineWrapperException: Substituted: MashupValueException:<pi>Microsoft.Data.Mashup.MashupValueException (0x80004005): SharePoint: Request failed: https://somerandomsharepoint.sharepoint.com/sites/everyone/Shared Documents/Business Intelligence/Data Sources/Finance/Revenues and Forecast_xlsx/_api/contextinfo
at Microsoft.Data.Mashup.ProviderCommon.MashupResource.<>c__DisplayClass45_2`1.<StartEvaluationAndGetResultSource>b__0()
at Microsoft.Mashup.Security.Impersonation.RunAsProcessUser[T](Func`1 func)
at Microsoft.Data.Mashup.MashupCommand.EvaluateAndGetSource[T](String commandText, CommandType commandType, Int32 commandTimeout, MashupParameterCollection parameters, String resultTransform, Boolean forColumnInfo, Boolean executeAction)
at Microsoft.Data.Mashup.MashupCommand.ExecuteReader(CommandBehavior commandBehavior, MashupCommandBehavior mashupCommandBehavior)
at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessorUtils.MashupExecuteReaderAsync(DbCommand command, ExecuteQueryRequest queryRequest, Guid activityId)
at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessor.<>c__DisplayClass3_3.<<ExecuteAdoQuery>b__2>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.PowerBI.DataMovement.Pipeline.GatewayPipelineTelemetry.PipelineTelemetryService.<ExecuteInActivity>d__7`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessor.<>c__DisplayClass3_0.<<ExecuteAdoQuery>b__0>d.MoveNext()</pi>GatewayPipelineErrorCode=DM_GWPipeline_UnknownError
GatewayVersion=
InnerType=MashupValueException
InnerMessage=<pi>SharePoint: Request failed: https://somerandomsharepoint.sharepoint.com/sites/everyone/Shared Documents/Business Intelligence/Data Sources/Finance/Revenues and Forecast_xlsx/_api/contextinfo</pi>
InnerToString=<pi>Microsoft.Data.Mashup.MashupValueException (0x80004005): SharePoint: Request failed: https://somerandomsharepoint.sharepoint.com/sites/everyone/Shared Documents/Business Intelligence/Data Sources/Finance/Revenues and Forecast_xlsx/_api/contextinfo
at Microsoft.Data.Mashup.ProviderCommon.MashupResource.<>c__DisplayClass45_2`1.<StartEvaluationAndGetResultSource>b__0()
at Microsoft.Mashup.Security.Impersonation.RunAsProcessUser[T](Func`1 func)
at Microsoft.Data.Mashup.MashupCommand.EvaluateAndGetSource[T](String commandText, CommandType commandType, Int32 commandTimeout, MashupParameterCollection parameters, String resultTransform, Boolean forColumnInfo, Boolean executeAction)
at Microsoft.Data.Mashup.MashupCommand.ExecuteReader(CommandBehavior commandBehavior, MashupCommandBehavior mashupCommandBehavior)
at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessorUtils.MashupExecuteReaderAsync(DbCommand command, ExecuteQueryRequest queryRequest, Guid activityId)
at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessor.<>c__DisplayClass3_3.<<ExecuteAdoQuery>b__2>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.PowerBI.DataMovement.Pipeline.GatewayPipelineTelemetry.PipelineTelemetryService.<ExecuteInActivity>d__7`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessor.<>c__DisplayClass3_0.<<ExecuteAdoQuery>b__0>d.MoveNext()</pi>
InnerCallStack= at Microsoft.Data.Mashup.ProviderCommon.MashupResource.<>c__DisplayClass45_2`1.<StartEvaluationAndGetResultSource>b__0()
at Microsoft.Mashup.Security.Impersonation.RunAsProcessUser[T](Func`1 func)
at Microsoft.Data.Mashup.MashupCommand.EvaluateAndGetSource[T](String commandText, CommandType commandType, Int32 commandTimeout, MashupParameterCollection parameters, String resultTransform, Boolean forColumnInfo, Boolean executeAction)
at Microsoft.Data.Mashup.MashupCommand.ExecuteReader(CommandBehavior commandBehavior, MashupCommandBehavior mashupCommandBehavior)
at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessorUtils.MashupExecuteReaderAsync(DbCommand command, ExecuteQueryRequest queryRequest, Guid activityId)
at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessor.<>c__DisplayClass3_3.<<ExecuteAdoQuery>b__2>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.PowerBI.DataMovement.Pipeline.GatewayPipelineTelemetry.PipelineTelemetryService.<ExecuteInActivity>d__7`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessor.<>c__DisplayClass3_0.<<ExecuteAdoQuery>b__0>d.MoveNext()
([1]Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.GatewayPipelineWrapperException.StackTrace:)
at Microsoft.Data.Mashup.ProviderCommon.MashupResource.<>c__DisplayClass45_2`1.<StartEvaluationAndGetResultSource>b__0()
at Microsoft.Mashup.Security.Impersonation.RunAsProcessUser[T](Func`1 func)
at Microsoft.Data.Mashup.MashupCommand.EvaluateAndGetSource[T](String commandText, CommandType commandType, Int32 commandTimeout, MashupParameterCollection parameters, String resultTransform, Boolean forColumnInfo, Boolean executeAction)
at Microsoft.Data.Mashup.MashupCommand.ExecuteReader(CommandBehavior commandBehavior, MashupCommandBehavior mashupCommandBehavior)
at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessorUtils.MashupExecuteReaderAsync(DbCommand command, ExecuteQueryRequest queryRequest, Guid activityId)
at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessor.<>c__DisplayClass3_3.<<ExecuteAdoQuery>b__2>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.PowerBI.DataMovement.Pipeline.GatewayPipelineTelemetry.PipelineTelemetryService.<ExecuteInActivity>d__7`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessor.<>c__DisplayClass3_0.<<ExecuteAdoQuery>b__0>d.MoveNext()
--- End of inner exception stack trace ---
([0]Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.MashupDataAccessValueException.StackTrace:)
at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessor.<>c__DisplayClass3_0.<<ExecuteAdoQuery>b__0>d.MoveNext()
--- End of stack trace from previous location where exception was thrTechnical Details:
RootActivityId: 35ff9ee2-a6da-4314-9484-20b66fb49c5a
Date (UTC): 8/10/2020 12:17:10 PM
The command has been canceled.. The exception was raised by the IDbCommand interface.
The command has been canceled.. The exception was raised by the IDbCommand interface.
The command has been canceled.. The exception was raised by the IDbCommand interface.
The command has been canceled.. The exception was raised by the IDbCommand interface.
The command has been canceled.. The exception was raised by the IDbCommand interface.
The command has been canceled.. The exception was raised by the IDbCommand interface.
The command has been canceled.. The exception was raised by the IDbCommand interface.
'. - littlemojopuppy
This is the error message produced by the Azure automation function when attempting to process the model after credentials expiring...
Invoke-ProcessASDatabase : Failed to save modifications to the server. Error returned: 'An error occurred during On-Premise Gateway related activity. Additional error details: DM_GWPipeline_Gateway_MashupDataAccessError Received error payload from gateway service with ID 1536613: Async operation fb3f13b5-f366-4084-a8e9-52556b8ed59f is faulted with [0]Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.MashupDataAccessValueException: Mashup expression evaluation error. Reason: . GatewayPipelineErrorCode=DM_GWPipeline_Gateway_MashupDataAccessError GatewayVersion=3000.51.7 Reason= ---> [1]Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.GatewayPipelineWrapperException: Substituted: MashupValueException:<pi>Microsoft.Data.Mashup.MashupValueException (0x80004005): SharePoint: Request failed: https://somerandomsharepoint.sharepoint.com/sites/everyone/Shared Documents/Business Intelligence/Data Sources/Finance/Revenues and Forecast_xlsx/_api/contextinfo at Microsoft.Data.Mashup.ProviderCommon.MashupResource.<>c__DisplayClass45_2`1.<StartEvaluationAndGetResultSource>b__0() at Microsoft.Mashup.Security.Impersonation.RunAsProcessUser[T](Func`1 func) at Microsoft.Data.Mashup.MashupCommand.EvaluateAndGetSource[T](String commandText, CommandType commandType, Int32 commandTimeout, MashupParameterCollection parameters, String resultTransform, Boolean forColumnInfo, Boolean executeAction) at Microsoft.Data.Mashup.MashupCommand.ExecuteReader(CommandBehavior commandBehavior, MashupCommandBehavior mashupCommandBehavior) at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessorUtils.MashupExecuteReaderAsync(DbCommand command, ExecuteQueryRequest queryRequest, Guid activityId) at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessor.<>c__DisplayClass3_3.<<ExecuteAdoQuery>b__2>d .MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.PowerBI.DataMovement.Pipeline.GatewayPipelineTelemetry.PipelineTelemetryService.<ExecuteInActivity>d__7` 1.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessor.<>c__DisplayClass3_0.<<ExecuteAdoQuery>b__0>d .MoveNext()</pi> GatewayPipelineErrorCode=DM_GWPipeline_UnknownError GatewayVersion= InnerType=MashupValueException InnerMessage=<pi>SharePoint: Request failed: https://somerandomsharepoint.sharepoint.com/sites/everyone/Shared Documents/Business Intelligence/Data Sources/Finance/Revenues and Forecast_xlsx/_api/contextinfo</pi> InnerToString=<pi>Microsoft.Data.Mashup.MashupValueException (0x80004005): SharePoint: Request failed: https://somerandomsharepoint.sharepoint.com/sites/everyone/Shared Documents/Business Intelligence/Data Sources/Finance/Revenues and Forecast_xlsx/_api/contextinfo at Microsoft.Data.Mashup.ProviderCommon.MashupResource.<>c__DisplayClass45_2`1.<StartEvaluationAndGetResultSource>b__0() at Microsoft.Mashup.Security.Impersonation.RunAsProcessUser[T](Func`1 func) at Microsoft.Data.Mashup.MashupCommand.EvaluateAndGetSource[T](String commandText, CommandType commandType, Int32 commandTimeout, MashupParameterCollection parameters, String resultTransform, Boolean forColumnInfo, Boolean executeAction) at Microsoft.Data.Mashup.MashupCommand.ExecuteReader(CommandBehavior commandBehavior, MashupCommandBehavior mashupCommandBehavior) at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessorUtils.MashupExecuteReaderAsync(DbCommand command, ExecuteQueryRequest queryRequest, Guid activityId) at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessor.<>c__DisplayClass3_3.<<ExecuteAdoQuery>b__2>d .MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.PowerBI.DataMovement.Pipeline.GatewayPipelineTelemetry.PipelineTelemetryService.<ExecuteInActivity>d__7` 1.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessor.<>c__DisplayClass3_0.<<ExecuteAdoQuery>b__0>d .MoveNext()</pi> InnerCallStack= at Microsoft.Data.Mashup.ProviderCommon.MashupResource.<>c__DisplayClass45_2`1.<StartEvaluationAndGetResultSource>b__0() at Microsoft.Mashup.Security.Impersonation.RunAsProcessUser[T](Func`1 func) at Microsoft.Data.Mashup.MashupCommand.EvaluateAndGetSource[T](String commandText, CommandType commandType, Int32 commandTimeout, MashupParameterCollection parameters, String resultTransform, Boolean forColumnInfo, Boolean executeAction) at Microsoft.Data.Mashup.MashupCommand.ExecuteReader(CommandBehavior commandBehavior, MashupCommandBehavior mashupCommandBehavior) at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessorUtils.MashupExecuteReaderAsync(DbCommand command, ExecuteQueryRequest queryRequest, Guid activityId) at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessor.<>c__DisplayClass3_3.<<ExecuteAdoQuery>b__2>d .MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.PowerBI.DataMovement.Pipeline.GatewayPipelineTelemetry.PipelineTelemetryService.<ExecuteInActivity>d__7` 1.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessor.<>c__DisplayClass3_0.<<ExecuteAdoQuery>b__0>d .MoveNext() ([1]Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.GatewayPipelineWrapperException.StackTrace:) at Microsoft.Data.Mashup.ProviderCommon.MashupResource.<>c__DisplayClass45_2`1.<StartEvaluationAndGetResultSource>b__0() at Microsoft.Mashup.Security.Impersonation.RunAsProcessUser[T](Func`1 func) at Microsoft.Data.Mashup.MashupCommand.EvaluateAndGetSource[T](String commandText, CommandType commandType, Int32 commandTimeout, MashupParameterCollection parameters, String resultTransform, Boolean forColumnInfo, Boolean executeAction) at Microsoft.Data.Mashup.MashupCommand.ExecuteReader(CommandBehavior commandBehavior, MashupCommandBehavior mashupCommandBehavior) at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessorUtils.MashupExecuteReaderAsync(DbCommand command, ExecuteQueryRequest queryRequest, Guid activityId) at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessor.<>c__DisplayClass3_3.<<ExecuteAdoQuery>b__2>d .MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at Microsoft.PowerBI.DataMovement.Pipeline.GatewayPipelineTelemetry.PipelineTelemetryService.<ExecuteInActivity>d__7` 1.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessor.<>c__DisplayClass3_0.<<ExecuteAdoQuery>b__0>d .MoveNext() --- End of inner exception stack trace --- ([0]Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.MashupDataAccessValueException.StackTrace:) at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessor.<>c__DisplayClass3_0.<<ExecuteAdoQuery>b__0>d .MoveNext() --- End of stack trace from previous location where exception was thr Technical Details: RootActivityId: 9a4abcd8-a0b2-40e6-b7db-dc61f7582bc4 Date (UTC): 8/10/2020 4:00:33 PM The command has been canceled.. The exception was raised by the IDataReader interface. Please review the error message and provider documentation for further information and corrective action. The command has been canceled.. The exception was raised by the IDataReader interface. Please review the error message and provider documentation for further information and corrective action. The command has been canceled.. The exception was raised by the IDataReader interface. Please review the error message and provider documentation for further information and corrective action. The command has been canceled.. The exception was raised by the IDataReader interface. Please review the error message and provider documentation for further information and corrective action. The current operation was cancelled because another operation in the transaction failed. '. At line:34 char:5 + Invoke-ProcessASDatabase -DatabaseName $DatabaseName -RefreshType ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidArgument: (HC Common Data Model:String) [Invoke-ProcessASDatabase], OperationException + FullyQualifiedErrorId : Microsoft.AnalysisServices.PowerShell.Cmdlets.ProcessASDatabase
- littlemojopuppy
Last thing to add...I know the credentials expire because if I look at the data model in Tabular Editor, it shows me an expiration date/time
littlemojopuppy
You are unlikely to reach a solution in community forum for this kind of unknown issue that we cannot test and reproduce. I would suggestion you to create a support ticket or even ask for a phone call support in the ticket.
Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.- littlemojopuppy
This wasn't a "how do I" kind of post. I was looking for ideas on possible solutions from some of the more experienced members.
Anyway...after ten days of trying to figure this out (and of having to manually process and deploy the database) the solution I went with is as follows:
- Take the data from SharePoint and drop it into SQL Server
- Created Azure Logic App for each data source and whenever the Excel file was updated, truncate the existing data in the database and repopulate with new data from the spreadsheet.
Thanks for the advice though!
