Это окончание перевода книги Роб Колли. Формулы DAX для Power Pivot. Главы не являются независимыми, поэтому рекомендую читать последовательно.
В книге мы сделали несколько смелых заявлений о влиянии Power Pivot на вашу карьеру. Мы использовали такие слова, как программист, инженер и разработчик, чтобы описать вашу меняющуюся роль. Однако, в какой-то момент вы, возможно, почувствуете, что PowerPivot уже недостаточно: ваши файлы становятся слишком большими или вы переросли модель доступа «всё или ничего»… Ну что ж, познакомьтесь с большим братом Power Pivot – SSAS Tabular (не путайте с табличными службами SSAS, которые используют тот же движок, что и Power Pivot).
Рис. П.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.
Формулы кубов
Рис. П.2. Это интерактивный отчет Power Pivot в Excel, причем без применения сводных таблиц
В старые времена, до того, как у нас был двигатель DAX, было много разных сценариев, в которых мы создавали одну или несколько сводных таблиц, прятали их на отдельных листах, а затем проникали в них с формулами, чтобы создать окончательный отчет на главном листе. При этом использовалась интуитивно непонятная функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ. Мы были вынуждены шаманить в трех случаях:
- Когда была нужна одна и та же сводная с разными фильтрами, чтобы создать окончательный отчет, состоящий из соотношений или процентов между этими разными подмножествами данных.
- Когда было две таблицы данных, и, поскольку мы не могли соединить их вместе в одной широкой таблице, мы создали две сводные, а затем строили отчет на их основе.
- Когда была нужна форма отчета, которую сводная не позволяла создать.
Сейчас функция CALCULATE() позволяет преодолеть первую проблему, создав фильтры в самих мерах. Связи между таблицами позволяют преодолеть второе препятствие. Но третья проблема все еще остается… пока кто-нибудь не покажет вам эту кнопку…
Рис. П.3. Выберите ячейку в любой сводной таблице, построенной на основе Power Pivot, найдите эту кнопку на ленте, нажмите ее и расслабьтесь; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке
Числа сохранятся, причем это будут не значения, а формулы, которые извлекают данные из модели данных, но теперь вы может отформатировать их, как захотите. В том числе, вы можете вставлять строки и столбцы внутрь этой таблицы.
Рис. П.4. Таблица на основе формул кубов
Данные по-прежнему «живут». Срезы, которые были подключены к сводной перед преобразованием, по-прежнему будут «нарезать» числа в отдельных ячейках. При обновлении исходных данных числа в таблице также обновятся.
Вам не обязательно преобразовывать готовую сводную таблицу. Вы можете написать формулы кубов «с нуля». Например, в Excel-файле примеров из главы 15 создайте новый лист, и в любой ячейке введите следующую формулу:
1 2 3 |
=КУБЗНАЧЕНИЕ("ThisWorkbookDataModel"; "[Measures].[Total Sales]"; "[Products].[Category].[All].[Bikes]") |
Рис. П.5. Функция КУБЗНАЧЕНИЕ() в ячейке В2 возвращает общие продажи велосипедов, как и в сводной таблице
На самом деле, двигатель DAX не знает разницы между формулами куба в ячейке и контекстом фильтра в сводной таблице.
Если вы хотите, чтобы формула куба фильтровалась срезом, продолжите набор формулы: введите точку с запятой и продолжайте вводить сре… Выпадет список автозаполнения всех срезов в книге. Выберите один из них, и теперь эта ячейка будет фильтроваться в соответствии с текущими установками этого среза (в качестве аргументов функции КУБЗНАЧЕНИЕ вы можете последовательно добавить несколько срезов).
Рис. П.6. Автозаполнение предлагает все имеющиеся в модели срезы
Несколько советов:
- Отчеты с формулами кубов – это отчеты с фиксированными координатами. Например, если отчет в качестве строк содержит все страны, в которых вы ведете бизнес, и в следующем месяце вы начнете вести бизнес в новой стране, эта новая страна не появится в отчете автоматически (в отличие от сводной таблицы). Поэтому, если набор данных часто меняется, формулы кубов вряд ли подойдут.
- Формулы кубов полезны в дашбордах, панелях KPI, в отдельных ячейках дополнительной информации, размещенных рядом со сводными и диаграммами.
- Если вы можете со сводной таблицей сделать то, что вам нужно, не используйте формулы кубов.
- Если вы испытываете соблазн написать формулу, которая «захватывает» значение из ячейки сводной таблице, лучше использовать формулу куба (если только вы не можете написать меру или связать таблицы данных в Power Pivot).
См. также список статей по теме http://ppvt.pro/CubeFormulasCat2.
Некоторые распространенные сообщения об ошибках
Следующие три ошибки указывают на то, что надстройка Power Pivot и Excel не синхронизированы друг с другом. Например, Power Pivot знает о поле, которое вы пытаетесь добавить, но Excel не думает, что это поле существует. Это происходит с недавно созданными полями. Мы никогда не видели, чтобы это происходило с полем, которое мы уже использовали в сводной таблице.
Рис. П.7. Ошибки синхронизации надстройка Power Pivot и Excel
Чтобы исправить ситуацию вам по сути нужно перезагрузить надстройку Power Pivot. Вы можете сделать это одним из трех способов:
- Отказаться от текущей сводной и создать новую. У новой сводной не будет этой проблемы.
- Выключить надстройку Power Pivot и затем включить снова (в Excel пройдите по меню Файл –> Параметры –> Надстройки; внизу окна в области Управление выберите Надстройки Com, кликните Перейти; снимите галочку напротив Microsoft Power Pivot for Excel; нажмите Ok; закройте окно Параметры Excel; повторите действия, но теперь поставьте галочку).
- Сохраните и закройте все книги Excel, закройте само приложение Excel, а затем снова откройте книгу.
Кстати, если вы просто добавили таблицу, столбец или меру в модель данных, и они не отображаются в списке полей, попробуйте те же действия.
Ошибка инициализации источника данных. Мы постоянно наблюдали эту ошибку в более ранних версиях Excel:
Рис. П.8. Ошибка инициализации
Вы можете полностью игнорировать это сообщение. Просто нажмите OK.
Другие страшные, но безвредные ошибки
А). Эти необработанные исключения появляются время от времени и очень редко указывают на то, что что-то действительно не так. Просто игнорируйте их. Если всё же что-то пошло не так, перезапустите Excel.
Б). Не удалось обновить лингвистическую схему. Вот это номер!
В). Если вы видите такое окно, возможно, вы находитесь в режим редактирования формулы в окне Power Pivot. Нажмите Esc, перейдите из Excel в Power Pivot, выйдите из режима редактирования, и вернитесь в Excel. Если вы не редактируете формулу в окне Power Pivot, просто закройте указанное окно (работа не будет потеряна), и ошибка исчезнет.