Случайны выбор дневника Раскрыть/свернуть полный список возможностей


Найдено 278 сообщений
Cообщения с меткой

кубы - Самое интересное в блогах

Следующие 30  »
tcansit881

Без заголовка

Понедельник, 13 Марта 2017 г. 21:58 (ссылка)

Кубы емкости для воды в Уфе - http://vkontakteguru.ru/post-599__Kuby-emkosti-dlya-vody-v-Ufe.html

Метки:   Комментарии (0)КомментироватьВ цитатник или сообщество
rss_rss_hh_new

Дисциплина, Точность, Внимание к деталям

Понедельник, 06 Марта 2017 г. 11:07 (ссылка)

Введение:

В этой статье речь пойдет о работе с Microsoft Analysis Services и немного о хранилище на Microsoft SQL Server, с которым SSAS работает. Мне пришлось столкнуться с не совсем тривиальными вещами и порой приходилось “прыгать через голову” ради того, чтобы сделать то, что от меня хотят. Работать приходилось в перерывах между совещаниями. Порой новый функционал обсуждался дольше, чем разрабатывался. Часто на совещаниях, по несколько раз, приходилось рассказывать одно и тоже. Когда я сказал, что мне сложно совещаться дольше одного часа, на меня посмотрели с удивлением и непониманием. Во многом, благодаря такой обстановке и появились эти, не совсем тривиальные вещи, о которых я решил написать.



Среднее за период



Требовалось посчитать среднее значение за период. В MS Analysis Services есть для этого функция Average of Children, которая считает среднее по всем не пустым значениям за выбранный период.

image



Но у заказчика, после скрупулёзного изучения результатов, появились вопросы?

Он показал мне следующую выборку, и сказал что она не правильная:

image

Так как, по его мнению, должно быть так:

image

На мой вопрос: Почему?

Он ответил, что ему нужно не среднее значение за период, а сумму средних значений по каждому элементу за период, то есть

НЕ ТАК:

image

( 5 + 6 + 7 ) / 3 = 6

, А ВОТ ТАК:

image

( 2,5 + 3,5 + 3 ) = 9

Это желание заставило меня пройти все стадии принятия неизбежного:


  1. Отрицание (Это что угодно, но не среднее за период);

  2. Гнев (Кто его математике учил?);

  3. Торг (Давайте оставим так и спросим тех, кто будет этим пользоваться?);

  4. Депрессия (А говорили, что здесь все очень добрые и хорошие…);

  5. Принятие ( Можно и зайца курить научить. Надо так надо, сделаю, как попросили).



Решение было не совсем очевидным. В разделе Calculations я создал Calculated Member и c помощью функций iif, isleaf и sum написал выражение.

Первый вариант:

iif (
not isleaf([ELEM].[ELEM SK].currentmember),
sum(EXISTING [ELEM].[ELEM SK].currentmember.Children,[Measures].[FCT VAL]),
[Measures].[FCT VAL]
)


, где [ELEM] – это измерение, а [ELEM SK] это его ключ.

Результат оказался неожиданным:

image

Странные цифры в Итогах оказались суммами всех значений элементов в измерении, не зависимо от того, какие значения я выбирал в фильтре. То есть в итоге всегда была сумма всех элементов, а не только выбранных.



Проблема решилась не сразу. Первым решением был вариант с дополнительным скрытым измерением. Я создал копию измерения [ELEM], изменил свойство Visible на False и написал так:

 iif (
not isleaf([ELEM].[ELEM SK].currentmember),
sum(EXISTING [ELEM COPY].[ELEM SK].currentmember.Children,[Measures].[FCT VAL]),
[Measures].[FCT VAL]
)


Получилось так, как хотел заказчик.

image

Чуть позже я нашёл “более правильное” решение. Я создал Named Set

CREATE DYNAMIC SET CURRENTCUBE.[Controller Set]
AS {
EXISTING [ELEM].[ELEM SK].currentmember.Children
};


, a Calculated Member переписал на такой:

iif (
not isleaf([ELEM].[ELEM SK].currentmember),
sum([Controller Set],[Measures].[FCT VAL]),
[Measures].[FCT VAL]
)


Таким образом, копия измерения [ELEM COPY] стала не нужна.



Я представил это решение заказчику и занялся другими задачами. Спустя какое-то время мне пришло новое техническое задание, в котором фигурировало понятие “statistical average”. На мой вопрос – Что это? Он сказал, что пользователям нужна не сумма средних значений по каждому элементу за период, а среднее за период, то есть обычный Average of Children, но мое решение он попросил сохранить.

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

image

( 5 + 6) / 3 = 3,666667

Задачу я решил с помощью дополнительного факта, который просто посчитал количество строчек в таблице для Time Dimension (в данном решении, в этой таблице на каждый день всегда есть одна запись). Далее, добавил факты с агрегацией – сумма (AggregateFunction = Sum) и добавил Calculated Member, в котором разделил Сумму, на количество дней.

[Measures].[Sum DATA]/[Measures].[TIME Count]


Хранилище



Клиент, на которого мы работаем, оказался не так прост. Про него можно сказать – “Месье знает толк в извращениях”. После того как мы разобрались с Кубом, перед нами была поставлена более глобальная задача. Разработать хранилище (Data Vault), но не простое. Первое что нам сказали – это то, что теперь наша Библия – это «Building a Scalable Data Warehouse with Data Vault 2.0», написанная Daniel Linstedt’ом и в довесок настояли на внедрении туда “Bitemporal Database Table Design”. На основе построенного хранилища требовалось построить Куб с историчностью. У такого куба два временных измерения, одно показывает бизнес дату, другое – транзакционную дату. Если говорить о процессе разработки, то было больно, даже сейчас больно, но что-то у нас получилось.

Суть “Bitemporal Database Table Design” заключается в том, что у каждой записи есть 4 дополнительных поля:


  1. Business_date_ from

  2. Business_date_to

  3. Transaction_date_from

  4. Transaction_date_to



Первые 2 содержат интервал бизнес дат — с какой и по какую дату значение оставалось неизменным. Вторые 2 поля содержат интервал транзакционных дат. В этом интервале хранится период в течение которого значение никто не поправлял (как вариант задним числом).



Одной из проблем при решении это задачи были исходные данные, точнее вид, в котором мы их получали. Данные приходили в виде ежедневных срезов. То есть, было поле Date, в котором хранилась дата на которую брались данные, на следующий день в поле Date было новое значение и данные либо менялись в этот день, либо нет. Требовалось объединить данные в периоды. Т.е. если, например, значение не менялось 3 дня подряд, то вместо 3 строчек надо сохранить одну, в которой вместо колонки DATA было бы две колонки BEGIN и END.

Данные до преобразования:

image

Данные после преобразования:

image

Я решил эту задачу через функции LAG и LEAD. Суть решения в том, что если значение не меняется 3 дня подряд, то дата из текущей строки, минус дата из предыдущей будет равна 1:

image


  • Если данные идут подряд, то 12.01.2017 – 11.01.2017 = 1

  • Если между данными есть разрыв, то 10.01.2017 – 03.01.2017 = 7



Отсюда:


  1. 	SELECT * FROM (
    SELECT Volume
    ,[Date] dt
    ,DATEDIFF(day, LAG([Date], 1) OVER (PARTITION BY Volume ORDER BY [Date]), [Date]) difLag
    ,DATEDIFF(day, [Date], LEAD([Date], 1) OVER (PARTITION BY Volume ORDER BY [Date])) difLead
    FROM dbo.Test n
    ) m
    WHERE ( difLag > 1 OR difLag IS NULL )
    OR ( difLead > 1 OR difLead IS NULL )

  2. Далее надо как-то сгруппировать этот результат, объединить пары для тех периодов, которые длились более одного дня и оставить те, период у которых длился один день. Группировку я сделал, пронумеровав строки и объединив четные с нечетными.

    Итак, весь запрос:
    WITH se
    AS (
    SELECT * FROM (
    SELECT Volume
    ,[Date] dt
    ,DATEDIFF(day, LAG([Date], 1) OVER (PARTITION BY Volume ORDER BY [Date]), [Date]) difLag
    ,DATEDIFF(day, [Date], LEAD([Date], 1) OVER (PARTITION BY Volume ORDER BY [Date])) difLead
    FROM dbo.Test n
    ) m
    WHERE ( difLag > 1 OR difLag IS NULL )
    OR ( difLead > 1 OR difLead IS NULL )
    )
    ,p1
    AS --choose only one day periods
    (
    SELECT Volume
    ,CASE WHEN difLead IS NOT NULL AND ( difLag IS NULL OR difLag > 1 ) THEN dt END AS VT_BEG
    ,CASE WHEN difLag IS NOT NULL AND ( difLead IS NULL OR difLead > 1 ) THEN dt END AS VT_END
    ,row_number() OVER ( ORDER BY Volume ,dt ) AS rn
    FROM se WHERE difLag > 1 AND difLead > 1
    )
    ,p2
    AS (
    SELECT Volume
    ,CASE WHEN difLead IS NOT NULL AND ( difLag IS NULL OR difLag > 1 ) THEN dt END AS VT_BEG
    ,CASE WHEN difLag IS NOT NULL AND ( difLead IS NULL OR difLead > 1 ) THEN dt END AS VT_END
    ,row_number() OVER ( ORDER BY Volume ,dt ) AS rn
    FROM se WHERE NOT ( difLag > 1 AND difLead > 1 )
    )
    SELECT * FROM (
    SELECT min(Volume) AS data
    ,min(VT_BEG) AS VT_BEG
    ,min(VT_END) AS VT_END
    FROM p2
    GROUP BY (CASE WHEN rn % 2 = 0 THEN rn ELSE rn + 1 END)
    UNION ALL
    SELECT Volume,VT_BEG,VT_END FROM p1
    ) g
    ORDER BY VT_BEG ,data



Итог:



В кабинете клиента, на которого я работаю, висит плакат с лозунгом. Этим лозунгом я решил назвать эту статью, так как, на мой взгляд, он, отчасти, объясняет причину тех трудностей, с которыми мне приходится сталкиваться. Проект ещё не закончен и я думаю, что всё самое интересное ещё впереди. С совещаниями я уже смирился и когда меня о чем-то спрашивают на них, порой на ум приходит фраза из КВН: “Молодец, задавай умные вопросы, получай глупые ответы…”, которая помогает мне собраться и попытаться ответить что-то вразумительное.



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

Original source: habrahabr.ru (comments, light).

https://habrahabr.ru/post/323276/

Комментарии (0)КомментироватьВ цитатник или сообщество

Следующие 30  »

<кубы - Самое интересное в блогах

Страницы: [1] 2 3 ..
.. 10

LiveInternet.Ru Ссылки: на главную|почта|знакомства|одноклассники|фото|открытки|тесты|чат
О проекте: помощь|контакты|разместить рекламу|версия для pda