Hi Team,
While trying to extract data from Essbase using an MDX query for Power BI Desktop, I encountered an error(Refer below images and detailed error log). Interestingly, I can successfully retrieve the identical data in an Excel file. It seems that Power BI has limitations in handling a substantial number of rows (>3000) of data. It can load data of less than 3,000 rows alongwith 67 columns, but it encounters challenges beyond that threshold.
Error log in detail
Feedback Type:
Frown (Error)
Error Message:
For security reasons DTD is prohibited in this XML document. To enable DTD processing set the DtdProcessing property on XmlReaderSettings to Parse and pass the settings into XmlReader.Create method.
Stack Trace:
Microsoft.Mashup.Host.Document.SerializedException
at Microsoft.Mashup.Evaluator.EvaluationHost.OnException(IEngineHost engineHost, IMessageChannel channel, ExceptionMessage message)
at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.Interface.IMessageChannelExtensions.WaitFor[T](IMessageChannel channel)
at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.WaitFor(Func`1 condition, Boolean disposing)
at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.get_TableSource()
at Microsoft.Mashup.Evaluator.Interface.TracingPreviewValueSource.get_TableSource()
at Microsoft.Mashup.Host.Document.Analysis.PackageDocumentAnalysisInfo.PackagePartitionAnalysisInfo.SetPreviewValue(EvaluationResult2`1 result, Func`1 getStaleSince, Func`1 getSampled)
Stack Trace Message:
For security reasons DTD is prohibited in this XML document. To enable DTD processing set the DtdProcessing property on XmlReaderSettings to Parse and pass the settings into XmlReader.Create method.
Invocation Stack Trace:
at Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
at Microsoft.Mashup.Client.UI.Shared.StackTraceInfo..ctor(String exceptionStackTrace, String invocationStackTrace, String exceptionMessage)
at Microsoft.PowerBI.Client.Windows.Telemetry.PowerBIUserFeedbackServices.GetStackTraceInfo(Exception e)
at Microsoft.PowerBI.Client.Windows.Telemetry.PowerBIUserFeedbackServices.ReportException(IWindowHandle activeWindow, IUIHost uiHost, FeedbackPackageInfo feedbackPackageInfo, Exception e, Boolean useGDICapture)
at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.<>c__DisplayClass14_0.<HandleException>b__0()
at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.HandleException(Exception e)
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
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.UI.Shared.WindowManager.ShowModal[T](T dialog, Func`1 showModalFunction)
at Microsoft.PowerBI.Client.Program.<>c__DisplayClass6_0.<RunApplication>b__0()
at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.<>c__DisplayClass3_0.<HandleExceptionsWithNestedTasks>b__0()
at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
at Microsoft.PowerBI.Client.Program.RunApplication(String[] args)
at Microsoft.PowerBI.Client.Program.Main(String[] args)
PowerBINonFatalError:
{"AppName":"PBIDesktop","AppVersion":"2.122.746.0","ModuleName":"","Component":"Microsoft.Mashup.Evaluator.EvaluationHost","Error":"Microsoft.Mashup.Host.Document.SerializedException - Microsoft.Mashup.Host.Document.Evaluation.ErrorEvaluationResult","MethodDef":"OnException","ErrorOffset":"","ErrorCode":""}
Snapshot Trace Logs:
C:\Users\Deepak\AppData\Local\Microsoft\Power BI Desktop\FrownSnapShotec922f57-dffe-4746-a911-1840a111af81.zip
Model Default Mode:
Import
Model Version:
PowerBI_V3
Performance Trace Logs:
C:\Users\Deepak\AppData\Local\Microsoft\Power BI Desktop\PerformanceTraces.zip
Enabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_qnaLiveConnect
PBI_b2bExternalDatasetSharing
PBI_enhancedTooltips
PBI_sparklines
PBI_scorecardVisual
PBI_NlToDax
PBI_fieldParametersSuperSwitch
PBI_angularRls
PBI_onObject
PBI_setLabelOnExportPdf
PBI_dynamicFormatString
PBI_oneDriveSave
PBI_oneDriveShare
PBI_gitIntegration
PBI_newCard
Disabled Preview Features:
PBI_SpanishLinguisticsEnabled
PBI_backstageUI
PBI_modelExplorer
Disabled DirectQuery Options:
TreatHanaAsRelationalSource
Cloud:
GlobalCloud
PowerBINonFatalError_ErrorDescription:
Microsoft.Mashup.Host.Document.Evaluation.ErrorEvaluationResult
PowerBIUserFeedbackServices_IsReported:
True
Formulas:
section Section1;
shared #"MAX_M M" = let
Source = Value.NativeQuery(Essbase.Cubes("https://essbasedev.com/essbase/xmla", [CommandTimeout=#duration(0, 0, 30, 0)]){[Name="EssbaseCluster"]}[Data]{[Name="MAX_M"]}[Data], "SELECT#(lf)#(lf) CrossJoin({[PTD Amount],[YTD Amount]},#(lf)#(lf) CrossJoin({[Actual],[Base]},#(lf)#(lf) {[Net Revenue],[Service Revenue],[Product Revenue],[Home Delivery Revenue],#(lf)#(lf) [Contribution],[Professional Compensation],[Non-DVM Compensation],#(lf)#(lf) [Order Count],[New Client Count],[Active Client Count],[Pet Medical Visit Count],#(lf)#(lf) [Dr Day Count],[Pet Appointment Count],[Appointment Lead Time], [Para Admin Hour Count]}#(lf)#(lf) ))#(lf)#(lf)ON COLUMNS,#(lf)#(lf)NON EMPTY#(lf) CrossJoin({Descendants([Practice Type], 2)},#(lf) CrossJoin(CrossJoin({[&VarYear]}, {Descendants([Period], 3)}),#(lf) CrossJoin({Descendants([VCA Western Division], 3)}, {Descendants([Total Cohort], 2)})))#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(lf)ON ROWS#(lf)#(lf)FROM#(lf)MAX_M.M#(lf)WHERE ([VCA US BU],[USD])")
in
Source;
Thanks,
Deepak
