Создание эффективным использованием памяти с помощью Excel и надстройки Power Pivot модели данных

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

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

Примечание : В этой статье описаны моделей данных в Excel 2013. Однако же моделированию данных и возможности Power Pivot, представленные в Excel 2013 также применять для Excel 2016. Нет эффективно прямым различия между этих версиях Excel.

Excel позволяет с легкостью создавать большие модели данных, но это не слишком рационально. Во-первых, большие модели с множеством таблиц и столбцов усложняют анализ данных и делают список полей громоздким. Во-вторых, они используют большой объем памяти, что негативно влияет на работу других приложений и отчетов, использующих те же системные ресурсы. И наконец, максимальный размер файла Excel в Office 365 (как в SharePoint Online, так и в Excel Web App) составляет 10 МБ. Этот лимит легко превысить, имея книгу с моделью данных, которая содержит миллионы строк. См. статью Спецификации и ограничения модели данных.

Из этой статьи вы узнаете, как создать тщательно продуманную модель, которая упрощает работу с данными и требует мало памяти. Время, потраченное на изучение эффективных методов построения моделей данных, окупится при их дальнейшем создании и использовании в Excel 2013, Office 365 SharePoint Online, на сервере Office Web Apps или в SharePoint 2013.

Также рекомендуем запустить средство Workbook Size Optimizer. Оно анализирует ваши книги Excel и при возможности дополнительно сжимает их. Загрузка оптимизатора размера книги.

В этой статье

Степень сжатия и аналитический обработчик в памяти

Сокращение количества столбцов для эффективного использования памяти

Два примера столбцов, которые не нужно импортировать

Исключение ненужных столбцов

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

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

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

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

Использование вычисляемых мер DAX вместо столбцов

Каких 2 столбца нужно оставить?

Заключение

См. также

Степень сжатия и аналитический обработчик в памяти

Модели данных в 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])"

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

Заключение

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

  • удаление столбцов — лучший способ экономии места. Оставляйте только действительно нужные столбцы;

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

  • если вам не нужны некоторые строки в таблице, вы можете исключить их с помощью мастера импорта таблиц;

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

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

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

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

См. также

Спецификации и ограничения модели данных

Загрузка Оптимизатора размера книги

PowerPivot — мощные средства анализа и моделирования данных в Excel

Примечание : Отказ от ответственности относительно машинного перевода. Данная статья была переведена с помощью компьютерной системы без участия человека. Microsoft предлагает эти машинные переводы, чтобы помочь пользователям, которые не знают английского языка, ознакомиться с материалами о продуктах, услугах и технологиях Microsoft. Поскольку статья была переведена с использованием машинного перевода, она может содержать лексические,синтаксические и грамматические ошибки.

Совершенствование навыков
Перейти к обучению
Первоочередный доступ к новым возможностям
Присоединиться к программе предварительной оценки Office

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

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

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

×