Planejando o datamart para cenários e soluções de planejamento de BI

Artigo
03/07/2017

Aplica-se a: SharePoint Server 2010 Enterprise

Tópico modificado em: 2016-11-30

Neste artigo:

Esquema e layout
Modelos para solução
Criando tabelas de dimensões
Criando tabelas de hierarquias
Criando tabelas de fatos

Esquema e layout

O datamart será criado com um servidor relacional do SQL Server 2008 R2 a ser usado como ponto único de registro para os operadores de informações, com um controle rígido de todos os dados que entram nesse datamart. O datamart será usado pelo servidor do SSAS como fonte de dados central para todos os cubos, dimensões e hierarquias. Três tipos de tabelas serão necessárias para o suporte ao modelo de dados do SSAS: tabelas de dimensões, de hierarquias e de fatos.

Observação

É possível que um cubo use mais de uma tabela de fatos. Isso pode ser obtido por meio de um particionamento no grupo de medidas e também com o uso de mais de um grupo de medidas no cubo.

Criaremos os modelos a seguir para oferecer suporte aos modelos de dados necessários para o processo de planejamento. Esses modelos determinarão quantas tabelas o nosso datamart incluirá.

Modelos para solução

Previsão: esse modelo será usado principalmente para capturar a entrada de dados referentes a previsões de receita e despesas operacionais para os períodos futuros.
Conta: a dimensão de conta conterá o plano de contas, que mostra itens de receita e despesa para previsão
Cenário: a dimensão de cenário particionará os dados entre "Reais" e "Previstos"
Tempo: a dimensão de tempo determinará os períodos fiscais para a previsão.
Região Geográfica: a dimensão de região geográfica será usada para controlar o processo de entrada de dados por cada operador de informações. Os operadores de informações individuais de cada região geográfica farão a entrada de dados em suas moedas locais.
Produto: a dimensão de produto é usada para representar a lista completa de produtos ativos e disponíveis. A receita será prevista por produto.
Orçamento de RH: esse modelo será usado por gerentes de linha para incluir no orçamento os funcionários esperados ao longo do ano fiscal. Os operadores de informações poderão interagir com esse modelo inserindo horas as trabalhadas esperadas e a classificação de nível salarial para cada recurso. Um teste de hipóteses será executado para determinar o orçamento necessário para a folha de pagamentos com base em alterações nas premissas.
Métrica: essa dimensão armazenará membros como "Horas Trabalhadas", "Nível Salarial" e "Compensação Total", entre outros
Região Geográfica: o local que receberá o recurso.
Tempo: orçamento para os funcionários a ser feito anualmente.
Funcionário: uma lista de recursos existentes e novos espaços reservados TBH.
Taxas de Pagamento: esse modelo é usado para definir as taxas de pagamento base para o ano. As informações sobre taxas de pagamento serão inseridas no modelo Orçamento de RH como dados base de premissas para uso em cálculos.
Tempo: taxas de pagamento são inseridas anualmente.
Nível Salarial: uma lista de níveis salariais que determinará o salário base.
Taxas de Câmbio: o modelo de taxa de câmbio é usado para determinar as taxas de conversão de moeda a serem usadas por mês quando dados forem convertidos do modelo de previsão no modelo de consolidação financeira. Como os dados são inseridos em cada região geográfica com base na moeda local, a tabela de taxas de câmbio será usada para regras de conversão de moeda e pacotes de fluxo de dados.
Tempo: taxas de câmbio são inseridas mensalmente.
Moeda de Origem: a moeda de origem de uma conversão.
Moeda de Destino: a moeda de destino de uma conversão.
Consolidação Financeira: o modelo de consolidação financeira é usado para relatórios financeiros usando uma única moeda em todas as regiões geográficas.
Conta: plano de contas consolidado.
Cenário: conterá "Reais" e "Previstos"
Tempo: o nível mais baixo de granularidade será de meses
Região Geográfica: todas as regiões geográficas com Lucros e Perdas
Tipo de Moeda: visualize os dados na moeda de relatório (EUR) ou na moeda local, que será determinada pela região geográfica selecionada no filtro.

Com um bom entendimento dos modelos necessários, podemos configurar o repositório de dados. Teremos aqui cinco tabelas de fatos e as tabelas de dimensões e hierarquias apropriadas. Essas tabelas serão organizadas em um esquema em estrela, com as tabelas de fatos no centro e as tabelas de dimensões e hierarquias formando os pontos externos desse esquema em "estrela". Definindo relacionamentos por meio de chaves estrangeiras entre as tabelas de fatos, dimensões e hierarquias no repositório de dados, podemos gerar rapidamente o modo de exibição de fonte de dados no SSAS quando chegar a hora de criar os cubos e as dimensões.

Criando tabelas de dimensões

Dimensões são os blocos de construção de qualquer banco de dados multidimensional. O agrupamento de um conjunto de dimensões formará a base geral de um cubo. Tabelas de dimensões armazenam dados de um determinado tipo. Por exemplo, você tem uma tabela de dimensões para armazenar todos os membros de conta, sendo que cada linha dessa tabela representa um membro de conta exclusivo da dimensão. Tabelas de dimensões também podem armazenar qualquer propriedade relacionada por meio de colunas de tabela. Por exemplo, na dimensão Conta, temos uma coluna chamada "AccountType" que armazena o tipo de conta específico do membro da dimensão.

MemberId	MemberLabel	MemberName	SortOrder	AccountType	ExpenseType
1	3100	Receita de Vendas	100	Rendimento	não aplicável
2	3200	Outras Receitas Operacionais	200	Rendimento	não aplicável
3	8100	Receitas de Juros	300	Rendimento	não aplicável

MemberId	MemberLabel	MemberName	SortOrder	Moeda de Entrada	Moeda de Destino
1	SEA	Seattle	100	USD	USD
2	OLY	Olympia	200	USD	USD
3	SPK	Spokane	300	USD	USD

Recomendação

Recomendamos que os campos a seguir sejam criados para uma tabela de dimensões:

ID: convém que as chaves de dimensão sejam do tipo inteiro (TinyInt, SmallInt, Int, BigInt) em vez de qualquer outro tipo de desempenho ideal. Consulte a seção sobre desempenho para obter uma leitura adicional. Além disso, use o tipo de dados mais apropriado com base no magnitude da dimensão.

Rótulo: use um código exclusivo para a legenda/nome de exibição de um membro da dimensão. Manter esse código exclusivo permitirá que você crie um cubo baseado em regras usando um MdxScript legível em vez de especificar um membro usando a notação de valores de chave, como '&[chave]'.

Nome: com muita frequência, os usuários finais preferirão ver os membros de uma dimensão com um nome amigável em vez do código do rótulo. Por exemplo, na nossa solução, temos códigos de conta usados em rótulo que farão sentido durante a criação de regras de cálculo, mas não quando forem exibidos em uma Tabela Dinâmica. A criação dessa propriedade garante que você possa atualizar com facilidade nomes para exibição sem afetar a lógica subjacente para definições de regras.

Ordem: é uma boa ideia ter uma coluna de classificação que possa ser usada pela dimensão para determinar a ordenação dos membros dessa dimensão.

As tabelas de dimensões para cenários de planejamento em geral não excedem 200.000 membros. Se você estiver trabalhando com dimensões que se tornaram muito grandes, recomendamos reduzir essas dimensões. Os dados associados a membros de dimensão reduzidos podem ser agregados e adicionados a outros membros de dimensão. Via de regra, quanto menores as dimensões, melhor será o desempenho geral dos cubos de planejamento.

Observação

Colunas de dimensão e propriedades de membro estão intimamente relacionadas.

Criando tabelas de hierarquias

Tabelas de hierarquias são necessárias quando você utiliza o recurso de hierarquia Pai-Filhos do SSAS. Essa hierarquia deve usar três colunas, chave (que representa um membro da dimensão da hierarquia), chave pai (outro membro da mesma dimensão) e classificação, para ordenar esses membros em um nível da hierarquia. A maioria das hierarquias pai-filhos tem suporte nessas três colunas, exceto quando uma agregação personalizada de membros é necessária. Por exemplo, um plano de contas precisará de uma agregação personalizada para ser definido. Agregações de conta são determinadas pelo tipo de conta de cada membro de conta e seu respectivo membro de conta pai. Para oferecer suporte a hierarquias que exigem agregação personalizada, devemos criar uma quarta coluna. Essa coluna, Operador Unário, conterá os valores +, - e ~ (+ significando agregar ao pai, - subtrair do pai e ~ ignorar a agregação ao pai).

ID	ID do Pai	Ordem de Classificação	Operador Unário	Rótulo	Nome
1	102	100	+	3100	Receita de Vendas
2	102	200	+	3200	Outras Receitas Operacionais
3	103	300	+	8100	Receitas de Juros
4	103	400	+	9100	Ganho de Venda de Ativos

Id	ID do Pai	Ordem de Classificação	Rótulo	Nome
1	4	500	SEA	Seattle
2	4	700	OLY	Olympia
3	4	600	SPK	Spokane

Hierarquias baseadas em nível são criadas de acordo com as colunas definidas em uma dimensão. As colunas de uma tabela relacional podem construir hierarquias de atributos no SSAS. A definição de relacionamentos entre hierarquias de atributos permitirá que você crie hierarquias baseadas em nível de eficiência. Por enquanto, será suficiente incluir todas as propriedades relacionadas de uma dimensão como colunas na tabela de dimensões quando for o momento de criar hierarquias baseadas em nível no SSAS.

Para obter mais informações, consulte o artigo sobre hierarquias e níveis.

Criando tabelas de fatos

Tabelas de fatos armazenam todos os dados numéricos de um cubo. O número de colunas de uma tabela de fatos varia dependendo de quantas dimensões estão associadas a esse cubo. Por exemplo, o cubo de Previsão tem sete colunas, das quais seis representam cada dimensão relacionada ao cubo de previsão e uma é usada para armazenar o valor numérico. A coluna que armazena o valor numérico é chamada de "medida". Na nossa solução, usamos somente uma medida para a tabela de fatos.

Cada coluna diferente da coluna de medida é relacionada a uma dimensão por meio de sua chave. No exemplo a seguir, vemos que o modelo Orçamento de RH tem cinco dimensões relacionadas à tabela de fatos, e cada linha dessa tabela de fatos representa um único registro de fatos. Convém evitar valores duplicados nos registros de fatos das chaves de dimensão, por exemplo, um valor idêntico aplicável a todas as chaves de dimensão em várias linhas de fatos. Se for esse o caso, combine o valor em uma única linha.

Rowld	MetricID	GeographyID	EmployeeID	TimeID	Valor
1	2	15	1010	20100000	2000
2	2	15	1009	20100000	2000
3	2	15	1008	20100000	2000

Rowld	AccountID	TimeID	ScenarioID	GeographyID	ProductID	VersionID	Valor
1151	1	20100200	1	1	232	1	1391
1153	1	20100400	2	1	232	1	1124
1155	1	20100600	2	1	232	1	1322

Compartilhar via