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

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

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

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

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

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

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

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

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

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

Обновить вручную

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

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

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

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

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

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

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

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

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

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

Откроется диалоговое окно существующих подключений.

• Выберите соединение.
• Нажмите кнопку Обновить.

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

Есть три возможных результата —

• Успех — отчеты о количестве строк, импортированных в каждую таблицу.

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

• Отменено — это означает, что Excel не выдавал запрос на обновление, возможно, потому что обновление отключено в соединении.

Успех — отчеты о количестве строк, импортированных в каждую таблицу.

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

Отменено — это означает, что Excel не выдавал запрос на обновление, возможно, потому что обновление отключено в соединении.

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

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

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

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

связи

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

таблицы

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

Колонны

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

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

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

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

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

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

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

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

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

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

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

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

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

• Нажмите кнопку Изменить. Откроется диалоговое окно «Редактировать соединение».

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

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

Нажмите кнопку Изменить. Откроется диалоговое окно «Редактировать соединение».

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

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

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

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

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

• Нажмите Закрыть, как только обновление данных будет успешным.

• Нажмите Закрыть в диалоговом окне «Существующие подключения».

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

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

Нажмите Закрыть, как только обновление данных будет успешным.

Нажмите Закрыть в диалоговом окне «Существующие подключения».

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

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

• Выберите вкладку, содержащую таблицу, которую вы хотите изменить, в окне Power Pivot.

• Перейдите на вкладку «Дизайн» на ленте.

• Нажмите Свойства таблицы.

Выберите вкладку, содержащую таблицу, которую вы хотите изменить, в окне Power Pivot.

Перейдите на вкладку «Дизайн» на ленте.

Нажмите Свойства таблицы.

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

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

• Имя выбранной таблицы в модели данных отображается в поле «Имя таблицы».

• Имя соответствующей таблицы во внешнем источнике данных отображается в поле «Имя источника».

• Существует два варианта имен столбцов: «Исходный» и «Модальный».

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

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

Имя выбранной таблицы в модели данных отображается в поле «Имя таблицы».

Имя соответствующей таблицы во внешнем источнике данных отображается в поле «Имя источника».

Существует два варианта имен столбцов: «Исходный» и «Модальный».

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

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

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

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

• При необходимости измените сопоставления столбцов.

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

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

• Нажмите кнопку Сохранить.

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

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

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

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

Нажмите кнопку Сохранить.

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

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

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

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

• Дважды щелкните заголовок столбца. Имя столбца в заголовке будет выделено.

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

• Выберите столбец, нажав на его заголовок.

• Щелкните правой кнопкой мыши столбец.

• Нажмите Переименовать столбец в раскрывающемся списке.

Дважды щелкните заголовок столбца. Имя столбца в заголовке будет выделено.

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

Выберите столбец, нажав на его заголовок.

Щелкните правой кнопкой мыши столбец.

Нажмите Переименовать столбец в раскрывающемся списке.

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

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

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

• Выберите столбец, который вы хотите изменить, щелкнув его заголовок.

• Перейдите на вкладку «Главная» на ленте.

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

Выберите столбец, который вы хотите изменить, щелкнув его заголовок.

Перейдите на вкладку «Главная» на ленте.

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

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

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

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

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

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

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

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

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

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

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

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

  Однако, если количество значений очень велико, отдельные элементы могут не отображаться в списке. Вместо этого вы увидите сообщение — «Слишком много предметов для показа».

• Нажмите Числовые фильтры или Текстовые фильтры (в зависимости от типа данных столбца).

  • Затем щелкните одну из команд оператора сравнения (например, «Равно») или нажмите «Пользовательский фильтр». В диалоговом окне «Пользовательский фильтр» создайте фильтр и нажмите «ОК».

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

Однако, если количество значений очень велико, отдельные элементы могут не отображаться в списке. Вместо этого вы увидите сообщение — «Слишком много предметов для показа».

Нажмите Числовые фильтры или Текстовые фильтры (в зависимости от типа данных столбца).

Затем щелкните одну из команд оператора сравнения (например, «Равно») или нажмите «Пользовательский фильтр». В диалоговом окне «Пользовательский фильтр» создайте фильтр и нажмите «ОК».

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

• Нажмите ОК. Вы вернетесь на страницу выбора таблиц и представлений мастера импорта таблиц.

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

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

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

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

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

• Перейдите на вкладку «Главная» в окне «Лента в Power Pivot».

• Нажмите Существующие подключения в группе Получить внешние данные. Откроется диалоговое окно существующих подключений.

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

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

Перейдите на вкладку «Главная» в окне «Лента в Power Pivot».

Нажмите Существующие подключения в группе Получить внешние данные. Откроется диалоговое окно существующих подключений.

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

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

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