On-Line Analytical Processing (OLAP) — это способ организации больших коммерческих баз данных. Базы данных OLAP позволяют получать и анализировать данные, что облегчает создание необходимых отчетов.

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

Основы OLAP

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

Уровни в географическом измерении

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

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

Средства OLAP в Microsoft Excel

Загрузка данных OLAP. Соединение с источниками данных OLAP производится так же, как и с другими внешними источниками данных. Можно работать с базами данных, созданными Microsoft SQL Server OLAP Services. Microsoft Excel позволяет работать с продуктами OLAP независимых разработчиков, совместимыми с OLE-DB для OLAP.

Данные OLAP могут быть отображены только в виде сводной таблицы или отчета сводной диаграммы, а не как внешний диапазон данных. Сводные таблицы и отчеты сводных диаграмм OLAP можно сохранить в шаблонах отчета, кроме того, можно создать файлы подключения к данным Microsoft Office (ODC) для подключения к базам данных OLAP и файлы запросов (ODY) для запросов OLAP. При открытии файла ODC или ODY Microsoft Excel отображает пустой отчет сводной таблицы, готовый для создания макета.

Создание файлов куба для автономного использования. Можно использовать мастер автономных кубов для создания файлов с подмножествами данных из серверной базы данных OLAP. Файлы автономного куба позволяют работать с данными OLAP даже при работе вне сети. Файлы куба могут быть созданы только в том случае, если поставщик данных для куба, например Microsoft SQL Server OLAP Services, поддерживает создание файлов куба.

Создание кубов на основе реляционных баз данных. Мастер куба OLAP предназначен для организации данных из реляционных баз данных, например Microsoft SQL Server, в кубы OLAP. Этот мастер доступен, только если Microsoft Query запущен из-под Microsoft Excel. Куб позволяет работать с большими объемами данных в сводной таблице или отчете сводной диаграммы и позволяет ускорить загрузку данных.

Программное обеспечение, необходимое для доступа к OLAP

Поставщик данных для куба OLAP. Чтобы настроить источники данных OLAP для Microsoft Excel, требуется один из следующих поставщиков данных для куба OLAP.

Поставщик данных для куба производства корпорации Майкрософт. Microsoft Excel включает драйвер источника данных и клиентское программное обеспечение, необходимые для доступа к базам данных, созданным с использованием OLAP-продукта Майкрософт, Microsoft SQL Server OLAP Services. Драйверы, входящие в комплект Microsoft Excel 2002, поддерживают версии 7.0 и 8.0 этого приложения. Драйверы версии 7.0, входящие в комплект Microsoft Excel 2000, можно использовать для доступа к базам данных версии 7.0, а для доступа к базам данных версии 8.0 необходимы драйверы версии 8.0.
Поставщики данных для куба от сторонних производителей. При использовании других продуктов для работы с OLAP необходимо установить дополнительные драйверы и клиентское программное обеспечение. Для использования возможностей Microsoft Excel при работе с данными OLAP продукты сторонних производителей должны соответствовать стандарту OLE-DB для OLAP и быть совместимыми с Microsoft Office. Для получения сведений об установке и использовании поставщиков данных OLAP от сторонних производителей обратитесь к системному администратору или поставщику OLAP-продукта.

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

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

Microsoft Query. Microsoft Query входит в состав дополнительных компонентов Microsoft Office, его можно установить и использовать из Microsoft Excel. Microsoft Query следует использовать для получения данных из внешних баз данных, например Microsoft SQL или Microsoft Access. Нет необходимости использовать Microsoft Query для получения данных из сводной таблицы OLAP, подключенной к файлу куба.

Различия возможностей отчетов, основанных на исходных данных OLAP

Существуют некоторые отличия в работе со сводными таблицами и отчетами сводных диаграмм, основанными на исходных данных OLAP и другими типами исходных данных.

Получение данных. Сервер OLAP передает новые данные в Microsoft Excel при каждом изменении макета отчета. При использовании других типов внешних исходных данных запрашиваются все исходные данные одновременно, кроме того, можно установить выполнение запроса только при отображении различных элементов поля страницы. Кроме того, имеется несколько других параметров для обновления отчета.

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

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

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

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

Поля страниц OLAP могут не иметь элемент Все, а команда Отобразить страницы может быть недоступна.

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

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

Оба типа исходных данных поддерживают дополнительные вычисления.

В отчетах, основанных на исходных данных OLAP, невозможно создавать вычисляемые поля или вычисляемые элементы.

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

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

Изменение вида импортированного текста

Выделите ячейку в импортированном текстовом файле.
На панели инструментов Внешние данные нажмите кнопку Изменить импорт текста .
В диалоговом окне Импорт текстового файла нажмите кнопку Импорт.
Внесите изменения в окне мастера текстов (импорт), а затем нажмите кнопку Готово.

Изменение имени диапазона внешних данных

Щелкните ячейку в диапазоне внешних данных, который требуется переименовать.
На панели инструментов Внешние данные нажмите кнопку Свойства диапазона данных .
В поле Имя введите имя диапазона внешних данных.

Копирование формул при расширении диапазона внешних данных

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

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

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

Создание шаблона отчета для импорта данных

Для выполнения следующих действий необходимо поместить на лист отчет сводной таблицы с диапазоном внешних данных.

Отформатируйте книгу.
Выберите в меню Файл команду Сохранить .
В поле Имя файла введите имя отчета.
В списке Тип файла выберите пункт Шаблон.
В поле Папка выберите папку, в которой должен храниться данный шаблон.
Нажмите кнопку Сохранить.
Выполните одно из следующих действий:
- чтобы удалять диапазон внешних данных с листа и автоматически обновлять данные при открытии шаблона отчета, нажмите кнопку Да;
- чтобы при открытии копии шаблона отчета в диапазоне внешних данных отображались текущие данные, нажмите кнопку Нет.

Совет

Шаблон отчета можно изменить в любой момент, открыв его в Microsoft Excel и внеся требуемые изменения.

Настройка имен полей в диапазоне внешних данных

Данная процедура применима только к данным, полученным из базы данных, но не к данным, полученным из текстового (TXT) файла или из веб-запроса (IQY).

Вставьте пустую строку над диапазоном внешних данных.

Инструкции
1. Выполните одно из следующих действий.
  
  Вставка новых пустых ячеек. Выделите диапазон ячеек, в который требуется вставить новые пустые ячейки. При этом необходимо, чтобы количество выделенных ячеек соответствовало количеству добавляемых ячеек.
  
  Вставка одной строки.   Щелкните ячейку в строке, над которой следует вставить новую строку. Например, если щелкнуть ячейку в строке 5, то над ней будет вставлена новая строка.
  
  Вставка нескольких строк.   Выделите строки, над которыми следует вставить новые строки. Количество выделенных и вставляемых строк должно совпадать.
  
  Вставка одного столбца.   Щелкните ячейку в столбце, слева от которого следует поместить новый столбец. Например, если щелкнуть ячейку в столбце B, то слева от него будет вставлен новый столбец.
  
  Вставка нескольких столбцов.   Выделите столбцы, справа от которых следует вставить новые столбцы. Количество выделенных и вставляемых столбцов должно совпадать.
2. В меню Вставка выберите команду Ячейки, Строки, или Столбцы.
3. При вставке пустых ячеек выберите вариант ячейки, со сдвигом вправо илиячейки, со сдвигом вниз, чтобы выбрать вариант сдвига имеющихся ячеек.
Совет

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

Примечание. При извлечении данных из базы данных изменения имен столбцов, вносимые в Microsoft Query, сохраняются в диапазоне внешних данных. Сведения об изменении имен столбцов в Microsoft Query см. в справке Microsoft Query.

Удаление диапазона внешних данных

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

Если вместо выбора команды Удалить из меню Правка нажать клавишу DEL на клавиатуре, после очистки диапазона внешних данных будет предложено удалить также соответствующий запрос.

Совет

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

Поиск диапазона внешних данных

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

Закрепление диапазона внешних данных

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

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

Примечание. При сохранении книги из нее будет удален запрос. Однако сохраненные файлы запросов к базам данных (DQY и ODC) не удаляются. При сохранении запроса в ходе его создании в мастере запросов или в Microsoft Query файл запроса сохраняется на локальном компьютере и может быть повторно использован для загрузки внешних данных. Дополнительные сведения см. в справке Microsoft Query.

Выбор способа добавления новых данных в диапазон внешних данных

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