SQL Server и Power BI: подключение, SQL-запросы и обновление

Обновлено 2 сентября 2026 года.

Microsoft SQL Server хранит и обрабатывает данные, а Power BI строит семантическую модель и отчёты. Хорошая интеграция начинается с определения таблиц, ключей и расчётов, затем — с выбора Import или DirectQuery, настройки доступа и проверки обновления после публикации.

Это руководство разбирает подключение, подготовку SQL-запросов, типичные ошибки агрегации и эксплуатацию. Для существующей корпоративной базы установка нового SQL Server на компьютер аналитика обычно не нужна: потребуются адрес сервера, имя базы и разрешённый доступ к подготовленным объектам.

SQL Server, SSMS и Analysis Services — разные компоненты

Не выбирайте коннектор SSAS вместо SQL Server только потому, что оба продукта Microsoft. Он работает с аналитической моделью, а не служит альтернативным входом в произвольные реляционные таблицы. Название Power Pivot относится прежде всего к Excel; для модели Power BI используйте её собственные средства моделирования.

Подключение SQL Server в Power BI Desktop

1. Получите у администратора точное имя сервера или экземпляра, базу, способ аутентификации и список разрешённых объектов.
2. В Desktop выберите Получить данные → SQL Server database.
3. Укажите сервер и, желательно, базу. Выберите Import либо DirectQuery.
4. Выберите поддерживаемую аутентификацию. Windows и Database — разные варианты; организационная учётная запись подходит только там, где её поддерживает сервер и выбранная среда.
5. В навигаторе выберите таблицы или представления. Откройте преобразование данных, если нужны фильтры, типизация и очистка.
6. Сверьте число строк и несколько записей по ключу, затем создавайте связи и меры.

Специально создавать ODBC DSN для встроенного коннектора SQL Server не требуется. Если нужен собственный SQL, поле SQL statement находится в дополнительных параметрах подключения. Это отдельный путь получения результата запроса, а не поле, которое обязательно заполнять после выбора каждой таблицы. Актуальная инструкция коннектора.

Доступ к базе для отчётности обычно ограничивают чтением согласованных таблиц или представлений. Не выдавайте аналитической учётной записи права администратора ради устранения ошибки подключения. Ошибки DNS, порта, сертификата, аутентификации и прав на объект требуют разных исправлений.

Import или DirectQuery

DirectQuery не является подпиской на изменение каждой строки и не означает автоматически «реальное время». Изменение источника, запрос визуализации и обновление страницы — отдельные события. Начните с измеряемого требования к задержке. Сценарии подробно разобраны в статье о больших данных и частом обновлении Power BI.

Подготовка таблиц: детализация важнее количества полей

У каждой таблицы должен быть понятный смысл строки: заказ, строка заказа, платёж, клиент или ежедневный остаток. Например, OrderId уникален в таблице заказов, но повторяется в строках заказа. Для строк нужен отдельный ключ или сочетание OrderId и LineNo.

• Для сумм выбирайте подходящие точные типы decimal с согласованными точностью и масштабом.
• Даты оставляйте датами, а не превращайте заранее в текст ради красивого отображения.
• Разделяйте NULL, ноль и пустую строку. NULL в сумме платежа не доказывает отсутствие платежа — он может означать неизвестное значение.
• Идентификаторы, коды с ведущими нулями и телефоны не следует преобразовывать в числа без понимания их назначения.
• Определите валюту, часовой пояс и правила возвратов до расчёта итогов.

В модели обычно удобно отделять факты от справочников даты, клиента и товара. У справочника ключ должен быть уникален. Проверяйте строки фактов, для которых не найден соответствующий элемент справочника. Рекомендации Microsoft по схеме «звезда».

Самостоятельный пример SQL: строки заказа и выручка

Ниже учебный запрос T-SQL для окна запроса SSMS. Он создаёт набор через VALUES внутри запроса и не создаёт постоянных таблиц. В примере UnitPrice — цена одной единицы, Quantity — количество, а выручка строки равна их произведению. Все строки относятся к одной валюте; скидок и возвратов в этом наборе нет.

WITH Sales AS (
    SELECT *
    FROM (VALUES
        (101, 1, 'North', 2, CAST(100.00 AS decimal(10,2))),
        (101, 2, 'North', 1, CAST(50.00 AS decimal(10,2))),
        (102, 1, 'South', 3, CAST(80.00 AS decimal(10,2))),
        (103, 1, 'South', 1, CAST(110.00 AS decimal(10,2)))
    ) AS v(OrderId, LineNo, Region, Quantity, UnitPrice)
)
SELECT
    Region,
    COUNT(*) AS LineCount,
    COUNT(DISTINCT OrderId) AS OrderCount,
    SUM(Quantity) AS Units,
    SUM(Quantity * UnitPrice) AS Revenue,
    SUM(Quantity * UnitPrice) / NULLIF(SUM(Quantity), 0)
        AS WeightedUnitPrice
FROM Sales
GROUP BY Region
ORDER BY Region;

Общая выручка — 600, заказов — 3, единиц — 7. Средний чек равен 600 / 3 = 200. Взвешенная цена единицы равна 600 / 7 ≈ 85,71. Сумма UnitPrice дала бы 340, а простое среднее UnitPrice — 85: это другие величины. Название показателя не заменяет формулу.

Запрос с CTE предназначен для изучения T-SQL. Для DirectQuery не копируйте его без изменений в native SQL: Microsoft ограничивает такие запросы SELECT без CTE и вызовов процедур. В рабочем проекте подготовьте подходящую таблицу или представление и подключитесь к нему. Ограничения native query и folding.

Агрегаты, NULL и типы: где появляются неверные итоги

Для проверки отсутствия значения используйте IS NULL или IS NOT NULL. Подстановка текстового N/A в числовой столбец через COALESCE может завершиться ошибкой преобразования. И замена неизвестной суммы нулём меняет смысл расчёта: делайте её только по согласованному правилу.

Чтобы получить дробное среднее целых значений, можно писать AVG(CAST(Quantity AS decimal(18,2))). Возвращаемый тип и обработка NULL описаны в справке AVG.

JOIN: проверяйте число строк до и после соединения

INNER JOIN возвращает совпавшие сочетания строк. LEFT JOIN сохраняет строки слева, добавляя совпадения справа или NULL, если их нет. Если справа несколько совпадений по ключу, строка слева повторяется. RIGHT и FULL JOIN отличаются тем, какие несовпавшие строки сохраняются.

Пример: заказ на 1 000 рублей связан с двумя платежами по 600 и 400. После соединения на уровне платежей сумма заказа повторится дважды; SUM(OrderAmount) даст 2 000. Это ошибка детализации, которую нельзя надёжно исправить общим DISTINCT: одинаковые суммы могут принадлежать разным заказам.

Сначала выберите нужную детализацию результата. Затем агрегируйте платежи до заказа либо храните заказы и платежи отдельными таблицами фактов. Проверяйте количество уникальных ключей и контрольные суммы на каждом этапе.

Условие по правой таблице в WHERE после LEFT JOIN может удалить строки без совпадений и изменить смысл соединения. Когда нужно сохранить все левые строки, размещение фильтра в ON и WHERE надо выбирать осознанно.

Фильтры дат и сортировка

Для периода с датой и временем удобно использовать полуоткрытый интервал: начало включено, конец исключён. Например, для августа 2026 года условие по существующей таблице dbo.Sales с полем SaleDate выглядит так:

SELECT OrderId, SaleDate, Amount
FROM dbo.Sales
WHERE SaleDate >= CONVERT(date, '20260801', 112)
  AND SaleDate < CONVERT(date, '20260901', 112);

Так не приходится угадывать последнюю долю секунды месяца. BETWEEN включает обе границы: дата 31 августа без времени не означает все часы этого дня. По возможности фильтруйте само поле диапазоном, не оборачивая его в функцию: это часто помогает использованию индекса, но результат проверяют по плану.

DATEDIFF считает пересечённые границы указанной единицы времени. Например, между 23:59 и 00:01 следующего дня DATEDIFF(day, …) возвращает 1, хотя прошло две минуты. Для длительности задачи выберите нужную точность и единицу. Справка DATEDIFF.

ORDER BY задаёт порядок результата конкретного запроса. Кластерный индекс не гарантирует порядок выдачи SELECT без ORDER BY, а сортировка исходного запроса не определяет автоматически сортировку визуализации Power BI.

Текстовые функции и подзапросы

CHARINDEX ищет позицию подстроки, SUBSTRING извлекает часть строки, REPLACE заменяет вхождения, CONCAT соединяет значения. LEN исключает пробелы в конце, поэтому это не универсальная проверка полного размера хранимой строки. DATALENGTH измеряет байты и решает другую задачу. Справка LEN.

Поиск LIKE ‘%текст%’ отличается по возможностям оптимизации от поиска по префиксу. Сравнение текста зависит от collation: учитывайте регистр, акценты и правила сортировки. Не изменяйте collation всей базы ради одного отчёта без проверки последствий.

Скалярный подзапрос, используемый после знака равенства, должен вернуть не больше одной строки. Для проверки принадлежности набору рассматривайте IN или EXISTS, а для связи наборов — JOIN. Подзапрос сам по себе не быстрее соединения: сравнивайте план и фактические чтения.

Представления, процедуры и триггеры

Обычное представление сохраняет определение запроса и предоставляет его как табличный объект. Оно не сохраняет вычисленный результат и не гарантирует ускорение. Индексированное представление материализует результат, но имеет ограничения и стоимость сопровождения при изменении базовых таблиц. Типы представлений SQL Server.

Хранимая процедура содержит T-SQL и вызывается через EXEC/EXECUTE. В синтаксисе CREATE PROCEDURE параметры объявляются после имени; обязательных круглых скобок вокруг списка параметров, как у функции, нет. Нельзя выбрать имя произвольной процедуры в навигаторе как обычную таблицу. Получение её результата через native SQL в Import требует проверки возвращаемой схемы и обновления; для DirectQuery такой путь не подходит.

DML-триггер выполняется в SQL Server в связи с изменением данных. Один оператор может затронуть много строк: логика должна обрабатывать весь набор inserted/deleted. Триггер не является встроенной командой обновления Power BI. Вызов внешнего сервиса внутри транзакции также связывает запись в базу с сетью и доступностью этого сервиса. Для BI обычно удобнее отдельный процесс загрузки, расписание или оркестратор. Обработка нескольких строк в триггере.

Транзакции и резервные копии: что не перепутать

BEGIN TRANSACTION начинает явную транзакцию, COMMIT фиксирует её, ROLLBACK отменяет. Точка сохранения создаётся командой SAVE TRANSACTION SaveName; откат к ней в T-SQL — ROLLBACK TRANSACTION SaveName. Команда ROLLBACK TO SAVEPOINT относится к другому синтаксису. Откат к точке не равен восстановлению всей базы из резервной копии. Правила SAVE TRANSACTION.

Обычный BACKUP DATABASE по умолчанию делает полную резервную копию. WITH FORMAT означает создание нового набора носителей и перезаписывает существующие заголовки и наборы резервных копий; это не переключатель «полный бэкап». WITH REPLACE при восстановлении тоже нельзя добавлять в шаблон как безусловно необходимую опцию. Документация BACKUP.

Процедуру резервного копирования и пробного восстановления определяет администратор. Экспорт CSV и файл PBIX не заменяют резервную копию исходной базы. Для аналитика важно знать, на какой копии или витрине разрешено работать и как восстанавливается процесс после сбоя.

Индексы и производительность: измеряйте конкретный запрос

Индекс выбирают под фильтры, соединения и сортировку. Он занимает место и требует обслуживания при изменениях данных; индексирование всех столбцов не является оптимизацией. Уникальность — свойство индекса, возможное и для кластерного, и для некластерного варианта. «Покрывающий» описывает соответствие индекса конкретному запросу: нужные данные доступны без дополнительных обращений к строкам таблицы.

1. Найдите медленный визуал через Performance Analyzer в Power BI.
2. Определите запрос и посмотрите план выполнения в SQL Server.
3. Измерьте длительность, логические чтения и влияние параллельной нагрузки.
4. Проверьте фильтры, статистику, ключи соединений, индексы и объём результата.
5. После изменения повторите замер на сопоставимых данных и убедитесь, что итоги не изменились.

Для диагностики SQL Server используют планы, Query Store, DMV и Extended Events. SQL Trace и SQL Server Profiler объявлены устаревшими; не делайте Profiler единственным рекомендуемым средством нового процесса. Статус Profiler и альтернативы.

Query folding позволяет передать поддерживаемые преобразования источнику. Это не гарантировано для каждой операции. Удаляйте лишние поля и фильтруйте историю рано, проверяйте фактическую передачу условий. Не добавляйте DISTINCT только ради обещания ускорения: сортировка или агрегация сама может быть дорогой и изменить смысл результата. Рекомендации по DirectQuery.

Публикация, шлюз и права зрителя

Если Power BI service не может напрямую обратиться к SQL Server в нужной сети, настройте подходящее подключение через шлюз. Машина шлюза должна видеть сервер; учётные данные и привязку источника настраивают в сервисе. Работающий Desktop не доказывает, что путь доступен шлюзу.

Для Import настройте расписание и проверьте историю обновлений. Кнопка Refresh в Desktop обновляет локальную модель; она не создаёт серверное расписание. Для DirectQuery проверьте доступность источника при запросах зрителей и подходящую схему аутентификации.

Права в исходной базе не переносятся автоматически на каждую строку импортированной модели. Если загрузка выполняется одной учётной записью, ограничение данных зрителям нужно спроектировать отдельно, например через RLS. Проверяйте доступ под пользователем с ролью зрителя, а не только под владельцем рабочей области.

Перед передачей отчёта зафиксируйте контрольные итоги, свежесть данных, владельца подключения, обработку ошибок и ожидаемое время обновления. Затем проверьте изменение одной тестовой записи от источника до отчёта. Это подтверждает, что работает весь процесс, а не только окно подключения.