SQL Server Stored Procedures in Tableau – Part 4 – Behind the scenes

This series of posts attempt to look at the pros and cons of using Stored Procedures as data access method for Tableau reports. The points made here are valid as of Tableau 8.2.

Part 1 gives a general introduction and identifies areas where some of SProcs advantages become irrelevant when it comes to Tableau

Part 2 looks at metadata considerations

Part 3 looks at Execution Plan considerations

Part 4 looks at how Tableau uses stored procedures to answer different queries

Behind the scenes look

Tableau has a more involved process when getting data from a stored procedure compared to a ad-hoc query it generates from the tables and relations present in it’s Data Source.

Continue reading

Advertisements

SQL Server Stored Procedures in Tableau – Part 3 – Execution Plan

This series of posts attempt to look at the pros and cons of using Stored Procedures as data access method for Tableau reports. The points made here are valid as of Tableau 8.2.

Part 1 gives a general introduction and identifies areas where some of SProcs advantages become irrelevant when it comes to Tableau

Part 2 looks at metadata considerations

Part 3 looks at Execution Plan considerations

Part 4 looks at how Tableau uses stored procedures to answer different queries

Execution Plan Considerations

T-SQL is a declarative language; i.e. the language only asks for what is wanted and doesn’t specify how it should be done. In a typical RDBMS, this task of how to do it is left to the Query Optimizer. The Optimizer looks at the various indexes, number of rows present, server’s hardware configuration among other information and prepares a plan on how to do the task at hand.

Continue reading

SQL Server Stored Procedures in Tableau – Part 1

This series of posts attempt to look at the pros and cons of using Stored Procedures as data access method for Tableau reports. The points made here are valid as of Tableau 8.2.

Part 1 gives a general introduction and identifies areas where some of SProcs advantages become irrelevant when it comes to Tableau

Part 2 looks at metadata considerations

Part 3 looks at Execution Plan considerations

Part 4 looks at how Tableau uses stored procedures to answer different queries

Stored Procedures are an important part of typical database development. In a typical web application or desktop application, the application interacts with the database through of stored procedures. It is recommended to use stored procedures rather embedding direct DML or SELECT sql statements in the application code. For a database developer involved in Business Intelligence development, how much of a benefit do Stored Procedures offer in developing dashboards with a tool like Tableau (or any typical self-service BI tool) ?

Continue reading

Connecting Tableau to SQL Server

A Tableau Data Connection to a SQL Server relational database can be made by using either Windows Authentication or SQL Server Authentication. After creating the Data Connection, the Data Source can be published to Tableau Server. While publishing, the Data Source can be configured to use a specific Authentication. Here is a list ofchoices.

http://kb.tableausoftware.com/articles/knowledgebase/tableau-server-database-logins

The choices made while creating the Data Connection and publishing the Data Source determine how logging into and reading data from a SQL Server database behaves when a workbook deployed to Tableau Server is opened.

The choice made during creating the Data Connection determines which SQL Server login is used to make connection and log into the database engine. Whereas, the Authentication choice made during publishing the Data Source to Tableau Server determines the security context under which the query is run.

Continue reading