Использование Power BI и бессерверного пула Synapse SQL для анализа данных Azure Cosmos DB с помощью Synapse Link

Область применения: Nosql Mongodb Гремлин

В этой статье рассказывается, как создать базу данных и представления бессерверного пула SQL с помощью Synapse Link для Azure Cosmos DB. Вы выполнить запрос к контейнерам Azure Cosmos, а затем на основе этих представлений построить модель с помощью Power BI, чтобы отразить этот запрос.

Azure Synapse Link позволяет создавать в Power BI панели мониторинга практически в реальном времени для анализа данных Azure Cosmos DB. На транзакционные рабочие нагрузки не влияют производительность или затраты, а управлять конвейерами ETL просто. Можно использовать режим DirectQuery или режим импорта.

В этом сценарии вы будете использовать выдуманные данные о продажах продукта Surface в розничном магазине партнера. Вы проанализируете доход по магазинам исходя из близости к большим семьям и воздействия рекламы за определенную неделю. В этой статье вы создадите два представления с именами RetailSales и StoreDemographics и запрос между ними. Пример данных продукта можно получить из репозитория GitHub.

Необходимые компоненты

Прежде чем начать, создайте следующие ресурсы.

• Создайте учетную запись Azure Cosmos DB для API noSQL или MongoDB.

• Включение Azure Synapse Link для учетной записи Azure Cosmos DB

• Создайте базу данных в учетной записи Azure Cosmos DB и два контейнера с включенным аналитическим хранилищем.

• Загрузите данные продуктов в контейнеры Azure Cosmos DB, как описано в этой записной книжке приема пакетных данных.

• Создайте рабочую область Synapse с именем SynapseLinkBI.

• Подключите базу данных Azure Cosmos DB к рабочей области Synapse.

Создание базы данных и представлений

В рабочей области Synapse перейдите на вкладку Разработка, нажмите значок + и выберите Скрипт SQL.

В каждой рабочей области есть бессерверная конечная точка SQL. После создания скрипта SQL на панели инструментов в верхней части окна выберите Встроенный.

Создание представлений в базе данных master или базе данных по умолчанию не рекомендуется и не поддерживается. Создайте новую базу данных с именем RetailCosmosDB и представление SQL для контейнеров с поддержкой Synapse Link. Следующая команда показывает, как создать учетную запись.

-- Create database
Create database RetailCosmosDB

Затем создайте несколько представлений в разных контейнерах Synapse Link с поддержкой Azure Cosmos DB. Представления позволяют использовать T-SQL для объединения и запроса данных Azure Cosmos DB, размещенных в разных контейнерах. При создании представлений обязательно выберите базу данных RetailCosmosDB.

В следующих скриптах показано, как создавать представления для каждого контейнера. Для простоты мы будем использовать функцию автоматического вывода схемы в бессерверном пуле SQL через контейнеры с поддержкой Synapse Link.

Представление 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

Обязательно вставьте регион Azure Cosmos DB и первичный ключ в приведенный выше скрипт SQL. Все символы в имени региона должны быть в нижнем регистре без пробелов. В отличие от других параметров команды 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, выбрав команду Run.

Запрос представлений

Теперь, когда два представления создаются, определим запрос для объединения этих двух представлений следующим образом.:

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]

Выполните команду Run. В результата будет показана следующая таблица.

Представления модели на основе контейнеров с помощью Power BI

Выполните следующие действия, чтобы открыть Power BI Desktop и подключиться к бессерверной конечной точке SQL.

1. Откройте приложение Power BI Desktop. Выберите Получить данные и нажмите Больше.

2. Выберите Azure Synapse Analytics (SQL DW) в списке параметров подключения.

3. Введите имя конечной точки SQL, в которой находится база данных. Введите SynapseLinkBI-ondemand.sql.azuresynapse.net в поле Сервер. В этом примере SynapseLinkBI — это имя рабочей области. Замените его, если вы получили другое имя для своей рабочей области. Выберите Прямой запрос в качестве режима подключения к данным и нажмите кнопку OK.

4. Выберите предпочтительный метод проверки подлинности, например идентификатор Microsoft Entra.

5. Выберите базу данных RetailCosmosDB и представления RetailSales, StoreDemographics.

6. Выберите Загрузить, чтобы загрузить эти два представления в режим прямого запроса.

7. Выберите Модель, чтобы создать связь между этими двумя представлениями через столбец storeId.

8. Перетащите столбец storeId из представления RetailSales в сторону столбца StoreId в представлении StoreDemographics.

9. Выберите связь "Многие к одному" (* : 1), так как в представлении RetailSales существует несколько строк с одним и тем же идентификатором магазина. StoreDemographics содержит только одну строку идентификатора магазина (это таблица измерения).

Теперь перейдите в окно отчета и создайте отчет для сравнения относительной важности размера семьи для среднего дохода на магазин по дискретному представлению дохода и индексу LargeHH.

1. Выберите точечную диаграмму.

2. Перетащите LargeHH из представления StoreDemographics на ось X.

3. Перетащите Доход из представления RetailSales на ось Y. Выберите Среднее, чтобы получить среднее количество продаж на каждый магазин и за неделю.

4. Перетащите элемент productCode из представления RetailSales в условные обозначения, чтобы выбрать определенную линейку продуктов. После выбора этих параметров вы увидите диаграмму, схожую с той, что на следующем снимке экрана.

Следующие шаги

Интегрированный интерфейс Power BI на портале Azure Cosmos DB для учетных записей с поддержкой Synapse Link

Использование T-SQL для запроса данных Azure Cosmos DB с помощью Azure Synapse Link

Используйте бессерверный пул SQL для анализа Открытых наборов данных Azure и визуализации результатов в Azure Synapse Studio