Power BI vs Tableau Reporting Comparison with SQL Server Data

By: Pablo Echeverria | Updated: 2023-12-12 | Comments | Related: > Power BI

Problem

In this tip, we connect to SQL Server using Power BI and Tableau and pull in data from the same table along with showing the queries that are run to grab the same data whether using Power BI vs. Tableau to show the difference of how these tools for retrieving data.

Solution

For this article we are using the following versions:

Power BI Desktop version 2.119.986.0 64-bit (July 2023)
Tableau Desktop version 2023.2.0 (20232.23.0611.2007)

Power BI

Either in the splash screen or in the "Start" menu, click on "Get data", then choose "SQL Server" and click "Connect":

You need to specify the server (in my case, a single dot for the local server), the database name (optional), and choose between "Import" (cache all data) and "DirectQuery" (use SQL Server engine). More information is provided here: Improve Power BI Performance with SQL Server Indexing.

If you expand the "Advanced options," you can specify the query timeout (in minutes) and an initial SQL command, which can be used to restrict access to data. See the following link for more information: Row-level security (RLS) with Power BI. When you click "OK," you need to choose between Windows Authentication, Database authentication, or Microsoft account authentication on the left pane; in my case, I chose "database," so I entered the database username and password and clicked "Connect":

Note if you don't use TLS, there will be a warning about using a non-encrypted connection, click "ok":

Then, you will see the database name and the available tables. When you need to check the ones to be used, you will get a preview of the rows in the table. Click "Load" to go directly to the report editor or "Transform data" to adjust the data before going to the report editor.

Queries Generated by Power BI

Below are the queries generated by Power BI within the SQL Server database:

SELECT @@version _VERSION
   ,CAST(SERVERPROPERTY('EngineEdition') AS VARCHAR(4)) _EDITION
   ,CASE 
      WHEN EXISTS (
            SELECT *
            FROM sys.extended_properties
            WHERE [name] = N'isSaaSMetadata'
               AND [value] = '1'
            )
         THEN 1
      ELSE 0
      END _IS_SAAS
   ,CASE 
      WHEN EXISTS (
            SELECT *
            FROM sys.types
            WHERE name = 'char'
               AND collation_name LIKE '%UTF8%'
            )
         THEN 1
      ELSE 0
      END _UTF8_COLLATION
SELECT t.[TABLE_CATALOG]
   ,t.[TABLE_SCHEMA]
   ,t.[TABLE_NAME]
   ,t.[TABLE_TYPE]
   ,tv.create_date [CREATED_DATE]
   ,tv.modify_date [MODIFIED_DATE]
   ,cast(e.value AS NVARCHAR(max)) [DESCRIPTION]
FROM [INFORMATION_SCHEMA].[TABLES] t
JOIN sys.schemas s ON s.name = t.[TABLE_SCHEMA]
JOIN sys.objects tv ON tv.name = t.[TABLE_NAME]
   AND tv.schema_id = s.schema_id
   AND tv.parent_object_id = 0
LEFT OUTER JOIN sys.extended_properties e ON tv.object_id = e.major_id
   AND e.minor_id = 0
   AND e.class = 1
   AND e.name = 'MS_Description'
WHERE 1 = 1
   AND 1 = 1
SELECT r.[ROUTINE_SCHEMA]
   ,r.[ROUTINE_NAME]
   ,r.[ROUTINE_TYPE]
   ,p.create_date [CREATED_DATE]
   ,p.modify_date [MODIFIED_DATE]
   ,cast(e.value AS NVARCHAR(max)) [DESCRIPTION]
FROM [INFORMATION_SCHEMA].[ROUTINES] r
JOIN sys.schemas s ON s.name = r.[ROUTINE_SCHEMA]
JOIN sys.objects p ON p.name = r.[ROUTINE_NAME]
   AND p.schema_id = s.schema_id
   AND p.parent_object_id = 0
LEFT OUTER JOIN sys.extended_properties e ON p.object_id = e.major_id
   AND e.minor_id = 0
   AND e.class = 1
   AND e.name = 'MS_Description'
WHERE 1 = 1
   AND 1 = 1
SELECT s.name [TABLE_SCHEMA]
   ,o.name [TABLE_NAME]
   ,c.name [COLUMN_NAME]
   ,cast(c.column_id AS BIGINT) [ORDINAL_POSITION]
   ,c.is_nullable [IS_NULLABLE]
   ,CASE 
      WHEN (
            t.is_user_defined = 0
            AND t.name IS NOT NULL
            )
         THEN t.name
      WHEN (
            c.system_type_id = 240
            OR t.name IS NULL
            )
         THEN 'udt'
      ELSE t_system.name
      END [DATA_TYPE]
   ,CASE 
      WHEN (
            c.system_type_id IN (
               59
               ,62
               )
            )
         THEN 2
      WHEN (
            c.system_type_id IN (
               48
               ,52
               ,56
               ,60
               ,104
               ,106
               ,108
               ,122
               ,127
               )
            )
         THEN 10
      ELSE NULL
      END [NUMERIC_PRECISION_RADIX]
   ,c.precision [NUMERIC_PRECISION]
   ,CASE 
      WHEN (
            c.system_type_id IN (
               59
               ,62
               )
            )
         THEN NULL
      ELSE c.scale
      END [NUMERIC_SCALE]
   ,CASE 
      WHEN (
            c.system_type_id IN (
               40
               ,41
               ,42
               ,43
               ,58
               ,61
               )
            )
         THEN c.scale
      ELSE NULL
      END [DATETIME_PRECISION]
   ,CASE 
      WHEN (
            c.system_type_id IN (
               231
               ,239
               )
            )
         THEN floor(c.max_length / 2)
      WHEN (
            c.system_type_id IN (
               165
               ,167
               ,173
               ,175
               )
            )
         THEN c.max_length
      ELSE NULL
      END [CHARACTER_MAXIMUM_LENGTH]
   ,cast(e.value AS NVARCHAR(max)) [DESCRIPTION]
   ,d.DEFINITION [COLUMN_DEFAULT]
   ,cc.DEFINITION [COLUMN_EXPRESSION]
   ,CASE 
      WHEN c.is_identity = 1
         OR c.is_computed = 1
         OR t.system_type_id = 189
         OR c.generated_always_type > 0
         THEN 0
      ELSE 1
      END [IS_WRITABLE]
   ,NULL FIELD_CAPTION
FROM sys.objects o
JOIN sys.schemas s ON s.schema_id = o.schema_id
JOIN sys.columns c ON o.object_id = c.object_id
LEFT JOIN sys.types t ON c.user_type_id = t.user_type_id
LEFT JOIN sys.types t_system ON t.system_type_id = t_system.user_type_id
LEFT JOIN sys.default_constraints d ON d.object_id = c.default_object_id
LEFT JOIN sys.computed_columns cc ON c.object_id = cc.object_id
   AND c.column_id = cc.column_id
LEFT JOIN sys.extended_properties e ON o.object_id = e.major_id
   AND c.column_id = e.minor_id
   AND e.class = 1
   AND e.name = 'MS_Description'
WHERE 1 = 1
SELECT convert(NVARCHAR, fk.object_id) [FK_NAME]
   ,cast(f.constraint_column_id AS BIGINT) [ORDINAL]
   ,s1.name [TABLE_SCHEMA_1]
   ,o1.name [TABLE_NAME_1]
   ,c1.name [PK_COLUMN_NAME_1]
   ,s2.name [TABLE_SCHEMA_2]
   ,o2.name [TABLE_NAME_2]
   ,c2.name [PK_COLUMN_NAME_2]
   ,f.constraint_object_id
   ,f.constraint_column_id
FROM sys.foreign_key_columns f
JOIN sys.foreign_keys fk ON f.constraint_object_id = fk.object_id
JOIN sys.objects o1 ON o1.object_id = f.parent_object_id
JOIN sys.schemas s1 ON s1.schema_id = o1.schema_id
JOIN sys.objects o2 ON o2.object_id = f.referenced_object_id
JOIN sys.schemas s2 ON s2.schema_id = o2.schema_id
JOIN sys.columns c1 ON c1.object_id = o1.object_id
   AND c1.column_id = f.parent_column_id
JOIN sys.columns c2 ON c2.object_id = o2.object_id
   AND c2.column_id = f.referenced_column_id
WHERE 1 = 1
UNION
SELECT convert(NVARCHAR, fk.object_id) [FK_NAME]
   ,cast(f.constraint_column_id AS BIGINT) [ORDINAL]
   ,s1.name [TABLE_SCHEMA_1]
   ,o1.name [TABLE_NAME_1]
   ,c1.name [PK_COLUMN_NAME_1]
   ,s2.name [TABLE_SCHEMA_2]
   ,o2.name [TABLE_NAME_2]
   ,c2.name [PK_COLUMN_NAME_2]
   ,f.constraint_object_id
   ,f.constraint_column_id
FROM sys.foreign_key_columns f
JOIN sys.foreign_keys fk ON f.constraint_object_id = fk.object_id
JOIN sys.objects o1 ON o1.object_id = f.parent_object_id
JOIN sys.schemas s1 ON s1.schema_id = o1.schema_id
JOIN sys.objects o2 ON o2.object_id = f.referenced_object_id
JOIN sys.schemas s2 ON s2.schema_id = o2.schema_id
JOIN sys.columns c1 ON c1.object_id = o1.object_id
   AND c1.column_id = f.parent_column_id
JOIN sys.columns c2 ON c2.object_id = o2.object_id
   AND c2.column_id = f.referenced_column_id
WHERE 1 = 1
ORDER BY f.constraint_object_id
   ,f.constraint_column_id
SELECT s.name [TABLE_SCHEMA]
   ,o.name [TABLE_NAME]
   ,i.name [INDEX_NAME]
   ,cc.name [COLUMN_NAME]
   ,cast(ic.key_ordinal AS BIGINT) [ORDINAL_POSITION]
   ,i.is_primary_key [PRIMARY_KEY]
FROM sys.objects o
JOIN sys.schemas s ON s.schema_id = o.schema_id
JOIN sys.indexes AS i ON i.object_id = o.object_id
JOIN sys.index_columns AS ic ON ic.object_id = i.object_id
   AND ic.index_id = i.index_id
JOIN sys.columns AS cc ON ic.column_id = cc.column_id
   AND ic.object_id = cc.object_id
WHERE (
      i.is_primary_key = 1
      OR i.is_unique_constraint = 1
      OR i.is_unique = 1
      )
   AND o.type IN (
      'U'
      ,'V'
      )
   AND ic.key_ordinal <> 0
   AND 1 = 1
ORDER BY i.name
   ,s.name
   ,o.name
SELECT TOP 200 [$Table].[MYKEY] AS [MYKEY]
   ,[$Table].[RANDOM_INT] AS [RANDOM_INT]
   ,[$Table].[RANDOM_FLOAT] AS [RANDOM_FLOAT]
FROM [dbo].[T1] AS [$Table]
SELECT TOP 1000 [$Table].[MYKEY] AS [MYKEY]
   ,[$Table].[RANDOM_INT] AS [RANDOM_INT]
   ,[$Table].[RANDOM_FLOAT] AS [RANDOM_FLOAT]
FROM [dbo].[T1] AS [$Table]
SELECT [$Table].[MYKEY] AS [MYKEY]
   ,[$Table].[RANDOM_INT] AS [RANDOM_INT]
   ,[$Table].[RANDOM_FLOAT] AS [RANDOM_FLOAT]
FROM [dbo].[T1] AS [$Table]

Tableau

Either in the splash screen or in the "Start" menu, under "Connect," click on "To a server," and select "SQL Server". Enter the server (in my case, a single dot for the local server), the database name, and the authentication type (Windows or SQL Authentication); in my case, for SQL Authentication, I enter the username and password and clicked on "Start session." Note: There is another tab for "Initial SQL," which can be used to restrict access to data. For more information, check out this link: Run Initial SQL.

Then you get a list of tables on the left, a visualization of the tables and relationships in the center, option to get data in real-time or extract the data on the top right, the columns at the bottom center and a preview of the data at the bottom right (click on "Update Now"):

Queries Generated by Tableau

Below are the queries generated by Tableau within the SQL Server database:

SELECT TOP 1 *
INTO [#Tableau_2_1_Connect_CheckSelectIntoCap]
FROM (
   SELECT 1 AS COL
   ) AS CHECKTEMP
INSERT INTO [#Tableau_2_2_Connect_CheckCreateTempTableCap] ([COL])
VALUES (@P1)
SELECT *
FROM [#Tableau_2_2_Connect_CheckCreateTempTableCap]
SELECT [name]
   ,SCHEMA_NAME([schema_id])
FROM [MYDB].[sys].[synonyms]
SELECT name
FROM [sys].[databases]
WHERE ISNULL(HAS_DBACCESS(name), 1) = 1
ORDER BY name
SELECT *
FROM [dbo].[T1]
SELECT COLUMN_NAME
   ,COLLATION_NAME
   ,TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'T1'
SELECT [R].[ROUTINE_SCHEMA]
   ,[R].[ROUTINE_NAME]
   ,[P].[ORDINAL_POSITION]
   ,[P].[IS_RESULT]
   ,[P].[PARAMETER_NAME]
   ,[P].[DATA_TYPE]
   ,[P].[PARAMETER_MODE]
   ,[P].[NUMERIC_PRECISION]
   ,[P].[NUMERIC_SCALE]
   ,[P].[NUMERIC_PRECISION_RADIX]
   ,[P].[DATETIME_PRECISION]
   ,[P].[CHARACTER_MAXIMUM_LENGTH]
FROM [MYDB].[INFORMATION_SCHEMA].[ROUTINES] AS [R]
LEFT OUTER JOIN [MYDB].[INFORMATION_SCHEMA].[PARAMETERS] AS [P] ON [P].[SPECIFIC_SCHEMA] = [R].[SPECIFIC_SCHEMA]
   AND [P].[SPECIFIC_NAME] = [R].[SPECIFIC_NAME]
WHERE [R].[ROUTINE_TYPE] = 'PROCEDURE'
   AND [R].[ROUTINE_SCHEMA] <> 'SYS'
ORDER BY [R].[ROUTINE_SCHEMA]
   ,[R].[ROUTINE_NAME]
   ,[P].ORDINAL_POSITION

Conclusion

As you can see, Tableau has fewer steps and is more straightforward. The data is presented in a single screen, has fewer SQL statements, and retrieves data only once. Power BI gets the first 200 rows, then the first 1000 rows, and finally all the rows. But being a Microsoft product, Power BI gets more metadata related to the underlying storage.

Next Steps

Check out these related articles:

Learn more about Power BI in this 3 hour training course.

Click here to start the Power BI course

About the author

Pablo Echeverria is a talented database administrator and C#.Net software developer since 2006. Pablo wrote the book "Hands-on data virtualization with Polybase". He is also talented at tuning long-running queries in Oracle and SQL Server, reducing the execution time to milliseconds and the resource usage up to 10%. He loves learning and connecting new technologies providing expert-level insight as well as being proficient with scripting languages like PowerShell and bash. You can find several Oracle-related tips in his LinkedIn profile.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2023-12-12

Building Reports with Power BI vs Tableau from SQL Server Data