Synapse Serverless SQL & Power BI — Having Fun With PIVOT!

Check how the latest improvements to Synapse Serverless SQL pool can help you create one of the most popular Power BI transformations in a more optimal way!

Image for post
Image for post
https://unsplash.com/photos/r7zjJ63kAPU

Scenario

#1 CSV File with no additional transformations in Serverless SQL pool

CREATE VIEW [dbo].[taxi201901csvStart] AS
SELECT
VendorID
,cast(tpep_pickup_datetime as DATE) tpep_pickup_datetime
,total_amount totalAmount
FROM
    OPENROWSET(
        BULK 'https://<Your ADLS>/nikolaiadlsfilesys/Data/yellow_tripdata_2019-01.csv',
        FORMAT = 'CSV',
        PARSER_VERSION='2.0',
HEADER_ROW = TRUE
)  WITH(
VendorID INT,
tpep_pickup_datetime DATETIME2,
total_amount DECIMAL(10,2)
)
as baseQuery
GO
Image for post
Image for post
Image by author
Image for post
Image for post
Image by author

#2 CSV file with aggregated data within Serverless SQL pool

CREATE VIEW [dbo].[taxi201901csvAggNormal] AS
SELECT
VendorID
,cast(tpep_pickup_datetime as DATE) tpep_pickup_datetime
,SUM(total_amount) totalAmount
FROM
    OPENROWSET(
        BULK 'https://<your ADLS>/nikolaiadlsfilesys/Data/yellow_tripdata_2019-01.csv',
        FORMAT = 'CSV',
        PARSER_VERSION='2.0',
HEADER_ROW = TRUE
)  WITH(
VendorID INT,
tpep_pickup_datetime DATETIME2,
total_amount DECIMAL(10,2)
)
as baseQuery
GROUP BY VendorID
,cast(tpep_pickup_datetime as DATE)
GO
Image for post
Image for post
Image by author

#3 CSV File with pivoted data within Serverless SQL pool

CREATE VIEW [dbo].[taxi201901csvAggPivot] AS
with baseQuery as (
SELECT
VendorID
,cast(tpep_pickup_datetime as DATE) tpep_pickup_datetime
,SUM(total_amount) totalAmount
FROM
    OPENROWSET(
        BULK 'https://<Your ADLS>/nikolaiadlsfilesys/Data/yellow_tripdata_2019-01.csv',
        FORMAT = 'CSV',
        PARSER_VERSION='2.0',
HEADER_ROW = TRUE
)  WITH(
VendorID INT,
tpep_pickup_datetime DATETIME2,
total_amount DECIMAL(10,2)
)
as baseQuery
GROUP BY VendorID
,cast(tpep_pickup_datetime as DATE)
)

select tpep_pickup_datetime AS pickupDate
,[1] AS Vendor1
,[2] AS Vendor2
,[4] AS Vendor4
from baseQuery
PIVOT (
SUM (totalAmount) FOR VendorID IN ( [1], [2], [4])
) AS Vendors
GO
Image for post
Image for post
Image by author

#4 Parquet File with no additional transformations in Serverless SQL pool

CREATE VIEW [dbo].[taxi20190102parquetStart] AS
SELECT
VendorID
,CAST(TpepPickupDatetime AS DATE) TpepPickupDatetime
,TotalAmount TotalAmount
FROM
OPENROWSET(
BULK 'puYear=*/puMonth=*/*.snappy.parquet',
DATA_SOURCE = 'YellowTaxi',
FORMAT='PARQUET'
)nyc
WHERE
nyc.filepath(1) = 2019
AND nyc.filepath(2) IN (1,2)
AND tpepPickupDateTime BETWEEN CAST('1/1/2019' AS datetime) AND CAST('2/28/2019' AS datetime)
GO
Image for post
Image for post
Image by author
Image for post
Image for post
Image by author
Image for post
Image for post
Image by author
Image for post
Image for post
Image by author
Image for post
Image for post
Image by author

Conclusion

Data Mozart — Don’t let data make noise, create music from it | data-mozart.com | https://www.linkedin.com/in/nikola-ilic-data-mozart/

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store