Uso de Power BI y un grupo de Synapse SQL sin servidor para analizar los datos de Azure Cosmos DB mediante Synapse Link

Artículo
10/16/2024

SE APLICA A: NoSQL MongoDB Gremlin

En este artículo va a aprender a compilar una base de datos de grupos de SQL sin servidor y vistas de Synapse Link para Azure Cosmos DB. Va a consultar los contenedores de Azure Cosmos DB y, después, va a compilar un modelo con Power BI de esas vistas que refleje esa consulta.

Importante

La creación de reflejo de Azure Cosmos DB en Microsoft Fabric ya está disponible en versión preliminar para la API NoSql. Esta característica proporciona todas las funcionalidades de Azure Synapse Link con un mejor rendimiento analítico, la posibilidad de unificar el patrimonio de datos con Fabric OneLake y el acceso abierto a los datos en formato Delta Parquet. Si está considerando Azure Synapse Link, le recomendamos probar la creación de reflejo para evaluar si es la solución adecuada para su organización. Introducción a la creación de reflejos en Microsoft Fabric.

Con Azure Synapse Link, puede crear paneles casi en tiempo real en Power BI para analizar los datos de Azure Cosmos DB. No hay ningún impacto en el rendimiento ni en el costo de las cargas de trabajo transaccionales y tampoco existe ninguna complejidad en la administración de canalizaciones de ETL. Puede usar los modos DirectQuery o importación.

Nota

Puede crear paneles de Power BI con tan solo unos clics mediante el portal de Azure Cosmos DB. Para obtener más información, consulte Experiencia integrada de Power BI en el portal de Azure Cosmos DB para cuentas habilitadas para Synapse Link. Se crearán automáticamente vistas T-SQL en grupos de SQL sin servidor de Synapse en los contenedores de Azure Cosmos DB. Simplemente puede descargar el archivo .pbids que se conecta a estas vistas T-SQL para empezar a crear los paneles de inteligencia empresarial.

En este escenario usará datos ficticios sobre las ventas de productos de Surface en un comercio asociado. Analizará los ingresos por tienda en función de la proximidad a núcleos familiares grandes y el impacto de la publicidad durante una semana específica. En este artículo creará dos vistas denominadas RetailSales y StoreDemographics y una consulta entre ellas. Puede obtener los datos del producto de ejemplo en este repositorio de GitHub.

Requisitos previos

Asegúrese de crear los siguientes recursos antes de empezar:

Cree una cuenta de Azure Cosmos DB para la API para NoSQL o MongoDB.
Habilite Azure Synapse Link para la cuenta de Azure Cosmos DB
Cree una base de datos en la cuenta de Azure Cosmos DB y dos contenedores que tengan el almacén analítico habilitado.
Cargue los datos de los productos en los contenedores de Azure Cosmos DB como se indica en este cuaderno sobre ingesta de datos por lotes.
Cree un área de trabajo de Synapse denominada SynapseLinkBI.
Conecte la base de datos de Azure Cosmos DB al área de trabajo de Synapse.

Creación de una base de datos y vistas

En el área de trabajo de Synapse, vaya a la pestaña Desarrollar, seleccione el icono + y Script SQL.

Incorporación de un script SQL al área de trabajo de Synapse Analytics

Cada área de trabajo incluye un punto de conexión de SQL sin servidor. Después de crear un script SQL, en la barra de herramientas de la parte superior, conéctese a Integrado.

Habilitación del script SQL para usar el punto de conexión de SQL sin servidor en el área de trabajo

No se recomienda ni se ofrece asistencia para la creación de vistas en las bases de datos maestra o predeterminada. Cree una base de datos, denominada RetailCosmosDB, y una vista SQL con los contenedores habilitados para Synapse Link. El siguiente comando muestra cómo crear una base de datos:

-- Create database
Create database RetailCosmosDB

A continuación, cree varias vistas en distintos contenedores de Azure Cosmos DB habilitados para Synapse Link. Las vistas le permitirán usar T-SQL para combinar y consultar los datos de Azure Cosmos DB en distintos contenedores. Asegúrese de seleccionar la base de datos RetailCosmosDB al crear las vistas.

Los scripts siguientes muestran cómo crear vistas en cada contenedor. Para simplificar, vamos a usar la característica de inferencia automática de esquemas de un grupo de SQL sin servidor con contenedores habilitados para Synapse Link:

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

Asegúrese de insertar la región de Azure Cosmos DB y la clave principal en el script SQL anterior. Todos los caracteres del nombre de la región deben estar en minúsculas, sin espacios. A diferencia de los demás parámetros del comando OPENROWSET, el parámetro del nombre de contenedor debe especificarse sin comillas.

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

Ahora ejecute el script SQL; para ello, seleccione el comando Run.

Consulta de las vistas

Ahora que se han creado las dos vistas, vamos a definir la consulta para combinar esas dos vistas de la siguiente manera:

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]

Seleccione Run para obtener la siguiente tabla como resultado:

Resultados de la consulta después de combinar las vistas StoreDemographics y RetailSales

Vistas del modelo en contenedores con Power BI

A continuación, abra Power BI Desktop y conéctese al punto de conexión de SQL sin servidor. Para ello, siga estos pasos:

Abra la aplicación Power BI Desktop. Seleccione Obtener datos y Más.
Elija Azure Synapse Analytics (SQL DW) de la lista de opciones de conexión.
Escriba el nombre del punto de conexión de SQL donde se encuentra la base de datos. Escriba SynapseLinkBI-ondemand.sql.azuresynapse.net en el campo Servidor. En este ejemplo, SynapseLinkBI es el nombre del área de trabajo. Reemplácelo si le ha dado un nombre diferente al área de trabajo. Seleccione DirectQuery como modo de conectividad y Aceptar.
Seleccione el método de autenticación preferido, por ejemplo, Microsoft Entra ID.
Seleccione la base de datos RetailCosmosDB y las vistas RetailSales y StoreDemographics.
Seleccione Cargar para cargar las dos vistas en el modo de consulta directa.
Seleccione Modelo para crear una relación entre las dos vistas mediante la columna storeId.
Arrastre la columna StoreId de la vista RetailSales a la columna StoreId de la vista StoreDemographics.
Seleccione la relación de varios a uno (*:1) porque hay varias filas con el mismo identificador de almacén en la vista RetailSales. StoreDemographics tiene solo una fila de identificador de almacén (es una tabla de dimensiones).

Ahora vaya a la ventana report y cree un informe para comparar la importancia relativa del tamaño de los núcleos familiares con el promedio de ingresos por tienda en función de la representación dispersa de ingresos y el índice LargeHH:

Seleccione Gráfico de dispersión.
Arrastre y coloque LargeHH desde la vista StoreDemographics hasta el eje X.
Arrastre y coloque Revenue (Ingresos) desde la vista RetailSales hasta el eje Y. Seleccione Media para obtener el promedio de ventas por producto por tienda y por semana.
Arrastre y coloque productCode de la vista RetailSales en la leyenda para seleccionar una línea de productos específica. Después de elegir estas opciones, debería ver un gráfico como el de la siguiente captura de pantalla: