slow

8 Topics

DAX too Slow with Filtering
Hi, I have the following measure: Measure = CALCULATE ( COUNT ( 'Table'[Value] ), FILTER ( ALL ( 'Table' ), 'Table'[ValidFromDate] < MAX ( 'Date'[Date] ) && 'Table'[ValidToDate] > MAX ( 'Date'[Date] ) ) ) The two tables (Table, Date) are not connected. I use this to see "Active" contracts on a specific date. However when I place it in a visual to see it on a daily basis (last 12 months for example), it is way too slow (I have 12 million rows). Is there a way to optimize this? Much appreciated!
zazaalaza
2 years ago Place DAX Commands and Tips
824Views
0likes
4Comments
REDO mesures without RELATEDTABLE
Hello, I could not find the answer on youtube, so I ask some help here. I ve got a new dataset to manage which was made not by me. It is very slow and I can't export many visuals to powerpoint directly because it lacks on memory. After investigation - the problem is RELATEDTABLE in DAX syntax - I was able to redo some mesures without relatedtable and it works just fine, but for some mesures I have different result, and old result with RELATEDTABLE is a right one. So my question is there any general tip to get rid of RELATEDTABLE and make my dax more fluent. Example of slow mesure: Base-TotalEng = CALCULATE( [base-F2F]+ [base-Phone]+ [Base-S2S]+ [Base-event]+ [base-FUE]+ [Base-Remote]+ [base-NLADOBE]+ [base-Web] ,RELATEDTABLE('Target') ,RELATEDTABLE('Decision')) Thank you
Anonymous
4 years ago Place DAX Commands and Tips
499Views
0likes
2Comments
Measure is slow when using variables
I have been trying to learn more about Optimising Dax using a data sample from sqlbi (DAX Optimizations Examples with Alberto Ferrari) Orders Table has 1.6 million rows of data. I have two identical measures except one does not use variables. The measure with variables takes 14 seconds to execute and the other 0.5 seconds. I am unsure why variables are having an impact on performance. Measure 1 Open Orders = VAR StartPeriod = MIN ( 'Date'[Date] ) VAR EndPeriod = MAX ( 'Date'[Date] ) RETURN CALCULATE ( COUNTROWS ( FILTER ( Orders, AND ( Orders[Order Date] <= EndPeriod, Orders[Delivery Date] >= StartPeriod ) ) ), ALL ( 'Date' ) ) Measure 2 Open Orders = CALCULATE ( COUNTROWS ( FILTER ( Orders, AND ( Orders[Order Date] <= MAX ( 'Date'[Date] ), Orders[Delivery Date] >= MIN ( 'Date'[Date] ) ) ) ), ALL ( 'Date' ) )
zenton
5 years ago Place DAX Commands and Tips
1.2KViews
0likes
3Comments
Severe Report Rendering Performance Degradation on May 2021 release
Anyone else experiencing report rendering performance issues in this release of Power BI Report Server? One of our customers PBI Report Servers was upgraded to May 2021, and after about a week many of the reports we have on the server now take several minutes to render. This doesn't apply to all reports, and we have had challenges tracking down why certain reports are slow to render and why others only take a few seconds. The CPU and Memory on the Report Server are under 40% utilization, so we aren't resource constrained. The content database has plenty of room allocated and its' server not resource constrained either. Here is the an example of some of the odd behavior we are seeing: If I create a brand new report with a one data source, one data table, and a single table visualization on a blank canvas and upload it to the Report Server, it takes 3 minutes to render on average. This file is less than 1 MB. This file can be authored in Jan 2021 or May 2021 release of PBI Desktop for RS. Same result. An existing report we have that is 300MB, has several pages, background images, and multiple data sources takes less than 5 seconds to render every time. If I take this same report and upload it to a different directory as a "new report", it performs just as well. All reports have not been configured with a scheduled refresh. Is anyone else experiencing this same issue? If a Microsoft rep is reading this, I would appreciate any insight you may have into how we can better troubleshoot this issue. Cheers
Solved
daxman
5 years ago Place Report Server
1.9KViews
0likes
2Comments
Slow Overwrite Publishing to the PBI Service
Does anyone know how to resolve slow overwrite publishing of datasets/reports? It does not seem that anyone has posted anything concerning this since 2018, and it "seems" that the issue must have been resolved with a new desktop release at that time. The overwrite publishing of datasets/reports can take a long long time, but strangely this is not always true. The time this takes is very inconsistent. If it the dataset/report does not exist, the publish to the service is quick. When it is an overwrite publish it can take hours. What is causing this? Was it a new release that is casuing this? How can this be resolved?
Solved
Anonymous
5 years ago Place Community Feedback
11KViews
2likes
2Comments
Repeated server name in API urls
Hi we're running the January 2019 release of Report Server and are getting slow performance reported by users running reports. We restart the service every few days while we are investigating as this helps with the performance issues. In the logs I can see errors where the RequestID doesn't relate back to an entry in ExecutionLog3 on the database. The detail of the error shows our server name repeated in the url eg http://powerbi/powerbi/api/explore/reports/3b1c1b5a-a4a1-4a37-961a-08acb7ae6f06/querydata 2019-05-22 11:32:38.4686|WARN|10|A data reduction algorithm was specified but the corresponding grouping does not exist. The data reduction will be ignored for the primary, secondary, and intersection hierarchy. - Source: 'PowerBI'| RequestID = b4256fb6-97b2-b6af-13e4-a34bf6444388 ClientSessionID = 79eeceeb-2b92-6427-7ed8-ac4942faeff1 2019-05-22 11:32:38.5468|ERROR|10|Unhandled error in the Web API|POST http://powerbi/powerbi/api/explore/reports/3b1c1b5a-a4a1-4a37-961a-08acb7ae6f06/querydata - Response Unknown| RequestID = b4256fb6-97b2-b6af-13e4-a34bf6444388 ClientSessionID = 79eeceeb-2b92-6427-7ed8-ac4942faeff1 System.ObjectDisposedException: Safe handle has been closed at System.Runtime.InteropServices.SafeHandle.DangerousAddRef(Boolean& success) at System.StubHelpers.StubHelpers.SafeHandleAddRef(SafeHandle pHandle, Boolean& success) at Microsoft.Win32.Win32Native.GetTokenInformation(SafeAccessTokenHandle TokenHandle, UInt32 TokenInformationClass, SafeLocalAllocHandle TokenInformation, UInt32 TokenInformationLength, UInt32& ReturnLength) at System.Security.Principal.WindowsIdentity.GetTokenInformation(SafeAccessTokenHandle tokenHandle, TokenInformationClass tokenInformationClass) at System.Security.Principal.WindowsIdentity.get_User() at System.Security.Principal.WindowsIdentity.GetName() at System.Security.Principal.WindowsIdentity.get_Name() at Microsoft.PowerBI.ReportServer.WebApi.PbiApi.PbiExecutionLogScope.Dispose() at Microsoft.PowerBI.ReportServer.WebApi.PbiApi.PbiApiController.<GetQueryDataAsync>d__19.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 System.Threading.Tasks.TaskHelpersExtensions.<CastToObject>d__3`1.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 System.Web.Http.Controllers.ApiControllerActionInvoker.<InvokeActionAsyncCore>d__0.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 System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.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 System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__0.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 System.Web.Http.Controllers.ActionFilterResult.<ExecuteAsync>d__2.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 System.Web.Http.Controllers.ExceptionFilterResult.<ExecuteAsync>d__0.MoveNext() 2019-05-22 11:32:38.7031|ERROR|13|Unhandled error in the Web API|POST http://powerbi/powerbi/api/explore/reports/3b1c1b5a-a4a1-4a37-961a-08acb7ae6f06/querydata - Response Unknown| RequestID = 0555856d-058b-b5a7-3dbf-f05bf849362e ClientSessionID = 79eeceeb-2b92-6427-7ed8-ac4942faeff1 System.ObjectDisposedException: Safe handle has been closed at System.Runtime.InteropServices.SafeHandle.DangerousAddRef(Boolean& success) at System.StubHelpers.StubHelpers.SafeHandleAddRef(SafeHandle pHandle, Boolean& success) at Microsoft.Win32.Win32Native.GetTokenInformation(SafeAccessTokenHandle TokenHandle, UInt32 TokenInformationClass, SafeLocalAllocHandle TokenInformation, UInt32 TokenInformationLength, UInt32& ReturnLength) at System.Security.Principal.WindowsIdentity.GetTokenInformation(SafeAccessTokenHandle tokenHandle, TokenInformationClass tokenInformationClass) at System.Security.Principal.WindowsIdentity.get_User() at System.Security.Principal.WindowsIdentity.GetName() at System.Security.Principal.WindowsIdentity.get_Name() at Microsoft.PowerBI.ReportServer.WebApi.PbiApi.PbiExecutionLogScope.Dispose() at Microsoft.PowerBI.ReportServer.WebApi.PbiApi.PbiApiController.<GetQueryDataAsync>d__19.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 System.Threading.Tasks.TaskHelpersExtensions.<CastToObject>d__3`1.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 System.Web.Http.Controllers.ApiControllerActionInvoker.<InvokeActionAsyncCore>d__0.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 System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.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 System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__0.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 System.Web.Http.Controllers.ActionFilterResult.<ExecuteAsync>d__2.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 System.Web.Http.Controllers.ExceptionFilterResult.<ExecuteAsync>d__0.MoveNext() 2019-05-22 11:32:38.7343|ERROR|13|Unhandled error in the Web API|POST http://powerbi/powerbi/api/explore/reports/3b1c1b5a-a4a1-4a37-961a-08acb7ae6f06/querydata - Response Unknown| RequestID = 0555856d-058b-b5a7-3dbf-f05bf849362e ClientSessionID = 79eeceeb-2b92-6427-7ed8-ac4942faeff1 System.ObjectDisposedException: Cannot access a disposed object. Object name: 'System.Net.HttpListenerRequest'. at System.Net.HttpListenerRequest.CheckDisposed() at System.Net.HttpListenerRequest.get_LocalEndPoint() at System.Net.HttpListenerRequest.get_IsLocal() at Microsoft.Owin.Host.HttpListener.RequestProcessing.OwinHttpListenerContext.GetServerIsLocal() at Microsoft.Owin.Host.HttpListener.RequestProcessing.CallEnvironment.get_ServerIsLocal() at Microsoft.Owin.Host.HttpListener.RequestProcessing.CallEnvironment.PropertiesTryGetValue(String key, Object& value) at Microsoft.Owin.Host.HttpListener.RequestProcessing.CallEnvironment.TryGetValue(String key, Object& value) at Microsoft.Owin.OwinContext.Get[T](String key) at System.Web.Http.Owin.OwinHttpRequestContext.get_IsLocal() at System.Web.Http.Owin.OwinHttpRequestContext.get_IncludeErrorDetail() at System.Net.Http.HttpRequestMessageExtensions.CreateErrorResponse(HttpRequestMessage request, HttpStatusCode statusCode, Func`2 errorCreator) at System.Web.Http.ExceptionHandling.DefaultExceptionHandler.Handle(ExceptionHandlerContext context) at System.Web.Http.ExceptionHandling.DefaultExceptionHandler.HandleAsync(ExceptionHandlerContext context, CancellationToken cancellationToken) at System.Web.Http.ExceptionHandling.LastChanceExceptionHandler.HandleAsync(ExceptionHandlerContext context, CancellationToken cancellationToken) at System.Web.Http.ExceptionHandling.ExceptionHandlerExtensions.<HandleAsyncCore>d__0.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 System.Web.Http.Dispatcher.HttpControllerDispatcher.<SendAsync>d__1.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 System.Web.Http.HttpServer.<SendAsync>d__0.MoveNext()
saj
7 years ago Place Report Server
2KViews
0likes
2Comments
Map Visual Takes lots of time to load in Power BI report server
Hi All, I am making use of Map visual in on of my reports. it takes lots of time to load in power bi report server (August 2018). Other visuals on the same report load pretty quickly when compared to Map visual. I am just plotting data points on Map visual. Please help fix this issue or guide me in the right direction to get this fixed. Looking forward to your feedback. Thanks
Anonymous
7 years ago Place Report Server
1.9KViews
0likes
1Comment
Why do reports take 20 mins to load after updating to report server August 2018?
Reports unusable since updating to PBIRS August 2018, just loading wheel! To fix I need to go back to March 2018; whats the easiest way of doing it; because March 2018 updated OK, we did not take a snapshot. We have 27 reports in use daily, some are OK (basic, not much data). Why is August 2018 update unusable?
Solved
kharveyausten
7 years ago Place Report Server
1.9KViews
0likes
2Comments