Работа с автономными файлами кубов. Проектирование кубов данных

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

Дополнительные сведения об автономных кубах

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

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

С помощью мастера автономного куба

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

Перевод данных в автономный режим, а затем перенос данных обратно в Интернете

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

Ниже описаны основные этапы перевода данных в автономный режим и их возврата в оперативный режим.

Примечание:

Щелкните отчет сводной таблицы. Если это отчет сводной диаграммы, выберите связанный отчет сводной таблицы.

На вкладке " Анализ " в группе вычисления нажмите кнопку Сервис OLAP и нажмите кнопку Автономно OLAP .

Выберите пункт OLAP при наличии связи , а затем нажмите кнопку ОК .

Если будет предложено найти источник данных, нажмите кнопку Найти источник и найдите OLAP-сервер в сети.

Щелкните отчет сводной таблицы, основанный на файле автономного куба.

В Excel 2016: На вкладке " данные " в группе запросы и подключения Обновить все и нажмите кнопку Обновить .

В Excel 2013: На вкладке " данные " в группе подключения щелкните стрелку рядом с кнопкой Обновить все и нажмите кнопку Обновить .

На вкладке " Анализ " в группе вычисления нажмите кнопку Сервис OLAP и нажмите кнопку Автономно OLAP .

Нажмите кнопку Автономный режим OLAP , а затем - .

Примечание: Остановить в диалоговом окне .

Предупреждение:

Создание автономного файла куба из базы данных OLAP-сервера

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

Проблема: Моя компьютера недостаточно места на диске при сохранении куба.

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

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

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

Подключение автономного файла куба к базе данных OLAP-сервера

Обновление и повторное создание автономного файла куба

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

Проблема: Новые данные не отображается в отчете, когда обновлять.

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

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

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

Включение в файл автономного куба других данных

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

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

Щелкните отчет сводной таблицы, созданный на основе автономного файла куба, или связанный отчет сводной таблицы для отчета сводной диаграммы.

На вкладке Параметры в группе Сервис нажмите кнопку Сервис OLAP и нажмите кнопку Автономный режим OLAP .

Нажмите кнопку Автономный режим OLAP , а затем - Изменить автономный файл данных .

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

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

Удаление автономного файла куба

Предупреждение: Если удалить файл автономного куба для какого-либо отчета, больше нельзя будет использовать этот отчет автономно и создать файл автономного куба для этого отчета.

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

В Microsoft Windows найдите и удалите автономный файл куба (файл CUB).

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community , попросить помощи в сообществе Answers community , а также предложить новую функцию или улучшение на веб-сайте

В предыдущей статье данного цикла (см. № 2’2005) мы рассказали об основных новшествах аналитических служб SQL Server 2005. Сегодня мы подробнее рассмотрим средства создания OLAP-решений, входящие в этот продукт.

Коротко об основах OLAP

режде чем начать разговор о средствах создания OLAP-решений, напомним, что OLAP (On-Line Analytical Processing) — это технология комплексного многомерного анализа данных, концепция которой была описана в 1993 году Э.Ф.Коддом, знаменитым автором реляционной модели данных. В настоящее время поддержка OLAP реализована во многих СУБД и иных инструментах.

OLAP-кубы

Что представляют собой OLAP-данные? В качестве ответа на этот вопрос рассмотрим простейший пример. Предположим, в корпоративной базе данных некоего предприятия имеется набор таблиц, содержащих сведения о продажах товаров или услуг, и на их основе создано представление Invoices с полями Country (страна), City (город), CustomerName (название компании-клиента), Salesperson (менеджер по продажам), OrderDate (дата размещения заказа), CategoryName (категория товара), ProductName (наименование товара), ShipperName (компания-перевозчик), ExtendedPrice (оплата за товар), при этом последнее из перечисленных полей, собственно, и является объектом анализа.

Выбор данных из такого представления можно осуществить с помощью следующего запроса:

SELECT Country, City, CustomerName, Salesperson,

OrderDate, CategoryName, ProductName, ShipperName, ExtendedPrice

FROM Invoices

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

SELECT Country, SUM (ExtendedPrice) FROM Invoices

GROUP BY Country

Результатом этого запроса будет одномерный набор агрегатных данных (в данном случае — сумм):

Если же мы хотим узнать, какова суммарная стоимость заказов, сделанных клиентами из разных стран и доставленных различными службами доставки, мы должны выполнить запрос, содержащий два параметра в предложении GROUP BY:

SELECT Country, ShipperName, SUM (ExtendedPrice) FROM Invoices

GROUP BY COUNTRY, ShipperName

Исходя из результатов этого запроса можно создать таблицу следующего вида:

Такой набор данных называется сводной таблицей (pivot table).

SELECT Country, ShipperName, SalesPerson SUM (ExtendedPrice) FROM Invoices

GROUP BY COUNTRY, ShipperName, Year

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

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

Иерархии в измерениях

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

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

Создание OLAP-кубов в SQL Server 2005

SQL Server 2005 кубы создаются с помощью SQL Server Business Intelligence Development Studio. Этот инструмент представляет собой специальную версию Visual Studio 2005, предназначенную для решения данного класса задач (а при наличии уже установленной среды разработки список шаблонов проектов пополняется проектами, предназначенными для создания решений на основе SQL Sever и его аналитических служб). В частности, для создания решений на основе аналитических служб предназначен шаблон Analysis Services Project (рис. 3).

Для создания OLAP-куба в первую очередь следует решить, на основе каких данных его формировать. Наиболее часто OLAP-кубы строятся на основе реляционных хранилищ данных со схемами «звезда» или «снежинка» (о них мы рассказывали в предыдущей части статьи). В комплекте поставки SQL имеется пример такого хранилища — база данных AdventureWorksDW, для использования которой в качестве источника следует найти в Solution Explorer папку Data Sources, выбрать пункт контекстного меню New Data Source и последовательно ответить на вопросы соответствующего мастера (рис. 4).

Затем рекомендуется создать Data Source View — представление, на основе которого будет создаваться куб. Для этого необходимо выбрать соответствующий пункт контекстного меню папки Data Source Views и последовательно ответить на вопросы мастера. Результатом указанных действий станет схема данных, с помощью которых будет построено представление источников данных, при этом в полученной схеме вместо исходных можно указать «дружественные» имена таблиц (рис. 5).

Описанный таким образом куб можно перенести на сервер аналитических служб, выбрав из контекстного меню проекта опцию Deploy, и осуществить просмотр его данных (рис. 7).

При создании кубов в настоящее время используются многие особенности новой версии SQL Server, такие, например, как представление источников данных. Описание исходных данных для построения куба, равно как и описание структуры куба, теперь производится с помощью знакомого многим разработчикам инструмента Visual Studio, что является немалым достоинством новой версии этого продукта — изучение разработчиками аналитических решений нового инструментария в этом случае сведено к минимуму.

Отметим, что в созданном кубе можно менять состав мер, удалять и добавлять атрибуты измерений и добавлять вычисляемые атрибуты членов измерений на основе имеющихся атрибутов (рис. 8).

Рис. 8. Добавление вычисляемого атрибута

Кроме того, в кубах SQL Server 2005 можно осуществлять автоматическую группировку или сортировку членов измерения по значению атрибута, определять связи между атрибутами, реализовывать связи «многие ко многим», определять ключевые показатели бизнеса, а также решать многие другие задачи (подробности о том, как выполняются все эти действия, можно найти в разделе SQL Server Analysis Services Tutorial справочной системы данного продукта).

В последующих частях данной публикации мы продолжим знакомство с аналитическими службами SQL Server 2005 и выясним, что нового появилось в области поддержки Data Mining.

Довольно давно являюсь обитателем Хабра, но так и не доводилось читать статьи на тему многомерных кубов, OLAP и MDX, хотя тема очень интересная и с каждым днем становится все более актуальной.
Не секрет, что за тот небольшой промежуток времени развития баз данных, электронного учета и онлайн систем, самих данных накопилось очень много. Теперь же интерес также представляет полноценный анализ архивов, а возможно и попытка прогнозирования ситуаций для подобных моделей в будущем.
С другой стороны, большие компании даже за несколько лет, месяцев или даже недель могут накапливать настолько большие массивы данных, что даже их элементарный анализ требует неординарных подходов и жестких аппаратных требований. Такими могут быть системы обработки банковских транзакций, биржевые агенты, телефонные операторы и т.д.
Думаю, всем хорошо известны 2 разных подхода построения дизайна баз данных: OLTP и OLAP. Первый подход (Online Transaction Processing - обработка транзакций в реальном времени) рассчитан на эффективный сбор данных в реальном времени, второй же (Online Analytical Processing – аналитическая обработка в реальном времени) нацелен именно на выборку и обработку данных максимально эффективным способом.

Давайте рассмотрим основные возможности современных OLAP кубов, и какие задачи они решают (за основу взяты Analysis Services 2005/2008):

• быстрый доступ к данным
• преагрегация
• иерархии
• работа с временем
• язык доступа к многомерным данным
• KPI (Key Performance Indicators)
• дата майнинг
• многоуровневое кэширование
• поддержка мультиязычности

Немного подробнее о возможностях

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

Иерархии
Закономерно, что анализируя данные и строя конечные отчеты, возникает потребность учитывать то, что месяцы состоят из дней, а сами образуют кварталы, а города входят в области, которые в свою очередь являются частью регионов или стран. Хорошая новость то, что OLAP кубы изначально рассматривают данные с точки зрения иерархий и взаимоотношений с другими параметрам одной и той же сущности, так что построение и использования иерархией в кубах – дело очень простое.

Работа с временем
Так как в основном анализ данных происходит на временных участках, именно времени в OLAP системах выделено особое значение, а значит, просто определив для системы, где у нас тут время, в дальнейшем можно с легкостью пользоваться функциями типа Year To Date, Month To Date (период от начала года/месяца и до текущей даты), Parallel Period (в этот же день или месяц, но в прошлом году) и т.п.

Язык доступа к многомерным данным
MDX (Multidimensional Expressions) - язык запросов для простого и эффективного доступа к многомерным структурам данных. И этим все сказано – внизу будет несколько примеров.

Key Performance Indicators (KPI)
Ключевые показатели эффективности - это финансовая и нефинансовая система оценки, которая помогает организации определить достижение стратегических целей. Ключевые показатели эффективности могут быть достаточно просто определены в OLAP системах и использоваться в отчетах.

Дата майнинг
Интеллектуальный анализ данных (Data Mining) - по сути, выявление скрытых закономерностей или взаимосвязей между переменными в больших массивах данных.
Английский термин «Data Mining» не имеет однозначного перевода на русский язык (добыча данных, вскрытие данных, информационная проходка, извлечение данных/информации) поэтому в большинстве случаев используется в оригинале. Наиболее удачным непрямым переводом считается термин «интеллектуальный анализ данных» (ИАД). Впрочем, это отдельная, не менее интересная тема для рассмотрения.

Многоуровневое кэширование
Собственно для обеспечения наиболее высокой скорости доступа к данным, кроме хитрых структур данных и преагрегаций, OLAP системы поддерживают многоуровневое кэширование. Кроме кэширования простых запросов, также кэшируются части вычитанных из хранилища данных, агрегированные значения, вычисленные значения. Таким образом, чем дольше работаешь с OLAP кубом, тем быстрее он, по сути, начинает работать. Также существует понятие «разогрев кэша» - операция, подготавливающая OLAP систему к работе с конкретными отчетами, запросами или всем вместе взятым.

Поддержка мультиязычности
Да-да-да. Как минимум Analysis Services 2005/2008 (правда, Enterprise Edition) нативно поддерживают мультиязычность. Достаточно привести перевод строковых параметров ваших данных, и клиенту, указавшему свой язык, будут приходить локализированные данные.

Многомерные кубы

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

Немного MDX

SELECT
{ . } ON COLUMNS,
{ ., . } ON ROWS
FROM
WHERE (., .)

Что означает – хочу количество iPhone-ов, проданных в июне и июле в Мозамбике.
При этом я описываю какие именно данные я хочу и как именно я хочу их увидеть в отчете.
Красиво, не правда ли?

А вот чуть посложнее:

WITH MEMBER AverageSpend AS
. / .
SELECT
{ AverageSpend } ON COLUMNS,
{ .., .. } ON ROWS
FROM
WHERE (.)

* This source code was highlighted with Source Code Highlighter .

Фактически, вначале определяем формулу подсчета «среднего размера покупки» и пытаемся сравнить – кто же (какой пол), за один заход в магазин Apple, тратит больше денег.

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

Заключение

P.S. Это мой первый пост об OLAP и первая публикацию на Хабре - буду очень признателен за конструктивный фидбек.
Update: Перенес в SQL, перенесу в OLAP как только разрешат создавать новые блоги.

Теги: Добавить метки

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

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

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

4. Происходит пересчет куба – данные попадают в куб.

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

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

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

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

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

Локальные кубы

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

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

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

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

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

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

Плюсы

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

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

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

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

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

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

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

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

Минусы:

• Стоимость внедрения. Необходимо дополнительное оборудование и программное обеспечение.
• Нехватка подготовленных специалистов. Расходы на обучение сотрудников it-отдела.