Remkomplekty.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;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:

Читать еще:  Объявление переменных в vba excel

Работа с файлами автономного куба

автономный файл куба (. cub) хранит данные в форме куба OLAP (Online Analytical Processing). Эти данные могут представлять часть базы данных OLAP на сервере OLAP или могут создаваться независимо от базы данных OLAP. Используйте автономный файл куба, чтобы продолжить работу с отчетами сводной таблицы и сводной диаграммы, если сервер недоступен или когда вы отключены от сети.

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

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

Примечание: Создание и использование файлов автономных кубов из служб Microsoft SQL Server Analysis Services регулируется термином и лицензированием установки Microsoft SQL Server. Ознакомьтесь с соответствующими сведениями о лицензировании версии SQL Server.

Работа с мастером автономного куба

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

Перевод данных в автономный режим и их обратное подключение

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

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

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

Создание автономный файл куба на компьютере. В разделе Создание файла автономного куба из базы данных OLAP-сервера (ниже в этой статье).

Отключение от сети и работа с файлом автономного куба.

Подключитесь к сети и повторно подключите файл куба автономно. Ознакомьтесь с разделом Повторное подключение файла автономного куба к базе данных OLAP-сервера (ниже, в этой статье).

Обновление файла автономного куба с новыми данными и повторное создание автономного файла куба. Ознакомьтесь с разделом обновление и повторное создание файла автономного куба (ниже в этой статье).

OLAP-кубы

В рамках данной работы будут рассмотрены следующие вопросы:

  • Что представляют собой OLAP-кубы?
  • Что такое меры, измерения, иерархии?
  • Какие виды операций можно выполнять над OLAP-кубами?

Понятие OLAP-куба

Главный постулат OLAP – многомерность в представлении данных. В терминологии OLAP для описания многомерного дискретного пространства данных используется понятие куба, или гиперкуба.

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

Факты – это данные об объектах и событиях в компании, которые будут подлежать анализу. Факты одного типа образуют меры (measures). Мера есть тип значения в ячейке куба.

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

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

На рисунке 1 показан пример куба, предназначенного для анализа продаж продуктов нефтепереработки некоторой компанией по регионам. Данный куб имеет три измерения (время, товар и регион) и одну меру (объем продаж, выраженный в денежном эквиваленте). Значения мер хранятся в соответствующих ячейках (cell) куба. Каждая ячейка уникально идентифицируется набором членов каждого из измерений, называемого кортежем. Например, ячейка, расположенная в нижнем левом углу куба (содержит значение $98399), задается кортежем [Июль 2005, Дальний Восток, Дизель]. Здесь значение $98399 показывают объем продаж (в денежном выражении) дизеля на Дальнем Востоке за июль 2005 года.

Стоит обратите также внимание на то, что некоторые ячейки не содержат никаких значений: эти ячейки пусты, потому что в таблице фактов не содержится данных для них.

Рис. 1. Куб с информацией о продажах нефтепродуктов в различных регионах

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

Еще одной специфической чертой многомерных кубов является сложность определения точки начала координат. Например, как задать точку 0 для измерения «Товар» или «Регионы»? Решением этой проблемы является внедрение специального атрибута, объединяющего все элементы измерения. Этот атрибут (создается автоматически) содержит всего один элемент – All («Все»). Для простых функций агрегирования, например, суммы, элемент All эквивалентен сумме значений всех элементов фактического пространства данного измерения.

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

Операции над OLAP-кубами

Над OLAP-кубом могут выполняться следующие операции:

  • срез;
  • вращение;
  • консолидация;
  • детализация.

Срез (рисунок 2) является частным случаем подкуба. Это процедура формирования подмножество многомерного массива данных, соответствующее единственному значению одного или нескольких элементов измерений, не входящих в это подмножество. Например, чтобы узнать, как продвигались продажи нефтепродуктов во времени только в определенном регионе, а именно на Урале, то необходимо зафиксировать измерение «Товары» на элементе «Урал» и извлечь из куба соответствующее подмножество (подкуб).

Создание куба ОLAP средствами Microsoft Query;

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

Читать еще:  Excel vba saveas

Концепция OLAP была описана в 1993 г. известным исследователем баз данных и автором реляционной модели данных Э. Ф. Коддом. В настоящее время поддержка OLAP реализована во многих СУБД и иных инструментах.

Куб OLAP содержит два типа данных:

· итоговые значения, значения, для которых требуется подвести итог, представляющие вычисляемые поля данных;

· описательные сведения, представляющие измерения или размерности. Описательные сведения обычно распределяются по уровням детализации. Например: «Год», «Квартал», «Месяц» и «День» в размерности «Время». Распределение полей по уровням детализации позволяет пользователям, работающим с отчетами, выбирать требуемый уровень детализации для просмотра, начиная с итоговых данных высокого уровня и затем переходя к более подробному представлению, и наоборот.

Средства Microsoft Query также позволяют создавать кубы OLAP из запроса, который загружает данные реляционной базы данных, например Microsoft Access, при этом происходит преобразование линейной таблицы в структурную иерархию (куб).

Мастер создания куба OLAP является встроенным средством Microsoft Query. Для создания куба OLAP на основе реляционной базы данных перед запуском мастера необходимо выполнить следующие действия.

1. Определить источник данных (см. рис. 6.1).

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

3. На последнем шаге мастера создания запросов установить переключатель на пункте Создание куба OLAP из данного запроса (см. рис. 6.2) или после того как запрос создан средствами непосредственно Query в меню Файл выбрать команду Создать куб OLAP, после чего мастер создания куба OLAP будет запущен.

Работа мастера создания куба OLAP состоит из трех шагов.

На первом шаге мастера (см. рис. 6.6) определяются поля данных –вычисляемые поля, для которых необходимо определить итоговые значения.

Рис. 6.6. Определение полей данных

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

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

Имя вычисляемого поля можно изменить в столбце Имя поля данных.

На втором шаге мастера определяются описательные данные и их размерности (см. рис. 6.7). Для выбора поля измерения необходимо из списка Исходные поля перетащить нужное поле размерности верхнего уровня в список Измерения в область, помеченную как Перетащите сюда поля для создания размерности. Для создания куба OLAP необходимо определить хотя бы одну размерность. На этом же шаге мастера с помощью контекстного меню можно изменить имя поля размерности или уровня.

Рис. 6.7. Определение полей измерений

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

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

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

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

На третьем шаге мастера определяется типа куба, создаваемого мастером, при этом возможны три варианта (см. рис. 6.8).

Рис. 6.8. Выбор типа создаваемого куба на третьем шаге мастера

· Первые два варианта предполагают создание куба при каждом открытии отчета (если куб просматривается из Excel, то речь идет о сводной таблице). В этом случае сохраняется файл запроса и файл определения куба *.oqy, содержащий инструкции по созданию куба. Файл *.oqy может быть открыт в программе Excel для создания отчетов на основе куба, а при необходимости внесения изменений в куб, можно его открыть Query для повторного запуска мастера создания куба.

По умолчанию файлы определения куба так же, как и файлы запросов, хранятся в папке профиля пользователя в Application DataMicrosoftQue-ries. При сохранении файла *.oqy в стандартной папке, имя файла определения куба выводится на вкладке Кубы OLAP при открытии нового запроса в Microsoft Query или при выборе команды Создать запрос (меню Данные, подменю Импорт внешних данных) в Microsoft Excel.

· В случае выбора третьего варианта типа куба Сохранение файла куба, содержащего все данные для куба, извлекаются все данные для куба и в указанном пользователем месте создается файл куба с расширением *.cub, в котором эти данные хранятся. Создание данного файла не происходит немедленно при нажатии кнопки Готово; файл создается либо при сохранении определения куба в файле, либо при создании отчета на основе куба.

Выбор типа куба определяется несколькими факторами: объемом данных, которые содержит куб; типом и сложностью отчетов, которые будут создаваться на основе куба; ресурсами системы (память и дисковое пространство) и т. п.

Отдельный файл куба *.cub следует создавать в следующих случаях:

1) для часто изменяемых интерактивных отчетов при наличии достаточного дискового пространства;

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

Технология OLAP и 1С:Предприятие

Технология OLAP и 1С:Предприятие

Итак, OLAP — что, зачем и как.

Сначала просто расшифруем аббревиатуру. OLAP — On-Line Analytical Processing, то есть анализирование на лету. Такое вот название было придумано для технологии. Кроме названия было придумано 5 принципов OLAP — если принципы не соблюдаются, то как бы и не OLAP это, а так, что-то другое (Гиперкуб, например). Эти принципы чуть ниже. Сначала все-таки неформальное определение так, как его понимаю я. Итак, предназначение этой технологии — давать аналитику (менеджеру, как правило) интересующую информацию со скоростью, которая не будет отставать от его мысли. Здесь имеется в виду и скорость построения отчета, и возможность легко извлечь нужные данные. И принципы следуют именно отсюда. Вот они:

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

Теперь — ближе к делу. OLAP — это технология, а не программа. Есть решения от Оракл, от других производителей ПО, но куда нам деться от родного Билли Гейтса! Рассмотрим решение от Майкрософт, всеми ругаемой и всеми используемой. Они начали включать OLAP в SQL-сервер с версии 7.0. Там он называется OLAP-Services. В SQL-2000 название почему-то сменили на Analysis Services. Но суть осталась.

Как это выглядит абстрактно — есть куб, имеющий несколько измерений, и заполненный данными. Можно брать срезы по любому набору измерений и смотреть, что внутри. Ничего не напоминает? Не зря один человек, чуть не сделавший на Территории 1С слово OLAP ругательным, называл регистры OLAPом для бедных: Доля правды в этом есть. Но в целом такое утверждение не верно — цели у регистров и OLAP-кубов разные. Первые заточены на быстрое занесение информации и ее получении на один конкретный момент времени, вторые — на максимально удобное извлечение информации. Поместить ее в OLAP-куб — процесс не быстрый:

Читать еще:  Power bi excel 2020

Теперь — как это реализуется чуть ближе. Итак, имеем какой-то набор таблиц, проще говоря — базу данных. Эта БД называется источником данных (Data source). В одной из таблиц — набор собственно данных. Она называется таблицей фактов (fact table). В кубе может быть только одна такая таблица. Кроме того, может быть любое количество таблиц с данными об измерениях куба. Указав, какие поля содержат данные, а также задав свойства измерений и взаимосвязи между ними, получаем структуру куба. Например, такую:

Это — не очень сложный куб. Однако, он дает возможность получать информацию о количестве, сумме, себестоимости (а заодно о вычисленных на их основе прибыли и проценте прибыли) а также об остатках на начало и конец любого выбранного периода. И это в любом разрезе данных по 7-ми измерениям с любым уровнем детализации. И это очень быстро и без помощи программиста. Очень быстро — потому, что все возможные данные на пересечении всех осей рассчитываются заранее. Без помощи программиста — потому, что существует множество клиентских программ для удобного извлечения информации. От безумно дешевого в нашей стране Office 2000 и Office XP до достаточно дорогого, но с мощнейшими возможностями ProClarity:

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

Для начала — MS Office (а точнее — Excel). 2000-й рассматривать не буду, потому что избалован нормальными клиентами: Даже по сравнению с малость недоделанной Data Vision Analyzer он выглядит слишком бледно. А вот XP — уже вполне неплохо. Опять же многого не хватает, но при том, что MS office стоит в любом, простите за каламбур, офисе доступность очевидна. На лицензионности я внимание не заостряю. Итак, скриншот:

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

Второй вариант — Data Vision Analyzer от питерской фирмы Digital Design. Он стоит денег. Однако его возможности все-таки посерьезней, чем у Excel. Очень удобный выбор строк и столбцов, возможность отборов, сортировок, выделения цветом определенных значений: экспорт результата в Excel, управление доступами, журнал регистрации.

В общем, очень неплохой клиент c русским интерфейсом, что также плюс. Однако есть у него и недостатки — например, необходимо наличие SQL-сервера для хранения служебной информации. Сортировки и фильтрации можно задать только при подготовке запроса (на уже сформированном отчете, по которому погуляли с помощью детализации или укрупнения, они не срабатывают). Добавление измерений — опять же только при начальном формировании структуры запроса. Включение в отчет, скажем, всех товаров (без групп) при количестве уровней от 2-х — занятие не для слабонервных: Из представлений информации — таблица и диаграмма. Стоимость программы относительно невелика.

А теперь — клиент, который позволяет получить что угодно. ProClarity 3.0.

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

Но и стандартные представления тоже есть, с очень удобным выбором чего бы то ни было: В таблице и диаграмме, как и в дереве декомпозиции, можно детализировать данные, либо углубляясь (drill down) на следующий иерархический уровень (что доступно в любом клиенте), либо подключая новое измерение. Элементарно раскрывается, например, товар по покупателям, причем сразу можно выбрать, на какой уровень попадешь. Все отборы и сортировки можно установить в любой момент. При работе с таблицей выбор измерений для строк и столбцов мне сначала казался немного неудобным, но это прошло. В общем, отличнейший клиент. Действительно позволяет ProЯснить ситуацию :-). Огорчает одно — стоимость. Около 800$ на одно рабочее место. Также может несколько затруднить работу английский интерфейс, но эта проблема в принципе решаема.

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

А теперь еще один вопрос — как загнать в эту красоту данные из 1С?

Собственно, ничего страшного. Есть два пути — один попроще, другой мне нравится :-).

Первый способ уже применяется довольно многими. На рынке даже есть решения для облегчения этого пути. Как правило, применяется на базах в формате SQL. Но и на dbf тоже работает. Суть в том, что в качестве источника данных используется непосредственно база 1С. Тогда как в качестве таблицы фактов мы используем таблицу какого-либо регистра, устанавливаем связи с таблицами справочников, и вперед. Дешево и сердито. Особенно хорошо при использовании Analysis Services (SQL 2000 ) — создание уровней измерений с помощью parent-child идеально ложится на структуру таблиц 1С. Для SQL 7.0 мы бы получили абсолютно плоские изменения товаров и покупателей, без групп (а это нарушило бы основное требование OLAP).

Плюсы достаточно очевидны — для обновления данных в кубе достаточно запустить процессинг (process) базы данных в OLAP-менеджере. В источнике данных информация всегда актуальна. Минусы — я с трудом представляю, как использовать такой подход на бухгалтерской компоненте и тем более на расчете. Определенно нельзя использовать в качестве измерений перечисления. Нельзя загнать в куб информацию из двух регистров (есть виртуальные кубы, позволяющие обЪединить несколько кубов, но у них свои недостатки). Нельзя создать многоуровневые измерения с использованием нескольких таблиц. Вообще структура куба достаточно жестко определяется структурой БД. А она может и изменятся: Кроме того, куб, разработанный для одной БД с большой долей вероятности не будет работать с другой, даже при аналогичной конфигурации. И вообще сама идея такого подхода основана на использовании недокументированной информации о хранении данных в 1С (человек, не знакомый со структурой файла 1cv7.dd этот подход использовать не сможет).

Второй способ (я использую именно его) — вынесение источника данных в промежуточное хранилище. Обычно это — база данных Access. Можно использовать таблицы SQL. Там создаются таблицы с учетом того, что мы хотим видеть, далее пишется обработка для переноса в это хранилище данных из рабочей базы (пример работы с Access доступен), и уже на основе этого хранилища делается OLAP -куб. Здесь мы имеем полную свободу — например я в одной не любимой мною конфигурации заношу данные в куб продаж не на основе регистров, а на основе расходных накладных, при этом беру только те, у которых покупатель принадлежит к определенной группе. Вот так приходится отсекать именно продажи от всего остального: А еще я добавляю в куб продаж остатки — чтобы увидеть, что, например, мало продавали просто из-за того, что не было товара на складе, или на линейной диаграмме показать остатки и продажи в штуках — легко оценивается запас товара на складе. А показывать остатки приходится на какого-то покупателя: маразм? Нет, просто требование OLAP. Я то просто выкрутился — добавил в таблицу с покупателями фиктивную запись, в таблицу с накладными тоже и отношу все записи информации об остатках в таблицу фактов на них: Готов держать пари, в первом способе такого не добьешься. Формат базы данных при таком способе абсолютно не важен. Есть также принципиальная возможность (я пока только обдумываю, но делать буду определенно) добавлять информацию из внешних источников — например из файла MS Excel определенного формата: там могут быть данные, скажем, о планах на продажи или о затратах на рекламную компанию — сравнение этих данных прямо на месте с информацией о продажах может оказаться очень интересным. Понятно, что подобная гибкость недостижима при использовании первого подхода.

Ссылка на основную публикацию
Adblock
detector