problem with cross data in Multidimensional cube

[AzizBoumiza]

Hello, im an MS BI consultant, and im currently working on a dashboard that manages the financial reporting.

I started by using your solution v:1.0.5 and it worked pretty well for us, even thought there is some missing features that are Crucial for our final product ( Column Subtotal / Formating / Saving expansion on hierarchy).

I found out that these features are now available in the 2.02 Released version and also in the Beta 2.0.2+, but unfortunetly the solution didnt work well for us.

I would like to share with you the error message that appears when adding the hierarchy in the rows. Starting from a CMD SQL DUMPER.EXE POP UP:

to an error message that you can find below:

PS: i tried this version on a tabular cube and it works really well.

[AzizBoumiza]

Here is some details that may help reproduce the problem After adding the Columns we got this error:

[toresenneseth]

Hi,

Seems strange. Are you sure your model is correct? The reason I'm asking is that the error message comes from Power BI, not from us. Power BI generates and executes a query based on the Rows, Columns and Values and then simply returns the rows to our visual so that we can render it.

Can you check if you get something back if you use the built-in matrix with the same Rows, Columns and Values?

[AzizBoumiza]

Hello Tor,

First of all thanks a lot for your response.

I'm pretty sure that the Model is correct since your matrix works perfectly using the 1.0.5 version with the same rows. It works also for the basic matrix of PBI. Here is a screenshot of the Matrix 1.0.5:

The problem occurred when making the switch to the new versions.

thanks in advance for your help, and i hope we manage to solve this since your solution fits perfectly our needs.

[toresenneseth]

You are using version 3.0.1 Beta (2)? https://github.com/Profitbase/PowerBI-visuals-FinancialReportingMatrix/releases/tag/3.0.1

Can you get the log that is created by SQLDumper? As far as I know, SQLDumper runs if something fails in SSAS but they can probably use it with the Power BI query engine too, so maybe it could provide some clue to what's going wrong here?

Is there any useful information available if you copy/paste the details?

[AzizBoumiza]

Yes, I'm trying the 3.0.1 right now, but the problem happens with any version 1.0.5+ I am going to try to provide you with the SQL dumper logs as soon as possible, but here is the details of the error generated from power bi.

Feedback Type: Frown (Error)

Timestamp: 2020-07-02T00:14:08.0153268Z

Local Time: 2020-07-02T01:14:08.0153268+01:00

Session ID: d2d80f32-8dbf-491c-8db7-48xxxxxx7e

Release: May 2020

Product Version: 2.81.5831.1181 (20.05) (x64)

Error Message: Couldn't retrieve the data for this visual. Please try again later.

Stack Trace: System.InvalidOperationException: Could not resolve Field Solde_N from table 1 at Microsoft.DataShaping.Contract.FailCore(String msg) at Microsoft.DataShaping.Processing.Reconciliation.ResultTableLookup.GetFieldIndex(String fieldId, Int32 tableIndex) at Microsoft.DataShaping.Processing.Reconciliation.ExpressionReconciler.Visit(FieldValueExpressionNode node) at Microsoft.DataShaping.Processing.Reconciliation.DataShapeDefinitionReconciler.Reconcile(Calculation calculation) at Microsoft.DataShaping.Processing.Reconciliation.ReconciliationUtils.ReconcileWritable[TDSD,TRDSD](IList1 dsdItems, Func2 reconcileFunc) at Microsoft.DataShaping.Processing.Reconciliation.DataShapeDefinitionReconciler.Reconcile(DataIntersection intersection) at Microsoft.DataShaping.Processing.Reconciliation.ReconciliationUtils.ReconcileWritable[TDSD,TRDSD](IList1 dsdItems, Func2 reconcileFunc) at Microsoft.DataShaping.Processing.Reconciliation.DataShapeDefinitionReconciler.Reconcile(DataMember member) at Microsoft.DataShaping.Processing.Reconciliation.ReconciliationUtils.ReconcileWritable[TDSD,TRDSD](IList1 dsdItems, Func2 reconcileFunc) at Microsoft.DataShaping.Processing.Reconciliation.DataShapeDefinitionReconciler.Reconcile(DataMember member) at Microsoft.DataShaping.Processing.Reconciliation.ReconciliationUtils.ReconcileWritable[TDSD,TRDSD](IList1 dsdItems, Func2 reconcileFunc) at Microsoft.DataShaping.Processing.Reconciliation.DataShapeDefinitionReconciler.Reconcile(DataShape dataShape) at Microsoft.DataShaping.Processing.Reconciliation.DataShapeDefinitionReconciler.ReconcileInternal(DataShapeDefinition dsd) at Microsoft.DataShaping.Processing.DataShapeProcessor.d1.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.ReportingServicesHost.DataShapingTelemetryService.d9.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.DataShaping.Engine.DataShapeProcessingHost.d2.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.DataShaping.Engine.DataShapeEngine.d7.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.ReportingServicesHost.DataShapingTelemetryService.d__9.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at Microsoft.DataShaping.Common.TaskUtils.WaitAndUnwrap(Task task) at Microsoft.PowerBI.ExploreHost.DataShapeEngineHost.ExecuteSemanticQuery(IDataShapeEngine engine, SemanticQueryDataShapeCommand command, EngineDataModel engineDataModel, IExecuteSemanticQueryResultWriter writer, IConnectionPool connectionPool, IDataShapingDataSourceInfo dsDataSourceInfo, DataReductionConfiguration dataReductionConfig, IConnectionUserImpersonator connectionUserImpersonator, ITelemetryService telemetryService, QueryExecutionOptions queryExecutionOptions, Int32 queryId, CancellationToken cancelToken, IFeatureSwitchProvider featureSwitchProvider) at Microsoft.PowerBI.ExploreHost.SemanticQuery.ExecuteSemanticQueryFlow.ExecuteDataQuery(IQueryResultDataWriter queryResultDataWriter, EngineDataModel engineDataModel, DataQuery query, Int32 queryId, ServiceErrorStatusCode& serviceErrorStatusCode, CancellationToken cancelToken) at Microsoft.PowerBI.ExploreHost.SemanticQuery.ExecuteSemanticQueryFlow.ProcessAndWriteSemanticQueryCommands(IQueryResultsWriter queryResultsWriter, IList1 queries, HashSet1 pendingQueriesToCancel, EngineDataModel engineDataModel)

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.ErrorHostService.GetErrorDetails(ShowErrorDialogArgs args) at Microsoft.PowerBI.Client.Windows.ErrorHostService.<>cDisplayClass2_0.<b0>d.MoveNext() at System.Runtime.CompilerServices.AsyncTaskMethodBuilder.Start[TStateMachine](TStateMachine& stateMachine) at Microsoft.PowerBI.Client.Windows.ErrorHostService.<>cDisplayClass2_0.b0() at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.<>cDisplayClass1_0`1.b0() at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.<>cDisplayClass0_1.b0(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.UI.Shared.WebDialogs.WebDialog.<>n0(IWindowHandle owner) at Microsoft.Mashup.Client.UI.Shared.WindowManager.ShowModal[T](T dialog, Func`1 showModalFunction) at Microsoft.PowerBI.Client.Program.<>cDisplayClass2_0.

b1() at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.<>cDisplayClass3_0.b__0() at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action) at Microsoft.PowerBI.Client.Program.Main(String[] args)

OS Version: Microsoft Windows NT 10.0.16299.0 (x64 fr-FR)

CLR Version: 4.7 or later [Release Number = 461814]

Peak Virtual Memory: 34.4 GB

Private Memory: 435 MB

Peak Working Set: 691 MB

IE Version: 11.1932.16299.0

User ID: 294392f4-db80-4334-abbf-3770xxxxxf76

Workbook Package Info: 1* - fr-FR, Query Groups: 0, fastCombine: Disabled, runBackgroundAnalysis: True.

Telemetry Enabled: True

AS Live Connection: True

Performance Trace Logs: C:\Users\mboumiza\AppData\Local\Microsoft\Power BI Desktop SSRS\PerformanceTraces.zip

Enabled Preview Features: PBI_NewWebTableInference PBI_hierarchicalSlicerCreation

Disabled Preview Features: PBI_shapeMapVisualEnabled PBI_SpanishLinguisticsEnabled PBI_qnaLiveConnect PBI_inlineExplore PBI_realTimeRefresh PBI_showMinervaRibbon PBI_showMinervaViewNavigator PBI_v3ModelsPreview PBI_relativeTimeSlicer PBI_dataSearchCuration

Enabled DirectQuery Options: TreatHanaAsRelationalSource

Cloud: GlobalCloud

DPI Scale: 100%

Supported Services: Power BI Report Server, Power BI

Formulas:

section Section1;

[toresenneseth]

Hi,

We found a way to reproduce this issue internally, so we'll take it from here.

Thanks for reporting the issue! Will let you know in this thread when we have fixed it.

[toresenneseth]

Hi,

I just wanted to give an update on this issue:

Unfortunately, this is a bug in Power BI and not in our visual. We have reported the issue to Microsoft, but we don't know when they will fix it. If they cannot fix it quickly, we're hoping for some kind of workaround.

The reason it works in version 1.0.5 is that Power BI did not aggregate subtotals with that API version, but now they do - except it does not work properly for a SSAS data source when Rows have multiple levels. Using SSAS as a data source for the built-in matrix works because the built-in matrix seems to have access to a different API than custom visuals.

Anyway, we'll keep you posted on any progress.

[AzizBoumiza]

Hey Tor,

It's unfortunate.. I would be glad to hear back from you.

thank you anyways

[toresenneseth]

Hi,

I wanted to give an update on the progress here. Microsoft has not resolved the issue yet, and it also seems like they are deprecating SSAS multidimensional for report server. So it seems like it will only work for Power BI Desktop going forward...not sure if using multidimensional will be of much use when working with Power BI, then...