Построение OLAP отчета в Excel

OLAP, SSAS  Построение Категория:  OLAP, SSAS EXCEL, ВИЗУАЛИЗАЦИЯ ДАННЫХ
Опубликовал:         15.11.2011               print

Предположим, поставлена задача построить следующие диаграммы:
- показать динамику (по отчетным датам) за 2010-2011гг. сумм общего основного долга USD по каждой из групп просрочки основного долга (ОД) 60+ (т.е. группы 60...90, 91...120, 121...150, и т.д.), рассчитанной по FIFO;
- на отчетную дату показать суммы просроченного основного долга USD по группам просрочки ОД FIFO (1..30, 31..60, 61..90, 90+) в разрезах банковских продуктов;
Желательно создать единый фильтр (Sliсer) по Центрам финансовых услуг (ЦФУ) и подключить его к обеим диаграммам.
В результате должна получиться информационная панель вида:



В Excel 2010 создадим новый лист и выполним подключение к имеющемуся OLAP-кубу.

Создадим сводную таблицу, на основе которой затем построим диаграмму № 1.
В область фильтров сводной таблицы поместим иерархию "Год-квартал-месяц-дата" измерения "Отчетные даты" (отфильтруем 2010 и 2011 годы), иерархию "Группа 0-60-60плюс" измерения "Группы просрочки ОД FIFO" (отфильтруем элемент "60+"), вспомогательное измерение "_Правила оформления" (вспомогательные измерения - это служебные измерения, не привязанные ни к одной из группе мер).


В область строк сводной таблицы перетянем иерархию "Год-месяц-дата из измерения "Отчетные даты". В область столбцов сводной таблицы поместим иерархию "Группы" измерения "Группы просрочки ОД FIFO", раскроем иерархию до уровня "Группа просрочки" и скроем уровень "Группа 0-30-60-90-90плюс" (находясь на измерении, щелчок правой кнопкой мыши -> в контекстном меню пункт "Показать / скрыть поля"). В область значений сводной таблицы поместим показатель "Общий остаток ОД" из папки показателей "Остатки\Балансовые".



Теперь создадим сводную таблицу для будущей диаграммы № 2.
В область фильтров сводной таблицы поместим иерархию "Год-квартал-месяц-дата" измерения "Отчетные даты" (отфильтруем элемент "20.05.2011"), вспомогательное измерение "_Правила оформления".

В область строк сводной таблицы перетянем иерархию "Группы продуктов-продукты" измерения "Банковские продукты". В область столбцов сводной таблицы поместим иерархию "Группы" измерения "Группы просрочки ОД FIFO".

В область значений сводной таблицы поместим показатель "Остаток просроченного ОД" из папки показателей "Остатки\Балансовые".

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



В обеих сводных таблицах задействуем вспомогательное измерение "_Правила оформления", отметив элемент "выделение цветом согласно группам просрочки ОД FIFO", а для первой (слева) сводной таблицы ещё отметим элементы "округление до тысяч сумм показателей", "показывать аббревиатуры округлений сумм".
С помощью базового функционала Excel установим формат ячеек всего листа: поменьше шрифт, выравнивание по центру, перенос по словам, границы (рамки) нужных ячеек.
Выше над таблицами вставим пустые строки для размещения наших диаграмм, в результате должна получиться следующая картина:




Позиционируем курсор на первой сводной таблице, и для неё создаем график с областями накопления:




Аналогично для сводной таблицы №2 создаем гистограмму с накоплением:



В каждой диаграмме:
- вставим подписи вертикальных осей: главное меню "Макет" -> "Название осей" -> "Название основной вертикальной оси" -> "Повернутое название" (см. предыдущий пример создания графических отчётов);
- вставим заголовки диаграммы: главное меню "Макет" -> "Название диаграммы" -> "Название по центру с перекрытием";
- главное меню "Макет" -> "Легенда" -> "Добавить легенду снизу" - легенда будет размещена внизу под графиком (затем легенду можно отбуксировать в желаемое место).


Между таблицами вставим пустые колонки про запас и скроем их на тот случай, если в левой таблице увеличится количество столбцов, она не будет перекрывать правую таблицу. Чтобы зафиксировать размеры и позиции диаграмм, воспользуемся контекстным меню (щелчок правой кнопкой мыши на границе диаграммы) -> "Формат области диаграммы…" -> "Свойства" -> "Не перемещать и не изменять размеры".


Группы просрочки целесообразнее упорядочить в обратном порядке, поскольку:
- будет видно, какой объем составляют вместе, например, просрочка 360 и 360+ ;
- при прямом порядке сортировки в случае роста объема наихудшей просрочки соответствующая ей полоса графика будет расширяться, но падать вниз, при обратном порядке сортировки зрительное восприятие графика улучшается.


Воспользуемся следующим приемом: выделим какой-либо элемент уровня "Группа просрочки", поскольку это ключевой атрибут измерения, то у него есть свойства, среди которых посредством контекстного меню выберем для отображения "Начало диапазона дней"; затем выделим любой элемент свойства и отсортируем колонки в обратном порядке (сортировка от Я до А); используя базовый функционал Excel - формат ячеек, "спрячем" элементы свойства – установим белый шрифт на белом фоне.


Упорядочивание можно сделать проще: щелчок правой кнопкой мыши на группе просрочки -> в контекстном меню выбираем "Сортировка" -> "Дополнительные параметры сортировки…" -> "Вручную" -> затем вручную поменять порядок столбцов, захватывая и перетаскивая ячейку заголовка каждой колонки.



Правее графиков вставим срезы: главное меню "Вставка" -> "Срез" -> выбираем атрибут "ЦФУ" иерархии "Подразделения - ЦФУ" измерения "ЦФУ". Аналогично вставим срез по атрибуту "Валюта" измерения "_Валюты конверсии".

Свяжем наши срезы со сводными таблицами: выделяем срез -> щелчок правой кнопкой мыши -> в контекстном меню выбираем пункт "Подключения к сводной таблице…" -> в диалоговом окне отмечаем все сводные таблицы -> нажимаем кнопку "OK".

Настроим оформление срезов. Выделим срез "Валюта эквивалента" -> в главном меню выбираем "Параметры" -> определяем количество столбцов в панели среза, высоту и ширину строк. Через пункт контекстного меню среза "Настройка среза…" переопределяем его заголовок в "Валюта эквивалента".



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


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


Остановимся на некоторых моментах:

  1. в зависимости от задачи и структуры данных следует выбирать наиболее подходящий вид графика. Так, говоря о динамике и структуре нескольких категорий, оптимален тип графика "с областями и накоплением"; говоря о сравнительном анализе многих категорий (например, филиалы) и их структуре (например, группы просрочки по кредитам) – столбцевая гистограмма с накоплением;
  2. диаграммы должны иметь содержательные заголовки, отражающие цель графика, оси координат - подписи; в заголовках не нужно констатировать / дублировать текст подписей осей;
  3. для слишком больших числовых значений следует использовать округление, например, до тысяч, миллионов, не забывая об отображении аббревиатур округлений;
  4. использовать единую цветовую палитру (в приоритете – корпоративно принятую), цвет тоже может нести важную смысловую нагрузку, визуализировать дополнительное измерение;
  5. информационные панели должны оформляться так, чтобы была сведена к минимуму необходимость прокрутки экрана по горизонтали / вертикали (оптимально – обзор панели одним взглядом), для чего задействовать штатные средства форматирования Excel (размер шрифта, высоту строк, ширину столбцов, перенос слов и т.д.);
  6. необходимо учитывать зрительную особенность человека: самая воспринимая область – левый верхний квадрант экрана -> здесь должна размещаться наиболее важная информация, наименее воспринимая область – правый нижний квадрант экрана;
  7. диаграммы не должны быть перегружены излишними вспомогательными метками (например, элементами фильтров).


Энергия идеи   dvbi.ru                    Последнее изменение: 2021-12-12 22:23:00Z         Возрастная аудитория: 14-70         Комментариев:  0
Теги:  Графики
Связанные статьи:

Пожалуйста, проголосуйте и ниже поставьте лайк:   rating


  Комментарии



Следующая статья:    Почему именно BI от Microsoft
Предыдущая статья:  Работа с web-службами в SQL Server Integration Services на примере сервисов ЦБ РФ