How to consolidate your multiple WorkflowMax accounts into a single cohesive reporting strategy
This article is part of our BI walkthrough series: reading time 4 minutes (approx)
The existing reporting tools for WorkflowMax are numerous and comprehensive. But if you're running multiple WorkflowMax accounts and need to consolidate data across your entire organization, things get a little more difficult.
In this walkthrough, we'll use SyncHub to take you through the process of syncing your various accounts to a single location, then aggregating your data to build a comprehensive view across your entire business.
Head over to your Connection Dashboard in SyncHub and follow the instructions to connect your first WorkflowMax account, which will take just a few mouseclicks and a couple of minutes. As part of the connection process, you're asked to provide details of the database where you'd like us to sync your data. We currently support Snowflake, BigQuery, Redshift, Postgres and SQL Server/Azure. And don't worry, if you don't have your own database, we provide a free dedicated SQL Azure database on all our pricing plans.
Once completed, SyncHub creates a unique database schema inside your data store, then immediately starts syncing your WorkflowMax data from the cloud. This results in various database tables that look something like this:
| Name | ClientUUID | Budget | StartDate | DueDate |
|---|---|---|---|---|
| Project X | 20c79894-3ffb-4249-9ba1-099ea3f83b17 | 34,000.00 | 2024-09-08 00:00:00 | 2025-09-08 00:00:00 |
| General maintenance | b1fa3d35-c8ea-4940-a982-91e7bd2d040b | NULL | 2024-05-09 00:00:00 | 2024-06-09 00:00:00 |
| Remedial | d3567773-f2d6-493c-8985-eabb8eb43f94 | 20,000.00 | 2024-12-12 00:00:00 | 2025-03-12 00:00:00 |
Now, follow a similar process to connect your other WorkflowMax accounts. SyncHub will store the data in the same database, but in different database schemas. This gives you the best of both worlds, where your data is demarcated at the connection level, but you can access everything from within a single query.
Now that you have your data in your database, we need a way to merge it into a single consolidated view. This is where our Insights platform comes into play. Using Insights, we can write a simple SQL query that merges the data from all of our schemas. Because each schema has an identical format, this is trivial:
select 'WorkflowMax 1' as [ConnectionName], [Name], [ClientUUID], [Budget], [StartDate], [DueDate]
from schema_workflowmax_1.JobDetails
UNION
select 'WorkflowMax 2' as [ConnectionName], [Name], [ClientUUID], [Budget], [StartDate], [DueDate]
from schema_workflowmax_2.JobDetails
UNION
select 'WorkflowMax 3' as [ConnectionName], [Name], [ClientUUID], [Budget], [StartDate], [DueDate]
from schema_workflowmax_3.JobDetails
Note: We're using SQL Server syntax here, but you'll need to tweak it a little depending on your database type
Insights stores your query results in a database table under the sh_report_cache schema alongside the raw data we've downloaded from WorkflowMax. You can call this table whatever you like, so let's use something self-explanatory like workflowmax_jobdetails_consolidated. We can then query it from our reporting tool as simply as this:
select * from sh_report_cache.workflowmax_jobdetails_consolidated
As you've seen, SyncHub's consistent data structure and background syncing makes it trivial to report from multiple WorkflowMax. You can use new your consolidated database table directly from your reporting tool, or use our own Insights platform to build charts and dashboards from within SyncHub itself.
SyncHub offers a fully functional free trial, so feel free to try out this walkthrough for yourself, using your own data.
Happy reporting everybody.