Transformação de bases de dados transacionais em Data Warehouse

O conceito de data warehouse (DW) está relacionado ao armazenamento de dados históricos de empresas em bases de dados, de forma que estes possam ser utilizados posteriormente para tomada de decisões estratégicas. Para que isso seja possível, após o DW estar construído, é necessária a utilização de técnicas de BI e/ou mineração de dados, com o intuito de encontrar conhecimento útil nos dados apresentados. A sua estrutura é a base para os sistemas de informações gerenciais e sistemas de apoio à decisão, pois ele possibilita a organização dos dados de forma a facilitar a integração com esses tipos de sistemas.

Guia do artigo:

• Entendendo a base de dados transacional
• Entendendo o data warehouse
• Criando o data warehouse
• Criando a interface gráfica e configurando o acesso aos dados no Delphi
• Importando os clientes
• Importando as lojas
• Importando os produtos
• Importando os dados de tempo
• Importando as vendas (tabela fato)

Dentro deste contexto, existem os sistemas OLTP (Online Trasaction Processing), que se enquadram na categoria dos sistemas de informações transacionais. Esse tipo tem a função principal de gerenciar as atividades diárias das empresas, como reserva de quartos em um hotel, processamento de folha de pagamento, controle de estoque, vendas em um supermercado, transações bancárias, dentre outros. Outras características dos sistemas transacionais são: possuir um alto grau de detalhe das informações, ser volátil e focado nas transações que estão ocorrendo em um determinado momento. Considerando o exemplo de um mercado, um sistema transacional pode ter a função principal de registrar as vendas, as quais são armazenadas em diversas tabelas que em geral seguem as formas normais da área de projeto de banco de dados.

Por outro lado, um data warehouse deve ser estruturado para possibilitar um alto nível de agregação das informações, bem como possibilitar uma visão dos dados integrados referentes às atividades ocorridas durante um tempo pré-determinado. Enquanto um sistema OLTP é volátil e rápido para oferecer detalhes minuciosos nos dados, um DW deve ser projetado para ser estável e rápido para fornecer agregações sobre os dados. A Figura 1 apresenta um comparativo que mostram as diferenças entre ele e um sistema transacional, na qual é possível observar que as principais operações em OLTP são: incluir, alterar, excluir e acessar. Por outro lado, em um data warehouse os dados são carregados e o foco principal é o acesso. Em geral, os dados armazenados em uma base desse tipo não podem ser alterados depois de carregados a menos que seja necessário fazer algum tipo de correção.

Para que a construção de um data warehouse seja possível é necessário existir pelo menos uma fonte da qual os dados serão extraídos. Normalmente, esses dados são importados dos sistemas transacionais/operacionais que existem nas empresas, porém, diversas outras fontes podem ser utilizadas, tais como dados de webservices, documentos de texto ou planilhas eletrônicas. A Figura 2 apresenta um esquema que mostra esse fato, no qual é possível visualizar diversas fontes de dados unindo-se ao data warehouse no centro da figura. Após a junção dos dados, diversos tipos de acesso podem ser feitos, desde a utilização de geradores de relatórios tradicionais (Fast Reports, por exemplo), ferramentas OLAP (FastCube, por exemplo) e/ou outros aplicativos.

Esse tipo de base de dados possui características distintas de uma base transacional. Por esse motivo, caso uma empresa deseje trabalhar com essa tecnologia, primeiramente precisa que sua base transacional, que utiliza normalização de dados, seja convertida para um novo padrão que se adeque às características do data warehouse, chamado de modelagem multidimensional. Esse processo normalmente não é trivial, pois requer diversas adaptações no novo banco, bem como a importação dos dados transacionais para o novo formato. Baseado nisso, o objetivo do presente artigo é DW. Será construído um importador no Delphi 10 Seattle utilizando o SQL Server e os componentes da biblioteca FireDAC. Em suma, o objetivo principal é transferir os dados, abordando os principais processos envolvidos nesta tarefa, bem como a redução de registros e o pré-processamento dos dados existentes.

As próximas seções apresentam as duas bases de dados utilizadas no exemplo proposto, bem como explicações sobre as principais diferenças entre uma base de dados transacional e um DW.

Entendendo a base de dados transacional

A Figura 3 apresenta o diagrama entidade relacionamento da base transacional. O objetivo principal desta base de dados é controlar a venda de produtos nas diversas filiais de uma determinada empresa, existindo para isso nove tabelas. É possível notar que o controle dos produtos é feito por quatro delas, a saber: produtos, produto_categorias, produto_departamentos e produto_familias. A tabela produtos armazena todos os itens existentes na loja, sendo representada somente pelo nome e pela categoria do produto. A categorização dos produtos é dada pelas três outras tabelas. Para exemplificar, considere a família de produtos Bebidas, relacionado a ela o departamento Bebidas alcóolicas e, por fim, o relacionamento com a categoria Cervejas. Em suma, a cerveja pertence ao grupo das bebidas alcoólicas, que por sua vez está ligado à família de produtos das bebidas.

A tabela cidades está relacionada com as tabelas de clientes e lojas, que por sua vez estão ligadas com a tabela de vendas. Como pode ser notado no esquema da base de dados, cada venda possui um cliente e uma loja na qual a venda foi realizada. Uma venda pode conter vários produtos, os quais são representados pela tabela vendas_produto.

Entendendo o data warehouse

A Figura 4 apresenta o diagrama entidade relacionamento da estrutura do DW já definida. É possível observar que a quantidade de tabelas diminuiu de nove para cinco, apresentando somente os dados que serão utilizados nas análises. Foram adicionados os campos cidade e estado diretamente nas tabelas de clientes e lojas. Nessas tabelas, pode-se notar também que os campos nome foram retirados, pois este tipo de informação normalmente não é relevante em agrupamento e/ou análise de dados. Utilizar o nome do cliente não traria muitos benefícios e insights para os gestores, pois talvez não faça muito sentido agrupar as vendas de todos os clientes com o nome de Maria, por exemplo. Já com relação às lojas, talvez fosse interessante agrupar os totais de vendas pelo nome da loja, pois assim o gestor poderia tomar decisões baseado em cada filial. Neste data warehouse, então, este campo não foi considerado.

Outra abreviação realizada na base de dados foi a junção das quatro tabelas sobre os produtos somente em uma, a qual possui a família, o departamento e a categoria dos produtos (produtos_classe). Essa redução foi necessária porque na tabela de produtos existem 10.281 registros distintos, o que tornaria o processo de análise e agrupamento dos dados inviável. Desta forma, foram utilizadas somente as 47 categorias existentes na base transacional, reduzindo muito a quantidade de registros únicos a serem processados; o que facilita também o agrupamento dos dados. Com isso, é possível visualizar os valores totais vendidos em cada grupo, como o total de unidades vendidas na categoria Cervejas.

A tabela vendas_fato é a principal, pois apresenta os campos que são as medidas, ou seja, as unidades vendidas e o valor vendido. Essa tabela, além de apresentar as chaves estrangeiras para clientes, lojas e classe de produtos, armazena os valores totais de cada venda, os quais são somados e agrupados da tabela vendas_produto do esquema transacional apresentado na Figura 3. É possível notar também a tabela tempo, a qual não consta no modelo transacional e tem o intuito de realizar o agrupamento dos dados pelo dia da semana, mês e quadrimestre. Esses dados podem ser obtidos por meio do campo data_venda da tabela vendas da base transacional. Neste contexto, a utilização de tabelas de tempo neste formato é bastante comum em cenários de Business Intelligence, já que essa separação da data completa em campos individuais possibilita melhor desempenho das consultas e melhor poder para resumo dos dados.

Essas alterações na base de dados podem parecer, à primeira vista, redundâncias e divergências com as formas normais se comparados ao modelo transacional. Porém, neste tipo de aplicação essas redundâncias são permitidas, pois os dados armazenados no data warehouse são de cunho histórico e utilizados somente para consultas. O usuário final de um sistema de informação transacional não terá acesso a esses dados, e operações rotineiras de inclusão, alteração e exclusão não são usualmente permitidas após as tabelas do DW serem preenchidas. Por esse motivo, não há a obrigatoriedade de manter integridade referencial na base de dados, pois como os registros não são excluídos, a integridade referencial não será quebrada em um relacionamento entre chave primária e chave estrangeira, por exemplo. Outro ponto a frisar é que como sua característica é possibilitar consultas, a redução do número de joins poderá reduzir os tempos de consultas à base de dados, dependendo da quantidade de tabelas e registros. Um exemplo claro na Figura 4 é a tabela produtos_classe que contém os principais dados dos produtos em uma única tabela, evitando consultas SQL com junção em quatro tabelas, originalmente oriundas do modelo transacional apresentado na Figura 3.

A estrutura apresentada na Figura 4 é chamada de modelo multidimensional e, neste contexto, é importante frisar três conceitos: tabela fato, medidas e dimensões. A tabela fato é onde encontram-se os valores numéricos que irão representar algum tipo de totalização, como os campos unidades_vendidas e valor_vendido da tabela vendas_fato. Esses campos é que são agrupados e visualizados pelas ferramentas OLAP de Businness Intelligence, os quais são conhecidos como medidas. Por fim, as dimensões são tabelas que possibilitam que as medidas sejam visualizadas de forma a possibilitar o gestor tomar decisões e/ou analisar os dados existentes na empresa. Por exemplo, com base na Figura 4 é possível agrupar os dados pela família dos produtos, pelo tipo da loja ou pelo dia da semana em que as vendas foram realizadas.

Existem basicamente dois tipos de modelos dimensionais: estrela (star scheme) e floco de neve (snow flake). No primeiro, as tabelas que representam as dimensões estão todas ligadas à tabela fato, o que o deixa mais simples e possibilita melhores formas de navegação nos dados pelas ferramentas OLAP, como o FastCube, por exemplo. Por outro lado, há um pouco de desperdício de espaço pelo fato das mesmas descrições serem repetidas em todos os registros, como na tabela produtos_classe. A Figura 4 é um exemplo de esquema de data warehouse que utiliza o modelo estrela.

Por outro lado, no modelo floco de neve as tabelas que representam as dimensões podem não estar ligadas diretamente à tabela fato. Por exemplo, na Figura 4 poderia existir a tabela cidades relacionada com a tabela de clientes e de lojas ao invés deste campo ser armazenado em formato texto diretamente nessas tabelas. Este modelo é mais complexo e, em geral, consome mais recursos computacionais para realizar as consultas, já que mais joins em SQL são necessários.

Criando o data warehouse

Esta seção tem o objetivo de mostrar os comandos em SQL para a construção do data warehouse apresentado na Figura 4, bem como explanar os campos existentes em cada uma das dimensões. A Listagem 1 mostra o script para a criação da dimensão clientes, e conforme abordado na seção anterior, os tipos de dados dos campos cidade e estado são caracteres, seguindo as definições do modelo estrela da modelagem multidimensional. A renda_anual também é representada por um campo alfanumérico e é composta por oito faixas de valores: R$ 0.0 – R$ 30.000, R$ 30.000 – R$ 50.000, R$ 50.000 – R$ 70.000, R$ 70.000 – R$ 90.000, R$ 90.000 – R$ 110.000, R$ 110.000 – R$ 130.000, R$ 130.000 – R$ 150.000 e R$ 150.000 +. Na base de dados transacional é utilizado um campo numérico para indicar o valor exato da renda, porém, no DW se faz necessária a conversão desses valores numéricos em faixas para facilitar as análises e agrupamentos. Similarmente, o campo escolaridade é composto pelos seguintes valores: Ensino médio incompleto, Ensino médio completo, Ensino superior incompleto, Ensino superior completo e Pós-graduação completo. Por fim, o campo gênero é composto somente pelos valores M para masculino e F para feminino.

1 create table dwclientes (
2      idcliente int primary key not null,
3      cidade varchar(15),
4      estado varchar(10),
5      renda_anual varchar(25),
6      genero char(1),
7      escolaridade varchar(26))

A Listagem 2 apresenta o script para a criação da dimensão lojas e os campos cidade e estado seguem a mesma lógica apresentada para a dimensão clientes. A única diferença é o campo tipo, que armazena os seguintes valores: Mercearia pequena, Mercearia grande, Supermercado, Supermercado de luxo, Supermercado gourmet e Sede da empresa. Similarmente aos campos cidade e estado, o campo tipo também é do tipo textual.

  1 create table dwlojas (
  2      idloja int primary key not null,
  3      tipo varchar(20),
  4      cidade varchar(15),
  5      estado varchar(10))

A Listagem 3, por sua vez, apresenta o comando SQL para a criação da dimensão relacionada aos produtos, sendo composta somente pela família, departamento e categoria dos produtos, todos textuais. Esse fato foi explanado nas seções anteriores e segue as diretrizes do modelo estrela da modelagem multidimensional.

  1 create table dwprodutos_classe (
  2      idproduto_classe int primary key not null,
  3      familia varchar(15),
  4      departamento varchar(30),
  5      categoria varchar(30))

A Listagem 4 mostra o script para criação da dimensão tempo, que é composta pelo dia da semana (domingo até sábado), pelo mês (janeiro até dezembro) e pelo quadrimestre do ano (Q1 até Q4). Todos esses campos são alfanuméricos, com exceção da chave primária idtempo que é do tipo inteiro e possui a propriedade para auto incremento (identity). Pode-se notar que as outras dimensões não possuem auto incremento, e isso se deve ao fato de que nas outras tabelas o valor da chave primária será importado das próprias tabelas da base transacional. O auto incremento é utilizado na dimensão tempo porque essa tabela não existe originalmente na base de dados.

1 create table dwtempo (
2      idtempo int primary key identity not null,
3      dia_semana varchar(15),
4      mes varchar(15),
5      quadrimestre char(2))

Por fim, a Listagem 5 apresenta o comando SQL para a criação da tabela fato, a qual possui as medidas unidades_vendidas e valor_vendido, bem como as chaves estrangeiras que fazem o relacionamento com as dimensões criadas anteriormente. É importante frisar que neste modelo as medidas já apresentam os valores totalizados para cada venda. Por exemplo, caso a venda número 14 contenha cinco produtos, na tabela dwvendas_fato serão armazenadas as respectivas totalizações para a quantidade vendida e o valor total referente a esses cinco produtos. Desta forma, elimina-se a necessidade de utilizar a tabela vendas_produto da Figura 3.

1 create table dwvendas_fato (
2      idvendas_fato int primary key not null,
3      idproduto_classe int foreign key references dwprodutos_classe (idproduto_classe),
4      idtempo int foreign key references dwtempo (idtempo),
5      idcliente int foreign key references dwclientes (idcliente),
6      idloja int foreign key references dwlojas (idloja),
7      unidades_vendidas int,
8      valor_vendido float )
  

Criando a interface gráfica e configurando o acesso aos dados no Delphi

As seções anteriores apresentaram a estrutura do banco de dados transacional e do data warehouse, bem como as principais diferenças entre ambos. Neste contexto, o objetivo das próximas seções é mostrar como importar os dados da base transacional e popular as dimensões e a tabela fato do DW. Esse procedimento será implementado via código e, para isso, a próxima etapa é a criação de uma aplicação do tipo VCL no Delphi (File > New > VCL Forms Application). A Figura 5 apresenta a janela principal (frmPrincipal), a qual contém cinco componentes TButton (btnClientes, btnLojas, btnProdutos, btnTempo e btnFato), um componente TFDConnection (conexao) e dois componentes TFDQuery (qryConsulta e qryInsercao) da biblioteca FireDAC. Cada botão realizará a importação dos dados das tabelas correspondentes do modelo transacional para as tabelas de dimensões e para a tabela fato, enquanto que o componente de conexão é o responsável por realizar a ligação com a base de dados do SQL Server. Por outro lado, os componentes TFDQuery são os responsáveis pelas consultas e inserções que serão realiza1das posteriormente. Esses dois componentes devem ter a sua propriedade Connection setada para o componente conexao.

Duplo clique no componente de conexão abre o editor de conexão do FireDAC, o qual deve ter a sua propriedade DriverID configurada para MSSQL. Os outros parâmetros que devem ser preenchidos são o Database com o nome da base de dados (Vendas), o Server com o valor localhost e a propriedade OSAuthent deve estar com o valor Yes. O SQL Server possui integração com a segurança do próprio Windows, e esse parâmetro informa que usuários autenticados no sistema operacional terão acesso à base de dados. Caso a instalação do SQL Server tenha um usuário e senha específicos, os campos User_Name e Password devem ser preenchidos no editor de conexão do FireDAC. Adicionalmente, a propriedade Login Prompt do TFDConnection deve ser definida como False para que a janela de login e senha não seja aberta a cada tentativa de conexão. A Figura 6 apresenta a janela de configuração.

Importando os clientes

O objetivo geral da importação dos dados é basicamente: (i) ler os dados da base transacional, (ii) realizar possíveis pré-processamentos e/ou ajustes nos dados e (iii) gravá-los no data warehouse. Para isso, a Listagem 6 apresenta o código fonte do procedimento que será executado ao clicar no botão para importação dos dados dos clientes. Pode-se notar na linha 6 que é adicionado na propriedade SQL da qryInsercao o comando insert com parâmetros para inserir os registros na dimensão de clientes (dwclientes). Por outro lado, na linha 7 é executada uma consulta que une a tabela de clientes com a tabela de cidades do modelo transacional, com o intuito de retornar os dados de acordo com as necessidades do data warehouse. Como existem 10.282 clientes nesta tabela, na linha 8 é definida uma estrutura de repetição para percorrer cada um dos registros existentes, executando assim os comandos para passagem dos valores retornados pela consulta por parâmetros. Entre as linhas 13 e 30 é executada uma operação de pré-processamento chamada de codificação, que consiste em mapear os valores numéricos existentes no campo renda do modelo transacional em intervalos. Esse processo é necessário devido ao fato de que existe uma grande variabilidade de rendas com valores únicos. Por exemplo, caso seja executado o comando SQL select distinct(renda) from clientes, serão obtidos 9.936 valores de rendas distintas, o que inviabilizaria a análise e agrupamentos dos clientes por este campo.

1 procedure TfrmPrincipal.btnClientesClick(Sender: TObject);
2 var
3  ARendaAnual: real;
4  ARotuloRendaAnual, ARotuloEscolaridade: string;
5 begin
6  qryInsercao.SQL.Add('insert into dwclientes (idcliente, cidade, estado, 
   renda_anual, genero, escolaridade) values (:idcliente, :cidade, :estado,
   :renda_anual, :genero, :escolaridade)');
7  qryConsulta.Open('select idcliente, cd.nome as cidade, estado, renda, genero,
   escolaridade  from clientes cl left outer join cidades cd on cl.idcidade = cd.idcidade');
8  while not qryConsulta.Eof do
9  begin
10    qryInsercao.ParamByName('idcliente').AsInteger :=   
      qryConsulta.FieldByName('idcliente').AsInteger;
11    qryInsercao.ParamByName('cidade').AsString := 
      qryConsulta.FieldByName('cidade').AsString;
12    qryInsercao.ParamByName('estado').AsString := 
      qryConsulta.FieldByName('estado').AsString;
13    ARendaAnual := qryConsulta.FieldByName('renda').AsFloat;
14    if (ARendaAnual >= 0) and (ARendaAnual <= 30000) then
15      ARotuloRendaAnual := 'R$ 0.0 - R$ 30.000'
16    else if (ARendaAnual > 30000) and (ARendaAnual <= 50000) then
17      ARotuloRendaAnual := 'R$ 30.000 – R$ 50.000'
18    else if (ARendaAnual > 50000) and (ARendaAnual <= 70000) then
19      ARotuloRendaAnual := 'R$ 50.000 – R$ 70.000'
20    else if (ARendaAnual > 70000) and (ARendaAnual <= 90000) then
21      ARotuloRendaAnual := 'R$ 70.000 – R$ 90.000'
22    else if (ARendaAnual > 90000) and (ARendaAnual <= 110000) then
23      ARotuloRendaAnual := 'R$ 90.000 – R$ 110.000'
24    else if (ARendaAnual > 110000) and (ARendaAnual <= 130000) then
25      ARotuloRendaAnual := 'R$ 110.000 – R$ 130.000'
26    else if (ARendaAnual > 130000) and (ARendaAnual <= 150000) then
27      ARotuloRendaAnual := 'R$ 130.000 – R$ 150.000'
28    else if (ARendaAnual > 150000) then
29      ARotuloRendaAnual := 'R$ 150.000 +';
30    qryInsercao.ParamByName('renda_anual').AsString := ARotuloRendaAnual;
31    qryInsercao.ParamByName('genero').AsString := qryConsulta.FieldByName
      ('genero').AsString;
32    case qryConsulta.FieldByName('escolaridade').AsInteger of
33      1: ARotuloEscolaridade := 'Ensino médio incompleto';
34      2: ARotuloEscolaridade := 'Ensino médio completo';
35      3: ARotuloEscolaridade := 'Ensino superior incompleto';
36      4: ARotuloEscolaridade := 'Ensino superior completo';
37      5: ARotuloEscolaridade := 'Pós-graduação completo';
38    end;
39    qryInsercao.ParamByName('escolaridade').AsString := ARotuloEscolaridade;
40    qryInsercao.ExecSQL;
41    qryConsulta.Next;
42  end;

Entre as linhas 32 e 38 da Listagem 6 é também executada a codificação dos dados numéricos em alfanuméricos por meio do comando case. Na base de dados transacional, cada categoria de escolaridade é representada por um número inteiro, enquanto que neste data warehouse estão sendo armazenadas as categorias em formato textual. Por fim, na linha 40 a instrução insert é executada e na linha 41 o cursor da consulta aponta para o próximo cliente a ser inserido na dimensão cliente.

Importando as lojas

Semelhante à importação dos clientes, o procedimento para importar as lojas é bastante similar e é mostrado na Listagem 7. A única diferença é a codificação dos tipos de lojas em descrições completas entre as linhas 10 e 23. Pode-se observar que o campo escolaridade na importação dos clientes é similar, porém, é representado de forma numérica, enquanto que para as lojas essa representação apresenta-se no formato de siglas. Optou-se por essa representação para demonstrar que em bases de dados transacionais os campos nem sempre podem estar padronizados, fato que torna necessária uma análise mais detalhada dos campos das tabelas.

  1 procedure TfrmPrincipal.btnLojasClick(Sender: TObject);
  2 var
  3  ATipoLoja, ARotuloTipoLoja: string;
  4 begin
  5   qryInsercao.SQL.Add('insert into dwlojas (idloja, tipo, cidade, estado) 
      values (:idloja, :tipo, :cidade, :estado)');
  6   qryConsulta.Open('select idloja, cd.nome as cidade, cd.estado, tipo 
      from lojas lj left outer join cidades cd on lj.idcidade = cd.idcidade');
  7   while not qryConsulta.Eof do
  8   begin
  9    qryInsercao.ParamByName('idloja').AsInteger := qryConsulta.
       FieldByName('idloja').AsInteger;
  10    ATipoLoja := qryConsulta.FieldByName('tipo').AsString;
  11    if (ATipoLoja = 'MG') then
  12      ARotuloTipoLoja := 'Mercearia grande'
  13    else if (ATipoLoja = 'MP') then
  14      ARotuloTipoLoja := 'Mercearia pequena'
  15    else if (ATipoLoja = 'SE') then
  16      ARotuloTipoLoja := 'Sede da empresa'
  17    else if (ATipoLoja = 'SP') then
  18      ARotuloTipoLoja := 'Supermercado'
  19    else if (ATipoLoja = 'SL') then
  20      ARotuloTipoLoja := 'Supermercado de luxo'
  21    else if (ATipoLoja = 'SG') then
  22      ARotuloTipoLoja := 'Supermercado gourmet';
  23    qryInsercao.ParamByName('tipo').AsString := ARotuloTipoLoja;
  24    qryInsercao.ParamByName('cidade').AsString := qryConsulta.
        FieldByName('cidade').AsString;
  25    qryInsercao.ParamByName('estado').AsString := qryConsulta.
        FieldByName('estado').AsString;
  26    qryInsercao.ExecSQL;
  27    qryConsulta.Next;
  28  end;
  29 end;

Importando os produtos

A Listagem 8 apresenta o código fonte para realizar a importação dos produtos. Na linha 3 foi declarada uma variável para armazenar a instrução SQL da consulta na base transacional. Pode-se observar na linha 5 que a consulta é longa, pois a seleção dos dados para o data warehouse é feita com base em todos os produtos existentes na base de dados (10.281 registros), e deve-se relacionar o produto com a categoria, a categoria com o departamento e o departamento com a família de produtos. Conforme abordado nas seções anteriores, o objetivo desta dimensão é apresentar os dados somente da categorização que existe entre os produtos. Isso se deve ao fato de que dependendo da base de dados, pode existir uma grande variedade de produtos, o que pode tornar as análises e agrupamentos mais difíceis para o analista.

  1 procedure TfrmPrincipal.btnProdutosClick(Sender: TObject);
  2 var
  3   ASQL: string;
  4 begin
  5   ASQL := 'select idproduto, pf.nome as familia, pd.nome as departamento, pc.nome as    
      categoria '
       + ' from produtos pr inner join produto_categorias pc on pr.idproduto_categoria =
       pc.idproduto_categoria ' 
       + ' inner join produto_departamentos pd on pc.idproduto_departamento = 
       pd.idproduto_departamento inner join produto_familias pf on 
         pd.idproduto_familia =
       pf.idproduto_familia';
  6  qryInsercao.SQL.Add('insert into dwprodutos_classe (idproduto_classe, familia, 
     departamento,
     categoria) values (:idproduto_classe, :familia, :departamento, :categoria)');
  7  qryConsulta.Open(ASQL);
  8  while not qryConsulta.Eof do
  9  begin
  10    qryInsercao.ParamByName('idproduto_classe').AsInteger := 
     qryConsulta.FieldByName('idproduto').AsInteger;
  11    qryInsercao.ParamByName('familia').AsString := qryConsulta.
     FieldByName('familia').AsString;
  12    qryInsercao.ParamByName('departamento').AsString := 
     qryConsulta.FieldByName('departamento').AsString;
  13    qryInsercao.ParamByName('categoria').AsString := 
     qryConsulta.FieldByName('categoria').AsString;
  14    qryInsercao.ExecSQL;
  15   qryConsulta.Next;
  16 end;
  17 end;

Similar às importações anteriores, na linha 8 é definida uma estrutura de repetição para percorrer os 10.281 produtos existentes na tabela da base transacional, ou seja, a dimensão dwprodutos_classe conterá esse mesmo número de registros, porém, o nome do produto foi suprimido. Por esse motivo, na linha 10 a chave primária da dimensão recebe o mesmo valor da chave primária da tabela de produtos. Por meio dessa importação, posteriormente o analista poderá agrupar os dados por essas categorias, por exemplo: existem três famílias de produtos (select distinct(familia) from dwprodutos_classe), 19 departamentos (select distinct(departamento) from dwprodutos_classe) e 45 categorias (select distinct(categoria) from dwprodutos_classe).

Importando os dados de tempo

Diferentemente das outras importações realizadas até o momento, para construir a dimensão tempo é necessário processar somente o campo data_venda da tabela de vendas do modelo transacional. Isso ocorre porque a dimensão tempo é formada somente pelo dia da semana, pelo mês e pelo quadrimestre, os quais são extraídos da data da venda. A Listagem 9 apresenta o código necessário para realizar essa operação, no qual é possível observar na linha 4 que é utilizada a função datepart para extrair o dia da semana e o quadrimestre, e a função month para extrair o mês. Essas funções retornam um número inteiro que corresponde a cada período, por exemplo: considerando o campo dia da semana, o valor 1 representa o domingo e o 7 representa o sábado. Essa mesma lógica é também utilizada para o mês e o quadrimestre. Na instrução SQL da linha 4 é também possível observar que é utilizado o comando distinct, que tem a função de buscar da tabela de vendas somente os registros únicos que pertençam ao mesmo tempo a um dia da semana, a um mês e a um quadrimestre. Esse processo reduz a quantidade de registros únicos nessa dimensão para 84, conforme pode ser observado caso a consulta da linha 4 seja executada no editor de consultas do SQL Server.

  1 procedure TfrmPrincipal.btnTempoClick(Sender: TObject);
  2 begin
  3   qryInsercao.SQL.Add('insert into dwtempo (dia_semana, mes, quadrimestre) 
      values (:dia_semana, :mes, :quadrimestre)');
  4   qryConsulta.Open('select distinct datepart(dw, data_venda) as dia_semana, 
      month(data_venda) as mes, datepart(qq, data_venda) as quadrimestre from vendas');
  5   while not qryConsulta.Eof do
  6   begin
  7     qryInsercao.ParamByName('dia_semana').AsInteger := qryConsulta.FieldByName('dia_semana')
      .AsInteger;
  8     qryInsercao.ParamByName('mes').AsInteger := qryConsulta.FieldByName('mes')
      .AsInteger;
  9     qryInsercao.ParamByName('quadrimestre').AsInteger :=  
        qryConsulta.FieldByName('quadrimestre').AsInteger;
  10    qryInsercao.ExecSQL;
  11    qryConsulta.Next;
  12  end;
  13 end;

Importando as vendas (tabela fato)

A importação dos dados das vendas é um pouco mais complexa do que as dimensões, pois é preciso associar as chaves estrangeiras e realizar as totalizações na tabela vendas_produto do modelo transacional. Para isso, foi definida uma variável do tipo record e mais três funções auxiliares na seção, conforme mostrado na Listagem 10.

  1 type
  2  TotalVenda = Record
  3    UnidadesVendidas: Integer;
  4    ValorTotal: Real;
  5  End;
   
  6 type
  7   TfrmPrincipal = class(TForm)
    ...
   
  8  private
  9     function GetIdTempo(ADiaSemana, AMes, AQuadrimestre: Integer): Integer;
  10    function GetIdProdutoClasse(AIdVenda: Integer): Integer;
  11    function GetTotalVenda(AIdVenda: Integer): TotalVenda;
  12 public
  13 end;

Para gerar a implementação das funções, deve-se pressionar ao mesmo tempo as teclas CRTL+SHIFT+C, para que elas sejam criadas na seção implementation do arquivo. A Listagem 11 apresenta o código das funções para buscar a classe do produto e os dados de tempo. A primeira função (GetIdProdutoClasse) recebe como parâmetro o identificador da venda (idvenda) e retorna a classe do produto relacionado a essa venda. Na linha 3 é criada uma query em tempo de execução, que executa o comando SQL definido na linha 6, retornando na linha 9 a classe do produto correspondente. Conforme explanado anteriormente, no projeto deste data warehouse é possível ligar uma venda somente a um produto, e por esse motivo na linha 6 é utilizado o comando top 1 para retornar o primeiro produto retornado pela consulta.

  1 function TfrmPrincipal.GetIdProdutoClasse(AIdVenda: Integer): Integer;
  2 begin
  3   with TFDQuery.Create(nil) do
  4   try
  5     Connection := conexao;
  6     SQL.Add('select idproduto_classe from dwprodutos_classe where idproduto_classe = 
        (select top 1 idproduto from vendas_produto where idvenda = :idvenda)');
  7     ParamByName('idvenda').AsInteger := AIdVenda;
  8     Open();
  9     Result := FieldByName('idproduto_classe').AsInteger;
  10  finally
  11    Free;
  12  end;
  13 end; 
  14 function TfrmPrincipal.GetIdTempo(ADiaSemana, AMes, AQuadrimestre: Integer): Integer;
  15 begin
  16   with TFDQuery.Create(nil) do
  17  try
  18    Connection := conexao;
  19    SQL.Add('select idtempo from dwtempo where dia_semana = 
        :dia_semana and mes = :mes and quadrimestre = :quadrimestre');
  20    ParamByName('dia_semana').AsInteger := ADiaSemana;
  21    ParamByName('mes').AsInteger := AMes;
  22    ParamByName('quadrimestre').AsInteger := AQuadrimestre;
  23    Open();
  24    Result := FieldByName('idtempo').AsInteger;
  25  finally
  26     Free;
  27  end;
  28 end;

Similarmente, a função GetIdTempo recebe como parâmetro o dia da semana, o mês e o quadrimestre, retornando na linha 24 o campo idtempo da dimensão tempo. Isso é feito por meio da consulta definida na linha 19, que filtra os registros com base nos parâmetros recebidos. A Listagem 12 apresenta o código que realiza a totalização da venda, pois como explanado anteriormente, a tabela fato armazena somente os totais das unidades vendidas e do valor vendido que são campos da tabela vendas_produto da Figura 3. Para isso, a função recebe como parâmetro o idvenda e retorna uma variável do tipo record TotalVenda que foi declarado na Listagem 10. O record possui dois campos: um para representar as unidades e outro para representar o valor total, e seu objetivo é facilitar o retorno da função. O comando SQL da linha 8 faz a soma das unidades vendidas, bem como realiza a multiplicação do valor vendido pelas unidades para obter o valor total. Nas linhas 12 e 13 o record recebe o resultado da consulta e na linha 14 é feito o retorno da função.

  1 function TfrmPrincipal.GetTotalVenda(AIdVenda: Integer): TotalVenda;
  2 var
  3   ATotal: TotalVenda;
  4 begin
  5   with TFDQuery.Create(nil) do
  6   try
  7      Connection := conexao;
  8      SQL.Add('select sum(unidades_vendidas) as unidades_vendidas, sum(valor_vendido *
         unidades_vendidas) as valor_vendido from vendas_produto where idvenda = :idvenda');
  9      ParamByName('idvenda').AsInteger := AIdVenda;
  10     Open();
  12     ATotal.UnidadesVendidas := FieldByName('unidades_vendidas').AsInteger;
  13     ATotal.ValorTotal := FieldByName('valor_vendido').AsFloat;
  14     Result := ATotal;
  15   finally
  16     Free;
  17   end;
  18 end;

Por fim, a Listagem 13 realiza o processo de importação das vendas. Na linha 7 é definido o comando SQL que faz a busca na tabela de vendas da base transacional, enquanto que na linha 8 é definido o comando para inserção dos dados. O processo é bastante similar às outras importações realizadas, com exceção das linhas 13 a 17, que buscam a classe do produto e associam ao parâmetro de inserção. Uma estrutura condicional foi feita para verificar se existe alguma venda sem produtos cadastrados, e, nesta situação, o parâmetro da classe do produto é setado para nulo. Neste exemplo estão sendo carregados para o data warehouse todas as vendas, independente de terem produtos associados. Na base de dados de exemplo, existem mais de 100 vendas nesta situação, e isso pode gerar problemas de interpretação ou até mesmo representar inconsistências presentes na base de dados transacional. Em uma aplicação comercial, esses dados teriam que ser avaliados pelo analista, e possivelmente ajustados ou excluídos. Entre as linhas 18 e 23 são chamadas as funções criadas nas Listagens 11 e 12, as quais retornam o cliente, a loja e o tempo.

  1 procedure TfrmPrincipal.btnFatoClick(Sender: TObject);
  2 var
  3   ASQL: string;
  4   ATotalVenda: TotalVenda;
  5   AIdProdutoClasse: Integer;
  6 begin
  7   ASQL := 'select idvenda, idcliente, idloja, datepart(dw, data_venda) as 
      dia_semana, month(data_venda) as mes, datepart(qq, data_venda) 
      as quadrimestre from vendas';
  8   qryInsercao.SQL.Add('insert into dwvendas_fato (idvendas_fato, idproduto_classe,
       idtempo, idcliente, idloja, 
      unidades_vendidas, valor_vendido) values (:idvendas_fato, :idproduto_classe, :idtempo, 
      :idcliente, :idloja, :unidades_vendidas, :valor_vendido)');
  9   qryConsulta.Open(ASQL);
  10   while not qryConsulta.Eof do
  11   begin
  12     qryInsercao.ParamByName('idvendas_fato').AsInteger := 
         qryConsulta.FieldByName('idvenda').AsInteger;
  13     AIdProdutoClasse := GetIdProdutoClasse(qryConsulta.
         FieldByName('idvenda').AsInteger);
  14     if (AIdProdutoClasse > 0) then
  15       qryInsercao.ParamByName('idproduto_classe').AsInteger := AIdProdutoClasse
  16     else
  17       qryInsercao.ParamByName('idproduto_classe').Value := null;
  18     qryInsercao.ParamByName('idtempo').AsInteger := 
         GetIdTempo(qryConsulta.FieldByName('dia_semana')
         .AsInteger, qryConsulta.FieldByName('mes').AsInteger, 
         qryConsulta.FieldByName('quadrimestre').AsInteger);
  19     qryInsercao.ParamByName('idcliente').AsInteger := 
         qryConsulta.FieldByName('idcliente').AsInteger;
  20     qryInsercao.ParamByName('idloja').AsInteger := qryConsulta.
         FieldByName('idloja').AsInteger;
  21     ATotalVenda := GetTotalVenda(qryConsulta.FieldByName('idvenda').AsInteger);
  22     qryInsercao.ParamByName('unidades_vendidas').AsInteger := 
         ATotalVenda.UnidadesVendidas;
  23     qryInsercao.ParamByName('valor_vendido').AsFloat := ATotalVenda.ValorTotal;
  24     qryInsercao.ExecSQL;
  25     qryConsulta.Next;
  26   end;
  27 end;

A importação de dados de uma base transacional para um data warehouse pode ser uma tarefa trabalhosa, principalmente se a quantidade de dimensões e registros for grande. Como visto no exemplo desenvolvido, são necessários vários procedimentos para realizar essa operação. Somente devem ser carregados no DW dados consistentes e que possam representar algum tipo de informação útil, portanto, em bases comerciais deve-se tomar um maior cuidado antes de transferir os dados. Neste contexto, diversas outras formas de pré-processamento podem ser realizadas, bem como a aplicação de algoritmos de filtros para escolher quais são os campos mais relevantes, a identificação e a redução de hierarquias entre os atributos, a limpeza de dados inconsistentes, o tratamento de informações ausentes e a normalização. Alguns desses métodos ou todos eles podem e devem ser aplicados, dependendo da necessidade da base de dados.