Novabase - Microsoft BI Arquitectura BI na área de Serviços Financeiros

1. Novabase - Microsoft BIArquitectura BI na área de Serviços Financeiros .: 1 :.

2. Apresentações Hugo Matos hugo.azevedo.matos@novabase.pt Consultor da Novabase há 4 anos, especializado na área de BusinessIntelligence Background: Desenho e implementação de projectos de BusinessIntelligence: Banco de Portugal, EPAL, SonaeSierra, BES. Rafael Augusto rafael.augusto@novabase.pt Consultor da Novabase há 4 anos, especializado na área de Business Intelligence Background: Desenho e implementação de projectos de Business Intelligence: ACSS, Bristol Meyers Squibb, Barclays, CP, Banco de Portugal .: 2 :.

3. Agenda • Caso de Estudo: Data WarehouseEstatístico • Enquadramento • Arquitectura do Negócio • Arquitectura Funcional • Desafios Técnicos • Caso de Estudo: Problemas de performance no SSAS • Enquadramento • Situação Inicial • Problema detectado • Recomendações (em conjunto com a Microsoft) .: 3 :.

4. DW Estatístico – Enquadramento Missão do Departamento de Estatística • Elaboração das estatísticas monetárias, financeiras, cambiais e da balança de pagamentos Problema / Necessidades • Evolução do contexto e das necessidades do negócio • Obsolescência ou precariedade das soluções aplicativas existentes • Necessidade de integração da informação das várias áreas • Dificuldade em fornecer informação a outros departamentos Abordagem • Sistema de Informação Estatística - Arquitectura de BusinessIntelligence: • Data warehouse que garanta a centralização da informação • Sistema central de gestão de dados de referência • Plataforma tecnológica comum .: 4 :.

5. DW Estatístico – Arquitectura do Negócio Cadeia de Valor da Informação Estatística • Aquisição: • Extracção de dados, processos de validação, correcção e transformação • Produção • Controlo de qualidade, análises de evolução temporal, detecção de valores atípicos, estimativa de valores omissos, cálculo de informação derivada • Exploração • Interpretação dos dados, elaboração dos produtos estatísticos finais, realização de estudos específicos • Difusão / Disseminação • Selecção e preparação dos conteúdos, divulgação da informação estatística • Difusão • Exploração • Produção • Aquisição .: 5 :.

6. DW Estatístico – Arquitectura Funcional de BI Componentes Funcionais do Sistema Data Entry Data Quality DataIntegration ManagedReporting AdHocReporting Multidimensional Database Reference Data Management / MetadataManagement BI Portal Dashboards RelationalDatabase Time Series AnalyticViews Reports .: 6 :.

7. DW Estatístico – Desafios Técnicos Problema • Exploração analítica do sistema de responsabilidades de crédito • 4 modelos analíticos: 6 métricas * 25 dimensões • Volume de informação: 30 milhões de linhas / mês • Histórico de informação: 24 meses 800.000.000 linhas  600.000 MB Desafios • Como gerir elevado volume de informação? • Como garantir a performance dos processos de integração e da exploração analítica? .: 7 :.

8. DW Estatístico – Desafios Técnicos Abordagem Modelo relacional • Particionamento mensal das tabelas • Filegroups distintos para cada partição • Rotação de partições (slidingwindowscenario) • Criação de índices (análise do plano das queries) • Actualização das estatísticas Processos ETL • Integração incremental diária Modelos analíticos • Particionamento mensal de measuregroups • Desenho de agregações • Processamento diário dos últimos 3 meses • Processamento semanal de todos os dados .: 8 :.

9. Agenda • Caso de Estudo: Data WarehouseEstatístico • Enquadramento • Arquitectura do Negócio • Arquitectura Funcional • Desafios Técnicos • Caso de Estudo: Problemas de performance no SSAS • Enquadramento • Situação Inicial • Problema detectado • Recomendações (em conjunto com a Microsoft) .: 9 :.

10. Performance SSAS – Enquadramento Disponibilização de Cubos Interna ProClarity Desktop Professional BD Internas Externa Disponibilização de Quadros pré-definidos ProClarity Analytics Server ... BD Externas Pesquisas e Relatórios Ad-hoc Fontes Exploração ETL Plataforma Business Intelligence Utiliz. .: 10 :.

11. Performance SSAS – Situação Inicial • Legenda: • PC: dimensão do tipo Parent-Child (http://technet.microsoft.com/en-us/library/ms174846.aspx ) • Regular: dimensão do tipo regular (http://msdn.microsoft.com/en-us/library/ms175439.aspx ) • Non Aggregatable: dimensão não tem membro All, ou seja, não agrega para um membro totalizador. • (http://msdn.microsoft.com/en-us/library/ms174497.aspx ) .: 11 :.

12. Performance SSAS – Problema detectado Problema de performance em Run-time ao executar queries. Foi aberto um caso de suporte com a Microsoft Posteriormente, optou-se por migrar a solução para SQL2008 .: 12 :.

13. Performance SSAS – Problema detectado Da Análise mais aprofundada ao problema (SQL Profiler) obtiveram-se algumas observações: • O motor analítico do SSAS2000 é superior ao SSAS2008 em algumas queries. • Grande parte do tempo em Serialize Cells • CalculationPassValue e NonemptyCrossJoin com impacto significativo • O produto cartesiano das dimensões devolve um número elevado de células vazias. .: 13 :.

14. Performance SSAS – Agregações Parent Parent Parent Childs Childs Childs .: 14 :.

15. Performance SSAS – Mais detalhes Análise individual dos componentes para verificar o que realmente tem impacto na performance: .: 15 :.

16. Performance SSAS – Solução Transformar as dimensões Pai-filho– (CommonTableExpressions) Criação de relacções entre atributos Optimizações no script do cubo Construção de agregações Desenvolvimento de um mecanismo de cache warmUp .: 16 :.

17. Performance SSAS – Recomendações A partir da versão 2005 do AS o motor passou a operar em bloccomputation em vez de cell-by-cell. Em casos complexos como este é aconselhavel passar parte da lógica para o ETL. Evitar dimensões Pai-filho, apesar de serem muito úteis. Dar prioridade ao NonEmptyCrossJoin em vez  do uso do normal produto cartesiano ( * ). Quanto ao Script do Cubo: • Utilizar ao máximo funções como SCOPE e NON_EMPTY_BEHAVIOUR. • Evitar o acesso às memberproperties usando a função Properties(“Nome Propriedade”). • Deve ser usada sempre que possível a função Filter() de forma a filtrar membros desnecessários em conjuntos que sejam alvo de operações ( CrossJoins, Unions, etc…); Outras recomendações em OLAP Design BestPractices for AnalysisServices2005 .: 17 :.

18. Performance SSAS – Referências Performance Improvements for MDX in SQL Server 2008 Analysis Services(http://msdn.microsoft.com/en-us/library/bb934106.aspx) Analysis Services Query Performance Top 10 Best Practices(http://technet.microsoft.com/en-us/library/cc966527.aspx) Dimensão do tipo Parent-Child(http://technet.microsoft.com/en-us/library/ms174846.aspx) Dimensão do tipo regular(http://msdn.microsoft.com/en-us/library/ms175439.aspx) Dimensão que não agrega para um membro totalizador(http://msdn.microsoft.com/en-us/library/ms174497.aspx) OLAP Design Best Practices for Analysis Services 2005(http://technet.microsoft.com/en-us/library/cc966399.aspx) SQL Server Best Practices Article: Identifying and Resolving MDX Query Performance Bottlenecks in SQL Server 2005 Analysis Services(http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=975c5bb2-8207-4b4e-be7c-06ac86e24c13) Commom Table Expressions(http://msdn.microsoft.com/en-us/library/ms190766.aspx) Criação de relações entre atributos(http://msdn.microsoft.com/en-us/library/ms174557.aspx http://www.sqlserveranalysisservices.com/OLAPPapers/AttributeRelationships.htm) Ragged Dimensions(http://msdn.microsoft.com/en-us/library/aa198080(v=SQL.80).aspx) .: 18 :.

19. Questões