Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
We've created a couple of SSAS Tabular models, which are exposed to Power BI through the Enterprise Data Gateway. A large number of reports are build in Power BI Desktop with live connection to a Tabular model.
Now, when uploading a report file, Power BI correctly picks up the datasource from the gateway; but a new data set is created for each report. So when I upload e.g. 25 reports, I end up with 25 data sets all pointing to the same Tabular model, causing a lot of clutter in the workspace.
The preferred behaviour is that one data set/connection to a Tabular model is created, and each report uses that data set instead of creating a new one.
Is this something that can be configured, or is this a bug? Or simply a missing feature?
Has anybody identified a workaround for this?
I agree with the Michiel; it is crazy that each Power BI (PBIX file), all of which are pointing to the same Tabular model, resulting in one connection per PBIX, with the further potential confusion of these connections named the same as the PBIX report, implying to our users that each connection is somehow different to the others.
**edit** I forgot to also mention the other, even more fundimental issue with the approach the Service currently seems to take re one dataset per report, that being enabling Q&A. As part of the enabling of Q&A for reports, you have to also enable the dataset; so instead of indicating you want your one, single tabular model to be available to Q&A once, you have to indicate it for each dataset connection to each report (it is not clear to me if you indicate one, but not the others, what the implications are).
This is such a basic expectation; Reporting Services has worked on this basis (one connection file, multiple reports) for years.
Sometimes you are left feeling that the left hand of MS does not speak to the right hand!
Is there anywhere I can "vote" for this functionality to be included in future updates?
@Anonymous@Michiel As of April there is a solution for this. After you deploy your initial report to the Service, you can then create your next report from that deployed dataset. Essentially, the Desktop connects to the Live Connection (model) and you can deploy more reports to the Service utilizing the same dataset. Accomplishing the "single datasource for many reports" process. I didn't think this worked for live connections initially, but I just tested it and it works as expected. Details here
Hi @Seth_C_Bauer.
For some reason my original replay has disappeared from the thread!
My reply explained how I had tried (and failed) the method your link points to. I receive an error when I chose the "Power BI Service" stating "Sequence contains more than one matching element"
I have been trying various work arounds all to no avail, until I thought I would exclude my username from the equation.
As part of the overall infrastructure we user accounts specifically setup for processing our tabular models. I added one of these accounts to an App Workspace as a member.
In BPI desktop I then created a new report, but unlike before, I was not connected to the PBI service with my own credentials, but with the credentials of the afore mentioned service account. I see the list of existing connections as expected.
This therefore points to an issue with my own account. The only thing I can think of is that I used my email address before the business signed up to Azure/Power BI, using the PBI trial. When we then signed up with MS for both Azure an PBI, I initially had issues with my work account and my trial account been seen as 2 separate accounts (I used my work email address when I started the trial).
Are you aware of any issues regarding this?
Thanks in advance
@Anonymous In regards to the use account, there can be mismatches in the UPN mapping that could cause problems, but I'm not aware of all the possible things that could go wrong in your scenario. Another thing to try would be to make sure that your user is also a member of the group workspace. I don't recall the specific scenario or if it is still an issue, but there were issues with users who are the owners of the group having issues if they weren't also a member.
Thanks again @Seth_C_Bauer
I am a memeber of the App Workspaces, indeed I created them.
I am now convinced there is an issue with my account as I have successfully created a number of reports, all pointint to existing connections within the PBI service, but in all cases I have not been able to do this with my own account.
I am sure there is an issue with my UPN mapping (of my account), but I have no idea where to look?
Thanks for the response @Seth_C_Bauer, however, unfortunately (unless I am missing something), I have tried (and failed) to apply the methodology you refer to.
When I select the "power BI Service" option I am presented with an error:
The error log file text below:
Feedback Type:
Frown (Error)
Timestamp:
2017-11-28T10:54:10.7315169Z
Local Time:
2017-11-28T10:54:10.7315169+00:00
Session ID:
74f827ea-6c4e-4399-9011-983ba2e7497c
Release:
November 2017
Product Version:
2.52.4921.581 (PBIDesktop) (x64)
Error Message:
Sequence contains more than one matching element
Stack Trace:
System.InvalidOperationException: Sequence contains more than one matching element
at System.Linq.Enumerable.Single[TSource](IEnumerable`1 source, Func`2 predicate)
at Microsoft.PowerBI.Client.Windows.Navigator.PowerBIServiceNavigator.TreeNodes.PowerBIServiceRootNode.GetSubTreeInternal()
at Microsoft.PowerBI.Client.Windows.Navigator.LiveConnectTreeNode.<>c__DisplayClassa.<GetSubTree>b__8(Object waitState)
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.FeedbackErrorInfo..ctor(String message, Exception exception)
at Microsoft.PowerBI.Client.Windows.Telemetry.PowerBIUserFeedbackServices.ReportException(IWin32Window activeWindow, WindowsHost windowsHost, FeedbackPackageInfo feedbackPackageInfo, Exception e)
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, IWin32Window owner)
at Microsoft.PowerBI.Client.Windows.PowerBIServiceNavigator.PowerBIServiceNavigatorFloatingDialog.Show(WindowsHost windowsHost, IAuthenticationManager authenticationManager, ICloudConfigurationManager cloudManager, IPowerBIServiceClient powerbiServiceClient, IPowerBITelemetryService telemetryService, IWindowService shellService, ConnectionProperties existingConnectionProperties)
at Microsoft.PowerBI.Client.Windows.PowerBIDataImporter.AddPowerBIServiceSource(ConnectionProperties existingConnectionProperties)
at Microsoft.PowerBI.Client.Windows.PowerBIDataImporter.TryAddSource(String sourceID, Query& newQuery)
at Microsoft.Mashup.Client.ClientShared.EditorDialogBase.Connect(String sourceId, Nullable`1 targetQueryGroupId, String[] additionalDefaultOptionalModules)
at Microsoft.PowerBI.Client.Windows.MainWindow.<.ctor>b__0(String s, Nullable`1 g)
at Microsoft.Mashup.Host.Document.DataSourceTemplateProvider.Connect(String formulaId, Nullable`1 targetQueryGroupId)
at Microsoft.Mashup.Client.ClientShared.Ux.FloatingDialog.SimpleDialog.DataSourceGalleryDialog.<>c__DisplayClass2.<ConnectToDataSource>b__0()
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, IWin32Window owner)
at Microsoft.PowerBI.Client.Program.<>c__DisplayClassb.<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)
OS Version:
Microsoft Windows NT 10.0.15063.0 (x64 en-US)
CLR Version:
4.7 or later [Release Number = 460798]
Peak Virtual Memory:
33.9 GB
Private Memory:
279 MB
Peak Working Set:
378 MB
IE Version:
11.726.15063.0
User ID:
7cdcf018-4a42-4639-9179-eb99e318047a
Workbook Package Info:
1* - en-GB, Query Groups: 0, fastCombine: Disabled, runBackgroundAnalysis: True.
Telemetry Enabled:
True
Model Default Mode:
Import
Snapshot Trace Logs:
C:\Users\jocallaghan\Microsoft\Power BI Desktop Store App\FrownSnapShot1087976931.zip
Performance Trace Logs:
C:\Users\jocallaghan\Microsoft\Power BI Desktop Store App\PerformanceTraces.zip
Disabled Preview Features:
PBI_shapeMapVisualEnabled
PBI_EnableReportTheme
PBI_numericSlicerEnabled
PBI_SpanishLinguisticsEnabled
PBI_daxTemplatesEnabled
CustomConnectors
PBI_reportBookmarks
Disabled DirectQuery Options:
PBI_DirectQuery_Unrestricted
Cloud:
GlobalCloud
WER Parameters:
{"AppName":"PBIDesktop","AppVersion":"2.52.4921.581","ModuleName":"System.Core.dll","Component":"System.Linq.Enumerable","Error":"System.InvalidOperationException","MethodDef":"Single","ErrorOffset":"3"}
WER Reported:
True
DPI Scale:
100%
Supported Services:
Power BI
Formulas:
section Section1;
Hi @Michiel,
What you describe is an expected behavior. As you use Live connection option to connect to SSAS database in Power BI Desktop, in this mode, all data come from a same database, and you are not allowed to add other SSAS databases in a single Power BI Desktop file. In other words, you create 25 reports in 25 Power BI Desktop file in your scenario, and you should get 25 datasets after uploading these Power BI Desktop files in this case.
If you want to have only one dataset, you can use import option to connect to SSAS database in Power BI Desktop, in this mode, you can import data from different SSAS databases, and create all the reports in a single Power BI Desktop file. This way, when you publish the Power BI Desktop file to Power BI Service, you will get a single dataset for all reports.
Thanks,
Lydia Zhang
Hi Lydia,
this is not what I meant. I'm aware that with Live connection, a report can use only one SSAS model. The point is that when I create more reports on the same model (so multiple .pbix files, all referring to the same SSAS model), I end up with multiple datasets in Power BI which are all the same: they all connect to the same SSAS model.
I don't get why this would be expected behavior - Power BI will derive the right data source in the data gateway from the information in the report file, so it shouldn't be too hard to determine that there's already a dataset pointing to the same data source. There's no way a user would need to have 25 datasets that are all the same basically.
Michiel
Hi @Michiel,
You create multiple PBIX files, right? If so, after you publish these PBIX file to Power BI Service, each dataset will be related to a PBIX file currently.
If you want to contain all reports in a single dataset, you would need to create all the reports in a single Power BI Desktop file.
Thanks,
Lydia Zhang
I understand that this is how it works currently, like @Seth_C_Bauer said. Your first comment suggests, however, that this is by design, or how it should be.
In a real-life scenario (like what I'm working on, with >2000 users in the near future) having all reports in a single Power BI Desktop file is not an option.
Anyway, I'll post an idea in the feedback section for this.
@Michiel Long standing "missing feature" - the hope was that the new download to PBIX feature in the Service would allow us to have a single dataset that supports many reports like you describe, plus have a "backup" of the reports we build just in case something were to happen in the Service, or we needed to move them to other workspaces.
Currently, there is no way around this.
Side Note: The new layout of the Power BI Service helps manage this a bit better. If you aren't familiar, give it a test run by enabling the preview feature in the Service. How to enable
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.