Execução Ano até a Data (YTD) em períodos fiscais com…

Neste post do blog, mostraremos as opções para modelar uma métrica Year to Date (YTD) em execução no SAP Datasphere, utilizando abordagens diferentes. Este post é o terceiro de uma série que inclui:

1. Geração do calendário fiscal para o SAP Datasphere utilizando o procedimento integrado
2. Geração de calendários fiscais autônomos e semanais para o SAP Datasphere
3. Execução Year to Date (YTD) nos períodos fiscais com o SAP Datasphere (este post)

Vamos examinar algumas razões para modelar uma métrica YTD no SAP Datasphere, conforme descrito neste blog:

1. O SAP Datasphere, no momento da redação deste artigo, não inclui funcionalidades OLAP prontas para definir métricas YTD ou outros tipos de totais parciais;
2. O SAP Analytics Cloud oferece YTD e outros parâmetros temporais de execução ou rotação prontos para uso, mas (também no momento da redação deste artigo) não os suporta para calendários fiscais no SAP Datasphere. Mesmo para dados baseados no calendário gregoriano, existem algumas limitações;
3. Você tem outra ferramenta (front-end) que se conecta ao SAP Datasphere e que pode não oferecer suporte para essas métricas baseadas no tempo;
4. Você deseja definir centralmente essas métricas.

O cálculo YTD neste blog é baseado nos períodos fiscais, mas você também pode basear o cálculo em outras características temporais, fiscais ou não, como semana do calendário, mês do calendário ou trimestre fiscal.

Junte os dados de transações com o calendário fiscal

Começamos com um conjunto de transações de amostra. O conjunto de dados é composto por mais de 25 milhões de registros, então podemos também falar sobre o desempenho das diferentes visualizações e consultas. Primeiro, unimos a tabela de transações com o calendário fiscal do post anterior do blog. É uma simples junção interna da data da transação com a data do calendário fiscal. Agora temos o ano fiscal e o período fiscal como parte de nossos dados de transação, que são os atributos básicos para nossas métricas YTD.

Figura 1: visualização dos fatos com junção entre a data da transação e a data do calendário fiscal

Obtenha simplesmente um valor YTD (ainda não em execução)

Passe para a próxima seção se estiver procurando um resultado desde o início do ano corrente. Mas se o resultado desejado for apenas um único valor YTD de um ano e período específicos, isso poderia ser obtido usando um simples filtro. Por exemplo, você pode usar um filtro de medida limitada em um modelo analítico, que exigirá que o usuário selecione um determinado período fiscal. Você deve primeiro definir a variável de medida limitada conforme ilustrado abaixo.

Figura 2: definição de medida limitada em um modelo analítico

Após definir a medida limitada, aplique o filtro variável dentro de uma medida limitada, conforme ilustrado abaixo.

Figura 3: aplicar a variável em uma medida limitada

Na visualização dos dados abaixo do editor do modelo analítico, escolhi o período 6 quando foi solicitada a variável, o que produz o resultado conforme ilustrado abaixo.

Figura 4: resultado no modelador analítico ao filtrar pelo período == 6

Ok, foi bastante simples, já que é apenas uma soma com um filtro aplicado. Você pode modelar isso em uma visualização SQL, em uma visualização gráfica ou em uma visualização analítica como mostrado acima. Mas e se quisermos mostrar os dados YTD atuais (ou seja, cumulativos), onde para cada período um valor YTD resume os períodos anteriores?

Executando YTD – opção 1 – função de janela

Para obter um resultado YTD em execução, onde cada período é exibido com seu valor YTD em execução correspondente, precisamos recorrer ao SQL. Em geral, existem duas opções principais. Uma usa uma função de janela e a outra usa uma junção com uma tabela auxiliar. Nesta seção, explicamos a opção da função de janela e na próxima seção a junção com uma tabela auxiliar. No SAP Datasphere, escrevemos o SQL dentro de uma visualização SQL definida com o uso semântico «fato», para que possamos usar a visualização posteriormente em um modelo analítico.

Com uma função de janela, você pode escrever uma instrução SQL bastante elegante. Abaixo está colada uma instrução SQL de exemplo. Existem duas partes principais da consulta:

1. Uma subconsulta que resume o valor para o ano e período;
2. Uma seleção na subconsulta, onde YTD_AMOUNT define o comportamento YTD móvel usando uma função de janela.

No caso de você não estar familiarizado com as funções de janela, explicamos o que a função de janela faz neste caso. Cada linha resultante da subconsulta representa um período fiscal. Para cada linha, a função de janela opera com uma janela de partição. Neste caso, a janela contém todas as linhas com o mesmo ano que a linha atual, desde a primeira linha da partição até a linha atual. Por que até a linha atual, você pode perguntar? Bem, esse é o comportamento padrão da janela de partições e a configuração padrão é exatamente o que precisamos. Como as linhas estão ordenadas por período, a janela de partição contém o período atual e o anterior. E estes são resumidos no valor de ano até à data.

--Função YTD de janela com subconsulta
SELECT
F_YEAR,
F_PERIOD,
PERIOD_AMOUNT,
SUM(PERIOD_AMOUNT) OVER (PARTITION BY F_YEAR ORDER BY F_PERIOD) 
AS YTD_AMOUNT
FROM (
SELECT
F_YEAR,
F_PERIOD,
SUM(AMOUNT) AS PERIOD_AMOUNT
FROM
FT_TX_FISCPER
GROUP BY
F_YEAR, F_PERIOD
)
ORDER BY
F_YEAR, F_PERIOD;

Figura 5: Resultado da consulta YTD com função de janela

Portanto, as bases são realmente bastante simples, uma vez que se entenda como as funções de janela funcionam. Torna-se um pouco mais complexo quando há requisitos mais avançados do que apenas exibir os valores YTD por período. O que acontece se você precisar dividir o resultado com base em atributos adicionais? Nesse caso, você terá que incluir esse atributo em sua consulta. Por exemplo, veja como a consulta é modificada para trazer os valores YTD por produto, além de ano e período.

--YTD, agora baseado em produto
SELECT
PRODUCT,
F_YEAR,
F_PERIOD,
PERIOD_AMOUNT,
SUM(PERIOD_AMOUNT) OVER (PARTITION BY PRODUCT, F_YEAR ORDER BY F_PERIOD) AS YTD_AMOUNT
FROM (
SELECT
PRODUCT,
F_YEAR,
F_PERIOD,
SUM(AMOUNT) AS PERIOD_AMOUNT
FROM
FT_TX_FISCPER
GROUP BY
PRODUCT, F_YEAR, F_PERIOD
)
ORDER BY
PRODUCT, F_YEAR, F_PERIOD;

Como você pode ver, é necessário modificar toda a consulta para levar em consideração a divisão do resultado em outro atributo. E aqui também é preciso ter cuidado: adicionando atributos, você aumenta o conjunto de resultados da subconsulta. Tudo bem se o conjunto de resultados da subconsulta permanecer relativamente pequeno. Se esse subconjunto se tornar bastante grande, a função de janela terá seu preço. Mesmo que divida o atributo em uma visualização ou em uma ferramenta downstream, resumindo novamente o resultado da consulta, a subconsulta na qual a função de janela opera não ficará menor.

Além disso, tenha em mente que há uma limitação no uso de funções de janela para esse caso de uso, ou seja, a subconsulta que você está utilizando deve conter dados para cada período passado. Caso contrário, o período para o qual não há dados disponíveis não fará parte do resultado. Para casos de uso produtivos, isso provavelmente não acontecerá quando a subconsulta se agregará em um nível bastante alto de ano e período. Mas no caso de estender a consulta ou filtrar a consulta, por exemplo, com base no atributo «produto», um produto pode ter períodos para os quais não há dados disponíveis. Isso pode ser aceitável, mesmo que pareça um pouco estranho: você não verá os períodos exibidos para os quais não havia valores. Mas tenha cuidado ao usar o resultado da consulta em outra consulta ou ferramenta; ao remover a dimensão «produto» para incluí-la apenas no ano e no período, as somas YTD não serão corretas.

Figura 6: lacunas nos resultados YTD se nem todos os períodos tiverem dados para um produto

Executando YTD – opção 2 – junção com a tabela auxiliar

Um método clássico para criar um resultado YTD em execução usando SQL é o uso de uma tabela auxiliar. Novamente, existem várias opções e aqui estou usando a tabela de suporte mais simples que me vem à mente. Definimos uma tabela auxiliar com apenas uma enumeração dos períodos em um ano, no meu caso 12. Para isso, criamos uma tabela local com uma coluna e usamos a função Editor de tabelas para inserir os diferentes valores, como pode ser visto abaixo.

Figura 7: tabela local com uma lista de períodos em um ano fiscal

A tabela auxiliar da lista de períodos permitirá resumir os períodos anteriores em um ano fiscal através de uma junção não equi do período dos dados da transação com a lista de períodos. Veja a instrução SQL e o conjunto de resultados abaixo. A consulta SQL é composta pelas seguintes partes:

• Uma subconsulta que resume o valor para o ano e período;
• Uma junção não equi que une cada período a todos os períodos seguintes do ano fiscal;
• A coluna F_PERIOD_AMOUNT soma apenas o valor para o período atual, usando uma instrução CASE;
• A coluna YTD_AMOUNT resume todas as linhas e, com isso, representa os valores YTD atuais.

--YTD usando lista de períodos e subconsulta
SELECT 
F_YEAR,
P.F_PERIOD,
SUM(CASE WHEN T.F_PERIOD = P.F_PERIOD THEN PERIOD_AMOUNT ELSE 0 END) 
AS F_PERIOD_AMOUNT,
SUM(PERIOD_AMOUNT) AS YTD_AMOUNT
FROM (
SELECT
F_YEAR,
F_PERIOD,
SUM(AMOUNT) AS PERIOD_AMOUNT
FROM
FT_TX_FISCPER
GROUP BY
F_YEAR, F_PERIOD
) T
JOIN
LT_PERIODLIST P ON T.F_PERIOD <= P.F_PERIOD
GROUP BY
F_YEAR, P.F_PERIOD
ORDER BY
F_YEAR, P.F_PERIOD;

Figura 8: saída da consulta YTD com tabela auxiliar

Você pode ver que o resultado é o mesmo da função de janela. Apenas a maneira de calculá-lo é diferente. No entanto, há uma vantagem com essa abordagem: mesmo que o resultado da subconsulta tenha lacunas, o que significa que para determinados períodos não há valores reais, os valores YTD ainda são calculados e exibidos. No caso de você estender a consulta com outros atributos e depois executar novamente o roll-up da consulta, o resultado ainda será correto. No entanto, tenha em mente que, com um amplo conjunto de resultados da subconsulta, a junção com a tabela auxiliar e a lógica CASE terão seu preço, assim como a opção da função de janela. Em meus testes, a opção da função de janela teve um desempenho um pouco melhor.

Desempenho de junção equi versus junção não equi

A consulta anterior utiliza uma junção não equi e uma tabela simples com uma enumeração de períodos para fazer o rollup. No entanto, também poderíamos usar uma tabela periódica ou uma visualização um pouco mais avançada, para podermos usar uma junção equi, que alguns argumentam ser mais eficiente do que uma junção não equi. Se sua subconsulta é pequena, não importa, mas se for maior, pode importar. No entanto, em meus testes com um conjunto de dados de mais de 25 milhões e um resultado de subconsulta de 20 milhões, a junção não equi realmente obteve melhores resultados.

Mas se você insistir, para fazer isso, você poderia definir uma tabela com duas colunas: F_PERIOD e F_PERIOD_MAPPED, e preenchê-la como mostra a visualização dos dados a seguir. Para cada período, você mapeia cada período a ser agrupado.

Figura 9: tabela de mapeamento de períodos

A consulta seria um pouco diferente, como mostrado no trecho de código a seguir.

--YTD usando tabela auxiliar de mapeamento de períodos
SELECT 
F_YEAR,
P.F_PERIOD,
SUM(CASE WHEN T.F_PERIOD = P.F_PERIOD THEN AMOUNT ELSE 0 END) 
AS F_PERIOD_AMOUNT,
SUM(AMOUNT) AS YTD_AMOUNT
FROM
FT_TX_FISCPER T
INNER JOIN
LV_YTD_PERIOD_MAPPING P ON T.F_PERIOD = P.F_PERIOD_MAPPED
GROUP BY
F_YEAR,


FONTE