Power Platform Integration Patterns: Dataflows for Simplicity, Azure for Power 🧬
- Jeremy Quittschreiber
- Apr 15
- 6 min read
Power Apps! Its everywhere! It might even be in our dreams soon if we are not careful!
But when discussing Power Apps, there are hundreds of other mini conversations we all begin to have, what database, what automation source, LICENSING! Oh the pain that one brings. But there is a much older, much less straightforward answer that we all must know, decide or otherwise understand more about before we can ever go down the road of any of these topics. And those items ultimately come down to 3 specific questions. 1.) What is my business use case?
2.) How much data am looking at and what do I need to do with it?
3.) Who are my users and what are their expectations?
These questions ultimately guide all of your solution architects decisions on where to guide the developers and database administrators and other team mates in the joint mission of delivering a system that is cost effective, manageable and most importantly solves the business problem presented! Lets break these down:
What is my use case?
For this example lets use a rose business as here at Nerdy Q we just love the simple natural beauty of them and they, like everything else do have a distribution change that we can use to solve a problem.
In this above example the company is using an Azure Data Lake and Oracle with excel sheets as their primary sources of information storage to date, it is affordable for just dumping out large amounts of data as a company when your not running active servers and just storing large amounts of invoices and other storage items but is not friendly to query or really manage those orders over time to create beautiful dashboards or metrics. This presents the problem, accessible data. This data is cheap to store but with it not being easy to track its causing roses to spoil in transit before getting to customers, is affecting delivery times and generally costing the company hundreds of thousands of dollars per year. For this they would like to use the Power Platform. They would like to call this new system the Smart Rose Allocation System or SRAS.
🧩 The Problem
The sales teams need real-time visibility into:
What rose varieties are available, in transit, or about to spoil
Which varieties are best suited for a given event or retailer
What customers typically buy together (e.g. red + white combos for weddings)
Tracking supplier performance and quality grading
The problem? This data lives in Oracle, Azure Data Lake, Excel sheets, and greenhouse sensors, with no centralized visibility. And when the marketing team asked IT for “just a quick Power App to help match roses to clients,” they underestimated the data chaos behind that request.
🧠 The Use Case: Smart Rose Allocation System (SRAS)
The solution is a Power Platform-driven system that:
Connects to multiple data sources: Oracle (inventory), spreadsheets (customer notes), IoT (temperature sensors), Azure Data Lake and CRM.
Cleanses and enriches the data using Azure Synapse to join, filter, and categorize only viable rose stock based on freshness and location.
Pushes summarized data into Dataverse to surface the ideal roses for each order through a sleek Canvas App for the sales team.
Uses Power Automate to:
Trigger alerts if rose freshness drops below thresholds
Notify logistics for reallocation
Sync status with Dynamics 365
💬 The “What’s My Use Case?” Question Answered
"We need a way to make sense of massive, messy, relational and non-relational data and serve just the relevant pieces to different user types in near real-time—without forcing them to learn our schema."
🌿 Why This Use Case Works for Architecture Education
This is not just about roses. It’s about:
Large, complex, relational data needing simplification for humans
A clear need to separate the data engine (Azure) from the user layer (Power Apps)
A blend of batch (dataflows) and reactive (flows) integrations
Building a user experience that doesn't punish users for normalized schema design
That’s it. That’s your use case.
Roses, like data, bloom beautifully when treated right. But if you don’t filter out the thorns, things get painful fast.
🌹 "How Much Data Am I Looking At, and What Do I Need to Do With It?"
Step 1: Quantify the Data
Let’s break it down:
Daily Inventory Updates from Oracle: ~500,000 rows across dozens of varieties and growing regions
Sensor Logs from Greenhouses: ~10,000–30,000 entries/day (temperature, humidity, spoilage predictions)
Customer Preference Data (CRM + Excel): ~5,000 unique clients with custom floral needs and notes
Supplier & Shipment Logs: ~1,000 updates/day with timestamps, delays, and incident reports store directly in the Data Lake
So in total? You’re working with millions of rows monthly, from multiple, relational, and non-relational time-sensitive sources.
🔍 Step 2: Ask: What does the USER actually NEED in Power Platform?
Here’s the trap many teams fall into: trying to bring everything into Dataverse.
In our rose story, you don’t need:
All the greenhouse IoT logs
All supplier shipment records
Historical inventory going back to 2015
You only need a filtered, focused slice:
What’s fresh
What’s available right now
What fits customer preferences
That’s maybe 5–10% of the data. Which means…
🎯 Step 3: The Right Architecture Filters the Noise
🚫 What Not to Do:
Shove raw Oracle data directly into Dataverse via Dataflows
Normalize 18 tables and expect a Model-driven app to magically be fast
Store IoT logs in Power Platform “just in case”
✅ What to Do:
Use Azure Synapse to pre-process and aggregate only the needed roses
Run freshness scoring, predicted bloom windows, and sort by location
Push only the "gold" data—say, ~20K records—to Dataverse nightly
Surface this in a Canvas app that lets sales search by color, freshness, or season
🧠 Answer:
You’re looking at large data, but you only need small data at the UX layer.
And that means:
Use Azure/Databrick for volume, filtering & enrichment
Use Dataverse for just-in-time display and interaction
Use Power Apps to craft the user experience—without performance pain
Power Platform is the boutique flower shop. Azure is the industrial greenhouse and logistics network.
Knowing how much you’re handling—and what users actually NEED—helps you build something that’s both beautiful and fast.
💡 The Missing Question: “Who Are My Users and What Do They Expect?”
Here’s the truth: the technology stack is secondary to user context. If you’re architecting without knowing who's touching what, how often, and WHY, then you're optimizing for the WRONG problem.
🧭 Question 1: Who Are My Users and What Do They Expect?
Let’s break it down:
Order Clerks
Need fast, filtered lists of active orders, stock availability, and auto-suggestions for matching varieties.
Expect: speed, simplicity, and a predictable flow.
Model-driven App with the new Power Apps grid control, preview panels, child tables? Perfect.
Note: Even better embed agents and co-pilot, discussed another day but the important lesson, even these tools are not as effective if this step isn't well planned!
Sales Managers
Need to pivot between customers, past behavior, supplier quality, and rose trends.
Expect: context-rich dashboards and drill-throughs.
A Canvas App over curated Dataverse data or embedded in a Power BI report might be the better experience.
Data Analysts / Ops
Need access to the big picture: freshness trends, supplier delays, spoilage heatmaps, revenue projections.
Expect: speed, scale, and flexibility.
Let them skip Dataverse entirely. Connect directly to Azure SQL, Synapse, or even Oracle via Power BI or a Canvas App with direct connector.
🧭 Question 2: Canvas or Model-Driven? Or Both?
Here’s the key: it’s not a binary decision. It’s role-based.
User Role | App Type | Reason |
Front-line Clerks | Model-driven | Leverage structured data, automation rules, and new grid UX |
Managers | Canvas or Embedded in Power BI | Optimized experience, data visualization, tailored UI |
Analysts | Power BI + Direct Connect or Canvas | No movement, full depth, direct querying of live data |
Want to go big? Use nested Canvas apps inside your model-driven forms or Power BI dashboards. This gives you dynamic UI with just-in-time data, without having to rebuild everything.
🧭 Question 3: Does It Even Need to Be in Dataverse?
Here's your architectural crossroads.
Option A: Full Dataverse Experience
Needed for automation, business rules, security roles, RLS
Great for structured workflows and when multiple teams interact
Option B: Direct Connect to Azure SQL or Oracle
No unnecessary data duplication
Lower latency for real-time dashboards
Great for read-heavy roles (managers, analysts)
Option C: Hybrid with Licensing (Premium)
Frontline apps work from curated 7-day Dataverse views
Advanced users connect directly to SQL/Oracle
Canvas Apps, Power BI, and Flows bridge the layers
🌿 Real-World Scenario End:
Our Rose company CRM now only keeps:
7-day active sales
Open/pending orders
Enriched, filtered rose stock relevant for the week
Model-driven apps show a clean, fast interface for clerks. Canvas apps inside Power BI dashboards let execs explore trends live against Synapse. Power Automate flows handle alerts on spoilage. And not a single unnecessary record clogs up Dataverse.
🧠 Final Thought for Architects:
If you design for the data, you get a database. If you design for the user, you get a solution.
Your job isn’t to move all the data.It’s to move just enough of the right data to the right place for the right person and that is the power of the Power Platform when just a little pause is taken to really figure out the problem and who your solving for rather than just diving head first into that drag and drop functionality!
Comments