Tempo de leitura:
As funções CUBO permitem analisar dados no Excel sem recorrer à tradicional tabela dinâmica. Quando temos um modelo de dados em Power Pivot, podemos usar todas as tabelas do modelo, medidas e KPIs criados no Power Pivot para criar relatórios na folha de cálculo, “retirando” os dados que existem no modelo e utilizá-los em qualquer célula da folha, sem estarmos limitados à estrutura da Tabela Dinâmica.
Assim as funções CUBO, são ideais para criar relatórios interativos e análises de negócios mais complexas, uma vez que podes utilizar fórmulas DAX.
Existem 7 funções de CUBO, das quais vamos analisar 6 no tutorial.
- CONJUNTOCUBO [CUBESET]: Permite obter os valores únicos de um CUBO (modelo), semelhante à função EXCLUSIVOS no Excel.
- CONTARCONJUNTOCUBO [CUBESETCOUNT]: Permite contar os valores únicos do conjunto do CUBO.
- VALORCUBO [CUBEVALUE]: Devolve um valor do cubo, um valor escalar, como se fosse uma medida.
- MEMBROCLASSIFICADOCUBO [CUBERANKEDMEMBER]: Devolve o enésimo valor ou a classificação mais alta num conjunto -> CONJUNTOCUBO [CUBESET]
- MEMBROCUBO [CUBEMEMBER]: Devolve o membro (item) específico do CUBO.
- MEMBROKPICUBO [CUBEKPIMEMBER]: Devolve uma propriedade de indicador de desempenho (KPI) e mostra o nome de KPI na célula.
As definições são “estranhas”, mas as funções são mais fáceis do que parecem.
Genericamente todas as funções usam argumentos semelhantes dos quais, nesta fase, destaco 2:
Ligação (obrigatório): Apenas o nome da ligação ao cubo, que deve ser colocado entre “” e o nome é o nome do Modelo de Dados (Data Model).
Expressão_membro: Texto com uma expressão multidimensional (MDX) que avalia um membro exclusivo do cubo. Basicamente o nome de um dos elementos do Modelo de Dados, uma Tabela, ou coluna, ou mesmo uma Expressão DAX. Algumas funções que se baseiam no CONJUNTOCUBO pedem a Expressão_conjunto.
Iniciar com uma Tabela Dinâmica
Vamos iniciar o exemplo com uma Tabela Dinâmica. Esta tabela dinâmica é definida em função do modelo de dados, o que significa que podemos construí-la a partir de todas as tabelas disponíveis no modelo.
Definimos de seguida a tabela dinâmica com os campos que pretendermos. Uma vez que estamos a usar o modelo de dados com as relações, podemos usar qualquer tabela do Modelo.
Esta tabela funciona, mas estamos limitados a uma estrutura tabular ou a um relatório cruzado. Com as funções CUBO podemos obter os valores do modelo e colocar o resultado em qualquer célula do nosso relatório.
Função CONJUNTOCUBO [CUBESET]
Vamos começar com a função CONJUNTOCUBO. Com esta função podemos obter os valores de um conjunto (uma coluna de uma tabela) por exemplo, de modo a obtermos os valores únicos / exclusivos.
A função é composta por 4 argumentos dos quais 2 são obrigatórios:
- Ligação: Define a ligação ao modelo de dados. Colocamos entre aspas o nome do Modelo de Dados do Ficheiro.
- Expressao_conjunto: Em texto também, identificamos o conjunto de membros que pretendemos obter. No exemplo podemos verificar que vamos obter uma coluna da tabela Marca. A opção Children permite obter todos os “itens” da coluna em particular.
- Podemos ainda identificar uma legenda, que será basicamente o nome a aparecer na célula.
- Definimos ainda opcionalmente a forma como os membros do conjunto são ordenados.
Começamos por criar um conjunto sobre uma das dimensões -> Marca.
=CONJUNTOCUBO(“ThisWorkbookDataModel”;”[Marca].[Marca].Children”;”Marcas”)
Repetimos o processo para criar um conjunto de cada uma das dimensões pretendidas: Categorias, Lojas e Produtos
=CONJUNTOCUBO(“ThisWorkbookDataModel”;”[Produtos].[Categoria].Children”;”Categorias”)
=CONJUNTOCUBO(“ThisWorkbookDataModel”;”[Loja].[Loja].Children”;”Lojas”)
=CONJUNTOCUBO(“ThisWorkbookDataModel”;”[Produtos].[Produto].Children”;”Produtos”)
Obter o número de elementos / itens no conjunto
Para obtermos o número de itens, ou elementos do conjunto usamos a função CONTARCONJUNTOCUBO [CUBESETCOUNT].
A função apenas necessita de um parâmetro ou argumento. Este argumento é o conjunto do cubo, definido no passo anterior. Podemos colocar a função CONJUNTOCUBO dentro deste argumento ou usar a célula que representa o conjunto.
Podemos repetir o processo para as restantes dimensões, ou seja, os restantes conjuntos.
Obter um valor do CUBO – Medida
No próximo exemplo vamos obter um “valor” do modelo (CUBO). Este valor normalmente é uma medida.
A função necessita de uma ligação ao Modelo, e uma ou várias expressões de um dos membros (itens) do Modelo. Nos exemplos seguintes vamos verificar que podemos obter um valor, baseado em vários membros.
No cenário vamos obter um valor a partir de um dos membros do modelo, uma Medida previamente calculada.
A fórmula final é a seguinte:
=VALORCUBO(“ThisWorkbookDataModel”;”[Measures].[Valor Total]”)
E o resultado é o apresentado.
Podemos repetir o processo para as restantes “Medidas”.
Obter um membro do cubo por classificação
A função MEMBROCLASSIFICADOCUBO [CUBERANKEDMEMBER] permite obter um membro (item) do cubo pela sua classificação, dependente por exemplo de um conjunto do cubo.
A função solicita (1) a ligação ao modelo, (2) o conjunto do cubo (CUBESET) e o membro a obter pela classificação.
=MEMBROCLASSIFICADOCUBO(“ThisWorkbookDataModel”;B7;3)
No exemplo estamos a usar o conjunto (Marcas) e a obter o 3º elemento do conjunto.
Contudo para o efeito do exemplo, pretendemos obter não apenas 1 item do ranking, mas sim todos. Para tal podemos usar a função SEQUÊNCIA [SEQUENCE] para devolver todos os valores do conjunto (CONJUNTOCUBO). Com este cenário podemos aproveitar também o valor devolvido pela função (CONTARCONJUNTOCUBO) que nos devolve todos os valores do conjunto.
Assim a fórmula final será:
=MEMBROCLASSIFICADOCUBO(“ThisWorkbookDataModel”;B7;SEQUÊNCIA(C7;1))
Vamos repetir o mesmo processo para aplicar agora nas colunas as categorias dos produtos.
=MEMBROCLASSIFICADOCUBO(“ThisWorkbookDataModel”;B8;SEQUÊNCIA(1;C8))
Para continuarmos a definir a nossa “Tabela Dinâmica” precisamos ainda de definir a medida “Base” que vai ser usada para calcular os valores de cada membro.
Neste caso vamos aplicar a função MEMBROCUBO para obter novamente um membro do cubo -> uma medida, que será a medida base. No exemplo vamos usar a medida “Valor Total”
=MEMBROCUBO(“ThisWorkbookDataModel”;”[Measures].[Valor Total]”;”Total”)
E de seguida vamos usar a função VALORCUBO para obter o valor do respetivo membro. Neste exemplo vamos verificar que o valor do cubo vai depender de 3 membros: (1) Medida Valor Total; (2) Marca e (3) Categoria. Ou seja, respetivamente a mesma lógica da estrutura de “construção” de uma Tabela Dinâmica.
=VALORCUBO(“ThisWorkbookDataModel”;$H$16;$H17;I$16)
A expressão retorna o valor esperado que é o Valor Total para a marca e categoria específicas. O facto de trancarmos as referências das células, permite adaptar a fórmula para outras células.
Adicionar mais detalhes ao Relatório
Usando as funções de CUBO podemos melhorar o aspeto do nosso relatório. Vamos utilizar objetos de Segmentação de Dados, que são elementos que podem ser ligados ao Modelo de Dados, e funcionarem também como membros do cubo.
Acrescentamos a Segmentação de dados pretendida, e podemos também adicionar uma linha cronológica se pretendido.
A segmentação de dados é tipicamente ligada a uma tabela dinâmica pelo botão “Ligações de Relatórios” existente no menu contextual da Segmentação de Dados.
No cenário pretendemos usar a função CUBO para criar a ligação. Para tal basta alterar a fórmula adicionando mais um membro -> a segmentação de dados.
Conseguimos verificar o efeito do filtro tanto na Tabela Dinâmica como no “relatório” com as funções.
Analisar indicadores KPI
Para terminar o exemplo vamos ver como podemos também usar os KPIs para definir o relatório com as funções CUBO.
Para já vamos começar por definir um KPI. Um KPI simples que avalia a Medida da Média, onde atribuímos um valor num intervalo entre 3500 a 4000.
- Valores abaixo de 3500 – ícone Vermelho
- Valores acima de 4000 – ícone verde
- Valores restantes – ícone amarelo
Para podemos consultar os valores fora da Tabela Dinâmica necessitamos de usar uma conjugação de 2 funções:
- MEMBROKPICUBO [CUBEKPIMEMBER]: Esta função permite retirar propriedades do KPI, como por exemplo o estado do KPI.
- VALORCUBO [CUBEVALUE]: Esta função é aquela que nos permite obter o valor sobre o estado do KPI.
Pela ordem correta a função VALORCUBO embrulha a função MEMBROKPICUBO.
Vamos preparar a base para obter os valores dos KPIs.
Passo 1: Obter uma lista de membros do cubo.
Passo 2: Obter os valores médios de cada um dos membros.
Passo 3: Obter o estado do KPI
Para obter o estado do KPI é que vamos usar a função MEMBROKPICUBO [CUBEKPIMEMBER] que permite obter apenas propriedades do KIP. As propriedades são definidas no último argumento pelo número. Os números mais habituais são 1 – ValorKPI, 2 – Objetivo (o valor definido do Objetivo) e 3 – Estado KPI que é o que pretendemos no caso.
O segundo argumento é o nome do KPI que pretendemos obter.
=MEMBROKPICUBO(“ThisWorkbookDataModel”;”Valor Médio”;3))
Esta fórmula por si só não nos devolve nada interessante como podemos verificar na imagem.
Esta função tem de ser envolvida agora na função 2. VALORCUBO para obtermos o valor do Estado do KPI.
Para dar cor aos valores do estado podemos usar formatação condicional.
E assim podemos verificar o relatório completo com as funções de CUBO.
Próximo artigo:
Artigo Anterior:
Artigos por Categoria
Artigos por Categoria
- Microsoft Excel (40)
- Power Apps (16)
- Power Automate (3)
- Power BI (10)
- Power Query (12)
- Python (3)
- VBA (7)
Leave a Reply