Прошу помощи в OLAP

[Cardagant]

Всем доброго времени суток!

Хочу попросить помощи у знатоков OLAP, в частности SSAS.
DAX 2012 R2

Имею следующие исходные условия:
Есть таблица, имеющая поля:
1. Поле основанное на EDT, который построен как массив из 7 элементов, определяющих каждый из дней недели и заполняющийся некоторым числовым значением (то есть определяет некое количество единиц в определённый день недели). Назову это поле Результаты (Results).
2. Есть два поля дат
FromDate - дата начала недели
ToDate - дата окончания недели.
3. Worker - поле, которое ссылается на таблицу сотрудников и будет обычной аналитикой.

Задача в следующем:
Создать такое измерение Даты, которое даст возможность анализировать числовые значения поля Результаты (Measure, Sum) как обычное измерение даты (в разрезе лет, кварталов, месяцев, недель и дней ). то есть, по сути, произвести разбивку массива из семи элементов для каждой даты недели.
Но как это правильно сделать и, что ещё важнее, возможно ли это?

Буду благодарен за любые советы!

Спасибо!

[Alex_K]

Структура в "сыром" виде для OLAP малоподходящая.
В любом случае я бы сначала привёл её к удобоваримому виду с помощью, например, периодического задания, которое будет переливать данные в буферную таблицу.
Отсюда вопросы:
1. FromDate и ToDate - это всегда понедельник и воскресенье?
2. Result - аддитивная мера для всех измерений?

Если в обоих случаях "да" - то всегда можно вычислить дату, зная индекс элемента в массиве.

Тогда просто грузим данные в таблицу вида:
Дата
Сотрудник
Result

и всё это скармливаем SSAS, где будет использоваться совершенно стандартное измерение дат.

[Cardagant]

Цитата:

Сообщение от Alex_K

Структура в "сыром" виде для OLAP малоподходящая.
В любом случае я бы сначала привёл её к удобоваримому виду с помощью, например, периодического задания, которое будет переливать данные в буферную таблицу.
Отсюда вопросы:
1. FromDate и ToDate - это всегда понедельник и воскресенье?
2. Result - аддитивная мера для всех измерений?

Если в обоих случаях "да" - то всегда можно вычислить дату, зная индекс элемента в массиве.

Тогда просто грузим данные в таблицу вида:
Дата
Сотрудник
Result

и всё это скармливаем SSAS, где будет использоваться совершенно стандартное измерение дат.

Спасибо за Ваш ответ!

1. Да.
2. Это никак не связанные между собой величины результативности данного дня. Но все считаются как суммы.

То есть, насколько я понимаю,,никакие стандартные преобразования, к примеру вычисляемые поля или др., здесь применить не удастся. Понял, спасибо!

[Alex_K]

Цитата:

Сообщение от Cardagant

То есть, насколько я понимаю,,никакие стандартные преобразования, к примеру вычисляемые поля или др., здесь применить не удастся. Понял, спасибо!

Я, честно говоря, давненько в Аксапту не заглядывал и не помню, как в базе хранятся значения массивов, но теоретически всё, что я сказал, можно сделать SQL-запросом на уровне DataSourceView в SSAS. Тогда выгрузка не понадобится.

[Cardagant]

Цитата:

Сообщение от Alex_K

Я, честно говоря, давненько в Аксапту не заглядывал и не помню, как в базе хранятся значения массивов, но теоретически всё, что я сказал, можно сделать SQL-запросом на уровне DataSourceView в SSAS. Тогда выгрузка не понадобится.

То есть на уровне SSAS создаётся новый View и запросом туда выбираются и преобразовываются значения? Все релейшены, соответственно также прописываются вручную?

Upd: Не нахожу я что-то инструментов у ДатасорсВью для реализации подобного.

[Alex_K]

Правый клик по диаграмме DatasourceView:


Обычный конструктор запросов.

Кстати, а редакция SQL какая?

[twilight]

Можно и в Аксапте создать View c вычислимыми полями.
Например, следующим образом:
1. Сделать таблицу / представление из 7 значений.
2. Сделать view в котором будет исходная таблица заджойнена на эту таблицу с 7 значениями (сначала создается query, потом на его основе view).
3. Во View сделать вычислимые поля, которые на основе значения поля из таблицы из 7 элементов будут определять результат и дату.

[Cardagant]

Цитата:

Сообщение от twilight

Можно и в Аксапте создать View c вычислимыми полями.
Например, следующим образом:
1. Сделать таблицу / представление из 7 значений.
2. Сделать view в котором будет исходная таблица заджойнена на эту таблицу с 7 значениями (сначала создается query, потом на его основе view).
3. Во View сделать вычислимые поля, которые на основе значения поля из таблицы из 7 элементов будут определять результат и дату.

Не совсем понимаю что имеется ввиду в первом пункте. И по чему джойнить к основной таблице?

[Cardagant]

Цитата:

Сообщение от Alex_K

Правый клик по диаграмме DatasourceView:
Вложение 8450

Обычный конструктор запросов.

Кстати, а редакция SQL какая?

А как вообще может выглядеть подобный запрос? Следует использовать временную таблицу?

[twilight]

Цитата:

Сообщение от Cardagant

Не совсем понимаю что имеется ввиду в первом пункте. И по чему джойнить к основной таблице?

Например, создать таблицу DayOfWeekTable с одним полем DayOfWeek, целое.
В таблице создать 7 записей со значениями 0, 1, 2, 3, 4, 5, 6.
В query для нового view в датасорсе по DayOfWeekTable relation не задавать, тогда получится cross join.
Т. е. на каждую запись исходной таблицы будет 7 записей в новой view с различными значения DayOfWeek.
Далее создаем вычислимые поля, в котором используем SysComputedColumn::switch() и прочие функции для анализа DayOfWeek. Для даты прибавляем к начальной дате DayOfWeek, для результата берем соответвующий DayOfWeek элемент из массива.

[Alex_K]

Цитата:

Сообщение от Cardagant

А как вообще может выглядеть подобный запрос? Следует использовать временную таблицу?

Под руками аксы нет, поэтому сразу и не ответить.

Всё-таки, каким образом хранятся в БД индекс и значение элемента массива? Пример можно?

[Cardagant]

Цитата:

Сообщение от Alex_K

Под руками аксы нет, поэтому сразу и не ответить.

Всё-таки, каким образом хранятся в БД индекс и значение элемента массива? Пример можно?

Аксапта хранит их как одно отдельное поле для каждого индекса.
К примеру, для Results это было бы
Result, Result2_, Result3_, ..., Result7_

[Cardagant]

Цитата:

Сообщение от twilight

Например, создать таблицу DayOfWeekTable с одним полем DayOfWeek, целое.
В таблице создать 7 записей со значениями 0, 1, 2, 3, 4, 5, 6.
В query для нового view в датасорсе по DayOfWeekTable relation не задавать, тогда получится cross join.
Т. е. на каждую запись исходной таблицы будет 7 записей в новой view с различными значения DayOfWeek.
Далее создаем вычислимые поля, в котором используем SysComputedColumn::switch() и прочие функции для анализа DayOfWeek. Для даты прибавляем к начальной дате DayOfWeek, для результата берем соответвующий DayOfWeek элемент из массива.

Благодарю за интересный вариант, думаю, это стоит попробовать!

[Cardagant]

Цитата:

Сообщение от twilight

Далее создаем вычислимые поля, в котором используем SysComputedColumn::switch() и прочие функции для анализа DayOfWeek. Для даты прибавляем к начальной дате DayOfWeek, для результата берем соответвующий DayOfWeek элемент из массива.

В 2012й программирую не так давно, прошу Вас привести пример подобного SysComputedColumn::switch() в контексте данной задачи!

Благодарю!

[twilight]

Например, вот анализируется поле EnumItemValue из датасорса MSKOLAP_FactTypeView представления MSKOLAP_FactSalesView и выбирается соответвующее ему значение из Map amountMap

public static server str getAmountFactType1()
{
    #macrolib.MSKOLAP

    Map                     amountMap = new Map(Types::String, Types::String);

    amountMap.insert(#cLiteral(MSKOlapFactType::Qty), #cField(MSKOLAP_FactSalesAllDiscView, CustInvoiceTrans, Qty));
    amountMap.insert(#cLiteral(MSKOlapFactType::Retail), MSKOLAP_FactSalesAllDiscView::amount());
    amountMap.insert(#cLiteral(MSKOlapFactType::Sale), MSKOLAP_FactSalesAllDiscView::amountSales());
    amountMap.insert(#cLiteral(MSKOlapFactType::Purch), MSKOLAP_FactSalesAllDiscView::amountPurch());

    return #cSwitch(#cComparisonField(MSKOLAP_FactSalesView, MSKOLAP_FactTypeView, EnumItemValue),
        amountMap,
        #cLiteral(0));
}

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

    #localmacro.cField
    SysComputedColumn::returnField(tableStr(%1), identifierStr(%2), fieldStr(%2, %3))
    #endmacro

    #localmacro.cFieldL
    SysComputedColumn::returnField(tableStr(%1), identifierStr(%2), fieldStr(%3, %4))
    #endmacro

    #localmacro.cAdd
    SysComputedColumn::add(%1, %2)
    #endmacro

    #localmacro.cMultiply
    SysComputedColumn::multiply(%1, %2)
    #endmacro

    #localmacro.cSubtract
    SysComputedColumn::subtract(%1, %2)
    #endmacro

    #localmacro.cLiteral
    SysComputedColumn::returnLiteral(%1)
    #endmacro

    #localmacro.cSwitch
    SysComputedColumn::switch(%1, %2, %3)
    #endmacro

    #localmacro.cComparisonField
    SysComputedColumn::comparisonField(tableStr(%1), identifierStr(%2), fieldStr(%2, %3))
    #endmacro

    #localmacro.cComparisonFieldL
    SysComputedColumn::comparisonField(tableStr(%1), identifierStr(%2), fieldStr(%3, %4))
    #endmacro

    #localmacro.cIf
    SysComputedColumn::if(%1, %2, %3)
    #endmacro

    #localmacro.cEqual
    SysComputedColumn::equalExpression(%1, %2)
    #endmacro

    #localmacro.cCast
    SysComputedColumn::cast(%1, %2)
    #endmacro

    #localmacro.isNullExpr
    SysComputedColumn::isNullExpression(%1)
    #endmacro

[Cardagant]

Благодарю!

А как в методе SysComputedColumn::returnField() правильно указать индекс поля массива в третьем параметре?

Upd: Можно прописать просто строкой. Но может есть какие-то стандартные методы?

[Alex_K]

Ну тады и от себя добавлю 5 копеек
Небольшая наколенная модель с использованием оператора UNPIVOT:

--    
CREATE TABLE Perfomance (EmplID varchar(20), FromDate datetime, ToDate datetime, 
                         Result int, Result2_ int, Result3_ int, Result4_ int,Result5_ int,Result6_ int, Result7_ int);

GO
INSERT INTO Perfomance VALUES ('Иванов','2013-10-07','2013-10-07',1,2,3,4,5,6,7);
INSERT INTO Perfomance VALUES ('Иванов','2013-10-14','2013-10-20',4,5,6,7,8,9,10);
INSERT INTO Perfomance VALUES ('Петров','2013-10-07','2013-10-07',2,3,4,5,6,7,8);
INSERT INTO Perfomance VALUES ('Петров','2013-10-14','2013-10-20',9,8,6,7,8,9,10);

GO

--  Unpivot    
SELECT EmplID, (dateadd(DAY, CAST(DayIdx as int), FromDate) ) as TransDate, Results
FROM 
   (SELECT EmplID, FromDate, Result "0", Result2_ "1", Result3_ "2", Result4_ "3", Result5_ "4", Result6_ "5", Result7_ "6"
   FROM Perfomance) p
UNPIVOT
   (Results FOR DayIdx IN 
      ("0", "1", "2", "3", "4", "5", "6")
)AS unpvt;
GO

Создаем в DSV новый Named Query, вставляем в него SELECT и после некоторого негодования BIDS на нему "Unpivot statement not supported" получаем искомый результат.

[twilight]

Цитата:

Сообщение от Cardagant

Благодарю!

А как в методе SysComputedColumn::returnField() правильно указать индекс поля массива в третьем параметре?

Upd: Можно прописать просто строкой. Но может есть какие-то стандартные методы?

Точно не знаю, с полями-массивами в вычислимых полях ранее не сталкивался. А просто указать индекс в [] у названия поля не работает?

[Cardagant]

2Alex_K

Спасибо большое за Ваш интересный вариант! С удовольствием его попробую!

[Cardagant]

Цитата:

Сообщение от twilight

Точно не знаю, с полями-массивами в вычислимых полях ранее не сталкивался. А просто указать индекс в [] у названия поля не работает?

Работает, если указать подобным образом, к примеру для пятого индекса:

comparisonExpressionMap.insert(SysComputedColumn::returnLiteral(WeekDays::Monday),
        SysComputedColumn::returnField(viewName, identifierStr(myTable), fieldStr(myTable, Results) + "[5]"));