Pre-filter large SharePoint Online list before importing via ODATA
Hi, I want to prefilter and then import a large SharePoint Online list (more than 100k items) with ODATA and get following error: I have a list with the same structure and only 2k items in it. For this list it works this way without problems. What can I do to solve this issue for the large list. Thanks for your help. Michael
OData authentication to Power BI Report Server shared dataset
Hello everyone, We're trying to create reports using the same dataset so we can reuse them e don't use a lot o space replicating Power BI Reports. So, we create a datasource and a dataset using Report Builder. So far we have been able to make everything work. The only way that we could find to create Power BI Reports using this dataset, is using OData Feed (according to PBI doc and some posts in this community). So, I created the report using the URL http://powerbi/reports/api/v2.0/DataSets(dataset-id)/data and could get the data successfully. After I published the report to the Report Server, I could open and view the visualizations. But when I try to update the data (through update button or schedule refresh) it won't work. When I go to credentials and try to connect using the same credentials used in Power BI Desktop (while creating the report) it won't connect and give the message "Log on failed. Ensure the user name and password are correct." Can you anyone help me understanding what am I missing? Thank you in advance!
OData Credentials- Couldn't connect msg
Hello, Could you please help me with the below Issue, When I connect to the OData feed from Power BI Desktop, I can connect, import data, and create reports. OData URIs Example: https://localhost:7048/BC230/ODataV4/Company('CRONUS International Ltd.')/Customer?$filter=City eq 'Miami' 1- If I don't specify the /Customer?$filter=City eq 'Miami' (bring only Customer and apply a filter or select only needed columns in the OdataFeed using $filter and $select) I don't get any refresh problems when publishing the report in Power BI Report Server, Data Source credentials connect successfully and I can create a scheduled refresh. 2- I only get the below problem after adding /Customer?$filter=City eq 'Miami' When I publish the report to our internally hosted Power BI Report Server,I get a red x Couldn't Connect with the following message Here I am Using the same credentials, as in 1- However, I can create a scheduled refresh and the report does refresh successfully(Status=Completed Data Refresh). Note: I am connected to Microsoft Dynamics 365 Business Central What do you think the problem is? a Bug in Power BI Report Server or a setting in BC?
OData exception occurred: System.Net.WebException: The remote server returned an error: (503) Server
This error is returned when a report request is issued like: GET 'http://localhost/reports/api/v2.0/CatalogItems(Path='/TestDUO/Test')' (~/entityset/key) Getting 'CatalogItems(Path='/TestDUO/Test')' Getting service state using: http://servername/powerbi/api/servicestate In the trace log we see the next lines: at System.Net.HttpWebRequest.GetResponse() at Microsoft.ReportingServices.Portal.ODataWebApi.Utils.PbixReportHelper.ShouldReShred(PowerBIReport entity, Uri basePortalUrl, ILogger logger, IPrincipal userPrincipal, String reportServerHostName) at Microsoft.ReportingServices.Portal.ODataWebApi.Common.CatalogItemControllerHelper`1.GetItem(String key) at Microsoft.ReportingServices.Portal.ODataWebApi.Common.CatalogItemControllerHelper`1.GetAllowedActions(String Id) at Microsoft.ReportingServices.Portal.ODataWebApi.V2.Controllers.PowerBIReportsController.GetAllowedActions(String Id) at lambda_method(Closure , Object , Object[] ) at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.<>c__DisplayClass6_2.<GetExecutor>b__2(Object instance, Object[] methodParameters) at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ExecuteAsync(HttpControllerContext controllerContext, IDictionary`2 arguments, CancellationToken cancellationToken) --- 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__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.ActionFilterResult.<ExecuteAsync>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.Controllers.ExceptionFilterResult.<ExecuteAsync>d__6.MoveNext(). What might cause this error?Error in refreshing Power BI report based off OData connection in Custom authentication mode
I am unable to refreseh Power BI Reports based off OData service uploaded in PBI RS which used custom authentication. Following are the logs from RSPowerBI 2018-03-23 16:11:02.2304|INFO|6|Handling event with data: TimeEntered: 3/23/2018 11:11:00 PM, Type: Event, SessionId: 532cc6cd-d84a-4388-8531-35c884795c79, EventType: DataModelRefresh 2018-03-23 16:11:02.3954|INFO|6|Processing Data Model Refresh: SessionId: 532cc6cd-d84a-4388-8531-35c884795c79, Status: Starting Data Refresh. 2018-03-23 16:11:02.4164|INFO|6|Processing Data Model Refresh: SessionId: 532cc6cd-d84a-4388-8531-35c884795c79, Status: Starting Retrieving PBIX AsDatabaseInfo. 2018-03-23 16:11:02.4503|INFO|6|Processing Data Model Refresh: SessionId: 532cc6cd-d84a-4388-8531-35c884795c79, Status: Starting Verifying all the data sources are embedded. 2018-03-23 16:11:02.4503|INFO|6|Processing Data Model Refresh: SessionId: 532cc6cd-d84a-4388-8531-35c884795c79, Status: Starting Verifying connection strings are valid. 2018-03-23 16:11:02.4582|INFO|6|Processing Data Model Refresh: SessionId: 532cc6cd-d84a-4388-8531-35c884795c79, Status: Starting Streaming model to Analysis Server. 2018-03-23 16:11:02.4860|ERROR|6|Error Processing Data Model Refresh: SessionId: 532cc6cd-d84a-4388-8531-35c884795c79, Status: Error Failed Streaming model to Analysis Server, Exception System.AggregateException: One or more errors occurred. ---> System.AggregateException: One or more errors occurred. ---> System.ArgumentNullException: Value cannot be null. Parameter name: s at System.IO.StringReader..ctor(String s) at System.Xml.Linq.XDocument.Parse(String text, LoadOptions options) at Microsoft.ReportingServices.CatalogAccess.PolicyAcessor.<GetItemPolicies>d__3.MoveNext() --- End of inner exception stack trace --- at System.Threading.Tasks.Task`1.GetResultCore(Boolean waitCompletionNotification) at Microsoft.PowerBI.ReportServer.WebHost.EventHandler.AnalysisServicesDataRefresh.<LoadDatabase>d__10.MoveNext() --- End of inner exception stack trace --- at System.Threading.Tasks.Task.ThrowIfExceptional(Boolean includeTaskCanceledExceptions) at System.Threading.Tasks.Task.Wait(Int32 millisecondsTimeout, CancellationToken cancellationToken) at Microsoft.PowerBI.ReportServer.WebHost.EventHandler.AnalysisServicesDataRefresh.LoadModel(AsDatabaseInfo asDatabaseInfo) at Microsoft.PowerBI.ReportServer.WebHost.EventHandler.DataRefreshScope.<>c__DisplayClass9.<ExecuteActionWithLogging>b__7() at Microsoft.PowerBI.ReportServer.WebHost.EventHandler.DataRefreshScope.<ExecuteFuncWithLogging>d__3`1.MoveNext() ---> (Inner Exception #0) System.AggregateException: One or more errors occurred. ---> System.ArgumentNullException: Value cannot be null. Parameter name: s at System.IO.StringReader..ctor(String s) at System.Xml.Linq.XDocument.Parse(String text, LoadOptions options) at Microsoft.ReportingServices.CatalogAccess.PolicyAcessor.<GetItemPolicies>d__3.MoveNext() --- End of inner exception stack trace --- at System.Threading.Tasks.Task`1.GetResultCore(Boolean waitCompletionNotification) at Microsoft.PowerBI.ReportServer.WebHost.EventHandler.AnalysisServicesDataRefresh.<LoadDatabase>d__10.MoveNext() ---> (Inner Exception #0) System.ArgumentNullException: Value cannot be null. Parameter name: s at System.IO.StringReader..ctor(String s) at System.Xml.Linq.XDocument.Parse(String text, LoadOptions options) at Microsoft.ReportingServices.CatalogAccess.PolicyAcessor.<GetItemPolicies>d__3.MoveNext()<--- <--- 2018-03-23 16:11:02.5050|ERROR|6|Error Processing Data Model Refresh: SessionId: 532cc6cd-d84a-4388-8531-35c884795c79, Status: Error Failed Data Refresh, Exception System.ArgumentNullException: Value cannot be null. Parameter name: s at Microsoft.PowerBI.ReportServer.WebHost.EventHandler.DataRefreshScope.ExecuteActionWithLogging(Action methodToExecute, String description, String localizedDescription, String messageInFailure, RefreshInfo refreshInfo, DataAccessors dataAccessors, ReportEventType operation, Boolean isDataRetrieval, Boolean showInExecutionLog, Boolean sendToTelemetry) at Microsoft.PowerBI.ReportServer.WebHost.EventHandler.AnalysisServicesDataRefresh.RefreshData(RefreshInfo refreshInfo) at Microsoft.PowerBI.ReportServer.WebHost.EventHandler.DataRefreshScope.<>c__DisplayClass9.<ExecuteActionWithLogging>b__7() at Microsoft.PowerBI.ReportServer.WebHost.EventHandler.DataRefreshScope.<ExecuteFuncWithLogging>d__3`1.MoveNext() I could run SQL trace and it looks like the issue is with calling SPs with wrong AuthType value exec GetCatalogItemProperties @CatalogItemID=N'54c2330e-9b69-4bdd-a3d0-8c6a18b15e1f' --PBI RS code makes this call exec GetPolicyByItemId @ItemId='54C2330E-9B69-4BDD-A3D0-8C6A18B15E1F',@AuthType=1 --I am assuming it should be making this call with @AuthType=3 exec GetPolicyByItemId @ItemId='54C2330E-9B69-4BDD-A3D0-8C6A18B15E1F',@AuthType=3
Creating a dataset on a Project Server based datasource
Hello, I am in the process of creating a number of dashboards based on MS Project Server data. First, I tried to build dashboards by having them connect directly to Project Server through the ODATA interface. This approach worked very well and proved easy to set up. I uploaded the reports at our on premises Report Server and users were able to access them. The only problem is that the data are embedded in the report(s), so to update the data one needs to open the actual report(s). This means that the update cannot be scheduled to happen automatically at offline hours which is particularly useful when a lot of data are involved. So I thought I should separate the data from the report(s) by taking advantage of the data source and dataset features of Report Server. I defined a data source of type XML pointing to our Project Server (connection string: "data source=https://xxx.xxx.com/sites/pwa/_api/ProjectData/Projects"). Pressing "Test Connection" reported back "ok" (although subsequent tests proved that almost any value entered there causes the same resonse). I then opened Report Builder and set as dataset the one I just created. Presumably because I specified an XML data source, I was presented with the text-based version of Report Builder where the user is expected to enter in the Query Designer window the actual query to be used. Does anybody know how to enter a valid query to read the XML data produced specifically by Project Server's ODATA interface? I assume it is some kind XML Query but I failed to find any examples or a simple tutorial. Furthermore, is this a good way to go about in order to create Project Server-based reports on an on premises Report Server (i.e. auto scheduled updates are very important) or should I employ some other approach? Thank you very much in advance.
odata refresh failed: Microsoft.OData.Edm.NetFX35.V7 not found
Hi, I'm testing Power BI Report server edition. I created a report connected to an OData source, I can consume it, I can setup the login / pwd in PBIRS successfully but the refresh failed with the error: File or Folder: Could not load file or assembly 'Microsoft.OData.Edm.NetFX35.V7, Version=7.4.0.11102, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. The system cannot find the file specified.. The exception was raised by the IDbCommand interface. [1] -1055784932: The command has been canceled.. The exception was raised by the IDbCommand interface. I'm using the january edition of PBIRS and this file is nowhere on my SSRS folders. What can I do?
