Alteryx has many features that distinguishes it from the other popular ETL products. The key differentiator though is the ability to host a virtual Data warehouse (DWH). It simply means that an Alteryx DB file (.yxdb) can hold up to 2 GB of data and act as a DWH without the need for a physical database to host the target DWH. This concept is very well suited to quickly set up small functional Data-marts enabling a data-driven decision support system. Alternatively, Alteryx has the capability to hold the logical layer and push the data from Data source to Tableau Server without any physical file or DB at all. This helps overcome the 2GB limit we have with the Alteryx DB file approach.
For very large, high volume and complex DWH, Alteryx can be used in the traditional manner as a fully functional and easy-to-use ETL tool.
Alteryx is Code Free, Flexible and Scalable. The intuitive user interface allows for Extract, Transform, Load (ETL) Tools within the Alteryx framework without the need for any programming. It is an easy to use self-service analytics platform that allows to work with multiple data sources and perform complex analytics, including predictive analytics, spatial, and statistical.
In a nutshell, Alteryx can be implemented quickly and as a comprehensive data analytics platform, it helps organizations in allowing analysts and data scientists to find answers to business problems faster than what can be accomplished through typical traditional approaches.
A case study below will help us deep dive and understand more about the highlights mentioned above.
Alteryx Implementation at a University
The university had the following Reporting requirements.
- Report 1 (Datamart 1) needs student details with their university rank/performance against all other universities
- Report 2 (Datamart 2) needs only Staff payroll and their projects
- Report 3 (Datamart 3) needs only Staff performance and their Student performance
The sources comprising relevant information included:
- ORACLE – Student Details
- File – All university performance
- MSSQL – Staff and their performance
Key reasons why Alteryx fits best include:
- Given the data volumes were below 2 GB for each of the Functional Data marts, no physical database was required for loading data into Datawarehouse database including MSSQL, ORACLE, POSTGRES
- There is seamless handshake between Alteryx and Tableau allowing for great visual stories to be created. Alteryx can control Tableau data source refresh which makes the process simple/smoother as compared to any other ETL tool.
- Single Schedule (No overlapping on data load). This means that Alteryx DWH and Tableau Reports need not be refreshed separately but only Alteryx refresh will do the needful due to handshake capabilities with Tableau.
- Easy to maintain
- Alteryx database file (.yxdb) for reusability / log
- Datawarehouse database license cost can be saved
Alteryx for Virtual DWH Approach
Oracle has information about Student details encompassing details like admission, fees, courses, department, etc., File has information about university performance, and MSSQL has information of Staff, Payroll, Projects, etc.
- Data mart 1 is an Alteryx virtual data mart built using ORACLE and FILE sources and pushed into the Tableau server as a data source
- Data mart 2 is an Alteryx virtual data mart built using MSSQL source and pushed into Tableau server as a data source
- Data mart 3 is an Alteryx virtual data mart built using ORACLE and MSSQL sources and pushed into Tableau server as a data source
To get a complete overview of Alteryx, let us explore the traditional approach with Alteryx and understand what it would look like.
Alteryx for Traditional Approach
ORACLE has information about Student details including admission, fees, courses, department, etc., File has information about university performance, and MSSQL has information covering Staff, Payroll, Projects, etc.
- Data will be loaded into centralised data warehouse using standard ETL process
- Al the three data marts are a view of data warehouse and they get pushed into Tableau server as respective data sources.
Virtual DWH Vs Traditional Approach
- Small analytical solution
- No database for target DWH storage thus making it cost effective
- Incremental process can get complex
- Works best with Tableau
- High performance for smaller Data sets
- Full blown warehouse solution
- Database to store the Datawarehouse data
- Incremental process is easy to apply
- Can be leveraged by any other tools for reporting/analysis
- Performance suited to high volume DWH implementations
Alteryx – Other Key Features
- View data in motion in terms of validating, quality, health as well as distribution of data enabled by robust inline visualizations
- Create interactive visuals in quick time wherein complex data can be shown via multi-layer charts as well as by using alluring features like customizable fonts and colours
- Give life to insights through interactive charts that also allow stakeholders to focus on critical insights that matter to them by drilling down into their analysis
- Leverage R-based analytics without having to do any coding by using drag-and-drop predictive tools, and in eliminating black box analytics
- Validating predictive model results using interactive visualization
- Leverage custom predictive tools and new packages for integrating direct Python or R coding into Alteryx workflow
- Make the most of location data that matters most by blending critical sources required for spatial analytics – from spatial-packaged data-options in Alteryx to big data etc.
- Leverage most-modern and intuitive spatial analytic tools enabling analysis via techniques including spatial matching, trade area, drive time and point creation
- Share outputs with visualization and mapping tools such as ESRI, tableau or MapInfo
- Create customer reports that can be represented through output formats such as ESRI, XML, Microsoft Excel, PDF, Qlik and Tableau
- Augment Qlikview and Tableau visualizations by reducing time needed for creating right datasets and leveraging advanced analytics for that of visualization