Расширение модели данных

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

• Добавление таблиц
• Добавление вычисляемых столбцов в существующую таблицу
• Создание мер в существующей таблице

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

Поскольку анализ прибылей и убытков предполагает работу с периодами времени, и вы будете использовать функции DAX Time Intelligence, вам потребуется таблица Date в модели данных.

Если вы новичок в таблицах дат, ознакомьтесь с главой — Общие сведения о таблицах дат.

Вы можете расширить модель данных следующим образом:

• Чтобы создать связь между таблицей данных, т. Е. Таблицей финансовых данных и таблицей дат, необходимо создать вычисляемый столбец Дата в таблице финансовых данных.

• Для выполнения различных типов вычислений необходимо создать взаимосвязи между таблицей данных — Финансовые данные и таблицами поиска — Счета и География.

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

Чтобы создать связь между таблицей данных, т. Е. Таблицей финансовых данных и таблицей дат, необходимо создать вычисляемый столбец Дата в таблице финансовых данных.

Для выполнения различных типов вычислений необходимо создать взаимосвязи между таблицей данных — Финансовые данные и таблицами поиска — Счета и География.

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

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

Далее вы узнаете, как создавать показатели и как их использовать в сводных таблицах Power в последующих главах. Это даст вам достаточное понимание моделирования данных с помощью DAX и анализа данных с помощью Power PivotTables.

Добавление таблицы дат в модель данных

Создайте таблицу Date для периодов времени, охватывающих финансовые годы, следующим образом:

• Создайте таблицу с одним столбцом с заголовком — Дата и смежные даты в диапазоне от 1 июля 2011 года по 30 июня 2008 года на новом листе Excel.

• Скопируйте таблицу из Excel и вставьте ее в окно Power Pivot. Это создаст новую таблицу в модели данных Power Pivot.

• Назовите таблицу как Дата.

• Убедитесь, что столбец Date в таблице Date имеет тип данных — Date (DateTime).

Создайте таблицу с одним столбцом с заголовком — Дата и смежные даты в диапазоне от 1 июля 2011 года по 30 июня 2008 года на новом листе Excel.

Скопируйте таблицу из Excel и вставьте ее в окно Power Pivot. Это создаст новую таблицу в модели данных Power Pivot.

Назовите таблицу как Дата.

Убедитесь, что столбец Date в таблице Date имеет тип данных — Date (DateTime).

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

Отчетный год

Предположим, что конец финансового года — 30 июня. Затем финансовый год длится с 1 июля по 30 июня. Например, период с 1 июля 2011 г. (01.07.2011) по 30 июня 2012 г. (30 июня 2012 г.) будет 2012 финансовым годом.

Предположим, вы хотите представить в таблице Date то же самое, что и FY2012.

• Вам необходимо сначала извлечь часть финансового года Даты и добавить ее в FY.

  • Для дат в период с июля 2011 года по декабрь 2011 года финансовый год составляет 1 + 2011.

  • Для дат в период с января 2012 года по июнь 2012 года финансовый год 0 + 2012.

  • Чтобы обобщить, если месяц финансового года — FYE, сделайте следующее:

    Целая часть ((месяц — 1) / год) + год

  • Далее, возьмите самые правильные 4 символа, чтобы получить финансовый год.

• В DAX вы можете представлять так же, как —

  ПРАВЫЙ (INT ((МЕСЯЦ ( ‘Дата’ [Дата]) — 1) / ‘Дата’ [FYE]) + Год ( ‘Дата’ [Дата]), 4)

• Добавьте вычисляемый столбец Fiscal Year в таблицу Date с формулой DAX —

  = «ФГ» & ПРАВЫЙ (INT ((МЕСЯЦ ( ‘Дата’ [Дата]) — 1) / ‘Дата’ [FYE]) + Год ( ‘Дата’ [Дата]), 4)

Вам необходимо сначала извлечь часть финансового года Даты и добавить ее в FY.

Для дат в период с июля 2011 года по декабрь 2011 года финансовый год составляет 1 + 2011.

Для дат в период с января 2012 года по июнь 2012 года финансовый год 0 + 2012.

Чтобы обобщить, если месяц финансового года — FYE, сделайте следующее:

Целая часть ((месяц — 1) / год) + год

Далее, возьмите самые правильные 4 символа, чтобы получить финансовый год.

В DAX вы можете представлять так же, как —

ПРАВЫЙ (INT ((МЕСЯЦ ( ‘Дата’ [Дата]) — 1) / ‘Дата’ [FYE]) + Год ( ‘Дата’ [Дата]), 4)

Добавьте вычисляемый столбец Fiscal Year в таблицу Date с формулой DAX —

= «ФГ» & ПРАВЫЙ (INT ((МЕСЯЦ ( ‘Дата’ [Дата]) — 1) / ‘Дата’ [FYE]) + Год ( ‘Дата’ [Дата]), 4)

Фискальный квартал

Если FYE представляет месяц финансового года, финансовый квартал получается как

Целая часть ((Остаток ((Месяц + FYE-1) / 12) + 3) / 3)

• В DAX вы можете представлять так же, как —

  INT ((MOD (МЕСЯЦ ( ‘Дата’ [Дата]) + ‘Дата’ [FYE] -1,12) + 3 ) / 3)

• Добавьте вычисляемый столбец Fiscal Quarter в таблицу Date с формулой DAX —

  = ‘Date’ [FiscalYear] & «- Q» & FORMAT (INT ((MOD (MONTH (‘Date’ [Date]) + ‘Date’ [FYE] -1,12) + 3) / 3), «0» )

В DAX вы можете представлять так же, как —

INT ((MOD (МЕСЯЦ ( ‘Дата’ [Дата]) + ‘Дата’ [FYE] -1,12) + 3 ) / 3)

Добавьте вычисляемый столбец Fiscal Quarter в таблицу Date с формулой DAX —

= ‘Date’ [FiscalYear] & «- Q» & FORMAT (INT ((MOD (MONTH (‘Date’ [Date]) + ‘Date’ [FYE] -1,12) + 3) / 3), «0» )

Финансовый месяц

Если FYE представляет конец финансового года, период финансового месяца получается как

(Остаток (Месяц + FYE-1) / 12) + 1

• В DAX вы можете представлять так же, как —

  MOD (МЕСЯЦ ( ‘Дата’ [Дата]) + ‘Дата’ [FYE] -1,12) +1

• Добавьте вычисляемый столбец Fiscal Month в таблицу Date с формулой DAX —

  = ‘Дата’ [Финансовый год] & «- P» & ФОРМАТ (MOD (МЕСЯЦ ([Дата]) + [FYE] -1,12) +1, «00»)

В DAX вы можете представлять так же, как —

MOD (МЕСЯЦ ( ‘Дата’ [Дата]) + ‘Дата’ [FYE] -1,12) +1

Добавьте вычисляемый столбец Fiscal Month в таблицу Date с формулой DAX —

= ‘Дата’ [Финансовый год] & «- P» & ФОРМАТ (MOD (МЕСЯЦ ([Дата]) + [FYE] -1,12) +1, «00»)

Месяц

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

= ФОРМАТ (MOD (МЕСЯЦ ([Дата]) + [FYE] -1,12) +1, «00») & «-» & ФОРМАТ ([Дата], «ммм»)

Результирующая таблица Date выглядит следующим образом.

Пометьте таблицу — Date as Date Table в столбце — Date как столбец с уникальными значениями, как показано на следующем снимке экрана.

Добавление вычисляемых столбцов

Чтобы создать связь между таблицей «Финансовые данные» и таблицей «Дата», требуется столбец значений «Дата» в таблице «Финансовые данные».

• Добавьте вычисляемый столбец Дата в таблице «Финансовые данные» с формулой DAX —

  = DATEVALUE («Финансовые данные» [Финансовый месяц])

Добавьте вычисляемый столбец Дата в таблице «Финансовые данные» с формулой DAX —

= DATEVALUE («Финансовые данные» [Финансовый месяц])

Определение отношений между таблицами в модели данных

У вас есть следующие таблицы в модели данных —

• Таблица данных — Финансовые данные
• Таблицы поиска — счета и география Locn
• Таблица дат — Дата

Чтобы определить отношения между таблицами в модели данных, выполните следующие действия:

• Просмотрите таблицы в представлении схемы Power Pivot.

• Создайте следующие отношения между таблицами —

  • Связь между таблицей данных финансов и таблицей счетов со столбцом Счет.

  • Связь между таблицей финансовых данных и географической таблицей Locn с колонкой Profit Center.

  • Связь между таблицей финансовых данных и таблицей даты со столбцом Дата.

Просмотрите таблицы в представлении схемы Power Pivot.

Создайте следующие отношения между таблицами —

Связь между таблицей данных финансов и таблицей счетов со столбцом Счет.

Связь между таблицей финансовых данных и географической таблицей Locn с колонкой Profit Center.

Связь между таблицей финансовых данных и таблицей даты со столбцом Дата.

Скрытие столбцов из клиентских инструментов

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

В таблице «Финансовые данные» у вас есть 4 столбца — финансовый месяц, дата, счет и центр прибыли, которые вы не будете использовать в качестве полей в любой сводной таблице. Следовательно, вы можете скрыть их, чтобы они не отображались в списке полей сводной таблицы.

• Выберите столбцы — Финансовый месяц, Дата, Счет и Центр прибыли в таблице Финансовые данные.

• Щелкните правой кнопкой мыши и выберите «Скрыть от инструментов клиента» в раскрывающемся списке.

Выберите столбцы — Финансовый месяц, Дата, Счет и Центр прибыли в таблице Финансовые данные.

Щелкните правой кнопкой мыши и выберите «Скрыть от инструментов клиента» в раскрывающемся списке.

Создание показателей в таблицах

Вы полностью готовы к моделированию и анализу данных с помощью DAX, используя модель данных и сводные таблицы Power.

В следующих главах вы узнаете, как создавать показатели и как их использовать в сводных таблицах Power. Вы создадите все показатели в таблице данных, то есть в таблице данных финансов.

Вы создадите меры с использованием формул DAX в таблице данных — Финансовые данные, которую можно использовать в любом количестве сводных таблиц для анализа данных. Меры по сути являются метаданными. Создание показателей в таблице данных является частью моделирования данных, а их суммирование в сводных таблицах Power — частью анализа данных.