O artigo trata de um projeto de DW (Data Warehouse), onde são abordadas algumas ferramentas e tecnologias da Microsoft integradas ao SQL Server 2008, como o Integration Services, Analysis Services e Reporting Services. Também será demonstrada uma visão geral da linguagem multidimensional MDX.
Em que situação o tema útil
O tema pode ser útil na definição de um modelo dimensional a partir de um banco de dados. A proposta do artigo é demonstrar as fases principais para a criação de um DW / DM (Data Mart) e como é possível fazê-la de uma forma simples e rápida. Consideramos que o leitor possua conhecimentos básicos sobre as ferramentas Microsoft utilizadas no artigo e também conceitos sobre DW e DM.
Resumo DevMan
Este artigo apresenta o uso das ferramentas Microsoft para a criação de um Data Mart. Usando o Visual Studio será gerada uma solução desde a extração dos dados com o Integration Services, a análise de dados do cubo com o Analysis Services e a geração de relatórios com a Reporting Services. O artigo apresenta também alguns aspectos sobre OLAP e relatórios com MDX. A versão do SQL Server utilizada no artigo está em inglês.
Autores: Sérgio Furgeri e Juliana Gothardo
A importância de criação de sistemas de Business Intelligence (BI) está crescendo a cada dia, e com isso a necessidade de criar projetos DW / DM. A Microsoft possui algumas ferramentas que nos auxiliam a criar esses sistemas, algumas delas sendo o Integration Services, o Analysis Services e o Reporting Services. Essas ferramentas serão abordadas no decorrer deste artigo.
Para apresentar as ferramentas mencionadas utilizaremos como exemplo um site sobre seriados de TV, que funciona como uma rede social. A partir dos dados transacionais contendo as atividades dos usuários, criaremos um Modelo Dimensional para iniciar nosso projeto de DM.
Por meio do Microsoft Visual Studio 2008 será criado um projeto do Integration Services, uma ferramenta ETL (Extração Transformação Carga) que usaremos para dar carga nas tabelas dimensionais. No nosso exemplo demonstraremos como capturar dados de um banco MySQL para popular um banco SQL Server.
Após a carga de dados, será utilizado o Analysis Services responsável pela criação de cubos e medidas, onde serão feitas análises dos dados e também KPIs (Key Performance Indicator).
Já o Reporting Services tem como objetivo a criação e exportação de relatórios. Iremos demonstrar como criar um relatório e como utilizar filtros para o relatório criado.
Para completar, será apresentado também como criar queries para banco de dados utilizando a linguagem MDX.
Com tudo isso, esperamos que ao final do artigo o leitor adquira uma boa idéia sobre os elementos e conceitos envolvidos na criação, preenchimento e manipulação de bancos de dados multidimensionais.
Contextualização do estudo de caso
O projeto demonstrado no artigo utiliza os dados de um site sobre seriados de TV (www.vejoseries.com) que funciona como uma rede social para fãs de séries. O usuário se cadastra no site e através de sua conta ele adiciona amigos, as séries e episódios que ele já viu, as séries que ele vê, as séries que ele quer ver e suas séries favoritas, dessa forma o usuário se mantém atualizado sobre as séries que ele acompanha. Também é possível adicionar amigos e mandar recados. A partir disso, nosso exemplo apresenta como identificar informações relevantes em função do comportamento do usuário, isto é, a partir das interações do usuário buscaremos identificar algumas informações relevantes que podem auxiliar no processo decisório realizado pelo gestor do site.
Modelo Transacional
O Modelo Transacional se refere ao banco de dados usado no site Vejo Séries para capturar informações e ações realizadas pelos usuários. Ele é composto por tabelas do modelo relacional que armazenam diversas informações como, por exemplo, data e horário que foi feita cada atividade.
O banco de dados contempla também tabelas com as informações das séries, canais e gêneros, e com informações sobre os episódios da semana, não apenas de episódios que passam no Brasil, como também no exterior. Há ainda outras tabelas que complementam o conteúdo do site.
Para nossos objetivos de criação de um DM a partir dos dados do site, foram consideradas informações presentes em 11 tabelas que contém informação das séries, dos canais, dos gêneros, das visitas de cada série, e das atividades do usuário, tais como das séries que ele viu, vê, quer ver e séries favoritas. Essas tabelas podem ser visualizadas no modelo da Figura 1.
Figura 1. Modelo transacional usado no site Vejo Séries
Modelo Dimensional
Apesar de ser possível utilizar-se do próprio modelo transacional exposto anteriormente para analisar os dados e transformá-los em informações úteis, existe uma maneira mais indicada de se armazenar os dados antes de iniciar o processo de busca das informações. Trata-se da criação do modelo dimensional. Não é nosso objetivo realizar seu estudo aqui, mas, o leitor deve ter em mente que a criação de um modelo desse tipo organiza melhor os dados e traz maior velocidade de acesso as informações e geração de relatórios. Quanto maior for a base de dados utilizada, maiores serão os benefícios. A forma mais usada na criação dessa estrutura é o modelo estrela que contempla a tabela fato (com o fato a ser analisado) no centro do modelo e algumas tabelas de dimensões com os elementos que participam do fato.
Nosso objetivo em criar esse modelo será analisar os dados sobre as séries (nosso fato). Para isso foram selecionadas quatro dimensões:
• Tempo;
• Canais;
• Gêneros;
• Séries.
A tabela fato agrupa todos os dados das dimensões por meio de relacionamentos com as dimensões. Veja o modelo estrela definido na Figura 2. O script em SQL para você criar esse modelo encontra-se na Listagem 1.
Listagem 1. Script SQL para criação do modelo dimensional
Listagem 1. Script SQL para criação do modelo dimensional
CREATE database dwVejoSeries
USE dwVejoSeries
create table dimSeries (
idSerie varchar(10) primary key,
nome varchar(85),
origem varchar(50),
inicio int,
fim varchar(10),
duracao varchar(10),
numTemp int,
numEps int,
tipo char(1)
)
create table dimCanal (
idCanal varchar(10) primary key,
nome varchar(50),
pais varchar(5),
)
create table dimTempo(
idTempo int identity(1,1) primary key,
dia int,
diadoano int,
mes int,
nomedomes char(9),
ano int
)
create table dimGenero (
idGenero varchar(10) primary key,
nome varchar(25)
)
create table fatoSeries (
idFato int identity(1,1) primary key,
idTempo int,
idSerie varchar(10),
idCanal varchar(10),
idGenero varchar(10),
totalUsers int,
totalVisitas int,
totalQrVer int,
totalVendo int,
totalViu int,
totalFavoritas int
FOREIGN KEY(idTempo)
REFERENCES dimTempo(idTempo),
FOREIGN KEY(idCanal)
REFERENCES dimCanal(idCanal),
FOREIGN KEY(idGenero)
REFERENCES dimGenero(idGenero),
FOREIGN KEY(idSerie)
REFERENCES dimSeries(idSerie)
) Como você pode observar no modelo, a tabela fatoSeries contém todas as chaves estrangeiras das dimensões (são quatro ids, um para cada dimensão) para agregar todas as informações pertinentes ao assunto analisado. A tabela fatoSeries contém também outros atributos chamados de medidas (em nosso caso todos os atributos que iniciam com total) que contém valores numéricos. Esses atributos contêm o total de usuários que visualizaram aquela série (totalUser), que visitaram a série (totalVisitas), que sentiram interesse em ver a série (totalQrVer) e assim por diante. Dessa forma, cada linha de dados da tabela fatoSeries conterá todas as informações referentes a uma série: a data que a série foi exibida, o gênero da série, o canal que exibiu a série, além de todos os totais citados.
Integration Services
O Integration Services presente no pacote de aplicativos do MS SQL Server Analysis Services permite que os dados de uma fonte (de um banco de dados, por exemplo) sejam transferidos para outra fonte de dados. O site Vejo Séries utiliza o banco de dados My SQL e nosso DM foi criado com o SQL Server. Isso não representa nenhum problema já que o Integration Services pode realizar essa tarefa integrando dados de fontes diferentes.
Para poder realizar a tarefa seguinte, você já deve ter criado o banco de dados no SQL Server 2008 usando o script da Listagem 1. Precisa também ter o MySQL instalado e com o banco de dados fornecido.
Abra o Visual Studio e crie um novo projeto do tipo Integration Services (File >> New >> Project >> Business Intelligence Projects >> Integration Services Project).
Control Flow
O Control Flow é responsável pelo fluxo de tarefas do ETL (Extract Transform and Load), isto é, permite inserir um conjunto de tarefas, e seus componentes necessários, para realizar a extração dos dados armazenados no banco de dados MySQL e inserí-los no modelo estrela do banco de dados do SQL Server.
Agora iremos adicionar a primeira tarefa do nosso Control Flow. Arraste o item Data Flow Task para o Control Flow, conforme a Figura 3.
Figura 3. Adicionando itens no Control Flow
É possível também renomear o Data Flow Task clicando duas vezes no nome do objeto. Para esse projeto foi necessário criar quatro Data Flows Task, três para dimensões e outro para a tabela fato, conforme a Figura 4. A dimensão tempo será gerada de outra forma tratada mais a frente.
Os quatro Control Flow criados foram:
• Import Canais: Importa os dados dos canais;
• Import Gênero: Importa os dados dos gêneros;
• Import Séries: Importa os dados das séries;
• Import Fato: Importa as informações necessárias para a tabela fato.
...
