Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / OLAP и DWH Новый топик    Ответить
 MDX вычисляемая мера для куба с акциями акционеров.  [new]
AlphaDog
Member

Откуда: in the middle of nowhere
Сообщений: 507
День добрый

Постановка задачи - имеется 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 ноя 20, 04:30
21 ноя 20, 04:32    [22236310]     Ответить | Цитировать Сообщить модератору
 Re: MDX вычисляемая мера для куба с акциями акционеров.  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 34255
Блог
AlphaDog,

баланс считается точно так же, как и остатки (см поиск по форуму) - через фиктивную sum-меру и scope

Сообщение было отредактировано: 21 ноя 20, 19:16
21 ноя 20, 19:20    [22236438]     Ответить | Цитировать Сообщить модератору
 Re: MDX вычисляемая мера для куба с акциями акционеров.  [new]
AlphaDog
Member

Откуда: in the middle of nowhere
Сообщений: 507
Критик,

Спасибо. Вы этот топик имели ввиду?

https://www.sql.ru/forum/524753-6/novyy-podhod-k-ostatkam-v-2005
22 ноя 20, 23:51    [22236819]     Ответить | Цитировать Сообщить модератору
 Re: MDX вычисляемая мера для куба с акциями акционеров.  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 34255
Блог
Да, оно. Но вы зря не хотите материализовать остатки в таблицу.
Они часто бывает нужны для
1) сторонних приложений
2) для расчетов поверх остатков (а так у вас получается, что сначала должны считаться остатки, потом поверх них что-то еще)

Сообщение было отредактировано: 23 ноя 20, 00:21
23 ноя 20, 00:24    [22236842]     Ответить | Цитировать Сообщить модератору
 Re: MDX вычисляемая мера для куба с акциями акционеров.  [new]
AlphaDog
Member

Откуда: in the middle of nowhere
Сообщений: 507
Критик,

Спасибо. Почитаю, попробую разобраться.

Технически не "остатки", или я не понимаю вашей терминологии. Это количество акций акционеров по разным типам акций. Приложение для этого куба только одно - PowerBI. Отчетов пока готовых нет - не окончился этап разработки проекта.

Пересчет всего в таблицу раздувает ее до астрономических размеров - со 170 миллионов записей до 4 миллиардов примерно, что делает процессинг куба крайне длительной операцией, да и работа с кубом потом очень медленная - читать с диска гигабайты данных это долго.

Если честно, я был бы крайне признателен за помощь - проект близок к сдаче и я основательно плаваю в теме.
23 ноя 20, 00:36    [22236855]     Ответить | Цитировать Сообщить модератору
 Re: MDX вычисляемая мера для куба с акциями акционеров.  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 34255
Блог
AlphaDog,

у вас куб многомерный или табулярный?
23 ноя 20, 12:24    [22237072]     Ответить | Цитировать Сообщить модератору
 Re: MDX вычисляемая мера для куба с акциями акционеров.  [new]
AlphaDog
Member

Откуда: in the middle of nowhere
Сообщений: 507
Критик,

Многомерный.

Идею я понял, теперь пытаюсь средствам MDX получить желаемое :)

Что-то типа

Мера = ....Currentmember.LastNonEmptyChild

и scope для LastNonEmptyChild -> будущее, который будет возвращать "Мера"

Не могу понять только, как мне вставить scope в MDX запросе в SSMS. Все примеры что нашел - для calculate в дизайне куба. Очень неудобно отлаживать запросы...
23 ноя 20, 12:37    [22237090]     Ответить | Цитировать Сообщить модератору
 Re: MDX вычисляемая мера для куба с акциями акционеров.  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 34255
Блог
AlphaDog,

в calculate и вставляете, а запросах используете вашу вычисляемую меру
23 ноя 20, 12:43    [22237096]     Ответить | Цитировать Сообщить модератору
 Re: MDX вычисляемая мера для куба с акциями акционеров.  [new]
AlphaDog
Member

Откуда: in the middle of nowhere
Сообщений: 507
Критик,

Я понимаю, но перепроцессить куб занимает некоторое время. Это значительно снижает скорость разработки. Хотя я думаю попробовать IIF() - это проще для понимания.
23 ноя 20, 12:48    [22237106]     Ответить | Цитировать Сообщить модератору
 Re: MDX вычисляемая мера для куба с акциями акционеров.  [new]
alexdr
Member

Откуда:
Сообщений: 249
AlphaDog
Не могу понять только, как мне вставить scope в MDX запросе в SSMS. Все примеры что нашел - для calculate в дизайне куба. Очень неудобно отлаживать запросы...

Когда-то у Моши подсмотрел такой прием. Для эмуляции SCOPE используется CELL CALCULATION. Ниже псевдокод иллюстрирующий идею с эмулятором скоупа:
WITH
	MEMBER [Measures].[Test] AS  AS NULL
	CELL CALCULATION ScopeEmulator
		FOR '(
				<тут определение скоупа>
				,[Measures].[Test]
			 )'
	AS
		<тут вычисление для субкуба(скоупа)>
--ниже запрос:
SELECT
(
	[Measures].[Test]
) ON 0,
(
	<тут выражение набора мемберов для строк>
) ON 1
FROM <SomeCube>

AlphaDog
... перепроцессить куб занимает некоторое время. Это значительно снижает скорость разработки.

Хм... если речь идет только о редактировании скрипта куба, существуют решения, которые позволяют деплоить скрипт куба без его перепроцешивания. Напр., ранее называвшееся решение BIDS helper. Сейчас его переименовали, но можно погуглить и понять, что нужно поискать.

И еще. Точно не помню, но... Если подключиться visual studio напрямую к отдеплоенному на сервере кубу, внести правку только в скрипт куба и нажать на кнопку сохранить, разве будет требоваться репроцессинг куба (ну просто запамятовал этот момент)?
23 ноя 20, 14:10    [22237190]     Ответить | Цитировать Сообщить модератору
 Re: MDX вычисляемая мера для куба с акциями акционеров.  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 34255
Блог
AlphaDog,

изменения в calculate не требуют репроцессинга
23 ноя 20, 14:38    [22237222]     Ответить | Цитировать Сообщить модератору
 Re: MDX вычисляемая мера для куба с акциями акционеров.  [new]
AlphaDog
Member

Откуда: in the middle of nowhere
Сообщений: 507
В общем нужное накопление суммы акций по датам я получил. Почти все работает как планировалось.

Есть только одна проблема - не знаю как прикрутить стоимость акций к полученным суммам. Должно быть что-то несложное, но не могу сообразить как это сделать. Обе таблицы линкуются по Date_FK и InvestmentOption_FK. Чувствую, что можно сделать через LINKMEMBER(), но мне кажется должно быть более изящное решение.

К сообщению приложен файл. Размер - 18Kb
24 ноя 20, 11:21    [22237737]     Ответить | Цитировать Сообщить модератору
 Re: MDX вычисляемая мера для куба с акциями акционеров.  [new]
bideveloper
Member

Откуда:
Сообщений: 511
AlphaDog,

Стоимость можно считать по тому же методу, что и баланс по Member Units. Т. е. надо сначала добавить стоимость акции на дату в таблицу/представление FactMemberBalance.
24 ноя 20, 12:16    [22237780]     Ответить | Цитировать Сообщить модератору
 Re: MDX вычисляемая мера для куба с акциями акционеров.  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 34255
Блог
AlphaDog,

тут смотря что вы понимаете под стоимостью...
например, у вас же есть сумма и количество - можно просто разделить одно на другое
24 ноя 20, 16:24    [22237905]     Ответить | Цитировать Сообщить модератору
 Re: MDX вычисляемая мера для куба с акциями акционеров.  [new]
AlphaDog
Member

Откуда: in the middle of nowhere
Сообщений: 507
Критик,

У меня есть сумма количества акций определенного типа. В другой таблице у меня есть котировки этих акций всех типов по дням. Нужен баланс в валюте на каждый день по каждому члену. То есть просто умножить количество акций на их стоимость в конкретный день
25 ноя 20, 00:01    [22238069]     Ответить | Цитировать Сообщить модератору
 Re: MDX вычисляемая мера для куба с акциями акционеров.  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 34255
Блог
AlphaDog,

что мешает в таблице фактов (или в представлении) умножить?

Сообщение было отредактировано: 25 ноя 20, 07:51
25 ноя 20, 07:56    [22238115]     Ответить | Цитировать Сообщить модератору
 Re: MDX вычисляемая мера для куба с акциями акционеров.  [new]
AlphaDog
Member

Откуда: in the middle of nowhere
Сообщений: 507
Критик
AlphaDog,

что мешает в таблице фактов (или в представлении) умножить?


То, что факты (изменения количества акций у акционера) появляются достаточно редко. Скажем 2-3-10 раз в год. А цена на акцию меняется ежедневно и баланс в валюте нужно анализировать ежедневно.

Можно создать таблицу фактов по дням, где баланс будет просчитан заранее, но это увеличивает таблицу фактом в DW с 200 миллионов записей до 4 миллиардов. Процессинг куба становится адом, и сама работа с ним - очень медленной. На диске чистый куб с минимум агрегаций берет 90гигабайт.
25 ноя 20, 08:53    [22238128]     Ответить | Цитировать Сообщить модератору
 Re: MDX вычисляемая мера для куба с акциями акционеров.  [new]
AlphaDog
Member

Откуда: in the middle of nowhere
Сообщений: 507
AlphaDog,

Вот такая у меня примерно картинка. Вот там где колонка UnitPrice должно быть не (All), а значение курса для типа акций 18 на соответствующую дату. Колонка Units - это только изменения по количеству акций по данному акционеру.

К сообщению приложен файл. Размер - 72Kb
25 ноя 20, 08:59    [22238131]     Ответить | Цитировать Сообщить модератору
 Re: MDX вычисляемая мера для куба с акциями акционеров.  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 34255
Блог
AlphaDog,

тогда задача сводится к задаче конвертации валют
25 ноя 20, 09:14    [22238136]     Ответить | Цитировать Сообщить модератору
 Re: MDX вычисляемая мера для куба с акциями акционеров.  [new]
AlphaDog
Member

Откуда: in the middle of nowhere
Сообщений: 507
Критик,

Спасибо. В AdventureWork2014 что-то для примера есть с конвертацией валют?
25 ноя 20, 09:17    [22238138]     Ответить | Цитировать Сообщить модератору
 Re: MDX вычисляемая мера для куба с акциями акционеров.  [new]
AlphaDog
Member

Откуда: in the middle of nowhere
Сообщений: 507
Вижу уже - есть что-то. Спасибо за наводку
25 ноя 20, 09:30    [22238140]     Ответить | Цитировать Сообщить модератору
Все форумы / OLAP и DWH Ответить