/ 3 min read / Using Holistics, Data at Work

Custom SQL with Tableau? No Problems

by Cedric Chin

Custom SQL with Tableau? No Problems

It's common knowledge amongst Tableau users that custom SQL queries bode bad news for Tableau. To quote Tableau themselves:

Custom SQL in the data connection can impact performance because Tableau issues the query to the database inside of a subquery.

But sometimes you're in a hurry. So, say that you make a request to your database people and they hand you a custom SQL query to stick into Tableau for one of your workbooks. You know this is a bad idea but you give it a try anyway ... and lo-and-behold, the workbook takes more than five minutes to open!

Tableau's recommendation is to use the custom SQL statement to create a 'materialized view' in the database, and then connect Tableau to that view. Think of the view as a temporary database table, constructed on the fly from the SQL query within the database itself.

The problem with this, of course, is that you're expected to have access to your analytical database. In some organizations, this simply isn't acceptable — you want some ability to do access control with the analysts in your org. More often that not, this forces you to rely on the good graces of the people in your database development department.

But what if you didn't have to do it this way? What if you had one tool that would allow you to create materialized views, provide access control, and handle all the other transformations you need to use Tableau like a champ?

Enter Holistics

With Holistics, creating a materialized view for Tableau is trivially easy.

Holistics works by connecting to and performing operations on top of your data warehouse. It's designed in this way in order to reduce the number of data tools your team has to purchase in order to do data work.

Because transformations are done within the warehouse, writing the custom SQL to create a materialized view in Holistics is a trivial thing to do.

In Holistics, this happens at what is called a data modeling layer. Data modeling is the process of using business logic to transform raw data, in order to produce 'modeled' data that is simpler for querying. (You can read more about that here.)

In Holistics, a data model is the core abstraction on which all other data operations are built. Conceptually, this is like creating a materialized view.

You can create a new model using custom SQL:

Or you can even create a new model using a graphical interface, if your materialized view is a simple combination of two or more database tables:

Once you do so, you may choose to persist this model into a new table in your database. This persisted table will be the database table you'll connect Tableau to.

Using Holistics's data modeling layer gives you a number of powerful benefits. You'll be able to annotate the fields of your newly created view. You'll also be able to see the entire data lineage in the same interface — that is, the original database tables from which your materialized view is built from.

View the data lineage for the new materialized view you've just built.

All in, Holistics gives you one location to manage all your Tableau data preparation needs. Some of our happiest customers use Holistics as an all-in-one data preparation tool upstream of Tableau. They document their data within Holistics, turn custom SQL into persisted tables that are kept updated by Holistics, and use Holistics as the one source of truth for their data operations and transformations.

Tableau may be the most powerful data visualization suite on the planet, but we've designed Holistics from the ground up to be the best possible companion to Tableau in the market.

Schedule a demo call and assess if we can add value to your data workflows!

Book a demo today →

Holistics superhero illustration
Cedric Chin

Cedric Chin

Staff writer at Holistics. Enjoys Python, coffee, green tea, and cats. I'd love to talk to you about the future of business intelligence!

Read More

Confused about the complex landscape of data analytics?

Read Book

"I'm shocked to be telling you: I read a free ebook from a company and actually loved it." - Mark, Data Engineer @wefox