Descrição do Mini-Mundo

UNIVERSIDADE FEDERAL DE PERNAMBUCO
CENTRO DE INFORMÁTICA
Banco de Dados Avançado
Banco de Dados Multidimensional
Alunos:
Antônio Carlos da Silva Júnior
Bruno Florêncio Pinheiro
Danilo Torres Ferreira
Rafael Barbosa Gonçalves
Vinicius Monteiro
Recife, 16 de dezembro de 2010
1. Conteúdo
a. Tema: Produto e Estoque.
b. Base: Adventure Works. Esquema (Production)
2. Descrição do Mini-Mundo
A base de dados contém informações sobre controle de estoque de produtos. Mantendo
informações importantes para gerenciamento do estoque tais como transações de entradas, saídas,
localização dos itens, saldos dos itens em determinado estoque, entre outras.
3. Definição das Entidades
TransactionType: define o tipo da movimentação do item no estoque. As movimentações podem
ser de três tipos:
 P = Ordem de compra.
 S = Ordem de venda.
 W = Ordem de serviço.
Location: define o estoque ao qual o produto pertence.
Product: contém os produtos utilizados para compra, venda e consumo interno.
ProductCategory: Define a categoria ao qual o produto pertence.
Time: Contém informações temporais. Com registros dentro do intervalo de tempo de ‘2003-0101’ à ‘2004-12-31’
ControleEstoque: contém informações sobre as movimentações de compra, saída e consumo
interno dos produtos no estoque.
4. Modelo Lógico da Base
Product (Production)
Column Name
ProductSubcategory (Production)
ProductSubcategoryID
ProductCategoryID
Name
rowguid
ModifiedDate
Data Type
ProductID
int
Name
Name:nvarchar(50)
ProductNumber
nvarchar(25)
MakeFlag
Flag:bit
FinishedGoodsFlag
Flag:bit
Color
nvarchar(15)
SafetyStockLevel
smallint
ReorderPoint
smallint
StandardCost
money
ListPrice
money
Size
nvarchar(5)
SizeUnitMeasureCode
nchar(3)
WeightUnitMeasureC...
nchar(3)
Weight
decimal(8, 2)
Allow Nulls
Time
PK_Date
Date_Name
ProductCategory (Production)
Column Name
Data Type
Year
Allow Nulls
ProductCategoryID
int
Name
Name:nvarchar(50)
rowguid
uniqueidentifier
ModifiedDate
datetime
Year_Name
Half_Year
Half_Year_Name
Trimester
Trimester_Name
Month
Month_Name
Day_Of_Year
Day_Of_Year_Name
Day_Of_Half_Year
Day_Of_Half_Year_Name
ControleEstoque
TempoId
TransactionType
QuantityInventory
Column Name
LocationId
Data Type
QuantityTransaction
TransactionTypeId
nchar(1)
ProductId
Name
nchar(1)
TransactionTypeId
CostTransaction
Location (Production)
Column Name
Data Type
LocationID
smallint
Name
Name:nvarchar(50)
CostRate
smallmoney
Availability
decimal(8, 2)
ModifiedDate
datetime
Allow Nulls
Allow Nulls
5.
Modelo Multidimensional
Esquema Estrela
Escolhemos a tabela TransactionHistory como sendo a base para modelarmos a tabela de fatos,
pois ela apresenta as informações mais relevantes (entradas, saídas e consumo interno) para o controle
dos produtos no estoque. Escolhemos este modelo pensando em obter resultados sobre o fluxo de
entradas e saídas de produtos por estoque em determinados períodos, além de considerar possível uma
análise histórica do saldo e valor dos itens. Com essa escolha, aumentamos a flexibilidade das análises
construídas pelo usuário. Seus fatos (QuantityInventory, QuantityTransaction e CostTransaction) são
carregados a partir dos dados das tabelas dimensionais. Nossa tabela de fatos (ControleEstoque)
armazenará grande quantidade de dados históricos, em função do tempo, obtidos a partir da
intersecção de todas as dimensões (tabelas auxiliares) .
Para a dimensão tempo, selecionamos a granularidade diária, mensal, semestral e anual para que
os dados contidos no sistema sejam melhores analisados. Aos elementos de tempo será atribuído um
valor chave por dia para agregar uma hierarquia na dimensão tempo.
6. Scripts de consulta
A seguir, algumas consultas em MDX utilizadas para acessar o cubo e gerar relatórios e informações
importantes.
1) Quantidade e Fatura das Vendas por Categoria e Mês. Permite analisar quais são as
categorias de produto que estão trazendo maior saída e faturamento.
with member [Measures].[Data] as ( [Time].[Month].CURRENTMEMBER )
Cell Calculation [NullFormatCostTransactio] for '({[Measures].[Cost
Transaction]})' as [Measures].[Cost Transaction], FORMAT_STRING =
"#,#;;;\0"
Cell Calculation [NullFormatQuantity] for '({[Measures].[Quantity
Transaction]})' as [Measures].[Quantity Transaction], FORMAT_STRING =
"#,#;;;\0"
select ( {Order ([Time].[Month].children, [Measures].[Data], BASC)}
,{ [Measures].[Quantity Transaction], [Measures].[Cost Transaction]
}) ON COLUMNS
, [Product].[Product ID].children ON ROWS from [CUBO_BDA] where
[Transaction Type].[Name].[S]
2) Os 10 meses que obtiveram maior faturamento das vendas. Com essa consulta podemos
saber quais são os meses que tendem a ter uma maior perspectiva de faturamento.
With
Cell Calculation [NullFormatCostTransactio] for '({[Measures].[Cost
Transaction]})' as [Measures].[Cost Transaction], FORMAT_STRING =
"#,#;;;\0"
SELECT {[Measures].[Cost Transaction]} ON COLUMNS,
TOPCOUNT([Time].[Month].children, 10, [Measures].[Cost Transaction])
ON ROWS
FROM [CUBO_BDA] where [Transaction Type].[Name].[S]
3) Produtos por estoque. Utilizando-se desta query podemos saber em quais estoques estão
localizados determinados produtos.
With Member [Measures].[Pertence] as
IIF([Measures].[Quantity Transaction] > 0,1, 0)
select non empty {[Location].[Name].children} on Columns,
{[Product].[Product Id].children} on Rows from [CUBO_BDA]
Where [Measures].[Pertence]
4) O Histórico de quantidade em estoque de determinado produto por dias de movimentações
do item. Com essa medida podemos analisar o fluxo de saída e entrada de determinado
produto.
SELECT non empty ORDER( {[Time].[Date].children},[Measures].[Quantity
Inventory],BASC) on COLUMNS,
{[Product].[Product ID].&[1]} ON ROWS
FROM [CUBO_BDA]
where [Measures].[Quantity Inventory]
5) Total do Custo das Compras do Ultimo semestre por categoria.
With
Cell Calculation [NullFormatCostTransactio] for '({[Measures].[Cost
Transaction]})' as [Measures].[Cost Transaction], FORMAT_STRING =
"#,#;;;\0"
SELECT [Measures].[Cost Transaction] ON COLUMNS,
Order (crossjoin ([Time].[Half Year].LASTCHILD,
[Location].[Name].members), [Measures].[Cost Transaction], BDESC) ON
ROWS
FROM [CUBO_BDA] where [Transaction Type].[P]
6) Ranking dos produtos que mais tiveram transações de estoque.
WITH SET [TransacoesCategoria] AS ORDER
([Product].[Product ID].children,[Measures].[Quantity Transaction],
BDESC)
MEMBER [Measures].[Rank] AS
RANK ([Product].[Product ID].CurrentMember,[TransacoesCategoria])
SELECT
{[Measures].[Rank],[Measures].[Quantity Transaction] }
ON 0,[TransacoesCategoria] ON 1
FROM [CUBO_BDA]
7) Média mensal de compra dos produtos. Permite estimar o custo médio que cada produto
possui por mês.
WITH MEMBER Measures.ValorMedio AS Avg
([Time].[Month].children,[Measures].[Quantity Transaction])
SELECT Measures.ValorMedio ON COLUMNS,[Product].[Product ID].CHILDREN
ON ROWS
FROM [CUBO_BDA]
where [Transaction Type].[P]
8) Porcentagem das Venda por Categoria de produto. Permite criar um gráfico de pizza das
vendas pela categoria do produto. Ficando assim fácil de verificar quais são as categorias de
produtos que possuem maiores rentabilidade.
WITH MEMBER Measures.ValorPercentual AS
( [Product].[Product ID].CURRENTMEMBER ,[Measures].[Cost Transaction])
/
( [Product].[Product ID].CURRENTMEMBER.Parent,[Measures].[Cost
Transaction])
* 100
SELECT Measures.ValorPercentual ON COLUMNS,
[Product].[Product ID].CHILDREN ON ROWS
FROM [CUBO_BDA]
9) Vendas, compras e consumo interno do Verão de 2004.
WITH MEMBER Measures.VALORES_DE_VERAO AS SUM
( { [Time].[Month].&[2003-12-01T00:00:00]:
[Time].[Month].&[2004-03-01T00:00:00]
}, [Measures].[Cost Transaction])
SELECT
Measures.VALORES_DE_VERAO ON COLUMNS,
[Transaction Type].[Name].CHILDREN ON ROWS
FROM [CUBO_BDA]
10) Dos 10 produtos mais vendidos mostrando sua Localização em estoque.
SELECT [Measures].[Quantity Transaction]
ON COLUMNS,
TOPCOUNT( NonEmptyCrossjoin
({[Location].[Name].children},{[Product].[Product ID].children}), 10,
[Measures].[Quantity Transaction])
ON ROWS
FROM [CUBO_BDA]
WHERE ([Transaction Type].[Name].[S])
11) Mostra as vendas, compras e consumos interno dos produtos: “ Bike Wash – Dissolver”,
“Classic Vest, M” e “Adjustable Race” no ano de 2003.
SELECT
{[Product].[Name].[Bike Wash - Dissolver],
[Product].[Name].[Classic Vest, M], [Product].[Name].&[Adjustable
Race]} ON COLUMNS,
{([Transaction Type].[Name].children)}ON ROWS
FROM [CUBO_IDW]
WHERE ([Measures].[Quantity Transaction], [Time].[Year].&[2003-0101T00:00:00])
12) Mostra os produtos que somados em ordem decrescente representam 80% das vendas.
SELECT [Measures].[Cost Transaction]
ON 0,
TopPercent( {[Product].[Name].children}, 80, [Measures].[Cost
Transaction])
ON 1
FROM [CUBO_IDW]
WHERE ([Transaction Type].[Name].[S])
13) Mostra em ordem decrescente o menor conjunto de produtos que somados representam
15mil vendas
SELECT [Measures].[Quantity Transaction]
ON 0,
TopSum( {[Product].[Product ID].children}, 15000, [Measures].[Quantity
Transaction])
ON 1
FROM [CUBO_BDA]
WHERE ([Transaction Type].[Name].[S])
14) Relatório anual das transações (vendas e compras) realizadas.
SELECT (Descendants ( [Time].[Year]), [Transaction
Type].[Name].children) ON COLUMNS,
{[Product].[Product ID].children} ON ROWS
FROM [CUBO_BDA]
WHERE [Measures].[Cost Transaction]
15) Maior movimentação de venda do item em determinado mês do ano.
WITH MEMBER MaiorVendaProdutoMes AS Max
([Time].[Month].&[2003-12-01T00:00:00],[Measures].[Quantity
Transaction] )
SELECT (MaiorVendaProdutoMes) ON 0,
NON EMPTY [Product].[Product ID].children
ON 1 FROM [Cubo_BDA] WHERE [Transaction Type].[Name].[S];
16) Quantidade de vendas a cada trimestre.
SELECT { DrillDownLevel( { [Time].[Trimester], [Trimester]})}ON
COLUMNS
,{ [Measures].[Quantity Transaction] } ON ROWS
FROM [CUBO_BDA]
WHERE ([Transaction Type].[Name].[S])
17) Lista os produtos e informa sua SubCategoria
With Member [Measures].[SubCategoria] as
'[Product].[Product].CurrentMember.Properties("Product SubCategory
ID")'
SELECT
NON EMPTY {[Measures].[SubCategoria] } ON
COLUMNS,
[Product].[Product ID].CHILDREN ON ROWS
FROM [CUBO_BDA]
18) Quantidade de movimentação em dinheiro do estoque específico.
SELECT { DrillDownMember( { [Location].[Name] } ,
{ [Location].[Name].firstchild} ) } ON COLUMNS
,{ ([Product].[Product ID].Members) } ON ROWS
FROM [CUBO_BDA]
WHERE [Measures].[Cost Transaction];
7. Ferramentas Utilizadas
Todo o desenvolvimento do projeto, desde a construção do cubo até as consultas em
MDX, foi feito com o uso do SQL Server 2008. Utilizamos a ferramenta Microsoft Analysis
Services, que trata-se de um conjunto de serviços desenvolvido pela Microsoft para
gerenciar dados em um Data Warehouse ou Data Mart, para organização das fontes de dados em
cubo e definição das dimensões de busca. Para elaboração das consulta MDX utilizamos a
ferramenta SQL Management Studio que fornece um ambiente para gerenciar e trabalhar com
os modelos de mineração de dados e cubos OLAP que já existem em um banco de dados do
Microsoft SQL Server Analysis Services.
8. Considerações Finais
Neste documento, demonstramos um estudo da criação de uma Data Warehouse com
o propósito de dar suporte à tomada de decisão. Utilizamos o Banco de Dados
AdventureWorks, dando um foco específico nos dados de controle de estoque. A base de
dados encontrava-se modelada segundo o modelo relacional. Remodelamos esta base usando
uma modelagem multi-dimensional (DW), seguindo o modelo estrela e usando 5 dimensões:
TransactionType (Tipo da Transação), Product (Produto), Location (Estoque) e Time (Tempo).