Excel DAX - обновление данных в модели данных

DAX используется для вычислений с данными в модели данных в Excel Power Pivot. DAX позволяет эффективно выполнять моделирование данных и отчеты. Однако для этого необходимо время от времени обновлять данные в модели данных, чтобы они отражали текущие данные.

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

Различные способы обновления данных в модели данных

Вы можете обновить данные в модели данных следующими способами:

Время от времени обновлять данные в модели данных.
Внесение изменений в источники данных, например в свойства соединения.
Обновление данных в модели данных после изменения исходных данных.
Фильтрация данных для выборочной загрузки строк из таблицы в источнике данных.

Обновление данных в модели данных

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

Обратите внимание, что добавление данных, изменение данных или редактирование фильтров всегда запускает пересчет формул DAX, которые зависят от этого источника данных. Обратитесь к главе - Пересчет формул DAX для получения подробной информации.

У вас есть два типа обновления данных в модели данных:

Ручное обновление

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

Автоматическое или запланированное обновление

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

Обновление существующего источника данных вручную

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

Если вы импортировали данные из реляционного источника данных, такого как SQL Server и Oracle, вы можете обновить все связанные таблицы за одну операцию. Операция загрузки новых или обновленных данных в модель данных часто запускает пересчет формул DAX, для выполнения обеих из которых может потребоваться некоторое время. Следовательно, вы должны знать о потенциальном воздействии, прежде чем изменять источники данных или обновлять данные, полученные из источника данных.

Чтобы обновить данные для одной таблицы или всех таблиц в модели данных, выполните следующие действия:

Щелкните вкладку «Главная» на ленте в окне Power Pivot.
Щелкните Обновить.
Нажмите «Обновить» в раскрывающемся списке, чтобы обновить выбранную таблицу.
Нажмите «Обновить все» в раскрывающемся списке, чтобы обновить все таблицы.

Чтобы обновить данные для всех таблиц, которые используют одно и то же соединение в модели данных, выполните следующие действия:

Щелкните вкладку «Главная» на ленте в окне Power Pivot.
Щелкните Существующие подключения в группе Получить внешние данные.

Появится диалоговое окно «Существующие подключения».

Выберите соединение.
Щелкните кнопку Обновить.

Появляется диалоговое окно «Обновление данных», и отображается информация о ходе обновления данных, когда механизм PowerPivot перезагружает данные из выбранной таблицы или из всех таблиц из источника данных.

Есть три возможных исхода -

Success - Отчеты о количестве строк, импортированных в каждую таблицу.
Error- Ошибка может возникнуть, если база данных отключена, у вас больше нет прав. Таблица или столбец удаляются или переименовываются в источнике.
Cancelled - Это означает, что Excel не выдал запрос на обновление, вероятно, потому, что обновление отключено для соединения.

Нажмите кнопку "Закрыть".

Изменение источника данных

Чтобы изменить данные в вашей модели данных, вы можете изменить информацию о подключении или обновить определение таблиц и столбцов, используемых в вашей модели данных, в окне Power Pivot.

Вы можете внести следующие изменения в существующие источники данных -

Подключения

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

Столы

Добавьте или удалите фильтр данных.
Измените критерии фильтра.
Добавить или удалить таблицы.
Измените имена таблиц.
Измените сопоставления между таблицами в источнике данных и таблицами в модели данных.
Выберите разные столбцы из источника данных.

Столбцы

Измените имена столбцов.
Добавить новые столбцы.
Удалить столбцы из модели данных (не влияет на источник данных).

Вы можете редактировать свойства существующего источника данных следующими способами:

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

Изменение подключения к существующему источнику данных

Вы можете изменить созданное вами соединение с внешним источником данных, изменив внешний источник данных, используемый текущим соединением. Однако процедура, которой необходимо следовать, зависит от типа источника данных.

Щелкните вкладку «Главная» на ленте в окне PowerPivot.
Щелкните Существующие подключения в группе Получить внешние данные.

Появится диалоговое окно «Существующие подключения». Выберите соединение, которое вы хотите изменить.

В зависимости от типа изменяемого источника данных поставщик может быть другим. Также могут потребоваться изменения доступных свойств. Рассмотрим простой пример подключения к книге Excel, содержащей данные.

Щелкните кнопку Edit. Откроется диалоговое окно «Редактировать соединение».
Нажмите кнопку «Обзор», чтобы найти другую базу данных того же типа (в данном примере книга Excel), но с другим именем или расположением.
Щелкните кнопку Открыть.

Будет выбран новый файл. Появится сообщение о том, что вы изменили информацию о подключении, и вам необходимо сохранить и обновить таблицы, чтобы проверить подключение.

Щелкните кнопку Сохранить. Вы вернетесь в диалоговое окно «Существующие подключения».
Щелкните кнопку Обновить. Появится диалоговое окно «Обновление данных», в котором отображается ход обновления данных. Будет отображен статус обновления данных. Обратитесь к разделу -Manually Refreshing an Existing Data Source для подробностей.
После успешного обновления данных нажмите «Закрыть».
Нажмите кнопку «Закрыть» в диалоговом окне «Существующие подключения».

Редактирование сопоставлений таблиц и столбцов (привязки)

Чтобы изменить сопоставления столбцов при изменении источника данных, выполните следующие действия:

Щелкните вкладку, содержащую таблицу, которую вы хотите изменить, в окне Power Pivot.
Щелкните вкладку «Дизайн» на ленте.
Щелкните Свойства таблицы.

Откроется диалоговое окно «Изменить свойства таблицы».

Вы можете наблюдать следующее -

Имя выбранной таблицы в модели данных отображается в поле «Имя таблицы».
Имя соответствующей таблицы во внешнем источнике данных отображается в поле Имя источника.
Есть два варианта имен столбцов из - Исходный и Модальный.
Если столбцы имеют разные имена в источнике данных и в модели данных, вы можете переключаться между двумя наборами имен столбцов, выбирая эти параметры.
В диалоговом окне появится предварительный просмотр выбранной таблицы.

Вы можете отредактировать следующее -

Чтобы изменить таблицу, которая используется в качестве источника данных, выберите таблицу, отличную от выбранной в раскрывающемся списке «Имя источника».
При необходимости измените сопоставления столбцов -
- Чтобы добавить столбец, который присутствует в источнике, но отсутствует в модели данных, установите флажок рядом с именем столбца. Повторите для всех столбцов, которые нужно добавить. Фактические данные будут загружены в модель данных при следующем обновлении.
- Если некоторые столбцы в модели данных больше не доступны в текущем источнике данных, в области уведомлений появляется сообщение, в котором перечислены недопустимые столбцы. Вам не нужно ничего делать.
Щелкните кнопку Сохранить.

Когда вы сохраните текущий набор свойств таблицы, вы получите сообщение - Подождите. Затем будет отображено количество полученных строк.

В таблице в модели данных все недопустимые столбцы автоматически удаляются и добавляются новые столбцы.

Изменение имени столбца и типа данных

Вы можете изменить имя столбца в таблице в модели данных следующим образом:

Дважды щелкните заголовок столбца. Название столбца в заголовке будет выделено.
Введите новое имя столбца, заменив старое имя. Кроме того, вы можете изменить имя столбца в таблице в модели данных следующим образом:
Выберите столбец, щелкнув его заголовок.
Щелкните столбец правой кнопкой мыши.
В раскрывающемся списке нажмите «Переименовать столбец».

Название столбца в заголовке будет выделено. Введите новое имя столбца, заменив старое имя.

Как вы узнали, все значения в столбце таблицы в модели данных должны быть одного типа данных.

Чтобы изменить тип данных столбца, выполните следующие действия:

Выберите столбец, который вы хотите изменить, щелкнув его заголовок.
Щелкните вкладку «Главная» на ленте.
Щелкните элементы управления в группе «Форматирование», чтобы изменить тип и формат данных столбца.

Добавление / изменение фильтра к источнику данных

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

Добавление фильтра к источнику данных во время импорта

Чтобы добавить новый фильтр к источнику данных во время импорта данных, выполните следующие действия:

Щелкните вкладку «Главная» на ленте в окне Power Pivot.
Щелкните один из источников данных в группе Получить внешние данные.

Появится диалоговое окно мастера импорта таблиц.

Перейдите к шагу - Выбрать таблицы и представления.
Выберите таблицу и нажмите «Предварительный просмотр и фильтр».

Откроется диалоговое окно «Предварительный просмотр выбранной таблицы».

Щелкните столбец, к которому вы хотите применить фильтр.
Щелкните стрелку вниз справа от заголовка столбца.

Чтобы добавить фильтр, выполните одно из следующих действий -

В списке значений столбца выберите или удалите одно или несколько значений для фильтрации, а затем нажмите OK.

Однако, если количество значений очень велико, отдельные элементы могут не отображаться в списке. Вместо этого вы увидите сообщение «Слишком много элементов для отображения».
Щелкните Числовые фильтры или Текстовые фильтры (в зависимости от типа данных столбца).
- Затем щелкните одну из команд оператора сравнения (например, «Равно») или щелкните «Пользовательский фильтр». В диалоговом окне «Пользовательский фильтр» создайте фильтр и нажмите «ОК».

Note - Если вы допустили ошибку на любом этапе, нажмите кнопку «Очистить фильтры строк» и начните заново.

Щелкните ОК. Вы вернетесь на страницу «Выбор таблиц и представлений» мастера импорта таблиц.

Как видите, в столбце «Сведения о фильтре» для столбца, для которого вы определили фильтр, отображается ссылка «Примененные фильтры».

Вы можете щелкнуть ссылку, чтобы просмотреть выражение фильтра, созданное мастером. Но синтаксис каждого выражения фильтра зависит от поставщика, и вы не можете его редактировать.

Нажмите Готово, чтобы импортировать данные с примененными фильтрами.
Закройте мастер импорта таблиц.

Изменение фильтра на существующий источник данных

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

Щелкните вкладку «Главная» на ленте в окне Power Pivot.
Щелкните Существующие подключения в группе Получить внешние данные. Появится диалоговое окно «Существующие подключения».
Щелкните соединение, содержащее таблицу, для которой необходимо изменить фильтр.
Щелкните кнопку Открыть.

Вы попадете в диалоговое окно мастера импорта таблиц. Повторите шаги из предыдущего раздела, чтобы отфильтровать столбцы.