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:
- Comparing Power BI vs Tableau as Data Visualization Tools
- Power BI for Business Intelligence
- SQL Server Power BI tips, tutorials, webinars and more
Pablo Echeverria has worked for more than 10 years as a software programmer and analyst, during which time I studied parallel programming and became a senior programmer specialist. Afterward, he switched to a DBA position implementing new processes and creating better monitoring tools, while growing his data scientist skills to improve my customer’s businesses. Check out Pablo’s most recent book, “Hands-on Data Virtualization with Polybase“. This book brings exciting coverage on establishing and managing data virtualization using Polybase. It teaches how to configure Polybase on almost all relational and nonrelational databases, to setup a test environment for any tool or software instantly without any hassle, and to rapidly design and build high performing data warehousing solutions.
- MSSQLTips Awards: Rising Star (50+ tips) – 2024 | Author Contender – 2018, 2022, 2023 | Rookie Contender – 2017
