Some of my best friends are IT professionals. As a data analyst, I know how important infrastructure is. I’ve got nothing but respect for data architecture, security, and governance. But business landscapes evolve quickly. It can be tough for IT to keep up with user needs. What happens when data demands change faster than you can remodel your data estate? That’s exactly the situation one of my clients found themselves in recently.
A few years ago, I wrote a blog about how you can’t let imperfect data thwart your business intelligence efforts. A closely related corollary: You can’t let imperfect data architecture thwart your BI either. Who you gonna call when you need a data set yesterday, it doesn’t exist in the data warehouse (or the data warehouse doesn’t exist), and you can’t manually pull a report fast enough? Automation to the rescue!
The Problem
My client had a nice, manageable set of energy production plants. It was easy enough to track productivity numbers. Every month, I would pull data from 3 different systems and use it to update several tables and Excel workbooks. To weave compelling data stories, I would then manually create and update PowerPoints to share with company decision-makers. It wasn’t the most efficient process, but given the organization’s structure and operations, it was good enough… Until a merger tripled the number of plants and data. That’s when data velocity needed to increase exponentially, fast.
“Who you gonna call when you need a data set yesterday, it doesn’t exist in the data warehouse (or the data warehouse doesn’t exist), and you can’t manually pull a report fast enough? Automation to the rescue!”
An internal IT team got to work building a data warehouse, but that’s a big job requiring strategy and time to execute. In the interim, I had zero chance of keeping up using the old manual system, and the company couldn’t just take a vacation from data-informed decision making until their data warehouse was operational.
Power Platform to the People
Using Microsoft Power Platform, I devised a system for meeting data analysis needs in the client’s new, much-expanded universe while their data warehouse is under construction. The first step of that system, automating daily data collection, involves a personal workflow automation. What starts as a bot in my Outlook inbox goes on to meet the data needs of an entire company. It illustrates the power of the citizen developer movement: business users creating low-code/no-code solutions that empower their whole department to work smarter.
As you can see in the process flow diagram, we needed to bring updated data from 3 sources into Power BI daily. One source was easily connected via Azure Analysis Services. We used Python to call on the API of another. For the third, I put my citizen developer skills to work. Using Power Automate, I set up a trigger event to initiate a sequence of automated actions. I scheduled a report to be sent from the data source to my email every night. When my Outlook sees the report, it downloads the new data tables, merges them into the existing behemoth tables, and sends the updated files back up to SharePoint. All while I sleep peacefully.
Lest you think this is only possible through the technological wizardry of data analysts, let me assure you: If you can use MS Office, you can build an automated workflow. Power Automate offers thousands of pre-built business process templates to customize and hundreds of connectors to all your favorite apps.
Power BI Makes Data Flow
Once we have updated data coming in daily, the flowchart action moves from Power Automate to Power BI Dataflows. Dataflows brings Power Query capabilities into your browser. You can prep and transform data online. The behind-the-scenes execution is powered by and happening in the cloud.
Now, remember, the Power Automate workflow I built is doing a big chunk of the extract, transform, load (ETL) work for the one data source it’s pulling from. Dataflows handles the remaining ETL for that source and the other two. Every dataflow is a folder of tables, and Power BI stores all that new data in Azure Data Lake Storage Gen 2 by default.
“Wait. Did my ADLS Gen2 account just become my de facto data warehouse?” I hear you asking.
There are important differences between a data lake and a data warehouse. You could say they each have a different theoretical framework for how they view and store data. Guru99 points out that a data warehouse does ETL, and a data lake does ELT. The latter casts a wide net, while the former has already determined which data is important and how your company is going to use it.
Guy in a Cube made an entertaining video on this whole topic. Very special guest and data analyst Matthew Roche explains how you can use Power BI dataflows to meet new self-service data needs and plug data warehouse holes. He and Patrick both agree dataflows should not replace your data warehouse (or give you an excuse to avoid building it). That said, this is one band-aid your IT team will actually thank you for. Every Power BI Dataflows entity is defined by a Power Query query, which makes it easy to incorporate into the data estate IT is building. If that went completely over your head, suffice it to say: All the stuff happening behind the scenes of your dataflow meshes nicely with what the tech folks have going on.
Power BI Datasets: A Single Source of Truth
Once Power BI dataflows has transformed the data, it can then feed Power BI Datasets. In this particular case, our dataflows feed into a master “golden” dataset, which is the data model for all the reports our data analytics team pulls. This single source ensures data consistency across all the analysis we conduct.
The components of your dataflows become the building blocks of your datasets. You can reuse tables and ETL steps across any number of datasets in your environment. There’s no need to reinvent the wheel; just recombine what you already have to get the analysis you need.
And there you have it! What started out as a personal workflow automation – a scheduled report delivered to my inbox every night – has now transformed into a central, accessible, up-to-date data source the whole team can use. The entire process, end-to-end, is housed in the Microsoft stack. And the best part? A lot less time spent on manual data pulls and manipulation.
Automate to Ascend
Mind Over Machine’s concept of Workforce Ascension℠ may seem ambiguous in the abstract, but this project produced concrete results for me, professionally. Automation allowed us to meet self-service data needs easier and faster. That time and energy savings enabled me to do higher caliber, more impactful work.
I still devote the same amount of time to this client each month. However, prior to automating their data collection, much of my time was spent assembling and maintaining reports. Now, I’m using it to build new tools and conduct more meaningful data analysis. My work is more valuable to the client. It is enabling their workforce to be more successful. Whether you call that a virtuous cycle or Workforce Ascension℠ & Enhancement, it’s the WAE forward.
About Fadi
Fadi Zureick was a tactile kid growing up in Columbia, MD. Legos and K’Nex were the only toys for him. Between his fascination with how different objects interact and both his parents being engineers, Fadi had no choice but to earn a degree in mechanical engineering from the University of Maryland. “I love starting from scratch and building something. It’s super gratifying to see what develops over time.”
The transition from mechanical engineering to data analytics platform development came when Fadi was working in the solar energy industry. He was drawn to BI’s capacity for creativity, seeing the whole picture and finding ways to maximize operational efficiency. “A fresh pair of eyes is the most valuable asset I can bring to a client, helping them find answers to questions they haven’t even thought of yet.”
When he’s not making the data tell a story, Fadi is a bit of a Renaissance man, equally comfortable playing soccer and the piano. And now he’s added another skill to his repertoire: parenting. His teething 1-year-old daughter makes up for the sleep she steals with her astounding cuteness, palling around with Charlie the big yellow lab and using baby signs to satiate her endless appetite for reading: “More books, please!”
