В Excel можно создавать модели данных, содержащие миллионы строк, а затем выполнять эффективный анализ данных для этих моделей. Модели данных можно создавать как с использованием надстройки Power Pivot для поддержки любого количества сводных таблиц, диаграмм и визуализации отчетов Power View в одной книге, так и без нее.
Хотя вы можете легко создать огромные модели данных в Excel, есть несколько причин этого не делать. Во-первых, большие модели, содержащие множество таблиц и столбцов, являются перебором для большинства анализов и создают громоздкий список полей. Во-вторых, крупные модели используют ценную память, негативно влияя на другие приложения и отчеты, которые используют одни и те же системные ресурсы. Наконец, в Microsoft 365 sharePoint Online и Excel Web App ограничивают размер файла Excel до 10 МБ. Для моделей данных книг, содержащих миллионы строк, вы довольно быстро получите ограничение в 10 МБ. См . статью Спецификация и ограничения модели данных.
Из этой статьи вы узнаете, как создать тщательно продуманную модель, которая упрощает работу с данными и требует мало памяти. Изучение рекомендаций по эффективному проектированию моделей окупится в будущем для любой модели, которую вы создаете и используете, независимо от того, просматриваете ли вы ее в Excel, Microsoft 365 SharePoint Online, на Office Web Apps Server или в SharePoint.
Также рекомендуем запустить средство 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-запроса
Найдя запрос, вы можете его изменить, чтобы еще уменьшить размер модели.
-
Если столбец содержит денежные или десятичные данные, используйте такой синтаксис, чтобы исключить ненужные десятичные значения:
"SELECT ROUND([Decimal_column_name],0)… .”
Если вы хотите, чтобы отображались копейки с двумя знаками в дробной части, замените 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 и при возможности дополнительно сжимает их. Скачайте оптимизатор размера книги.
См. также
Спецификации и ограничения модели данных
Power Pivot: мощные средства анализа и моделирования данных в Excel
