Este artigo abrange alguns conceitos do ambiente de Business Intelligence, seu mapeamento a produtos Microsoft e relaciona algumas decisões de arquitetura e implementação adotadas na construção de um ambiente de BI numa instituição financeira brasileira.
Introdução
Cada vez mais cresce o número de negócios e corporações que estão incorporando aplicações analíticas aos seus sistemas. Os sistemas tradicionais OLTP (Online Transaction Processing) geram quantidades massivas de dados; transformar tais dados em informação que possa ser consumida e facilitar a tomada de decisões é uma necessidade para manter a competitividade no mercado. Sem o auxílio de indicadores críticos fornecidos no tempo certo, os tomadores de decisão passam a utilizar seu tempo organizando dados, ao invés de extrair o significado deles. Quando bem implementados, os sistemas analíticos oferecem aos tomadores de decisão as ferramentas necessárias para realização de análises (tais como tendência e comparações) dos dados em vários níveis de granularidade, permitindo visualizar os detalhes, os relacionamentos entre os dados e explorar novas possibilidades.
Este artigo descreve o ambiente de Business Intelligence construído em uma instituição financeira brasileira.
Inicialmente, o Data Warehouse atenderá as necessidades de relatórios enviados para o Banco Central, mostrando detalhes de operações financeiras e seu nível de risco.
Em uma segunda etapa, os usuários poderão usufruir das informações gerenciais e análise de tendência de comportamento das operações ao longo do tempo, comparando o nível de risco com o resultado obtido.
O Projeto REAL será utilizado no projeto como referência de arquitetura e implementação sempre que possível. Este artigo não pretende substituir o Projeto REAL, pelo contrário, é um complemento aos itens que não foram abordados ou que exigiram adaptação para o ambiente em questão.
O projeto REAL - Reference implementation, End-to-end, At scale, and Lots of users - é uma implementação de referência para sistemas de business intelligence (BI) que utilizam dados reais em larga escala de um cliente real. O objetivo é a descoberta das melhores práticas para a criação de sistemas de BI utilizando o SQL Server 2005 e a construção de um sistema que demonstre tais práticas. O projeto não é somente uma demonstração — ele é criado para operação em produção. São tratados itens como:
Desenho de esquemas – tanto relacional quanto os utilizados no Analysis Services;
Implementação dos processos de extração, transformação e carga de dados (ETL);
Desenho e implantação de sistemas front-end, tanto para relatórios quanto para Análise Interativa;
Dimensionamento de sistemas para produção;
Gerenciamento e manutenção de sistemas de maneira contínua, incluindo atualização incremental de dados.
Para as informações mais detalhadas, veja o site Project REAL.
Filosofias de Data Warehouse
No campo de data warehousing, freqüentemente ouvimos discussões sobre a filosofia de Bill Inmon e de Ralph Kimball, duas das maiores autoridades no assunto. Resumidamente:
Paradigma Bill Inmon: o Data Warehouse é parte de um sistema completo de Business Intelligence. Uma empresa possui um data warehouse, de onde os datamarts extraem sua informação. No data warehouse, as informações são armazenadas em terceira forma normal.
Paradigma Ralph Kimball: o Data Warehouse é o conglomerado de todos os datamarts da empresa. A informação sempre é armazenada em modelo dimensional.
Não há certo ou errado entre as duas idéias, na medida em que representam filosofias diferentes para o Data Warehouse. Muitas empresas estão mais próximas da idéia de Ralph Kimball, porque os data warehouses começam como um esforço departamental e se originam como datamarts. Somente quando mais datamarts são construídos, ocorre a evolução para o data warehouse.
Além das questões técnicas discutidas nas seções anteriores, algumas empresas também levam em consideração o prazo para disponibilização de dados para utilização do usuário. Quando a prioridade é de dados detalhados pode se optar por iniciar a construção pelo Data Warehouse. Se, num primeiro momento, a necessidade maior foi por informações sumarizadas, o trabalho pode ser iniciado pelo Data Mart.
Uma discussão mais detalhada sobre as características de cada uma das duas filosofias pode ser encontrada em Inmon Vs. Kimball – Data Warehouse: Similarities and Differences of Inmon and Kimball.
Para o Project Real, adota-se a filosofia Ralph Kimball, enquanto que, devido às exigências de negócio e padronização da companhia, a arquitetura do Ambiente de BI da companhia, segue-se a filosofia de Fábrica de Informações, proposta por Bill Inmon. Veja detalhes em Corporate Information Factory – A conceptual Architecture for Business Intelligence.
Ciclo de Vida entre ODS, Data Warehouse e Data Mart
A Figura 1 mostra o diagrama da arquitetura utilizada:
As informações vindas dos sistemas transacionais são carregadas diariamente no ambiente de ODS (Operational Data Store), através de arquivos texto, com dados do dia anterior;
O ambiente ODS armazena informações dos dias úteis do último mês, com dados detalhados de todas as operações;
No último dia do mês, a posição do mês corrente é transferida para a área de Data Warehouse, sem nenhuma transformação;
Para demandas operacionais e gestão tática, são gerados modelos dimensionais no ambiente de Data Mart, com dados vindos do ODS ou do Data Warehouse;
Os cubos modelados no Analysis Services – que é o servidor UDM (Unified Dimensional Model) – são carregados com informações vindas do Data Mart;
Relatórios são disponibilizados para o usuário no Reporting Services. O desenvolvedor pode construir os relatórios utilizando como fontes de dados:
a. Tabelas de bases relacionais de Data Mart, com utilização de Transact-SQL;
b. Cubos de bases multidimensionais do Analysis Services, com utilização de MDX;
O usuário final pode realizar consultas através de:
a. Reporting Services, que possui dois modos de utilização:
i. Relatórios previamente construídos pelo Report Designer (Visual Studio), via http, pelo Internet Explorer;
ii. Relatórios construídos pelo próprio usuário, através do Report Builder, para consultas ad-hoc;
b. Excel, que através da Tabela Dinâmica, permite o acesso a cubos, a construção de relatórios e exploração de dados de forma autônoma e flexível, com opção de drillthrough para a fonte relacional.
Figura 1: Ciclo de Vida entre ODS, Data Warehouse e Data Mart
Arquitetura Lógica
A infra-estrutura Tecnológica criada para o projeto em questão é resultante da identificação e definição dos componentes necessários para suportar as necessidades, quanto à aquisição, integração, armazenamento e disponibilização dos dados, e manutenção dos metadados. A plataforma é totalmente baseada em plataforma MS SQL Server 2005, e atende aos requisitos de alta disponibilidade e tolerância a falhas. O desempenho e escalabilidade devem atender os volumes projetados para a ordem de grandeza de terabytes. O Data Warehouse irá armazenar cerca de 8 TB, o ambiente ODS 4 TB e o Data Mart 2 TB.
Para suporte a tolerância a falhas e escalabilidade:
Todos os servidores possuem processadores 64-bit e utilizam sistema Operacional Windows Server 2003 Enterprise Edition;
Os servidores SQL Server 2005 foram instalados com suporte a failover cluster, tanto para os servidores relacionais que vão armazenar os Data Warehouses e Data Marts, quanto os que forem hospedar os ODSs;
Os servidores Analysis Services foram instalados com suporte a failover cluster;
Os servidores que hospedarão Reporting Services foram configurados como Web Farm, em Load Balancing;
Os servidores que hospedarão o SSIS (SQL Server Integration Services) também foram configurados com suporte a failover cluster.
Extração, Transformação e Carga (ETL) de Dados
O processo de extração, transformação e carga (ETL) talvez seja o componente de BI que envolva mais riscos em um projeto. As diversas fontes heterogêneas de sistemas transacionais, a uniformização de domínios, o grande volume de dados a serem transformados, são alguns dos desafios a serem considerados.
Para o projeto, utilizou-se, sempre que possível, a implementação do Project REAL como referência para construção dos diversos pacotes do SQL Server Integration Services (SSIS). Em algumas ocasiões, foi necessária a elaboração de soluções específicas, que descreveremos nesta seção.
A extração de dados pode seguir dois modelos básicos, o modelo Push e o modelo Pull mais uma terceira opção, combinando os dois modelos:
Modelo Push: O sistema origem toma iniciativa no processo de extração, que tipicamente resulta em extração e movimentação de dados para a área de Staging ou para arquivos texto;
Modelo Pull: O banco de dados de staging toma iniciativa do processo de extração, emitindo comandos SQL para extrair dados dos sistemas origem;
Modelo Híbrido: Neste modelo, o sistema origem extrai os dados dos sistemas origem e armazena na Staging Area. O sistema de DW então faz a carga a partir da Staging Area a intervalos regulares de tempo.
O Project REAL utiliza o modelo Pull, onde é necessário conhecimento do modelo de dados do sistema transacional com as devidas permissões para leitura de dados.
Para diminuir os riscos envolvidos no projeto em questão, adotou-se o modelo Push, devido à complexidade dos sistemas transacionais, que envolvem plataformas heterogêneas, diversos gestores de negócio e equipes de TI variadas. Todos os dados foram disponibilizados em arquivos texto.
Tratamento de Arquivos Texto
Duas questões surgiram para o tratamento de arquivos texto:
1. O tratamento diferenciado da primeira linha do arquivo texto, que possui um layout diferente das linhas de detalhe. Esse cabeçalho contém informações diversas, inclusive o número total de linhas no arquivo;
-
2. O reaproveitamento dos tratamentos de cabeçalho e de finalização dos arquivos.
Public Sub Main()
Dts.TaskResult = Dts.Results.Failure
End Sub
Dim sr As System.IO.StreamReader = New System.IO.StreamReader("c:\oper.txt")
Dim header As String, nRows As String, tpLinha As String, FileName As String
If Dts.Variables.Contains("FileName") = True Then
FileName = CType(Dts.Variables("FileName").Value, String)
End If
header = sr.ReadLine()
tpLinha = header.Substring(0, 2)
nRows = header.Substring(2, 2)
If tpLinha <> "00" Then
' header precisa ter linha começada com 00
End If
If Dts.Variables.Contains("ErrMsg") = True Then
Dts.Variables("ErrMsg").Value = "Header Inválido"
End If
Return
…
Dts.TaskResult = Dts.Results.SuccessListagem 1: Script para tratamento do Cabeçalho
Para o primeiro caso, foi necessária a utilização de uma script task do SSIS para tratamento diferenciado da linha de cabeçalho, armazenando as informações importantes em variáveis do pacote SSIS – Listagem 1. O tratamento das outras linhas de detalhes é implementado em um Data Flow padrão, com o cuidado de configurar a conexão do arquivo texto para ignorar a primeira linha – Figura 3.
Figura 3: Configuração para ignorar a primeira linha
Para reaproveitamento das regras de tratamento de início e fim de arquivo, foram criados pacotes padrão denominados Consiste_Header e Finaliza_Arquivo, que devem ser chamados por um pacote Master. Veja Figura 4.
Comunicação entre Pacotes
Teoricamente, todo o processo de ETL pode ser implementado em um único pacote SSIS. Obviamente, não recomendamos esta abordagem. Mas a decomposição do processo em diversos pacotes traz algumas questões que são endereçadas de maneira bastante didática no Webcast de Joy Mundy do Kimball Group, Putting It Together: Moving Beyond the Basics of SQL Server 2005 Integration Services. Os principais pontos de atenção:
Utilização de pacote Master para gerenciamento de fluxo, onde são coordenados os pacotes para início, meio e fim do processo de cada arquivo;
Comunicação entre pacotes Master e pacote Filho: no sentido do Pai para o Filho, a comunicação é facilmente configurável através de variáveis comuns aos dois pacotes. Já no sentido do pacote Filho para o pacote Master, não existe comunicação direta, já que as variáveis são passadas por valor e não por referência. As alternativas utilizadas para equacionar este problema são:
Criar uma variável do tipo Object, que será mapeada para um Recordset, gerando o efeito de variável passada por referência, utilizada no projeto em questão;
Utilização de uma tabela do Banco de Dados relacional como área de comunicação entre os pacotes.
Uma série de cuidados foi tomada para evitar problemas de desempenho nos pacotes SSIS. Listaremos os mais relevantes:
Remova colunas que não são utilizadas no fluxo;
Para um grande número de linhas que não existirão na tabela de dimensão, considere a utilização de lookup antes do SCD (Slow Changing Dimension), sem esquecer-se de configurar a task de Lookup para utilização de cache – Figura 6;
Utilize Fast Parse para colunas que possuam tipos suportados pelo Fast Parse;
Para transformações que usam condições baseadas em colunas da fonte OLEDB ou ADO.Net, considere o uso de filtro nativo da base relacional para remover linhas desnecessárias do fluxo;
Revise a configuração Fast Load no adaptador destino – Figura 7. Fast loads são 10-100 vezes mais rápidos que linha-a-linha.
Figura 5: Configuração para Fast Load
Mesmo tomando estes cuidados, podem surgir questões de desempenho. O Webcast SQL Server 2005 Integration Services-Performance Scalability apresenta a Estratégia OVAL, para detecção de gargalos e melhoria de desempenho de pacotes SSIS, segundo a qual devemos considerar os seguintes fatores:
Operações: Qual lógica deve ser aplicada aos dados?
Volume: Quantos dados devem ser processados?
Aplicação: Qual aplicação é mais adequada para essa lógica, com esse volume de dados. Por exemplo, é melhor ordenar os dados utilizando Transact-SQL ou Integration Services?
Localização: Onde a aplicação deve ser executada? Num servidor compartilhado ou num servidor dedicado?
Uma utilização desse ambiente de BI é a extração de dados para envio ao Banco Central do Brasil; esses relatórios são gerados no Reporting Services. Além disso, os usuários podem consultar informações gerenciais disponibilizadas em cubos do Analysis Services, via Report Builder.
Navegação entre os ambientes DW, ODS e DM
Do ponto de vista do usuário, é conveniente que todas as informações estejam disponíveis de maneira ágil, flexível e no formato adequado.
Para a navegação entre os ambientes de ODS, DW e DM, a plataforma Microsoft conta com as seguintes opções:
Comando Drillthrough do MDX;
Drillthrough por Hyperlink de relatórios do Reporting Services.
Comando Drillthrough do MDX
O Multidimensional Expressions (MDX) implementa o comando DRILLTHROUGH para recuperação de um conjunto de linhas de uma fonte de dados que foram utilizados na composição do valor de uma célula do cubo. Para definição de uma Action de drillthrough, utiliza-se o Business Intelligence Development Studio, painel Actions do Cube Designer, onde podem ser especificados "name", "target", "condition", e as colunas que serão devolvidas pelo comando DRILLTHROUGH.
O exemplo a seguir mostra o uso do comando DRILLTHROUGH onde o nível de Folhas das hierarquias Store, Product e Time do Cubo Stores são consultados e devolve as colunas [Department MeasureGroup].[Department Id] e [Employee].[First Name].
O comando DRILLTHROUGH é utilizado internamente por uma aplicação cliente do Analysis Services, como é o caso do Excel 2007.DRILLTHROUGH Select {Leaves(Store), Leaves(Product), Leaves(Time),*} on 0
From Stores
RETURN [Department MeasureGroup].[Department Id], [Employee].[First Name]
O comando de DRILLTHROUGH depende do modelo estrela do banco de dados relacional que deu origem ao cubo, para que possam ser devolvidas as linhas que compuseram determinada célula de um cubo. Isto significa que toda informação para a qual se deseja fazer drillthrough deve existir no UDM, ou seja, deve ser parte de alguma dimensão ou medida de algum cubo.
Dessa forma, limita-se a navegação por comando MDX DRILLTHROUGH do Analysis Services apenas ao ambiente de Data Mart.
Vale lembrar que, neste caso, é necessário que o modelo estrela da base relacional possua as informações na menor granularidade requerida pelo usuário.
Drillthrough por hyperlink do Reporting Services
Uma opção para a navegação entre os ambientes de Data Mart, ODS e eventualmente Data Warehouse, é a utilização do recurso de Hyperlink action disponível no Reporting Services. Através dele, pode ser feita a navegação entre os diversos ambientes, sem a necessidade do comando MDX DRILLTHROUGH.
Esta opção é bastante flexível, permitindo que sejam criadas consultas em bases do Analysis Services utilizando MDX ou em bases do SQL Server utilizando Transact-SQL.
Figura 7: Hyperlink Action do Reporting Services
Em contrapartida, ao utilizar a flexibilidade de acesso direto às bases relacionais, sem passar pelo Analysis Services, não podem ser utilizadas as características do UDM como ponto de unificação de bases relacionais e multidimensionais.
Reporting Services
Para as informações pré-definidas e que serão consultadas de maneira freqüente, foram construídos relatórios do Reporting Services, com acesso às bases multidimensionais.
A fim de eliminar a necessidade de intervenção da equipe de TI para alteração ou criação de relatórios, permitimos que o usuário crie seus relatórios de maneira autônoma, utilizando o Reporting Builder.
Report Builder
O primeiro passo para utilização do Report Builder é a disponibilização de um Report Model, criado por um profissional de TI e disponibilizado para os usuários de negócio.
Para a criação de um Report Model, devem ser seguidos os seguintes passos:
-
Remover atributos que não devem estar no Report Model;
Alterar nomes não amigáveis;
Usar Folders para organização de Entidades, Atributos e Roles;
-
Criar atributos calculados;
Remover agregações sem sentido;
Adicionar descrições;
Criar perspectivas para as áreas de negócio.
Se a fonte de dados para o Report Model for um banco relacional, o Business Intelligence Development Studio do SQL Server 2005 traz um modelo de projeto para criação do modelo e os passos acima são executados diretamente.
Para este projeto de DW, onde a fonte são cubos do Analysis Services, o Report Model é criado automaticamente pelo Reporting Services via Report Manager – Figura 8 - ou via Management Studio. Desta forma, os passos descritos acima devem ser executados indiretamente, através da manipulação dos atributos no Analysis Services.
Figura 8: Geração de Report Model pelo Report Manager
Usar Folders para organização de Entidades, Atributos e Roles: Configurar a Propriedade AttributeHierachyDisplayFolder do Atributo da Dimensão;
Criar atributos calculados: Configurar a Propriedade AssociatedMeasureGroup do Membro Calculado;
Adicionar descrições: Configurar Propriedade Description do Atributo da Dimensão.
Figura 9: Propriedades AssociatedMeasureGroup e InstanceSelection
Excel
O Excel 2007 será disponibilizado para o perfil de Power Users, que necessitam de consultas ad-hoc para investigação de cenários de negócios não contemplados em relatórios criados com o Reporting Services.
Existem melhorias importantes do Excel 2007 para acesso a dados do Analysis Services em relação às versões anteriores, por exemplo:
Drillthrough nativo para visualização dos detalhes dos fatos que compuseram determinado valor de célula na Tabela Dinâmica.
Suporte a Key Performance Indicators (KPI) para visualização de metas e tendências.
Formatação Condicional para colunas, com visualização de barras e escala de cores.
Decision Trees,Naïve Bayes,Clustering,Time Series,Neural Networks,Association RulesO recurso do Lift Chart permite que o modelo seja treinado contra uma base de casos de testes, para comparação do algoritmo mais adequado ao modelo.
Figura 11: Visualização de Forecast de Vendas no Excel 2007 com suporte para Data Mining
Figura 12: Lift Chart com comparação de Algoritmos
Conclusão
Este projeto permitiu que as diversas equipes envolvidas – infra-estrutura, DBAs, Equipe de BI, Equipe de DW, Equipe de Gestão de Negócio, Fábrica de Software e Usuário Final – tomassem contato com as tecnologias disponíveis no MS SQL Server 2005.
Durante os trabalhos foram detectados pontos de atenção para o ambiente da empresa e foram gerados padrões de utilização de cada ferramenta para adequação às normas e padrões da companhia.
O ambiente de Business Intelligence gerado mostrou que o MS SQL Server 2005 possui as tecnologias exigidas para atender às demandas de um ambiente corporativo, atendendo aos requisitos de funcionalidade, desempenho, escalabilidade e tolerância a falhas.
Referência: http://msdn.microsoft.com/pt-br/library/cc517991.aspx
Tags: BI, Business, Intelligence, OLAP, SQL, BI, DW, SQL Server, DBA, OLTP, RAL, ITL, MART, Alexandre Ricardo Nardi, Cláudio Chiba, SQL Server 2005
Nenhum comentário:
Postar um comentário