Author:
Jonathon McMillin
Role:
Senior BI Manager/Analyst
Overview of Use Case
The Customer Analytics department at an Australian financial services company was challenged with building and maintaining dashboards that provided, at a glance, a view of key performance indicators for top senior executives. Learn in this use case how Jonathon McMillin, Senior BI Manager/Analyst at the company, created an Alteryx app to provide a self-service tool that allows end users to extract data and answer their own questions.
Describe the business challenge or problem you needed to solve
Jonathon and his team wanted to decrease the number of requests required to build customized analyses and reports from departments across the company. He wanted non-technical users to be able to capture and build their own relevant metrics, rather than always having to rely on others. “I wanted to give the end user the ability to choose their aggregations, allowing our line of business analysts to ask additional questions of our data, without needing additional input from my team,” he said.
This financial service company used data from multiple sources such as Teradata, an internal SQL database, external Ad Hoc, and API calls that significantly slowed down the SQL server. Jonathon said that before Alteryx “it was really challenging to join all the data sources. We had to work with the Data Management team to get that external data into our SQL database. It would take months to bring the data into the SQL environment, and the more data we brought in the worse it was.”
Describe your working solution
Jonathon used the Tableau Super Store data to illustrate the steps below. This is how he described his process:
I took the tree input from the user, converted it to raw XML instructions to insert into the summarize tool on the final part of the workflow, and then returned the results.
Tree Input
The tree input interface tool allows the user to select fields and append aggregations as they desire. This interface tool provides the backbone allowing a rich user interaction by letting the users freely choose the structure of the data without my team needing to think of every possible aggregation grouping. Instead of manually creating the file for the tree input I used a macro to develop the tree structure.
Tree Build Macro
This takes the Field List Input and uses a base input to append aggregations to each field and ultimately outputs the Tree build structures as shown below.
Once the file was built, we can use it as the data source for the Tree Macro input:
To take the tree input and send it to the summarize tool, we had to edit the Raw XML of the summarize tool. Then, we needed to convert the tree tool output into the XML for the summarize tool. To do this, I had to use another macro.
Tree Output to Summarize XML
The tree output the keys with a line break between each value. So we split the keys into rows on the new line (\n) character with the Text to Columns tool. Then, using the tree.csv output from the tree macro, we built up the Field and Action instructions to feed the summarize tool. Last, we built individual XML instructions and concatenated all into one field.
Workflow Batch MacroWith all of the XML instructions built and with a batch macro we could send a data flow directly into an update tool using a Control Parameter tool. As we were only sending one record into the batch macro, it would only execute once.
We connected the Control Parameter to the summarize tool and instructed the Action tool to update the Raw XML, which then output the data either into the original app or a file.
Let’s see how it works! Let’s group by customer segment and sum profit:
Describe the benefits you have achieved
“We deployed our app at Alteryx Server and anybody in the business can apply to have access and can use it,” Jonathon said. His team achieved their goal to reduce the number of ad-hoc requests, and now people can answer their own questions. “The people on my team saved 50% to 70% of their time,” he added.
Jonathon celebrates that, now with Alteryx, “without the mundane data pulling we can take on more difficult questions. We got a lot more time, we can also have the time to manage and maintain different data sources.”
Related Resources
See workflow attached below.