Artigos Banco de Dados Introdução ao Analysis Services 2005 – Parte 3

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.

Modelo de Dados

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.

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.