“Index was outside the bounds of the array” error when importing data using Power BI

0
votes

I'm using Power BI for the first time, and I'm trying to import a few tables from a SQL Server 2012 Express database into Power BI.

After I've selected the tables and click "Load" it fails at the "Creating connection in model..." stage with the following error:

Error Message:
Index was outside the bounds of the array.

Stack Trace:
   at Microsoft.Mashup.Client.ClientShared.Relationships.RelationshipTableEntries.TryGetForeignKeyRelationship(HashSet`1 tablesToProcess, RelationshipTableEntry leftEntry, RelationshipTableEntry rightEntry, RelationshipInfo relationship, ForeignKeyRelationship& foreignKeyRelationship)
   at Microsoft.Mashup.Client.ClientShared.Relationships.RelationshipTableEntries.GetForeignKeyRelationships(IEnumerable`1 tableEntries, IEnumerable`1 tableNames)
   at Microsoft.PowerBI.Client.Windows.Modeling.RelationshipLoader.GetForeignKeyRelationships(ReportMetadata reportMetadata, IDataModel dataModel, List`1 relationshipTableEntries, List`1 queriesToProcess)
   at Microsoft.PowerBI.Client.Windows.Modeling.RelationshipLoader.ImportRelationships(SynchronizationContext uiThreadContext, Report report, IDataModel dataModel, IAnalysisServicesLoadToken loadToken, IEnumerable`1 loadInputs, IAnalysisServicesDatabase database, IAnalysisServicesLoadContext loadContext, IRelationshipManager relationshipManager, HashSet`1 previouslyLoadedInputs)
   at Microsoft.PowerBI.Client.Windows.Modeling.RelationshipLoader.DetectAndImportRelationships(SynchronizationContext uiThreadContext, IAnalysisServicesLoadToken loadToken, IEnumerable`1 loadInputs, IAnalysisServicesLoadContext loadContext, Report report, IExtendedModelChangeScope modelChangeScope, List`1 newTableColumnPairs, HashSet`1 previouslyLoadedInputs, Action relationshipAutodetectStartCallback)
   at Microsoft.PowerBI.Client.Windows.Modeling.PowerQueryToModelLoader.CreateRelationships(IAnalysisServicesLoadContext loadContext, IExtendedModelChangeScope modelChangeScope, List`1 newTableColumnPairs, HashSet`1 previouslyLoadedInputs, Action relationshipAutodetectStartCallback)
   at Microsoft.PowerBI.Client.Windows.Modeling.PowerQueryToModelLoader.ProcessLoadInputs(IExtendedModelChangeScope modelChangeScope, Action relationshipAutodetectStartCallback)
   at Microsoft.PowerBI.Client.Windows.Modeling.PowerQueryToModelLoader.<>c__DisplayClassb.<PerformLoadInternal>b__7(IExtendedModelChangeScope modelChangeScope)
   at Microsoft.PowerBI.Client.Windows.Modeling.AsyncModelAuthoringService.<>c__DisplayClassf.<RunExtendedModelChangeAsync>b__e()
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.Tasks.Task.Execute()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Microsoft.PowerBI.Client.Windows.Utilities.TaskExtensions.WaitAndUnpackException(Task task)
   at Microsoft.PowerBI.Client.Windows.Modeling.PowerQueryToModelLoader.PerformLoadInternal(Action relationshipAutodetectStartCallback)
   at Microsoft.PowerBI.Client.Windows.Modeling.PowerQueryToModelLoader.PerformLoad(Action relationshipAutodetectStartCallback)
   at Microsoft.PowerBI.Client.Windows.Modeling.PowerQueryToModelLoader.<>c__DisplayClass1.<LoadTablesAsync>b__0(Object state)
   at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)

Stack Trace Message:
Index was outside the bounds of the array.

Invocation Stack Trace:
   at Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
   at Microsoft.Mashup.Client.ClientShared.FeedbackErrorInfo..ctor(String message, Exception exception)
   at Microsoft.PowerBI.Client.Windows.Telemetry.PowerBIUserFeedbackServices.ReportException(IWindowContainer activeWindow, WindowsHost windowsHost, FeedbackPackageInfo feedbackPackageInfo, Exception e, Boolean useGDICapture)
   at Microsoft.Mashup.Client.ClientShared.UnexpectedExceptionHandler.<>c__DisplayClass1.<HandleException>b__0()
   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, IWindowContainer owner)
   at Microsoft.Mashup.Client.ClientShared.Ux.FloatingDialog.FloatingDialog.ShowDialogWithTimeout(IWindowContainer owner, Nullable`1 showTimeout)
   at Microsoft.Mashup.Client.ClientShared.Ux.WindowService.ShowDialogWithTimeout(FloatingDialog dialog, Nullable`1 showTimeout)
   at Microsoft.PowerBI.Client.Windows.FloatingDialog.KoLoadToReportDialog.StartEvaluationAndShowDialog(IWindowService windowService, IEnumerable`1 queriesToLoad, Nullable`1 showTimeout, Boolean requireFullDataRefresh)
   at Microsoft.PowerBI.Client.Windows.FloatingDialog.KoLoadToReportDialog.<>c__DisplayClass2.<TryShowDialogForQueries>b__0(IWindowContainer ownerWindow)
   at Microsoft.PowerBI.Client.Windows.PowerBIDataImporter.LoadQueries(IEnumerable`1 queriesToLoad)
   at Microsoft.PowerBI.Client.Windows.PowerBIDataImporter.OnImportSuccess(IEnumerable`1 addedQueries, DataImportDestination destination)
   at Microsoft.Mashup.Client.ClientShared.DataImporter.ProcessImportedQueries(IEnumerable`1 importedQueries, DataImportDestination destination)
   at Microsoft.Mashup.Client.ClientShared.DataImporter.ImportNavigationSource(Query query)
   at Microsoft.Mashup.Client.ClientShared.DataImporter.OnGetPreviewResult(PreviewResult preview, Query query, String sourceID, String formulaTitle, Nullable`1 explicitImportDestination, Boolean isNewQuery, Boolean isFromEditor)
   at Microsoft.Mashup.Client.ClientShared.DataImporter.GetPreviewResult(Query query, String sourceID, String formulaTitle, Boolean isNewQuery, Boolean isFromEditor, Nullable`1 explicitImportDestination)
   at Microsoft.Mashup.Client.ClientShared.DataImporter.<>c__DisplayClass1b.<OnQuerySettingsResolved>b__18()
   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.ClientShared.WindowManager.ShowDialog[T](T form, IWindowContainer owner)
   at Microsoft.PowerBI.Client.Program.<>c__DisplayClassa.<Main>b__0()
   at Microsoft.PowerBI.Client.Windows.IExceptionHandlerExtensions.<>c__DisplayClass7.<HandleExceptionsWithNestedTasks>b__6()
   at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
   at Microsoft.PowerBI.Client.Program.Main(String[] args)

Model Default Mode:
DirectQuery

Snapshot Trace Logs:
C:\Users\User\AppData\Local\Microsoft\Power BI Desktop\FrownSnapShot996727657.zip

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

Disabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_EnableReportTheme
PBI_numericSlicerEnabled
PBI_SpanishLinguisticsEnabled
CustomConnectors
PBI_AdobeAnalytics
PBI_qnaExplore
PBI_variationUIChange
PBI_customVisualsGallery
PBI_canvasTooltips

Disabled DirectQuery Options:
PBI_DirectQuery_Unrestricted
TreatHanaAsRelationalSource

Cloud:
GlobalCloud

ModelChangeStartingStackTrace:
   at System.Environment.GetStackTrace(Exception e, Boolean needFileInfo)
   at System.Environment.get_StackTrace()
   at Microsoft.PowerBI.Client.Windows.Modeling.AsyncModelAuthoringService.RunExtendedModelChangeAsync(Action`1 extendedModelChange, CancellationToken cancellationToken, Boolean blockUI)
   at Microsoft.PowerBI.Client.Windows.Modeling.PowerQueryToModelLoader.RunExtendedModelChangeAsync(Action`1 action)
   at Microsoft.PowerBI.Client.Windows.Modeling.PowerQueryToModelLoader.PerformLoadInternal(Action relationshipAutodetectStartCallback)
   at Microsoft.PowerBI.Client.Windows.Modeling.PowerQueryToModelLoader.PerformLoad(Action relationshipAutodetectStartCallback)
   at Microsoft.PowerBI.Client.Windows.Modeling.PowerQueryToModelLoader.<>c__DisplayClass1.<LoadTablesAsync>b__0(Object state)
   at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
   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.QueueUserWorkItemCallback.System.Threading.IThreadPoolWorkItem.ExecuteWorkItem()
   at System.Threading.ThreadPoolWorkQueue.Dispatch()

I try to import no more than eight tables containing very little data (just a few thousand entries). I can add the first seven tables just fine, and then when adding table number eight the error pops up.

But if I try to add this table first it works, so there is not a specific table that is the problem. The error only appear after having added sufficient number of tables. Like it surpasses some treshold and then just fails, which I find puzzling as this is a relatively small database with around 100 table.

I get the same result using both Direct Query and Import options.

I run Windows 7 and Power BI Version 2.56.5023.861 64-bit.

1
powerbi
I'd guess it might be trying to create a relationship between tables that doesn't work, but it's really hard to diagnose without having access to a file that can reproduce the error. – Alexis Olson
Thank you for pointing that out, it turned out to be a problem with building the relationships which lead me to find out the real source of the problem. – Abra

1 Answers

0
votes

The problem here turned out to be the rowversion column in the tables I wanted to import. If the rowversion column was before the ForeignKey column, the import to Power BI would fail:

create table [dbo].[TableOne]
(
    [Id]                int         not null    identity(1,1)
    , [Rowversion]      rowversion  not null
    constraint [PK_TC] primary key nonclustered ([Id])
)
go

create table [dbo].[TableTwo]
(
    [Id]                int         not null    identity(1, 1)
    , [Rowversion]      rowversion  not null -- This will fail
    , [TableOneId]      int         null
    , constraint [PK_TCO] primary key nonclustered ([Id])
    , constraint [FK_TableOneId] foreign key ([TableOneId]) references [dbo].[TableOne]([Id])
)
go

But if I put the rowversion column last, it would import without a problem:

create table [dbo].[TableOne]
(
    [Id]                int         not null    identity(1,1)
    , [Rowversion]      rowversion  not null
    constraint [PK_TC] primary key nonclustered ([Id])
)
go

create table [dbo].[TableTwo]
(
    [Id]                int         not null    identity(1, 1)
    , [TableOneId]      int         null
    , [Rowversion]      rowversion  not null -- This will import successfully
    , constraint [PK_TCO] primary key nonclustered ([Id])
    , constraint [FK_TableOneId] foreign key ([TableOneId]) references [dbo].[TableOne]([Id])
)
go