You’ve decided to delve into the wide world of data analytics. Or maybe you’re considering switching tools for your next project. As part of your due diligence, you’ve examined what visualizations you can produce, the dashboarding capabilities, the performance, the ease of use, and of course, the cost.
But…did you consider data wrangling? Also known as data preparation or data transformation, data wrangling includes data cleansing and data modeliing. While it might not have the most star appeal, careful data preparation is an important part of successful data analysis. You will want to consider how easy or challenging it will be with your new tool to get your data into a report-ready state.
In this blog post, we compare the data wrangling capabilities of three leading tools: Power BI, Cognos and Tableau.
Why data wrangling is crucial
Much of the data we collect contains some level of noise. In other words, information that is meaningless to the business or is in a format the system can’t read or contains too much variation to be efficiently used. Wrangling data helps normalize noisy data, cleaning, enriching and consolidating it so that it is ready to use and report upon. Data that is accurate, timely and relevant to business needs drives the business and enables informed decisions.
Noisy data is everywhere
Even the best systems present data issues. There are many reasons for “dirty” data. For example, data can be in the wrong format (i.e. a year field saved as a varchar instead of a number). Or you can have denormalized data across multiple sources that has to be brought together for reporting. And if those different sources are at different granularity levels – e.g. your inventory system captures data weekly but your sales data does so daily – those variances need to be reconciled before reporting can happen.
Sometimes data issues might be the result of plain old human error: Typos in data plague everyone regardless of how accurate we try to be. Lastly, the issue might just be outlier data that needs to be removed or handled.
No matter the reason, the ways for wrangling data are handled differently with Power BI, Cognos and Tableau.
Watch the on-demand webinar on Data Prep: Power BI vs. Cognos vs. Tableau
Data preparation in Power BI using Power Query Editor
Power BI offers a robust data preparation solution in Power Query Editor. In addition to many built-in functions, Power Query Editor (PQE) offers the ability to leverage M. A powerful scripting language, M lets users create complex advanced functions that help ensure data is ready for reporting.
Built into PQE is the ability to merge together queries, append data from other queries, use rows as headers and much more. It also lets you keep or remove rows which can be very helpful when trying to ensure your queries are folding properly. (See our on-demand webinar on query folding for more on that topic.)
The Transform section of PQE includes functionality including grouping, the ability to create new columns, textual based functions, date and time calculations, and even pivot/unpivot of columns. If that’s not enough, you can take your existing R and Python scripts, drop them right in, and run them from PQE!
But it doesn’t end there. Learning to leverage and work with M allows you to even write your own custom SQL or use the M functions to get the data where and how you need it.
Takeaways: All in all, Power BI + Power Query provides full suite of features you will need to make sure your data is ready for reporting.
Prepping data using Tableau Prep
Not to be outdone, Tableau offers its powerful Tableau Prep for wrangling data. Unlike PQE, which is built into Power BI, Tableau Prep is a standalone product. It is included in the Creator license (as of the writing of this blog) and is available to download from the Tableau website.
Tableau Prep allows for many of the same capabilities as PQE including merging queries, appending, built-in cleaning functionality for working with text or numeric fields, and much more.
Here we see some data combined in a single field in a source file:
Tableau Prep can easily split this apart into multiple usable fields for reporting with a single click!
Beyond that, Tableau Prep offers a simple, easy-to-understand graphical interface that may be less intimidating to non-technical users. Through a straightforward drag and drop process with zero coding, users can create joins and unions easily.
As with PQE, pivot/unpivot, scripting, aggregation and filtering are all built right in.
Takeaways: For many, Tableau Prep’s friendly UI is the biggest draw. There may be less ramp up for users in getting data ready to go. While it doesn’t leverage M or have the same programmatic capabilities as Power BI Query Editor, it offers a much quicker turnaround time.
Using Cognos Framework Manager and Cognos data modules for data wrangling
Cognos Analytics has two distinct products for data preparation: Framework Manager and data modules.
Cognos Framework Manager focuses on the modeling side of things. It has been an integral part of the Cognos solution for a very long time and makes modeling data a snap. Since many folks deal with transactional sources, it is where FM shines. Using query flattening, custom SQL (when needed), macros and join creation, Framework Manager lets you turn that complex transactional schema into a ready-to-use star schema for packages and report creation.
Where FM has a challenge is on the data cleansing side. Actions like removing rows or splitting columns can be more of a challenge and users may need to write complex functions to handle these more routine tasks.
Takeaways: Cognos Framework Manager is an excellent data wrangling tool for professional report authors and data modelers, but not ideal for less technical analysts.
Data modules was introduced to Cognos Analytics and offers a second way for users to do self-service data wrangling. You’ll find many clean functions here, like splitting, merging, joins, etc. And similar to Tableau Prep, the data modules interface incorporates a drag and drop manner of handling things.
Takeaways: Like Tableau Prep, Cognos data modules is much easier for non-technical folks. Users can quickly do things like upload XLS files, join them together, create unions, clean the fields, and take it right into Cognos reporting or dashboarding. It can even be modeled and prepped for use in Explorations or Stories.
Comparing data wrangling capabilities in Power BI, Tableau and Cognos
All three tools provide a solution to handle data preparation including data cleansing and data modeling. The chart below summarizes what each of the three tools can do for you and your organization.
When narrowing down to the final decision, think about the types of data you are working with and who your users will be. For example
- If your data is transactional and the bulk of the work is schema modeling, then Cognos Framework Manager might be the best choice.
- If you need the flexibility of writing your own SQL or programmatic changes, then Power BI Query Editor shines the brightest.
- If you’re giving this task to analysts and other business users, the more graphical interface of Tableau Prep will help them achieve goals faster.
Bottom line: None of the three products will let you down. The choice can sometimes boil down to nothing more than personal preference or company directive.
You can see live demos of the data wrangling capabilities of these three products and get a more in-depth analysis in our on-demand webinar.
No matter which one you end up with, Senturus can help your company learn how to get the most out of your tools through training, mentoring and consulting. Give us a shout if you want to discuss any of the three more in depth. We’re happy to review your unique situation and make a recommendation based on your data and goals.
