2 Replies Latest reply on Feb 14, 2018 9:53 AM by Jeffrey Kritzman

    Tableau-ing Clarity!....?

    Benjamin Cole

      Hi Ya'll,


      ***Warning, I'm not quite sure what my question is****


      I've been working for a hospital here in California where my team and I use EHR data to guide clinicians to reduce variance in their practice to improve care and reduce cost.  Currently I import excel clarity extracts into Tableau to do my analysis.  I just realized that Clarity is Microsoft SQL Server, so I now am connected directly to Clarity via Tableau!  Its really fast considering how much data I'm working with.


      My question

      There are SQL analysts that support our team who will write 800 lines of SQL code to produce a single excel file that I then run descriptive statistics on.  Considering the complexity of healthcare data and the size of Clarity (20k tables) what is the greatest way to utilize my new found Clarity-Tableau connection.  I can drag, drop and join a handful of tables, but I doubt that I'll be able to exclude and include the exact data I need considering how nuanced even a single diagnosis is. 


      Can I input the 800 lines of SQL code from the analyst team directly into the "custom SQL" option in the tableau data frame?


      Maybe my real question -->  Is Tableau robust enough to actually navigate Clarity in a meaningful way?






        • 1. Re: Tableau-ing Clarity!....?

          Hi Benjamin,


          There are a few ways to do it depending on what you're going for.  You can use the custom SQL feature, but it's not ideal when it comes to long, complex SQL statements.  I've found it's more useful for small, simple SQL statements.  If the SQL is super complex you can have your SQL developers put the code into Views on SQL Server.  You'll probably want to create a new database for them, so the Views aren't on the production database.  A View is basically a Table comprised of a SQL statement.  They do not contain any data unless they're called (I think). 


          Views are what we use for 95% of our SQL Server data.  Stored Procedures can be used as well.  I tend to avoid those if possible, because of issues I've had connecting them to Tableau. 


          My recommendation would be to sit with your SQL devs and explain what you're trying to do given what Tableau is capable of. 


          Hope that helps.

          • 2. Re: Tableau-ing Clarity!....?
            Jeffrey Kritzman

            Hi Benjamin,


            I agree with Mark.  As someone who is also fairly new to connecting directly to Clarity through Tableau, I'll try to build on what he said.


            I've found that whether to use the actual tables or custom SQL statements depends on the job, but I generally use custom SQL.  There are some limitations to what type of SQL commands Tableau can handle (for instance, temp tables don't work).  You can get around this by getting creative and 'thinking like Tableau.'  Sometimes you need to break a complex SQL query into 2 or 3 (or more) separate queries, and join them together.  I generally try to minimize the number of custom SQL queries I use, just to make it more understandable / readable.


            One thing I've found is that the really long and complex SQL queries are generally doing things that don't need to be done in SQL.  Often, these SQL queries are doing lots of contortions to do aggregations and calculations that are very easy in Tableau.  I would definitely recommend sitting down with a SQL programmer in front of Tableau and working through this.  If possible, try to get a consistent contact.  After the first project or two, once they understand how to best use Tableau and SQL together, things get a lot smoother.