使用 Power BI 和无服务器 Synapse SQL 池来分析具有 Synapse Link 的 Azure Cosmos DB

项目
01/08/2024

适用对象： NoSQL MongoDB Gremlin

本文介绍如何在 Azure Cosmos DB 的 Synapse Link 上生成无服务器 SQL 池数据库和视图。你将查询 Azure Cosmos DB 容器，然后在那些视图中使用 Power BI 生成模型，以反映该查询。

借助 Azure Synapse Link，可以在 Power BI 中生成近乎实时的仪表板，以便分析 Azure Cosmos DB 数据。对事务工作负载没有任何性能或成本影响，也不存在管理 ETL 管道的复杂性。可以使用 DirectQuery 或 import 模式。

注意

使用 Azure Cosmos DB 门户，只需点击几下便可构建 Power BI 仪表板。有关详细信息，请参阅在 Azure Cosmos DB 门户中为启用 Synapse Link 的帐户提供集成的 Power BI 体验。这会自动在 Azure Cosmos DB 容器上的 Synapse 无服务器 SQL 池中创建 T-SQL 视图。只需下载连接到这些 T-SQL 视图的 pbids 文件，即可开始构建 BI 仪表板。

在此方案中，你将使用合作伙伴零售商店中有关 Surface 产品销售的虚拟数据。你将根据靠近大型家庭的程度和特定某一周中广告的影响来分析每家店的收入。在本文中，你会创建名为“RetailSales”和“StoreDemographics”的两个视图，并创建它们之间的查询。可以从此 GitHub 存储库获取示例产品数据。

注意

Synapse Link for Gremlin API 现为预览版。可以使用 Azure CLI 在新图形或现有图形中启用 Synapse Link。有关如何配置它的详细信息，请单击此处。

先决条件

在开始之前，请确保创建以下资源：

创建 API for NoSQL 或 API for MongoDB 帐户。
为 Azure Cosmos DB 帐户启用 Azure Synapse Link
在 Azure Cosmos DB 帐户中创建数据库，并且创建两个启用分析存储的容器。
按照批处理数据引入笔记本中的说明，将产品数据加载到 Azure Cosmos DB 容器中。
创建名为“SynapseLinkBI”的 Synapse 工作区。
将 Azure Cosmos DB 数据库连接到 Synapse 工作区。

创建数据库和视图

从 Synapse 工作区中，转到开发选项卡，选择++图标，然后选择SQL 脚本。

Add a SQL script to the Synapse Analytics workspace

每个工作区都带有无服务器 SQL 终结点。在创建 SQL 脚本后，从顶部的工具栏连接到“内置”。

Enable the SQL script to use the serverless SQL endpoint in the workspace

建议不要在 master 数据库或 default 数据库中创建视图，此操作也不受支持。在启用了 Synapse Link 的容器上创建名为“RetailCosmosDB”的新数据库和 SQL 视图。以下命令演示如何创建数据库：

-- Create database
Create database RetailCosmosDB

接下来，在启用了 Synapse Link 的不同的 Azure Cosmos DB 容器中创建多个视图。通过视图，可以使用 T-SQL 来联接和查询位于不同容器中的 Azure Cosmos DB 数据。在创建视图时，请确保选择“RetailCosmosDB”数据库。

以下脚本演示如何在每个容器上创建视图。为了简单起见，让我们在启用了 Synapse Link 的容器上使用无服务器 SQL 池的自动架构推理功能：

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

请确保在前一个 SQL 脚本中插入 Azure Cosmos DB 区域和主键。区域名称中的所有字符都应该使用小写，不带空格。与 OPENROWSET 命令的其他参数不同，在指定容器名称参数时，应该不带引号。

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

现在，请通过选择“运行”命令来运行该 SQL 脚本。

查询视图

现在，已经创建了两个视图，接下来让我们来定义查询，以便联接那两个视图，如下所示：

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]

选择“运行”，此操作会提供下表作为结果：

Query results after joining the StoreDemographics and RetailSales views

使用 Power BI 的容器上的模型视图

接下来，使通过用以下步骤打开 Power BI Desktop 并连接到无服务器 SQL 终结点：

打开 Power BI Desktop 应用程序。选择“获取数据”并选择”更多” 。
从连接选项列表中选择“Azure Synapse Analytics (SQL DW)”。
输入数据库所在的 SQL 终结点的名称。在“服务器”字段中输入“SynapseLinkBI-ondemand.sql.azuresynapse.net”。在此示例中，SynapseLinkBI 是工作区的名称。如果为工作区指定了其他不同名称，请替换此名称。为数据连接模式选择“直接查询”，然后选择“确定” 。
选择首选身份验证方法，如 Microsoft Entra ID。
选择“RetailCosmosDB”数据库，以及“RetailSales”、“StoreDemographics”视图。
选择“加载”，以将这两个视图加载到直接查询模式。
选择“模型”，以通过“storeId”列在两个视图之间建立关系。
从“RetailSales”视图将“StoreId”列拖到“StoreDemographics”视图中的“StoreId”列。
选择多对一 (*: 1) 关系，因为 RetailSales 视图中存在多个具有相同存储 ID 的行。 StoreDemographics 只有一个存储 ID 行（它是维度表）。