Modelo de dados
Propaganda
Escolhendo uma experiência de modelagem de tabela ou multidimensional no SQL Server 2012 Analysis Services Artigo técnico sobre o Microsoft Business Intelligence Autores Hitachi Consulting: Liz Vitt – autora Scott Cameron – autor Hilary Feier - revisora Microsoft: T.K. Anand - revisor Ashvini Sharma - revisor Publicado em: maio de 2012 Aplica-se a: SQL Server 2012 Analysis Services Resumo: Este white paper fornece diretrizes práticas para ajudar os profissionais e tomadores de decisões de BI a decidir se a modelagem de tabela ou multidimensional do SQL Server 2012 Analysis Services é a mais adequada para sua próxima solução de BI. Direitos autorais Este documento é fornecido “como está”. As informações e opiniões expressas nele, inclusive URLs e outras referências a sites da Internet, poderão ser alteradas sem aviso prévio. Você se responsabiliza por usá-lo. Este documento não fornece direitos legais a nenhuma propriedade intelectual de qualquer produto Microsoft. Você pode copiar e usar este documento para sua referência interna. © 2012 Microsoft Corporation. Todos os direitos reservados. Escolhendo um paradigma de modelagem de dados no SQL Server 2012 Analysis Services 2 Sumário Introdução ................................................................................................................................................................................. 4 Informações básicas sobre a modelagem BISM......................................................................................................... 5 Modelagem multidimensional ..................................................................................................................................... 5 Modelagem de tabela ..................................................................................................................................................... 5 Ferramentas de análise de cliente BISM ........................................................................................................................ 6 Modelo de dados ................................................................................................................................................................... 6 Relações de dados ............................................................................................................................................................ 7 Relações um-para-muitos ........................................................................................................................................ 7 Relações muitos-para-muitos ................................................................................................................................. 7 Relações de referência ............................................................................................................................................... 8 Hierarquias ........................................................................................................................................................................... 8 Hierarquias padrão ...................................................................................................................................................... 8 Hierarquias desbalanceadas .................................................................................................................................... 8 Hierarquias pai-filho ................................................................................................................................................... 9 Recursos de modelagem adicionais .......................................................................................................................... 9 Lógica de negócios .............................................................................................................................................................. 10 Transformações em nível de linha ............................................................................................................................ 10 Valores agregados .......................................................................................................................................................... 11 Cálculos ............................................................................................................................................................................... 12 Cenários de lógica de negócios................................................................................................................................. 13 Lógica de hierarquia ................................................................................................................................................. 14 Rollups personalizados ............................................................................................................................................ 14 Medidas semiaditivas ............................................................................................................................................... 15 Inteligência de dados temporais ......................................................................................................................... 16 KPIs .................................................................................................................................................................................. 16 Conversão de moeda ............................................................................................................................................... 16 Conjuntos nomeados ............................................................................................................................................... 17 Acesso a dados e armazenamento ................................................................................................................................ 17 Desempenho e escalabilidade ................................................................................................................................... 17 Modelos multidimensionais .................................................................................................................................. 17 Modelos de tabela..................................................................................................................................................... 19 Programação ..................................................................................................................................................................... 21 Segurança ................................................................................................................................................................................ 21 Segurança em nível de linha/atributo ..................................................................................................................... 21 Segurança dinâmica ....................................................................................................................................................... 22 Segurança em nível da célula e avançada ............................................................................................................. 22 Resumo..................................................................................................................................................................................... 24 Para saber mais ..................................................................................................................................................................... 28 Escolhendo um paradigma de modelagem de dados no SQL Server 2012 Analysis Services 3 Introdução A modelagem de dados é uma disciplina praticada há vários anos por profissionais de BI com um objetivo comum: organizar dados discrepantes em um modelo analítico que dê suporte efetivo e eficiente às necessidades de relatório e análise dos negócios. À medida que a modelagem de dados evolui ao longo dos anos com novas tecnologias e ferramentas, as organizações enfrentam o desafio crescente de misturar seus paradigmas de modelagem de uma forma consistente e coerente que satisfaça não só as diversas necessidades de análise, mas também ofereça uma experiência de análise comum à empresa. Com o lançamento do SQL Server 2012, a Microsoft cumpre esse desafiante objetivo com a introdução do BISM(modelo semântico de BI), um modelo único que pode dar suporte a diversos relatórios e análises mesclando duas experiências de modelagem do Analysis Services nos bastidores: A modelagem multidimensional, introduzida no SQL Server 7.0 OLAP Services e mantida no SQL Server 2012 Analysis Services, permite que os profissionais de BI criem cubos multidimensionais sofisticados usando o OLAP (processamento analítico online) tradicional. A modelagem de tabela, introduzida com o PowerPivot para Microsoft Excel 2010, fornece recursos de modelagem de dados de autoatendimento para analistas de negócios e de dados. A experiência de modelagem de tabela está mais acessível a esses usuários, muitos dos quais passaram anos trabalhando com dados em ferramentas de produtividade para área de trabalho, como o Excel e o Microsoft Access. No SQL Server 2012, a modelagem de tabela foi ampliada para permitir que profissionais de BI criem modelos de tabela no Analysis Services ou importem um modelo de tabela do PowerPivot para o Analysis Services. Observe que um modelo do PowerPivot não pode ser importado para um modelo multidimensional do Analysis Services. O objetivo deste white paper é fornecer diretrizes práticas para ajudar você a decidir qual experiência de modelagem do SQL Server 2012 Analysis Services - de tabela ou multidimensional - é a mais adequada para sua próxima solução de BI. As descrições e recomendações de produtos neste documento baseiam-se no SQL Server 2012 Analysis Services, que foi lançado em março de 2012. As características e recomendações de produtos poderão mudar à medida que a modelagem multidimensional e de tabela do Analysis Services evoluir em versões futuras do SQL Server. Escolhendo um paradigma de modelagem de dados no SQL Server 2012 Analysis Services 4 Informações básicas sobre a modelagem BISM Antes de mergulhar nas diferenças detalhadas entre a modelagem multidimensional e a de tabela, vamos começar com breves informações básicas sobre cada experiência de modelagem BISM fornecida pelo SQL Server 2012 Analysis Services. Modelagem multidimensional Em seu núcleo, a modelagem multidimensional cria cubos compostos de medidas e dimensões baseadas em dados contidos em um banco de dados relacional. Para usar esse paradigma, o servidor do Analysis Services deve ser configurado para funcionar no modo multidimensional, que é a configuração padrão. Nesse modo, o mecanismo OLAP usa o modelo multidimensional para agregar previamente grandes volumes de dados para dar suporte a tempos de resposta rápidos de consultas. O mecanismo OLAP pode armazenar essas agregações em disco com o armazenamento MOLAP (OLAP multidimensional) ou no banco de dados relacional com armazenamento ROLAP (OLAP relacional). As principais características da modelagem multidimensional incluem: Modelo de dados avançado – o modelo multidimensional do SQL Server 2012 Analysis Services está em sua sexta versão e oferece ampla funcionalidade para modelar medidas e dimensões de conjuntos de dados simples e complexos normalmente encontrados em data warehouses empresariais. Um conjunto de dados mais complexo costuma incluir recursos avançados, como relações muitospara-muitos, hierarquias pai-filho e localização. O modelo multidimensional fornece essa funcionalidade pronta para uso. Análise sofisticada – o modelo multidimensional também fornece um cálculo avançado e uma linguagem de consulta chamada MDX (Multidimensional Expressions). Usando a linguagem MDX, é possível criar lógica de negócios e cálculos sofisticados que podem funcionar em qualquer lugar no espaço multidimensional para realizar alocações financeiras, cálculos de série temporal ou métricas semiaditivas. Embora a modelagem de dados abrangente e a analítica sofisticada sejam importantes benefícios da modelagem multidimensional, elas geralmente têm, em contrapartida, ciclos de desenvolvimento mais longos e menor capacidade de adaptação rápida a condições comerciais variáveis. Além disso, a experiência multidimensional tende a exibir habilidades de modelagem avançada e MDX. Modelagem de tabela A modelagem de tabela organiza dados em tabelas relacionadas. Se você quiser usar esse tipo de modelagem, o Analysis Services deverá ser configurado para funcionar no modo de tabela. Nesse modo, você pode usar o mecanismo em memória xVelocity (antes conhecido como Vertipaq) para carregar dados de tabela na memória para obter respostas rápidas de consultas, ou usar o DirectQuery para passar consultas para o banco de dados de origem e aproveitar seus recursos de processamento de consultas. Escolhendo um paradigma de modelagem de dados no SQL Server 2012 Analysis Services 5 As principais características da modelagem de tabela incluem: Familiaridade - o trabalho com dados de tabela é familiar a vários públicos que trabalham regularmente com tabelas armazenadas em bancos de dados relacionais, no Excel ou no Access. Além disso, os cálculos são escritos usando DAX (Data Analysis Expressions), uma linguagem de fórmula considerada como uma extensão da linguagem de fórmula do Excel. Dessa forma, as habilidades necessárias para criar modelos de tabela são mais comuns ou mais facilmente aprendidas quando comparadas às habilidades necessárias para criar modelos multidimensionais. Flexibilidade - como não há uma organização rígida de dados em medidas e dimensões; a modelagem de tabela pode acelerar os ciclos de desenvolvimento, exigindo menos preparação antecipada de dados e rigor de design do que os modelos multidimensionais. Essa arquitetura de dados também pode acomodar mais as alterações de modelagem de dados ao longo do tempo, quando é necessário atualizar relações e cálculos de acordo com as necessidades comerciais variáveis. Embora a familiaridade e a flexibilidade sejam os principais benefícios da modelagem de tabela, elas também têm prós e contras. Por exemplo, a modelagem de tabela pode não ser adequada para as soluções que têm conjuntos de dados altamente complexos ou pode exigir uma lógica de negócios sofisticada. Em geral, os usuários da linguagem DAX podem criar fórmulas DAX para fornecer funcionalidade analítica que, caso contrário, não estaria disponível no modelo de tabela. Nesses casos, porém, pode ser mais apropriado e eficiente usar os recursos avançados fornecidos de forma nativa pela modelagem multidimensional. Ferramentas de análise de cliente BISM Escolhendo a modelagem multidimensional ou de tabela, é importante observar que você poderá usar as ferramentas de cliente que geram MDX ou DAX para consultar o modelo. O Excel e o SQL Server Reporting Services são exemplos das ferramentas de cliente que geram consultas usando MDX, e o Power View é um exemplo de uma ferramenta de cliente que gera consultas usando DAX. Há duas exceções a essa diretriz. O Power View é uma ferramenta de exploração e visualização de dados interativa que é um recurso do suplemento Microsoft SQL Server 2012 Reporting Services para o Microsoft SharePoint Server 2010 Enterprise Edition. Se você quiser usar o Power View ou qualquer outro cliente de análise que usa DAX para consultar um BISM, precisará de um modelo de tabela. Espera-se que as versões futuras do SQL Server ofereçam a capacidade de usar DAX para consultar modelos multidimensionais, de modo que eles possam ser acessados por uma ferramenta de cliente como o Power View. Os modelos de tabela que foram configurados para usar DirectQuery exigem uma ferramenta de cliente que gera consultas DAX, como o Power View. Esperase que as versões futuras do SQL Server permitam que modelos de tabela configurados para usar DirectQuery aceitem consultas MDX. Modelo de dados As características do seu modelo de dados são uma consideração fundamental na escolha da sua experiência de modelagem. Escolhendo um paradigma de modelagem de dados no SQL Server 2012 Analysis Services 6 Relações de dados Um requisito fundamental de qualquer modelo de dados é representar corretamente a forma como os elementos de dados desse modelo se interrelacionam e se conectam, de modo muito semelhante às peças de um quebra-cabeça. Os modelos de tabela e os modelos multidimensionais exigem que você defina relações entre suas tabelas de dados de origem. As relações comuns que você vê na modelagem de dados são relações um-para-muitos, muitos-para-muitos e de referência. Relações um-para-muitos Em uma relação um-para-muitos, um único registro de uma tabela está relacionado a vários registros de outra tabela. Um exemplo de uma relação um-para-muitos é um cliente com várias ordens de venda. Os modelos de dados de tabela e multidimensionais lidam nativamente com relações um-para-muitos. Relações muitos-para-muitos Em uma relação muitos-para-muitos, vários registros de uma tabela estão relacionados a vários registros de uma segunda tabela. Por exemplo, um único cliente tem uma relação um-para-muitos com ordens de venda; no entanto, cada cliente pode ser categorizado em um ou mais perfis de cliente (como esportista (Sports Enthusiast), jogador casual (Casual Gamer) e profissional de educação física (Fitness Expert)). A análise de ordens por perfil de cliente é um desafio muitos-para-muitos, onde pode ocorrer uma contagem dobrada: um pedido de uma bicicleta por um cliente que seja esportista e profissional de educação física pode ser facilmente contado duas vezes quando as ordens por perfil de cliente são somadas para a obtenção do total de pedidos. Normalmente, o gerenciamento de relações muitos-para-muitos desmembraas em duas relações um-para-muitos usando uma tabela de ponte ou intermediária, como é representado na Figura 1. ID do cliente 1 … Nome do cliente Elizabeth Johnson … ID do cliente 1 1 1 … Tabela de clientes ID do cliente 1 1 1 … Ordem de ... S9100 S9101 S9102 … Valor das ... $ 4,000 $ 2,500 $ 7,000 … Tabela de ordens de venda Perfil do cliente Entusiasta de esportes Jogador casual Especialista em exercícios … Tabela de ponte/intermediária para atribuir o perfil de cliente Figura 1 - Exemplo de muitos-para-muitos Em modelos multidimensionais, você pode definir e criar relações muitos-para-muitos diretamente no modelo de dados identificando a tabela de ponte e, em seguida, relacionando-a essa tabela de ponte a outras tabelas em seu modelo. Ao agregar, o Analysis Services aplica uma soma distinta para garantir que os totais dos dados tenham sido corretamente resumidos e não incorretamente inflacionados. Escolhendo um paradigma de modelagem de dados no SQL Server 2012 Analysis Services 7 Os modelos de tabela do SQL Server 2012 Analysis Services não dão suporte à definição de relações muitos-para-muitos. Porém, você pode usar a linguagem DAX para criar fórmulas que manipulem o desafio muitos-para-muitos. Relações de referência Um modelo de dados pode conter um conjunto de atributos comuns que estejam relacionados a várias entidades. Por exemplo, atributos geográficos estão relacionados a clientes, fornecedores e lojas. Na modelagem multidimensional, você deve criar uma dimensão que contenha os atributos comuns e depois criar relações de dimensão de referência para cada uma das dimensões relacionadas. Na modelagem de tabela, não há necessidade de criar relações de referência. Em um modelo de tabela, tudo que você precisa fazer é criar relações entre a tabela que contém os atributos comuns e as tabelas que contêm as entidades relacionadas. Hierarquias As hierarquias categorizam dados em uma estrutura de árvore para facilitar as análises detalhadas. Hierarquias padrão As hierarquias padrão são compostas de níveis ordenados que vêm de colunas dos dados de origem. Por exemplo, uma hierarquia de produtos pode organizar produtos em subcategorias, as quais ainda podem ser organizadas em categorias. Nesse caso, você teria uma hierarquia com três níveis, onde cada nível vem de uma coluna separada em seus dados de origem. As hierarquias simples, como a hierarquia de produtos descrita aqui, têm suporte em modelos de tabela e multidimensionais. Observe que, nos modelos multidimensionais, há uma etapa adicional de criação de relações de atributos, que é identificar explicitamente as relações um-para-muitos entre os atributos de cada dimensão. A definição de relações de atributos é altamente recomendada porque elas permitem um design mais eficiente de agregações précalculadas e, além disso, a semântica da linguagem MDX utiliza relações de atributos. A modelagem de tabela é mais simples porque você não cria relações de atributos. Os modelos de tabela não calculam previamente agregações e a semântica da linguagem DAX não depende da identificação das relações um-para-muitos entre atributos, portanto, na modelagem de tabela, não há nenhum equivalente às relações de atributos da modelagem multidimensional. Hierarquias desbalanceadas As hierarquias desbalanceadas ocorrem quando um elemento de dados especificado está ausente na árvore hierárquica. Por exemplo, ocorrerá uma hierarquia desbalanceada de produtos se houver produtos que nunca foram atribuídos a uma subcategoria, mesmo que tenham atribuições de categoria de produto. Nessas situações, em vez de mostrar a lacuna na árvore, você poderá optar por ocultar a lacuna para facilitar a análise detalhada. Os modelos multidimensionais incluem suporte pronto para uso a hierarquias desbalanceadas; porém, os modelos de tabela não dão suporte a esse recurso. Escolhendo um paradigma de modelagem de dados no SQL Server 2012 Analysis Services 8 Hierarquias pai-filho As hierarquias pai-filho oferecem um design hierárquico mais complicado. Nem todas as ramificações em uma hierarquia pai-filho têm o mesmo número de níveis. Por exemplo, uma relação pai-filho entre funcionário e gerente pode gerar uma hierarquia na qual alguns gerentes têm apenas subordinados diretos, enquanto outros têm subordinados diretos que também têm seus próprios subordinados. Esse tipo de hierarquia é modelado por meio da criação de uma relação entre duas colunas em uma tabela de dados de origem, como mostra a Figura 2. Employee Funcionário Ken J. Sánchez Brian S. Welcker Amy E. Alberts Jae B. Pak David M. Bradley Kevin F. Brown Manager Gerente Ken J. Sánchez Ken J. Sánchez Brian S. Welcker Amy E. Alberts Ken J. Sanchez David M. Bradley Dados de origem pai-filho Árvore hierárquica pai-filho Figura 2 - Hierarquia pai-filho Os modelos multidimensionais oferecem uma funcionalidade pronta para uso que permite definir e criar hierarquias pai-filho baseadas em relações nos dados de origem. Em modelos de tabela, você pode usar funções DAX para criar fórmulas que navegam na estrutura pai-filho e a utilizam em cálculos. Para saber mais sobre o uso de hierarquias pai-filho em modelos de tabela, consulte Compreendendo funções para hierarquias pai-filho em DAX (http://msdn.microsoft.com/ptbr/library/gg492192(v=sql.110).aspx). Recursos de modelagem adicionais Além de relações e hierarquias de dados, há os recursos de modelagem adicionais que podem ajudá-lo a escolher a melhor experiência de modelagem: As perspectivas permitem que você defina um subconjunto de um modelo de dados para simplificar a navegação dos usuários finais. As perspectivas estão disponíveis nos modelos multidimensionais e de tabela. As traduções permitem que os modelos multidimensionais exibam a dimensão, o atributo, a medida, o membro calculado e outros valores de membros de dimensão e nomes de objetos no idioma especificado nas configurações locais do computador. Habilitar esse recurso requer que o desenvolvedor de modelos forneça os nomes de objetos traduzidos e faça referência às colunas dos dados de origem que contêm os valores dos membros de dimensão traduzidos. Os modelos de tabela não dão suporte a essa funcionalidade. Escolhendo um paradigma de modelagem de dados no SQL Server 2012 Analysis Services 9 As ações capacitam os usuários finais a executar um relatório do Reporting Services, navegar até uma URL ou iniciar uma operação externa baseada no contexto da célula onde ocorre a ação. Por exemplo, usando uma ação, um usuário final pode iniciar uma página da Web que exiba o catálogo de produtos da empresa filtrado automaticamente para os produtos que o usuário estava procurando. As ações têm suporte nativo em modelos multidimensionais, e muitas ferramentas de cliente (como o Excel e o Reporting Services) permitem que os usuários executem ações. No SQL Server 2012, a capacidade de criar ações em um modelo de tabela usando o SQL Server Data Tools não tem suporte. O detalhamento permite a navegação pelos dados detalhados armazenados em seu modelo. O detalhamento está disponível nas modelagens multidimensional e de tabela. Os modelos multidimensionais também permitem criar ações de detalhamento de forma que você possa personalizar a experiência de detalhamento especificando as colunas que serão retornadas pela ação de detalhamento e o espaço de cubo onde a ação será habilitada. O write-back é um recurso que geralmente é necessário em aplicativos de orçamento e previsão. Nesses cenários, os usuários corporativos normalmente desejam executar a análise hipotética (“what-if”) onde eles alteram e atualizam valores de dados no modelo e, em seguida, os publicam para que outras pessoas os vejam. Os modelos multidimensionais dão suporte nativo ao writeback de dados. No SQL Server 2012, os modelos de tabela não dão suporte a essa funcionalidade. Lógica de negócios A lógica de negócios pode adicionar um enorme valor a qualquer modelo de dados na forma de cálculos e regras de negócio que aprimoram dados para a análise do usuário final. As modelagens de tabela e multidimensional oferecem linguagens de fórmula sofisticadas para a implementação da lógica de negócios. A modelagem multidimensional usa a linguagem MDX, e modelagem de tabela usa a linguagem DAX. Antes de nos aprofundarmos em alguns dos cenários avançados de lógica de negócios de cada paradigma, é importante estabelecermos uma compreensão básica de como a lógica de negócios pode ser aplicada usando transformações em nível de linha, valores agregados e cálculos nas modelagens multidimensional e de tabela. Transformações em nível de linha Você talvez precise executar cálculos e transformações de dados que não estão prontamente disponíveis em sua fonte de dados. Por exemplo, seus dados de origem podem ter uma coluna Sales Amount e uma coluna Daily Exchange Rate, mas talvez não tenham as vendas convertidas para a moeda estrangeira, ou seus dados de origem podem ter Employee First Name e Employee Last Name, mas não ter um Employee Full Name concatenado. Observe que, nestes exemplos, o cálculo ou a manipulação de dados deverá ocorrer em dados em nível da linha, não agregados. Escolhendo um paradigma de modelagem de dados no SQL Server 2012 Analysis Services 10 Na modelagem multidimensional, as transformações em nível de linha em dados não agregados devem ser executadas antes que os dados sejam carregados no modelo, ou devem ser executadas quando o modelo é consultado. Você pode transformar atributos de dimensão, como nomes de funcionários, aplicando a transformação no sistema de fonte de dados ou escrevendo uma expressão SQL que seja aplicada quando o Analysis Services consultar o banco de dados de origem. As transformações em nível de linha de dados numéricos podem ser executadas com uma expressão SQL antes que os dados sejam carregados no Analysis Services, ou a transformação pode ser aplicada com uma expressão MDX em uma instrução Scope de forma que o cálculo seja aplicado em nível de linha. Se a transformação for aplicada antes que os dados sejam carregados, então o Analysis Services poderá agregar previamente os valores numéricos. Se a transformação for aplicada com uma instrução Scope, a agregação ocorrerá no momento da consulta. Na modelagem de tabela, as transformações em nível de linha são criadas com o uso de colunas calculadas. Ao criar uma coluna calculada, você adiciona a coluna a uma tabela específica em seu modelo e usa fórmulas DAX para definir os valores da coluna. A fórmula é então avaliada para cada registro dessa tabela e carregada na memória como qualquer outra coluna do modelo. Essa flexibilidade permite que você aprimore seus dados diretamente no modelo de tabela, baseado nos requisitos específicos de análise, e reduz a necessidade fazer ajustes em fontes de dados upstream que podem ou não acomodar suas alterações de maneira oportuna. As colunas calculadas oferecem uma maneira muito conveniente de criar e manter os cálculos que devem ser executados em um nível detalhado dos dados antes de serem agregados. Embora essa flexibilidade seja sofisticada, observe que as colunas calculadas não se destinam a executar a limpeza pesada de dados ou as transformações de dados que você encontraria nos processos ETL (extração, transformação e carregamento). Valores agregados Na modelagem multidimensional, você usa medidas para criar valores agregados. O mecanismo OLAP do Analysis Services agrega previamente as medidas de um cubo usando funções de agregação como SUM, COUNT, MIN, MAX, COUNT e DISTINCT, entre outras. Durante o processamento do cubo, cada medida é agregada de baixo para cima em todas as hierarquias. Como esse processamento acontece antes da análise do usuário final, as medidas previamente agregadas podem fornecer enormes benefícios para o desempenho da consulta. Quando você cria uma medida no cubo, há uma relação um-para-um entre uma medida do cubo e uma coluna numérica em seus dados de origem. Sendo assim, na modelagem multidimensional, as medidas são úteis quando você precisa executar a agregação de baixo para cima de elementos de dados numéricos que (1) existem em seus dados de origem no nível mais baixo de detalhe e (2) exigem um rollup que use uma das funções de agregação nativas do cubo. Na modelagem de tabela, você também usa medidas para criar valores agregados. Você cria uma medida selecionando uma coluna e, em seguida, especificando a função de agregação (SUM, COUNT, DISTINCT COUNT, MIN, MAX ou AVERAGE), ou pode escrever uma expressão DAX que especifique a função que você deseja usar para agregar a medida. Em um modelo de tabela, os dados em nível de linha são armazenados na memória e as agregações são calculadas na consulta. Como explicado na próxima seção, na modelagem de tabela, as medidas também podem ser usadas para aplicar cálculos. Isso pode incluir os cálculos baseados em várias colunas agregadas. Escolhendo um paradigma de modelagem de dados no SQL Server 2012 Analysis Services 11 Cálculos Na modelagem multidimensional, você usa a linguagem MDX para criar cálculos. MDX é uma linguagem de expressão e de consulta com funções que compreendem nativamente o design de dimensões, hierarquias, relações de atributos e medidas de um cubo. Essa compreensão nativa permite que você crie expressões sucintas e eficientes, que aplicam a lógica de negócios a vários contextos de dados. Você cria e armazena cálculos MDX no script de cálculo do cubo, onde pode controlar a ordem em que a lógica é aplicada. Os membros calculados são os cálculos MDX mais comuns, e são avaliados no momento da consulta, depois que os dados são previamente agregados. Os membros calculados podem ser criados em qualquer dimensão. Quando são criados na dimensão de medidas, eles são frequentemente chamados de medidas calculadas. Os membros calculados podem ser razoavelmente simples, com operações aritméticas básicas como vendas por unidade (vendas/unidade) ou gasto por pessoa (gasto/número de funcionários). Eles também podem ser mais complexos quando você precisa aplicar regras de negócio específicas, como Rolling 3 Period Average Sales ou YTD Margin. Por exemplo, se você quiser calcular vendas para o período atual como uma porcentagem do período pai, poderá usar o cálculo MDX a seguir. [Measures].[Sales Amount] / ([Date].[Calendar].CurrentMember.Parent,[Measures].[Sales Amount]) A criação de um membro calculado em uma dimensão diferente da dimensão de medidas adiciona um valor a um atributo na dimensão. Por exemplo, se você tivesse um atributo de dimensão contendo uma lista de cores, talvez quisesse adicionar as Cores Primárias de um membro calculado, que somariam os valores das cores vermelho, verde e azul. Na modelagem de tabela, a criação de uma medida é semelhante à criação de um membro calculado na dimensão de medidas em um modelo multidimensional. Nessa modelagem, você não pode adicionar um valor a uma coluna de uma tabela, portanto a modelagem de tabela não dá suporte ao equivalente à criação de um membro calculado em uma dimensão que não seja a dimensão de medidas em um modelo multidimensional. Escolhendo um paradigma de modelagem de dados no SQL Server 2012 Analysis Services 12 As atribuições de escopo são mais avançadas do que as medidas calculadas, mas também são mais eficientes. Como mencionado anteriormente na seção “Transformação em nível de linha”, você pode usar uma instrução Scope de forma que os cálculos sejam aplicados no nível de linha. No entanto, você também pode usar uma instrução Scope para especificar o intervalo de células do cubo onde deseja aplicar um cálculo. As atribuições de escopo são compiladas antes do momento da consulta, e permitem que o Analysis Services forneça um caminho de execução otimizado quando o cálculo é consultado. Devido à sua potência, as atribuições de escopo não só podem fazer o trabalho de várias medidas calculadas, como também fazem o trabalho com mais eficiência. Por exemplo, em uma solução de orçamento, você deseja atribuir o orçamento do próximo ano da região Leste como 90% do orçamento do ano atual, e quer que o novo orçamento da região Oeste seja de 75% do orçamento do ano atual. Você deseja que o novo orçamento da região Sul seja de 105% do orçamento do ano atual, e que o novo orçamento da região Norte seja igual ao orçamento do ano atual. Em vez de escrever uma única medida calculada complexa com instruções IF aninhadas ou várias medidas calculadas que separem cada cenário de orçamento individualmente, você pode usar atribuições de escopo para aplicar efetivamente essas taxas em nível de região e, em seguida, agregar os totais de dados. Por exemplo, se você quisesse converter o valor de venda em uma moeda estrangeira usando as taxas de câmbio diárias, poderia usar a seguinte expressão MDX: Scope([Date].[Date]); This = [Measures].[Sales Amount] * [Measures].[Daily FX Rate]; End Scope; Na modelagem de tabela, você usa a linguagem DAX para criar cálculos. Conforme mencionado anteriormente, na modelagem de tabela, você aplica cálculos em nível de linha criando colunas calculadas. Você também pode aplicar cálculos quando cria uma medida ao escrever uma expressão DAX. Como você usa explicitamente uma combinação de funções de agregação e em nível de linha DAX, as medidas em modelos de tabela são muito flexíveis. Você pode aplicar funções em nível de linha e depois aplicar uma função de agregação de forma que a medida aplique cálculos antes da agregação, ou pode aplicar funções de agregação primeiro e depois aplicar funções em nível de linha para que a medida aplique cálculos após as agregações. A linguagem DAX pode avaliar dinamicamente uma fórmula em diferentes contextos de dados (não apenas na exibição atual de uma planilha do Excel ou de uma Tabela Dinâmica) usando um conjunto especial de funções chamadas de funções FILTER. No sentido mais amplo, essas funções têm uma finalidade semelhante às atribuições de escopo do Analysis Services, pois permitem que você defina e execute um cálculo em um conjunto específico de linhas. Por exemplo, você pode usar funções FILTER para lidar com o exemplo de orçamento descrito anteriormente. Cenários de lógica de negócios Agora que você tem uma boa noção de como pode criar e aplicar lógica de negócios básica em MDX e DAX, considere os cenários de cálculo a seguir para comparar e estabelecer um contraste entre as experiências de modelagem de tabela e multidimensional. Escolhendo um paradigma de modelagem de dados no SQL Server 2012 Analysis Services 13 Lógica de hierarquia Como mencionado anteriormente, as hierarquias oferecem um meio de os usuários empresariais fazerem drill up ou drill down durante a análise de dados. Em algumas situações, é útil criar cálculos que naveguem na hierarquia. Por exemplo, considere uma dimensão de produto em que você tenha a categoria do produto, a subcategoria do produto e o produto. Para cada nível da hierarquia, você deseja adicionar um cálculo que meça como os membros de cada nível contribuem para o total de vendas do pai. Isso é chamado de cálculo de porcentagem de pai, uma vez que deve navegar pela hierarquia para retornar o valor desejado. As linguagens MDX e DAX oferecem funções para o trabalho com dados organizados em uma hierarquia e a criação de cálculos como a porcentagem de pai; porém, as funções MDX tendem a ser mais simples e fáceis de usar. Por exemplo, na linguagem MDX, esta é a expressão que fornece a porcentagem de pai na dimensão Product. [Measures].[Sales Amount] / ([Product].[Product Categories].CurrentMember.Parent, [Measures].[Sales Amount]) Esta expressão mais complexa é necessária para criar o cálculo da mesma porcentagem de pai usando DAX. IF( ISFILTERED(Product[Product]) ,[Sales]/CALCULATE([Sales],ALL(Product[Product])) ,IF( ISFILTERED(Product[Subcategory]) ,[Sales]/CALCULATE([Sales],ALL(Product[Subcategory])) ,1 ) ) Rollups personalizados Embora o resumo uniforme de dados seja aplicável em muitos cenários, também há situações em que você deseja ter o controle mais apurado de como seus dados são acumulados. Um exemplo disso é o caso dos modelos financeiros, onde você tem um plano de contas (normalmente em um formato pai-filho) com a lógica de rollup específica necessária para cada conta. Como mostrado aqui, o cálculo de Margem Bruta é Vendas Líquidas menos o Custo Total de Vendas, e o cálculo de Lucro Operacional é Margem Bruta menos Despesas Operacionais. Escolhendo um paradigma de modelagem de dados no SQL Server 2012 Analysis Services 14 Não apenas os modelos multidimensionais fornecem suporte nativo a hierarquias pai-filho, como também fornecem inteligência de conta interna, o que permite aplicar facilmente operadores unários e fórmulas MDX nas contas que orientam o rollup de dados. Em modelos de tabela, a inteligência pai-filho ou de conta não é interna, mas você pode criar sua própria solução usando uma combinação de colunas e medidas calculadas para criar a hierarquia pai-filho e aplicar o rollup personalizado. Medidas semiaditivas Em linhas gerais, as medidas semiaditivas são aquelas que agregam uniformemente em todas as dimensões, com exceção de data. Os exemplos de medidas semiaditivas incluem o saldo de abertura e o saldo de fechamento. Para essas medidas, você deseja aplicar lógica especial para resumir corretamente os dados por período. Afinal, o saldo de estoque disponível para o mês de março não é a soma do estoque disponível para todos os dias de março. Além disso, esse saldo também deve funcionar corretamente em todos os atributos de data, como trimestre e ano. Por exemplo, o estoque disponível do primeiro trimestre deve ser igual ao saldo informado em 31 de março (supondo que 31 de março seja o último dia do primeiro trimestre). Os modelos multidimensionais oferecem suporte pronto para uso a medidas semiaditivas com funções de agregação especiais, como First Child, Last Child, FirstNonEmptyChild e LastNonEmptyChild. Se essas funções de agregação não satisfizerem seus requisitos de lógica específicos, você também poderá escrever fórmulas MDX personalizadas. Os modelos de tabela fornecem funções semelhantes, como ClosingBalanceMonth e OpeningBalanceMonth. Existem funções adicionais que se aplicam em outros atributos de data, como trimestre e ano. Escolhendo um paradigma de modelagem de dados no SQL Server 2012 Analysis Services 15 Inteligência de dados temporais Quase todas as soluções de BI que você encontrar exigirão a inteligência de dados temporais, que inclui a capacidade de calcular resumos acumulados no ano até a data e executar comparações com o ano anterior. As linguagens MDX e DAX fornecem funções de série temporal; entretanto, cada uma usa um design de modelo de dados ligeiramente diferente. Os modelos multidimensionais fornecem inteligência de dados temporais pronta para uso por meio do Assistente de Business Intelligence do Analysis Services. Usando esse assistente, os cálculos de tempo podem ser adicionados ao design da dimensão de tempo e também ser aplicados a todas as medidas no modelo. Embora o uso do assistente seja uma maneira de criar cálculos de tempo, você também pode escrever seus próprios cálculos MDX no modelo multidimensional. Em modelos de tabela, embora não haja um assistente para criar cálculos de inteligência de dados temporais, você poderá criar cálculos manualmente criando fórmulas DAX que usem uma variedade de funções, incluindo TOTALMTD e TOTALYTD, além de SAMEPERIODSLASTYEAR. KPIs Os KPIs (indicadores chave de desempenho) identificam as medidas especiais que você deseja monitorar em relação a um valor de destino usando um indicador visual, como um sinal de trânsito. Os modelos multidimensional e de tabela dão suporte a KPIs. Ambos oferecem a capacidade de atribuir uma meta para uma medida e usar a comparação do valor real com meta para avaliar o status de desempenho da medida. Os modelos multidimensionais oferecem a capacidade adicional de avaliar a tendência do KPI e atribuir um indicador visual separado para representar o desempenho do KPI ao longo do tempo. Conversão de moeda As conversões de moeda exigem que você converta dados de uma ou mais moedas de origem em uma ou mais moedas de relatório. Por exemplo, se sua organização processa transações de vendas em EUR, JPY e USD, para consolidar o relatório de vendas na organização inteira, você precisará converter as transações de vendas em uma ou mais moedas de relatório. Para implementar conversões de moeda em qualquer experiência de modelagem, você deverá ter acesso aos dados de taxa de câmbio da moeda e incluir esses dados em seu modelo. Em modelos multidimensionais, você pode usar o Assistente de Business Intelligence do Analysis Services para criar cálculos de conversão de moeda MDX que são otimizados para dar suporte a várias moedas de origem e de relatório. Em um modelo de tabela, é possível criar sua própria solução de conversão de moeda criando fórmulas DAX. Escolhendo um paradigma de modelagem de dados no SQL Server 2012 Analysis Services 16 Conjuntos nomeados Na modelagem multidimensional, os conjuntos nomeados oferecem uma maneira de retornar um conjunto de membros de dimensão que são frequentemente usados em aplicativos de relatório. Por exemplo, talvez você queira criar um conjunto nomeado para retornar os últimos 12 meses. A criação desse conjunto nomeado dentro do cubo permite que você defina centralmente a lógica de conjunto, para acessar o conjunto de qualquer aplicativo de relatório e para simplificar a lógica armazenada em seu aplicativo de relatório. Para criar o conjunto nomeado Last 12 Months, você poderia usar a expressão MDX a seguir. Create Set CurrentCube.[Last 12 Months] As Max([Date].[Calendar].[Month]).Lag(11):Max([Date].[Calendar].[Month]) Os conjuntos nomeados não estão disponíveis na modelagem de tabela. Acesso a dados e armazenamento Desempenho e escalabilidade O desempenho e a escalabilidade são os fatores importantes que devem ser considerados para o êxito de qualquer solução de BI. Como cada uma das experiências de modelagem utiliza diferentes tecnologias subjacentes, elas têm características de desempenho e comportamentos diferentes que você deve compreender para considerar corretamente qual experiência de modelagem se adapta melhor às suas necessidades. Modelos multidimensionais Conforme mencionado anteriormente no white paper, os modelos multidimensionais do Analysis Services usam um mecanismo OLAP. Em disco, os dados OLAP podem ser armazenados em arquiteturas de dados MOLAP e ROLAP. Em MOLAP, os dados são armazenados em disco em um formato multidimensional otimizado com compactação 3x típica. Em ROLAP, os dados são armazenados no banco de dados relacional de origem. Quando você pensa em desempenho, geralmente é útil considerá-lo em dois segmentos: desempenho de consulta e desempenho de processamento. Desempenho de consulta O desempenho de consulta afeta diretamente a qualidade da experiência do usuário final. Como tal, ele é o principal parâmetro usado para avaliar o êxito de uma implementação OLAP. O Analysis Services fornece uma variedade de mecanismos para acelerar o desempenho de consulta, incluindo agregações, cache e recuperação de dados indexada. Além disso, você pode melhorar o desempenho de consulta otimizando o design de seus atributos de dimensão, cubos e consultas MDX. Escolhendo um paradigma de modelagem de dados no SQL Server 2012 Analysis Services 17 Uma das principais formas de otimizar o desempenho de consulta é o uso de agregações. Uma agregação é um resumo pré-calculado de dados usado para aprimorar o desempenho de consulta em modelos multidimensionais. Ao consultar um modelo multidimensional, o processador de consulta do Analysis Services decompõe a consulta em solicitações para o mecanismo de armazenamento OLAP. Para cada solicitação, o mecanismo de armazenamento primeiro tenta recuperar dados do cache do mecanismo de armazenamento na memória. Se não houver dados disponíveis no cache, ele tentará recuperar dados de uma agregação. Se nenhuma agregação estiver presente, ele recuperará os dados das partições de um grupo de medidas. O design de agregações de dados envolve a identificação do esquema de agregação muito mais eficiente para sua carga de trabalho de consulta. Quando você projetar agregações, deverá considerar os benefícios de consulta que as agregações oferecem comparados com o tempo necessário para criar e atualizar as agregações. De fato, a adição de agregações desnecessárias pode piorar o desempenho de consulta, pois as ocorrências raras movem a agregação para o cache de arquivos ao custo de mover mais alguma coisa. O cache também é importante para o ajuste do desempenho de consulta do Analysis Services. Você deve ter memória suficiente para armazenar todos os dados da dimensão, além de espaço para armazenar em cache os resultados da consulta. Durante a consulta, a memória é usada principalmente para armazenar os resultados armazenados nos caches do processador de consulta e do mecanismo de armazenamento. Para otimizar os benefícios do cache, muitas vezes você poderá aumentar a capacidade de resposta da consulta ao pré-carregar dados em um ou em ambos os caches. Isso pode ser feito com a execução prévia de uma ou mais consultas ou o uso de uma instrução do cache de criação. Desempenho de processamento O processamento é a operação que atualiza dados em um banco de dados do Analysis Services. Quanto mais rápido for o desempenho de processamento, mais cedo os usuários poderão acessar dados atualizados. O Analysis Services fornece uma variedade de mecanismos que você pode usar para influenciar no desempenho do processamento, incluindo o design eficiente de dimensão, agregações efetivas, partições e uma estratégia de processamento econômica (por exemplo, incremental versus atualização completa versus cache pró-ativo). Você pode usar partições para separar dados de medidas (normalmente dados da tabela de fatos) em unidades físicas. O uso mais eficiente de partições pode melhorar o desempenho da consulta, melhorar o desempenho do processamento e facilitar o gerenciamento de dados. Para cada partição, você poderá ter um design de agregação separado e uma agenda de atualização separada, o que pode otimizar muito o desempenho do processamento. Para cada tabela de fatos, você também pode ter uma combinação de partições MOLAP e ROLAP. Esse tipo de estratégia de particionamento pode ser usado para fornecer consultas em tempo real ou pode ser usado para fornecer acesso a conjuntos de dados muito grandes para serem processados em um cubo. O uso de técnicas de otimização de consulta e de processamento como essas pode ajudar você a dimensionar seus modelos multidimensionais para lidar com terabytes de dados. Para saber mais sobre o ajuste de desempenho, consulte Guia de desempenho do Analysis Services 2008 R2 (http://sqlcat.com/sqlcat/b/whitepapers/archive/ 2011/10/10/analysis-services-2008-r2-performance-guide.aspx). Escolhendo um paradigma de modelagem de dados no SQL Server 2012 Analysis Services 18 Modelos de tabela Os modelos de tabela usam o mecanismo de análise xVelocity, que fornece processamento de dados em memória, ou DirectQuery, que transmite consultas para o banco de dados de origem para aproveitar os recursos de processamento de consulta. Os benefícios dos bancos de dados em coluna e do processamento de dados na memória são usados em conjunto. Os bancos de dados em coluna obtêm uma compactação maior do que o armazenamento tradicional, normalmente a compactação de 10x, dependendo da cardinalidade dos dados. A cardinalidade de dados se concentra na caracterização da distribuição de dados em uma única coluna. Alta cardinalidade de dados significa que os valores de dados em uma coluna são altamente exclusivos (por exemplo, o número do cliente). Baixa cardinalidade de dados significa que os valores de dados em uma coluna podem se repetir (por exemplo, sexo e estado civil). Quanto mais baixa a cardinalidade dos dados, maior será a compactação, o que significa que mais dados podem caber na memória em um determinado momento. Como modeladores de dados, é importante entender a cardinalidade dos dados para determinar quais conjuntos de dados são mais adequados para seu modelo de tabela, bem como os requisitos de memória associados para dar suporte ao modelo. Desempenho de consulta Quando um usuário consulta um modelo de tabela, o mecanismo executa varreduras de memória para recuperar os dados e para calcular agregações dinamicamente sem a necessidade de processamento de E/S do disco. Essa abordagem pode oferecer um desempenho muito alto de consulta sem exigir ajuste especial e gerenciamento de agregação. A maneira mais fácil de otimizar o desempenho de consulta para modelos de tabela é maximizar a memória disponível. De uma perspectiva de escalabilidade, o volume de dados é limitado em sua maior parte pela memória física. É altamente recomendável que você forneça memória suficiente para conter todos os dados dos seu modelo de tabela. Em cenários onde a memória é restrita, o mecanismo em memória também fornece suporte básico de paginação de acordo com a memória física. Além disso, há parâmetros de configuração do lado do servidor que permitem à equipe de TI gerenciar mais de perto a memória disponível para os modelos de tabela. Para saber mais sobre a configuração de memória do modelo de tabela, consulte Propriedades de memória (http://msdn.microsoft.com/pt-br/library/ms174514.aspx). Desempenho de processamento Os modelos de tabela oferecem duas diferenças básicas de desempenho de processamento em relação aos modelos multidimensionais: Ao contrário dos modelos multidimensionais, os modelos de tabela carregam dados diretamente na memória e não exigem a gravação de dados em disco Como os modelos de tabela não categorizam dados em dimensões e em grupos de medidas, o processamento pode ser muito mais flexível. Ambas as diferenças significam que há menos sobrecarga a cada atualização de dados, o que, por sua vez, pode permitir tempos de resposta mais rápidos e uma agilidade maior. Considere este exemplo. Em sua organização, é comum que representantes de venda viajem de uma região para outra regularmente. Os usuários empresariais desejam ver os dados de vendas acumulados pelas atribuições mais recentes do representante de vendas e da região. Escolhendo um paradigma de modelagem de dados no SQL Server 2012 Analysis Services 19 Em um modelo multidimensional, para realizar essa tarefa, você deve atualizar primeiro a dimensão da sua organização de vendas. Depois que a dimensão de organização de vendas tiver sido atualizada, você deverá atualizar a partição do grupo de medidas de vendas. A atualização da partição de vendas atualiza os dados detalhados e as agregações. A etapa final na preparação de dados (como uma prática recomendada) é aquecer o cache de consulta do Analysis Services para recuperar os dados mais úteis do disco na memória. Dependendo de seu design de modelo de dados, do tamanho dos dados e da sua opção específica em técnicas de processamento (processamento incremental versus completo), isso poderia levar minutos ou horas. A boa notícia é que há diversas técnicas comprovadas usadas diariamente por profissionais de BI para otimizar a superfície de processamento de modelos multidimensionais à medida que eles equilibram os requisitos de processamento de dados com as demandas de disponibilidade de dados. Agora considere o mesmo cenário em um modelo de tabela. No modelo de tabela, não há nenhum conceito de dimensões e de grupos de medidas. Em vez disso, os dados são organizados em tabelas com relações entre si. Suponha que os dados da organização de vendas e os dados de vendas estejam cada um em suas respectivas tabelas com uma relação baseada no representante de vendas individual. Com esse design, quando você atualiza sua tabela de organização de vendas, atualiza automaticamente as colunas calculadas, as relações e as hierarquias de usuários. Isso significa que os dados de vendas refletem automaticamente os rollups da região de vendas atualizados, sem a necessidade de processar novamente os dados de vendas. Essa flexibilidade pode oferecer benefícios significativos quando você tem dimensões rapidamente variáveis e precisa que os dados reflitam as atualizações mais recentes. Além disso, observe que, em modelos de tabela, também não é necessário criar agregações, gravar dados em disco ou aquecer o cache de consultas para colocar os dados na memória. Com os modelos de tabela, os dados são movidos do disco diretamente para a memória e estão prontos. De modo semelhante aos modelos multidimensionais, os modelos de tabela permitem que você divida seus dados de tabela em partições, eliminando a necessidade de processamento de dados desnecessário. Por exemplo, você pode dividir tabelas maiores em várias partições, como uma partição mensal para cada mês do ano atual e uma partição anual para cada um dos anos anteriores. Essa abordagem permite isolar as partições de processamento que exigem uma atualização. Ao contrário dos modelos multidimensionais, observe que, embora você possa processar várias tabelas em paralelo, não poderá processar partições da tabela de um indivíduo em paralelo. DirectQuery Como uma alternativa ao modo em memória xVelocity dos modelos de tabela, os profissionais de BI também podem criar modelos de tabela usando o modo DirectQuery, que está disponível para modelos de tabela com fontes de dados do SQL Server. O DirectQuery oferece o recurso de ignorar o processamento de dados passando consultas e cálculos DAX para o banco de dados de origem para aproveitar os recursos do SQL Server. Isso pode ser útil principalmente com grandes volumes de dados que exijam atualização frequente. Entretanto, com o DirectQuery, não há suporte para colunas calculadas e algumas funções DAX. Escolhendo um paradigma de modelagem de dados no SQL Server 2012 Analysis Services 20 Programação AMO (Objetos de Gerenciamento de Análise) é a API para desenvolver e gerenciar objetos do Analysis Services. Essa API foi criada antes de a modelagem de tabela ter sido adicionada ao Analysis Services e, portanto, só contém classes para objetos tradicionalmente associados à modelagem multidimensional: cubos, dimensões, grupos de medidas, scripts MDX e assim por diante. No entanto, essa API também pode ser usada para o desenvolvimento e o gerenciamento de modelos de tabela. Esse é um benefício de as modelagens multidimensional e tabular serem encapsuladas pelo Modelo Semântico de BI. Embora os modelos de tabela e dimensional sejam distintos internamente, o BISM apresenta a mesma interface externa. Apesar de você poder usar AMO para programar modelos de tabela e multidimensional, sua interface é menos intuitiva para modelos de tabela. Para obter mais informações, incluindo um exemplo de código AMO de modelo de tabela, consulte Tutoriais do Analysis Services (http://msdn.microsoft.com/pt-br/library/hh231701.aspx) Segurança Ter uma estratégia de segurança de dados apropriada é importante para garantir que as pessoas certas tenham acesso aos dados certos. As organizações devem controlar o acesso aos dados para manter seus ativos de dados seguros e cumprir os regulamentos de privacidade. Ambos os modelos, multidimensional e de tabela, oferecem um conjunto de recursos robustos que satisfazem uma ampla variedade de requisitos de segurança. Os recursos apresentam diferenças sutis, os quais você deve compreender antes de escolher a experiência de modelagem que melhor atenda às suas necessidades de segurança. No Analysis Services, você gerencia a segurança de projetos multidimensionais e de tabela criando uma função e concedendo permissões a ela. Em seguida, você adiciona nomes de usuários e grupos do Windows à função, permitindo assim o acesso de usuários correspondentes com base nas permissões da função. Segurança em nível de linha/atributo Em um projeto multidimensional, você usa o conceito de segurança de dados de dimensão para gerenciar o acesso em nível de linha. Para implementar a segurança de dados de dimensão para uma função, você concede ou nega o acesso aos dados da dimensão marcando ou desmarcando membros da dimensão. Você também pode implementar uma configuração de segurança mais complexa ao definir um conjunto de membros usando uma expressão MDX. Além disso, você especifica se a função deve ter o acesso concedido ou negado aos novos membros da dimensão. O acesso concedido ou negado a um membro da dimensão afeta o acesso que uma função terá aos membros da dimensão relacionados. Por exemplo, se você limitar uma função de forma que ela só acesse a subcategoria de produto Mountain Bikes, os membros da função só poderão exibir a categoria de produto Bicicletas e os produtos e as vendas pertencentes à subcategoria Mountain Bikes. Escolhendo um paradigma de modelagem de dados no SQL Server 2012 Analysis Services 21 Em um projeto de tabela, você implementa a segurança em nível de linha concedendo acesso às linhas de uma tabela. Em um projeto de tabela do SQL Server Data Tools, você concede permissão inserindo uma expressão DAX que filtra as linhas de uma tabela. A função terá acesso às novas linhas da tabela se elas satisfizerem o filtro DAX. O acesso concedido a uma linha de uma tabela afeta o acesso que uma função tem às linhas de tabelas relacionadas. Se duas tabelas tiverem uma relação um-para-muitos, os filtros de linha da tabela no lado 'um' da relação filtrarão linhas da tabela no lado 'muitos' da relação, mas não no sentido contrário. Por exemplo, se você limitar uma função de forma que ela só exiba a linha Mountain Bikes na tabela de subcategoria do produto, os membros da função só poderão exibir linhas nas tabelas de produtos e de vendas relacionadas à subcategoria Bicicletas. No entanto, os membros da função ainda podem exibir todas as linhas da tabela de categorias de produto (bicicletas, roupas e assim por diante). Segurança dinâmica Sua organização pode precisar limitar o acesso aos dados com base em uma ID de usuário ou em outros critérios dinâmicos. Por exemplo, os associados só poderão ver seus próprios dados de desempenho e RH. Entretanto, a criação de uma função de segurança para cada indivíduo em uma organização pode ser impraticável. Em vez disso, você pode implementar a segurança dinâmica, que oferece o recurso de orientar a lógica de segurança com base em uma ID de usuário ou em outros critérios dinâmicos. Os projetos de tabela e multidimensional dão suporte à segurança dinâmica. Você poderá configurar a segurança dinâmica baseada em usuário se seus dados contiverem uma relação entre as IDs de usuário e os dados que os usuários têm permissão de acessar incluindo a relação na expressão MDX ou DAX que estiver usando para gerenciar permissões. Segurança em nível da célula e avançada Para muitos aplicativos, há uma necessidade de restringir o acesso a dados usando critérios mais complexos do que simplesmente uma linha em uma tabela. Faça, por exemplo, uma pesquisa de satisfação do funcionário que compartilhe resultados agregados de uma pesquisa de feedback. Esses modelos geralmente contêm dados altamente confidenciais, e as respostas individuais da pesquisa devem ser protegidas. Embora o modelo possa não conter nomes de pessoas, com um tamanho de amostra pequeno o suficiente, a identidade dos entrevistados individuais poderia ser derivada. Nesses casos, talvez você queira implementar uma lógica mais complexa que examine o tamanho da amostra e só permita o acesso à medida resultante se o número de respostas for maior do que uma determinada contagem de respostas. Além disso, pode haver perguntas específicas e combinações de métricas que você queira restringir especificamente ao RH. Os projetos multidimensionais permitem nativamente que você implemente recursos avançados de segurança não disponíveis em um projeto de tabela. Em um projeto multidimensional, você pode implementar a segurança em nível de célula para restringir o acesso a uma célula ou a um grupo de células específico em seu modelo. A segurança em nível de célula não é fornecida em um modelo de tabela. Além disso, os projetos multidimensionais também permitem controlar o uso de totais visuais, conceder ou negar permissão para detalhar os dados e criar membros padrão para cada função. Escolhendo um paradigma de modelagem de dados no SQL Server 2012 Analysis Services 22 Em um projeto multidimensional, os valores resumidos agregados previamente são calculados quando os dados são processados em um modelo para melhorar os tempos de resposta da consulta. Por exemplo, Vendas de Todos os Produtos é um valor précalculado. A segurança dos dados de dimensão é aplicada após o processamento dos dados e, portanto, se um usuário só tiver permissão para acessar a categoria Bicicletas, por padrão o valor de Vendas de Todos os Produtos será a soma de vendas de acessórios, bicicletas, roupas e assim por diante. Isso pode ou não ser o valor que você deseja que membros da função vejam. Nesse caso, se você quiser que o valor de Vendas de Todos os Produtos esteja limitado ao valor de vendas de bicicletas, deverá habilitar os totais visuais. A habilitação de totais visuais restringe os valores resumidos de modo que eles sejam iguais à soma dos valores detalhados para os quais uma função tem permissão de acesso. Essa alteração afeta o tempo de resposta da consulta, porque os valores resumidos devem ser calculados no momento da consulta. Os projetos de tabela não calculam previamente valores resumidos e, portanto, os valores resumidos são sempre iguais à soma de valores detalhados, ou seja, os totais visuais estão sempre habilitados em um modelo de tabela. Em um modelo multidimensional, você pode habilitar a permissão para detalhar os dados para cada função. Em um modelo de tabela, as funções não são usadas para controlar o acesso ao recurso de detalhamento. Em vez disso, todas as funções são capazes de detalhar. Em um modelo multidimensional, você pode especificar um membro padrão para cada atributo em uma dimensão. Um membro padrão se comporta como um filtro que é aplicado automaticamente. Por exemplo, se o membro padrão de ano for 2012, então, por padrão, apenas os dados de 2012 serão exibidos. No entanto, um usuário pode optar por consultar dados de um ano diferente ou consultar dados de todos os anos. Em um modelo multidimensional, você pode configurar um membro padrão para cada atributo que se aplique a todas as funções ou pode especificar um membro padrão diferente para cada função. Em um modelo de tabela, você não pode especificar um valor padrão. Em vez disso, se desejar um filtro padrão, deverá configurar esse recurso em sua ferramenta de relatórios e análise. Escolhendo um paradigma de modelagem de dados no SQL Server 2012 Analysis Services 23 Resumo No SQL Server 2012, a Microsoft introduziu o BISM (Business Intelligence Semantic Model) para dar suporte a diversas necessidades de relatório e análise. As duas experiências de modelagem encapsuladas no BISM, modelagem multidimensional e de tabela, fornecem os recursos complementares que permitem a você aproveitar os recursos que melhor atendam às suas necessidades. A modelagem de tabela proporciona uma experiência de modelagem prontamente acessível, com recursos de relatório que atenderão à maioria das necessidades da análise. A maioria dos usuários está acostumada a trabalhar com tabelas e relações e aprende rapidamente a implementar a lógica de negócios usando a linguagem DAX, semelhante ao Excel. A facilidade de uso e a modelagem simplificada e flexível fornecidas pela experiência de tabela significam que as soluções podem ser desenvolvidas com rapidez. O mecanismo xVelocity, na memória e baseado em colunas, oferece resposta de consulta extremamente rápida para conjuntos de dados que podem conter bilhões de registros. Os modelos de tabela dão suporte a todas as ferramentas de relatório e análise que geram consultas MDX, como o Excel e o Reporting Services, e também ao Power View do Reporting Services, que gera consultas DAX. A modelagem multidimensional fornece extensos recursos para ajudá-lo a gerenciar seus desafios mais complexos e seus maiores desafios de BI. O modelo de dados multidimensional combinado a uma linguagem MDX fornece funcionalidade pronta para uso, para que você possa criar modelos personalizados sofisticados e implementar lógica de negócios complexa. O armazenamento de dados em disco, as agregações pré-calculadas e o cache em memória permitem que os modelos multidimensionais cresçam até a escala de vários terabytes e ofereçam respostas rápida a consultas. Com a segurança em nível de célula, você pode atender aos requisitos de segurança mais rigorosos. Em resumo, a modelagem de tabela oferece uma experiência de modelagem simplificada com recursos que devem atender à maioria das suas necessidades de relatório e análise. Se você necessitar de modelagem, lógica de negócios ou segurança complexas, ou se precisar de uma solução em escala muito grande, a modelagem multidimensional poderá ser a melhor solução para suas necessidades. Escolhendo um paradigma de modelagem de dados no SQL Server 2012 Analysis Services 24 A tabela a seguir fornece uma comparação resumida das características dos modelos multidimensional e de tabela. Grupo de recursos Critérios para a decisão Tempo da solução Curva de aprendizagem Multidimensional/ de tabela / / Modelo de dados Relações de dados / Modelo de dados Hierarquias / Modelo de dados Lógica de negócios Lógica de negócios Lógica de negócios Recursos de modelagem de dados adicionais Linguagem de cálculo Cálculos / / / Funções de agregação / Modelagem multidimensional Tempo maior da solução. A modelagem dimensional e a linguagem MDX criam uma curva de aprendizagem mais acentuada, mas fornecem recursos mais complexos nativamente. Um-para-muitos. Muitos-para-muitos. As relações de referência devem ser modeladas explicitamente. Suporte nativo para hierarquias padrão, desbalanceadas e pai-filho Perspectivas, traduções, ações, detalhamento, procedimentos armazenados e write-back. MDX Suporte nativo para cálculos comuns e complexos. Sum, Count, Min, Max, Distinct Count, None, ByAccount, AverageOfChildren, FirstChild, LastChild, FirstNonEmpty e LastNonEmpty. Modelagem de tabela Tempo menor da solução. A modelagem relacional e a linguagem DAX, semelhante ao Excel, criam uma curva de aprendizagem menos acentuada, mas os recursos complexos podem exigir expressões DAX sofisticadas. Um-para-muitos. Muitos-para-muitos exige expressões DAX. As relações da modelagem de tabela criam relações de referência. Suporte nativo para hierarquias padrão. As hierarquias pai-filho exigem expressões DAX. Perspectivas e detalhamento. DAX Suporte nativo para cálculos comuns e muito complexos. Sum, Count, Min, Max, Average, DistinctCount e diversas funções de inteligência de dados temporais, como FirstDate, LastDate, OpeningBalanceMonth e ClosingBalanceMonth. Grupo de recursos Lógica de negócios Critérios para a decisão Lógica de hierarquia Multidimensional/ de tabela Modelagem multidimensional Funções para navegar por hierarquias padrão e pai-filho. / Lógica de negócios KPIs Lógica de negócios Conversão de moeda Acesso a dados e armazenamento Acesso a dados e armazenamento Escala / / / Desempenho / Acesso a dados e armazenamento Fontes de dados Acesso a dados e armazenamento Linguagem de consulta Real, meta, status e tendência, com indicadores gráficos Oferece suporte à conversão de várias moedas usando o Assistente de Business Intelligence. Escala extremamente grande (vários terabytes) Índices e valores de medida préagregada armazenados em disco. Dados de dimensão e resultados de consultas armazenados em cache na memória. Compactação de dados de aproximadamente 3x. Bancos de dados relacionais / / MDX Modelagem de tabela Funções DAX para navegar por hierarquias pai-filho, expressões DAX para implementar lógica em dimensões padrão. A lógica de hierarquia geralmente é mais difícil usando DAX. Real, meta e status, com indicadores gráficos. Implemente usando expressões DAX. Grande escala (bilhões de registros) Armazenamento de dados baseado em coluna na memória. Compactação de dados de aproximadamente 10x. Bancos de dados relacionais, Excel, Texto, feeds OData, Azure Data Market, Analysis Services. DAX MDX (somente modo Na Memória) Grupo de recursos Acesso a dados e armazenamento Critérios para a decisão Armazenamento de dados Multidimensional/ de tabela / Acesso a dados e armazenamento Acesso a dados e armazenamento Compactação de dados Ferramentas de cliente / / Acesso a dados e armazenamento Programação Segurança Segurança / / - menos recursos. - mais recursos. Modelagem multidimensional MOLAP - dimensão, fatos e dados agregados armazenados em disco. Dados de dimensão e resultados de consultas armazenados em cache na memória. ROLAP – dimensão, fato e dados agregados armazenados em um banco de dados relacional. Normalmente 3x. Excel, Reporting Services, Microsoft PerformancePoint e outras ferramentas de cliente de terceiros. Power View do Reporting Services com suporte em versões futuras do SQL Server. XMLA, ASSL, ADOMD.NET, MSOLAP, AMO, Windows PowerShell para AMO. Desenvolvido para uso com modelos multidimensionais. Segurança de membros da dimensão e em nível de célula Segurança dinâmica. Modelagem de tabela Na Memória - todos os dados armazenados em cache na memória utilizando o mecanismo analítico baseado em colunas do xVelocity DirectQuery – dados armazenados no SQL Server 2012. Normalmente 10x. Power View do Reporting Services, Excel, Reporting Services, Microsoft PerformancePoint e outras ferramentas de cliente de terceiros. XMLA, ASSL, ADOMD.NET, MSOLAP, AMO, PowerShell para AMO. Disponível, mas menos intuitivo para uso com modelos de tabela. Segurança em nível de linha. Segurança dinâmica. Para saber mais Site do SQL Server http://www.microsoft.com/sqlserver/ SQL Server TechCenter http://technet.microsoft.com/pt-br/sqlserver/ SQL Server DevCenter http://msdn.microsoft.com/pt-br/sqlserver/ Blog da equipe do Analysis Services e do PowerPivot http://blogs.msdn.com/b/analysisservices/ Analysis Services http://msdn.microsoft.com/pt-br/library/bb522607.aspx Modelagem multidimensional (SSAS) http://msdn.microsoft.com/pt-br/library/hh230904.aspx Modelagem de tabela (SSAS de tabela) http://msdn.microsoft.com/pt-br/library/hh212945.aspx Recursos por modo de servidor ou tipo de solução (SSAS) http://msdn.microsoft.com/pt-br/library/hh212940(v=sql.110).aspx Este white paper ajudou você? Envie seus comentários. Em uma escala de 1 (ruim) a 5 (excelente), como você classificaria este white paper e por quê? Por exemplo: Você escolheu uma classificação alta porque ele tem bons exemplos, excelentes capturas de tela, clareza do texto ou por outro motivo? Você escolheu uma classificação baixa devido a exemplos insatisfatórios, capturas de tela confusas ou texto confuso? Os comentários nos ajudam a melhorar a qualidade dos white papers que lançamos. Enviar comentários. Escolhendo um paradigma de modelagem de dados no SQL Server 2012 Analysis Services 28
