Build Near Real Time Power BI reports using Synapse Link and SQL On-Demand easily

Published Feb 25 2021 03:23 AM 2,591 Views
Microsoft

This guide describes how to build near real time Power BI reports leveraging Synapse Link and SQL On-Demand. The intent is to demonstrate the simplicity of using these technologies.

 

Now let's start!

Ensure you have the new Synapse Workspace enabled in your subscription:

NewSynapseWorkspace.png

 

Ensure you have Synapse Link enabled at your Cosmos DB account

SynapseLinkEnabled.png

 

Create your Database and container, verify the container has the Analytical Store enabled

As a prerequisite you need to ensure you are running Cosmos DB Python SDK v4.1.0 by executing the code below in a notebook:

 

import azure.cosmos as cosmos
print (cosmos.__version__)

 

 

Result should be 4.1.0, if it’s below then run this command in a new cell:

 

pip install --force-reinstall azure-cosmos

 

 

You’ll then need to open a new notebook to get the new version taken into account and run the following code:

 

import azure.cosmos
from azure.cosmos.partition_key import PartitionKey

database = cosmos_client.create_database_if_not_exists('RetailDemo')
print('Database RetailDemo created')

container = database.create_container_if_not_exists(id='WebsiteData', partition_key=PartitionKey(path='/CartID'),analytical_storage_ttl=-1)


print('Container WebsiteData created')

 

 

Note that you have created in the database RetailDemo a container named WebsiteData partitioned on CartID and you enabled the Analytical Store with the the parameter "analytical_storage_ttl=-1"

 

Once the container is created you can check the Analytical Store is enabled by default:

AnalyticalStoreEnabled.png

 

Then let start to load a small sample of data, for this you just need to create a new notebook in your Cosmos DB Data Explorer as follow:

CosmosDataExplorer.png

 

Code to run in your notebook:

 

%%upload --databaseName RetailDemo --containerName WebsiteData --url https://cosmosnotebooksdata.blob.core.windows.net/notebookdata/websiteData-small.json

 

 

Here is how the data look like now:

CosmosDBItems.png

 

Once this first step is complete you have a container with a few items and an Analytical Store on it.

 

Next Step is to go to Synapse Workspace and from there to Synapse Studio, create a SQL On Demand Database and test querying the Cosmos DB Analytical Store from there.

 

You can either use an existing Synapse Workspace or create a new one and launch Synapse Studio directly on your Workspace.

SynapseWorkspace2.png

 

To discover how to create a SQL On-Demand Database and start learning how it works simply use this link:

https://docs.microsoft.com/en-us/azure/synapse-analytics/quickstart-sql-on-demand 

 

Now you can create a view in the On-Demand Database with the following syntax:

 

Click on the Develop icon on the left side to access the SQL script and Notebooks, click on the ‘+’ sign to get a new SQL script then connected to the SQL On-Demand engine and ‘myondemanddb’ database.

SQLondemandView2.png

 

Code to run in your SQL script:

 

CREATE VIEW CosmosDBTest
AS 
SELECT
*
FROM OPENROWSET
    (

     'CosmosDB',
       'account=cosmosdblp2;database=RetailDemo;region=northeurope;key=your_key',
       WebsiteData
    )

AS q1

 

 

In my my current example region is northeurope.

Once the view is created, you can run simplistic queries such as the ones below and get the results from the CosmosDB container WebsiteData:

SynapseOnDemandQueries.png

SynapseOnDemandQueries2.png

 

Code to run in your SQL script:

 

select * from CosmosDBTest;

select country,sum(price) 
from CosmosDBTest

group by country;

 

 

Note that Synapse Link take care of the JSON document flattening into a table format for you.

 

Now that the general mechanism is now in place so you are ready to build a Power BI report on top of this. In case you don’t have PBI Desktop already you can get it from there:

https://www.microsoft.com/en-us/download/details.aspx?id=58494

 

Start PBI Desktop and select the Azure SQL Database source and put the SQL On-Demand endpoint as the server name:

SynapseWorkspace2.png

PBIopenAzureSQLDB.png

Do not forget to specify port 1433 (yours-ondemand.sql.azuresynapse.net,1433) and DirectQuery mode:

PBIConnection.png

 

After giving your credentials let’s pick up the view CosmosDBTest that you created precedingly:

PBISourceSelect.png

 

Here is the simple report we can build and let focus on Guinea-Bissau, the current price value is 7.5:

PBIReport1.png

Let’s go back to CosmosDB and select the corresponding items where we can raise the price of the corresponding item by 100 for instance:

CosmosDBUpdateItem2.png

 

After a latency of around a minute the price increase is reflected on the report:

PBIReport2.png

 

And what if you load a larger set of Data into your Cosmos DB container?

Let’s then take a larger data set and bulk load it into your container as follow:

CosmosDBLargeSet.png

 

How is this rendered in Power BI?

PBIReport3.png

 

Almost there!

What if you publish to your Power BI Tenant?

PBITenant.png

 

This is it!

 

To wrap this up:

  • I enabled the Analytical Store on a Cosmos DB container
  • I created a SQL on-demand view on this container Analytical Store
  • I created a Power BI report connected to the SQL on-demand database as a regular Azure SQL Database 

 

Call to Action:

%3CLINGO-SUB%20id%3D%22lingo-sub-2165451%22%20slang%3D%22en-US%22%3EBuild%20Near%20Real%20Time%20Power%20BI%20reports%20using%20Synapse%20Link%20and%20SQL%20On-Demand%20easily%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2165451%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EThis%20guide%20describes%20how%20to%20build%20near%20real%20time%20Power%20BI%20reports%20leveraging%20Synapse%20Link%20and%20SQL%20On-Demand.%20The%20intent%20is%20to%20demonstrate%20the%20simplicity%20of%20using%20these%20technologies.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENow%20let's%20start!%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EEnsure%20you%20have%20the%20new%20Synapse%20Workspace%20enabled%20in%20your%20subscription%3A%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22NewSynapseWorkspace.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F257793i53E34E9C7C5F97BF%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22NewSynapseWorkspace.png%22%20alt%3D%22NewSynapseWorkspace.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EEnsure%20you%20have%20Synapse%20Link%20enabled%20at%20your%20Cosmos%20DB%20account%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22SynapseLinkEnabled.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F257794i42B61DB6EEEE0947%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22SynapseLinkEnabled.png%22%20alt%3D%22SynapseLinkEnabled.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECreate%20your%20Database%20and%20container%2C%20verify%20the%20container%20has%20the%20Analytical%20Store%20enabled%3C%2FP%3E%0A%3CP%3EAs%20a%20prerequisite%20you%20need%20to%20ensure%20you%20are%20running%20Cosmos%20DB%20Python%20SDK%20v4.1.0%20by%20executing%20the%20code%20below%20in%20a%20notebook%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-python%22%3E%3CCODE%3Eimport%20azure.cosmos%20as%20cosmos%0Aprint%20(cosmos.__version__)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EResult%20should%20be%204.1.0%2C%20if%20it%E2%80%99s%20below%20then%20run%20this%20command%20in%20a%20new%20cell%3A%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-python%22%3E%3CCODE%3Epip%20install%20--force-reinstall%20azure-cosmos%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EYou%E2%80%99ll%20then%20need%20to%20open%20a%20new%20notebook%20to%20get%20the%20new%20version%20taken%20into%20account%20and%20run%20the%20following%20code%3A%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-python%22%3E%3CCODE%3Eimport%20azure.cosmos%0Afrom%20azure.cosmos.partition_key%20import%20PartitionKey%0A%0Adatabase%20%3D%20cosmos_client.create_database_if_not_exists('RetailDemo')%0Aprint('Database%20RetailDemo%20created')%0A%0Acontainer%20%3D%20database.create_container_if_not_exists(id%3D'WebsiteData'%2C%20partition_key%3DPartitionKey(path%3D'%2FCartID')%2Canalytical_storage_ttl%3D-1)%0A%0A%0Aprint('Container%20WebsiteData%20created')%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3ENote%20that%20you%20have%20created%20in%20the%20database%20RetailDemo%20a%20container%20named%20WebsiteData%20partitioned%20on%20CartID%20and%20you%20enabled%20the%20Analytical%20Store%20with%20the%20the%20parameter%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3E%22analytical_storage_ttl%3D-1%22%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOnce%20the%20container%20is%20created%20you%20can%20check%20the%20Analytical%20Store%20is%20enabled%20by%20default%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22AnalyticalStoreEnabled.png%22%20style%3D%22width%3A%20681px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F257796i32B002F9F1CA01DE%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22AnalyticalStoreEnabled.png%22%20alt%3D%22AnalyticalStoreEnabled.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EThen%20let%20start%20to%20load%20a%20small%20sample%20of%20data%2C%20for%20this%20you%20just%20need%20to%20create%20a%20new%20notebook%20in%20your%20Cosmos%20DB%20Data%20Explorer%20as%20follow%3A%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22CosmosDataExplorer.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F257797i396AB1EE47DD7A31%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22CosmosDataExplorer.png%22%20alt%3D%22CosmosDataExplorer.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3ECode%20to%20run%20in%20your%20notebook%3A%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-python%22%3E%3CCODE%3E%25%25upload%20--databaseName%20RetailDemo%20--containerName%20WebsiteData%20--url%20https%3A%2F%2Fcosmosnotebooksdata.blob.core.windows.net%2Fnotebookdata%2FwebsiteData-small.json%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EHere%20is%20how%20the%20data%20look%20like%20now%3A%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22CosmosDBItems.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F257798i08FBD19833E95883%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22CosmosDBItems.png%22%20alt%3D%22CosmosDBItems.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOnce%20this%20first%20step%20is%20complete%20you%20have%20a%20container%20with%20a%20few%20items%20and%20an%20Analytical%20Store%20on%20it.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENext%20Step%20is%20to%20go%20to%20Synapse%20Workspace%20and%20from%20there%20to%20Synapse%20Studio%2C%20create%20a%20SQL%20On%20Demand%20Database%20and%20test%20querying%20the%20Cosmos%20DB%20Analytical%20Store%20from%20there.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20either%20use%20an%20existing%20Synapse%20Workspace%20or%20create%20a%20new%20one%20and%20launch%20Synapse%20Studio%20directly%20on%20your%20Workspace.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22SynapseWorkspace2.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F257800iBE2DBCCAF2D100C4%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22SynapseWorkspace2.png%22%20alt%3D%22SynapseWorkspace2.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3ETo%20discover%20how%20to%20create%20a%20SQL%20On-Demand%20Database%20and%20start%20learning%20how%20it%20works%20simply%20use%20this%20link%3A%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsynapse-analytics%2Fquickstart-sql-on-demand%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsynapse-analytics%2Fquickstart-sql-on-demand%3C%2FA%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENow%20you%20can%20create%20a%20view%20in%20the%20On-Demand%20Database%20with%20the%20following%20syntax%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EClick%20on%20the%20Develop%20icon%20on%20the%20left%20side%20to%20access%20the%20SQL%20script%20and%20Notebooks%2C%20click%20on%20the%20%E2%80%98%2B%E2%80%99%20sign%20to%20get%20a%20new%20SQL%20script%20then%20connected%20to%20the%20SQL%20On-Demand%20engine%20and%20%E2%80%98myondemanddb%E2%80%99%20database.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22SQLondemandView2.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F257801i044E9149AC95095B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22SQLondemandView2.png%22%20alt%3D%22SQLondemandView2.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3ECode%20to%20run%20in%20your%20SQL%20script%3A%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3ECREATE%20VIEW%20CosmosDBTest%0AAS%20%0ASELECT%0A*%0AFROM%20OPENROWSET%0A%20%20%20%20(%0A%0A%20%20%20%20%20'CosmosDB'%2C%0A%20%20%20%20%20%20%20'account%3Dcosmosdblp2%3Bdatabase%3DRetailDemo%3Bregion%3Dnortheurope%3Bkey%3Dyour_key'%2C%0A%20%20%20%20%20%20%20WebsiteData%0A%20%20%20%20)%0A%0AAS%20q1%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20my%20my%20current%20example%20region%20is%20northeurope.%3C%2FP%3E%0A%3CP%3EOnce%20the%20view%20is%20created%2C%20you%20can%20run%20simplistic%20queries%20such%20as%20the%20ones%20below%20and%20get%20the%20results%20from%20the%20CosmosDB%20container%20WebsiteData%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22SynapseOnDemandQueries.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F257802iD2F9A28467771ACF%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22SynapseOnDemandQueries.png%22%20alt%3D%22SynapseOnDemandQueries.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22SynapseOnDemandQueries2.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F257803i0FFBBADF2338476D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22SynapseOnDemandQueries2.png%22%20alt%3D%22SynapseOnDemandQueries2.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3ECode%20to%20run%20in%20your%20SQL%20script%3A%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3Eselect%20*%20from%20CosmosDBTest%3B%0A%0Aselect%20country%2Csum(price)%20%0Afrom%20CosmosDBTest%0A%0Agroup%20by%20country%3B%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENote%20that%20Synapse%20Link%20take%20care%20of%20the%20JSON%20document%20flattening%20into%20a%20table%20format%20for%20you.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENow%20that%20the%20general%20mechanism%20is%20now%20in%20place%20so%20you%20are%20ready%20to%20build%20a%20Power%20BI%20report%20on%20top%20of%20this.%20In%20case%20you%20don%E2%80%99t%20have%20PBI%20Desktop%20already%20you%20can%20get%20it%20from%20there%3A%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.microsoft.com%2Fen-us%2Fdownload%2Fdetails.aspx%3Fid%3D58494%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.microsoft.com%2Fen-us%2Fdownload%2Fdetails.aspx%3Fid%3D58494%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EStart%20PBI%20Desktop%20and%20select%20the%20Azure%20SQL%20Database%20source%20and%20put%20the%20SQL%20On-Demand%20endpoint%20as%20the%20server%20name%3A%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22SynapseWorkspace2.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F257804i1E5CA3C10F88F6F8%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22SynapseWorkspace2.png%22%20alt%3D%22SynapseWorkspace2.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22PBIopenAzureSQLDB.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F257805i29655CF6FF49C5F6%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22PBIopenAzureSQLDB.png%22%20alt%3D%22PBIopenAzureSQLDB.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EDo%20not%20forget%20to%20specify%20port%201433%20(yours-ondemand.sql.azuresynapse.net%2C1433)%20and%20DirectQuery%20mode%3A%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22PBIConnection.png%22%20style%3D%22width%3A%20980px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F257806iE6F453C0F6DE1450%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22PBIConnection.png%22%20alt%3D%22PBIConnection.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EAfter%20giving%20your%20credentials%20let%E2%80%99s%20pick%20up%20the%20view%20CosmosDBTest%20that%20you%20created%20precedingly%3A%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22PBISourceSelect.png%22%20style%3D%22width%3A%20846px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F257807i43BB95B2C6D37809%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22PBISourceSelect.png%22%20alt%3D%22PBISourceSelect.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EHere%20is%20the%20simple%20report%20we%20can%20build%20and%20let%20focus%20on%20Guinea-Bissau%2C%20the%20current%20price%20value%20is%207.5%3A%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22PBIReport1.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F257811i2BD27F8FAC015B61%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22PBIReport1.png%22%20alt%3D%22PBIReport1.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3ELet%E2%80%99s%20go%20back%20to%20CosmosDB%20and%20select%20the%20corresponding%20items%20where%20we%20can%20raise%20the%20price%20of%20the%20corresponding%20item%20by%20100%20for%20instance%3A%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22CosmosDBUpdateItem2.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F257817iF25D43F628A83A30%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22CosmosDBUpdateItem2.png%22%20alt%3D%22CosmosDBUpdateItem2.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EAfter%20a%20latency%20of%20around%20a%20minute%20the%20price%20increase%20is%20reflected%20on%20the%20report%3A%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22PBIReport2.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F257818i5227CF66BE1F7BFC%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22PBIReport2.png%22%20alt%3D%22PBIReport2.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnd%20what%20if%20you%20load%20a%20larger%20set%20of%20Data%20into%20your%20Cosmos%20DB%20container%3F%3C%2FP%3E%0A%3CP%3ELet%E2%80%99s%20then%20take%20a%20larger%20data%20set%20and%20bulk%20load%20it%20into%20your%20container%20as%20follow%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22CosmosDBLargeSet.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F257819i7D32C0015CC1C063%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22CosmosDBLargeSet.png%22%20alt%3D%22CosmosDBLargeSet.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EHow%20is%20this%20rendered%20in%20Power%20BI%3F%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22PBIReport3.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F257820i2E8F5909B31D554D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22PBIReport3.png%22%20alt%3D%22PBIReport3.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAlmost%20there!%3C%2FP%3E%0A%3CP%3EWhat%20if%20you%20publish%20to%20your%20Power%20BI%20Tenant%3F%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22PBITenant.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F257821i24627466B9AF0876%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22PBITenant.png%22%20alt%3D%22PBITenant.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20is%20it!%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20wrap%20this%20up%3A%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EI%20enabled%20the%20Analytical%20Store%20on%20a%20Cosmos%20DB%20container%3C%2FLI%3E%0A%3CLI%3EI%20created%20a%20SQL%20on-demand%20view%20on%20this%20container%20Analytical%20Store%3C%2FLI%3E%0A%3CLI%3EI%20created%20a%20Power%20BI%20report%20connected%20to%20the%20SQL%20on-demand%20database%20as%20a%20regular%20Azure%20SQL%20Database%26nbsp%3B%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3ECall%20to%20Action%3A%3C%2FSPAN%3E%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EPlease%20try%20now%20the%20Cosmos%20DB%20to%20Spark%20integration%20by%20following%20these%20examples%3A%3C%2FLI%3E%0A%3CLI%3E%3CA%20href%3D%22https%3A%2F%2Fgithub.com%2FAzure-Samples%2FSynapse%2Ftree%2Fmain%2FNotebooks%2FPySpark%2FSynapse%2520Link%2520for%2520Cosmos%2520DB%2520samples%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3ESynapse%2FNotebooks%2FPySpark%2FSynapse%20Link%20for%20Cosmos%20DB%20samples%20at%20main%20%C2%B7%20Azure-Samples%2FSynapse%20(github.com)%3C%2FA%3E%3C%2FLI%3E%0A%3C%2FUL%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-2165451%22%20slang%3D%22en-US%22%3E%3CH2%20id%3D%22toc-hId--523268528%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22SynapseLinkLogo.png%22%20style%3D%22width%3A%20979px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F257768i03AF33EE04509277%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22SynapseLinkLogo.png%22%20alt%3D%22SynapseLinkLogo.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FH2%3E%0A%3CH2%20id%3D%22toc-hId-1964244305%22%3EThis%20is%20a%20step%20by%20step%20guide%20to%20easily%20build%20near%20real%20time%20dashboards%20based%20on%20Cosmos%20DB%20data%20using%20Synapse%20Link%3C%2FH2%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2165451%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESynapse%20SQL%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESynapse%20Studio%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Co-Authors
Version history
Last update:
‎Feb 25 2021 03:23 AM
Updated by: