Cannot create measure in table obtained by DirectQuery from Azure SQL Data Warehouse

[suszonydzik]

I’ve added data source of type Microsoft Azure SQL Data Warehouse, then I tried to add a measure by right clicking on table name:

but error occurred:

The message says there is no connection with source but I already successfully added this table so I'm confused what exactly is wrong.

Error Message:

A connection could not be made to the data source with the Name of 'xxx'.

The following exception occurred while the managed IDbConnection interface was being used: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 25 - Connection string is not valid);The parameter is incorrect.

Stack Trace:

Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException: A connection could not be made to the data source with the Name of 'xxx'.

The following exception occurred while the managed IDbConnection interface was being used: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 25 - Connection string is not valid);The parameter is incorrect.

   at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.AdomdConnection.IXmlaClientProviderEx.Discover(String requestType, String requestNamespace, IDictionary restrictions, InlineErrorHandlingType inlineErrorHandling, IDictionary requestProperties)

   at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.GetSchemaDataSet(String schemaName, String schemaNamespace, IDictionary adomdRestrictions, Boolean throwOnInlineErrors, IDictionary requestProperties)

   at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.GetSchemaDataSet(String schemaName, String schemaNamespace, AdomdRestrictionCollection restrictions, Boolean throwOnInlineErrors, AdomdPropertyCollection requestProperties)

   at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.GetSchemaDataSet(String schemaName, AdomdRestrictionCollection restrictions)

   at Microsoft.PowerBI.DAXParser.DAXACListPopulater.ServerInformation.LoadFunctionsWithRestrictions(AdomdRestrictionCollection restrictions, IModel model)

   at Microsoft.PowerBI.DAXParser.DAXACListPopulater.ServerInformation.LoadFunctionCache(IModel model)

   at Microsoft.PowerBI.DAXParser.DAXACListPopulater.EnsureServerInformation(IModel model)

   at Microsoft.PowerBI.DAXParser.DAXACListPopulater.PopulateAll(IModel model, DAXMatchObjectSet& matchObjects)

   at Microsoft.PowerBI.DAXParser.DAXParser.BuildContextForMeasure(IModel model, IMeasure measure, Boolean restrictedDirectQuery)

   at Microsoft.PowerBI.Modeler.ModelManager.BuildContextForMeasure(Measure tomMeasure, Boolean showNonUserTables, Boolean restrictedDirectQuery, Boolean enableVariationNotation)

   at Microsoft.PowerBI.Client.Windows.Modeling.Measure.BuildContext()

   at Microsoft.PowerBI.Client.Windows.HtmlControls.DaxFormulaBarHtmlControlAsync.UpdateFormulaBarContents()

   at Microsoft.PowerBI.Client.Windows.HtmlControls.DaxFormulaBarHtmlControlAsync.SetCalculation(ICalculationObject calculation)

   at Microsoft.PowerBI.Client.Windows.HtmlControls.DaxFormulaBarHtmlControlAsync.ShowCalculation(ICalculationObject calculation)

   at Microsoft.Practices.Prism.PubSubEvents.EventSubscription`1.<>c__DisplayClass2.<GetExecutionStrategy>b__0(Object[] arguments)

   at Microsoft.Practices.Prism.PubSubEvents.EventBase.InternalPublish(Object[] arguments)

   at Microsoft.PowerBI.Client.Windows.Services.EventAggregationService.<>c__DisplayClass8`2.<PublishInternal>b__7()

   at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)

Invocation Stack Trace:

   at Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()

   at Microsoft.Mashup.Client.ClientShared.StackTraceInfo..ctor(String exceptionStackTrace, String invocationStackTrace)

   at Microsoft.Mashup.Client.ClientShared.FeedbackErrorInfo..ctor(String message, Nullable`1 errorCode, String requestId, Exception exception)

   at Microsoft.Mashup.Client.ClientShared.UnexpectedExceptionHandler.<>c__DisplayClass1.<HandleException>b__0()

   at Microsoft.Mashup.Client.ClientShared.UnexpectedExceptionHandler.HandleException(Exception e)

   at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)

   at Microsoft.PowerBI.Client.Windows.Services.SchemaItemActivationService.SetActiveItem(SchemaItem schemaItem, Boolean raiseEventOnChange)

   at Microsoft.PowerBI.Client.Windows.ExplorationClientHost.<>c__DisplayClass3c.<SetActiveSchemaItem>b__3b()

   at Microsoft.PowerBI.Client.Windows.ExplorationClientHost.TryEnsureExplorationClientHost(Action action)

   at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.<>c__DisplayClass3.<SendAndMarshalExceptions>b__0(Object null)

   at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)

   at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)

   at System.Delegate.DynamicInvokeImpl(Object[] args)

   at System.Windows.Forms.Control.InvokeMarshaledCallbackDo(ThreadMethodEntry tme)

   at System.Windows.Forms.Control.InvokeMarshaledCallbackHelper(Object obj)

   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)

   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)

   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)

   at System.Windows.Forms.Control.InvokeMarshaledCallback(ThreadMethodEntry tme)

   at System.Windows.Forms.Control.InvokeMarshaledCallbacks()

   at System.Windows.Forms.Control.WndProc(Message& m)

   at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

   at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)

   at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)

   at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)

   at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)

   at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)

   at System.Windows.Forms.Form.ShowDialog(IWin32Window owner)

  at Microsoft.Mashup.Client.ClientShared.WindowManager.ShowDialog[T](T form, IWin32Window owner)

   at Microsoft.PowerBI.Client.Program.<>c__DisplayClassb.<Main>b__0()

   at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)

   at Microsoft.PowerBI.Client.Program.Main(String[] args)

[suszonydzik · Accepted solution]

There was a colon sign in my connection string, not comma. After this change I can create measures.

Thanks, for sharing!

Mark

[v-haibl-msft]

@suszonydzik

I can create measure successfully while my data source is Azure data warehouse. From the message you posted, it seems that there is a connection problem. Can you get the data refreshed properly while you click Refresh in Power BI Desktop?

What did you type in Server/Database field when you try to get data from Azure data warehouse? I get the connection string in Azure portal as below.

Best Regards,

Herbert