|
MDX вычисляемая мера для куба с акциями акционеров.
|
|||
---|---|---|---|
#18+
День добрый Постановка задачи - имеется data warehouse c таблицами и соответствующими объектами в кубах: Dimensions: 1) DimMember - MemberPK, MemberNumber 2) DimUnitPrice - DatePK, UnitTypePK, UnitPrice Facts 2) FactMemberBalance - DatePK, MemberFK, UnitTypeFK, MemberUnits Таблица FactMemberBalance заполнена по принципу - в ней есть записи только прошедших транзакций по изменению MemberUnits у мембера. То есть, если Мембер 1 получил 10 MemberUnits UnitType=1 01.01.2020, то запись будет выглядеть примерно так (изначально баланс Мембер 1 равен нулю по всем UnitType) DatePK, MemberFK, UnitTypeFK, MemberUnits 20200101, 1, 1, 10 Следующая возможная транзакция по этому мемберу может быть такой DatePK, MemberFK, UnitTypeFK, MemberUnits 20200103, 1, 2, 15 DimUnitPrice имеет следующие данные DatePK, UnitTypePK, UnitPrice 20200101, 1, 1.0 20200101, 2, 1.5 20200102, 1, 1.5 20200102, 2, 2.0 20200103, 1, 2.0 20200103, 2, 2.5 20200104, 1, 3.3 20200104, 2, 4.4 20200105, 1, 5 20200105, 2, 5 Как мне создать в кубе меру, которая будет считать баланс по мемберу на каждый день: Member, Day, Balance 1, 20200101, 10 --(10*1) 1, 20200102, 15 1, 20200103, 57.5 --(10*2+15*2.5) 1, 20200104, 99 --(10*3.3+15*4.4) 1, 20200105, 125 --(10*5+15*5) Дизайн как таблиц в DW, так и самих кубов может быть изменен. Единственное - не хотелось бы генерить все "промежуточные" балансы по MemberUnits на каждый день - это увеличивает таблицу до огромных размеров и приводит к совершенно дикому времени процессинга куба. Мне кажется, что это должна быть достаточно стандартная задача для области применения SSAS. Но мой скромный опыт работы с SSAS ставит меня в тупик - как это может быть реализовано. Заранее спасибо за помощь. ... |
|||
:
Нравится:
Не нравится:
|
|||
21.11.2020, 04:32 |
|
MDX вычисляемая мера для куба с акциями акционеров.
|
|||
---|---|---|---|
#18+
AlphaDog, баланс считается точно так же, как и остатки (см поиск по форуму) - через фиктивную sum-меру и scope ... |
|||
:
Нравится:
Не нравится:
|
|||
21.11.2020, 19:20 |
|
MDX вычисляемая мера для куба с акциями акционеров.
|
|||
---|---|---|---|
#18+
Критик, Спасибо. Вы этот топик имели ввиду? https://www.sql.ru/forum/524753-6/novyy-podhod-k-ostatkam-v-2005 ... |
|||
:
Нравится:
Не нравится:
|
|||
22.11.2020, 23:51 |
|
MDX вычисляемая мера для куба с акциями акционеров.
|
|||
---|---|---|---|
#18+
Да, оно. Но вы зря не хотите материализовать остатки в таблицу. Они часто бывает нужны для 1) сторонних приложений 2) для расчетов поверх остатков (а так у вас получается, что сначала должны считаться остатки, потом поверх них что-то еще) ... |
|||
:
Нравится:
Не нравится:
|
|||
23.11.2020, 00:24 |
|
MDX вычисляемая мера для куба с акциями акционеров.
|
|||
---|---|---|---|
#18+
Критик, Спасибо. Почитаю, попробую разобраться. Технически не "остатки", или я не понимаю вашей терминологии. Это количество акций акционеров по разным типам акций. Приложение для этого куба только одно - PowerBI. Отчетов пока готовых нет - не окончился этап разработки проекта. Пересчет всего в таблицу раздувает ее до астрономических размеров - со 170 миллионов записей до 4 миллиардов примерно, что делает процессинг куба крайне длительной операцией, да и работа с кубом потом очень медленная - читать с диска гигабайты данных это долго. Если честно, я был бы крайне признателен за помощь - проект близок к сдаче и я основательно плаваю в теме. ... |
|||
:
Нравится:
Не нравится:
|
|||
23.11.2020, 00:36 |
|
MDX вычисляемая мера для куба с акциями акционеров.
|
|||
---|---|---|---|
#18+
AlphaDog, у вас куб многомерный или табулярный? ... |
|||
:
Нравится:
Не нравится:
|
|||
23.11.2020, 12:24 |
|
MDX вычисляемая мера для куба с акциями акционеров.
|
|||
---|---|---|---|
#18+
Критик, Многомерный. Идею я понял, теперь пытаюсь средствам MDX получить желаемое :) Что-то типа Мера = ....Currentmember.LastNonEmptyChild и scope для LastNonEmptyChild -> будущее, который будет возвращать "Мера" Не могу понять только, как мне вставить scope в MDX запросе в SSMS. Все примеры что нашел - для calculate в дизайне куба. Очень неудобно отлаживать запросы... ... |
|||
:
Нравится:
Не нравится:
|
|||
23.11.2020, 12:37 |
|
MDX вычисляемая мера для куба с акциями акционеров.
|
|||
---|---|---|---|
#18+
AlphaDog, в calculate и вставляете, а запросах используете вашу вычисляемую меру ... |
|||
:
Нравится:
Не нравится:
|
|||
23.11.2020, 12:43 |
|
MDX вычисляемая мера для куба с акциями акционеров.
|
|||
---|---|---|---|
#18+
Критик, Я понимаю, но перепроцессить куб занимает некоторое время. Это значительно снижает скорость разработки. Хотя я думаю попробовать IIF() - это проще для понимания. ... |
|||
:
Нравится:
Не нравится:
|
|||
23.11.2020, 12:48 |
|
MDX вычисляемая мера для куба с акциями акционеров.
|
|||
---|---|---|---|
#18+
AlphaDog Не могу понять только, как мне вставить scope в MDX запросе в SSMS. Все примеры что нашел - для calculate в дизайне куба. Очень неудобно отлаживать запросы... Когда-то у Моши подсмотрел такой прием. Для эмуляции SCOPE используется CELL CALCULATION. Ниже псевдокод иллюстрирующий идею с эмулятором скоупа: Код: sql 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18.
AlphaDog ... перепроцессить куб занимает некоторое время. Это значительно снижает скорость разработки. Хм... если речь идет только о редактировании скрипта куба, существуют решения, которые позволяют деплоить скрипт куба без его перепроцешивания. Напр., ранее называвшееся решение BIDS helper. Сейчас его переименовали, но можно погуглить и понять, что нужно поискать. И еще. Точно не помню, но... Если подключиться visual studio напрямую к отдеплоенному на сервере кубу, внести правку только в скрипт куба и нажать на кнопку сохранить, разве будет требоваться репроцессинг куба (ну просто запамятовал этот момент)? ... |
|||
:
Нравится:
Не нравится:
|
|||
23.11.2020, 14:10 |
|
MDX вычисляемая мера для куба с акциями акционеров.
|
|||
---|---|---|---|
#18+
AlphaDog, изменения в calculate не требуют репроцессинга ... |
|||
:
Нравится:
Не нравится:
|
|||
23.11.2020, 14:38 |
|
MDX вычисляемая мера для куба с акциями акционеров.
|
|||
---|---|---|---|
#18+
В общем нужное накопление суммы акций по датам я получил. Почти все работает как планировалось. Есть только одна проблема - не знаю как прикрутить стоимость акций к полученным суммам. Должно быть что-то несложное, но не могу сообразить как это сделать. Обе таблицы линкуются по Date_FK и InvestmentOption_FK. Чувствую, что можно сделать через LINKMEMBER(), но мне кажется должно быть более изящное решение. ... |
|||
:
Нравится:
Не нравится:
|
|||
24.11.2020, 11:21 |
|
MDX вычисляемая мера для куба с акциями акционеров.
|
|||
---|---|---|---|
#18+
AlphaDog, Стоимость можно считать по тому же методу, что и баланс по Member Units. Т. е. надо сначала добавить стоимость акции на дату в таблицу/представление FactMemberBalance. ... |
|||
:
Нравится:
Не нравится:
|
|||
24.11.2020, 12:16 |
|
MDX вычисляемая мера для куба с акциями акционеров.
|
|||
---|---|---|---|
#18+
AlphaDog, тут смотря что вы понимаете под стоимостью... например, у вас же есть сумма и количество - можно просто разделить одно на другое ... |
|||
:
Нравится:
Не нравится:
|
|||
24.11.2020, 16:24 |
|
MDX вычисляемая мера для куба с акциями акционеров.
|
|||
---|---|---|---|
#18+
Критик, У меня есть сумма количества акций определенного типа. В другой таблице у меня есть котировки этих акций всех типов по дням. Нужен баланс в валюте на каждый день по каждому члену. То есть просто умножить количество акций на их стоимость в конкретный день ... |
|||
:
Нравится:
Не нравится:
|
|||
25.11.2020, 00:01 |
|
MDX вычисляемая мера для куба с акциями акционеров.
|
|||
---|---|---|---|
#18+
AlphaDog, что мешает в таблице фактов (или в представлении) умножить? ... |
|||
:
Нравится:
Не нравится:
|
|||
25.11.2020, 07:56 |
|
MDX вычисляемая мера для куба с акциями акционеров.
|
|||
---|---|---|---|
#18+
Критик AlphaDog, что мешает в таблице фактов (или в представлении) умножить? То, что факты (изменения количества акций у акционера) появляются достаточно редко. Скажем 2-3-10 раз в год. А цена на акцию меняется ежедневно и баланс в валюте нужно анализировать ежедневно. Можно создать таблицу фактов по дням, где баланс будет просчитан заранее, но это увеличивает таблицу фактом в DW с 200 миллионов записей до 4 миллиардов. Процессинг куба становится адом, и сама работа с ним - очень медленной. На диске чистый куб с минимум агрегаций берет 90гигабайт. ... |
|||
:
Нравится:
Не нравится:
|
|||
25.11.2020, 08:53 |
|
MDX вычисляемая мера для куба с акциями акционеров.
|
|||
---|---|---|---|
#18+
AlphaDog, Вот такая у меня примерно картинка. Вот там где колонка UnitPrice должно быть не (All), а значение курса для типа акций 18 на соответствующую дату. Колонка Units - это только изменения по количеству акций по данному акционеру. ... |
|||
:
Нравится:
Не нравится:
|
|||
25.11.2020, 08:59 |
|
MDX вычисляемая мера для куба с акциями акционеров.
|
|||
---|---|---|---|
#18+
... |
|||
:
Нравится:
Не нравится:
|
|||
25.11.2020, 09:14 |
|
MDX вычисляемая мера для куба с акциями акционеров.
|
|||
---|---|---|---|
#18+
Критик, Спасибо. В AdventureWork2014 что-то для примера есть с конвертацией валют? ... |
|||
:
Нравится:
Не нравится:
|
|||
25.11.2020, 09:17 |
|
MDX вычисляемая мера для куба с акциями акционеров.
|
|||
---|---|---|---|
#18+
Вижу уже - есть что-то. Спасибо за наводку ... |
|||
:
Нравится:
Не нравится:
|
|||
25.11.2020, 09:30 |
|
MDX вычисляемая мера для куба с акциями акционеров.
|
|||
---|---|---|---|
#18+
День добрый. После прочтения нескольких статей по конвертации валют, проблему "почти" решил. Решение было достаточно простым. То есть я получаю нужные цифры с суммами по всем разным валютам(типам акций) в соответствии с их текущим курсом, но ПО ДНЯМ и курсу акций/валюты на этот день. ... |
|||
:
Нравится:
Не нравится:
|
|||
03.12.2020, 07:36 |
|
MDX вычисляемая мера для куба с акциями акционеров.
|
|||
---|---|---|---|
#18+
Но есть одно "но" - ежедневно у меня только текущие транзакции, а не общая сумма акций. Поэтому мне нужна аддитивная мера типа: Код: sql 1. 2.
Которую нужно умножить курс/рейт для этого типа акций на день получения значение AmountAcc. Типа: Код: sql 1. 2.
Но мера Rate у меня приходит как NULL на любом уровне йерархий, и как результат общий баланс тоже получается NULL: ... |
|||
:
Нравится:
Не нравится:
|
|||
03.12.2020, 07:42 |
|
MDX вычисляемая мера для куба с акциями акционеров.
|
|||
---|---|---|---|
#18+
То есть я ищу решение, которое могло бы быть получено при этом дизайне меры. Но MeasureExpression property не может работать с вычисляемыми мерами. Как это можно сделать на MDX в Calculations куба? У меня ощущение, что должно быть что-то совсем простое, но я не могу сообразить что... Помогите пожалуйста - я уже голову себе сломал... ... |
|||
:
Нравится:
Не нравится:
|
|||
03.12.2020, 07:49 |
|
MDX вычисляемая мера для куба с акциями акционеров.
|
|||
---|---|---|---|
#18+
AlphaDog Но MeasureExpression property не может работать с вычисляемыми мерами Точно? Просто я с ними не особо работал. Попробуйте сделать AmountAcc фиктивной мерой, а ваше вычисляемое выражение написать в scope ... |
|||
:
Нравится:
Не нравится:
|
|||
03.12.2020, 07:51 |
|
MDX вычисляемая мера для куба с акциями акционеров.
|
|||
---|---|---|---|
#18+
Мера Rate у меня объявлена вот так: ... |
|||
:
Нравится:
Не нравится:
|
|||
03.12.2020, 07:52 |
|
MDX вычисляемая мера для куба с акциями акционеров.
|
|||
---|---|---|---|
#18+
AlphaDog, еще попробуйте create member currentcube.measures.AmountAcc AS SUM(null:[Dim Date].[Date].currentMember,[Measures].[Amount] * курс); ... |
|||
:
Нравится:
Не нравится:
|
|||
03.12.2020, 07:57 |
|
MDX вычисляемая мера для куба с акциями акционеров.
|
|||
---|---|---|---|
#18+
Критик AlphaDog Но MeasureExpression property не может работать с вычисляемыми мерами Точно? Просто я с ними не особо работал. Сообщение об ошибке: Код: sql 1. 2.
Критик Попробуйте сделать AmountAcc фиктивной мерой, а ваше вычисляемое выражение написать в scope Я не совсем понимаю, что такое "фиктивная мера", да и значение AmountAcc пользователям будет нужно для анализа и отчетов. А SCOPE я банально не могу понять как он работает. Нет, я понимаю что он вырубает в кубе подкуб и задает вычисления отличные от остальной части куба. Но моя трехмерная голова ломается многомерном пространстве. Вроде все просто, но я туплю 😢 ... |
|||
:
Нравится:
Не нравится:
|
|||
03.12.2020, 08:02 |
|
MDX вычисляемая мера для куба с акциями акционеров.
|
|||
---|---|---|---|
#18+
Критик AlphaDog, еще попробуйте create member currentcube.measures.AmountAcc AS SUM(null:[Dim Date].[Date].currentMember,[Measures].[Amount] * курс); Сделал: Код: sql 1. 2.
Возвращает NULL. Да и если бы считало, я так понимаю, [Measures].[Rate] был бы на конкретный [Dim Date].[Date].currentMember и на [Measures].[Amount] на этот день, а мне нужно на ТЕКУЩУЮ дату получения всей суммы. ... |
|||
:
Нравится:
Не нравится:
|
|||
03.12.2020, 08:07 |
|
MDX вычисляемая мера для куба с акциями акционеров.
|
|||
---|---|---|---|
#18+
Я так понимаю, что проблема в том, что [Measures].[Rate] возвращает NULL. Но для искомого значения, на мой взгляд, есть все нужные данные - связи по ключам date и currency есть. Все что нужно - на определенную дату и тип валюты\акции вытащить конкретное значение Rate и умножить его на расчитанное аддитивной мерой значение. В TSQL это проще простого... ... |
|||
:
Нравится:
Не нравится:
|
|||
03.12.2020, 08:36 |
|
MDX вычисляемая мера для куба с акциями акционеров.
|
|||
---|---|---|---|
#18+
AlphaDog Я не совсем понимаю, что такое "фиктивная мера", да и значение AmountAcc пользователям будет нужно для анализа и отчетов определяете меру как null в DSV-слое или в представлении/запросе, а в scope переопределяете её ... |
|||
:
Нравится:
Не нравится:
|
|||
03.12.2020, 08:38 |
|
MDX вычисляемая мера для куба с акциями акционеров.
|
|||
---|---|---|---|
#18+
AlphaDog Но для искомого значения, на мой взгляд, есть все нужные данные - связи по ключам date и currency есть возможно у вас currency и ее ключ неверно связаны, то есть идет агрегация (даже если ее в реальности нет), соответственно rate пустой ... |
|||
:
Нравится:
Не нравится:
|
|||
03.12.2020, 08:43 |
|
MDX вычисляемая мера для куба с акциями акционеров.
|
|||
---|---|---|---|
#18+
Спасибо. Практически получилось. Да, было что-то с агрегациями и связями. Сделал все аккуратно и внимательно - получил нужные цифры. НО! Баланс собирается ТОЛЬКО если в запросе есть оба дименшина - даты и валюты: Клиент->Дата->Валюта=БалансНаДату Как сделать что бы подобная агрегация работала бы и в случае сворачивания запроса до Клиент->Дата=БалансНаДату Ну и с большой долей вероятности клиент захочет агрегацию Клиент->Валюта=БалансНаПоследнююДату. И Клиент=БалансНаПоследнююДату. На текущий момент три последние агрегации возвращают NULL. Я решал эту проблему через IIF( dim.currentmember.level.name='text', функция, функция), но в данном случае в сводной таблице вообще нет дименшина - как с этим работать? ... |
|||
:
Нравится:
Не нравится:
|
|||
03.12.2020, 12:01 |
|
MDX вычисляемая мера для куба с акциями акционеров.
|
|||
---|---|---|---|
#18+
AlphaDog НО! Баланс собирается ТОЛЬКО если в запросе есть оба дименшина - даты и валюты Значит у вас что-то не так сделано, раз считается только на детальном уровне ) По смыслу - у вас только перемножение должно работать на детальном уровне, а все остальное должно считаться от уже умноженных цифр. Имхо - штудируйте первую ссылку в топике. ... |
|||
:
Нравится:
Не нравится:
|
|||
03.12.2020, 15:37 |
|
MDX вычисляемая мера для куба с акциями акционеров.
|
|||
---|---|---|---|
#18+
Критик AlphaDog НО! Баланс собирается ТОЛЬКО если в запросе есть оба дименшина - даты и валюты Значит у вас что-то не так сделано, раз считается только на детальном уровне ) По смыслу - у вас только перемножение должно работать на детальном уровне, а все остальное должно считаться от уже умноженных цифр. Имхо - штудируйте первую ссылку в топике. Я сделал не через "фиктивная меру", а просто вот так: Код: sql 1. 2. 3. 4. 5. 6. 7. 8.
Далее, видимо, нужно прописать SCOPE, который будет на более высоких уровнях агрегации (или как это правильно описать) агрегировать все что было насчитано на более низких Пошел читать дальше.... ... |
|||
:
Нравится:
Не нравится:
|
|||
04.12.2020, 00:50 |
|
MDX вычисляемая мера для куба с акциями акционеров.
|
|||
---|---|---|---|
#18+
Пришел вот к такому решению. Теперь вопрос - как в местах кода THIS = 1,2,3 спуститься до нижнего уровня йерархии, просчитать на нем все [Measures].[UnitsSUM] * [Measures].[Rate] и потом проагрегировать? Код: sql 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18. 19. 20. 21. 22. 23. 24. 25. 26. 27.
... |
|||
:
Нравится:
Не нравится:
|
|||
04.12.2020, 02:41 |
|
MDX вычисляемая мера для куба с акциями акционеров.
|
|||
---|---|---|---|
#18+
Похоже нашел решение... Код: sql 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18. 19. 20. 21. 22. 23.
... |
|||
:
Нравится:
Не нравится:
|
|||
04.12.2020, 04:31 |
|
MDX вычисляемая мера для куба с акциями акционеров.
|
|||
---|---|---|---|
#18+
Интересный вопрос - а как Property "Visible" меры влияет на меру в Calculations? Я так полагал, этот параметр влияет только на видимость меры в Excel или PowerB. Но, похоже, все иначе - делаю меру [Measures].[Rate], невидимой и у меня перестает считаться моя measures.BalanceSUM. Почему? Это же бред... Код: sql 1. 2. 3.
... |
|||
:
Нравится:
Не нравится:
|
|||
06.12.2020, 12:31 |
|
|
start [/forum/topic.php?all=1&fid=49&tid=1857223]: |
0ms |
get settings: |
9ms |
get forum list: |
9ms |
check forum access: |
3ms |
check topic access: |
3ms |
track hit: |
29ms |
get topic data: |
7ms |
get forum data: |
2ms |
get page messages: |
55ms |
get tp. blocked users: |
1ms |
others: | 245ms |
total: | 363ms |
0 / 0 |