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

добавлено: 31 янв 13
понравилось:0
просмотров: 3138
комментов: 0

теги:

Автор: McCar

Аналитика по складским остаткам, - классическая задача, с которой сталкиваются, наверное, почти все специалисты Business Intelligence. В рунете на эту тему можно почитать например, на сайте Южакова: SSAS - анализ остатков.
Здесь я хочу показать на примере два стандартных подхода к решению этой задачи, и один, как мне кажется, новый.
  Обычный бизнес- сценарий:
-на входе имеем такую схему первичного учета, - есть специальная таблица «проводок», в которой каждое движение товара на складе (поступление, перемещение, списание и т.д.) фиксируется отдельной записью.
-на выходе нужно получать отчетность, позволяющую увидеть остаток на произвольную дату в разрезе товара, склада и т.д.    Если мы заменим слово «Склад» на слово «Счет», а слово «Количество товара» на «Сумма», то задача складского учета превращается в задачу финансового учета, соответственно на выходе бухгалтерия хочет увидеть «Оборотно-Сальдовую ведомость».
  Рассмотрим на примере.
Изначально у нас есть: два справочника (с возможность построить "деревянные иерархии" – товары и склады,
и таблица "Проводок" (Движения товара по складам, приход и расход - отдельными записями).
Метаданные в хранилище данных выглядят так:
+ DDL

CREATE TABLE [dbo].[S_WARE_HOUSES](/*Склады*/
	[C_WARE_HOUSE] [int] PRIMARY KEY,
	[C_WARE_HOUSE_PARENT] [int] NOT NULL REFERENCES      [S_WARE_HOUSES]([C_WARE_HOUSE]), --для "Деревянной иерархии"
	[N_WARE_HOUSE] [nvarchar](240) NOT NULL)
GO
CREATE TABLE [dbo].[S_PRODUCTS](/*Товары*/
	[C_PRODUCT] [int] PRIMARY KEY,
	[C_PRODUCT_PARENT] [int] NOT NULL REFERENCES [S_PRODUCTS]([C_PRODUCT]), --для "Деревянной иерархии"
	[N_PRODUCT] [nvarchar](240) NOT NULL,
) 
GO
CREATE TABLE [dbo].[L_MOVE]( /*Проводки*/
	[C_MOVE] [int] PRIMARY KEY,
	[C_PRODUCT] [int] NOT NULL  REFERENCES [S_PRODUCTS]([C_PRODUCT]),  --ссылка на товар
	[C_WARE_HOUSE] [int] NOT NULL REFERENCES [S_WARE_HOUSES]([C_WARE_HOUSE]), --ссылка на склад 
	[MOVE_DATE] [date] NOT NULL, --дата проводки
	[DTCR] [int] NOT NULL, /*0- приход, 1 расход*/
	[MOVE_QTY] [decimal](18, 3) NOT NULL, /*количество товара которое пришло (0)   или ушло (1)*/
	[MOVE_REM] [decimal](18, 3) NULL,
	[MOVE_DATE_KEY]  AS ((datepart(year,[MOVE_DATE])*(10000)+datepart(month,[MOVE_DATE])*(100))+datepart(day,[MOVE_DATE])) PERSISTED
	/*вычисляемое поле нужное для связи с олапным измерением «Дата»*/,
) 
GO

CREATE  INDEX [IX_L_MOVE_C_PRODUCT] ON [dbo].[L_MOVE] ([C_PRODUCT] )
CREATE  INDEX [IX_L_MOVE_C_WARE_HOUSE] ON [dbo].[L_MOVE]([C_WARE_HOUSE] )
CREATE  INDEX [IX_L_MOVE_MOVE_DATE] ON [dbo].[L_MOVE] ([MOVE_DATE] )


В эти таблицы я загрузил некую тестовую выборку данных из реальной базы. Получилось 12 000 товаров, 3 400 складов, и 1 500 000 проводок за период в три года.
   Для начала попробуем решить задачу через MDX Calculations. Нам нужно:
а)Построить таблицу фактов, в которой будут храниться движения товара на складе.
б)На основе этой таблицы фактов строим группу мер с мерами «Приход», «Расход» и «Оборот».
в)Построить вычисляемые меры "Приход", "Расход", "Оборот" и "Остаток на дату".
Причем "Остаток на дату" мы вычисляем суммируя меру "Оборот" "с начала времен".
г)Обеспечить заказчика нужной отчетностью и повысить тем самым производительность труда своей фирмы
д)Идти в бухгалтерию получить премию. :-)

  Детали реализации:
   а)В dsv рисуем следующие таблицы:
Картинка с другого сайта.

  Б)Измерения и меры в кубе
На основе этих таблиц строим:
-Измерения:
--Склады (DimWareHouses)
--Товары (DimProducts)
--Тип Движения (приход или расход) (DimTransactionTypes)
--Дата (DimDate).
-Группа мер:
--Проводки ([F_MOVE]) - обычная мера с суммированием.
Картинка с другого сайта.

  в)Вычисляемые меры:
+ MDX CubeCalculatedMembers

CREATE MEMBER CURRENTCUBE.[Measures].[cmMOVE_QTY_INCOME] --ПРИХОД
as ([DimTransactionTypes].[TransactionTypes].&[0], [MOVE_QTY])
,NON_EMPTY_BEHAVIOR={[MOVE_QTY]} 
,FORMAT_STRING="#,#"
,Caption='Приход'
,ASSOCIATED_MEASURE_GROUP = 'F_MOVE';
 CREATE MEMBER CURRENTCUBE.[Measures].[cmMOVE_QTY_OUTCOME] --РАСХД
as ([DimTransactionTypes].[TransactionTypes].&[1], [MOVE_QTY])
,FORMAT_STRING="#,#"
,NON_EMPTY_BEHAVIOR={[MOVE_QTY]} 
,Caption='Расход'
,ASSOCIATED_MEASURE_GROUP = 'F_MOVE';
CREATE MEMBER CURRENTCUBE.[Measures].[cmMOVE_QTY_INCOME_OUTCOME] --ОБОРОТ
as [cmMOVE_QTY_INCOME] - [cmMOVE_QTY_OUTCOME]
,FORMAT_STRING="#,#"
,NON_EMPTY_BEHAVIOR={[MOVE_QTY]} 
,Caption='Оборот'
,ASSOCIATED_MEASURE_GROUP = 'F_MOVE'; 
CREATE MEMBER CURRENTCUBE.[Measures].[cmRemByINCOME_OUTCOME] 
/*остаток на конец периода расчитываемый по 
сумме оборота с начала времен*/
as sum({null:tail(exists([DimDate].[Day].[Day], [DimDate].[Hierarchy].CurrentMember),1)(0)},[cmMOVE_QTY_INCOME_OUTCOME] ) 
,Caption='Ост. на конец пер.'
,FORMAT_STRING="#,#"
,ASSOCIATED_MEASURE_GROUP = 'F_REM_FOR_EVERY_DAY'; 



  г)Отчетность.
Все. У нас есть меры "Приход", "Расход", "Оборот" и "Остаток на дату".
Мы можем строить отчет, например такой -"Динамика остатка на складе за один месяц":
select 
{[cmMOVE_QTY_INCOME],[cmMOVE_QTY_OUTCOME] ,[cmMOVE_QTY_INCOME_OUTCOME], [cmRemByINCOME_OUTCOME]} on 0,
non empty [DimDate].[Hierarchy].[Month].&[201211].Children on 1 
from [Inventory-Cube]
where 
({[DimWareHouses].[WareHouseParent].&[110119643]})


Результатом этого запроса будет такая таблица.
ДатаПриходРасходОборотОст. на конец пер.
01.11.20121776634082-163161538312
02.11.20121538312
03.11.20121668624898-82121530100
04.11.20125931453-139371516163
05.11.2012218327043-24861491303
06.11.20125100614568364381527741
07.11.20121527741
08.11.20121527741
09.11.2012129436162851131511640892
10.11.2012


  д)В теории.. все шоколадно. А в жизни - по разному.
Гранулярность мер в кубе обычно включает большее количество измерений, чем в тестовом примере.
Пользователь, привыкнув к "кубо-верчению", выдумывает свои отчетные показатели с своей логикой агрегирования вычисляемых мер, вроде "Динамики среднего остатка по товарным группам, в которых этот средний остаток превышает определенный порог", и тому подобное.
Рано или поздно при использовании вычисляемых мер мы сталкиваемся с проблемой производительности.
И выясняется что.. пункт "д", где про премию, откладывается. :-(

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

Комментарии




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