Business Intelligence com o microsoft sql server

BI com MS SQL Server

1. SQL Server 2005
Implementando uma solução Business Intelligence com o
Microsoft SQL Server 2005 – Parte 1
Modelagem Multidimensional, Data Mart, ETL, Cubo Olap e Visualização de
dados com o Excel 2007

Atualmente, a necessidade de cruzar informações para realizar uma gestão
empresarial eficiente é uma realidade vivenciada pelo mercado, fazendo com que
as empresas não adiem decisões importantes relacionadas diretamente ao seu
negócio. O interesse pelo Business Intelligence (BI) vem crescendo na medida em
que seu emprego possibilita às organizações realizarem uma série de análises e
projeções, de forma a agilizar os processos relacionados às tomadas de decisão.
Resumindo, BI é um conjunto de ferramentas e metodologias para gestão do
negócio que tem como objetivo final auxiliar os responsáveis para tomada de
decisões através da análise das informações internas e externas à empresa. O BI é
composto de um conjunto de processos, conceitos e tecnologias, os quai s serão
abordados e explicados no decorrer deste artigo.

Sistemas OLTP x Sistemas OLAP
Os sistemas OLTP (On-Line Transaction Processing) são sistemas que armazenam
as transações de um negócio e as colocam em estruturas relacionais, seguindo um
padrão baseado nas Formas Normais de bancos de dados relacionais. As principais
características dos sistemas OLTP são:
 Realizar transações em tempo real e, desta forma, os dados armazenados
mudam constantemente;
 São os responsáveis pela manutenção dos dados, realizando inclusões,
atualizações e exclusões dos dados;
 As estruturas dos dados devem estar otimizadas para validar a entrada dos
mesmos e rejeitá-los se não atenderem a determinadas regras de negócio;
 Para a tomada de decisões, possuem capacidades limitadas, pois não é seu
objetivo principal. Caso seja necessário obter uma informação histórica
poderá ocorrer uma sobrecarga no sistema, devido à necessidade de
execução de consultas SQL complexas.

Como exemplo de sistema OLTP pode-se citar sistemas de Vendas, de Folha de
Pagamento, de controle Acadêmico, dentre outros.
Os sistemas OLAP (On-Line Analytical Processing) oferecem uma alternativa aos
sistemas transacionais, produzindo uma visão dos dados orientada à aná lise, além
de uma navegação rápida e flexível. Um sistema OLAP apresenta as seguintes
características:
 Esquema otimizado para que as consultas realizadas pelos usuários sejam
retornadas rapidamente;
 Geração de relatórios complexos de uma forma simples;
 Utilização interativa com os usuários, ou seja, as consultas não necessitam
estar pré-definidas;
 Permite a redundância de dados para otimização das consultas.

Pode-se tomar como exemplo de um sistema OLAP a construção de um Data Mart
gerado a partir de um Sistema OLTP de Vendas.

Conceitos de Data Warehouse e Data Mart
Pode-se definir um Data Warehouse como uma coleção de dados orientados por
assuntos, integrados, variáveis com o tempo e não voláteis, com o objetivo de dar
suporte ao processo de tomada de decisão.
Um Data Mart é um armazém de dados com informações de interesse particular
para um determinado setor da empresa. Desta forma, um Data Mart é um pequeno

2. Data Warehouse que fornece suporte à tomada de decisão para uma determinada
área de negócio, como, áreas de vendas, compras, estoque ou recursos humanos.
Alguns autores possuem visões diferentes na hora de definir uma estratégia para a
construção de um armazém de dados. Para Bill Inmon, uma empresa inicia um
projeto com a construção de um Data Warehouse, de onde os Data Marts extraem
suas informações. Mas para Ralph Kimball, um Data Warehouse inicia-se com a
criação de Data Marts, que juntos irão formar o Data Warehouse da empresa. Neste
artigo será abordada a filosofia de Kimball.
Na prática muitos projetos iniciam-se com a construção do Data Mart pois, neste
caso, é possível obter um resultado mais rápido devido ao menor escopo do projeto
(atende apenas a uma determinada área de negócio).

Modelagem Multidimensional
Os sistemas de base de dados tradicionais utilizam a normalização para garantir a
consistência dos dados e uma minimização do espaço de armazenamento
necessário.
Entretanto, algumas transações e consultas em bases de dados norma lizadas
podem se tornar lentas devido às operações de junção entre as tabelas envolvidas.
Um Data Warehouse ou Data Mart utiliza normalmente dados em formato
desnormalizados. Isto aumenta o desempenho das consultas e, como benefício
adicional, o processo torna-se mais intuitivo para os usuários finais.
O modelo multidimensional é baseado em três tipos de tabelas:
 Fatos;
 Dimensões;
 Medidas.

Tabela de Fatos
A tabela de Fatos é a tabela central do modelo e contém os valores do negócio que
se deseja analisar, geralmente possui um grande volume de dados. A tabela Fato
possui chaves externas (estrangeiras), que se relacionam com suas respectivas
tabelas de dimensões, e campos numéricos que são os valores (medidas) que serão
analisados.

Tabelas de Dimensão
As tabelas de dimensões representam um aspecto do negócio que está sendo
analisado. Cada dimensão é definida pela sua chave primária, que serve para
manter a integridade referencial na tabela Fato à qual está relacionada. Uma
dimensão oferece ao usuário um grande número de combinações e interseções para
analisar os dados. As tabelas de Dimensões podem ser implementadas de duas
maneiras (ver Figura 1):

 Esquema Estrela: Cada dimensão será formada por apenas uma tabela
não padronizada. Pelo fato das tabelas não estarem normalizadas, isto
resultará em uma menor quantidade de tabelas no modelo do Data Mart,
mas como conseqüência poderá causar redundância dos dados, fato
característico neste tipo de aplicação;
 Esquema Floco de Neve : Neste esquema, as tabelas da dimensão estão
padronizadas para eliminar a redundância de dados. Diferente do esquema
Estrela, neste esquema os dados das dimensões estão distribuídos em
múltiplas tabelas.

3. Figura 1. Exemplo de tabela Não Padronizada e tabela Padronizada

Em relação à estrutura das dimensões, estas possuem níveis, onde cada nível de
uma dimensão deve ter correspondência com uma coluna na tabela da dimensão.
Os níveis são ordenados por grau de detalhe e são organizados em uma estrutura
hierárquica (ver Figura 2).


Figura 2. Estrutura hierárquica de uma dimensão

Neste exemplo, a dimensão Produto possui três níveis. O nível mais alto
hierarquicamente é representado pela Categoria, em seguida tem -se o nível
Subcategoria, ligado hierarquicamente a Categoria, e o nível Produto, subordinado
ao nível Subcategoria.
Na prática, isso significa que se pode detalhar a consulta de determinada medida
(por exemplo, quantidade de vendas) de acordo com o nível desejado.

Medidas
Uma Medida é um atributo que qualifica um determinado fato, representando o
desempenho de um indicador em relação às dimensões que participam do fato. O
contexto de uma medida é determinado em função das dimensões que participam
do fato.
Por exemplo, quando a implementação do estudo de caso deste artigo estiver
concluída, será possível consultar a quantidade de vendas (medida contida na
tabela fato) por Loja, Vendedor ou Cidade (dimensões do modelo que
representam um aspecto do negócio).
Será possível, também, incluir a dimensão Tempo, definindo o período para o qual
se pretende realizar a análise. Por exemplo, perguntas do tipo: “Qual o número de
vendas por vendedor em determinada loja e em um determinado período?” poderão
ser respondidas.

Estudo de Caso
Este artigo irá demonstrar o desenvolvimento de uma solução de BI que terá como
base a área de negócios de uma rede de lojas com filiais distribuídas em vários
estados e cidades do Brasil. Estas lojas vendem diversos produtos que são
classificados por categoria e subcategoria. Cada loja possui uma equipe de

4. vendedores responsáveis pelas vendas dos produtos. A Figura 3 apresenta o
Modelo Relacional do banco de dados de produção “dbVendas”.


Figura 3. Visualização do Modelo Relacional do banco de dados de produção
“dbVendas”

Nota: Para obter o banco de dados “dbVendas” já populado, é necessário fazer o
download do backup “dbVendas.bak” no site da revista SQLMagazine.

Os requisitos levantados nesta etapa provêm de entrevistas realizadas com os
usuários finais que irão utilizar a ferramenta. Por se tratar de um estudo de caso de
uma área específica, ou seja, o setor de vendas da empresa, será desenvolvido um
Data Mart. De acordo com as necessidades levantadas, é importante para empresa
obter as seguintes informações:

 A quantidade de unidades vendidas por estados e cidades onde existem
lojas da rede: pode-se destacar como possível medida a quantidade de
unidades vendidas, exibidas em detalhes por Estado, Cidade e Loja,
assim, deve-se criar a dimensão Loja com os níveis Estado, Cidade e Loja.
Por outro lado, a quantidade de unidades vendidas refere-se aos produtos,
detecta-se então uma nova dimensão, a dimensão Produto ;
 O valor de vendas por produto: identifica-se aqui uma nova medida,
valor de vendas por produto, sabendo que será utilizada a dimensão Produto
para obter o valor das vendas de cada Produto;
 Verificar o perfil de produtos vendidos em cada cidade em um determinado
período: para isso, é necessário tratar das vendas realizadas por categoria
de produto por cidade e por ano, mês e dia. Verifica-se que é necessário
analisar os produtos de acordo com a sua categoria, agrupando por

5. Categoria e Subcategoria, definindo mais dois níveis na dimensão
Produto;
 Premiar anualmente os vendedores que ultrapassem as metas de vendas
atribuídas. A análise, neste caso, deverá incluir os vendedores e as vendas
realizadas por mês para o ano fiscal: sobre este requisito, deve-se
acrescentar a dimensão Vendedor, e as medidas utilizadas serão as
mesmas destacadas anteriormente. Pode ser muito útil acrescentar o nível
Loja na dimensão Vendedor para facilitar na hora da pesquisa. Para premiar
os vendedores que ultrapassarem a meta estipulada deve -se implementar
uma KPI (Key Performance Indicators). KPIs são indicadores de desempenho
que fornecem a capacidade de definir gráficos e métricas customizáveis de
negócio. Um KPI é representado por um símbolo gráfico que assume
determinado estado de acordo com a programação realizada;
 O Data Mart contém informação histórica que a empresa analisará para
diferentes períodos e, desta forma, deve-se acrescentar mais uma dimensão
denominada Tempo. Quanto à granularidade, os dados deverão ser
atualizados diariamente. Como os valores da dimensão Tempo são
previamente conhecidos, ou seja, sabe-se o período que se deseja analisar,
ela pode ser tratada de maneira diferente, sendo alimentada previamente
com todos os dias do ano;
 Também será necessário exibir a informação completa sobre as vend as,
detalhando o Endereço completo da Loja e o Vendedor responsável pela
venda. Uma forma de exibir estas informações é através da criação de uma
consulta Drillthrough. Drillthrough é uma consulta que retorna uma
informação fixa, previamente definida pelo usuário;
 Em relação aos campos do Data Mart, a regra de negócio definida é que
todos os dados devem ser armazenados historicamente caso ocorra
alteração no banco de produção (OLTP). Como será necessário armazenar as
informações historicamente, será necessário criar um campo para conter o
status atual do registro (Corrente/Expirado) em cada tabela de cada
dimensão.

Construindo o Data Mart Vendas
Nesta etapa, devem-se modelar as tabelas relacionais em uma estrutura não
padronizada. Para isso, é preciso organizar os dados em uma estrutura formada por
uma tabela central chamada tabela de Fatos, e um conjunto de tabelas organizadas
ao redor dela, as tabelas de Dimensões. Este design das tabelas servirá de
estrutura para a posterior montagem do cubo OLAP, que será explicado no decorrer
do artigo.
Em relação à estrutura das dimensões, estas possuem níveis, onde cada nível de
uma dimensão deve ter correspondência com uma coluna na tabela da dimensão.
Os níveis são ordenados por grau de detalhe e são organizados em uma estrutura
hierárquica.
A definição da estrutura hierárquica do estudo de caso será realizada levando em
consideração as necessidades apresentadas pelo usuário final, como os períodos de
tempo pelos quais as informações precisam ser analisadas e pelo grau de detalhe
que o usuário necessita visualizar estas informações.
Para representar todas as dimensões do Data Mart, será utilizado o Esquema
Estrela. A solução atenderá à área de vendas de uma empresa, neste caso, o Data
Mart é a opção indicada. Outra regra que se deve adotar ao se criar um banco de
dados no Modelo Multidimensional é a utilização de chaves substitutas conhecidas
como Surrogate Keys. Segundo Ralph Kimball, “Surrogate Key é uma chave
artificial ou sintética que é usada como chave substituta de uma chave natural”.
Desta forma, as chaves do Data Mart ficam independentes das chaves do banco de
dados de produção (ver Figura 4).

6. Figura 4. Visualização da estrutura hierárquica do Data Mart

Na Figura 5 pode-se visualizar o modelo físico do Data Mart de acordo com os
requisitos levantados no estudo de caso.


Figura 5. Visualização do modelo relacional do banco de dados do Data Mart
“dmVendas”

Nota: Para obter o banco de dados “dmVendas”, é necessário fazer o download do
backup “dmVendas.bak” no site da revista SQLMagazine.

De acordo com o modelo da Figura 5, temos as seguintes tabelas:

dim_Loja:

7. Esta tabela registra informações sobre a Loja que poderão ser utilizadas na análise
dos dados (ver Tabela 1).

Chave Campo Descrição
PK id_loja Campo auto-incremento que representa a surrogate key
da dimensão Loja. Substitui a chave original cod_loja.
cod_loja Campo chave primária no modelo relacional. Na
modelagem multidimensional, é conhecido como
Businnes key ou chave de negócio.
nome_loja
Campos incluídos na dimensão para atender aos
requisitos de informações levantados junto ao usuário
final.
endereco
cidade
estado
status_loja Campo para armazenar o status do registro, ou seja, se
o registro é atual ou não.
Tabela 1. Estrutura da tabela dim_Loja

dim_Produto:
Esta tabela registra informações sobre o Produto que poderão ser utilizadas na
análise dos dados (ver Tabela 2).

Chave Campo Descrição
PK id_produto Campo auto-incremento que representa a
surrogate key da dimensão Produto. Substitui
a chave original cod_produto.
cod_produto Campo chave primária no modelo relacional.
desc_produto
Campos incluídos na dimensão para atender
aos requisitos de informações levantados junto
ao usuário final.
cod_categoria
desc_categoria
cod_subcategoria
desc_subcategoria
status_produto Campo para armazenar o status do registro,
ou seja, se o registro é atual ou não.
Tabela 2. Estrutura da tabela dim_Produto

dim_Vendedor :

Esta tabela registra informações sobre o Vendedor que poderão ser utilizadas na
análise dos dados (ver Tabela 3).

Chave Campo Descrição
PK id_vendedor Campo auto-incremento que representa a
surrogate key da dimensão Loja. Substitui a
chave original cod_loja.
cod_vendedor Campo chave primária no modelo relacional.
nome_vendedor Campos incluídos na dimensão para atender
aos requisitos de informações levantados junto
ao usuário final.
cod_loja
nome_loja
status_loja Campo para armazenar o status do registro, ou
seja, se o registro é atual ou não.
Tabela 3. Estrutura da tabela dim_Vendedor

dim_Tempo:

8. Esta tabela registra informações sobre datas que poderão ser utilizadas na análise
dos dados, permitindo a aplicação de filtros de períodos (ver Tabela 4).

Chave Campo Descrição
PK id_data Campo auto-incremento que representa a
surrogate key da dimensão Tempo.
data Campo para representar a data no formato
dd/mm/aaaa.
ano
Campos incluídos para atender a necessidade de
visualização das informações por ano, mês e dia.
mes
dia
Tabela 4. Estrutura da tabela dim_Tempo

fact_Venda:

Esta tabela registra as transações do negócio que serão analisadas. Cada registro
informa o produto vendido, a quantidade vendida, o valor da venda, o vendedor
que efetuou a venda e a loja a qual o vendedor pertence (ver Tabela 5).

Chaves Campo Descrição
FK id_data Chave estrangeira utilizada para fazer ligação com a
tabela dim_Tempo.
FK id_produto Chave estrangeira utilizada para fazer ligação com a
tabela dim_produto.
FK id_vendedor Chave estrangeira utilizada para fazer ligação com a
tabela dim_Vendedor.
FK id_loja Chave estrangeira utilizada para fazer ligação com a
tabela dim_Loja.
quantidade Medida usada para registrar a quantidade vendida
do produto.
valor_venda Medida usada para registrar o valor da venda do
produto.
Tabela 5. Estrutura da tabela fact_Venda

ETL (Extract, Transform and Load - Extração, Transformação e Carga)
Este processo é responsável por ler os dados dos bancos de dados de origem,
tratar, limpar, transformar e carregar os dados no Data Mart. Estas etapas serão
vistas passo a passo no decorrer deste artigo na implementação do ETL do estudo
de caso.
O ETL é uma das fases mais críticas e complexas de um Data Mart, pois os dados
que o alimentam são geralmente resultantes de diferentes sistemas OLTP, sendo
assim, torna-se necessário realizar todas as adaptações pertinentes, tais como:

 Codificação: dados podem ser codificados de diferentes maneiras nos
sistemas de origem, por exemplo, a codificação da descrição do sexo de
uma pessoa pode ser encontrada como “M” e “F”, “1” e ”0”, ou “Masculino” e
“Feminino”. Na etapa de transformação, deverá ser escolhida uma única
convenção para alimentar o Data Mart;
 Formatos: formatos de datas seguindo padrões regionais. As datas podem
estar armazenadas como “dd/mm/aaaa”, “mm/dd/aaaa” ou “aaaa/mm/dd”.
Na hora de alimentar o Data Mart, deve-se definir com qual formato irá se
trabalhar;
 Unidades de medida: os dados armazenados podem apresentar diferentes
unidades de medidas. Um exempl o é uma medida em litros ou centímetros
cúbicos, assim, deve-se escolher uma única unidade de medida que seja útil
para o Data Mart e transformar os dados;

9.  Várias colunas para uma: os dados de endereço, por exemplo,
geralmente estão armazenados em vários campos nos bancos de dados
relacionais. Ao transformar estes dados para o Data Mart, é possível
armazená-los em um único campo, facilitando a sua visualização por parte
do usuário, desde que não comprometa as consultas a serem realizadas;
 Uma coluna para várias: é possível, por exemplo, que seja necessário
colocar o nome de uma pessoa em um campo e o seu sobrenome em outro
campo, ao transformar os dados para o Data Mart.

A ferramenta do SQL Server 2005 responsável pelo ETL é o Microsoft SQL Server
Integration Services (SSIS). O SSIS substitui o SQL Server 2000 Data
Transformation Services, e oferece novos recursos para importação, exportação e
transformação de dados, e o desempenho necessário para atender a todas as
etapas de um processo de ETL. Para acessar o SSIS, basta ir à opção Iniciar
Programas>Microsoft SQL Server 2005>SQL Server Business Intelligence
Development Studio e criar um novo projeto do tipo Integration Services Project.
O novo processo de criação de pacotes no SSIS é separado em Control Flow e Data
Flow. Onde o controle de fluxo de dados é feito pela aba Control Flow e toda a parte
de importação, exportação e transformação dos dados são feitas através da aba
Data Flow. A seguir são apresentadas as principais características deste novo
processo (ver Figura 6):

 Control Flow: coordena a lógica do fluxo do processo de trabalhos de um
pacote. Cada pacote tem exatamente um fluxo de controle principal, tenha
ele uma etapa simples ou várias tarefas interligadas. As tarefas dentro do
fluxo de controle estão interligadas por fluxos de sucesso, falha e conclusão.
Nesta aba são incluídos os componentes do Control Flow Items, que são as
tarefas mais gerais de um pacote, ou seja, tarefas para copiar um arquivo,
enviar um email, fazer um FTP ou executar um comando SQL;
 Data Flow: mecanismo de processamento de dados que trata da
movimentação dos dados, da lógica de transformação, da organização dos
dados e da extração e confirmação dos dados para origens e destinos e vice -
versa, do componente Data Flow Task. O componente Data Flow Task é
responsável pela tarefa de transformação, limpeza e modificação de um
conjunto de dados de uma ou mais origens para um ou mais destinos, e isto
é realizado através da utilização dos componentes do Data Flow Elements.


Figura 6. Visualização de um projeto no SSIS

10. Criando o pacote ETL_Vendas no SSIS
Para começar a desenvolver o ETL, primeiramente deve-se criar um novo projeto
no SSIS. Para isso, é preciso abrir a ferramenta Business Intelligence Development
Studio do SQL Server 2005, clicar no meu File>New>Project, selecionar Integration
Services Project em Templates, renomear para “ETL_Vendas” no campo Name e
clicar em Ok.
Feito isto, o próximo passo será criar as conexões com os bancos de dados
necessários para o projeto, devendo-se clicar com o botão direito na aba
Connection Managers, selecionar New OLE DB Connection e configurar duas novas
conexões de dados, uma para o banco de dados de produção “dbVendas” (ver
Figura 7) e outra para o banco “dmVendas” (banco de dados do Data Mart). Para
configurar a conexão com o banco “dmVendas”, basta selecioná -lo em Select or
enter a database name.
Ainda na aba Connection Managers, será criada uma conexão para o envio de e-
mails para demonstrar esta funcionalidade e, para isso, deve-se selecionar New
Connection e, na janela Add SSIS Connection Manager, selecionar SMTP e clicar no
botão Add, informando um servidor SMTP de envio de e-mails (ver Figura 8).


Figura 7. Visualização da janela para configurar conexão com banco de dados
“dbVendas”

11. Figura 8. Visualização da janela para configurar uma conexão SMTP

Adicionando a dimensão Loja
Para implementar esta primeira dimensão as seguintes características deverão ser
contempladas:
 Deve-se importar os dados de todas as Lojas no banco de dados de
produção “dbVendas”;
 Exibir o Endereço das Lojas em uma única coluna, visando facilitar a
visualização dos dados da consulta Drillthrough que será implementada no
decorrer deste artigo;
 Manter historicamente as mudanças sofridas pelas colunas da dimensão
Loja. Por exemplo, caso ocorra alguma alteração no endereço da loja, a
informação anterior será preservada e um novo registro será adicionado
com a nova informação.

Como primeiro passo, será necessário adicionar um componente Data Flow Task na
aba Control Flow do projeto “ETL_Vendas” criado anteriormente, com o nome “DFT
dim_Loja”, e mudar para a aba Data Flow desta tarefa para que se possa adicionar
os componentes de transformação dos dados. Agora, na aba Data Flow do “DFT
dim_Loja” deve-se adicionar o componente OLE DB Source com o nome “OLE_SRC
Tabela Loja” e configurá-lo para importar os dados das Lojas utilizando a instrução
SQL descrita na Tabela 6. Este componente é responsável pela criação de uma
conexão com uma base de dados qualquer, podendo gerenciar esta conexão
através de um data source, data source view ou do uso de uma instrução SQL.
Ao utilizar a opção data source, os dados serão retornados diretamente de uma
base de dados. Na opção data source view, os dados serão retornados a partir de
uma view do banco de dados. Já na opção instrução SQL, pode-se montar uma
consulta para retornar os dados desejados.

Propriedade Valor
OLE DB Connection manager localhost.dbVendas
Data access mode Sql command
Sql command text select * from loja
Tabela 6. Configuração do OLE DB Source Editor do “OLE_SRC Tabela Loja”

Outro tratamento que deverá ser feito antes de importar os dados para a tabela
“dim_Loja” será a concatenação dos campos de endereço para um único campo,

12. visando facilitar a visualização dos dados pelo usuário final. Para isso, será
adicionado o componente Derived Column com o nome “DER Coluna Endereço” ,
conectá-lo ao fluxo de dados do componente anterior e configurá-lo para
concatenar os campos (ver Tabela 7).
O componente Derived Column é responsável pela transformação de valores de
colunas aplicando-se expressões de transformação. Uma expressão pode conter
qualquer combinação de colunas a partir da transformação de entrada, variáveis,
funções e operadores. O resultado pode ser adicionado como uma nova coluna ou
inserido substituindo o valor de uma coluna existente. Neste exemplo, na
propriedade Expression será implementada a expressão para a concatenação dos
campos, onde será preciso utilizar a função (DT_STR, «length», «code_page») para
adicionar caracteres, como, a palavra “CEP: “ antes da coluna “cep”, também será
necessário definir o tamanho desta nova coluna na propriedade Length.

Propriedade Valor
Derived Column Name Endereco
Derived Column <add as new column>
Expression logradouro + (DT_STR,2,1252)(", ") + numero +
(DT_STR,1,1252)(" ") + complemento +
(DT_STR,3,1252)(" - ") + bairro + (DT_STR,10,1252)("
- CEP: ") + CEP
Data Type string [DT_STR]
Length 150
Tabela 7. Configuração do Derived ColumnTransformation Editor do “DER Coluna
Endereço”

Para finalizar a implementação do “DFT dim_Loja”, deve -se adicionar o componente
Slowly Changing Dimension (SCD) com o nome “SCD Dimensão Loja” e conectá -lo
ao fluxo de dados do componente anterior. Este componente irá gerenciar todas as
alterações sofridas pelas dimensões. O SSIS possui um assistente que orienta o
desenvolvedor por uma série de etapas baseadas nos esquemas da dimensão de
origem e de destino, para determinar as características a serem alteradas. Em
seguida, o assistente cria as transformações necessárias para processar a
dimensão. O SCD será o principal componente para a transformação dos dados nas
dimensões. A seguir são apresentadas suas principais características:

 Atributos da dimensão em constante mudança: o valor histórico da
coluna é substituído toda vez que o valor da coluna de origem é alterado.
Por exemplo, se o nome da loja sofrer alguma alteração, o valor anterior
será substituído pelo novo. É conhecido também como coluna de tipo 1;
 Atributos da dimensão histórica: onde o valor histórico é mantido e um
registro é adicionado na tabela da dimensão. Por exemplo, se o nome da
loja sofrer alguma alteração, o valor anterior será mantido e um novo
registro será criado com o novo valor. É conhecido também como coluna de
tipo 2.

Então, para começar a configurar o componente SCD, será necessário definir pelo
menos uma Business Key. Uma Business Key é uma chave de negócio que
geralmente é a chave primária no banco de produção, mas também pode -se criar
uma nova chave alternativa para representá-la. Agora, na janela Select a
Dimension Table and Keys deve-se conectar na tabela “dim_Loja” do banco
“dmVendas” e definir a coluna “cod_loja” como Business Key (ver Figura 9) e clicar
em Next.

13. Figura 9. Definir a coluna Business Key da dimensão

Continuando a configuração do “SCD Dimensão Loja”, na janela Slowly Changing
Dimension Columns deve-se definir as características de mudanças das colunas da
dimensão. Como solicitado pelo usuário final, os dados deverão ser armazenados
historicamente, assim, deve-se definir as colunas como Historical Attribute (tipo 2)
e clicar em Next. Desta forma, quando uma coluna sofrer alguma alteração no
banco de dados de produção, esta nova informação será gravada em um novo
registro na tabela da dimensão, mantendo o valor anterior (ver Figura 10).

14. Figura 10. Configuração da janela Slowly Changing Dimension Columns

A configuração será definir uma coluna da dimensão para indicar se um registro é
atual ou não e, para isso, na janela Historical Attribute Options deve-se marcar o
item Use a single column, selecionar o campo “status_loja” para armazenar o
status do registro e selecionar os valores desejados, Current para dados atuais e
Expired para dados anteriores, e depois, clicar em Next (ver Figura 11). Já na
janela Inferred Dimension Members , não marcar nenhuma opção e clicar em Next
para finalizar a configuração.

15. Figura 11. Configuração da janela Historical Attribute Options

Após a configuração do componente “SCD Dimensão Loja”, o próprio irá criar as
próximas transformações necessárias para a dimensão automaticamente, bastando
renomear os demais componentes ger ados (ver Figura 12). Com isso, pode-se
destacar que o componente SCD nos poupará de um maior esforço, realizando
automaticamente todas as transformações necessárias para carregar os dados para
a tabela da dimensão correspondente.

16. Figura 12. Visualização da aba Data Flow da dimensão Loja

O SCD irá criar dois caminhos em paralelo, um para registros que sofreram alguma
alteração no banco de dados de produção e outro para novos registros. O fluxo de
dados gerado para os atributos históricos é ligado ao componente Derived Column
de nome “DER Status Expired” e este é responsável em adicionar informação
“Expired” ao fluxo de dados. O próximo componente OLE DB Command de nome
“CMD Update Status” irá executar a query UPDATE [dbo].[dim_Loja] SET
[status_loja] = ? WHERE [cod_loja] = ? AND [status_loja] = 'Current' alterando a
informação da coluna “status_loja” de “Current” para “Expired”. Já o outro fluxo de
dados criado pelo SCD irá conter os novos registros, estes dois caminhos serão
unidos em um único fluxo de dados pelo componente Union All de nome “Union All
Loja” e serão ligados a um componente Derived Column de nome “DER Status
Current” para adicionar a informação “Current” para os novos registros. No próximo
componente OLE DB Destination de nome “OLE_DST Insert dim_Loja” os dados
finalmente serão gravados na tabela “dim_Loja” do banco “dm_Vendas”.
Em nosso exemplo, se o endereço de uma loja for alterado no banco de dados de
produção, o SCD irá identificar esta alteração automaticamente. No fluxo de dados
de atributos histórico o registro que contém o endereço antigo da loja recebe o
status “Expired”, já no outro fluxo de dados gerado para novos registros o SCD irá
criar um novo registro com o novo endereço da loja atribuindo o status “Current”.

Adicionando a dimensão Vendedor
De volta à aba Control Flow do projeto, deve-se adicionar um novo componente
Data Flow Task, agora, com o nome “DFT dim_Vendedor”, e implementar a
dimensão Vendedor seguindo as características a seguir:
 Deve-se importar os dados de todos os Vendedores no banco de dados de
produção “dbVendas”;
 Manter historicamente as mudanças sofridas pelas colunas da dimensão
Vendedor.

17. Na aba Data Flow do “DFT dim_Vendedor”, deve-se seguir os mesmos passos da
implementação anterior, mas com algumas diferenças. Adicionar o componente OLE
DB Source com o nome “OLE_SRC Tabela Vendedor” e configurá -lo para importar
os dados dos Vendedores e sua respectiva loja com a s eguinte instrução SQL (ver
Tabela 8).

Propriedade Valor
OLE DB Connection manager localhost.dbVendas
Data access mode Sql command
Sql command text select v.cod_vendedor, v.nome_vendedor,
l.cod_loja, l.nome_loja
from vendedor v inner join loja l on l.cod_loja =
v.cod_loja
Tabela 8. Configuração do OLE DB Source Editor do “OLE_SRC Tabela Vendedor”

Agora, deve-se adicionar o componente Slowly Changing Dimension como nome
“SCD Dimensão Vendedor” e conectá -lo ao fluxo de dados do componente anterior.
A configuração do SCD será muito parecida com a implementação feita
anteriormente na dimensão Loja. Na janela Select a Dimension Table and Keys
deve-se conectar na tabela “dim_Vendedor” do banco “dmVendas”, definir a coluna
“cod_vendedor” como Business Key e clicar em Next. Para manter os dados
historicamente, na janela Slowly Changing Dimension Columns deve-se definir as
colunas da dimensão como Historical Attribute (tipo 2) e clicar em Next.
Para configurar a janela Historical Attribute Options deve-se marcar o item Use a
single column, selecionar o campo “status_vendedor” para armazenar os valores de
status do registro, o Current para dados atuais e Expired para dados antigos e,
depois, clicar em Next. Na próxima janela, Inferred Dimension Members, não
marcar nenhuma opção e clicar em Next para finalizar a configuração da dimensão
(ver Figura 13). Lembre-se que o SCD irá criar automaticamente as próximas
transformações necessárias para esta dimensão.

18. Figura 13. Visualização da aba Data Flow da dimensão Vendedor

Adicionando a dimensão Produto
Para implementar a dimensão Produto, deve -se voltar à aba Control Flow do
projeto e adicionar um novo componente Data Flow Task com o nome “DFT
dim_Produto” e implementar a dimensão seguindo as características a seguir:
 Deve-se importar os dados de todos os Produtos no banco de dados de
produção “dbVendas”;
 Manter historicamente as mudanças sofridas pelas colunas da dimensão
Produto.

Na aba Data Flow do “DFT dim_Produto”, deve-se adicionar o componente OLE DB
Source com o nome “OLE_SRC Tabela Produto” e configurá -lo para importar os
dados dos Produtos com suas respectivas categorias e subcategorias (ver Tabela
9).

Propriedade Valor
OLE DB Connection manager localhost.dbVendas
Data access mode Sql command
Sql command text select p.cod_produto, p.desc_produto,
s.cod_subcategoria, s.desc_subcategoria,
c.cod_categoria, c.desc_categoria
from produto p
inner join subcategoria s on s.cod_subcategoria
= p.cod_subcategoria and s.cod_categoria =
p.cod_categoria
inner join categoria c on c.cod_categoria =
p.cod_categoria
Tabela 9. Configuração do OLE DB Source Editor do “OLE_SRC Tabela Produto”

19. Depois, deve-se adicionar o componente Slowly Changing Dimension com o nome
“SCD Dimensão Produto” e conectá -lo ao fluxo de dados do componente anterior.
Na janela Select a Dimension Table and Keys deve-se conectar na tabela
“dim_Produto” do banco “dmVend as” e definir a coluna “cod_produto” como
Business Key e clicar em Next. Para manter os dados historicamente, na janela
Slowly Changing Dimension Columns deve-se definir as colunas da dimensão como
Historical Attribute (tipo 2) e clicar em Next.
Para configurar a janela Historical Attribute Options deve-se marcar o item Use a
single column, selecionar o campo “status_produto” para armazenar o status do
registro e selecionar os valores desejados, o Current para dados atuais e Expired
para dados antigos, e depois, clicar em Next. Na próxima janela Inferred Dimension
Members não marcar nenhuma opção e clicar em Next para finalizar a configuração
da dimensão (ver Figura 14).


Figura 14. Visualização da aba Data Flow da dimensão Produto

Dimensão Tempo
Como já foi descrito neste artigo, a dimensão Tempo será tratada separadamente,
pois os dados que ela armazena se tratam de informações fixas como os meses de
um ano. Desta forma, uma maneira simples de alimentar a dimensão Tempo será
criar uma planilha no Excel contendo as mesmas colunas da tabela “dim_Tempo” e
utilizar o utilitário Import Data do SQL Server 2005 em Management Studio
Tasks>Import Data para importar os dados para os anos que se deseja trabalhar.

Nota: O arquivo de backup “dmVendas.bak” já possui a dimensão Tempo
alimentada para os anos de 2006, 2007 e 2008.

Adicionando a fato Venda
O próximo passo da implementação do pacote “ETL_Vendas” será importar os
dados para a tabela de fatos do Data Mart. Como a atualização do Data Mart é

20. diária, visando otimizar a extração dos dados, deve-se alimentar a tabela
“fact_Venda” do banco “dmVendas” de modo incremental. Para isso, deve -se
adicionar o componente Execute SQL Task na aba Control Flow do projeto com o
nome “SQL Max Data Venda”. Esta tar efa será responsável por retornar o último
valor da data que foi carregado para a tabela de fatos. Para configurar o “SQL Max
Data Venda”, na janela Execute SQL Task Editor deve-se editá-lo como na Figura
15 e, em SQLStatement, utilizar a instrução SQL select max(data) as max_date
from fact_Venda v inner join dim_Tempo t on v.id_data = t.id_data para retornar a
maior data.
Seguindo a configuração, na opção Result Set, que se encontra no menu à
esquerda da janela Execute SQL Task Editor (ver Figura 15), deve-se adicionar
uma nova variável de usuário. Para criar uma nova variável utilizando o Execute
SQL Task, é preciso clicar no botão Add e, em Result Name, colocar o nome
“max_date” e selecionar <New variable...> em Variable Name. Na janela ADD
Variable, configurar de acordo com a Figura 16. Pode-se observar que é
necessário inicializar a variável com uma data qualquer.


Figura 15. Visualização da janela Execute SQL Task Editor do “SQL Max Data
Venda”

21. Figura 16. Visualização da janela ADD Variable

Agora, com a variável de usuário criada para o projeto, pode-se implementar a
extração dos dados para a tabela de fatos do Data Mart. Na aba Control Flow do
projeto, deve-se adicionar um componente Data Flow Task com o nome “DFT
fact_Venda”. Então, na aba Data Flow do “DFT fact_Venda” adicionar o
componente OLE DB Source com o nome “OLE_SRC Tabela Item_Venda” e
configurá-lo para importar os dados dos itens vendidos com seus respectivos
relacionamentos com as outras tabelas do banco de produção (ver Tabela 10).

Propriedade Valor
OLE DB Connection
manager
localhost.dbVendas
Data access mode Sql command
Sql command text select ve.cod_loja, v.cod_vendedor, i.cod_produto,
v.data_venda, i.quantidade,
(i.quantidade*i.valor_unitario) as valor_venda
from venda v
inner join item_venda i on i.cod_venda = v.cod_venda
inner join vendedor ve on ve.cod_vendedor =
v.cod_vendedor where data_venda > ?
Tabela 10. Configuração do OLE DB Source Editor do “OLE_SRC Tabela
Item_Venda”

Pode-se observar que na instrução SQL, o sinal de interrogação representa uma
passagem de parâmetro e este receberá o valor da variável de usuário
implementada anteriormente. Para isso, ainda na janela OLE DB Source Editor, é
necessário clicar no botão Parameters e, na janela Set Query Parameters,
selecionar a variável “User::max_date”, passando o valor da maior data como
parâmetro, assim a instrução SQL trará somente as vendas que ainda não foram
incluídas no Data Mart.
Depois, deve-se relacionar as tabelas do banco de produção com as tabelas das
dimensões do Data Mart, para retornar suas respectivas Surrogates Keys das
tabelas das dimensões e para que se possa incluí-las na tabela de fatos. Como já
explicado neste artigo, as chaves das tabelas de dimensão não possuem nenhum
vínculo com as chaves primárias do banco de produção. Desta forma, temos que

22. relacionar as Business Key que foram definidas em cada uma das dimensões
anteriormente, com as chaves primárias do banco de dados de produção. Para isso,
deve-se utilizar o componente Lookup, que conforme as linhas de origem fluem, ele
retorna chaves substitutas (Surrogate Key) necessárias para a tabela de fatos
baseada na associação de chave comercial (Business Key).
Continuando a implementação, é preciso ad icionar um componente Lookup com o
nome “LKP dim_Loja”, conectá -lo ao fluxo de dados do componente anterior e
configurá-lo para retornar a Surrogate Key da dimensão Loja. Na aba Reference
Table da janela Lookup Transformation Editor deve-se configurar de acordo com a
Tabela 11. Em seguida, mudar para a aba Columns e mapear o relacionamento
entre a consulta feita dos itens de venda (Input Columns) e a tabela “dim_Loja”
(Lookup Columns), retornando a Surrogate Key da dimensão Loja, a coluna
“id_loja” (ver Figura 17).

Propriedade Valor
OLE DB Connection manager localhost.dmVendas
Use results of an SQL query select id_loja, cod_loja from dim_loja
where status_loja = 'Current'
Tabela 11. Configuração do Lookup Transformation Editor do “LKP dim_Loja”


Figura 17. Visualização da aba Columns do “LKP dim_Loja”

A seguir, deve-se fazer o mesmo passo anterior para as outras dimensões,
adicionando um componente Lookup para cada dimensão restante, conectá-los aos

23. fluxos de dados correspondentes e mapeá -los corretamente para retornar a
respectiva Surrogate Key.
Para implementar o “LKP dim_Vendedor”, abra a janela Lookup Transformation
Editor do componente e configure-a de acordo com a Tabela 12. Depois, mude
para a aba Columns e relacione a coluna “cod_vendedor” do Input Columns, com a
coluna “cod_vendedor” do Lookup Columns, para retornar a coluna “id_vendedor”.
Fazer o mesmo para o “LKP dim_Produto”, abrir a janela Lookup Transformation
Editor e configurar de acordo com a Tabela 13. Mude para a aba Columns e
relacione a coluna “cod_produto” do Input Columns, com a coluna “cod_produto”
do Lookup Columns, para retornar a coluna “id_produto”. No “LKP dim_Tempo”,
abra a janela Lookup Transformation Editor e configure-a de acordo com a Tabela
14. Mude para a aba Columns e relacione a coluna “data” do Input Columns, com a
coluna “data” do Lookup Columns, para retornar a coluna “id_data”.

Propriedade Valor
OLE DB Connection manager localhost.dmVendas
Use results of an SQL query select id_vendedor, cod_vendedor from
dim_vendedor
where status_vendedor = 'Current'
Tabela 12. Configuração do Lookup Transformation Editor do “LKP dim_Vendedor”

Propriedade Valor
OLE DB Connection manager localhost.dmVendas
Use results of an SQL query select id_produto, cod_produto from dim_produto
where status_produto = 'Current'
Tabela 13. Configuração do Lookup Transformation Editor do “LKP dim_Produto”

Propriedade Valor
OLE DB Connection manager localhost.dmVendas
Use results of an SQL query select id_data, data from dim_tempo
Tabela 14. Configuração do Lookup Transformation Editor do “LKP dim_Tempo”

Agora, com todos os lookups implementados, deve-se adicionar o componente OLE
DB Destination para gravar os dados na tabela “fact_venda” do banco “dmVendas”.
Para isso, é preciso colocar o nome “OLE_DST Insert fact_Venda” e conectá -lo ao
fluxo de dados do componente anterior (ver Figura 18). Na opção Mappings da
janela OLE DB Destination Editor, pode-se observar que o SSIS mapeia
automaticamente as colunas do fluxo de dados de Input com as colunas de destino
da tabela “fact_loja”.

24. Figura 18. Visualização da aba Data Flow da fato Vendas

Para finalizar o pacote “ETL_Vendas”, deve-se adicionar um componente Send Mail
Task na aba Control Flow e configurá-lo com uma conta de e-mail válida para
enviar uma mensagem caso ocorra falha no processamento deste projeto. Depois,
ligue todas as tarefas com fluxos de sucesso (verde) e conecte os fluxos de falha
(vermelho) ao componente de email (ver Figura 19). Com o pacote “ETL_Vendas”
implementado e tabela “dim_Tempo” alimentada pelo script “dim_Tempo.sql”,
pode-se agora importar os dados para o nosso Data Mart. Assim, na aba Control
Flow, basta clicar na tecla F5 para executar o pacote e começar a extração dos
dados.

25. Figura 19. Visualização final da aba Control Flow do pacote “ETL_Vendas”

26. Implementando uma solução Business Intelligence com o
Microsoft SQL Server 2005 – Parte 2
Modelagem Multidimensional, Data Mart, ETL, Cubo Olap e Visualização de
dados com o Excel 2007

De que se trata o artigo:
Implementação de uma solução de Business Intelligence (BI) através do SQL
Server 2005. Este artigo complementa o anterior, que exemplifica as etapas de
modelagem multidimensional e ETL (extração, transformação e carga). Nesta
segunda parte serão apresentadas as etapas de visualização e análise de dados.

Para que serve:
Fornecer uma solução que permita a realização de consultas, sobre as bases de
dados de uma organização, de forma amigáv el, rápida e flexível, proporcionando a
geração de informações estratégicas para dar suporte ao processo de tomada de
decisão pelos usuários de sistemas e gestores do negócio.

Em que situação o tema é útil:
Uma solução de BI atende a organizações de pequeno, médio e grande porte. Deve
ser implementada quando existir a necessidade se conhecer e explorar com mais
eficiência e agilidade os dados armazenados em um ou mais bancos de dados da
empresa.

Implementado uma solução Bus iness Intelligence com o Microsoft SQL
Server 2005 – Resumo Devman
Neste estudo, a solução de Business Intelligence permite analisar as vendas de
uma empresa realizadas, através de um site de comércio eletrônico. Na primeira
parte do artigo foi realizada a criação e carga do Data Mart. Nesta etapa será
criada, através do Analysis Services, a estrutura multidimensional que irá permitir a
execução de consultas de forma simples e direta através do Excel 2007.
Nesta segunda parte do artigo finalizaremos a solução de Business Intelligence
através da construção do projeto no Analysis Services e da configuração da
visualização dos dados no Microsoft Excel.

Analysis Services
Com o Data Mart de vendas devidamente alimentado, após o processo de ETL,
agora se deve utilizar uma ferramenta OLAP para disponibilizar os dados para a
consulta do usuário final. O SQL Server 2005 oferece o Microsoft Analysis Services
como ferramenta OLAP para a geração dos cubos de consulta. Um cubo é uma
estrutura multidimensional que requer a definição de pelo menos uma tabela de
dimensão e uma tabela de fatos no seu esquema.
Para iniciar um novo projeto no Analysis Services, deve-se abrir a ferramenta
Business Intelligence Development Studio do SQL Server 2005, clicar na opção
File>New>Project, selecionar Analysis Services Project em Templates, renomear
para “OLAP_Vendas” no campo Name e clicar em Ok (ver Figura 1).

27. Figura 1. Criando um projeto para o Analysis Services

Agora, o próximo passo será criar um Data Source para conectar no banco de
dados “dmVendas”, que foi disponibilizado na primeira parte deste artigo. Na janela
Solution Explorer (selecionar através do menu View – Solution Explorer), clique
com o botão direito do mouse em Data Sources e selecione New Data Source. Na
janela Data Source Wizard, em Data connections selecione “localhost.dmVendas” e
clique em Next. (ver Figura 2).


Figura 2. Criando um data source e definindo a conexão com o banco de dados
“dmVendas”

Na janela Impersonation Information marque a opção Default e clique em Next. Em
seguida defina o nome para o Data Source e clique em Finish para finalizar a etapa
de criação.

28. Após a criação do Data Source, deve-se criar um Data Source Views para selecionar
as tabelas que serão utilizadas neste projeto. Dessa forma, na janela Solution
Explorer, deve-se clicar com o botão direito em Data Sources Views e escolher a
opção New Data Source Views, selecionar o Data Source criado anteriormente e
clicar em Next.
Na janela seguinte, selecione todas as dimensões e a tabela de fatos, localizadas no
quadro Available objects. Em seguida, clique no botão >> para mover os objetos
para o quadro Include objects (ver Figura 3).


Figura 3. Selecionando todas as dimensões e a tabela de fatos

Na janela seguinte defina o nome para o Data Source View e clique em Finish para
finalizar a etapa de criação. Com a conexão do banco de dados criada e com as
tabelas do projeto definidas, deve-se agora criar um cubo seguindo as
características que foram levantadas no estudo de caso. Para isso, na janela
Solution Explorer clique com o botão direito em Cubes e em New Cube. Na janela
Select Build Method marque a opção Build the cube using a data source e
desmarque a opção Auto build (ver Figura 4).

29. Figura 4. Selecionando o método de construção do cubo

Com a opção Auto build marcada, o cubo será gerado automaticamente, mas, no
intuito de demonstrar passo a passo como construir um cubo, deve -se deixar
desmarcada esta opção. Na próxima janela, Select Data Source View, selecione o
Data Source criado anteriormente e clique em Next. Na janela Identify Fact and
Dimensions Tables devem-se definir as tabelas de dimensões e a tabela de fatos do
cubo, além de informar quem será a dimensão Tempo (ver Figura 5).


Figura 5. Visualização da janela Identify Fact and Dimensions Tables

Na janela seguinte, Select Time Periods, deve-se definir a hierarquia da dimensão
Tempo, seguindo a granularidade definida para o Data Mart, que foi diária (ver
Figura 6).


Figura 6. Definindo a hierarquia da dimensão tempo na janela Select Time Periods

30. Ainda no Cube Wizard, mas agora na janela Select Measures, pode-se visualizar
que as medidas do cubo foram geradas automaticamente, mas seguindo o estudo
de caso, apenas duas medidas foram identificadas, a quantidade de unidades
vendidas e o valor de venda por produto. Deste modo, deve-se desmarcar a
medida Fact Venda Count, pois não será necessário contar o número de vendas. A
seguir, na janela Review New Dimensions pode-se visualizar e alterar as estruturas
das dimensões com suas respectivas estruturas e hierarquias.
Finalmente na janela Completing the Wizard, deve-se revisar a estrutura do cubo e
finalizar a sua implementação. Com o cubo de Vendas construído, na Figura 7
pode-se visualizar a sua estrutura multidimensional com suas respectivas
dimensões e tabela de fatos.


Figura 7. Visualização do cubo de Vendas

Seguindo o que foi definido no estudo de caso, que se encontra na primeira parte
do artigo, devemos criar as hierarquias das dimensões restantes. Para criar os
níveis da dimensão Loja, dê um duplo clique em Dim Loja, localizado na janela
Solution Explorer, na pasta Dimensions. Na aba Dimension Structure, arraste com o
mouse os atributos “Estado”, “Cidade” e “Nome Loja”, nesta ordem, para a área
Hierarchies and Levels (arraste um campo de cada vez posicionando uma abaixo do
outro). Em seguida, altere a descrição do objeto de Hierarchy para “Estado –
Cidade - Loja” (para renomear clique em Hierarchy e tecle F2).
Em nosso exemplo, por utilizarmos uma estrutura não padroni zada, deve-se
também definir o relacionamento entre os atributos em new attribute relationship,
como Cidade>Estado e Nome Loja>Cidade. Para definir o relacionamento deve-se
clicar na seta, em frente à descrição do nível, de forma a expandir suas
propriedades. Em seguida, arraste o atributo utilizado para o relacionamento para o
campo new attribute relationship. Deve-se fazer o mesmo para as dimensões

31. Vendedor e Produto, mas sempre levando em consideração o que foi levantado no
estudo de caso (ver Figuras 8, 9 e 10).


Figura 8. Visualização da hierarquia (Estado – Cidade – Loja) da dimensão Loja


Figura 9. Visualização da hierarquia (Loja – Vendedor) da dimensão Vendedor


Figura 10. Visualização da hierarquia (Categoria – Sub – Produto) da dimensão
Produto

Agora, deve-se implementar a KPI que foi solicitada para premiar os vendedores
que ultrapassarem a meta de vendas estipulada. No intuito de demonstrar esta
implementação, vamos definir a meta em R$ 750.000,00 de vendas no total dos
anos de 2006 e 2007, por exemplo. Para criar uma KPI, deve-se selecionar o cubo
com um duplo clique no Solution Explorer, ir à aba KPIs, clicar com o botão direito
do mouse na área KPI Organizer e selecionar New KPI.
O próximo passo será definir o seu nome como “Meta”, associar ao grupo de
medidas “Fact Venda” e definir um valor para a expressão “[Measures].[Valor
Venda]”. Depois, deve-se implementar o código MDX em Status expression (ver
Figura 11), para que se possa qualificar valores acima de 750.000 como meta de
vendas ultrapassada e abaixo de 650.000 como mínimo não alcançado. Por último,
defina a visualização da KPI utilizando o modelo de semáforo tradicional de três
cores em Status indicator. A cor verde indicará a situação ideal, enquanto o
vermelho indicará a pior situação. A visualização destes sinais poderá ser vista no
decorrer deste artigo.

32. Figura 11. Criando a KPI Meta

A última configuração que se faz necessária será a implementação da consulta
Drillthrough. Como já visto, Drillthrough é uma consulta que retorna uma
informação fixa, previamente definida pelo usuário. Na aba Actions do cubo, deve-
se clicar com o botão direito do mouse na área Actions Organizer e selecionar New
Drillthrough Action. Depois, definir seu nome como Exibir Dados e selecionar as
colunas que serão exibidas pela consulta em Drillthrough Columns (ver Figura 12).
Com todas as configurações do cubo de Vendas finalizadas, primeiramente deve -se
compilar o projeto com a tecla F5. Feito isto, deve-se processar o cubo para gerar
os dados. Em Solution Explorer clique com o botão direito do mouse no cubo e
clique em Process. Após o processamento do cubo, na aba Browser do cubo, é
possível visualizar os dados que foram gerados para consulta.

33. Figura 12. Configurando a consulta Drillthrough

Visualizando os dados com o Excel 2007
O Microsoft Excel 2007 suporta trabalhar com bases de dados multidimensionais
como, por exemplo, o Analysis Services. Assim, podem-se utilizar as dimensões
OLAP para criar relatórios complexos de forma livre.
Para conectar o Excel 2007 ao cubo Vendas e possibilitar a visualização dos dados
deve-se criar uma nova planilha. Com a nova planilha aberta, acesse o menu
Dados>Obter Dados de Outras Fontes, escreva o nome do servidor de dados onde
se encontra o cubo (localhost) e, na próxima janela, selecione o cubo de Vendas
(ver Figura 13). Após conectar ao cubo, os usuários podem realizar diferentes
operações para visualizar e analisar seus dados. As principais operações que podem
ser realizadas na área de dados são:

 Drill Down e Drill Up: o usuário pode navegar pelas hierarquias de uma
dimensão com o Drill Up do detalhe para o geral (agrupando) e com o Drill
Down do geral para o detalhado (desagrupando). Por exemplo, ao analisar
os dados de vendas de um determinado Estado (geral), pode -se realizar
uma operação Drill Down para exibir estas vendas detalhadas por Cidades
(detalhe);
 Slice e Dice (Filtro): o Slice e o Dice funcionam como filtros, podendo
gerar fatias ou pedaços específicos de um cubo. Por exemplo, ao selecionar
a dimensão Tempo pode –se definir uma fatia do cubo para exibir somente
as vendas realizadas no ano de 2007;
 Rotação: seleciona a ordem de visualização das dimensões, gira o cubo de
acordo com a necessidade do usuário. Por exemplo, pode -se ter uma
consulta de vendas por Cidades (na posição de coluna) e Lojas (na posição
de linha). A operação de rotação consiste em trocar a posição das
dimensões, ou seja, posicionar Cidades na posição de Linha e Lojas na
posição de coluna;
 Sort: o usuário pode ordenar uma informação, podendo ser crescente ou
decrescente.

34. Figura 13. Visualização do assistente para conexão de dados do Excel 2007

A navegação utilizando o Excel será feita através de uma Tabela Dinâmica,
permitindo que os usuários explorem facilmente as dimensões e medidas de um
cubo. A Tabela Dinâmica apresenta a seguinte estrutura (ver Figura 14):
1. Lista de Campos: contém a lista das dimensões e as medidas do cubo;
2. Filtro de Relatórios: na parte superior da tabela dinâmica, podem ser
incluídas uma ou mais dimensões. É possível filtrar a informação
selecionando vários níveis ou membros em particular. Nesta á rea é
implementada somente a operação de Filtro;
3. Rótulos de Colunas: posicionada na parte superior da Tabela Dinâmica,
debaixo da área de filtros. Nela são definidas as dimensões que serão
exibidas em colunas. Nesta zona podem ser arrastadas as dime nsões,
navegar por elas e decidir quais níveis mostrar e o grau de abertura da
informação. Nesta área são implementadas as operações Drill-Up, Drill-
Down e Filtro;
4. Rótulos de Linhas: na parte esquerda são definidas as dimensões que
serão exibidas em linhas. Nesta zona podem ser arrastadas as dimensões,
navegar por elas e decidir quais níveis mostrar e o grau de abertura da
informação. Nela são implementadas as operações Drill-Up, Drill-Down e
Filtro;
5. Valores: no centro da tabela dinâmica, podem ser incluídas apenas
medidas. Quando se arrasta uma medida, obtém -se o resultado da
intersecção com as dimensões escolhidas como linhas e colunas, e para o
Filtro.

35. Figura 14. Visualização da estrutura da Tabela Dinâmica no Excel 2007

Em nosso exemplo, se for necessário fazer uma consulta de valores vendidos por
Categoria, Subcategoria ou de um Produto específico, deve -se selecionar na Lista
de Campos a medida Valor Venda e o item Categoria – Sub – Produto (ver
Figura 15) para gerar a consulta. As mudanças das consultas são feitas de forma
intuitiva, bastando arrastar o item desejado para a área da Tabela Dinâmica
desejada. Com isso, todos os Requisitos levantados no estudo de caso podem ser
gerados facilmente. Por exemplo, a quantidade de itens vendidos por Estado e
Cidade, bastando selecionar a medida Quantidade e o item Estado – Cidade –
Loja e, assim, pode-se visualizar a quantidade vendida por Estado, Cidade ou até
por Loja, se necessário.

36. Figura 15. Visualização do cubo de Vendas utilizando o Excel 2007

Clicando com o botão direito do mouse em cima da medida “Valor Venda” ou
“Quantidade”, no menu Ações adicionais>Exibir Dados, pode-se visualizar o
resultado da consulta Drillthrough, com o endereço completo da Loja e o nome do
Vendedor responsável pela venda (ver Figura 16). Adicionando o “Nome
Vendedor” à célula “Rótulos de Linha”, mais a medida “Valor Venda” e a KPI “Meta
Status” à área de valores, pode-se visualizar o resultado dos semáforos, indicando
quais vendedores ultrapassaram a meta estipulada e os que nem atingiram o valor
mínimo definido (ver Figura 17).


Figura 16. Visualização da consulta Drillthrough

37. Figura 17. Visualização da KPI Meta

Gerenciando partições e otimizando o processamento de dados
Uma implementação que pode ser realizada visando melhorar o processamento de
um cubo é a criação de partições. Um cubo é formado por pelo menos uma
partição, e uma partição é uma divisão ou fracionamento das informações que
formam um cubo. As partições de um cubo são invisíveis para o usuário final. Para
cada partição é possível definir a fonte de dados, o tipo de armazenamento e a
porcentagem de agregação de forma independente das demais partições. As
agregações são resumos de dados pré-calculados que melhoram o tempo de
resposta nos processos de busca de informação.
Um dos principais benefícios de criar várias partições é que se podem projetar tipos
diferentes de armazenamentos para diferentes partes de um cubo. A segui r,
podem-se visualizar os tipos de armazenamentos e suas vantagens e desvantagens
(ver Tabela 1):

 MOLAP: no modo de armazenamento MOLAP uma cópia dos dados de
origem do cubo, junto com as suas agregações, ficam armazenadas em uma
estrutura multidimensional;
 ROLAP: em um modelo ROLAP toda a informação do cubo, seus dados,
suas agregações e somas, são armazenadas em um banco de da dos
relacional;
 HOLAP: o HOLAP (OLAP Híbrido) combina atributos do MOLAP e do ROLAP.

Vantagens Desvantagens
MOLAP Melhor desempenho no tempo
de resposta
Duplica o armazenamento de
dados, desta forma, ocupa
mais espaço
ROLAP Economia de espaço de
armazenamento. Útil quando
se trabalha com um grande
conjunto de dados
O tempo de resposta das
consultas é maior
HOLAP Bom tempo de resposta,
somente para informação
sumarizada
Volumes de dados maiores no
banco de dados relacional
Tabela 1. Vantagens e desvantagens dos tipos de armazenamento de um cubo

Ao definir as agregações de um cubo, é importante levar em consideração o tipo de
armazenamento e o valor da porcentagem de agregações, para se conseguir bons
tempos de resposta e espaço de armazenamento.

38. Se forem calculadas todas as agregações possíveis de um cubo, seria necessária
uma grande quantidade de tempo de processamento e espaço de armazenamento.
Se não forem pré-calculadas as agregações, a quantidade de espaço de
armazenamento nece ssário fica reduzida ao mínimo, entretanto o tempo de
resposta aumentará. Portanto, deve existir um equilíbrio entre o espaço de
armazenamento, porcentagem de agregações e o desempenho desejado.
No intuito de demonstrar a implementação de partições com dif erentes tipos de
armazenamento, deve -se, por exemplo, criar uma partição que contenha
informações referentes às vendas do ano atual e do ano anterior (2008 e 2007,
respectivamente). Como estas informações serão acessadas com maior freqüência,
por se tratar de informações atuais, deve-se definir o tipo de armazenamento como
MOLAP, com agregações para proporcionar um aumento de pelo menos 50% de
desempenho.
Para criar esta partição de vendas atuais, na aba Partitions do cubo deve-se
primeiramente excluir a partição default existente e depois clicar em New Partition
(ver Figura 18).


Figura 18. Visualização da aba Partitions do cubo

Na janela Specify Source Information selecione “dbo.fact_Venda” em Available
Tables e clique em Next. Na janela Restrict Rows deve-se selecionar a opção
Specify a query to restrict rows para informar uma consulta que retorne somente às
vendas realizadas a partir de 2007. Como a tabela de fatos possui somente
medidas e chaves de dimensões, será necessário encontrar a chave co rrespondente
ao primeiro dia de 2007, através da consulta select id_data from dim_Tempo
where data = '2007 -01-01' no banco de dados “dmVendas”, que retornará,
neste caso, o valor 5846. Feito isto, informar a consulta select * from fact_venda
where id_data >= 5846 no campo Query (ver Figura 19) e, com isso, somente
as vendas realizadas a partir do ano de 2007 serão retornadas.
Na próxima janela, devem-se manter as configurações default e, na última janela
de configuração, definir a partição com o nome “Vendas Atuais” e selecionar a
opção Design aggregations for the partition now, para que se possa definir o tipo de
armazenamento e criar as agregações necessárias para esta partição.

39. Figura 19. Especificando a query para retornar somente os dados a partir de 2007

Agora, deve-se definir o tipo de armazenamento como MOLAP (ver Figura 20) e
clicar em Next. Na janela Specify Objects Counts deve-se clicar em Count para
calcular automaticamente o número de registros. Na próxima janela deve -se
configurar como solicitado anteriormente, selecionando a opção Performance gain
reaches e definindo o seu valor com 50%, clicar em Start. Desta forma, o Analisys
Services irá gerar automaticamente as agregações necessárias e otimizar seu
desempenho para as consultas (ver Figura 21).


Figura 20. Definindo o tipo de armazenamento da partição “Vendas Atuais”

40. Figura 21. Otimizando a partição “Vendas Atuais”

Nossa segunda partição deverá conter as informações referentes às vendas
anteriores ao ano de 2007. Por se tratar de informações históricas, estas
informações serão acessadas ocasionalmente e, assim, deve -se especificar o tipo
de armazenamento MOLAP com agregações de desempenho de 30%.
Para criar esta nova partição será semelhante aos passos anteriores, ir a aba
Partitions, clicar em New Partition e na janela Specify Source Information selecionar
“dbo.fact_Venda” em Available Tables, depois clicar em Next. Na janela Restrict
Rows selecionar a opção Specify a query to restrict rows e informar a query select
* from fact_venda where id_data < 5846 , para retornar as vendas realizadas
antes do ano de 2007 (o número 5846 corresponde à data 01/01/2007 na tabela
“dim_Tempo” do banco de dados “dmVendas”). Na próxima janela devem -se
manter as configurações default e, na última janela de configuração, informar o
nome para a partição como “Vendas 2006” e selecionar a opção Design
aggregations for the partition now, para que se possa definir o tipo de
armazenamento e criar as agregações necessárias para esta segunda partição.
Na janela Specify Storage and Caching Options deve-se definir o tipo de
armazenamento como MOLAP e clicar em Next. Na janela Specify Objects Counts
deve-se clicar em Count para calcular automaticamente o número de registros. Na
próxima janela, deve-se configurar como solicitado anteriormente, selecionando a
opção Performance gain reaches e definindo o seu valor com 30% clicar em Start.
Desta forma, o Analisys Services irá gerar automaticamente as agregações
necessárias e otimizar seu desempenho para a realização de consultas.

Conclusões
Vimos que uma solução de BI representa uma excelente ferramenta de apoio a
evolução e crescimento do negócio das organizações. Estas soluções devem ser
desenhadas de forma que possam acompanhar esta evolução e crescimento. Os
sistemas de BI são válidos para qualquer processo de tomadas de decisões, não
sendo exclusividade das áreas comerciais ou financeiras.
Também vimos que uma solução de BI não são apenas simples geradores de
relatórios, vão muito mais além, pois disponibilizam as informações com um grau
de detalhe específico para cada tipo de análise. Desta forma, o usuário final não
precisa ficar preso a um único formato ou padrão definido pelos desenvolvedores,
estando livre para montar suas próprias consultas de acordo com as necessidades
de cada momento.

41. Referências


HANCOCK, J. C., TOREN, R. Practical Business Intelligence with SQL Server 2005.
Addison-Wesley Professional, 2006.

JACOBSON, R., MISNER, S., CONSULTING, H. SQL Server 2005 Analysis
Services Passo a Passo . Bookman, 2007.

KNIGHT, B., et al. Professional SQL Server 2005 Integration Services. Wrox, 2006.

KNIGHT B., VEERMAN, E. Expert SQL Server 2005 Integration Services. Wrox,
2007.

TURLEY, P., et al. Microsoft SQL Server 2005 Integration Services Step by Step.
Microsoft Press, 2007.