Hey Everyone,
I've got an app used for marketing purposes that shows a members spend in store along with all kinds of other details. All-in-all the solution works beautifully and the client is thrilled...except for one little issue, loading the member screen takes 15-20 minutes.
I'm taking their Member List and their Member Sales Logs (each product for 1 sale listed as a separate line items) and adding up all their Order Totals based on the unique order ID, pretty slick actually, if it didn't take so long that is!
I need to keep updating their files daily so manipulating the data sources is not ideal, instead I'm using flow to update their lists with new items only, easy flows that run daily with the help of PAD so there is no manual effort for them to maintain it. They have about 1200 members and close to 10,000 rows of data in the sales history at this point, which will just keep growing, so I need to fix this before they really start complaining!
Here's the little piece of logic that takes forever to run, any ideas how I could do this another way or just more efficiently?
Should I do this with flow, or is it time I get my head around DataVerse maybe with relationships being the answer? - I know there are options I just want to make sure I don't waste time doing this the wrong way.
ClearCollect(
MemberList1,
AddColumns(
MemberList,
"Spend",
Value(
Sum(
GroupBy(
Filter(
'Member Log Lakeview',
Title = MemberList[@Title]
),
"field_OrderNumber",
"field_OrderTotal",
"Total"
),
field_OrderTotal
)
),"IsSelected",false
)
)
It looks like something of a heavy aggregation logic.
I recommend that you do not put such a heavy kind of aggregation logic directly in Canvas App itself. This could explain why it is taking very long to load. Instead you should place this aggregation logic somewhere else such as in Power BI (which is better suited for this kind of thing) which is tied to a field either in Dataverse or perhaps more simply, some single field of some single row somewhere in SharePoint (perhaps via Dataflow), updated at some interval. In Power BI Pro you can update at least once a day automatically, and you can actually schedule it for more often than that (there is a limit, I believe of about 48 per day, but that might mean it can be done more than once a day) and then the Canvas App is pulling only that field. Then it is probably going to load almost instantly I think.
If you want not to set up Dataflow and/or not to use Power BI at all, you could attempt set up aggregation in Dataverse, but to be honest my personal preference would probably be Power BI for this sort of thing. Using Power BI Dataflow to then tie it to some simple field - even as simple as SharePoint - to just put the value in there so it can be fetched easily - then after that the app should load really much faster then.
Check if the above overall idea might help you.