IDVT,
1. Не совсем, дело в том, что в модели мы используем сразу два календаря, Грегорианский и Финансовый (недельный), и как оказалось PARALLELPERIOD не верно работает с Finance Calendar (недельный календарь), по этой причине мы расчитываем start day и last day для периода в зависимости от того, что выбрано в фильтре (год, квартал, месяц, неделя, день) и делаем CALCULATE с фильтрацией по этому периоду.
2. Нет, на текущий момент это не возможно, дело в том , что все эти таблицы сгрупированы по своему определеному полю (Товару, Компании, категории и атк далее) и это сделано для каждого из двух календарей (Грегорианского и Финансового) и далее на каждой из этих таблиц делается вычисление вида DISTINCT COUNT. И затем уже, в зависимости от того какой уровень групировки используется в очтете данные вытягиваются из нужной таблички.
3. У нас есть только несколько калькулируемых колонок в табличках (буквально две или три и все) В Tabular model не существует CREATE MEMBER или CREATE CELL калькуляций
4. Листинг мер:
Как я уже сказал выше, заказчик в своих отчетах использует 2 календаря. Потому мы делаем калькуляции все в двух видах (для обоих календарей). Кроме того, в прошлой модели у заказчика было несколько CREATE MEMBER созданных внутри куба, а не на стороне DWH.
A. Period Dimension:
"Current", - в DWH только этот
"Last Year",
"Diff",
"vs LY %",
"MTD",
"MTD LY",
"MTD Diff",
"MTD vs LY %",
"QTD",
"QTD LY",
"QTD Diff",
"QTD vs LY %",
"YTD",
"YTD LY",
"YTD Diff",
"YTD vs LY %"
Male,
Female,
Male+Female - Калькулируемый в кубе
Ну вот лень им каждый раз тыкать MALE и FEMALE в списках, хотят сразу галочку MALE+FEMALE и это для них принципиально, потому что так было раньше и они не хотят по другому сейчас.
Получается, что мы сделали этих MEMBERS не на стороне куба, а на стороне DWH мемберами в Dimensions и в DAX мерах в самом кубе вынуждены просчитывать сразу все варианты для всех срезов , но выводить данные только те, что выбраны в фильтрах заказчика. Т.е мы в мере считаем и "Current" и "Last Year" и "Male" и "Female" и "MALE+FEMALE" , но если в репорте в фильтре был выбран вариант Period Dimension только "Current" и только "MALE+FEMALE", то выводим только эти значения. Есть подозрение, что все меры в любом случае высчитываются и только потом фильтруется, но как я сказал мы постарались их разделить на разные метрики, чтобы это предотвратить.
Изначально была одна большая мера со всеми Пириодами для Грегорианского календаря и одна для Финансового календаря. Потом разделили их на несколько маленьких сгрупированных по Периодам (Год, квартал, месяц и так далее), которые вызываются одной главной мерой.
Проблема с Периодами еще была и в том, что часть мемберов должны возвращаться в другом формате (в процентах), а часть в определнных шаблоназ (типа 4 знака после запятой или 2 знака после запятой). Формат выставляем в свойствах меры: "FORMAT STRING" - тогда само Value меры возвращается как число в Excel (это очень важно для заказчика). А если мы используем DAX функцию FORMAT - то значение возвразается как текст, потому что эта функция сама по себе конвертит все в текст. В итоге процентные значения у нас возвращаются как текст. Получается, что одна мера должна возвращать значения в двух разных форматах. В мультидименшен модели это делалось тем же свойством но для отдельного CREATE MEMBER можно было вставить свой FORMAT_STRING.
дополнительнонадо проверять и еще один вариант, если в фильтре был вывран мембер "MALE+FEMALE" - т.е внутри куба мы внутри каждой метрики делаем подсчет вида: MALE+FEMALE = (взять value MALE) + (взять value FEMALE) и возвращаем если это было выбрано в фильтре.
Пример мер ниже:
SALARY_MAIN
SALARY:= IF(
ISCROSSFILTERED('Period Dimension') && ISCROSSFILTERED('Finance Calendar'),
SWITCH (SELECTEDVALUE('Period Dimension'[Period Dimension_],"Current"),
"Current", [SALARY_FC_LY],
"Last Year", IF(HASONEVALUE('Finance Calendar'[YearNumber]), [SALARY_FC_LY], BLANK()),
"Diff", IF(HASONEVALUE('Finance Calendar'[YearNumber]), [SALARY_FC_LY], BLANK()),
"vs LY %", IF(HASONEVALUE('Finance Calendar'[YearNumber]), IF(ISBLANK([SALARY_FC_CR_vs_LY_%]), BLANK(),FORMAT([SALARY_FC_CR_vs_LY_%], "#,#0.0%;-#,#0.0%")), BLANK()),
"MTD", IF(HASONEVALUE('Finance Calendar'[Period DimensionNumber]), [SALARY_FC_MTD],BLANK()),
"MTD LY", IF(HASONEVALUE('Finance Calendar'[Period DimensionNumber]), [SALARY_FC_MTD] ,BLANK()),
"MTD Diff", IF(HASONEVALUE('Finance Calendar'[Period DimensionNumber]), [SALARY_FC_MTD],BLANK()),
"MTD vs LY %", IF(HASONEVALUE('Finance Calendar'[Period DimensionNumber]), IF(ISBLANK([SALARY_FC_MTD_vs_LY_%]), BLANK(),FORMAT([SALARY_FC_MTD_vs_LY_%], "#,#0.0%;-#,#0.0%")),BLANK()),
"QTD", IF(HASONEVALUE('Finance Calendar'[QuarterNumber]), [SALARY_FC_QTD], BLANK()),
"QTD LY", IF(HASONEVALUE('Finance Calendar'[QuarterNumber]), [SALARY_FC_QTD] ,BLANK()),
"QTD Diff", IF(HASONEVALUE('Finance Calendar'[QuarterNumber]), [SALARY_FC_QTD], BLANK()),
"QTD vs LY %", IF(HASONEVALUE('Finance Calendar'[QuarterNumber]), IF(ISBLANK([SALARY_FC_QTD_vs_LY_%]), BLANK(),FORMAT([SALARY_FC_QTD_vs_LY_%], "#,#0.0%;-#,#0.0%")),BLANK()),
"YTD", IF(HASONEVALUE('Finance Calendar'[YearNumber]), [SALARY_FC_YTD], BLANK()),
"YTD LY", IF(HASONEVALUE('Finance Calendar'[YearNumber]), [SALARY_FC_YTD], BLANK()),
"YTD Diff", IF(HASONEVALUE('Finance Calendar'[YearNumber]), [SALARY_FC_YTD], BLANK()),
"YTD vs LY %", IF(HASONEVALUE('Finance Calendar'[YearNumber]), IF(ISBLANK([SALARY_FC_YTD_vs_LY_%]), BLANK(),FORMAT([SALARY_FC_YTD_vs_LY_%], "#,#0.0%;-#,#0.0%")),BLANK())
),
IF(ISCROSSFILTERED('Period Dimension') && ISCROSSFILTERED('Gregorian Calendar'),
SWITCH (SELECTEDVALUE('Period Dimension'[Period Dimension_],"Current"),
"Current", [SALARY_GC_LY],
"Last Year", IF(HASONEVALUE('Gregorian Calendar'[YearNumber]), ...
"Diff", IF(HASONEVALUE('Gregorian Calendar'[YearNumber]), ...
"vs LY %", IF(HASONEVALUE('Gregorian Calendar'[YearNumber]), ...
"MTD", IF(HASONEVALUE('Gregorian Calendar'[Period DimensionNumber]), ...
"MTD LY", IF(HASONEVALUE('Gregorian Calendar'[Period DimensionNumber]), ...
"MTD Diff", IF(HASONEVALUE('Gregorian Calendar'[Period DimensionNumber]), ...
"MTD vs LY %", IF(HASONEVALUE('Gregorian Calendar'[Period DimensionNumber]), ...
"QTD", IF(HASONEVALUE('Gregorian Calendar'[QuarterNumber]), ...
"QTD LY", IF(HASONEVALUE('Gregorian Calendar'[QuarterNumber]), ...
"QTD Diff", IF(HASONEVALUE('Gregorian Calendar'[QuarterNumber]), ...
"QTD vs LY %", IF(HASONEVALUE('Gregorian Calendar'[QuarterNumber]), ...
"YTD", IF(HASONEVALUE('Gregorian Calendar'[YearNumber]), ...
"YTD LY", IF(HASONEVALUE('Gregorian Calendar'[YearNumber]), ...
"YTD Diff", IF(HASONEVALUE('Gregorian Calendar'[YearNumber]), ...
"YTD vs LY %", IF(HASONEVALUE('Gregorian Calendar'[YearNumber]), ...
),
[SALARY_CR]
)
)
SALARY_FC_LY:=
/* --Current Year selected values: */
VAR CY = YEAR(NOW())
VAR SY = SELECTEDVALUE('Finance Calendar'[YearDescription],YEAR(NOW()))
VAR SQ = SELECTEDVALUE('Finance Calendar'[QuarterDescription])
VAR SM = SELECTEDVALUE('Finance Calendar'[PeriodDescription])
VAR SW = SELECTEDVALUE('Finance Calendar'[WeekDescription])
/* --Previous Year selected values: */
VAR PeriodLastYear = REPLACE(SELECTEDVALUE('Finance Calendar'[PeriodDescription]),3,2,
RIGHT(SELECTEDVALUE('Finance Calendar'[YearNumber]) - 1,2)
)
VAR QuarterLastYear = REPLACE(SELECTEDVALUE('Finance Calendar'[QuarterDescription]),3,2,
RIGHT(SELECTEDVALUE('Finance Calendar'[YearNumber]) - 1,2)
)
VAR YearLastYear = REPLACE(SELECTEDVALUE('Finance Calendar'[YearDescription]),3,2,
RIGHT(SELECTEDVALUE('Finance Calendar'[YearNumber]) - 1,2)
)
VAR WeekLastYear = REPLACE(SELECTEDVALUE('Finance Calendar'[WeekDescription]),3,2,
RIGHT(SELECTEDVALUE('Finance Calendar'[YearNumber]) - 1,2)
)
VAR DayLastYear = REPLACE(SELECTEDVALUE('Finance Calendar'[DayDescription]),3,2,
RIGHT(SELECTEDVALUE('Finance Calendar'[YearNumber]) - 1,2)
)
/* Choose MAX Date depend on Time interval (Week/MonthQuarter/Year) */
VAR Max_Date_Interval = SWITCH(TRUE(),
ISINSCOPE('Finance Calendar'[DayDescription]), CALCULATE
(
MAX('Finance Calendar'[DayDate]),
FILTER (
ALL('Finance Calendar'),
'Finance Calendar'[WeekDescription] = WeekLastYear
&& 'Finance Calendar'[DayNumber] = SELECTEDVALUE('Finance Calendar'[DayNumber])
)
)
,
ISINSCOPE('Finance Calendar'[WeekDescription]),CALCULATE
(
MAX('Finance Calendar'[DayDate]),
FILTER (
ALL('Finance Calendar'),
'Finance Calendar'[WeekDescription] = WeekLastYear
)
)
,
ISINSCOPE('Finance Calendar'[PeriodDescription]), CALCULATE
(
MAX('Finance Calendar'[DayDate]),
FILTER (
ALL('Finance Calendar'),
'Finance Calendar'[PeriodDescription] = PeriodLastYear
)
)
,
ISINSCOPE('Finance Calendar'[QuarterDescription]),CALCULATE
(
MAX('Finance Calendar'[DayDate]),
FILTER (
ALL('Finance Calendar'),
'Finance Calendar'[QuarterDescription] = QuarterLastYear
)
)
,
ISINSCOPE('Finance Calendar'[YearDescription]), CALCULATE
(
MAX('Finance Calendar'[DayDate]),
FILTER (
ALL('Finance Calendar'),
'Finance Calendar'[YearDescription] = YearLastYear
)
)
)
/* Max Date for Week*/
var Max_Date_Week =
CALCULATE
(
MAX('Finance Calendar'[DayDate]),
FILTER (
ALL('Finance Calendar'),
'Finance Calendar'[WeekDescription] = WeekLastYear
)
)
/*Max Date for LY calculation */
var Max_Date =
CALCULATE
(
MAX('Finance Calendar'[DayDescription])
)
VAR Max_DateLastYear = REPLACE(Max_Date,3,2,
RIGHT(SELECTEDVALUE('Finance Calendar'[YearNumber]) - 1,2)
)
/*Min Date for LY calculation */
var Min_Date =
CALCULATE
(
MIN('Finance Calendar'[DayDescription])
)
VAR Min_DateLastYear = REPLACE(Min_Date,3,2,
RIGHT(SELECTEDVALUE('Finance Calendar'[YearNumber]) - 1,2)
)
/* --Calculations: */
VAR SELECTED_PERIOD_CY = IF (SELECTEDVALUE('Class'[Class_Total], "Class_Total")= "MALE+FEMALE",
CALCULATE([SALARY] - [TAX],
ALL('Class'),'Class'[Description] in {"Male","Female"}),
CALCULATE([SALARY] - [TAX]
)
)
VAR SELECTED_PERIOD_LY = IF (SELECTEDVALUE('Class'[Class_Total], "Class_Total")= "MALE+FEMALE",
SWITCH(TRUE(),
ISINSCOPE('Finance Calendar'[DayDescription]), CALCULATE
(
[SALARY] - [TAX],
ALL('Class'),'Class'[Description] in {"Male","Female"},
FILTER (
ALL('Finance Calendar'),
'Finance Calendar'[DayDescription] = DayLastYear
)
)
,
ISINSCOPE('Finance Calendar'[WeekDescription]), CALCULATE
(
[SALARY] - [TAX],
ALL('Class'),'Class'[Description] in {"Male","Female"},
FILTER (
ALL('Finance Calendar'),
'Finance Calendar'[WeekDescription] = WeekLastYear
)
)
,
ISINSCOPE('Finance Calendar'[PeriodDescription]), CALCULATE
(
[SALARY] - [TAX],
ALL('Class'),'Class'[Description] in {"Male","Female"},
FILTER (
ALL('Finance Calendar'),
'Finance Calendar'[PeriodDescription] = PeriodLastYear
)
)
,
ISINSCOPE('Finance Calendar'[QuarterDescription]), CALCULATE
(
[SALARY] - [TAX],
ALL('Class'),'Class'[Description] in {"Male","Female"},
FILTER (
ALL('Finance Calendar'),
'Finance Calendar'[QuarterDescription] = QuarterLastYear
)
)
,
ISINSCOPE('Finance Calendar'[YearDescription]), CALCULATE
(
[SALARY] - [TAX],
ALL('Class'),'Class'[Description] in {"Male","Female"},
FILTER (
ALL('Finance Calendar'),
'Finance Calendar'[YearDescription] = YearLastYear
)
)
,
0
)
,
SWITCH(TRUE(),
ISINSCOPE('Finance Calendar'[DayDescription]), CALCULATE
(
[SALARY] - [TAX],
FILTER (
ALL('Finance Calendar'),
'Finance Calendar'[DayDate] = Max_Date_Interval
)
)
,
ISINSCOPE('Finance Calendar'[WeekDescription]), CALCULATE
(
[SALARY] - [TAX],
FILTER (
ALL('Finance Calendar'),
'Finance Calendar'[WeekDescription] = WeekLastYear
)
)
,
ISINSCOPE('Finance Calendar'[PeriodDescription]), CALCULATE
(
[SALARY] - [TAX],
FILTER (
ALL('Finance Calendar'),
'Finance Calendar'[PeriodDescription] = PeriodLastYear
)
)
,
ISINSCOPE('Finance Calendar'[QuarterDescription]), CALCULATE
(
[SALARY] - [TAX],
FILTER (
ALL('Finance Calendar'),
'Finance Calendar'[QuarterDescription] = QuarterLastYear
)
)
,
ISINSCOPE('Finance Calendar'[YearDescription]), CALCULATE
(
[SALARY] - [TAX],
FILTER (
ALL('Finance Calendar'),
'Finance Calendar'[YearDescription] = YearLastYear
)
)
,
0
)
)
RETURN
IF(SELECTEDVALUE('Period Dimension'[Period Dimension_],"Current") = "Current", SELECTED_PERIOD_CY,
IF(SELECTEDVALUE('Period Dimension'[Period Dimension_],"Current") = "Last Year", SELECTED_PERIOD_LY,
IF(SELECTEDVALUE('Period Dimension'[Period Dimension_],"Current") = "Diff", IF(SELECTED_PERIOD_LY = 0, BLANK(),SELECTED_PERIOD_CY - SELECTED_PERIOD_LY)
)
)
)
SALARY_FC_MTD:=
/* --Current Year selected values: */
VAR CY = YEAR(NOW())
VAR SY = SELECTEDVALUE('Finance Calendar'[YearDescription],YEAR(NOW()))
VAR SQ = SELECTEDVALUE('Finance Calendar'[QuarterDescription])
VAR SM = SELECTEDVALUE('Finance Calendar'[PeriodDescription])
VAR SW = SELECTEDVALUE('Finance Calendar'[WeekDescription])
/* --Previous Year selected values: */
VAR PeriodLastYear = REPLACE(SELECTEDVALUE('Finance Calendar'[PeriodDescription]),3,2,
RIGHT(SELECTEDVALUE('Finance Calendar'[YearNumber]) - 1,2)
)
VAR QuarterLastYear = REPLACE(SELECTEDVALUE('Finance Calendar'[QuarterDescription]),3,2,
RIGHT(SELECTEDVALUE('Finance Calendar'[YearNumber]) - 1,2)
)
VAR YearLastYear = REPLACE(SELECTEDVALUE('Finance Calendar'[YearDescription]),3,2,
RIGHT(SELECTEDVALUE('Finance Calendar'[YearNumber]) - 1,2)
)
VAR WeekLastYear = REPLACE(SELECTEDVALUE('Finance Calendar'[WeekDescription]),3,2,
RIGHT(SELECTEDVALUE('Finance Calendar'[YearNumber]) - 1,2)
)
VAR DayLastYear = REPLACE(SELECTEDVALUE('Finance Calendar'[DayDescription]),3,2,
RIGHT(SELECTEDVALUE('Finance Calendar'[YearNumber]) - 1,2)
)
/* Choose MAX Date depend on Time interval (Week/MonthQuarter/Year) */
VAR Max_Date_Interval = SWITCH(TRUE(),
ISFILTERED('Finance Calendar'[DayDescription]), CALCULATE
(
MAX('Finance Calendar'[DayDate]),
FILTER (
ALL('Finance Calendar'),
'Finance Calendar'[WeekDescription] = WeekLastYear
&& 'Finance Calendar'[DayNumber] = SELECTEDVALUE('Finance Calendar'[DayNumber])
)
)
,
ISFILTERED('Finance Calendar'[WeekDescription]),CALCULATE
(
MAX('Finance Calendar'[DayDate]),
FILTER (
ALL('Finance Calendar'),
'Finance Calendar'[WeekDescription] = WeekLastYear
)
)
,
ISFILTERED('Finance Calendar'[PeriodDescription]), CALCULATE
(
MAX('Finance Calendar'[DayDate]),
FILTER (
ALL('Finance Calendar'),
'Finance Calendar'[PeriodDescription] = PeriodLastYear
)
)
,
ISFILTERED('Finance Calendar'[QuarterDescription]),CALCULATE
(
MAX('Finance Calendar'[DayDate]),
FILTER (
ALL('Finance Calendar'),
'Finance Calendar'[QuarterDescription] = QuarterLastYear
)
)
,
ISFILTERED('Finance Calendar'[YearDescription]), CALCULATE
(
MAX('Finance Calendar'[DayDate]),
FILTER (
ALL('Finance Calendar'),
'Finance Calendar'[YearDescription] = YearLastYear
)
)
)
/* Max Date for Week*/
var Max_Date_Week =
CALCULATE
(
MAX('Finance Calendar'[DayDate]),
FILTER (
ALL('Finance Calendar'),
'Finance Calendar'[WeekDescription] = WeekLastYear
)
)
/*Max Date for LY calculation */
var Max_Date =
CALCULATE
(
MAX('Finance Calendar'[DayDescription])
)
VAR Max_DateLastYear = REPLACE(Max_Date,3,2,
RIGHT(SELECTEDVALUE('Finance Calendar'[YearNumber]) - 1,2)
)
/*Min Date for LY calculation */
var Min_Date =
CALCULATE
(
MIN('Finance Calendar'[DayDescription])
)
VAR Min_DateLastYear = REPLACE(Min_Date,3,2,
RIGHT(SELECTEDVALUE('Finance Calendar'[YearNumber]) - 1,2)
)
/* --Calculations: */
VAR MTD_CY = IF (SELECTEDVALUE('Class'[Class_Total], "Class_Total")= "MALE+FEMALE",
CALCULATE
(
[SALARY] - [TAX],
ALL('Class'),'Class'[Description] in {"Male","Female"},
FILTER (
ALL('Finance Calendar'),
'Finance Calendar'[PeriodDescription] = SM
&& 'Finance Calendar'[DayDescription] <= Max_Date
)
)
,
CALCULATE
(
[SALARY] - [TAX],
FILTER (
ALL('Finance Calendar'),
'Finance Calendar'[PeriodDescription] = SM
&& 'Finance Calendar'[DayDescription] <= Max_Date
)
)
)
VAR MTD_LY = IF (SELECTEDVALUE('Class'[Class_Total], "Class_Total")= "MALE+FEMALE",
CALCULATE
(
[SALARY] - [TAX],
ALL('Class'),'Class'[Description] in {"Male","Female"},
FILTER (
ALL('Finance Calendar'),
'Finance Calendar'[PeriodDescription] = PeriodLastYear
&& 'Finance Calendar'[DayDate] <= Max_Date_Interval
)
)
,
CALCULATE
(
[SALARY] - [TAX],
FILTER (
ALL('Finance Calendar'),
'Finance Calendar'[PeriodDescription] = PeriodLastYear
&& 'Finance Calendar'[DayDate] <= Max_Date_Interval
)
)
)
RETURN
IF(SELECTEDVALUE('Period Dimension'[Period Dimension_],"Current") = "MTD", MTD_CY,
IF(SELECTEDVALUE('Period Dimension'[Period Dimension_],"Current") = "MTD LY", MTD_LY,
IF(SELECTEDVALUE('Period Dimension'[Period Dimension_],"Current") ="MTD Diff", IF(MTD_LY = 0, BLANK(), MTD_CY - MTD_LY)
)
)
)
- этот вариант, кстати, работает очень быстро и практически не потребляет памяти
SALARY_CR:=
IF(
ISBLANK(IF (SELECTEDVALUE('Class'[Class_Total], "Class_Total")= "MALE+FEMALE",
CALCULATE( [SALARY] - [TAX],
ALL('Class'),'Class'[Description] in {"Male","Female"}
),
CALCULATE( [SALARY] - [TAX] )
)
),
BLANK(),
IF (SELECTEDVALUE('Class'[Class_Total], "Class_Total")= "MALE+FEMALE",
CALCULATE( [SALARY] - [TAX],
ALL('Class'),'Class'[Description] in {"Male","Female"}
),
CALCULATE( [SALARY] - [TAX] )
)
)
Мое подозрение, что в процессе вычислений в наших мерах отрабатывается/вычисляется кучу всего лишнего и потом это фильтруется и отображается только то, что было выбрано в параметрах. Т.е перевести Мультидименшен модель на Табулярную со всеми запросами заказчика (виртуальными мемберами) нам удалось в плане логики (данные схоядтся между старой и новой моделью), но тперь заказчика стал беспокоить Performance, Вернее то, что если выбрать много значений и Period Dimension, выбрать разные группы товаров и категорий, выбрать разных поставщиков - такой репорт отрабатывает очень долго у нас и часто съедат по 50 - 100 Gb памяти и это при условии, что сам куб 30 Gb размером.
Буд очень благодарен за любые советы по делу. |
