Приложения. SSAS Tabular, формулы кубов, сообщения об ошибках

Рубрика: 7. Полезняшки Excel

Это окончание перевода книги Роб Колли. Формулы DAX для Power Pivot. Главы не являются независимыми, поэтому рекомендую читать последовательно.

Предыдущая глава        Содержание

В книге мы сделали несколько смелых заявлений о влиянии Power Pivot на вашу карьеру. Мы использовали такие слова, как программист, инженер и разработчик, чтобы описать вашу меняющуюся роль. Однако, в какой-то момент вы, возможно, почувствуете, что PowerPivot уже недостаточно: ваши файлы становятся слишком большими или вы переросли модель доступа «всё или ничего»… Ну что ж, познакомьтесь с большим братом Power Pivot – SSAS Tabular (не путайте с табличными службами SSAS, которые используют тот же движок, что и Power Pivot).

Ris. P.1. Krivaya obucheniya Excel PowerPivot i SSAS Tabular

Рис. П.1. Кривая обучения Excel, PowerPivot и … SSAS Tabular

Скачать заметку в формате Word или pdf

SSAS (SQL Server Analysis Services) долгое время были флагманской платформой BI у Microsoft. Однако традиционный продукт (в настоящее время называемый SSAS Multidimensional) нелегко изучить и использовать. Профессионалы SSAS могут взимать премиальную цену за свои навыки. Но SSAS Tabular – это новый игрок на поле BI. Корпорация Майкрософт не хотела бы публично сказать, что SSAS Tabular отменяет или замещает SSAS Multidimensional, … но поверьте нам, именно это и происходит.

Особенности SSAS Tabular

Хотя базовый движок в SSAS Tabular совпадает с Power Pivot (это DAX), есть некоторые важные отличия:

  • Теоретически нет ограничений на количество строк, которые может содержать модель.
  • Новые функции, такие как Разделы и Безопасность на уровне строк. Разделы позволяют обновлять только самые последние данные, а не всю таблицу. Безопасность на уровне строк может проверять личность пользователя, обращающегося к модели данных, и предоставлять доступ на основе этого. Например, продавец в Северной Америке может видеть только данные о продажах в Северной Америке, даже если модель содержит данные по всему миру.
  • Можно создать модель в Power Pivot, а затем загрузить ее в SSAS Tabular. Но для создания моделей можно использовать и Visual Studio со всеми ее преимуществами. Например, интеграцию с системой управления версиями.
  • Возможности администрирования и использования сценариев. SSAS относится к семейству SQL Server, поэтому доступны все ее инструменты. Можно также создавать сценарии и автоматизировать задачи с помощью языка XMLA.
  • SSAS Tabular позволяет сотням пользователей подключаться к табличным моделям из настольных версий Excel (мы рассмотрели это в предыдущей главе).

Одна из причин, по которой мы любим SSAS Tabular, заключается в том, что он не только прост в использовании, но и прост в установке и администрировании. Настройка SSAS Tabular находится в пределах досягаемости пользователей Excel. Вы можете запустить его менее чем за 30 минут (убедитесь сами на http://ppvt.pro/pp2ssas, и сравните это с SharePoint, который является настоящим зверем для настройки и администрирования).

Хороший способ перехода на SSAS Tabular – продолжить создавать модели в Excel PowerPivot, а затем загружать их в SSAS Tabular. Это позволит вам воспользоваться преимущества SSAS Tabular при сохранении привычной среды разработки, ориентированной на Excel. Больше информации по теме см. http://ppvt.pro/pp2ssas и http://ppvt.pro/pp2tabular.

Формулы кубов

Ris. P.2. Eto interaktivnyj otchet Power Pivot v Excel prichem bez primeneniya svodnyh tablits

Рис. П.2. Это интерактивный отчет Power Pivot в Excel, причем без применения сводных таблиц

В старые времена, до того, как у нас был двигатель DAX, было много разных сценариев, в которых мы создавали одну или несколько сводных таблиц, прятали их на отдельных листах, а затем проникали в них с формулами, чтобы создать окончательный отчет на главном листе. При этом использовалась интуитивно непонятная функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ. Мы были вынуждены шаманить в трех случаях:

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

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

Ris. P.3. Vyberite yachejku v lyuboj svodnoj tablitse

Рис. П.3. Выберите ячейку в любой сводной таблице, построенной на основе Power Pivot, найдите эту кнопку на ленте, нажмите ее и расслабьтесь; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

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

Ris. P.4. Tablitsa na osnove formul kubov

Рис. П.4. Таблица на основе формул кубов

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

Вам не обязательно преобразовывать готовую сводную таблицу. Вы можете написать формулы кубов «с нуля». Например, в Excel-файле примеров из главы 15 создайте новый лист, и в любой ячейке введите следующую формулу:

Ris. P.5. Funktsiya KUBZNACHENIE v yachejke V2 vozvrashhaet obshhie prodazhi velosipedov kak i v svodnoj tablitse

Рис. П.5. Функция КУБЗНАЧЕНИЕ() в ячейке В2 возвращает общие продажи велосипедов, как и в сводной таблице

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

Если вы хотите, чтобы формула куба фильтровалась срезом, продолжите набор формулы: введите точку с запятой и продолжайте вводить сре… Выпадет список автозаполнения всех срезов в книге. Выберите один из них, и теперь эта ячейка будет фильтроваться в соответствии с текущими установками этого среза (в качестве аргументов функции КУБЗНАЧЕНИЕ вы можете последовательно добавить несколько срезов).

Ris. P.6. Avtozapolnenie predlagaet vse imeyushhiesya v modeli srezy

Рис. П.6. Автозаполнение предлагает все имеющиеся в модели срезы

Несколько советов:

  1. Отчеты с формулами кубов – это отчеты с фиксированными координатами. Например, если отчет в качестве строк содержит все страны, в которых вы ведете бизнес, и в следующем месяце вы начнете вести бизнес в новой стране, эта новая страна не появится в отчете автоматически (в отличие от сводной таблицы). Поэтому, если набор данных часто меняется, формулы кубов вряд ли подойдут.
  2. Формулы кубов полезны в дашбордах, панелях KPI, в отдельных ячейках дополнительной информации, размещенных рядом со сводными и диаграммами.
  3. Если вы можете со сводной таблицей сделать то, что вам нужно, не используйте формулы кубов.
  4. Если вы испытываете соблазн написать формулу, которая «захватывает» значение из ячейки сводной таблице, лучше использовать формулу куба (если только вы не можете написать меру или связать таблицы данных в Power Pivot).

См. также список статей по теме http://ppvt.pro/CubeFormulasCat2.

Некоторые распространенные сообщения об ошибках

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

Ris. P.7. Oshibki sinhronizatsii nadstrojka Power Pivot i Excel

Рис. П.7. Ошибки синхронизации надстройка Power Pivot и Excel

Чтобы исправить ситуацию вам по сути нужно перезагрузить надстройку Power Pivot. Вы можете сделать это одним из трех способов:

  1. Отказаться от текущей сводной и создать новую. У новой сводной не будет этой проблемы.
  2. Выключить надстройку Power Pivot и затем включить снова (в Excel пройдите по меню Файл –> Параметры –> Надстройки; внизу окна в области Управление выберите Надстройки Com, кликните Перейти; снимите галочку напротив Microsoft Power Pivot for Excel; нажмите Ok; закройте окно Параметры Excel; повторите действия, но теперь поставьте галочку).
  3. Сохраните и закройте все книги Excel, закройте само приложение Excel, а затем снова откройте книгу.

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

Ошибка инициализации источника данных. Мы постоянно наблюдали эту ошибку в более ранних версиях Excel:

Ris. P.8. Oshibka initsializatsii

Рис. П.8. Ошибка инициализации

Вы можете полностью игнорировать это сообщение. Просто нажмите OK.

Другие страшные, но безвредные ошибки

Ris. P.9. Drugie strashnye no bezvrednye oshibki

А). Эти необработанные исключения появляются время от времени и очень редко указывают на то, что что-то действительно не так. Просто игнорируйте их. Если всё же что-то пошло не так, перезапустите Excel.

Б). Не удалось обновить лингвистическую схему. Вот это номер!

В). Если вы видите такое окно, возможно, вы находитесь в режим редактирования формулы в окне Power Pivot. Нажмите Esc, перейдите из Excel в Power Pivot, выйдите из режима редактирования, и вернитесь в Excel. Если вы не редактируете формулу в окне Power Pivot, просто закройте указанное окно (работа не будет потеряна), и ошибка исчезнет.


Прокомментировать