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

В 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.

Запустив его, вы сможете выбирать таблицы для импорта.

Мастер импорта таблиц в надстройке PowerPivot

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

Панель просмотра в мастере импорта таблиц

Фильтрация необходимых строк

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

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

Область фильтров в мастере импорта таблиц

Можно ли уменьшить объем памяти, необходимый для хранения столбца?

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

Изменение столбцов даты и времени

В большинстве случаев столбцы даты и времени занимают много места. К счастью, существует несколько способов уменьшения объема памяти, необходимого для хранения этого типа данных. Выбор способа зависит от того, как используется столбец, и удобства построения SQL-запросов.

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

  • нужны ли вам данные времени;

  • Нужна ли часть времени на уровне часов? Минут? Секунд? Миллисекунд?

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

Ответы на эти вопросы определят способ изменения столбца даты и времени.

Все эти способы требуют изменения SQL-запроса. Чтобы упростить эту задачу, следует исключить как минимум один столбец из каждой таблицы. Таким образом, конструкция запроса изменится с сокращенного формата (SELECT *) на оператор SELECT с полными именами столбцов, которые гораздо легче изменить.

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

Лента окна PowerPivot с изображением команды "Свойства таблицы"

В окне "Свойства таблицы" выберите Редактор запросов.

Открытие редактора запросов из диалогового окна "Свойства таблицы"

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

SQL-запрос на извлечение данных

Если вы полностью импортировали таблицу, не снимая флажок с какого-либо столбца и не применяя фильтр, запрос будет отображаться в формате "Select * from ", который намного сложнее изменить:

SQL-запрос с сокращенным синтаксисом по умолчанию

Изменение SQL-запроса

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

  1. Если столбец содержит денежные или десятичные данные, используйте такой синтаксис, чтобы исключить ненужные десятичные значения:

    "SELECT ROUND([Decimal_column_name],0)… .”

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

  2. При наличии столбца даты и времени "dbo.Bigtable.[Date Time]", в котором вам не нужна часть данных времени, используйте такой синтаксис для ее исключения:

    "SELECT CAST (dbo.Bigtable.[Date time] as date) AS [Date time]) "

  3. При наличии столбца даты и времени "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]"

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

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

    Timefromparts(datepart(hh, dbo.Bigtable.[Date Time]), datepart(mm, dbo.Bigtable.[Date Time])) as [Date Time HourMinute]

  5. Если у вас есть два столбца даты и времени, например [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

Нужна дополнительная помощь?

Нужны дополнительные параметры?

Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.

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

Были ли сведения полезными?

Насколько вы удовлетворены качеством перевода?
Что повлияло на вашу оценку?
После нажатия кнопки "Отправить" ваш отзыв будет использован для улучшения продуктов и служб Майкрософт. Эти данные будут доступны для сбора ИТ-администратору. Заявление о конфиденциальности.

Спасибо за ваш отзыв!

×