Modelo de Dados
O modelo simplificado utilizado possui 4 tabelas de dimensão, também conhecidas como lookups, e uma tabela de fatos que consolida dados sobre vendas, meta de vendas, lucro líquido, comissão e desconto. A modelagem utilizada é star schema. A figura 1.0 ilustra o modelo utilizado.
Populando a estrutura
Abaixo estão os scripts para preencher a estrutura de dados. Cada instrução encontra-se devidamente comentada.
----------------------------------------------------------------
--Criação das lookups - tabelas de DIMENSÃO
----------------------------------------------------------------
--Dimensão ESTADO
create table lk_estado(codigo integer identity,
descricao varchar(100))
alter table lk_estado add constraint pk_estado primary key (codigo)
go
--Dimensão LOJA
create table lk_loja(codigo integer identity,
descricao varchar(100))
alter table lk_loja add constraint pk_loja primary key (codigo)
go
--Dimensão VENDEDOR
create table lk_vendedor(codigo integer identity,
descricao varchar(100))
alter table lk_vendedor add constraint pk_vendedor primary key (codigo)
go
--Dimensão PERÍODO
create table lk_periodo(data_venda datetime not null)
alter table lk_periodo add constraint pk_periodo primary key (data_venda)
go
-----------------------------------------------------
--Criação da tabela de FATO
-----------------------------------------------------
create table ft_fato1(
loja integer not null,
estado integer not null,
vendedor integer not null,
datetime not null,
vendas decimal(12,2),
meta_vendas decimal(12,2),
lucro_liquido decimal(12,2),
comissao decimal(12,2),
desconto decimal(12,2)
)
alter table ft_fato1
add constraint pk_fato1 primary key (loja,estado,vendedor,data_venda)
go
alter table ft_fato1
add constraint fk_fato1_loja foreign key (loja)
references lk_loja(codigo)
go
alter table ft_fato1
add constraint fk_fato1_estado foreign key (estado)
references lk_estado(codigo)
go
alter table ft_fato1
add constraint fk_fato1_vendedor foreign key (vendedor)
references lk_vendedor(codigo)
go
alter table ft_fato1
add constraint fk_fato1_periodo foreign key (data_venda)
references lk_periodo(data_venda)
go
----------------------------------------------------------------
--Instruções DML para popular a base de dados
----------------------------------------------------------------
--Preenchendo a lookup de ESTADO
insert into lk_estado(descricao)
values ('Bahia')
insert into lk_estado(descricao)
values ('São Paulo')
insert into lk_estado(descricao)
values ('Rio de Janeiro')
insert into lk_estado(descricao)
values ('Minas Gerais')
--Preenchendo a lookup de LOJA
insert into lk_loja(descricao)
values ('XPTO - Cabula')
insert into lk_loja(descricao)
values ('XPTO - Iguatemi')
insert into lk_loja(descricao)
values ('XPTO - Pituba')
insert into lk_loja(descricao)
values ('XPTO - L. Freitas')
insert into lk_loja(descricao)
values ('XPTO - Vilas')
insert into lk_loja(descricao)
values ('XPTO - Brotas')
insert into lk_loja(descricao)
values ('XPTO - Itaigara')
insert into lk_loja(descricao)
values ('XPTO - Liberdade')
--Preenchendo a lookup de VENDEDOR
insert into lk_vendedor(descricao)
values ('Pedro Rocha')
insert into lk_vendedor(descricao)
values ('Sandra Rocha')
insert into lk_vendedor(descricao)
values ('Jayna Manezes')
insert into lk_vendedor(descricao)
values ('Izara Silva')
insert into lk_vendedor(descricao)
values ('Carmen Araújo')
--Preenchendo a lookup de PERÍODO
insert into lk_periodo(data_venda)
values ('05/01/2007')
insert into lk_periodo(data_venda)
values ('05/02/2007')
insert into lk_periodo(data_venda)
values ('05/03/2007')
insert into lk_periodo(data_venda)
values ('05/04/2007')
insert into lk_periodo(data_venda)
values ('05/05/2007')
--Preenchendo a FATO
insert into ft_fato1(loja,estado,vendedor,data_venda,vendas,
lucro_liquido,comissao,desconto,meta_vendas)
values (1,1,1,'05/01/2007',100.56,100.56,0,0,80)
insert into ft_fato1(loja,estado,vendedor,data_venda,vendas,
lucro_liquido,comissao,desconto,meta_vendas)
values (1,1,2,'05/02/2007',1400.00,1200.00,50,50,1300)
insert into ft_fato1(loja,estado,vendedor,data_venda,vendas,
lucro_liquido,comissao,desconto,meta_vendas)
values (1,3,1,'05/01/2007',100.56,100.56,0,0,80)
insert into ft_fato1(loja,estado,vendedor,data_venda,vendas,
lucro_liquido,comissao,desconto,meta_vendas)
values (1,3,2,'05/02/2007',1400.00,1200.00,50,50,1300)
insert into ft_fato1(loja,estado,vendedor,data_venda,vendas,
lucro_liquido,comissao,desconto,meta_vendas)
values (2,1,4,'05/03/2007',4100.00,3500.00,200,400,3000)
insert into ft_fato1(loja,estado,vendedor,data_venda,vendas,
lucro_liquido,comissao,desconto,meta_vendas)
values (3,2,3,'05/01/2007',50,50,0,0,120)
insert into ft_fato1(loja,estado,vendedor,data_venda,vendas,
lucro_liquido,comissao,desconto,meta_vendas)
values (4,4,4,'05/03/2007',4100.00,3500.00,200,400,6000)
insert into ft_fato1(loja,estado,vendedor,data_venda,vendas,
lucro_liquido,comissao,desconto,meta_vendas)
values (5,2,3,'05/01/2007',50,50,0,0,15)
----------------------------------------------------------------
----Queries para conferência
----------------------------------------------------------------
--Meta de Vendas e Vendas por ESTADO
select est.descricao as Estado,
sum(ft.meta_vendas) as [Meta de Vendas],
sum(ft.vendas) as Vendas
from ft_fato1 ft inner join
lk_estado est on
est.codigo = ft.estado inner join
lk_loja loj on
loj.codigo = ft.loja inner join
lk_vendedor vend on
vend.codigo = ft.vendedor
group by est.descricao
order by 1
--Meta de Vendas e Vendas por LOJA e VENDEDOR
select loj.descricao as Loja,
vend.descricao as Vendedor,
sum(ft.meta_vendas) as [Meta de Vendas],
sum(ft.vendas) as Vendas
from ft_fato1 ft inner join
lk_estado est on
est.codigo = ft.estado inner join
lk_loja loj on
loj.codigo = ft.loja inner join
lk_vendedor vend on
vend.codigo = ft.vendedor
group by loj.descricao,vend.descricao
order by 1
A figura 2.0 mostra a saída das queries de verificação.
Conclusão
Apresentamos a estrutura de banco de dados necessária para montar o nosso modelo no Analysis Services. Nos próximos artigos falaremos sobre a construção de objetos como: data source, data source view, lookups (dimensões), cubo juntamente com as métricas, métricas calculadas, utilização do browser, indicadores chave de desempenho (KPIs), partições (Agregações), perspectivas, traduções e utilização de consultas MDX no SQL Server Management Studio.
