cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lachlanP
Helper II
Helper II

Connecting to a large data table in Business Central

I am trying to load the Resource Capacity table from Business Central into PBI, in order to create some metrics around resource utilization.

 

The data in this table is stuctured in an awkward way, with one row for each day starting at 01/01/0001, for each resource. I'd like to filter to dates this year, but I'm getting an error whenever I try to filter this dataset:

lachlanP_0-1637184780059.png

 

It seems like any filters I try to apply creates a timeout error with the business central connection. 

 

Is there any way around this? I know Power BI can handle datasets of this size.

 

Thanks!

1 ACCEPTED SOLUTION

HI @lachlanP,

it sounds like you want to do some operation before getting data to power bi desktop side, right? if that is the case, I'd like to suggest you switch to different data connectors with query options. (e.g. OData connector) Then you can get data with the additional query filter to reduce the response data amounts.

Odata URL Conventions 

OData Web Services - Business Central | Microsoft Docs
Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

6 REPLIES 6
Stachu
Community Champion
Community Champion

does the 01/01/0001 correspond to an actual date? if yes, what would that date be? It seems that in general it should be possible to handle this date in M:
https://docs.microsoft.com/en-us/powerquery-m/sharpdate

but I would expect issues when loading to DAX



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

@Stachu , thanks for the reply.

 

Yes, it does correspond to an actual date and I can create a simple date filter in M:

 

lachlanP_0-1637192019161.png

 

The problem is when I apply the filter (press ok), the system loads and loads and then I get the error that the web service timed out

 

 

 

Stachu
Community Champion
Community Champion

so it really relates to the year one in the gregorian calendar? or is it a placeholder for null or there is some other logic behind it?



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

@Stachu ,

Yes, it relates to year one. I suppose it could be considered a placeholder. Basically this table is showing resource availability for every resource for every day (since the begining of time...ish). Of course none of this data is relevant or populated until recent dates, for me I only care about this year. The problem is that over 99.9% of this data (rows full of zeros) needs to get filtered out, but the connection breaks before I can do this. 

In Business Central the system processes this into a matrix and you perform queries within the system to get the info you need. I think I am going to need to write and publish a custom query to expose just the portion of this dataset that I care about.

HI @lachlanP,

it sounds like you want to do some operation before getting data to power bi desktop side, right? if that is the case, I'd like to suggest you switch to different data connectors with query options. (e.g. OData connector) Then you can get data with the additional query filter to reduce the response data amounts.

Odata URL Conventions 

OData Web Services - Business Central | Microsoft Docs
Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Microsoft themselves explicetly state in Business Central to consider using API request instead of OData. 
to the OP: have you tried to save a new report and using filtering rules in BC directly ? 
Under asssisted setup there as point in "Repirt on financial health" (don´t knwo why its not translated as I switched to english for this screenshot)

bco_lde_0-1666854072709.png

 



using this option lets you choose data sources in BC (pages and existing queries - so all you need is the page objct ID, afaik all page objects seem available for selection, even from 3rd party extensions ) , choose the fields to be included , then you can apply additional filters. 

bco_lde_1-1666854337612.png



this report gets automatically published under web services and thus can be used via the legacy business central data source connector

maybe that helps out?



Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.