Аналитика по остаткам в SSAS: MDX, Snapshot и Many-To-Many. Часть вторая.

добавлено: 02 фев 13
понравилось:0
просмотров: 2350
комментов: 0

теги:

Автор: McCar

Часть Вторая. Реализация через semiadditive measures
(Начало - здесь:http://www.sql.ru/blogs/dklmnmsbi/1450)
Итак, наша новая задача - кардинально увеличить производительность запросов, в которых выводятся наши остатки (не поймите выражение "наши остатки" неправильно :-) ).
План - такой:
а)Делаем расчет в реляционной базе остатков, которые образуются после каждого "Движения".
б)Делаем в SSAS таблицу фактов, в которой для каждого сочетания "Товар - Склад" (при добавлении других измерении естественно гранулярность подсчета меняется), и для каждого дня, когда есть ненулевой остаток, подсчитаны текущие значения.
в)Делаем в SSAS на основе этой таблицы полуаддитивные меры с остатками. Привязка к измерениям остается тривиальной.
г)Тестируем отчетность. Убеждаемся что значения новой меры совпадает со старой, меняем используемую меру в существующих отчетах.
д)Наблюдаем удовлетворенные лица пользователей. Идем в кассу за премией.

Детали реализации
а)Для хранения подсчитанного остатка мы создадим отдельную таблицу.
CREATE TABLE [dbo].[L_MOVE_REM](
	[C_MOVE] [int] PRIMARY KEY REFERENCES [dbo].[L_MOVE] ([C_MOVE]),
/*ссылка на проводку, которая сформировала остаток*/
	[C_MOVE_NEXT] [int] NULL REFERENCES [dbo].[L_MOVE] ([C_MOVE]),
/*ссылка на следующую проводку по этому сочитанию Товар- Склад - (и т.д. в зависимости от гранулярности)
-нужно для оптимизации запроса*/
	[MOVE_REM] [decimal](18, 3) NOT NULL,
/*остаток который образовался после "движения товара" в жизни, ну или "проводки" в учетной системе*/
)


Сам расчет производим следующим скриптом:
+ Скрипт для расчета нарастающего итога и остатков

USE Inventory;
truncate table L_MOVE_REM
DECLARE @RunningTotal decimal(18,3)
declare @tmp_moves table  (C_MOVE int, MOVE_DATE DATE, QTY DECIMAL(18,3), QTY_ROLLING decimal(18,3));
DECLARE @C_PRODUCT int, @C_WARE_HOUSE INT;
declare @cnt int; 
set @cnt=0;
DECLARE @mess varchar(100);
DECLARE CRS CURSOR FOR
select   m.C_PRODUCT,m.C_WARE_HOUSE
from L_MOVE m group by  m.C_PRODUCT,m.C_WARE_HOUSE --having COUNT(*)  between 5 and 10;

OPEN CRS; --цикл по всем сочетаниям "Товар-Склад"
FETCH NEXT FROM CRS
INTO @C_PRODUCT, @C_WARE_HOUSE
WHILE @@FETCH_STATUS = 0 
BEGIN
set @cnt=@cnt+1;
set @mess=CAST(@cnt as varchar(10));raiserror(@mess,5,10) with nowait; 
delete from @tmp_moves; 

insert @tmp_moves(C_MOVE, MOVE_DATE, QTY)--
SELECT
max(m.c_move) as MIN_C_MOVE /*НА конец  ДНЯ СЧИТАЕМ, поэтому соотносим остаток с последней проводокой задень если их несколько*/,  
M.MOVE_DATE,sum(m.MOVE_QTY  *case when m.DTCR=0 then 1 else -1 end) 
 from L_MOVE m 
 where  m.C_PRODUCT=@C_PRODUCT  and m.C_WARE_HOUSE=@C_WARE_HOUSE
 group by m.MOVE_DATE order by  m.MOVE_DATE asc;
 ----
SET @RunningTotal = 0 
UPDATE @tmp_moves SET @RunningTotal = QTY_ROLLING = @RunningTotal + QTY FROM @tmp_moves;
--считаем остаток через "нарастающий итог"
with cte_rn as (/*джойним по порядковому номеру чтобы наити следующее значение остатка*/
select m.*, ROW_NUMBER() over (order by move_date asc) as RN from  @tmp_moves m)
, cte_joined_to_next as (
select  m.*, mnext.C_MOVE as C_MOVE_NEXT from cte_rn m left join cte_rn mnext on m.RN=mnext.RN-1)
insert L_MOVE_REM(C_MOVE, C_MOVE_NEXT,MOVE_REM)
select mm.C_MOVE, mm.C_MOVE_NEXT, mm.QTY_ROLLING from cte_joined_to_next mm;
--
 FETCH NEXT FROM CRS
INTO @C_PRODUCT, @C_WARE_HOUSE
   END
CLOSE CRS;
DEALLOCATE CRS;
--------



Подозреваю, что такой способ подсчета не является оптимальным по производительности, к тому же здесь используется не вполне задокументированный способ подсчета нарастающего итога
(UPDATE @tmp_moves SET @RunningTotal = QTY_ROLLING = @RunningTotal + QTY FROM @tmp_moves;).
С благодарностью приму варианты реализации подсчета без таких "кул-хаков".

б) Таблица фактов для SSAS
У нас есть подсчитанные значения всех остатков на те дни, когда этот остаток был изменен.
Но нам для таблицы фактов нужны все дни, в том числе те, когда движения не происходили.
Чтобы облегчить себе жизнь при дальнейшем развитии системы,мы делаем некоторые вспомогательные функции и вьюхи
+ ВСПОМОГАТЕЛЬНЫЕ ФУНКЦИИ и вьюхи

CREATE  FUNCTION [dbo].[udfDateToInteger](@Date DATE)
returns int --для превращения дат в интовые ключи -удобные для использования в качестве ключа измерения
as
begin
    return Year(@Date)*10000+Month(@Date)*100+Day(@Date);
end
GO

GO
CREATE FUNCTION [dbo].[udf_GetDateRange] 
(	--просто выводим перечень последовательных дат из заданного диапазона
	@DATE_BEGIN DATE,
	@DATE_END DATE
)
RETURNS  @RETTABLE TABLE(DATE_CURRENT DATE)
AS 
BEGIN
declare @i int
declare @dat datetime
set @i=0
set @dat=@DATE_BEGIN;
while @dat<=@DATE_END
begin
INSERT INTO @RETTABLE VALUES (@dat);
set @i=@i+1;
set @dat=DATEADD("d",@i,@DATE_BEGIN)
end
return;
END
GO
CREATE  VIEW [dbo].[vOlapMoveRemByPeriods] as 
select 
 m.C_MOVE,
m.MOVE_DATE as DATE_BEGIN, coalesce(DATEADD(DAY, -1,mnext.move_date), cast(getdate() as date)) as DATE_END,
--dbo.udfDateToInteger(m.MOVE_DATE) as DATE_BEGIN_KEY,  dbo.udfDateToInteger(coalesce(DATEADD(DAY, -1,mnext.move_date), cast(getdate() as date))) as DATE_END_KEY,
  m.C_PRODUCT, m.C_WARE_HOUSE,mr.MOVE_REM 
  from L_MOVE_REM  mr join L_MOVE m on m.C_MOVE=mr.C_MOVE left join L_MOVE mnext on mnext.C_MOVE=mr.C_MOVE_NEXT
GO


И с помощью этих функций формируем запрос для таблицы фактов F_REM_FOR_EVERY_DAY,которая по сути представляет собой некий "Вычисляемый snapshop на конец дня".
SELECT m.C_MOVE, dbo.udfDateToInteger(daterange.DATE_CURRENT) as DATE_REM_KEY,
m.C_PRODUCT, m.C_WARE_HOUSE,m.MOVE_REM 
FROM vOlapMoveRemByPeriods m
CROSS APPLY dbo.udf_GetDateRange(m.DATE_BEGIN,m.DATE_END) daterange

в)Строим в SSAS на этот раз уже физическую меру "Остаток" (REM_FOR_EVERY_DAY) с AggregateFunction=LastChild
Получается такая картина:
Картинка с другого сайта.

г)Тестируем через сравнение с значениями остатка по старой, вычисляемой мере, например таким запросом
select 
{[DimWareHouses].[WareHouseParent].&[110119643]}
*{[cmRemByINCOME_OUTCOME],[REM_FOR_EVERY_DAY]} on 0,
non empty [DimDate].[Hierarchy].[Month].&[201211].Children on 1 
from [Inventory-Cube]

Убедившись что значения новой (физической) и старой (вычисляемой) мерой совпадают, выдумываем запрос посложнее и смотрим в профайлере производительность.
Изменяем название меры в существующих отчетах
д)Забираем в кассе премию и .. задумываемся еще немного..
Продолжение - следует.
(SSAS проект и скрипт для создания метаданных в реляционной базе можно взять здесь: https://www.dropbox.com/s/e42ueesskoexk83/Inventory-SSAS-Project.zip )
----------------
Кальманович Дмитрий.

Комментарии




Необходимо войти на сайт, чтобы оставлять комментарии