How a global retailer optimized their operation performance with Alteryx & Tableau
CLIENT
A Global Retailer
AIM
Increase the brand’s presence online by providing an eCommerce solution that offers a collection service for ordered goods.
BACKGROUND
During the COVID-19 pandemic, the service was proving popular, but a specific market was interested in how to optimize the operations of their service. Data was being collected from various data sources and prepared using Alteryx workflows scheduled on an on-premise Alteryx Server. The data was imported into a Microsoft SQL Database, where further processing was made using stored procedures to create material views for reporting.
Technical expertise was required to manage the database and create the stored procedures; however, it was considered essential to deal with large amounts of data quickly and to be able to provide daily reporting to Senior Management.
The Senior Managers from the market concerned had specific goals and objectives, and consequently their own interpretation of data provided in the Views in the database. Tableau was used to read the data from the database and present mainly in tabular form to management.
As time progressed, some of the Tableau reports would be working with over 500 Mbytes of data which proved impractical for a remote connection to the Tableau server, and also quite slow to respond when accessing reports on-premise.
As the reports were prepared every morning, the Tableau server was scheduled to create Extracts of the data from the database before the start of the working day, which greatly improved the performance on-premise and also made it possible for people to work on reports remotely using Tableau desktop (provided the extracts created were a suitable size).
WHAT DID CONTINUUM DO?
As we, at Continuum were working remotely, it was essential that we set up appropriate extracts of data sources before building reports, as there wasn’t any documentation available to describe the data. We found it was particularly useful to be able to access the database directly using MS SQL Manager, which could show the relationships between tables and the quantities of data being used.
We appreciated the speed of response when making queries using MS SQL to the database, taking seconds rather than hours, as with some of the cases using Tableau. Using Tableau desktop it was possible to make relationships between database tables, for example to link Orders made with how they were processed within a Store. In other cases, it was possible to blend data sources, where Tableau prompted how they could be combined. We found that it was most reliable to make the relationships, but bearing in mind that these were made with a good understanding of the underlying database structure.
Using Tableau to present the results in tabular format, often to later be exported to Excel did not seem to be the best way of exploiting the powerful visualization capabilities of Tableau, but in most cases, this is what was required.
Calculated Fields were needed to aggregate metrics on a daily, weekly, and monthly basis to present in the format required. It was essential to ensure that the aggregation was made at the correct level of detail (LOD), which needed to be explicitly defined in the calculation. For example, if counting the Orders per Customer, fixing the distinct count of customers {FIXED [Customer ID]:COUNTD([Order ID]) }
Such calculations are relatively complicated for the uninitiated, much more so than when the calculations are handled automatically by Tableau.
Calculations are saved within each Table Workbook. So if you are working with multiple Workbooks there are some challenges in keeping the Calculations synchronized if changes are made. For example, if a field is changed on the database the related Calculations will need to be updated in each sheet.
We found a very useful blog post describing how to read a Tableau TWB file into Alteryx, and process the data to show details of the Fields within each Workbook. With a few tweaks, we created a workflow to extract the Calculated Fields from each of my Workbooks, which was useful for the creation of a Data Dictionary and seeing which Workbooks would need updating if a database field was altered.
CONCLUSION
On reflection, completing the first phase of this project, we believe it’s better to keep the majority of the data manipulation to the back-end processes either with MS SQL Stored Procedures or within the Alteryx Workflows (our preference to start with). It’s possible to create the Extract files directly from Alteryx (using the Output tool to write Tableau “.hyper” files to the Tableau Server). If indeed the Senior Management only want to view the reports in Excel it would also be possible to Output the Excel sheets directly from Alteryx. Having shared our thoughts on this, it will be interesting to see what Phase 2 will have in store!