ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / OLAP и DWH [игнор отключен] [закрыт для гостей] / Aging Report (Ведомость остатков задолженности)
7 сообщений из 7, страница 1 из 1
Aging Report (Ведомость остатков задолженности)
    #33465103
Андрей Никифоров
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Андрей Никифоров
Участник
Всем Привет!

Бьюсь над aging report в SSAS (MSAS2K5).

Исходные данные:

1. таблица Item с выставленными invoice:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
CREATE TABLE [dbo].[Item](
	[ItemID] [int] IDENTITY( 1 , 1 ) NOT NULL,
	[Description] [nvarchar]( 50 ) NOT NULL,
	[Price] [money] NOT NULL ,
	[ItemDate] [datetime] NOT NULL ,
	[CustomerID] [int] NOT NULL,
 CONSTRAINT [PK_Item] PRIMARY KEY CLUSTERED ([ItemID] ASC)
)

2. таблица с платежами:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
CREATE TABLE [dbo].[Payment](
	[PaymentID] [int] IDENTITY( 1 , 1 ) NOT NULL,
	[PaymentAmount] [money] NOT NULL,
	[PaymentDate] [datetime] NOT NULL ,
	[ItemID] [int] NOT NULL,
 CONSTRAINT [PK_Payment] PRIMARY KEY CLUSTERED ([PaymentID] ASC)
)

3. таблица с группами сроков задолженности:

Код: plaintext
1.
2.
3.
4.
5.
6.
CREATE TABLE [dbo].[Age](
	[Description] [nvarchar]( 50 ) NOT NULL,
	[StartDay] [int] NOT NULL ,
	[EndDay] [int] NOT NULL ,
	[AgeId] [int] IDENTITY( 1 , 1 ) NOT NULL,
 CONSTRAINT [PK_Age] PRIMARY KEY CLUSTERED ([AgeId] ASC)
)

примеры: 0-30, 16-45, 31-60 и т.д. (группы могут пересекаться)

4. таблица customer

Что нужно получить в Excel:

На <As Of Date>:

-------------- 0-30 --- 31-60 --- 61-120 --- >120
Customer 1 | 10 | 20 | 0 | 0

Т.е. на выбранную пользователем дату надо показать остаток клиентской задолженности разбитый по группам сроков (aging).

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

У кого-нибудь есть опыт или идеи как это можно сделать?
...
Рейтинг: 0 / 0
29.12.2005, 21:14
| Ответить | Цитировать | Написать  
Aging Report (Ведомость остатков задолженности)
    #33465108
Владимир Штепа
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владимир Штепа
Участник
группы могут пересекаться

не есть гут, точнее совсем плохо.


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

Для этого еще до процессинга куба в SQL вычисляется срок задолженности каждого счета и куб строится на вьюхе в которой делаентся JOIN по неравенству [Age] с таблицей счетов/оплат.

Решал я это еще для AS2K, который ничего кроме тупого inner join не понимает.
...
Рейтинг: 0 / 0
29.12.2005, 21:34
| Ответить | Цитировать | Написать  
Aging Report (Ведомость остатков задолженности)
    #33465144
Андрей Никифоров
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Андрей Никифоров
Участник
Я сделал следующим образом.

1. сделал view который собирает Item & Payment

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
create view [dbo].[DebtDelta] as
select i.itemid, i.Price as DebtDelta, i.itemdate debtdate
from
	Item i
union all
select i.itemid, - p.paymentamount as DebtDelta, p.paymentdate debtdate
from 
	Payment p
	join Item i on i.itemid = p.itemid

2. построил кубик:

DebtDelta -- Fact, Item/Customer -- измерение с иерархией Customer - Item, Date -- серверное измерение с иерархией [Year - Quarter - Month - Date]
Age -- измерение добавленное в кубик, но не соединенное

3. CMs:


Код: plaintext
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.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
CREATE MEMBER CURRENTCUBE.[MEASURES].Age
 AS iif(isleaf([Item].[Item Date]) 
    ,iif(datediff("d"
        ,[Item].[Item Date].currentmember.membervalue
        ,ClosingPeriod([Date].[Year -  Quarter -  Month -  Date].[Date]).membervalue
    ) >=  0 
        ,datediff("d"
            ,[Item].[Item Date].currentmember.membervalue
            ,ClosingPeriod([Date].[Year -  Quarter -  Month -  Date].[Date]).membervalue
        )
        ,null
    )    
    ,null
), 
VISIBLE =  1 ;    
CREATE MEMBER CURRENTCUBE.[MEASURES].[Amount PreCalc]
 AS sum(
	Generate(
		Filter(
            Descendants( 
                [Item].[Customer - Item].CurrentMember
                , [Item].[Customer - Item].[Item]
                , SELF 
            )
			,not IsLeaf([Age].[Age]) 
            or (ClosingPeriod([Date].[Year -  Quarter -  Month -  Date].[Date]), Measures.Age) >= [Age].[Start Day].currentmember.membervalue
            and (ClosingPeriod([Date].[Year -  Quarter -  Month -  Date].[Date]), Measures.Age) <= [Age].[End Day].currentmember.membervalue
			)	
		,generate(
			ascendants([Date].[Year -  Quarter -  Month -  Date].currentmember)
			,iif(
				[Date].[Year -  Quarter -  Month -  Date].currentmember IS
				[Date].[Year -  Quarter -  Month -  Date].firstsibling,
				{},
				[Date].[Year -  Quarter -  Month -  Date].firstsibling:
				[Date].[Year -  Quarter -  Month -  Date].prevmember
			)	
		) + [Date].[Year -  Quarter -  Month -  Date].currentmember
	)	
	,[Debt Delta]
), 
VISIBLE =  1 ;  
CREATE MEMBER CURRENTCUBE.[MEASURES].Amount
 AS iif([Measures].[Amount PreCalc] =  0 , null, [Measures].[Amount PreCalc]), 
VISIBLE =  1 ;

Все работает, но есть проблемы.
...
Рейтинг: 0 / 0
29.12.2005, 22:51
| Ответить | Цитировать | Написать  
Aging Report (Ведомость остатков задолженности)
    #33465147
Андрей Никифоров
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Андрей Никифоров
Участник
Проблемы:

1. при 2M Item & 4M Payment:

ответ на MDX запрос: 2m
из Excel: 1h+ (отменил)

2. все CM используют определенные иерархии, как сделать так, чтобы работало на любых иерархиях (например не только [Year - Quarter - Month - Date], но и [Year - Week - date])
...
Рейтинг: 0 / 0
29.12.2005, 22:55
| Ответить | Цитировать | Написать  
Aging Report (Ведомость остатков задолженности)
    #33465153
Андрей Никифоров
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Андрей Никифоров
Участник
Примечания:

1. <As Of Date> может быть любой гранулярности. Если выше даты, то надо брать конец периода.

2. Age считать нужно, т.к. надо будет считать прочие показатели использующие сроки и суммы (например Days Sales Outstanding)

3. Пересекающиеся группы сроков нужны разным отделам. Т.е. можно попытаться создать доп уровень иерархии в Age, где все листья будут непересекающимися (но All все равно не будет additive)
...
Рейтинг: 0 / 0
29.12.2005, 23:00
| Ответить | Цитировать | Написать  
Aging Report (Ведомость остатков задолженности)
    #33465156
Андрей Никифоров
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Андрей Никифоров
Участник
backfire

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



Отчет на любую дату делается в существующей OLTP. Т.е. клиент и сейчас может запустить отчеты за разные периоды, скопировать данные в Excel и посчитать разницы/построить графики. Но ему это не очень нравится :-)
...
Рейтинг: 0 / 0
29.12.2005, 23:06
| Ответить | Цитировать | Написать  
Aging Report (Ведомость остатков задолженности)
    #33466470
Андрей Никифоров
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Андрей Никифоров
Участник
Поправил CM (убрал избыточный generate)

Код: plaintext
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.
CREATE MEMBER CURRENTCUBE.[MEASURES].[Amount PreCalc]
 AS sum(
	(
		Filter(
            Descendants( 
                [Item].[Customer - Item].CurrentMember
                , [Item].[Customer - Item].[Item]
                , SELF 
            )
			,not IsLeaf([Age].[Age]) 
            or (ClosingPeriod([Date].[Year -  Quarter -  Month -  Date].[Date]), Measures.Age) >= [Age].[Start Day].currentmember.membervalue
            and (ClosingPeriod([Date].[Year -  Quarter -  Month -  Date].[Date]), Measures.Age) <= [Age].[End Day].currentmember.membervalue
			)	
		,generate(
			ascendants([Date].[Year -  Quarter -  Month -  Date].currentmember)
			,iif(
				[Date].[Year -  Quarter -  Month -  Date].currentmember IS
				[Date].[Year -  Quarter -  Month -  Date].firstsibling,
				{},
				[Date].[Year -  Quarter -  Month -  Date].firstsibling:
				[Date].[Year -  Quarter -  Month -  Date].prevmember
			)	
		) + [Date].[Year -  Quarter -  Month -  Date].currentmember
	)	
	,[Debt Delta]
), 
VISIBLE =  1 ;
...
Рейтинг: 0 / 0
31.12.2005, 00:00
| Ответить | Цитировать | Написать  
7 сообщений из 7, страница 1 из 1
Форумы / OLAP и DWH [игнор отключен] [закрыт для гостей] / Aging Report (Ведомость остатков задолженности)
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]