BusinessObjects

O blog do Consultor BO

@aggregate_aware function: Não é tão monstruoso assim…

Hoje eu irei cortar algumas cabeças desse dragão chamado Aggregate Aware. Essa é uma função do universo muito útil, porém o conceito para utiliza-la é um pouco complexo, mas não um dragão de 7 cabeças (apenas 3 cabeças).

Tudo começa com a questão de métricas aditivas, semi-aditivas ou não-aditivas:

  • Aditivas: métrica que pode ser agregada por todas as dimensões relacionadas. Exemplo: valor de pedido, ele pode ser somado por cliente, período, produto, estado, etc.;
  • Semi-aditivas: métrica que pode ser agregada apenas por algumas dimensões relacionadas. Exemplo: Saldo de conta corrente pode ser somado por conta, agência, banco, cliente, mas não pode ser somado por tempo, ou seja, somar o saldo de janeiro com o saldo de fevereiro;
  • Não-aditiva: é o inverso da aditiva, são métrica que não pode ser agregada. Exemplo: valores percentuais derivados de outras métricas aditivas. É mais viável calcular a métrica em tempo de execução;

A função @aggregate_aware resolve essa questão também. Vou explicar utilizando um caso de uma modelagem para saldos de contas bancárias.

Para esse caso vou utilizar a ferramenta Universe Design Tool produzindo um UNV que serve tanto para o BO 4.0 quanto para o BO 3.1. A lógica se aplica para o UNX também, porém feita na ferramenta Information Design Tool.

Vamos considerar o seguinte universo:
aggregate_aware_01

 

Observando o modelo de dados, temos apenas 3 tabelas:

  • conta: Tabela da dimensão conta, que se refere a contas correntes e conta poupanças;
  • periodo: Tabela da dimensão período, que se refere os dias compreendidos entre 01/01/2010 à 20/09/2013;
  • saldo: Tabela fato de indicadores de Entrada, Saída e Saldo diariamente das contas;

Nesse modelo, se efetuar uma consulta pelos objetos [Conta], [Dia] e [Saldo] o SQL gerado será:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT DISTINCT
  conta.conta_cod,
  periodo.dia_data,
  sum(saldos.Saldo)
FROM
  conta,
  periodo,
  saldos
WHERE
  ( conta.conta_cod=saldos.conta_cod  )
  AND  ( saldos.periodo_cod=periodo.periodo_cod  )
GROUP BY
  conta.conta_cod, 
  periodo.dia_data

Porém se efetuar uma consulta de um nível mais alto com os objetos [Banco], [Mês] e [Saldo] o SQL gerado será:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT DISTINCT
  conta.banco_cod,
  periodo.mes_cod,
  sum(saldos.Saldo)
FROM
  conta,
  periodo,
  saldos
WHERE
  ( conta.conta_cod=saldos.conta_cod  )
  AND  ( saldos.periodo_cod=periodo.periodo_cod  )
GROUP BY
  conta.banco_cod, 
  periodo.mes_cod

O valor apresentado pela métrica [Saldo] estará errado, pois a consulta irá somar o saldo de todos os dias do mês.

Então, qual é a regra de agregação de Saldos?
Saldo de um determinado mês: Saldo do ultimo dia do mês;
Saldo de um determinado ano: Saldo do ultimo dia do ultimo mês do ano;

Talvez você fosse responder que o Saldo Mensal corresponde o Saldo do dia 31 ou 30 ou 28. Porém se os dados estiverem parciais no mês, por exemplo, com a data base de 20/09/2013 (como é o caso dos dados do meu exemplo) o ultimo dia do mês de setembro de 2013 não é 30, e sim o dia 20.

O caso para o Saldo Anual segue a mesma lógica: para o ano de 2013 o saldo corresponde ao saldo do dia 20 de setembro, porém para os anos anteriores corresponde o saldo do dia 31 de dezembro.

Essa questão é resolvida na modelagem dos dados e sua respectiva carga, mas basicamente a lógica é: identificar qual é o ultimo dia do mês e identificar qual é o ultimo dia do ano.

No modelo de dados desse exemplo, na tabela de “período” existe uma flag que determina se aquele dia é o ultimo dia do mês em que ele pertence (dia_ultimo_mes). Existe outra flag que determina se aquele mês é o ultimo mês do ano em que ele pertence (mes_ultimo_ano). Com a combinação dos dois eu sei qual é o ultimo dia do ano.

Considero que esses campos devem ser manipulados em tempo de carga do banco, mantendo a posição correta da data base dos dados carregados.

Considerando tudo isso, ao efetuar uma consulta de um nível mais alto com os objetos [Banco], [Mês] e [Saldo] o SQL gerado deverá ser:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT DISTINCT
  conta.banco_cod,
  periodo.mes_cod,
  sum(saldos.Saldo)
FROM
  conta,
  periodo,
  saldos
WHERE
  ( conta.conta_cod=saldos.conta_cod  )
  AND  ( saldos.periodo_cod=periodo.periodo_cod  )
  AND  ( periodo.dia_ultimo_mes=1 )
GROUP BY
  conta.banco_cod, 
  periodo.mes_cod

Se fosse efetuado com os objetos [Banco], [Ano] e [Saldo] o SQL gerado deverá ser:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT DISTINCT
  conta.banco_cod,
  periodo.ano,
  sum(saldos.Saldo)
FROM
  conta,
  periodo,
  saldos
WHERE
  ( conta.conta_cod=saldos.conta_cod  )
  AND  ( saldos.periodo_cod=periodo.periodo_cod  )
  AND  ( periodo.dia_ultimo_mes=1 )
  AND  ( periodo.mes_ultimo_ano=1 )
GROUP BY
  conta.banco_cod, 
  periodo.ano

Voltando para o universo, não gostaríamos que os usuários das ferramentas do BO precisassem implementar essas regras nas suas consultas sobre universo. O cenário ideal é que o usuário precisasse simplesmente incluir as dimensões que ele deseja, incluísse a métrica de saldo e o universo se virasse para montar a consulta com as devidas regras. Isso é possível, mas antes temos de chegar ao @aggregate_aware é necessário criar as estruturas necessárias.

Vou abstrair essas regras de saldo ano e saldo mês em 2 views no banco. Elas também poderiam ser tabelas derivadas no universo, porém, para facilitar o entendimento das consultas SQL mais a frente, vou abstrair diretamente no banco. Abaixo segue o SQL das 2 views no banco:

saldos_mes:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT DISTINCT
  conta.conta_cod,
  periodo.periodo_cod,
  sum(saldos.Saldo) as Saldo
FROM
  conta,
  periodo,
  saldos
WHERE
  ( conta.conta_cod=saldos.conta_cod  )
  AND  ( saldos.periodo_cod=periodo.periodo_cod  )
  AND  ( periodo.dia_ultimo_mes  =  1 )
GROUP BY
  conta.conta_cod, 
  periodo.periodo_cod

saldos_ano:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT DISTINCT
  conta.conta_cod,
  periodo.periodo_cod,
  sum(saldos.Saldo) as Saldo
FROM
  conta,
  periodo,
  saldos
WHERE
  ( conta.conta_cod=saldos.conta_cod  )
  AND ( saldos.periodo_cod=periodo.periodo_cod  )
  AND ( periodo.dia_ultimo_mes = 1 )
  AND ( periodo.mes_ultimo_ano = 1 )
GROUP BY
  conta.conta_cod, 
  periodo.periodo_cod

O universo com as novas tabelas ficaria assim:
aggregate_aware_02

Os contextos do universo ficariam assim:
saldos_dia:
aggregate_aware_03

saldos_mes:
aggregate_aware_04

saldos_ano:
aggregate_aware_05

Agora entra o @aggragate_aware. A lógica de funcionamento é assim:

  • Para uma análise diária, o campo saldo será da tabela “saldos”. Exemplo: Objetos selecionados na consulta seriam [Conta], [Dia] e [Saldo] e os campos das tabelas seriam respectivamente conta.conta_cod, período.dia_data e saldos.saldo;
  • Para uma análise mensal, o campo saldo será da tabela “saldo_mes”. Exemplo: Objetos selecionados na consulta seriam [Conta], [Mês] e [Saldo] e os campos das tabelas seriam respectivamente conta.conta_cod, período.mes_cod e saldos_mes.saldo;
  • Para uma análise anual, o campo saldo será da tabela “saldo_mes”. Exemplo: Objetos selecionados na consulta seriam [Conta], [Mês] e [Saldo] e os campos das tabelas seriam respectivamente conta.conta_cod, período.mes_cod e saldos_mes.saldo;

Mesmo que a consulta tenha objetos de 2 níveis de período, será respeitado o de menor nível. Exemplo: Objetos selecionados na consulta seriam [Conta], [Ano], [Mês] e [Saldo], o saldo seria o campo saldo da tabela saldo_mes.

Na métrica [Saldo] coloca-se a seguinte fórmula no Select:

aggregate_aware_06

@Aggregate_Aware(sum(saldos_ano.Saldo), sum(saldos_mes.Saldo), sum(saldos.Saldo))

A função @aggregate_aware esta considerando as regras que falamos acima, primeiro o saldo anual, depois o saldo mensal, e por fim o saldo diário.

O último passo é definir a navegação agregada, ela trabalha junto com a função @aggregate_aware. Sem definir a navegação agregada, a solução não irá funcionar.

A navegação agregada é a relação entre os objetos e tabelas do schema. Vamos definir no universo que o objeto [Dia] é incompatível com a tabela “saldos_mes“ e “saldos_ano”, que o objeto [Mês] é incompatível com a tabela “saldos_ano”.

Para configurar a navegação agregada acesse o menu “Tools > Aggregate Navigation…”:
aggregate_aware_07

Na lista da esquerda ficam as tabelas do universo. No painel da direita ficam os objetos marcados como INCOMPATÍVEIS com a tabela selecionada na esquerda.
Selecione a tabela saldos_ano, marque os objetos [Mês] e [Dia]:
aggregate_aware_08
Selecione a tabela saldos_mes, marque o objeto [Dia]:
aggregate_aware_09
Clique em OK e pronto, a navegação agregada já esta configurada. Agora vamos testar!
Para o teste podemos utilizar o Web Intelligence ou mesmo a ferramenta de consultas do próprio Universe Design Tool: O Query Panel. Ele é o mesmo painel de consulta que o Desktop Intelligence, muito familiar ao painel de consulta do Web Intelligence. Para acessar essa ferramenta vá ao menu “Tool > Query Panel”. Esse recurso não exibe os dados da consulta, ele apenas monta e faz um teste de execução. Para verificar o SQL gerado é necessário clicar no botão View SQL:
aggregate_aware_10.0
Abaixo as consultas realizadas e os SQL gerados:
aggregate_aware_10.1.1 aggregate_aware_10.1.2
aggregate_aware_10.2.1 aggregate_aware_10.2.2
aggregate_aware_10.3.1 aggregate_aware_10.3.2
aggregate_aware_10.4.1 aggregate_aware_10.4.2
Basicamente lidamos com um caso em que foi necessário fazer outras tabelas fato para agregações (no caso utilizamos views). Essa técnica de modelagem não existe apenas para resolver questões de métricas semi-aditivas (como no exemplo do saldo), ela também é utilizada para desempenho de consultas. Vou dar um Exemplo:
Considere que você esta com um banco modelado do Submarino (aquele site de compras pela internet), para analisar as vendas dos produtos. O menor nível de agregação na tabela fato é com o número do pedido. Porém alguns usuários fazem análises mensais ignorando o numero do pedido. Imagine então efetuar um “group by” de um período de 12 meses sobre todos esses registros de pedidos. A consulta não terá um bom desempenho. Mas se você criar uma nova tabela fato agregando os dados sem o número de pedido, sem o dia, sem dimensões que não fazem parte das análises mensais, o volume de registros dessa nova tabela será bem menor e com isso a consulta será bem mais rápida. Nesse exemplo o @aggregate_aware entra para as métricas do universo apontando para a tabela granular ou a agregada conforme os objetos selecionados pelo usuário durante a consulta.
Então pessoal, com a devida utilização da função @aggregate_aware, o que antes era criado um universo para análise anual, outro para mensal, e outro para diário, com a função @agrregate_aware é possível condensar esses 3 universos em 1 universo apenas.
Clique aqui para fazer o download do exemplo.

, , , , , , ,

4 thoughts on “@aggregate_aware function: Não é tão monstruoso assim…

  • Caio Henrique disse:

    Cara que ótimo exemplo!

    Ficou super simples de entender toda a lógica, muito obrigado e continue com o bom trabalho!

  • Anderson disse:

    Bom dia estou com um problema para resolver e não acho uma solução…

    Preciso fazer uma soma que acumula o valor dos ultimos 12 meses de cada linha(data).

    tenho os campos data e valor, para cada linha quero ter um acumulados dos meses anteriores.

    Alguem pode ajudar?

    • Gustavo Henrique Oliveira disse:

      Olá Anderson, isso não é um bicho de sete cabeças. Vamos por partes. Considere a TAB1 (DATE, VALUE) conforme você falou. No Universo crie uma tabela derivada TABNY com um SQL sobre a tabela TAB1, só que esse SQL irá retornar um campo a mais: DateNY (Data Next Year), esse campo é o campo Date com mais 1 ano (que é 12 meses). Imagino que o SQL em (Em MSSQL) seja algo semelhante à:
      SELECT DATE, VALUE, DATEADD(YEAR, 1, DATE) DATENY FROM TAB1;
      Então você terá 2 tabelas: TAB1 (DATE, VALUE) e TABNY (DATE, VALUE, DATENY). No universo faça o link entre essas tabelas pelos campos TAB1.DATE = TABNY.DATENY. O campo TABNY.DATE pode ser ignorado, ele esta ai só pra te ajudar a ver qual é a data original. Considerando o campo TAB1.DATE como referência, o campo TAB1.VALUE é valor referente a data do campo TAB1.DATE, o campo TABNY.VALUE é o valor do ano passado em relação ao campo TAB1.DATE. Espero que te ajude.

  • Montoya disse:

    Animal seu exemplo…

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *