Estendendo o modelo de dados
Neste capítulo, você aprenderá como estender o modelo de dados criado nos capítulos anteriores. Estender um modelo de dados inclui -
- Adição de tabelas
- Adição de colunas calculadas em uma tabela existente
- Criação de medidas em uma tabela existente
Destes, criar as medidas é crucial, pois envolve fornecer novos insights de dados no modelo de dados que permitirão que aqueles que usam o modelo de dados evitem retrabalho e também economizem tempo ao analisar os dados e tomar decisões.
Como a análise de lucros e perdas envolve trabalhar com períodos de tempo e você usará funções DAX Time Intelligence, você precisa de uma tabela de datas no modelo de dados.
Se você é novo nas tabelas de datas, leia o capítulo - Compreendendo as tabelas de datas.
Você pode estender o modelo de dados da seguinte forma -
Para criar um relacionamento entre a tabela de dados, ou seja, a tabela Dados financeiros e a tabela Data, é necessário criar uma coluna calculada Data na tabela Dados financeiros.
Para realizar diferentes tipos de cálculos, você precisa criar relacionamentos entre a tabela de dados - Dados Financeiros e as tabelas de consulta - Contas e Localização geográfica.
Você precisa criar várias medidas que o ajudem a realizar vários cálculos e realizar a análise necessária.
Essas etapas constituem essencialmente as etapas de modelagem de dados para Análise de Lucros e Perdas usando o modelo de dados. No entanto, esta é a sequência de etapas para qualquer tipo de análise de dados que você deseja realizar com o modelo de dados Power Pivot.
Além disso, você aprenderá como criar as medidas e como usá-las nas tabelas dinâmicas avançadas nos capítulos subsequentes. Isso lhe dará compreensão suficiente da modelagem de dados com DAX e análise de dados com tabelas dinâmicas do Power.
Adicionando uma tabela de datas ao modelo de dados
Crie uma tabela de datas para os períodos de tempo que abrangem os anos fiscais da seguinte forma -
Crie uma tabela com uma única coluna com cabeçalho - Data e datas contíguas variando de 01/07/2011 a 30/06/2018 em uma nova planilha do Excel.
Copie a tabela do Excel e cole-a na janela do Power Pivot. Isso criará uma nova tabela no modelo de dados do Power Pivot.
Nomeie a tabela como Data.
Certifique-se de que a coluna Data na tabela Data seja do tipo de dados - Data (DateTime).
Em seguida, você precisa adicionar as colunas calculadas - Ano Fiscal, Trimestre Fiscal, Mês Fiscal e Mês à tabela de Data da seguinte maneira -
Ano fiscal
Suponha que o final do ano fiscal é 30 de junho th . Em seguida, um vãos ano fiscal de 1 st de Julho a 30 th junho. Por exemplo, o período de 01 de julho st , 2011 (7/1/2011) para 30 de junho th , 2012 (2012/06/30) será o ano fiscal de 2012.
Na tabela Data, suponha que você queira representar o mesmo que o ano fiscal de 2012.
Você precisa primeiro extrair a parte do exercício financeiro da Data e anexá-la ao FY.
Para as datas nos meses de julho de 2011 a dezembro de 2011, o ano financeiro é 1 + 2011.
Para as datas nos meses de janeiro de 2012 a junho de 2012, o ano fiscal é 0 + 2012.
Para generalizar, se o mês de encerramento do ano financeiro for FYE, faça o seguinte -
Integer Part of ((Month – 1)/FYE) + Year
Em seguida, pegue os 4 caracteres mais à direita para obter o Ano Financeiro.
No DAX, você pode representar o mesmo que -
RIGHT (INT ((MONTH ('Date' [Date]) - 1) / 'Date' [FYE]) + YEAR ('Date' [Date]), 4)
Adicione a coluna calculada Ano Fiscal na tabela Data com a fórmula DAX -
= "FY" & RIGHT (INT ((MONTH ('Date' [Date]) - 1) / 'Date' [FYE]) + YEAR ('Date' [Date]), 4)
Trimestre Fiscal
Se FYE representa o mês do final do ano financeiro, o trimestre financeiro é obtido como
Integer Part of ((Remainder of ((Month+FYE-1)/12) + 3)/3)
No DAX, você pode representar o mesmo que -
INT ((MOD (MONTH ('Date' [Date]) + 'Date' [FYE] -1,12) +3) / 3)
Adicione a coluna calculada Fiscal Quarter na tabela Date com a fórmula DAX -
= 'Data' [Ano Fiscal] & "- Q" & FORMATO (INT ((MOD (MÊS ('Data' [Data]) + 'Data' [FYE] -1,12) + 3) / 3), "0" )
Mês Fiscal
Se FYE representa o final do ano financeiro, o período do mês financeiro é obtido como
(Remainder of (Month+FYE-1)/12) + 1
No DAX, você pode representar o mesmo que -
MOD (MONTH ('Date' [Date]) + 'Date' [FYE] -1,12) +1
Adicione a coluna calculada Fiscal Month na tabela Date com a fórmula DAX -
= 'Data' [Ano Fiscal] & "- P" & FORMATO (MOD (MÊS ([Data]) + [FYE] -1,12) +1, "00")
Mês
Finalmente, adicione a coluna calculada Mês que representa o número do mês em um ano financeiro da seguinte maneira -
= FORMATO (MOD (MÊS ([Data]) + [FYE] -1,12) +1, "00") & "-" & FORMATO ([Data], "mmm")
A tabela de datas resultante se parece com a imagem a seguir.
Marque a tabela - Data como Tabela de Data com a coluna - Data como a coluna com valores exclusivos, conforme mostrado na imagem a seguir.
Adicionando Colunas Calculadas
Para criar um relacionamento entre a tabela Dados financeiros e a tabela Data, você precisa de uma coluna de valores de data na tabela Dados financeiros.
Adicione uma coluna calculada Data na tabela de dados financeiros com a fórmula DAX -
= DATEVALUE ('Dados Financeiros' [Mês Fiscal])
Definindo relacionamentos entre tabelas no modelo de dados
Você tem as seguintes tabelas no modelo de dados -
- Tabela de dados - Dados financeiros
- Tabelas de pesquisa - Contas e local geográfico
- Tabela de datas - data
Para definir relacionamentos entre as tabelas no modelo de dados, a seguir estão as etapas -
Visualize as tabelas na Visualização do Diagrama do Power Pivot.
Crie as seguintes relações entre as tabelas -
Relação entre a tabela Finance Data e a tabela Accounts com a coluna Account.
Relação entre a tabela Finance Data e a tabela Geography Locn com a coluna Profit Center.
Relação entre a tabela Dados Financeiros e a tabela Data com a coluna Data.
Ocultando Colunas de Ferramentas Cliente
Se houver colunas em uma tabela de dados que você não usará como campos em qualquer Tabela Dinâmica, você poderá ocultá-las no modelo de dados. Então, eles não ficarão visíveis na lista Campos da Tabela Dinâmica.
Na tabela Dados financeiros, você tem 4 colunas - mês fiscal, data, conta e centro de lucro que não usará como campos em nenhuma tabela dinâmica. Portanto, você pode ocultá-los para que não apareçam na lista Campos da tabela dinâmica.
Selecione as colunas - Mês fiscal, data, conta e centro de lucro na tabela Dados financeiros.
Clique com o botão direito e selecione Ocultar das ferramentas de cliente na lista suspensa.
Criação de medidas nas tabelas
Você está pronto para modelagem e análise de dados com DAX usando o modelo de dados e as tabelas dinâmicas do Power.
Nos capítulos subsequentes, você aprenderá como criar medidas e como usá-las nas tabelas dinâmicas do Power. Você criará todas as medidas na tabela de dados, ou seja, tabela de dados financeiros.
Você criará medidas usando fórmulas DAX na tabela de dados - Dados Financeiros, que você pode usar em qualquer número de Tabelas Dinâmicas para a análise de dados. As medidas são essencialmente os metadados. Criar medidas na tabela de dados faz parte da modelagem de dados e resumi-las nas Power PivotTables faz parte da análise de dados.