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;Provider=MSOLAP.2;Data Source=LONDON1;Initial Catalog = FoodMart 2000”

  • свойства 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

Источник: vbastudio.ru

Сводные таблицы 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, которая содержит сводную таблицу, связанную с выбранным кубом данных. После создания вы можете распространить автономный куб данных среди всех заинтересованных пользователей, которые работают в режиме отключенной локальной сети.

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

Источник: pivot-table.ru

Подключение к базе данных OLAP (импорт)

Вы можете использовать файл подключения к данным Office (ODC), чтобы подключиться к базе данных OLAP (Online Analytical Processing).

На вкладке данные выберите пункт Получение данных > из базы данных > из служб Analysis Services.

Примечание: Если вы используете Excel 2013, 2010 или 2007, на вкладке данные в группе получить внешние данные выберите из других источников > из служб Analysis Services.

Запустится мастер подключения к данным. У этого мастера три экрана.

Введите имя сервера OLAP в текстовом поле имя сервера .

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

В разделе учетные данные входавыполните одно из указанных ниже действий.

Чтобы использовать текущие имя пользователя и пароль Windows, выберите команду использовать проверку подлинности Windows.

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

Примечание по безопасности:

Используйте надежные пароли, состоящие из букв в верхнем и нижнем регистре, цифр и символов. В ненадежных паролях используются не все эти элементы или они не смешаны. Например, Y6dh! et5 является надежным паролем, а House27 — ненадежный пароль. Пароль должен содержать не менее 8 символов. Лучше всего использовать парольную фразу длиной не менее 14 знаков.

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

Нажмите кнопку Далее , чтобы перейти к следующему экрану мастера

В разделе Выберите базу данных, содержащую нужные сведения выберите базу данных.

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

Нажмите кнопку Далее , чтобы перейти к следующему экрану мастера,

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

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

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

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

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

ИДЕНТИФИКАЦИ Выберите этот параметр, чтобы использовать единый вход, а затем введите соответствующую идентификационную строку в текстовом поле идентификатор единого входа . Администратор сайта может настроить сайт SharePoint на использование единого входа в базу данных, в которой можно хранить имя пользователя и пароль. Этот метод может быть наиболее эффективен, если имеется много пользователей.

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

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

Примечание: Параметры проверки подлинности используются только службами Excel, а не Microsoft Office Excel. Если вы хотите, чтобы эти данные были доступны при открытии книги в Excel или службах Excel, убедитесь в том, что параметры проверки подлинности в Excel совпадают.

Нажмите кнопку ОК.

Нажмите кнопку Готово, чтобы закрыть окно мастера подключения к данным.

Откроется диалоговое окно ” Импорт данных “.

В разделе выберите способ просмотра данных в книге выполните одно из указанных ниже действий.

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

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

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

В группе Куда следует поместить данные? выполните одно из следующих действий:

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

Читайте также:  Форма excel

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

Чтобы поместить отчет сводной таблицы на новый лист, начиная с ячейки a1, нажмите кнопку Новый лист.

Кроме того, вы можете изменить свойства подключения, нажав кнопку Свойства, внеся изменения в диалоговом окне Свойства подключения , а затем нажав кнопку ОК. Дополнительные сведения см. в статье Свойства подключения.

Совет: При использовании книги, подключенной к базе данных служб SQL Server Analysis Services, вам может потребоваться дополнительная информация для ответа на конкретные вопросы о продукте, такие как справочные сведения о МНОГОМЕРных выражениях или процедурах настройки для сервер аналитической обработки в сети (OLAP).

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Источник: support.office.com

Создаем OLAP куб. Часть 1

Продолжая тематику Многомерные кубы, OLAP и MDX и olap для маленькой компании, традиционно, предлагаю начать с простенького «Hello World» куба, который будет анализировать процессы и тенденции голосований на Хабре.

Итак, давайте попробуем создать свою первую OLAP систему.
Но, прежде чем, потирая руки, запускать Business Intelligence Studio, предлагаю вначале создать хранилище данных хабра-голосов, так называемый Data Warehouse.
Зачем? Причин в этом несколько:

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

Немного теории.

По сути, Data Warehouse может быть:

  • чисто виртуальный (например, определенным как множество SELECT-ов или даже вызовов сложных хранимых процедур, которые каким-то образом определят входные данные для куба)
  • вполне реальным, то есть существовать физически на каком-то сервере (или серверах)

В последнем случае, вы, скорее всего, захотите имплементировать ETL процессы (используя Integration Services или что-то еще), но это уже повод для другой, не менее интересной, статьи.

Каким же должен быть Data Warehouse?

Все очень просто – ваш Data Warehouse должен иметь структуру формы звездочки (star model) или снежинки (snowflake model) и состоять из фактов (facts) и измерений (dimensions).

Факты – это фактические записи (records) о каком-то процессе, который мы хотим анализировать, например, процесс голосования на Хабра, или процесс изменения цены товара на бирже. Очень часто факты содержат какие-нибудь числовые данные, например, фактическое значение голоса или цены.

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

Определив где у нас факты, а где измерения — очень просто построить модель звезды.

Звезда.

В центре указываем нашу таблицу фактов, а лучами выводим измерения.

А теперь снежинка.

Снежинка — это та же звезда, только измерения могут зависеть от измерений следующего уровня, а те в свою очередь могут включать еще уровни.


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

HabraDW.

Перейдем к собственно разработке нашего Data Warehouse-а.

Наша цель – анализ тенденций голосования на Хабре, нахождение закономерностей и трендов.
Основные тенденции, которые мы хотим определить:

  • в какое время года/месяца/недели голосуют лучше/хуже/чаще
  • как голосуют по пятницам и понедельникам (например)
  • как влияет на результат голосования наличие в посте слов Microsoft, или Карма
  • средняя активность пользователей, «пики» голосования
  • и т.п.

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

В итоге, имеем следующие таблицы:

  • Таблица фактов FactHabravote – определяет кто, когда, за что и как именно проголосовал. Значение Vote в нашем случае будет +- 1, но тип поля позволяет расширить дельту голосами, например, +- 10
  • Измерение времени DimTime – определяет нужные для анализа атрибуты времени (значения и названия)
  • Измерение пользователей DimUser – определяет пользователей Хабра, пока только никнейм
  • Измерение постов DimPost – определяет посты, в нашем случае содержит заголовок и булевые поля, определяющие содержит ли пост слова Microsoft и Карма.

Итоговая схема нашей звезды будет такой.

А здесь исходный SQL скрипт, который создает и наполняет (пока что только случайными данными) наше хранилище.

Ну вот, теперь все готово, чтобы загрузить данные в куб.
До встречи в следующей статье.

Источник: habr.com

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. Догадаться, что нужно сделать, часто бывает непросто. Поэтому рекомендуется для получения подсказок активно использовать макрорекордер. В то же время при работе со сводными таблицами пользователям часто приходится выполнять одни и те же повторяющиеся операции, поэтому автоматизация во многих ситуациях необходима.

Читайте также:  Как в 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;Provider=MSOLAP.2;Data Source=LONDON1;Initial Catalog = FoodMart 2000”

  • свойства 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:

Источник: vbastudio.ru

Создание куба OLAP в excel 2010

16.03.2013, 21:24

Создание OLAP куба. Не могу развернуть базу
Привет всем.. Помогите пожалуйста. Есть база данных Univer на основе которой я создаю олап-куб.

Автогенерация Olap-куба
В Enterprise Manager создаю local Package в папке Data Transformation Services. Затем в меню Task.

Реализация OLAP куба
Добрый день! Вообщем делаю курсовую, связанную с реализацией OLAP-куба. База данных реализована.

Ошибка при обработке olap куба
Здравствуйте , не могу понять в чем ошибка при обработке olap куба ? Прикрепляю скриншот базы.

21.03.2013, 17:47 2

Почти любая сводная таблица считается OLAP кубом.

Для иных случаев я написал класс OLAP , связанный с диапазонами всех страниц книги. Стопка заполненных ячеек на листах книги является OLAP кубом.
Класс OLAP предназначен для работы с этими ячейками как с OLAP кубом.
В Module1 в процедуре test находятся примеры работы с классом, вызова данных из OLAP куба с распечаткой в окно Immediate редактора VBA, присвоение данных кубу с промежуточными остановками программы для контроля результатов присвоения на листах книги.

Вложения

OLAP_куб.zip (24.3 Кб, 395 просмотров)
22.03.2013, 10:51 3

Решение

Более совершенный куб.
Присваивает матрицы и вектора как меньшего, так и большего размера, присваивает 3-х мерные матрицы.
Если координаты элемента или размерность присваиваемой матрицы по одному или нескольким измерениям превышает начальную размерность, то размерность куба увеличивается.
Добавлено свойство .cubeUbound() и добавлены примеры присвоения с увеличением размерности куба.

Работает и в Excel 1997-2003, XP.

Комментарий по ориентации измерений:
X строки Excel, увеличение сверху вниз.
Y столбцы Excel, увеличение слева направо.
Z листы Excel, увеличение в глубину.

Вложения

OLAP_куб_2.zip (22.4 Кб, 331 просмотров)
26.03.2013, 14:57 4 Вложения

OLAP_куб_N-мерный.zip (33.3 Кб, 326 просмотров)
26.03.2013, 14:57
26.03.2013, 14:57

Заказываю контрольные, курсовые, дипломные и любые другие студенческие работы здесь.

Способы получения данных с OLAP куба
Форумчане, подскажите каким способом можно регулярно получать данные с OLAP куба (MS Analysis.

Excel 2010 Создание журнала внесения изменений
Здравствуйте! Нарисовалась еще одна проблема! Задача состоит в следующем: есть таблица, с ней.

Создание фильтрации результатов по выбранным параметрам в Excel 2010
Есть файл Excel, состоящий из 11-ти листов: 1) Главная 2) Общие данные 3-11) Январь-Декабрь .

ВПР в Excel + OLAP
Добрый день, Уважаемые форумчане! Есть такой вот вопросик, просто не понятно куда ближе.

Источник: www.cyberforum.ru

Добавить комментарий

Adblock
detector