DAX para modelos multidimensionais

Aplica-se a: SQL Server Analysis Services Azure Analysis Services Fabric/Power BI Premium

Este artigo descreve como o Power BI usa consultas DAX (Expressões de Análise de Dados) para relatar modelos multidimensionais em SQL Server Analysis Services.

Historicamente, os aplicativos de relatório usam MDX (Expressões Multidimensionais) como uma linguagem de consulta em bancos de dados multidimensionais. O MDX é otimizado para padrões visuais comuns, como Tabelas Dinâmicas no Excel e outros aplicativos de relatório direcionados à semântica de negócios multidimensional. A partir do SQL Server 2012 SP1, o Analysis Services dá suporte ao uso de DAX e MDX em modelos multidimensionais e tabulares. O DAX, no entanto, foi originalmente projetado para modelos de dados tabulares. Embora o DAX seja considerado mais fácil de usar, ele também é mais focado em visualizações de dados mais simples, como tabelas, gráficos e mapas em relatórios e painéis. O Power BI usa DAX para consultar modelos tabulares e multidimensionais.

Como o DAX é projetado principalmente para modelos tabulares, há alguns mapeamentos e restrições interessantes e úteis que devem ser compreendidos ao usar DAX em modelos multidimensionais.

Compatibilidade

O Power BI usa DAX para consultar modelos multidimensionais do Analysis Services no SQL Server 2016 e edições Enterprise ou Standard posteriores. SQL Server 2012 e SQL Server edições Enterprise ou Business Intelligence de 2014 também têm suporte, no entanto, essas versões agora estão sem suporte base.

Recursos

DAX não é um subconjunto de MDX. O DAX foi projetado inicialmente para ser semelhante à linguagem de fórmula do Excel. Em modelos tabulares, o DAX é usado em um armazenamento de dados relacional composto por tabelas e relações. O DAX também é usado para criar medidas personalizadas, colunas calculadas e regras de segurança em nível de linha.

Além de ser uma linguagem de cálculo, o DAX também pode ser usado para executar consultas. Este artigo descreve como as consultas DAX funcionam em um modelo multidimensional.

Interação entre MDX e DAX

As expressões DAX têm suporte apenas em modelos tabulares. Você não pode usar medidas criadas por uma expressão DAX em um modelo multidimensional. Uma consulta DAX para um modelo multidimensional pode referenciar uma medida ou outro cálculo definido nesse modelo, mas esses cálculos devem ser criados usando a linguagem MDX. Expressões DAX não podem ser usadas quando uma expressão MDX é necessária e vice-versa, e algumas funções DAX, como PATH, não são aplicáveis na modelagem multidimensional.

Sintaxe DAX

A sintaxe de fórmulas DAX é bem parecida com a de fórmulas do Excel e usa uma combinação de funções, operadores e valores. Para saber mais sobre a sintaxe para funções individuais, consulte a referência de função DAX.

Mapeamento de objeto multidimensional a tabular

O Analysis Services oferece uma representação de metadados de modelo tabular de um modelo multidimensional. Os objetos em modelos multidimensionais são representados como objetos tabulares no Power BI. Esse mapeamento é exposto ao Power BI usando o conjunto de linhas de esquema DISCOVER_CSDL_METADATA.

Mapeamento de objetos

Medidas, grupos de medidas e KPIs

Os grupos de medidas em um cubo multidimensional são mostrados na lista Campos do Power BI como tabelas com um ícone de calculadora.

Medidas em um grupo de medidas aparecem como medidas. Se houver medidas calculadas que não tenham um grupo de medidas associado, elas serão agrupadas sob uma tabela especial chamada Medidas.

Para ajudar a simplificar modelos multidimensionais mais complexos, os autores do modelo poderão definir um conjunto de medidas ou KPIs em um cubo a ser colocado em uma pasta de exibição. O Power BI pode mostrar pastas de exibição e as medidas e KPIs nelas.

Medidas e KPIs em um grupo de medidas

Medidas como variantes

As medidas em modelos multidimensionais são variantes. Isso significa que as medidas não são fortemente tipadas e podem ter tipos de dados diferentes. Por exemplo, na imagem abaixo, a medida Valor na tabela Relatório Financeiro por padrão é Conversor de Moedas tipo de dados, mas também tem um valor de cadeia de caracteres NA para o subtotal de Contas Estatísticas, que é tipo de dados String. O Power BI reconhece determinadas medidas como variantes e mostra os valores e a formatação corretos nas diferentes visualizações.

Medida como variante

Medidas implícitas

Os modelos tabulares oferece aos usuários a capacidade de criar medidas implícitas como contagem, soma ou média em campos. Para os modelos multidimensionais, como os dados de atributo de dimensão são armazenados de forma diferente, a consulta de medidas implícitas poderá demorar mais. Por isso, medidas implícitas contra modelos multidimensionais não estão disponíveis no Power BI.

Dimensões, atributos e hierarquias

As dimensões de cubo são expostas como tabelas em metadados tabulares. Na lista Campos do Power BI, os atributos de dimensão são mostrados como colunas dentro de pastas de exibição. Os atributos de dimensão que têm a propriedade AttributeHierarchyEnabled definida como False; por exemplo: o atributo Data de Nascimento na dimensão Cliente ou a propriedade AttributeHierarchyVisible definida como false não aparecerá na lista Campos do Power BI. Hierarquias de vários níveis ou hierarquias de usuário; por exemplo, Geografia do Cliente na dimensão Cliente, são expostas como hierarquias na lista Campos do Power BI. UnknownMembers ocultos de um atributo de dimensão são expostos em consultas DAX e no Power BI.

Dimensão, atributos e hierarquias na lista de campos do SQL Server Data Tools (SSDT) e do Power BI

Tipo de atributo de dimensão

Os modelos multidimensionais dão suporte à associação de atributos de dimensão com tipos de atributo de dimensão específicos. A imagem abaixo mostra a dimensão Geografia em que os atributos de dimensão Cidade, Estado- Província, País e Código Postal têm tipos de geografia associados a eles. Eles serão expostos nos metadados tabulares. O Power BI reconhece os metadados que permitem que os usuários criem visualizações de mapa. Isso é indicado pelo ícone de mapa ao lado das colunas Cidade, País, Cep e State-Province na tabela Geografia na Lista de Campos do Power BI.

Dimensão geography na lista campos do SSDT e do Power BI

Membros calculados de dimensão

Modelos multidimensionais dão suporte a membros calculados para filho de Todos com um único membro real. As restrições adicionais durante a exposição deste tipo de membro calculado são:

• Deve ser um único membro real quando a dimensão tiver mais de um atributo.
• Um atributo com membros calculados não poderá ser o atributo de chave da dimensão, a menos que seja o único atributo.
• Um atributo com membros calculados não pode ser um atributo pai-filho.

Os membros calculados das hierarquias de usuário não são expostos no Power BI, no entanto, os usuários ainda podem se conectar a um cubo que contém membros calculados em hierarquias de usuário.

Membros padrão

Membros padrão de suporte a modelos multidimensionais para atributos de dimensão. O membro padrão é usado pelo Analysis Services durante a agregação de dados para uma consulta. O membro padrão de um atributo de dimensão é exposto como o valor ou o filtro padrão para a coluna correspondente nos metadados tabulares.

O Power BI se comporta da mesma forma que as Tabelas Dinâmicas do Excel quando os atributos são aplicados. Quando um usuário adiciona uma coluna a uma visualização do Power BI (tabela, matriz ou gráfico) que contém um valor padrão, o valor padrão não será aplicado e todos os valores disponíveis serão mostrados. Se o usuário adicionar a coluna a Filtros, o valor padrão será aplicado.

Segurança de dimensão

Dimensão de suporte a modelos multidimensionais e segurança no nível da célula por meio de funções. Um usuário que se conecta a um cubo usando o Power BI é autenticado e avaliado quanto às permissões apropriadas definidas pelas funções às quais o usuário pertence. Quando a segurança da dimensão é aplicada, os respectivos membros da dimensão não são vistos pelo usuário no Power BI. No entanto, se um usuário tiver uma permissão de segurança de célula definida onde determinadas células são restritas, esse usuário não poderá se conectar ao cubo com o Power BI. Em alguns casos, os usuários poderão agregar dados quando partes desses dados forem calculados a partir dos dados protegidos.

Atributos/hierarquias não agregáveis

Em modelos multidimensionais, os atributos de uma dimensão podem ter a propriedade IsAggregatable definida como False. Isso significa que o autor do modelo especificou que os aplicativos de relatório não devem agregar os dados entre hierarquias (atributo ou vários níveis) ao consultar os dados. No Power BI, esse atributo de dimensão é exposto como uma coluna para a qual os subtotais não estão disponíveis. Na imagem a seguir, você verá um exemplo de uma hierarquia não aggregável, Contas. O nível superior da hierarquia pai-filho de Contas é não agregável, enquanto os outros níveis são agregáveis. Em uma visualização de matriz da hierarquia Contas (dois primeiros níveis), você vê subtotais para Nível de Conta 02 , mas não para o nível mais alto, Nível de Conta 01.

Hierarquia não agregável no Power BI

Imagens

O Power BI fornece a capacidade de renderizar imagens. Em modelos multidimensionais, uma das maneiras de fornecer imagens a serem mostradas no Power BI é expor colunas que contêm URLs (Uniform Resource Locator) das imagens. O Analysis Services dá suporte à marcação de atributos de dimensão como o tipo ImageURL. Esse tipo de dados é fornecido ao Power BI nos metadados tabulares. Em seguida, o Power BI pode baixar e exibir as imagens especificadas nas URLs em visualizações.

Tipo de atributo de dimensão ImageURL no SSDT

Hierarquias pai-filho

Modelos multidimensionais dão suporte a hierarquias pai-filho, que são expostas como uma hierarquia em metadados tabulares. Cada nível da hierarquia pai-filho é exposta como uma coluna oculta. O atributo de chave da dimensão pai-filho não é exposto nos metadados tabulares.

Hierarquias pai-filho na lista de campos do SSDT e do Power BI

Perspectivas e traduções

As perspectivas são exibições de cubos, onde somente determinadas dimensões ou grupos de medidas estão visíveis em ferramentas de cliente. Você pode especificar um nome de perspectiva como um valor para a propriedade cube cadeia de conexão. Por exemplo, no seguinte cadeia de conexão, 'Vendas Diretas' é uma perspectiva no modelo multidimensional:

Data Source=localhost;Initial Catalog=AdventureWorksDW-MD;Cube='Direct Sales'

Os cubos podem ter metadados e traduções de dados especificadas para vários idiomas dentro do modelo. Para ver as traduções (dados e metadados), um aplicativo pode adicionar a propriedade opcional Identificador de Localidade ao cadeia de conexão, por exemplo:

Data Source=localhost;Initial Catalog=AdventureWorksDW-MD;Cube='Adventure Works'; Locale Identifier=3084

Quando Power BI Desktop se conecta a um modelo multidimensional, ele passa automaticamente a localidade de usuário atual identificada para o servidor. No entanto, isso não ocorre para um relatório publicado no serviço do Power BI.

Recursos sem suporte

Segurança em nível de célula – não há suporte em relatórios do Power BI.

Ações – não há suporte em relatórios do Power BI ou em consultas DAX em um modelo multidimensional.

Conjuntos nomeados – em modelos multidimensionais, não têm suporte no Power BI ou em consultas DAX em um modelo multidimensional.

Anotações de CSDLBI

Os metadados de cubo multidimensional são expostos como um modelo conceitual baseado em EDM (Modelo de Dados de Entidade) por anotações de CSDLBI (Linguagem de Definição de Esquema Conceitual com Business Intelligence).

Os metadados multidimensionais são representados como um namespace de modelo tabular em um documento CSDLBI, ou CSDL de saída, quando uma solicitação DISCOVER_CSDL_METADATA for enviada para a instância do Analysis Services.

Exemplo: solicitação de DISCOVER_CSDL_METADATA

<Envelope xmlns="http://schemas.xmlsoap.org/soap/envelope/">
   <Body>
      <Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
         <RequestType>DISCOVER_CSDL_METADATA</RequestType>
         <Restrictions>
            <RestrictionList>
              <CATALOG_NAME>"catalogname"<CATALOG_NAME>
            </RestrictionList>
         </Restrictions>
         <Properties>
            <PropertyList>
            </PropertyList>
         </Properties>
      </Discover>
   </Body>
</Envelope>

A solicitação DISCOVER_CSDL_METADATA tem as seguintes restrições:

O documento CSDL de saída de retorno representa o modelo como um namespace, que contém entidades, associações e propriedades.

Para saber mais sobre anotações CSDLBI, consulte Referência técnica para anotações de BI para CSDL e [MS-CSDLBI]: Formato de arquivo de definições de esquema conceitual com anotações do Business Intelligence.

SuperDAXMD

A cada versão do SQL Server Analysis Services, as melhorias dão suporte a funcionalidades e funções DAX novas e existentes. No SQL Server 2019 CU5, uma classe de funções DAX introduzidas pela primeira vez para modelos tabulares informalmente conhecidos como SuperDAX agora estão habilitadas para modelos multidimensionais.

Embora alguns padrões de consulta DAX existentes precisem ser reprojetados, as funções SuperDAX fornecem melhorias significativas no desempenho da consulta. Os padrões de consulta DAX modernos que usam o SuperDAX para modelos multidimensionais fornecem um forte incentivo para que as organizações que usam o Power BI atualizem seus servidores de fonte de dados multidimensionais para SQL Server 2019 com CU5. Para saber mais, confira SuperDAX para modelos multidimensionais.

Confira também

Referência do DAX