Usare Power BI e il pool Synapse SQL serverless per analizzare i dati di Azure Cosmos DB con Collegamento a Synapse

SI APPLICA A: NoSQL MongoDB Gremlin

Questo articolo illustra come creare un database e viste del pool SQL serverless tramite Collegamento a Synapse per Azure Cosmos DB. Si eseguirà una query sui contenitori di Azure Cosmos DB e con Power BI si creerà quindi un modello sulle viste per riflettere la query.

Con Collegamento ad Azure Synapse è possibile creare dashboard quasi in tempo reale in Power BI per analizzare i dati di Azure Cosmos DB. Non vi è alcun impatto sulle prestazioni o sui costi per i carichi di lavoro transazionali e nessuna complessità nella gestione delle pipeline ETL. È possibile usare DirectQuery o modalità di importazione.

Nota

È possibile creare dashboard in Power BI con pochi clic usando il portale di Azure Cosmos DB. Per altre informazioni, vedere Esperienza di Power BI integrata nel portale di Azure Cosmos DB per account abilitati per Collegamento a Synapse. In questo modo verranno automaticamente create viste T-SQL in pool SQL serverless di Synapse inclusi in contenitori di Azure Cosmos DB. È sufficiente scaricare il file con estensione pbids che si connette a queste viste T-SQL per iniziare a creare i dashboard BI.

In questo scenario si useranno dati fittizi sulle vendite di prodotti Surface in un negozio di vendita al dettaglio partner. Si analizzeranno i ricavi per negozio in base alla prossimità a famiglie numerose e all'impatto di una campagna pubblicitaria di una settimana. In questo articolo vengono create due viste denominate RetailSales e StoreDemographics, su cui verrà eseguita una query. È possibile ottenere i dati dei prodotti di esempio da questo repository GitHub.

Nota

Collegamento a Synapse per l'API Gremlin è ora disponibile in anteprima. È possibile abilitare Collegamento a Synapse nei grafici nuovi o esistenti usando l'interfaccia della riga di comando di Azure. Per altre informazioni su come configurarla, fare clic qui.

Prerequisiti

Prima di iniziare, assicurarsi di aver creato le risorse seguenti:

• Creare un account Azure Cosmos DB per l'API per NoSQL o MongoDB.

• Abilitare Collegamento ad Azure Synapse per un account Azure Cosmos DB

• Creare un database all'interno dell'account Azure Cosmos DB e due contenitori con archivio analitico abilitato.

• Caricare i dati dei prodotti nei contenitori di Azure Cosmos DB come descritto in questo notebook sull'inserimento di dati in batch.

• Creare un'area di lavoro di Synapse denominata SynapseLinkBI.

• Connettere il database Azure Cosmos DB a un'area di lavoro di Synapse.

Creare un database e viste

Nell'area di lavoro di Synapse passare alla scheda Sviluppo e selezionare l'icona + e quindi Script SQL.

Ogni area di lavoro include un endpoint SQL serverless. Dopo aver creato uno script SQL, dalla barra degli strumenti nella parte superiore connettersi al valore Predefinito.

Non è consigliata (o supportata) la creazione di viste nel database master o in quello predefinito. Creare un nuovo database denominato RetailCosmosDB e una vista SQL nei contenitori abilitati per Collegamento a Synapse. Il comando seguente illustra come creare un database:

-- Create database
Create database RetailCosmosDB

Creare quindi più viste in diversi contenitori di Azure Cosmos DB abilitati per Collegamento a Synapse. Le viste consentono di usare T-SQL per creare un join ed eseguire query su dati di Azure Cosmos DB in contenitori diversi. Assicurarsi di selezionare il database RetailCosmosDB durante la creazione delle viste.

Gli script seguenti illustrano come creare viste in ogni contenitore. Per semplicità, si userà la funzionalità di inferenza automatica dello schema del pool SQL serverless sui contenitori abilitati per Collegamento a Synapse:

Vista RetailSales:

-- Create view for RetailSales container
CREATE VIEW  RetailSales
AS  
SELECT  *
FROM OPENROWSET (
    'CosmosDB', N'account=<Your Azure Cosmos DB account name>;database=<Your Azure Cosmos DB database name>;region=<Your Azure Cosmos DB Region>;key=<Your Azure Cosmos DB key here>',RetailSales)
AS q1

Assicurarsi di inserire l'area e la chiave primaria di Azure Cosmos DB nello script SQL precedente. Tutti i caratteri che compongono il nome dell'area devono essere scritti in lettere minuscole senza spazi. A differenza degli altri parametri del comando OPENROWSET, il parametro del nome del contenitore deve essere specificato senza virgolette.

Vista StoreDemographics:

-- Create view for StoreDemographics container
CREATE VIEW StoreDemographics
AS  
SELECT  *
FROM OPENROWSET (
    'CosmosDB', N'account=<Your Azure Cosmos DB account name>;database=<Your Azure Cosmos DB database name>;region=<Your Azure Cosmos DB Region>;key=<Your Azure Cosmos DB key here>', StoreDemographics)
AS q1

Eseguire ora lo script SQL selezionando il comando Esegui.

Eseguire query sulle viste

Dopo aver creato le due viste, è ora possibile definire la query per eseguire il join delle viste come segue:

SELECT 
sum(p.[revenue]) as revenue
,p.[advertising]
,p.[storeId]
,p.[weekStarting]
,q.[largeHH]
 FROM [dbo].[RetailSales] as p
INNER JOIN [dbo].[StoreDemographics] as q ON q.[storeId] = p.[storeId]
GROUP BY p.[advertising], p.[storeId], p.[weekStarting], q.[largeHH]

Selezionare Esegui per visualizzare la tabella seguente:

Modellare le viste nei contenitori con Power BI

Aprire quindi Power BI Desktop e connettersi all'endpoint SQL serverless seguendo questa procedura:

1. Aprire l'app Power BI Desktop. Selezionare Scarica i dati e selezionare altro.

2. Nell'elenco delle opzioni di connessione scegliere Azure Synapse Analytics (SQL DW).

3. Immettere il nome dell'endpoint SQL in cui si trova il database. Immettere SynapseLinkBI-ondemand.sql.azuresynapse.net nel campo Server. In questo esempio, il nome dell'area di lavoro è SynapseLinkBI. Sostituirlo se all'area di lavoro è stato assegnato un nome diverso. Selezionare la modalità di connessione dati Query diretta e quindi scegliere OK.

4. Selezionare il metodo di autenticazione preferito, ad esempio Microsoft Entra ID.

5. Selezionare il database RetailCosmosDB e le viste RetailSales e StoreDemographics.

6. Selezionare Carica per caricare le due viste nella modalità di query diretta.

7. Selezionare Modello per creare una relazione tra le due viste tramite la colonna storeId.

8. Trascinare la colonna StoreId dalla vista RetailSales verso la colonna StoreId nella vista StoreDemographics.

9. Selezionare la relazione Molti-a-uno (*:1) poiché nella vista RetailSales sono presenti più righe con lo stesso storeId. StoreDemographics include una sola riga storeId (si tratta di una tabella delle dimensioni).

Passare ora alla finestra Report e creare un report per confrontare l'importanza relativa delle dimensioni dei nuclei familiari per i ricavi medi di ogni negozio secondo la rappresentazione distribuita dei ricavi e dell'indice LargeHH:

1. Selezionare Grafico a dispersione.

2. Trascinare e rilasciare LargeHH dalla vista StoreDemographics all'asse X.

3. Trascinare e rilasciare Revenue dalla vista RetailSales all'asse Y. Selezionare Media per ottenere le vendite medie per prodotto, per negozio e per settimana.

4. Trascinare e rilasciare productCode dalla vista RetailSales alla legenda per selezionare una linea di prodotto specifica. Dopo aver scelto queste opzioni, verrà visualizzato un grafico simile allo screenshot seguente:

Passaggi successivi

Esperienza di Power BI integrata nel portale di Azure Cosmos DB per account abilitati per Collegamento a Synapse.

Usare T-SQL per eseguire query sui dati di Azure Cosmos DB tramite Collegamento ad Azure Synapse

Usare un pool SQL serverless per analizzare i set di dati aperti di Azure e visualizzare i risultati in Azure Synapse Studio