Файл: Автоматизация аналитического учета ООО «ПепсиКо Холдингс».pdf

По продуктовому и торговому направлениям справочная информация хранится в «Current», т.е. любое изменение в справочной информации отображается во всей ИС.

Пример работы с временной иерархией на рисунке 2.2.

Рисунок 2.2. Иерархия времени

Пример работы с продуктовой иерархией на рисунке 2.3.

Рисунок 2.3. Иерархия продуктов

Пример работы с торговой иерархией на рисунке 2.4.

Рисунок 2.4. Торговая иерархия

Справочная информация с временной иерархие автоматическая и поддерживается средствами MS SQL Server, мэппинги по продуктам и клиентам поддерживаются специальным отделом Data Master и доступны в Excel формате.

Данные справочники, включая продажи загружаются на SQL Server средсвами MS SQL Server Integrated Service, где в DataFlow пакетах настраивается «забор» данных из сетевых папок, на которые любой аналитик, обрабатывающий данные может выложить необходимые ресурсы.

Далее теме же SSIS пакетами происходит обработка данных, где в запросе на обновление указан следующий запрос:

delete

FROM [CWHD].[dbo].[SellOut]

where ISNULL( [Рубли],0)= 0 and ISNULL( [Штуки],0)= 0

TRUNCATE TABLE [CWHD].[dbo].fact_table

INSERT INTO [CWHD].[dbo].fact_table

( [date]

,[outlet_code]

,[sku_code]

,[amount]

,[qty]

,[vol]

,[vol\8oz])

SELECT [Дата] as [date]

,cast(hashbytes('****',upper(SellOut.[Клиент] + '_' + [Код ТТ])) as bigint) as outlet_code

,cast(hashbytes('****',upper([Код СКЮ])) as bigint) as sku_code

,round(cast([Рубли] as float),4) as amount

,round(cast([Штуки] as float),4) as qty

,round(cast(([Штуки]*cast(replace(dbo.[Cross_Prod].[Вес],',','.') as float)) as float),5) as vol

,case

when Cross_Prod.[Группа продукта PEP]='Beverages' then round(cast(([Штуки]*cast(replace(dbo.[Cross_Prod].[Вес],',','.') as float)) as float),5)/5.67812

else round(cast(([Штуки]*cast(replace(dbo.[Cross_Prod].[Вес],',','.') as float)) as float),5)

end as [vol/8oz]

FROM dbo.SellOut LEFT OUTER JOIN

dbo.Cross_Prod ON dbo.SellOut.[Код СКЮ] = dbo.Cross_Prod.[Product ID Dixy]

По условиям изначальной задачи необходимо получение данных не только в рублях и штуках, а так же и в КГ и Литрах + для категории напитков в восьмиунцовках (еденица, необходимая для точного производства). Для этого все исходные данные из промежуточной таблицы с кодировкой загружаются в таблицу fact_table с учетом арефметических операций.

2.4. Характеристика результатной информации

Результатной информацие будет OLAP Cube, доступ к которому выдается определенному количеству лиц для функционального использования.

Куб содержит следующие данные:

Меры
1. #Active Account
2. #SKU
3. Net Revenue
4. Quantity
5. Vol 8oz
6. Vol
7. VPO, NR
8. VPO, QTY
9. VPO, Volume
Измерение времени
1. Year
2. Quarter
3. Month
4. Week
Измерение продуктов
1. Barcode
2. SKU Name
3. Package size
4. Package type
5. Fat
6. Flavor
7. Brand group
8. Brand
9. Category
10. Category PEP
Измерение клиентов
1. Outlet_ID
2. Address
3. City
4. Area
5. Region
6. RSM
7. TSM
8. Developer
9. DC
10. Branch

Глава 3. Программное обеспечение задачи

3.1. Общие положения (дерево функций и сценарий диалога)

Т.к. информационная система в конечном счете представляет собой источник данных для self-service обработки данных, диалог с пользователем осуществляется посредством сводной таблицы Excel. Пример на рисунке 3.1.

Рисунок 3.1. Сводная таблица

Так же, гибкость системы и скорость настройки OLAP кубов позволит при необходимости добавлять новый расчетные меры в уже существующую модель.

3.2. Характеристика базы данных

Для управления и хранения большого объема данных ИС используется СУБД MS SQL Server 2008 (SP3) – 10.0.5538.0 (X64). Для автоматизации процесса загрузки, обработки и обнавления информации используется MS Visual Studio: SS Analysis Service и SS Integration Service. В решаемой задачи используется ER-модель, представленная на рисунке 3.2. ER-модель описывает взаимосвязь таблиц в БД.

Рисунок 3.2. ER модель

Структура таблицы DIM_Time представляет собой автоматически сформировную таблицу времени, где в качестве первичного ключа используется дата в формате dd.mm.yyyy. В OLAP Кубе по ТЗ требуются не все поля, поэтому на этапе формирования структуры измерения в дальнейшем будут использованы лишь необходимые:

Год
Квартал
Месяц
Номер месяца
Неделя

Так же, с признаком Not Visible используется поле Дата.

Структура таблицы Prod_table представлена в таблице 3.1.

Таблица 3.1.

Структура “Prod_table”

№	Наименование поля	Идентификатор поля	Тип поля	Прочее
1		client	nvarchar(255)
2		sku_code	bigint	PK, hashbytes
3		Product	nvarchar(255)
4		barcode	nvarchar(255)
5		sku_name	nvarchar(255)
6		Category	nvarchar(255)
7		category_pep	nvarchar(255)
8		sub_category	nvarchar(255)
9		brand_groupe	nvarchar(255)
10		brand	nvarchar(255)
11		sub_brand	nvarchar(255)
12		flavor	nvarchar(255)
13		pack	nvarchar(255)
14		pack_size	nvarchar(255)
15		fat	nvarchar(255)
16		volume	float
17	sku_code	sku_code_true	nvarchar(255)

Структура таблицы store_table представлена в таблице 3.2.

Таблица 3.2.

Структура “Store_table”

№	Наименование поля	Идентификатор поля	Тип поля	Прочее
1		chain	nvarchar(255)
2		client_id	bigint	PK, hashbytes
3		sap_id	nvarchar(255)
4		region	nvarchar(255)
5		area	nvarchar(255)
6		city	nvarchar(255)
7		outlet_address	nvarchar(255)
8		rsm	nvarchar(255)
9		tsm	nvarchar(255)
10	Route	dev_route	nvarchar(255)
11		format	nvarchar(255)
12		branch	nvarchar(255)
13		dc	nvarchar(255)
14	client_id	client_id_true	nvarchar(255)

Структура таблицы fact_table пердставлена в таблице 3.3.

Таблица 3.3.

Структура “Fact_table”

№	Наименование поля	Идентификатор поля	Тип поля	Прочее
1		date	datetime	PK
2		outlet_code	bigint	PK, hashbytes
3		sku_code	bigint	PK, hashbytes
4	Net Revenue	amount	float
5	Quantity	qty	float
6	Volume	vol	float
7		[vol\8oz]	float

Так же в структуре существуют расчетные поля – Количество точек, количество СКЮ, VPO. Код их рассчета в SSAS, ассоциирующий показатели в группу Fact Table представлен ниже.

CALCULATE;

CREATE MEMBER CURRENTCUBE.[Measures].[Av Price per QTY]

AS [Measures].[Net Revenue]/[Measures].[Quantity],

FORMAT_STRING = "# ##0,00;-# ##0,00",

NON_EMPTY_BEHAVIOR = { [Net Revenue] },

VISIBLE = 1 ;

CREATE MEMBER CURRENTCUBE.[Measures].[#Active Account]

AS sum(

filter(

EXISTING [Measures].[AA],

sum([Measures].[Quantity]) > 0)),

VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Fact Table';

CREATE MEMBER CURRENTCUBE.[Measures].[#SKU]

AS sum(

filter(

EXISTING [Measures].[SKU],

[Measures].[quantity] >0

)

VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Fact Table';

CREATE MEMBER CURRENTCUBE.[Measures].[VPO, Volume]

AS measures.volume/measures.[#Active Account],

VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[Measures].[VPO, NR]

AS measures.amount/measures.[#Active Account],

VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[Measures].[VPO, QTY]

AS MEASURES.quantity/measures.[#Active Account],

VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[Measures].[VPO, Vol8oz]

AS [Measures].[Vol 8oz]/measures.[#Active Account],

VISIBLE = 1;

3.3. Структурная схема пакета (дерево вызова программных модулей)

Т.к. интерфейс конечной ИС – Excel Pivot Table, то схема вызова програмных модулей отсутвует или может быть представлена в виде только одного блока «Excel Pivot Table».

Процесс работы с данной таблицей представляет собой «перетаскивание» необходимых полей в кросс-таблицу. Пример работы представлен на рисунках 3.3 и 3.4.

Рисунок 3.3. Работа с кубом

Рисунок 3.4. Работа с кубом

3.4. Описание программных модулей

На рисунке 3.5 представлена блок-схема.

Рисунок 3.5. Блок-схема задачи

Глава 4. Контрольный пример реализации проекта и его описание

В ходе выполненных работ был разработан OLAP Cube, выполняющий все необходимые задачи.

Проведем два теста, первый с заведомо недостающим мэппингом, второй корректный.

Для начала загружаем исходные данные на сетевой ресурс:

Рисунок 3.6. Данные на сетевом ресурсе

В ходе выполнения Job задачи на сервере (рисунок), мы получаем первые результаты

Рисунок 3.7. Успешное выполнение Job задачи

Результат первого теста представлен на рисунке 3.8.

Рисунок 3.8. Отбивка с ошибкой

Результат второго теста представлен на рисунке 3.9.

Рисунок 3.9. Отбивка об успешном выполнении

По результатам второго теста необходимые исходные данные полностью загружены в куб и доступны, контрольные цифры совпадают.

ЗАКЛЮЧЕНИЕ

В результате выполения курсовой работы была выполнена автоматизация аналитического учета на основе SellOut данных. Ранее данные хранились не структурировано и доступ к ним, а тем более расчеты выполнялись полностью вручную и это занимало огромное количество времени, к тому же людские ресурсы не позволяли в иерархии времени опускаться ниже одного месяца. Автоматизация этого процесса позволила сохранить большое количество рабочих часов, избавиться от человеческого фактора, выявлять ошибки заранее и получить конечному пользователю возможность быстро и «без хлопот» получать необходимые для себя данные по продажам клиентов отдела. К тому же данная система легко позволит при необходимости увеличить количество клиентов, регулировать количество продуктовых категорий и расширять возможности загрузки данных во временном интервале. Автоматический загрузчик настроен таким образом, что с легкостью может загрузить такое количество файлов единовременно, насколько это позволит дисковое пространство, так как на этом этапе не происходит финальной агрегации данных. И только в случае успешной загрузки файлов и полностью корректных данных начинается «слияние» с уже загруженными.