Remkomplekty.ru

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

Олап кубы в 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 в область столбцов:

Читать еще:  Vba excel address

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 (импорт)

Вы можете использовать файл подключения к данным 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).

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

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 не работает.
Читать еще:  Справочник по vba excel на русском

Остальные свойства объекта 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

Получить данные из dbf-файлов 1С в сводные таблицы Excel для анализа — часто встречающаяся и очень заманчивая задача. Традиционный способ состоит в использовании промышленного Microsoft SQL Server c OLAP Analysis Server по следующей схеме:


Первый опыт автора был именно таким (2003г.). Настройка процедур переноса данных из dbf-файлов 1С в таблицы SQL Server и далее формирование OLAP-куба в Analysis Server достаточно трудоемкая задача. Но пользователь подключившись к OLAP-кубу из Excel может анализировать данные в многомерных сводных таблицах. Выгрузка данных в куб должна выполняться ежедневно.

Кроме того, Excel позволяет создавать локальные OLAP-кубы, представляющие собой подмножества данных серверных OLAP-кубов. Локальные кубы хранятся в файлах с расширением *.cub. Их можно копировать, переносить, просматривать на локальном компьютере. Локальный OLAP-куб на основе серверного куба создается через панель Сводные таблицы > Автономный режим OLAP, далее Создать автономный файл данных… . Мастер позволяет выбрать измерения и их уровни, а также меры, которые будут присутствовать в локальном кубе… и сохранить локальный куб в файле с расширением *.cub.

Здесь мы опишем простой способ получения данных 1С для анализа в сводных таблицах Excel, с использованием Access в качестве хранилища данных, вместо громоздкого SQL Server. Аналогично можно в получать для анализа в Excel данные из других систем, например, Navision (2006-2009гг). Схема выборки данных будет такой:


Решение задачи состоит из следующих шагов:

1. Отбор документов и справочников 1С, определение полей для связывания и для анализа.
2. Импорт dbf-файлов документов и справочников в таблицы Access + Изменение одного параметра в реестре Windows. Создание запросов для сводных таблиц.
3. Настройка получения данных в сводную таблицу Excel из ‘внешнего источника’ — Access.

Рассмотрим каждый шаг в отдельности. Скачайте а) готовый образец сводной таблицы Excel и источник данных для неё б) базу данных этого примера (должна лежать по адресу C:olap.mdb).
На 1-ом шаге в нашем примере были отобраны следующие документы и справочники 1С:
Таблицы для импорта
——————-
1. 1SJOURN.dbf __ Журналы
2. DH8815.dbf ___ Документ МояРеализация
3. DT8815.dbf ___ Документ (Мн.Ч.) МояРеализация — табличная часть
4. SC4014.dbf ___ Справочник Фирмы
5. SC493.dbf ____ Справочник ЮрЛица
6. SC796.dbf ____ Справочник Проекты
7. SC84.dbf _____ Справочник Номенклатура
8. SC8721.dbf ___ Справочник Производители
9. SC8723.dbf ___ Справочник Посты

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

На 2-ом шаге следует импортировать выбранные файлы из конфигурации 1С в Access через пункт меню Файл -> Внешние данные -> Импорт, установив тип файлов dBase5 (*.dbf). В итоге у нас получилось так:


Здесь необходимо снять проблему, связанную с неверным отображением кириллицы (если открыть таблицу, то вместо кириллицы вы увидите символы псевдографики). В реестре Windows для Jet -> xBase, параметр DataCodePage необходимо установить в ANSI, точнее HKEY_LOCAL_MACHINESOFTWAREMicrosoftJet4.0EnginesxBase далее параметр DataCodePage=ANSI вместо OEM.

Далее построим запросы для выборки нужных данных из таблиц. Для простоты создадим промежуточные запросы Журнал, Документ, Строка, которые затем объединим в один запрос Куб. Куб и будет источником данных для Excel.


Запросы в режиме конструктора показаны ниже. Таблицы в запросах связаны по интересующим полям — назначение полей описано в файле 1Cv7.DD. Скачайте базу данных этого примера, для просмотра таблиц и запросов прямо в Access!

Запрос Журнал:
Результат запроса Журнал:
Запрос Документ:
Запрос Строка:
Итоговый запрос Куб:
Переходим к 3-му шагу — настройке получения данных в сводную таблицу Excel из ‘куба’ Access. (О загрузке внешних данных в Excel подробно рассказано здесь). Кратко так. Запустите Excel. Через пункт меню Данные -> Сводная таблица запустите мастер, выберите п. ‘во внешнем источнике данных’, в следующем окне мастера нажмите кн. Получить данные. В окне ‘Выбор источника данных’ на закладке ‘Базы данных’ выберите База данных MS Access и нажмите ОК. Далее в окне найдите местоположение скачанной базы данных Access olap.mdb и опять ОК (во избежание ошибок из-за длинного пути к файлу, сохраните файл olap.mdb в корне раздела диска или в папке с коротким именем!). В следующем окне мастер предложит выбрать таблицы и поля для подключения — в левой части найдите только Куб и стрелкой > перенесите все поля запроса Куб в правую колонку! Затем 3 раза кн. Далее и кн. Готово, чтобы вернуть данные в Excel.

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


Перетащите измерения и меры по своему усмотрению или как показано ниже. Скачайте готовый образец сводной таблицы Excel — источник данных для этой настроенной сводной таблицы должен лежать по адресу C:olap.mdb. Но лучше самостоятельно выполнить шаг 3. (О работе со сводными таблицами в Excel подробно читайте здесь).

Ссылка на основную публикацию
ВсеИнструменты
Adblock
detector
×
×