Global Restaurant Chain uses Alteryx + Snowflake to get insights on its PowerBI Environment
Customer's Challenge
A global restaurant chain was looking for a way to better understand how its business teams were using their Power BI environment. Having access to this information would better inform the Power BI team as they proposed future efficiency-building business solutions. They had previously developed some Python code to extract data, but it was hard to maintain and adjust as few people knew how to change the code.
phData's Solution
The phData Analytics team created an Alteryx workflow to extract data from the Power BI environment using several API calls. This workflow can show existing data within the environment, such as datasets, dataflows, pipelines, refreshables, activities, etc.
Results
By developing the solution, phData’s team generated helpful insights for the Power BI team by enabling them to quickly get the usage data. They then built a dashboard on top of it to better understand and visualize the data. Now that the client understands how—and at what frequency—they can better manage costs and more precisely propose improved efficiencies or alternate workflows.
The Full Story
The global restaurant company had a high usage Power BI Environment and wanted to better understand what people were doing inside of it. As the workload increased, they wanted to check which users were actively developing and maintaining dashboards and which teams were using those dashboards.
They developed a Python code to extract some of the data, which worked for some time. However, the code was hard to maintain and update, and only a few extractions were being done.
As the company already used Alteryx for other areas, the Power BI team asked the phData Analytics team if Alteryx could extract data from Power BI and then connect to the Snowflake Data Cloud to save the data in tables that they could use to build a dashboard on top of.
phData devised a plan to build automation, improve performance, and further expand the data being extracted into the tables required for the Power BI usage dashboard.
Why phData?
phData has a celebrated track record of helping the client succeed within the Alteryx platform on several past projects. They knew they would be in good hands once phData presented a solution and offered a speedy timeline.
Building the Solution
Alteryx was used to extract some of the API Calls, and a notebook in Azure Synapses (Fabric) was created to extract some DMV (Dynamic Management View) Queries from workspaces.
The Alteryx workflow was split into two parts, one for all of the main API calls with no limitations and a second focusing on high-importance workspaces that could hit some API limitations if not pre-filtered.
The initial workflow extracts a list of Activity Events, Capacities, Groups, Datasets, Reports, Apps, Dataflows, Refreshables, and Pipelines. Groups were listed twice because the company had more than 5000+ groups, so the API had to be split because of some limitations.
Authentication was done in a very small Python code by using a tenant, client, and client_secret. It was possible to use an API call to do it, but the Python code was easier to create and connect.
For every API, a URL is made with the token obtained from the Python code; and a batch macro downloads, parses, and saves the data in Snowflake. Table names are dynamically updated with the name of the API Call. All calls have their tables overwritten daily, except the Activity Events one, which is appended to keep track of daily changes happening in the PowerBI Environment.
With this first step complete, another workflow is executed to further extract more data. This second one needs to wait until the first is done because it will use the updated data from some calls to extract: App users, Datasets Datasources, Pipelines Users, Groups Users, Datasets Users, Reports Users, Dataflows Users, Users Subscriptions, and Users Artifacts.
Most of those calls have a limit of a certain number of rows per hour. To work around this, a filter is added to only extract data from high-importance workspaces. The downloaded data is parsed and saved in Snowflake similarly to the previous step.
In total, Alteryx extracts 17 API calls and saves them to Snowflake. To further improve the understanding of the Power BI environment, a notebook in Fabric was created using Python code to extract some DMV queries.
The queries executed obtain data for TMSCHEMA columns, tables, table row counts, table parameters, partitions, dependencies, measures, and relationships. Those are directly saved in the Synapse lakehouse, where Power BI can directly connect to read data.
Take the next step
with phData.
Learn how phData can help solve your most challenging data analytics and machine learning problems.
