Streamline Workflows with Automatic Data Prep

Written by Jordan Zenko on 15 June 2017.

The process of preparing data to be used for visual data analysis is one of the most important steps for any BI project, and is often the most time consuming and difficult, especially if you are not that familiar with technical details such as inner joins or correlations formulas. Before you can transform your data into stunning dashboards, multi-page reports and visual data analytics, you must in fact, not only connect to data but actually make sure your data is properly organized. This is why Dundas BI enables automatic data prep, in order to provide not only technical users with a streamlined workflow, which ultimately reduces the amount of steps required to progress from data, to desired visual. Automatic data prep allows users to be proactive, and focus on the data they need, and from there, easily enrich and share it.

Dundas BI has been designed to adapt to different users’ skill levels and needs. Specifically, when it comes to data prep workflows, Dundas BI provides a tailored data prep experience, with tiered complexity. For example, business analysts who are not that well versed into the database structures will enjoy the automatic data prep that “just works” for data manipulations such as inner joins and formula calculations. This experience allows the quick creation of a base model that can serve their data discovery or dashboard creation process. At the same time, BI professionals are offered a full visual data prep designer with many more advanced data prep capabilities often required in order to create enterprise level data models. The beauty of it all, is that BI professionals can easily leverage base models created by others and further tune and expand upon it so then other analysts can feed off a standardized model. This means that Dundas BI provides analysts and BI professionals with the right tools to create their data analysis and feed off each other in order to turn those into beautiful reports with ease.

There are 3 primary instances in which automatic data prep is used within Dundas BI:

Auto Detection of Relationships,

Auto Detection of Non-Natural Hierarchies, and

Auto Generated Formulas Calculation for Advanced Visuals

Auto Detection of Relationships

Also known as data-blending, auto detection of relationships is the process of combining data from multiple sources into one functional dataset. Users of Dundas BI can drag-and-drop data from different sources onto the same visuals and Dundas BI will automatically find the fields to join, in order to create relationships. Dundas BI will accomplish this feat without analysts even being aware that a behind-the-scenes join was performed. What’s particularly nice about this auto detection, is that once a user has blended data sources together on the visual data exploration layer, the model created by the system can then be promoted to the visual data prep layer where the join can be customized even further. In addition to configuring the join type (Inner, Left, Right or Full), the user can further enrich the model with supplementary transformations as well as with metadata descriptions to make the model easier to use across all other analysts.

To further enhance the data prep experience, Dundas BI auto detects non-natural hierarchies (i.e. product or organization level hierarchies vs. just date based hierarchies). The power of hierarchies is in that it allows any user to quickly drill up and down through the different hierarchy’s levels to see the data aggregated accordingly. For example, starting with a bar chart showing order quantities at the product category level and then being able to drill down on the largest category to see the breakdown at the product level.

To auto prep the hierarchies, Dundas BI allows users to simply drag a dataset to the canvas. At that point, Dundas BI looks at existing relationships between that dataset and others in the database, and constructs multi-level hierarchies. If the relationships are not defined in the database, Dundas BI will use internal algorithms to define the best hierarchy for the provided dataset.

The fun doesn’t stop there. Even though a hierarchy may be auto-generated, the option to further customize it still exists. For example, you can change the name of the hierarchy or hierarchy level or add/delete levels as needed.

Auto Generated Formulas Calculation Required for Advanced Visuals

With Dundas BI, users are able to drag-and-drop simple data (such as measures and dimensions), onto a dashboard, and the system will allow them to generate advanced visuals. One such example is a box plot, including all the calculations for it (percentiles, means, averages, etc.), which can be generated in a single click, via Dundas BI’s formula visualization to summarize with a box plot. When this feature is selected, as the user drags-and-drops data onto their visual, the system will calculate the elements needed to create the distribution that the box plot requires. This is a simple case that traditionally would require many steps with other BI tools.

To see this functionality in action, take a look at the video below. Dundas BI’s auto data prep has allowed me to create a box plot in 30 seconds, with zero data prep.

Other such advanced formula based visualizations that can be created in a single click include Pareto Charts, Histograms, correlation matrices, Parallel Coordinates Charts, and forecasts.

Now, we all know that data prep isn’t as exciting as visual discovery, but what it is however, is important. There can be no stunning dashboards, no seamless user experience, and no powerful data visualizations without solid data prep.

And while data prep is traditionally onerous and labor-intensive, Dundas BI’s automatic data prep capabilities simplify the process in an unprecedented manner, and ensures data is ready to be transformed into meaningful visuals with minimal work on the user’s part.