Foreversoft.ru

IT Справочник
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Olap в excel

VBA в Excel Объект Excel.PivotTable и работа со сводными таблицами и кубами OLAP в Excel

10.8 Работа со сводными таблицами (объект PivotTable)

Объект Excel.PivotTable, программная работа со сводными таблицами и кубами OLAP в Excel средствами VBA, объект PivotCache, создание макета сводной таблицы

В процессе работы большинства предприятий накапливаются так называемые необработанные данные (raw data) о деятельности. Например, для торгового предприятия могут накапливаться данные о продажах товаров — по каждой покупке отдельно, для предприятий сотовой связи — статистика нагрузки на базовые станции и т.п. Очень часто менеджменту предприятия необходима аналитическая информация, которая генерируется на основе необработанной — например, посчитать вклад каждого вида товара в доходы предприятия или качество обслуживания в зоне данной станции. Из необработанной информации такие сведения извлечь очень тяжело: нужно выполнять очень сложные SQL-запросы, которые выполняются долго и часто мешают текущей работе. Поэтому все чаще в настоящее время необработанные данные сводятся вначале в хранилище архивных данных — Data Warehouse, а затем — в кубы OLAP, которые очень удобны для интерактивного анализа. Проще всего представить себе кубы OLAP как многомерные таблицы, в которых вместо стандартных двух измерений (столбцы и строки, как в обычных таблицах), измерений может быть очень много. Обычно для описания измерений в кубе используется термин «в разрезе». Например, отделу маркетинга может быть нужна информация во временном разрезе, в региональном разрезе, в разрезе типов продукта, в разрезе каналов продаж и т.п. При помощи кубов (в отличие от стандартных SQL-запросов) очень просто получать ответы на вопросы типа «сколько товаров такого-то типа было продано в четвертом квартале прошлого года в Северо-Западном регионе через региональных дистрибьюторов.

Конечно же, в обычных базах данных такие кубы не создать. Для работы с кубами OLAP требуются специализированные программные продукты. Вместе с SQL Server поставляется база данных OLAP от Microsoft, которая называется Analysis Services. Есть OLAP-решения от Oracle, IBM, Sybase и т.п.

Для работы с такими кубами в Excel встроен специальный клиент. По-русски он называется Сводная таблица (на графическом экране он доступен через меню Данные -> Сводная таблица), а по-английски — Pivot Table. Соответственно, объект, который представляет этот клиент, называется PivotTable. Необходимо отметить, что он умеет работать не только с кубами OLAP, но и с обычными данными в таблицах Excel или баз данных, но многие возможности при этом теряются.

Сводная таблица и объект PivotTable — это программные продукты фирмы Panorama Software, которые были приобретены Microsoft и интегрированы в Excel. Поэтому работа с объектом PivotTable несколько отличается от работы с другими объектами Excel. Догадаться, что нужно сделать, часто бывает непросто. Поэтому рекомендуется для получения подсказок активно использовать макрорекордер. В то же время при работе со сводными таблицами пользователям часто приходится выполнять одни и те же повторяющиеся операции, поэтому автоматизация во многих ситуациях необходима.

Как выглядит программная работа со сводной таблицей?

Первое, что нам потребуется сделать — создать объект PivotCache, который будет представлять набор записей, полученных с источника OLAP. Очень условно этот объект PivotCache можно сравнить с QueryTable. Для каждого объекта PivotTable можно использовать только один объект PivotCache. Создание объекта PivotCache производится при помощи метода Add() коллекции PivotCaches:

Dim PC1 As PivotCache

Set PC1 = ActiveWorkbook.PivotCaches.Add(xlExternal)

PivotCaches — стандартная коллекция, и из методов, которые заслуживают подробного рассмотрения, в ней можно назвать только метод Add(). Этот метод принимает два параметра:

  • SourceType — обязательный, определяет тип источника данных для сводной таблицы. Можно указать создание PivotTable на основе диапазона в Excel, данных из базы данных, во внешнем источнике данных, другой PivotTable и т.п. На практике обычно OLAP есть смысл использовать только тогда, когда данных много — соответственно нужно специализированное внешнее хранилище (например, Microsoft Analysis Services). В этой ситуации выбирается значение xlExternal.
  • SourceData — обязательный во всех случаях, кроме тех, когда значение первого параметра — xlExternal. Собственно говоря, определяет тот диапазон данных, на основе которого и будет создаваться PivotTable. Обычно принимает объект Range.

Следующая задача — настроить параметры объекта PivotCache. Как уже говорилось, этот объект очень напоминает QueryTable, и набор свойств и методов у него очень похожий. Некоторые наиболее важные свойства и методы:

  • ADOConnection — возможность возвратить объект ADO Connection, который автоматически создается для подключения к внешнему источнику данных. Используется для дополнительной настройки свойств подключения.
  • Connection — работает точно так же, как и одноименное свойство объекта QueryTable. Может принимать строку подключения, готовый объект Recordset, текстовый файл, Web-запрос. файл Microsoft Query. Чаще всего при работе с OLAP прописывается строка подключения напрямую (поскольку получать объект Recordset, например для изменения данных, большого смысла нет — источники данных OLAP практически всегда доступны только на чтение). Например, настройка этого свойства для подключения к базе данных Foodmart (учебная база данных Analysis Services) на сервере LONDON может выглядеть так:

PC1.Connection = «OLEDB;Prov >

  • свойства CommandType и CommandText точно так же описывают тип команды, которая передается на сервер баз данных, и текст самой команды. Например, чтобы обратиться на куб Sales и получить его целиком в кэш на клиенте, можно использовать код вида
  • свойство LocalConnection позволяет подключиться к локальному кубу (файлу *.cub), созданному средствами Excel. Конечно, такие файлы для работы с «производственными» объемами данных использовать очень не рекомендуется — только для целей создания макетов и т.п.
  • свойство MemoryUsed возвращает количество оперативной памяти, используемой PivotCache. Если PivotTable на основе этого PivotCache еще не создана и не открыта, возвращает 0. Можно использовать для проверок, если ваше приложение будет работать на слабых клиентах.
  • свойство OLAP возвращает True, если PivotCache подключен к серверу OLAP.
  • OptimizeCache — возможность оптимизировать структуру кэша. Изначальная загрузка данных будет производиться дольше, но потом скорость работы может возрасти. Для источников OLE DB не работает.

Остальные свойства объекта PivotCache совпадают с аналогичными свойствами объекта QueryTable, и поэтому здесь рассматриваться не будут.

Главный метод объекта PivotCache — это метод CreatePivotTable(). При помощи этого метода и производится следующий этап — создание сводной таблицы (объекта PivotTable). Этот метод принимает четыре параметра:

  • TableDestination — единственный обязательный параметр. Принимает объект Range, в верхний левый угол которого будет помещена сводная таблица.
  • TableName — имя сводной таблицы. Если не указано, то автоматически сгенерируется имя вида «СводнаяТаблица1».
  • ReadData — если установить в True, то все содержимое куба будет автоматически помещено в кэш. С этим параметром нужно быть очень осторожным, поскольку неправильное его применение может резко увеличить нагрузку на клиента.
  • DefaultVersion — это свойство обычно не указывается. Позволяет определить версию создаваемой сводной таблицы. По умолчанию используется наиболее свежая версия.

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

PC1.CreatePivotTable Range («A1»)

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

  • область столбцов — в нее помещаются те измерения («разрез», в котором будут анализироваться данные), членов которых меньше;
  • область строк — те измерения, членов которых больше;
  • область страницы — те измерения, по которым нужно только проводить фильтрацию (например, показать данные только по такому-то региону или только за такой-то год);
  • область данных — собственно говоря, центральная часть таблицы. Те числовые данные (например, сумма продаж), которые мы и анализируем.

Полагаться на пользователя в том, что он правильно разместит элементы во всех четырех областях, трудно. Кроме того, это может занять определенное время. Поэтому часто требуется расположить данные в сводной таблице программным образом. Эта операция производится при помощи объекта CubeField. Главное свойство этого объекта — Orientation, оно определяет, где будет находиться то или иное поле. Например, помещаем измерение Customers в область столбцов:

PT1.CubeFields («[Customers]»).Orientation = xlColumnField

Затем — измерение Time в область строк:

PT1.CubeFields («[Time]»).Orientation = xlRowField

Затем — измерение Product в область страницы:

PT1.CubeFields («[Product]»).Orientation = xlPageField

И наконец, показатель (числовые данные для анализа) Unit Sales:

PT1.CubeFields(«[Measures].[Unit Sales]»).Orientation = xlDataField

Теперь сводная таблица создана и с ней вполне можно работать. Однако часто необходимо выполнить еще одну операцию — раскрыть нужный уровень иерархии измерения. Например, если нас интересует поквартальный анализ, то нужно раскрыть уровень Quarter измерения Time (по умолчанию показывается только самый верхний уровень). Конечно, пользователь может сделать это самостоятельно, но не всегда можно рассчитывать, что он догадается, куда щелкнуть мышью. Программным образом раскрыть, например, иерархию измерения Time на уровень кварталов для 1997 года можно при помощи объектов PivotField и PivotItem:

Сводные таблицы Excel

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

Если вам все же необходимо анализировать OLAP-данные после отключения от сети, создайте автономный куб данных. Автономный куб данных — это отдельный файл, который представляет собой кеш сводной таблицы и хранит OLAP-данные, просматриваемые после отключения от локальной сети. OLAP-данные, скопированные в сводную таблицу, можно распечатать, на сайте http://everest.ua подробно об этом рассказано.

Чтобы создать автономный куб данных, сначала создайте сводную таблицу OLAP. Поместите курсор в пределах сводной таблицы и щелкните на кнопке Средства OLAP (OLAP Tools) контекстной вкладки Параметры (Tools), входящей в группу контекстных вкладок Работа со сводными таблицами (PivotTable Tools). Выберите команду Автономный режим OLAP (Offline OLAP) (рис. 9.8).

Рис. 9.8. Создание автономного куба данных

На экране появится диалоговое окно настроек автономного куба данных OLAP. Щелкните в нем на кнопке Создать автономный файл данных (Create Offline Data File). Вы запустили мастер создания файла куба данных. Щелкните на кнопке Далее (Next), чтобы продолжить процедуру.

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

Щелкните на кнопке Далее для перехода к следующему диалоговому окну мастера. В нем вы получаете возможность указать члены или элементы данных, которые не будут включаться в куб. В частности, вам не потребуется мера Internet Sales-Extended Amount, поэтому флажок для нее будет сброшен в списке. Сброшенный флажок указывает на то, что указанный элемент не будет импортироваться и занимать лишнее место на локальном жестком диске.

На последнем этапе укажите расположение и имя куба данных. В нашем случае файл куба будет назван MyOfflineCube.cub и будет располагаться в папке Work.

Файлы кубов данных имеют расширение .cub

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

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

olap для маленькой компании

В посте Многомерные кубы, OLAP и MDX Vitko написал: «тема очень интересная и с каждым днем становится все более актуальной». К сожалению, это заклинание произносится уже очень давно (по крайней мере я его слышу с 2004 года ), но olap проектов до сих пор очень мало. Возможно, потому что традиционно считается, что всё, что связанно с olap нужно только для крупных компаний с большими объемами накопленных данных и стоит очень дорого. Но это не совсем так. Я хочу рассказать о проекте, который внедрен в одной относительно небольшой компании.

Проект очень древний, начинался ещё в 2003 году. Про некоторые вещи можно сказать «так исторически сложилось». Но, мне кажется общая идея, может быть полезной.

Итак. Компания занимается оптовой торговлей кондитерскими изделиями. Опт кондитерки достаточно специфичный бизнес. При относительно небольших оборотах приходится иметь дело с большими объемами данных. Клиентами компании являются как крупные торговые сети, так и небольшие магазинчики в деревнях области. Плюс огромный ассортимент продукции. Причем клиент может купить любой объем товара – от одного сникерса до вагона печенья (были прецеденты, когда на склад возврата товара поступало половинка зефиринки (история умалчивает какой было причина возврата) ).

Основная учетная система — 1С «Торговля и склад» 7.0, причем dbf версия. Она достаточно успешно справляется с задачами учета товара. Но получить в ней отчеты за большие периоды времени практически нереально. Подобные попытки создают серьезную нагрузку на сервер, начинаются проблемы у операторов 1с, жалобы в It отдел.

Потребность в таких отчетах была постоянная. Сложилась идеальная ситуация для реализации bi проекта: большой объём информации + люди заинтересованные в её анализе.

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


Посмотреть avi в нормальном качестве можно скачав отсюда 5,25Mb ( 6 минут )
Поработать с локальным кубом можно скачав пример 2,64Mb
или тут 8Mb

Как это реализовано:

Синие стрелки — пути, которыми информация попадает в систему, зеленными – как информация в дальнейшем используется.

  1. Информация о заказах заносится в систему 1с – dbf версия.
  2. Загрузка данных «автообмен». Вообще – то это лишний шаг. Данные можно получать напрямую из dbf базы. Но программисты 1с решили что стандартный (для 1с ) механизм выгрузки данных, принесет меньше вреда.
  3. Раз в сутки изменения за прошедший день выгружаются в специально подготовленную базу MsSql – хранилище. Выгружается не вся информация, а только то, что нужно для кубов.

В принципе необязательно строить «хранилище». Данные для куба можно получать напрямую из базы 1с ( MsSQL или dbf ). Но в моем случае из 1с данные прошлых периодов периодически удаляются и очищаются справочники. Кроме того перед загрузкой в хранилище данные немного «чистятся».

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

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

    Любой пользователь может получить информацию разными путями:

      Построить отчет самостоятельно на web-странице или в excel

    Сначала использовался только excel, но возникало много проблем с тем, что екселевские файлы «разбредались», нужно было получить одну «точку входа» для выбора информации.
    Поэтому был создан локальный сайт, на котором опубликованы страницы с PivotTable. Сотрудник, который хочет получить пару цифр «здесь и сейчас» заходит на этот сайт и строит отчет в нужной ему форме. Если человеку нужно использовать этот отчет в дальнейшем – он может написать заявку, чтобы его отчет опубликовали в SSRS или сам сохраняет его в excel.

  • Посмотреть стандартный отчет, опубликованный в SQL Server Reporting Services ( SSRS )
  • Получить локальный куб – и вне офиса «вращать» данные с помощью excel
  • Подписаться на рассылку и получать стандартные отчеты из SSRS на e-mail
  • Отдел маркетинга кроме того использует программу CubeSlice. В ней можно создавать локальные кубы самостоятельно и гораздо удобнее, чем в excel
  • Локальные кубы

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

    Как правило, поставщику тоже неудобно работать с большими отчетами. Поэтому большая часть, попробовав работать с локальными кубами, согласилась получать отчетность в таком виде. Список отчетов, которые формировал отдел маркетинга, значительно сократился. Оставшиеся тяжелые отчеты были реализованы в SSRS, созданы подписки (отчеты формируются автоматически и рассылаются поставщикам по расписанию)

    Основные параметры системы

    Конфигурация сервера:

    процессор: 2xAMD Opteron 280
    память: 4Gb
    дисковые массивы:
    операционная система: RAID 1 (зеркало) 2xSCSI 15k
    данные: RAID 0+1 4xSCSI 10k

    Согласитесь, такую машинку сложно назвать «мощным» сервером

    Объем данных:

    хранилище 10Гб, данные с 2002 года
    агрегация 30%
    Размер многомерной базы 350М
    кол-во членов «больших измерений»: товары 25 тыс., адреса – 20 тыс.
    кол-во документов в день — 400. среднее кол-во строк в документе — 30

    Что в итоге получила компания:

    Плюсы

    Для руководства предприятия

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

    Для менеджера

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

    Для поставщиков

    Возможность интерактивной работы с информацией

    С точки зрения it-специалиста

    Уменьшение рутинной работы. Большую часть отчетов пользователь получает самостоятельно.

    Сводные таблицы Excel

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

    Если вам все же необходимо анализировать OLAP-данные после отключения от сети, создайте автономный куб данных. Автономный куб данных — это отдельный файл, который представляет собой кеш сводной таблицы и хранит OLAP-данные, просматриваемые после отключения от локальной сети. OLAP-данные, скопированные в сводную таблицу, можно распечатать, на сайте http://everest.ua подробно об этом рассказано.

    Чтобы создать автономный куб данных, сначала создайте сводную таблицу OLAP. Поместите курсор в пределах сводной таблицы и щелкните на кнопке Средства OLAP (OLAP Tools) контекстной вкладки Параметры (Tools), входящей в группу контекстных вкладок Работа со сводными таблицами (PivotTable Tools). Выберите команду Автономный режим OLAP (Offline OLAP) (рис. 9.8).

    Рис. 9.8. Создание автономного куба данных

    На экране появится диалоговое окно настроек автономного куба данных OLAP. Щелкните в нем на кнопке Создать автономный файл данных (Create Offline Data File). Вы запустили мастер создания файла куба данных. Щелкните на кнопке Далее (Next), чтобы продолжить процедуру.

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

    Щелкните на кнопке Далее для перехода к следующему диалоговому окну мастера. В нем вы получаете возможность указать члены или элементы данных, которые не будут включаться в куб. В частности, вам не потребуется мера Internet Sales-Extended Amount, поэтому флажок для нее будет сброшен в списке. Сброшенный флажок указывает на то, что указанный элемент не будет импортироваться и занимать лишнее место на локальном жестком диске.

    На последнем этапе укажите расположение и имя куба данных. В нашем случае файл куба будет назван MyOfflineCube.cub и будет располагаться в папке Work.

    Файлы кубов данных имеют расширение .cub

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

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

    Читать еще:  Автоматизация excel с помощью vba
    Ссылка на основную публикацию
    Adblock
    detector