3 Replies Latest reply on Oct 28, 2016 10:12 PM by Dmitry Chirkov

    Live MS SQL Server Connection Optimization

    anton burtsev

      Hello,

       

      I'm trying to use live connection to MS SQL Server. And I see that Tableau generates inefficient SQL that executes minutes instead of seconds.

      For example:

      If I use measure table expenditures (region_id, ..., costs) and dimension table regions(region_id, region_name), Tableau generates the following query:

       

      select sum(costs), regions.region_name from expenditures join regions ... group by regions.region_name;

       

      However, I expect

       

      select sum(costs), regions.region_name from (

            select sum(costs) costs, regions.region_id from expenditures ... group by region_id

      )t

      join regions ...

      group by regions.region_name;

       

      I don't want to put region_name inside measure table since in real life there not only region name, but complete region hierarchy with names, codes and classifications. And there are also some other dimensions with similar structure.

       

      Also, if I calculate distinct count - I need a bit another pattern to sppedup aggregation.

       

      Is it possible to

      1. tune SQL generation somehow?
      2. create custom live connector? But I want to utilize data schema editor that I have for MS SQL Server connector.

       

      Thanks!