Hey Guys,
I have this amazingly weird Power BI solution that I created, and it is fully dynamic API-based refresh with Dynamic M paramets in source query for ( RLS)
TLDR
I set up a Power BI embedding solution that updates M parameters and does a full refresh on every view. It’s clever but slow, and now I have problems with this — one customer’s data at a time. Looking for something better with my requirements of speed, security, isolation, and cost.
The Situation:
We are budgeting /forecasting company , Users update budgets or forecasts in our system and need to see those changes reflected in an embedded Power BI report almost immediately. A scheduled refresh was not sufficient .
What I currently do:
Whenever someone wants to re-open a dashboard,
I update M parameters based on the most recent details,
Then I do a full dataset refresh, and then generate the dashboard embed token to use.
I’ve been led to this approach via ChatGPT and never got any real feedback on it. I even asked Microsoft support for help, but they Suggested to ask Community .
I'm not an expert, but I see many problems with this:
It takes a lot of time — for a big dataset, sometimes even 1 minute or more.
I have another external API delay process on top of that, which makes things worse. That’s a major concern.
Only one dataset exists - if refresh fails, clients can see each other's data
Also, there is only one dataset meaning only one forecast company data at a time and If refresh is not succesful then come person may end up seeing others old data .
My Concerns:
Doing a full dataset refresh each time is not good. Someone suggested I try incremental refresh, but I thought it wouldn’t work in my case since there is no time dimension and I thought because it recalculates entirely each time
Recently I read in the Kimball book and in it that for budgets, you ideally just store the item transaction that has changed or altered — and only they need to be updated.
My system is not optimized this way, but I think I can find some way to add that policy.
Even so, the issue remains: the newer dataset gets wiped first. That's not ideal. If there’s a way I could retain the old and then push the current forecast into a new label, and if that same is used, then update via that — and if it doesn’t exist, then I create new and keep the most updated one — that would reduce time as well.
This way, for the same forecast, it won’t rerun each time. Only then can I apply my incremental refresh logic.
Other Things I Need:
The last requirement is that I want to give edit access to the dashboard in my external app So they can put their own logos etc and Customize things a bit . I don’t understand this much either, but I think that would only be possible if I create some level of isolation — maybe a separate workspace for each client — and then have it in there.
I just want someone who has dealt with something similar to please guide me:
Am I even on the right path?
Should I consider things like Push Datasets, Hybrid models, etc.?
I don’t use Direct Query because it’s way too limited, and I can’t deal with all those restrictions.
So please suggest what could be done ideally in a situation like this. Any documentation, guidance, architecture reference, or keywords I should research further would be really appreciated. I feel lost and want to start reviewing and optimizing this approach properly and know the next steps .
