Introdução ao Analysis Services 2005 – Parte 3

Veja o modelo de dados que será utilizado no exemplo Analysis Services.

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.

Artigos relacionados