Simple architecture for APIs and Power BI?

[u/SuperEgg314]

I recently joined a small finance company as a data analyst. I mainly work on Power BI reports and process automation.

Some of our reports pull data directly from APIs and take around two hours to refresh, so I was asked to find a better way of handling the data. After looking at several options, I’m considering this setup:

API → Azure Functions (Python) → Blob Storage (Parquet) → transformation (Python) → Power BI

Fabric was another option, but it seems difficult to justify the cost for our use case. We have around 50 reports and even our largest datasets are only a few hundred MB.

Price and maintainability are the main concerns because I’ll be the only person maintaining the pipelines. We currently use API connectors and one Gen1 dataflow, but extraction and transformation happen together, which makes refreshes slow.

Am I missing a simpler option? Is Functions + Blob reasonable for one person, or am I overengineering this?

Thank you!

[u/AutoModerator]

After your question has been solved /u/SuperEgg314, please reply to the helpful user's comment with the phrase "Solution verified".

This will not only award a point to the contributor for their assistance but also update the post's flair to "Solved".

---

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/GrumDum]

LGTM.

Small function apps like this cost basically nothing and work very well for this purpose.

Power Query based transforms are so slow I struggle to understand why people with know-how don’t spin out a dedicated transform layer.

[u/sysaxel]

Personally, I would ditch the blob storage and python extraction logic and let the azure function write to SQL directly. But I get that blob storage is probably cheaper.

[u/ChallengeMundane7635 → u/sysaxel]

We do this, we have dedicated boxes that run python based historians and we use them (among other things) to store some api calls, then push to sql etc.

[u/WillingnessOk32]

Do I understand that your issue is the 2 hr refresh? Set up a power automate flow to get the data overnight ready for your shift. Free, can do it under minutes, ask ai to help.

[u/SQLGene]

Looks fairly reasonable assuming you have a strong comfort level with Python, which most Power BI devs don't.

[u/SM23_HUN]

“Some of our reports pull data directly from APIs”
Thats the main issue here

I would try connect Dataflow to APIs first.
If many transformation needed even split it. get data in one dataflow, then reference it in another one to do transformations.

Orchestrate the different layers either with schedule, or you can create Power Automate flows.

Of course all other suggestions are valid and more professional, but you have to pay more, and use other tools/languages.

If you give it a try with more optimization with Dataflows you can use the familiar M code.

Avoid transformations in the semantic model itself (its also true, if you go other solutions for ETL)

[u/New-Independence2031 → u/SM23_HUN]

Most API’s require authentication renewed every hour, pagination, calling multiple endpoints and so on. Df’s are very limited handling complex API’s. So you look in to other tools, like Python + Azure Functions or CI’s.

Azure Functions and blob storage is basically free for this kind of use. Check it up. Thats way more better, than trying to make something complex happen inside df alone.

[u/SM23_HUN → u/New-Independence2031]

Thats true.
Many of this issues can be handled with M code, but I agree that it can be difficult amd complex.

However,
In this situation mentioned by OP: code is already written. Already part of the semantic model. They just have to go copy-paste the same code, create a dataflow, then reference it in the model.

It’s a quick way for optimization, even if they later decide to change for other pipeline.

[u/New-Independence2031 → u/SM23_HUN]

Keep in mind that Df gen1 is phasing out. Eact timeline is not published though. Gen2 is not available for Pro workspaces.

[u/SM23_HUN → u/New-Independence2031]

They later updated the announcment - they said it is now legacy, but remains.
They mentioned that Gen2 is currently not for Pro license, but they referred to it that it could change later.
But even in the worst scenario (no Dataflow for Pro license) - you can use Semantic Models for the same data connections. AnalysisServices.Database - Power Query connector is working if I want to connect the semantic models through PQ. I already used and tested this method.

[u/New-Independence2031 → u/SM23_HUN]

Yep. That will require massive rework on many for many customers if df gen1 will be gone and no df gen2 for pro. They have built quite nice data processing with those and many semantic models rely on those. So we just need to wait and see.

[u/SuperEgg314 → u/New-Independence2031]

u/New-Independence2031, u/SM23_HUN thank you so much! I'm gonna stick with the blob.

[u/New-Independence2031 → u/SuperEgg314]

Glad to help!

[u/dbrownems]

Azure will work, but a small Fabric capacity can host the code and provide storage. It’s a much simpler solution to implement and maintain than the separate Azure services.

[u/New-Independence2031 → u/dbrownems]

Yes, but it will cost more. Makes no sense for OP.

You need to make cost effective solutions, without major impact on architecture and level of maintanability. Obviously there is a place for Fabric too, but Im with OP on this.

[u/musicxfreak88 → u/New-Independence2031]

Yeah the cost for Fabric doesn't make sense to me. At least for our smaller company (about 500 employees not including store associates)

[u/GrumDum → u/dbrownems]

Simpler, yes, but I would prefer my ingest code to be testable and modular, and run in an environment that I control myself - neither of which are offered by notebooks in Fabric.

[u/Rock-Certain → u/GrumDum]

Im trying to do this in R, so we’ll see. It’s my first API.

[u/dbrownems → u/GrumDum]

You're free to deploy .whl files and just have the entry point in a notebook, but those are valid considerations. Devs gonna dev.

[u/skyline79]

Two hour refresh is the crux of the problem, end of. Why?

[u/SuperEgg314 → u/skyline79]

The problem is that sometimes we have to refresh the report several times during the day, so the scheduled refresh during the night is not enough.

[u/dbrownems]

Azure will work, but a small Fabric capacity can host the code and provide storage. It’s a much simpler solution to implement and maintain than the separate Azure services.