Как установить power pivot
Надстройки Power Query и Power Pivot
Power Query и Power Pivot — надстройки Excel для легкой работы с тяжелыми файлами. С помощью Power Query можно подключать и обрабатывать источники информации произвольного вида, а в Power Pivot – выполнять сложные расчеты и создавать модель данных.
Надстройка Power Query
Появление Power Query – одно из важных событий в истории развития Excel. То, что раньше пользователи делали в Excel с помощью рутинных операций по преобразованию данных, писали огромные формулы или макросы на VBA, теперь можно выполнять буквально в несколько щелчков мышкой.
Power Query – это надстройка для создания запросов по импорту, очистке и преобразованию данных.
Как работает
В привычном нам виде Power Query впервые появился в Excel 2013. В Excel 2010 и 2013 надстройка отображается в виде отдельной вкладки «Power Query».
В Excel 2016 надстройка уже встроена по умолчанию, на вкладке Данные – раздел «Скачать и преобразовать».
В версии Excel 2019 на вкладке Данные разделы с Power Query — «Получить и преобразовать данные» и «Запросы и подключения».
Возможности Power Query
Операции по преобразованию данных выполняются в окне редактора запросов.
В редакторе Power Query пользователь работает с данными – выполняет преобразования, а его действия автоматически записываются в виде шагов запроса.
Как скачать Power Query
Надстройка устанавливается бесплатно и доступна для Excel начиная с версий 2010 года:
Надстройка Power Pivot
Power Pivot — это надстройка Excel, в которой можно выполнять улучшенные вычисления для сводных таблиц с помощью DAX-формул. Power Pivot значительно расширяет функционал сводных таблиц и позволяет работать с большими объемами данных – в десятки миллионов строк.
Как работает
Возможности Power Pivot
Кстати, в Power Pivot есть свои инструменты для импорта данных, но из небольшого числа источников – некоторые базы данных, файлы Excel и текстовые и др. Эти инструменты можно найти на вкладке Главная. Так как доступных источников данных не очень много, то, как правило, в качестве источника для него выступает Power Query, функционал которого по подключению намного больше.
Увидеть все загруженные данные можно в нижней части окна Power Pivot – ярлычки с названиями таблиц находятся там же, где названия листов в «обычном» Excel.
Надстройка для Excel — Power Pivot, или жизнь после 1 048 576 строк
Как показывает практика, если в файле Excel больше 50 тысяч строк, да еще формулы типа ВПР, он падает и умирает. Потом восстает, как зомби, чтобы выпить нашу кровь и нервы. Ведет он себя тоже как зомби — еле двигается и «ни черта» не соображает.
Что же делать? Ответ простой: начать работать с надстройкой для Excel — Power Pivot. Этот инструмент создан для работы с данными. Он может легко обрабатывать миллионы строк!
Надстройка Power Pivot в Excel
Power Pivot – это надстройка Excel, с помощью которой можно работать с данными в несколько миллионов строк, объединять таблицы в модель данных и создавать аналитические вычисления.
В «обычном» Excel пользователи ограничены количеством строк в таблице – не более размера листа в 1 048 тысяч строк, но в Power Pivot такого ограничения нет. Надстройка может подключаться к данным из внешних источников и работать с большими объемами информации в миллионы строк.
Открыть надстройку Power Pivot можно, нажав на вкладке меню Power Pivot кнопку Управление. Эта вкладка выглядит одинаково во всех версиях Excel.
Хорошая новость: начиная c версий после 2019 года компания Microsoft анонсировала включение Power Pivot во все версии Excel.
Работа с данными в Power Pivot
Как правило, разработка отчетов в Power Pivot происходит в следующем порядке:
Подключения к источникам, связи и вычисления настраиваются в отчете один раз. При изменении исходных данных отчеты можно обновить в меню Данные → Обновить все. Давайте разберем подробнее, как это работает.
Добавление данных в Power Pivot
Чтобы начать работать с Power Pivot, перейдите на вкладку меню Power Pivot → нажмите Управление. Добавить данные в открывшейся надстройке можно несколькими способами:
Способ 1. Подключение к данным с помощью встроенных инструментов импорта.
В Power Pivot есть свои инструменты для импорта внешних данных, которые можно найти на вкладке Главная → кнопки Из базы данных, Из службы данных, Из других источников.
С помощью встроенных инструментов настраивается подключение к 15 видам источников данных.
Увидеть весь список можно в окне «Мастер импорта таблиц», которое открывается в меню Главная → Из других источников.
Настроим подключение к данным на примере файла Excel. Укажите путь к файлу, поставьте галочку «Использовать первую строку в качестве заголовков столбцов», выберите таблицы, жмем «Готово». У вас в окне включится счетчик импорта строк — работает довольно быстро. В результате импорта в окне Power Pivot появятся вкладки с таблицами.
Способ 2. Добавить данные из Power Query.
Загрузка данных с помощью инструментов Power Pivot делается легко, но Power Query лучше подходит для импорта и значительно расширяет возможности аналитики. В нем намного больше доступных источников и возможностей для обработки таблиц произвольного вида.
Чтобы настроить подключение с помощью Power Query, вам нужно создать запрос к источнику данных. Список ранее созданных запросов находится на вкладке «Запросы и подключения». Нажмите на запрос правой кнопкой мышки и выберите Загрузить в… В открывшемся окне доступных вариантов импорта поставьте галочку «Добавить эти данные в модель данных». Задать настройки импорта также можно в самом редакторе Power Query.
К сожалению, в Excel 2010 Power Pivot почти невозможно «подружить» с Power Query и этот новый функционал в старом Excel сильно ограничен.
Интерфейс Power Pivot
Разберем подробнее интерфейс Power Pivot.
В окне Power Pivot есть:
Модель данных и связи
Чтобы перейти к настройке связей между таблицами, выберите в меню Главная → Представление диаграммы (вернутся обратно к просмотру таблиц можно, нажав Представление данных).
Модель данных в Power Pivot – это набор таблиц, объединенных связями.
Графически связь таблиц обозначается линией между ними, как в примере на рисунке. Чтобы создать связь, выделите мышкой поле в одной таблице и «перетащите» его на соответствующее ему поле в области другой таблицы.
Power Pivot поддерживает типы связей «один к одному», «один ко многим».
Если выделить мышкой линию связи в модели данных, то можно увидеть, с помощью каких полей задана связь. Выделенные линии можно удалять. Или, щелкнув по ним дважды, менять связи в открывшемся окне. Также управление связями доступно в окне, которое открывается в меню Конструктор → Управление связями.
Вычисления в Power Pivot
Формулы Power Pivot пишут на языке DAX (Data Analysis Expressions, выражения для анализа данных). DAX-формулы позволяют, по аналогии с формулами Excel, выполнять вычисления и/или настраивать произвольную фильтрацию и представление данных в таблицах.
Язык DAX впервые появился в 2010 году вместе с надстройкой Power Pivot. В этом языке сотни функций, с помощью которых можно создавать аналитические расчеты. Кроме Power Pivot в Excel, DAX-формулы также доступны в Power BI и Analysis Services. То есть эти формулы вам точно пригодятся.
Вычисления с помощью DAX-формул создаются в виде:
Вычисляемые столбцы представляют собой столбцы в таблицах данных, созданные с помощью формул. Чтобы добавить такой столбец, щелкните мышкой дважды по столбцу слева «Добавление столбца», введите название вычисления, а затем знак «=» и формулу в строке формул.
Вычисляемый столбец похож на любой другой столбец в таблице. Также с помощью таких столбцов можно создавать связи в модели. Вычисляемые столбцы в Excel рассчитываются в момент загрузки (обновления) данных, а значения в самом столбце сохраняются, увеличивая размер файла. То есть столбцы – это статические вычисления.
Как и вычисляемые столбцы, меры тоже создаются с помощью DAX-формул. В отличие от столбцов, меры – это динамические вычисления, результат которых зависит от контекста – его можно увидеть в отчете, где мы задаем в разрезе каких полей, фильтров и др. вычислить меру. Меры записываются под таблицей в области вычислений.
Меры в Power Pivot можно превратить в KPI – ключевые показатели эффективности. Для этого выделите меру и нажмите на кнопку Создать KPI в меню Главная. Кроме мер, созданных пользователями, в Excel также есть неявные меры. Они создаются автоматически при формировании сводной таблицы, когда пользователь помещает данные в область значений. Чтобы посмотреть, есть ли у вас в Power Pivot неявные меры, выберите на вкладке Главная → Показать скрытые.
Start the Power Pivot add-in for Excel
Power Pivot is an add-in that you can use to perform powerful data analysis in Excel. The add-in is built into certain versions of Office, but by default, it’s not enabled.
For a list of the versions of Office that include Power Pivot, as well as a list of the versions that do not, please see: Where is Power Pivot?
Here’s how you enable Power Pivot before using it for the first time.
Go to File > Options > Add-Ins.
In the Manage box, click COM Add-ins> Go.
Check the Microsoft Office Power Pivot box, and then click OK. If you have other versions of the Power Pivot add-in installed, those versions are also listed in the COM Add-ins list. Be sure to select the Power Pivot add-in for Excel.
The ribbon now has a Power Pivot tab.
Open the Power Pivot window
This is the tab where you work with Power Pivot PivotTables, calculated fields, and key performance indicators (KPIs), and creating linked tables.
Now you’re in the Power Pivot window. Here you can click Get External Data to use the Table Import Wizard to filter data as you add it to your file, create relationships between tables, enrich the data with calculations and expressions, and then use this data to create PivotTables and PivotCharts.
Troubleshooting: Power Pivot ribbon disappears
In rare cases, the Power Pivot ribbon will disappear from the menu if Excel determines that the add-in is destabilizing to Excel. This might occur if Excel closes unexpectedly while the Power Pivot window is open. To restore the Power Pivot menu, do the following:
Go to File > Options > Add-Ins.
In the Manage box, click Disabled Items > Go.
Select Microsoft Office Power Pivot and then click Enable.
If the previous steps do not restore the Power Pivot ribbon, or if the ribbon disappears when you close and reopen Excel, try the following:
Point to Start > Run and then type regedit.
In Registry Editor, expand this registry key:
If you have Excel 2013: HKEY_CURRENT_USER > Software > Microsoft > Office > 15.0 > User Settings.
If you have Excel 2016: HKEY_CURRENT_USER > Software > Microsoft > Office > 16.0 > User Settings
Right-click PowerPivotExcelAddin and then click Delete.
Scroll back up to the top of Registry Editor.
Expand HKEY_CURRENT_USER > Software > Microsoft > Office > Excel > Addins.
Right-click PowerPivotExcelClientAddIn.NativeEntry.1 and then click Delete.
Close Registry Editor.
Enable the add-in using the steps at the top of this article.
Power Pivot для Excel: что это такое и как его использовать
Добавьте таблицы поиска в свои наборы данных с помощью этого изобретательного дополнения
У вас есть данные и их много. Если вы хотите проанализировать все эти данные, узнайте, как использовать надстройку Power Pivot с Excel для импорта наборов данных, определения связей, создания сводных таблиц и создания сводных диаграмм.
Инструкции в этой статье применяются к Excel 2019, 2016, 2013 и Excel для Office 365.
Как получить надстройку Excel Power Pivot
Power Pivot предоставляет вам мощное приложение для анализа и анализа бизнес-данных. Вам не нужно специализированное обучение для разработки моделей данных и выполнения расчетов. Вам просто нужно включить его, прежде чем вы сможете его использовать.
Следуйте вместе с учебником
Если вы хотите быстро приступить к работе с Power Pivot, учитесь на примере. У Microsoft есть несколько примеров наборов данных, доступных для бесплатной загрузки, которые содержат необработанные данные, модель данных и примеры анализа данных. Это отличные инструменты обучения, которые позволяют понять, как профессионалы анализируют большие данные.
В этом руководстве используется образец рабочей книги Microsoft Student Data Model. В первой заметке на странице вы найдете ссылку для загрузки учебного пособия и заполненной модели данных.
Данные в этом образце книги Excel имеют следующее:
Есть другие примеры наборов данных на веб-сайте Microsoft. Изучите эти учебные ресурсы:
Перед использованием любого набора данных очистите его. Используйте функцию CLEAN в Excel, чтобы избавиться от непечатных символов, запустить проверку орфографии, удалить дублирующиеся строки данных, преобразовать числа и даты в нужный формат и перегруппировать данные.
Как добавить данные в файл Excel и построить модель данных
Вы собрали данные, которые вам нужны. Теперь пришло время импортировать ваши наборы данных в Excel и автоматически создать модель данных. Модель данных похожа на реляционную базу данных и предоставляет табличные данные, используемые в сводных таблицах и сводных диаграммах.
Если вам нужны данные для школьного задания, рабочего проекта или для выполнения этого учебного руководства, вы найдете отличные общедоступные наборы данных на GitHub.
Чтобы импортировать данные Excel в модель данных Power Pivot:
Откройте пустой лист и сохраните файл с уникальным именем.
В Excel 2013 выберите Power Query > Получить внешние данные и выберите источник данных.
Выберите таблицы, которые вы хотите импортировать.
При импорте двух или более таблиц Excel автоматически создает модель данных.
Окно Power Pivot отображает ваши данные в формате рабочего листа и состоит из трех основных областей: таблица данных, область расчета и вкладки таблицы данных.
Вкладки в нижней части окна Power Pivot соответствуют каждой из импортированных таблиц.
Закройте окно Power Pivot.
Создание связей между таблицами с помощью Power Pivot Excel
Теперь, когда у вас есть модель данных, пришло время создать отношения между каждой из таблиц данных.
Импортированные таблицы отображаются в виде отдельных блоков в представлении схемы. Перетащите, чтобы переместить таблицы в другое место. Перетащите угол окна, чтобы изменить его размер.
Перетащите заголовок столбца из одной таблицы в другую или в таблицы, содержащие одинаковый заголовок столбца.
Продолжайте сопоставлять заголовки столбцов.
Как создавать сводные таблицы
Когда вы используете Power Pivot для создания модели данных, большая часть тяжелой работы, включающей сводные таблицы и сводные диаграммы, была выполнена для вас. Отношения, которые вы создали между таблицами в вашем наборе данных, используются для добавления полей, которые вы будете использовать для создания сводных таблиц и сводных диаграмм.
На панели Поля сводной таблицы выберите поля, которые нужно добавить в сводную таблицу. В этом примере создается сводная таблица, которая содержит имя учащегося и его среднюю оценку.
Чтобы отсортировать данные сводной таблицы, перетащите поле в область «Фильтры». В этом примере поле «Имя класса» добавляется в область «Фильтры», поэтому список можно отфильтровать, чтобы показать среднюю оценку ученика для класса.
Проанализируйте ваши данные. Поэкспериментируйте с фильтрами и отсортируйте данные, используя стрелки раскрывающегося списка заголовков столбцов.
Преобразовать сводную таблицу в сводную диаграмму
Если вы хотите визуализировать данные сводной таблицы, превратите сводную таблицу в сводную диаграмму.
Создать сводные диаграммы
Если вы предпочитаете анализировать данные в визуальном формате, создайте сводную диаграмму.
Перетащите поля для добавления в сводную диаграмму. В этом примере создается сводная диаграмма, показывающая среднюю оценку для классов, отфильтрованных по семестрам.
Проанализируйте ваши данные. Поэкспериментируйте с фильтрами и отсортируйте данные с помощью стрелок раскрывающегося списка заголовков столбцов.
Создание модели данных с эффективным использованием памяти Excel и надстройки Power Pivot
В Excel 2013 г. или более поздней можно создавать модели данных, содержащие миллионы строк, а затем выполнять с этими моделями мощный анализ данных. Модели данных можно создавать как с использованием надстройки Power Pivot для поддержки любого количества сводных таблиц, диаграмм и визуализации отчетов Power View в одной книге, так и без нее.
Примечание: В этой статье описаны модели данных Excel 2013. Тем не менее те же функции моделирования данных и Power Pivot, Excel 2013, также относятся к Excel 2016. Между этими версиями Excel фактически нет Excel.
Хотя вы можете легко создавать большие модели данных в Excel данных, есть несколько причин не делать этого. Во-первых, большие модели, содержащие большое количество таблиц и столбцов, являются неоценимыми для большинства анализа и являются утомительным списком полей. Во-вторых, большие модели используют ценную память, что отрицательно влияет на другие приложения и отчеты, в которые используются те же ресурсы системы. Наконец, в Microsoft 365, как SharePoint Online, так и Excel Web App, размер файла Excel 10 МБ. Для моделей данных книги, содержащих миллионы строк, достаточно быстро вы получите ограничение в 10 МБ. См. спецификацию и ограничения модели данных.
Из этой статьи вы узнаете, как создать тщательно продуманную модель, которая упрощает работу с данными и требует мало памяти. Время, необходимое для того, чтобы изучить методики эффективного проектирования модели, окупится при создании и использовании любой модели независимо от того, просматриваете ли вы ее в Excel 2013, Microsoft 365 SharePoint Online, на сервере Office Web Apps Server или в SharePoint 2013.
Также рекомендуем запустить средство Workbook Size Optimizer. Оно анализирует ваши книги Excel и при возможности дополнительно сжимает их. Скачайте оптимизатор размера книги.
В этой статье
Степень сжатия и аналитический обработчик в памяти
Модели данных в Excel используют аналитический обработчик в памяти для хранения данных в памяти. Этот обработчик применяет мощные технологии сжатия, чтобы уменьшить объем памяти, требуемый для хранения, в результате чего размер результирующего набора получается гораздо меньше исходного.
Вы можете получить модель данных, которая в среднем в 7–10 раз меньше исходной. Например, при импорте данных размером 7 МБ из базы данных SQL Server размер модели данных в Excel может составлять 1 МБ или меньше. Степень сжатия зависит главным образом от количества уникальных значений в каждом столбце. Чем больше уникальных значений, тем больше памяти понадобится для их хранения.
Почему мы говорим о сжатии и уникальных значениях? Поскольку построение эффективной модели, которая позволяет сократить использование памяти, — это максимальное сжатие, и самый простой способ сделать это — избавиться от столбцов, которые вам не нужны, особенно если эти столбцы содержат большое количество уникальных значений.
Примечание: Объем памяти, необходимый для хранения отдельных столбцов, может существенно отличаться. Иногда лучше иметь несколько столбцов с небольшим количеством уникальных значений, чем один столбец, в котором их много. Данный способ подробно описан в разделе об оптимизации столбцов даты и времени.
Сокращение количества столбцов для эффективного использования памяти
Меньше всего памяти занимает столбец, который вы не импортировали. Чтобы построить эффективную модель, определите значимость каждого столбца для необходимого вам анализа. Если столбец не играет большой роли, не импортируйте его. В случае необходимости вы всегда сможете добавить столбцы в дальнейшем.
Два примера столбцов, которые не нужно импортировать
Первый пример касается данных из хранилищ. В хранилищах данных всегда можно найти артефакты процессов извлечения, преобразования и загрузки, с помощью которых выполняются обновление и загрузка данных. Столбцы типа «Дата создания», «Дата обновления» и «Запуск ETL» создаются при загрузке данных. Они не нужны для построения модели, и их не следует выбирать при импорте данных.
Второй пример касается исключения столбца первичного ключа при импорте фактической таблицы.
Многие таблицы, в том числе и фактические, содержат первичные ключи. Их наличие необходимо для большинства таблиц (например, для тех, которые содержат данные о клиентах, сотрудниках или продажах), так как первичные ключи используются для создания связей внутри модели.
С фактическими таблицами дело обстоит иначе. В таких таблицах первичные ключи делают уникальной каждую строку. Это важно для нормализации, но совсем ни к чему в модели данных, где нужны только те столбцы, которые используются для анализа или установления связей между таблицами. В связи с этим рекомендуется исключать первичный ключ при импорте данных из фактической таблицы. Занимая много места в модели, первичные ключи в фактических таблицах не приносят пользы, так как они не участвуют в создании связей.
Примечание: В хранилищах и многомерных базах данных большие таблицы, содержащие преимущественно числовые данные, часто называют таблицами фактов. Как правило, таблицы фактов содержат данные о производительности предприятия или финансовых операциях (например, точки данных о продажах и затратах, которые группируются по подразделениям, продуктам, рыночным сегментам, географическим регионам и т. д.). Для поддержки анализа данных в модель следует включать все столбцы таблицы фактов, которые содержат бизнес-данные или могут использоваться для создания перекрестных ссылок на данные других таблиц. Исключается лишь столбец первичного ключа, который содержит уникальные значения, существующие только в данной таблице фактов. Из-за большого размера таблиц фактов исключение из них строк или столбцов способствует созданию эффективной модели данных.
Исключение ненужных столбцов
Эффективные модели данных содержат только те столбцы, которые действительно используются в книге. Если вы хотите контролировать, какие столбцы включаются в модель, для импорта данных вместо диалогового окна «Импорт данных» в Excel следует использовать мастер импорта таблиц в надстройке Power Pivot.
Запустив его, вы сможете выбирать таблицы для импорта.
Нажав кнопку «Просмотр и фильтр», можно выбрать необходимые элементы для каждой таблицы. Мы рекомендуем сначала снять флажки со всех столбцов, а затем установить их рядом с теми, которые необходимы для анализа.
Фильтрация необходимых строк
Многие таблицы в корпоративных базах данных и хранилищах содержат ретроспективные данные за длительный период времени. Кроме того, необходимо выяснить, содержат ли интересующие вас таблицы бизнес-данные, не отвечающие целям вашего анализа.
С помощью мастера импорта таблиц можно исключить ретроспективные или несвязанные данные, освободив таким образом место в модели. На рисунке ниже показан пример использования фильтра данных для извлечения строк, содержащих данные за текущий год, исключая ненужные ретроспективные данные.
Можно ли уменьшить объем памяти, необходимый для хранения столбца?
Существует несколько способов уменьшения размера столбца при сжатии. Как вы помните, единственным свойством столбца, влияющим на сжатие, является количество уникальных значений. В этом разделе вы узнаете, как можно изменить столбцы, чтобы уменьшить количество уникальных значений.
Изменение столбцов даты и времени
В большинстве случаев столбцы даты и времени занимают много места. К счастью, существует несколько способов уменьшения объема памяти, необходимого для хранения этого типа данных. Выбор способа зависит от того, как используется столбец, и удобства построения SQL-запросов.
Столбцы даты и времени содержат часть данных даты и часть данных времени. Выбирая нужные столбцы, тщательно продумайте следующие вопросы относительно столбца даты и времени:
нужны ли вам данные времени;
Нужна ли часть времени на уровне часов? Минут? Секунд? Миллисекунд?
зачем вам нужны несколько столбцов даты и времени (чтобы вычислить между ними разницу, или просто чтобы сгруппировать данные по году, месяцу, кварталу и т. п.).
Ответы на эти вопросы определят способ изменения столбца даты и времени.
Все эти способы требуют изменения SQL-запроса. Чтобы упростить эту задачу, следует исключить как минимум один столбец из каждой таблицы. Таким образом, конструкция запроса изменится с сокращенного формата (SELECT *) на оператор SELECT с полными именами столбцов, которые гораздо легче изменить.
Рассмотрим созданные для вас запросы. В диалоговом окне Свойства таблицы можно переключиться в редактор запросов и увидеть текущий SQL запрос для каждой таблицы.
В окне «Свойства таблицы» выберите Редактор запросов.
В редакторе запросов отображается SQL-запрос, используемый для заполнения таблицы. Если вы исключили один из столбцов во время импорта, запрос содержит полные имена столбцов:
Если вы полностью импортировали таблицу, не снимая флажок с какого-либо столбца и не применяя фильтр, запрос будет отображаться в формате «Select * from «, который намного сложнее изменить:
Изменение SQL-запроса
Найдя запрос, вы можете его изменить, чтобы еще уменьшить размер модели.
Если столбец содержит денежные или десятичные данные, используйте такой синтаксис, чтобы исключить ненужные десятичные значения:
Если вы хотите, чтобы отображались копейки с двумя знаками в дробной части, замените 0 на 2. Отрицательные числа можно округлить до единиц, десятков, сотен и т. д.
При наличии столбца даты и времени «dbo.Bigtable.[Date Time]», в котором вам не нужна часть данных времени, используйте такой синтаксис для ее исключения:
«SELECT CAST (dbo.Bigtable.[Date time] as date) AS [Date time]) «
При наличии столбца даты и времени «dbo.Bigtable.[Date Time]», в котором вам не нужны части данных даты и времени, вместо данного столбца используйте в SQL-запросе несколько столбцов:
«SELECT CAST (dbo.Bigtable.[Date Time] as date) AS [Date Time],
datepart(hh, dbo.Bigtable.[Date Time]) as [Date Time Hours],
datepart(mi, dbo.Bigtable.[Date Time]) as [Date Time Minutes],
datepart(ss, dbo.Bigtable.[Date Time]) as [Date Time Seconds],
datepart(ms, dbo.Bigtable.[Date Time]) as [Date Time Milliseconds]»
Используйте столько столбцов, сколько требуется для сохранения каждой части в отдельном столбце.
Если вы хотите, чтобы часы и минуты отображались в одном столбце времени, используйте такой синтаксис:
Timefromparts(datepart(hh, dbo.Bigtable.[Date Time]), datepart(mm, dbo.Bigtable.[Date Time])) as [Date Time HourMinute]
Если у вас есть два столбца даты и времени, например [Start Time] и [End Time], и вам нужно получить разницу между ними в секундах в столбце [Duration], удалите оба столбца из списка, добавив следующее:
«datediff(ss,[Start Date],[End Date]) as [Duration]»
Используя ключевое слово «ms» вместо «ss», вы получите разницу в миллисекундах.
Использование вычисляемых мер DAX вместо столбцов
Если вы раньше уже использовали язык выражений DAX, вам должно быть известно, что вычисляемые столбцы используются для получения новых столбцов на основе определенного столбца в модели, а вычисляемые меры определяются в модели один раз и проверяются только при использовании в сводной таблице или другом отчете.
Один из способов эффективного использования памяти предполагает замену обычных или вычисляемых столбцов вычисляемыми мерами. Классическим примером является наличие столбцов «Цена за единицу», «Количество» и «Всего». Имея три столбца, можно освободить дополнительное место, оставив два из них и вычислив третий с помощью DAX.
Каких 2 столбца нужно оставить?
В приведенном выше примере необходимо оставить столбцы «Количество» и «Цена за единицу». Они содержат меньше значений, чем столбец «Всего». Чтобы вычислить столбец «Всего», добавьте следующую вычисляемую меру:
«TotalSales:=sumx(‘Sales Table’,’Sales Table’[Unit Price]*’Sales Table’[Quantity])»
Вычисляются столбцы, как обычные столбцы, которые обеим сторонам требуется место в модели. С другой стороны, вычисляемая мера вычисляется «на лету» и не отбирает место.
Заключение
В этой статье мы рассмотрели несколько подходов к построению модели с эффективным использованием памяти. Чтобы уменьшить размер файла и объем памяти, требуемый для хранения модели, необходимо уменьшить общее количество строк и столбцов и количество уникальных значений в каждом столбце. Вот краткое описание рассмотренных нами приемов:
удаление столбцов — лучший способ экономии места. Оставляйте только действительно нужные столбцы;
в некоторых случаях можно удалить столбец из таблицы, заменив его вычисляемой мерой;
Возможно, вам не нужны все строки в таблице. Вы можете отфильтровать строки в мастере импорта таблиц.
разбиение столбца на отдельные части позволяет уменьшить количество содержащихся в нем уникальных значений. Каждая часть будет содержать небольшое количество уникальных значений, а их общее количество получится меньше, чем в исходном столбце;
в большинстве случаев отдельные части столбца также приходится использовать в качестве срезов в отчетах. При необходимости вы можете создавать иерархии из частей, таких как часы, минуты и секунды;
как правило, столбцы содержат больше данных, чем требуется. Например, если столбец содержит десятичные значения, вы можете применить форматирование, чтобы их скрыть. Округление помогает значительно уменьшить размер числового столбца.
Теперь, когда вы сделали все возможное, чтобы уменьшить размер книги, также рекомендуем запустить средство Workbook Size Optimizer. Оно анализирует ваши книги Excel и при возможности дополнительно сжимает их. Скачайте оптимизатор размера книги.