Quick tip: SQL Server Management Studio 19 supports AAD service principal authentication

SQL Server Management Studio 19.x and higher now allows you to login to SQL using Azure Active Directory application ids and secrets - nice improvement and a reason for me to upgrade.

Visualize S&P 500 data in Power BI using Azure Synapse Serverless SQL Pool

In Explore and analyze stock ticker data in Azure data lake with Azure Synapse serverless SQL Pool, I showed you can download stock ticker data from Yahoo Finance, stored it in Azure Data Lake and retrieve the data using standard T-SQL in Azure Synapse Studio. In this post, I will show how easy it is to consume the data from Synapse SQL Serverless using Power BI.

For the standard visual with the evolution of the S&P 500 closing price, I connected directly on SP500 external table in the Synapse SQL. You can connect to Synapse SQL Serverless using either the Azure SQL Database or Azure Synapse Analytics SQL connector and you will need to enter the Serverless SQL endpoint which looks something like this <yoursynapse>-ondemand.sql.azuresynapse.net

With the second reported I want to visualize the S&P 500 yearly return and the average return since December 1927. To make it easier, I created a separate view on top of the external table which calculates the yearly returns

As you see from the visual, returns can vary quite a lot both on the negative side as well as on the positive side - for the last 20 years, there was a huge drop in 2008 (-38%) and also this year is not looking great (-22%), but 2013, 2019 and 2021 all had returns above 20%. On average across the S&P 500 returned 7% (not included dividends).

For the last visual in the Power BI report, I wanted to show a histogram with the S&P 500 yearly returns. I based myself on Power BI Histogram example using DAX since  Power BI does not have a standard histogram and I did not want to use a custom visual ( I used Power BI custom visuals from Pragmatic Works in the past)

Equity returns roughly follow a normal distribution or "bell curve", meaning that most values cluster near the central peak and values farther from the average are less common.  Stock returns however have fat tails - meaning that the occurrences on the extremes are far more common than expected in a normal distribution.  The Greate Depression (1931) and the Global Financial Crisis (2008) led to two of the largest stock market losses of the S&P 500. With a loss between -20% and -30% this year, we are in the same category/bin as 1930, 1974 and 2002.

You can download the synapsestockdemo.pbix file and the benchmark.csv file from my Power BI repo on GitHub

References:

• Tutorial: Use serverless SQL pool with Power BI  Desktop & create a report
• Tutorial: Explore and analyze data lakes with serverless SQL pool
• Power BI connectors to Azure Synapse

Getting started with Azure Synapse Analytics on demand webinars

 Mid December 2020, Microsoft organized some webinars to get Microsoft partners up to speed with Azure Synapse Analytics. All these webinars are now available on demand on the Microsoft events page - just search for synapse and add the filters "category: on demand" and "events for: Partners"

On demand webinars for Microsoft Partners - Introduction to Azure Synapse Analytics

• Session1: Introduction to Azure Synapse Analytics: Studio and orchestration
• Session 2: Introduction to Azure Synapse Analytics: Azure Synapse Analytics - Synapse and SQL Serverless
• Session 3: Introduction to Azure Synapse Analytics: Apache Spark and Synapse Workspace security
• Session4: Introduction to Azure Synapse Analytics: Azure Synapse Analytics Security, Metastore and Synapse link for Cosmos DB
• Session 5: Introduction to Azure Synapse Analytics: Azure Synapse Analytics -  Power BI and Azure Synapse Analytics Integration

Quick tip: SQL access to Common Data Service using Tabular Data Stream (TDS)

Recently Microsoft announced Power BI DirectQuery support (in preview) to the Common Data Service (CDS)  using Tabular Data Stream. You can also leverage this  CDS TDS endpoint within SQL Server Management Studio to write SQL queries against the CDS database. You can connect using the base url of your CDS instance and add the port 5558. To authenticate you will need to use Azure Active Directory - Password.

By default this functionality is not enabled, so you if you try to use SQL Server Management Studio you will get below exception.

As outlined on http://aka.ms/enablesqlforcds, you will need to to go the Power Platform Admin Center to switch on the TDS support.

References:

• DirectQuery CDS for Power BI
• Tabular Data Stream (TDS) Protocol endpoint for Common Data Service (CDS)