Atualização de Dimensões Usando Slowly Changing Dimensions Type 1 e Tipo 2 no SSIS

Slowly Changing Dimensions (SCD) é uma técnica de modelagem usada em data warehouses para atualização das tabelas de dimensões.

Existem duas principais metodologias para atualização de dimensões:

Tipo 1

Para esse tipo de SCD, você simplesmente sobrescreve os valores de dados existentes com ops novos valores da origem. Isso facilita a atualização da dimensão e limita o crescimento da tabela de dimensões apenas para novos registros. A desvantagem disso é que você perde o valor histórico dos dados porque a dimensão sempre conterá os valores atuais de cada atributo. Por exemplo, você tem uma dimensão de armazenamento que possui um atributo para uma região geográfica. Se houver uma mudança e algumas lojas se moverem de uma região para outra, todos os dados históricos serão movidos. Por exemplo, uma loja que pode estar reportando resultados na região Nordeste, depois altera sua localização para a região do Meio Atlântico. Mas, como resultado da atualização, agora toda a história dessa loja antes da mudança é essencialmente removida do Nordeste e transferida para o distrito do Meio Atlântico. Essa alteração distorcerá os relatórios históricos e os relatórios serão executados antes que a atualização não corresponda mais aos relatórios executados após a atualização no mesmo período de tempo.

Tipo 2

Este é o tipo mais comumente usado para contornar o problema de alteração de histórico do tipo 1. Nesse tipo, a atualização de um dos campos, marca o registro antigo como inativo. Isso permite que a tabela de fatos continue a usar a versão antiga dos dados para fins de geração de relatórios históricos, deixando os dados alterados no novo registro para impactar apenas os dados de fato desse ponto em diante. Várias colunas devem ser adicionadas à tabela de dimensões (datas de início / término do registro ativo e um sinalizador de registro ativo atual) para fornecer o gerenciamento de alterações históricas e garantir o uso ideal do registro ativo. Usando o mesmo exemplo da dimensão Tipo 1 acima, a alteração na região da loja, marcará as colunas de identificação do registro como inativo, e irá incluir um novo registro com a versão atualizada com uma nova chave. Essa nova chave de dimensão será usada na geração da tabela de fatos em andamento. Isso permite que a tabela de fatos ainda use os dados armazenados na chave de dimensão antiga para relatórios históricos. Isso garantirá que os dados permaneçam os mesmos e que um relatório histórico para o mesmo período de tempo executado antes de a atualização ser feita continuará exibindo exatamente os mesmos dados de antes da alteração ter sido feita.

Implementando o Tipo 1

Nesse tipo, os registros da tabela de dimensão destino são atualizados com os novos valores da tabela de origem. Os valores são basicamente substituídos e nenhum histórico é mantido.

Veja como será o fluxo de dados do exemplo:

1-Etapa Source SalesTerritory: Os dados de origem são extraídos da tabela de origem Sales.SalesTerritory

2-Lookup Country Region: O nome da região do país é inserido no fluxo de dados de origem para complementar o fluxo de dados de entrada. Esta etapa é opcional.

3-Slowly Changing Dimension: Etapa principal do fluxo de dados. Ela identifica os campos que serão monitorados no processo de atualização.

Nesta tela, você irá apontar para a tabela de dimensão no destino e irá marcar a coluna da chave negócio.

O chave de negócio representa a chave da tabela de origem, é a chave nativa do sistema de origem. A dimensão usará ela como chave candidata.

Ainda nesta tela, estão listadas todas as colunas de entrada que serão usadas no processo de atualização da dimensão. Marque todas como na tela abaixo e clique em próximo.

- Selecionar o tipo de alteração: identificar quais colunas de entradas serão usadas para realizar a comparação entre o sistema de origem e a dimensão de destino. Marque todas que serão monitoradas pelo processo de atualização.

Ao finalizar o processo, serão criadas duas saídas. Uma para os registros novos e outra para os registros com os campos atualizados.

- Inserir Destino - Novos registros: Verifique se os relacionamentos da tarefa "Inserir Destino" estão criados corretamente.

- Comando OLE DB - Registros atualizados: Verifique o comando de atualização e o mapeamento dos parêmentros.

Para cada linha com dados atualizados, este comando será processado.



































Mapeamento dos parâmetros.

Este tipo de atualização de dimensões é muito comum pois atende grande parte dos cenários, avalie sempre a real necessidade de usar o tipo 2, pois ela adiciona complexidade e aumenta o volume de dados na tabela de dimensão.

Este processo do tipo 1 pode ser substituído por outras técnicas de atualização incremental, já demonstrada em outros artigos neste blog.

Implementando o Tipo 2

A atualização SCD Tipo 2 é um método de atualização mais complexo, ele precisa lidar com cada atributo da dimensão de uma forma diferente.

Existem 3 tipos de tratamento que podem ser configurado para cada atributo.

- Atributo fixo: o valor não é alterado, usado quando é necessário garantir que o valores serão constantes.

- Atributo de alteração: o valor atual é sobrescrito com um novo valor. Usado em atributos que não geram a necessidade de versionar o registro para manutenção de histórico.

- Histórico de Atributo: o valor atual é marcado como inativo e um novo registro é inserido com a mesma chave candidata mas com uma chave substituta nova. Este versionamento, permite que o histórico seja mantido na tabela de fatos. Usar em casos como mudança de localização por exemplo.

Após configurar a fonte de dados, conecte à um componente SCD e inicie a configuração.













Selecione a tabela de Dimensão de Destino. Esta tabela deve ter uma coluna com a chave candidata marcada com o tipo "chave de negócio".

Além disso, a dimensão deve ter duas colunas para identificar a data de criação do registro (StartDate) e a data de inativação do registro (EndDate). Estes campos serão usados para o versionamento de registros com  colunas do tipo "histórico".

Marcar o tipo de tratamento que será dado para cada atributo. Neste momento deve ser avaliado cada atributo, caso a caso.

Nesta etapa serão configurados os tratamentos dos atributos históricos. Para identificar o valor atual e os registros históricos, serão utilizadas as colunas StartDate e EndDate da dimensão.

Após configurar o winzard são geradas automaticamente 4 outputs.

Historical Attribute Inserts Output: todos os registros que foram alterados em seus atributos marcados com o tipo "Histórico". Neste fluxo de dados, um novo registro é inserido, o registro antigo recebe a data atual na coluna "EndDate".

UPDATE [dbo].[DimEmployee]
SET [EndDate] = ? --Recebe a data atual
, [Status] = NULL
WHERE [EmployeeNationalIDAlternateKey] = ? AND [EndDate] IS NULL --NULL representa o registro atual

Após alteração, o fluxo de dados se une aos novos registros da outra saída "New Output"

New Output: São os registros identificados como novos, neste caso eles são apenas inseridos no fluxo e unidos com os registros alterados.

Changing Attribute Updates: São os registros com os atributos marcados com o tipo atributo de alteração. Neste caso, os valores atuais são sobrescritos, não são versionados.

UPDATE [dbo].[DimEmployee]
SET [CurrentFlag] = ?,[EmailAddress] = ?,[FirstName] = ?,[Gender] =
?,[LastName] = ?,[LoginID] = ?,[MaritalStatus] = ?,[MiddleName] = ?,
[Phone] = ?,[SickLeaveHours] = ?,[Title] = ?,[VacationHours] = ?
WHERE [EmployeeNationalIDAlternateKey] = ?


Com cada saída identificada, o fluxo de dados gerado pelo compomente SCD trata a maioria dos casos possíveis garantindo assim a correta atualização das dimensões.




Fonte:
https://www.nuwavesolutions.com/slowly-changing-dimensions/
Livro: Professional SQL Server Integration Services 2014

SWITCH em DAX - Condição Única e Condição Múltipla

A função SWITCH pode ser útil para tratar múltiplas condições e regras de negócios que geram novas colunas calculadas.

Muitas vezes a função IF atende bem este cenário, mas a medida que muitas condições começam a ser aninhadas, a leitura fica prejudicada, deixando a fórmula extensa e suscetível a erros.

Exemplo usando IF para criara a regra de negócio que representa o tipo de cargo:

=IF([Salario]<=3000, “Junior”,
IF([Salario]>3000, “Pleno”,
IF([Salario]>5000, “Senior I”,
IF(AND([Salario]>8000,[Salario]<=10000), “Senior II”, "Outros"))))

Veja, que devemos respeitar o encadeamento.

Trabalhando com SWITCH

Existem basicamente duas formas de usar switch

1-Condição Única:

Estabelece uma expressão e avalia os possíveis resultados.

SWITCH(expression,
   value1, result1,
   value2, result2,
    :
    :
    else
   )

expression: retorna um valor escalar que são comparados nas constantes value1 com result1.

Exemplo:
=SWITCH([MonthNum],
    1,”January”,
    2,”February”,
    3,”March”,
    4,”April”,
    5,”May”,
    6,”June”,
    7,”July”,
    8,”August”,
    9,”September”,
    10,”October”,
    11,”November”,
    12,”December”,
    “Invalid Month Number”
   )

O problema desta implementação é que ficamos restritos a avaliação de igualdade de um único valor. Já na alternativa a seguir, vamos que podemos avaliar ranges de valores.

2-Condições Múltiplas

Estabelece o resultado da expressão como TRUE() e avalia as expressões em busca daquela que possui o valor verdadeiro.

SWITCH(TRUE(),
    booleanexpression1, result1,
    booleanexpression2, result2,
    :
    :
    else
   )


Retorna um valor para cada expressão que é avaliada como TRUE()

SWITCH(TRUE(),
             AND([salario]>=0, [salario]<=3000), “Junior”,
             AND([salario]>=3001, [salario]<=5000), “Pleno”,
             AND([salario]>=5001, [salario]<=8000), “Senior I”,
             AND([salario]>=8001, [salario]<=10000), “Senior II”,         
             “Outros”
           )

Desta maneira, cada resultado pode ter uma expressão complexa, que represente adequadamente a regra de negócio estabelecida.

Apesar de ser possível chegar ao mesmo resultado usando IF, a função SWITCH é bem mais fácil de ser construída e lida, sendo assim menos suscetível a erros e mais simples de ser debugada.

Fonte: https://powerpivotpro.com/2012/06/dax-making-the-case-for-switch/

Carga Incremental Usando SQL Change Data Capture com SSIS - Integration Services

Neste post vou resumir o que é e como implementar uma estratégia de ETL utilizando o recurso CDC , nativo do SQL Server disponível nas versões superiores a 2008 do SQL Server.

A importação utilizando CDC permite criar uma carga de dados incremental que se adéqua bem a cenários onde o sistema de origem não consegue ou não possui uma forma específica para evidenciar quais registros precisam ser atualizados, normalmente em sistemas que manipulam fatos que podem ser alterados de forma retroativa em qualquer momento do seu ciclo de vida. Nestes casos, as cargas incrementais precisam necessariamente ser feitas analisando toda a base de origem para uma atualização consistente da base de destino.

O recurso CDC é uma alternativa para este cenário, onde o volume de dados impede uma carga full em um tempo razoável.

Change Data Capture um recurso nativo do SQL Server que monitora os inserts, updates e deletes salvos no arquivo de log do banco de dados.

Para implementar uma estratégia de importação incremental existem diversas abordagens podem ser usadas com seus prós em contras:

- incluir uma coluna de data de alteração para controlar na fonte o que deve ser incluído ou não. Isso não funciona bem para bancos legados que não podem ser alterados.

- criar tabelas espelhos e popular com triggers e depois usar estas tabelas para popular o destino

- fazer querys que comparam todos os dados da fonte com todos os dados de destino. As vezes muito oneroso

- Carga/Descarga Full: apagar tudo no destino e atualizar com tudo da origem. Para bancos maiores ou para sistemas que precisam de intervalos de atualização pequenos pode ser muito oneroso

Change Data Capture

Benefícios:
- baixo impacto: não precisa criar colunas adicionais, triggers e fazer querys longas
- Pouco overhead: o processo é assíncrono e não afeta o banco
- Granular: configurado sob demanda tabela por tabela
- SQL nativo


1-Habilitar Feature no Banco 

Ao habilitar, o sql cria um esquema "cdc" para armazenar as tabelas espelho.
As tabelas espelho são réplicas das tabelas monitoradas, com algumas colunas adicionais com metadados das operações realizadas.

Para o log de alteração ser monitorado corretamente, deve ser habilitado o serviço SQL Agent.

2-Habilitar CDC nas Tabelas

* capture_instance HumanResources_Employee' é uma tabela sombra/espelho que armazena as alterações

* essa tabela tem a mesma estrutura, mais alguns campos adicionais como tipo da operação realizada (insert, delete, update). Esses metadados serão usados pelos componentes do SSIS para tratar a importação

* o SQL busca os dados alterados do log do banco de forma assíncrona nos momentos de menor atividade do banco, isso faz com que o processo seja transparente para o banco.

Os componentes do SSIS fazem a leitura das tabelas sombra também chamadas de  "Capture_Instances"

Para consultar um tabela espelho

SELECT * FROM cdc.HumanResources_Employee_CT;

Entre os metadados, esta é a coluna que mapeia a operação DML realizada

__$operation column 

1 = delete, 

2 = insert, 

3 = update [valor anterior], 

4 = update [novo valor], 

and 5 = merge

As operações de update armazenam a versão atual e a nova versão do registro, são 2 linhas para cada alteração.

3-Alterando um valor da tabela monitorada

--Atualizações

UPDATE HumanResources.Employee

SET HireDate = DATEADD(day, 1, HireDate)

WHERE [BusinessEntityID] IN (1, 2, 3);

Usando CDC no SSIS

O SSIS utiliza os seguintes componentes:

CDC Control Task: para configurar o fluxo de dados marcando o início e o fim do processamento. Existem várias opções de configuração para este componente, o mais importante é entender qual tipo de operação utilizar e em qual ponto no fluxo de dados

- Mark Initial Load Start: informa que será feita a carga inicial full

- Mark Initial Load End: informa que a carga inicial full foi concluida

- Mark CDC Start: Marca o início

- Get Processing Range: marca o início de uma nova carga incremental, a partir da marca do fim da anterior

- Mark Processed Range: marca o fim do processamento incremental

Ao configurar o CDC Control, será criada uma tabela que controla o estado das mudanças

CDC Source: data flow task que se conecta às tabelas espelho para importar as alterações CDC

CDC Spliter: cria data paths para cada tipo de operação capturada (insert, delete, updade)

Carga Inicial

- criar a conexão,a tabela CDC state e a variável

Na primeira carga usar  Mark Initial Load Start e Mark Initial Load End

Cargas Incrementais

Após rodar a carga full apenas uma vez, a carga incremental entra em ação.

A primeira atividade é criar um etapa CDC Control com a opção  Get Processing Range para marcar o início da carga incremental. 

Na mesma tela são criadas a fonte de dados ADO, a variável do tipo string e uma tabela de states, tudo para apoiar o processo CDC.

Com a etapa anterior configurada, a etapa de extração pode irá ser feita. Inclua um data flow task, mas fontes de dados, inclua CDC Source para extrair da tabela sombra.

Na configuração da fonte, marcar a tabela que esta sendo monitorada e a variável.

Com a fonte configurada, conecte com um CDC Split para identificar os tipos de operações.

Com as saídas identificadas, basta enviar para o destino os registros novos, os atualizados podem ser usados em um comando UPDATE na base de destino e os registros do caminho DELETE podem opcionalmente ser usado para deletar no destino, ou atualizar alguma coluna de controle.

Definições Business Intelligence

Muito se fala de BI e nem sempre existe um alinhamento sobre que essa sigla realmente representa.

O termo BI (Business Intelligence) é muito usado para definir ferramentas de software de geração de relatórios, mas devemos ampliar esta visão e entender que se trata de um conceito. BI está relacionado a capacidade e habilidade que uma empresa tem de promover um processo de tomada de decisão baseado em informações confiáveis, disponíveis no momento certo e apresentadas no formato certo para as pessoas certas.

Não está simplesmente relacionado a tecnologias, também remete maneira que os processos de negócio obtêm as informações necessárias para suportar as decisões. Algumas pessoas podem pensar que para ter BI é preciso ter ERP estruturado, um CRM, ou vários sistemas implantados gerando dados operacionais. Isso não é premissa. De fato é preciso ter processos que capturam as informações certas para os objetivos definidos no plano estratégico, seja em sistemas de informação complexos, em planilhas ou qualquer tipo de formato.

Para visualizarmos um cenário de aplicação de BI vamos a um exemplo:

Uma aeronave em operação possui um objetivo único e bem definido: cumprir seu plano de voo e chegar ao seu destino em segurança.

Porém, para chegar ao seu destino o piloto precisa verificar constantemente os dados de controle da aeronave: altitude, localização, velocidade, temperatura, dados de meteorologia, etc.

Se algo sair do plano e variar mais do que o aceitável, o piloto deve fazer pequenas ou grades intervenções baseando-se nos dados e feedback atualizado do dashboard da aeronave.

As empresas e seus gestores, assim como as aeronaves e seus pilotos, possuem objetivos (destinos), planejamento estratégico (plano de voo) e controles (indicadores de performance) que as permitem olhar para o passado, perceber o presente e agir com base em informação para vislumbrar o futuro e tomar a decisão antes que seja tarde demais.

Estes dados  dão ao gestor a opção de fazer correções durante o curso, simplesmente observando a evolução dos indicadores que são fatores críticos do sucesso de um objetivo.

Felizmente as aeronaves tem mais sucessos nos seus objetivos do que as empresas, será que isso está relacionado com o fato das métricas e indicadores dos aviões serem mais precisas e confiáveis do que das empresas?

A medida que o tempo passa, as ferramentas de BI estão evoluindo cada vez mais para fornecer informações confiáveis, que possam ser entregues no momento certo e no formato certo. Mas felizmente não são apenas as ferramentas que evoluem, novas metodologias e melhores práticas sugiram nos últimos anos para auxiliar na definição dos objetivos de controles. Alguns exemplos são COBIT e BSC.

Tão importante como conseguir medir com consistência usando tecnologias de BI é saber o que medir. As métricas precisam estar focadas nos objetivos estratégicos para que BI realmente entregue valor para o negócio ao invés ter o efeito negativo de tirar o foco dos fatores críticos para o sucesso.

As métricas nascem basicamente da evolução da:

MISSÃO, que define uma...

VISÃO, que orienta o...

PLANO ESTRATÉGICO, que se desdobra em...

OBJETIVOS, que são medidos por...

INDICADORES, que são fontes da dados para tomadas de decisão.

 
Algumas perguntas que precisam ser respondidas pelos indicadores:

    O que aconteceu?

    Porque aconteceu?

    O que está acontecendo?

    O que poderá acontecer?

 
Porém a maior parte do problema ocorre quando é preciso buscar as respostas para estas questões. Os dados não estão disponíveis ou não estão sendo coletados corretamente pelos processos de negócio ou ainda estão disponíveis mas de forma descentralizada, em um formato bruto que precisa ser trabalhado. 

Uma importante premissa para o sucesso do uso de ferramentas de BI, está muito mais relacionado à definição de um plano estratégico claro e seus respectivos processos para coleta e tratamento das informações. Do que a escolha da melhor ferramenta de software de BI.


Então o que é BI?
 
BI compreende ferramentas que ajudam as empresas executarem o gerenciamento da performance dos indicadores e seus respectivos objetivos estratégicos.

Estas ferramentas ajudam no sentido de prover recursos para consolidação de dados de diferentes fontes, permitir que usuários tenham um meio de acesso rápido e preciso que permita uma análise avançada destes dados.


De fato, com uma estrutura consistente de BI acaba com aquele cenário onde cada departamento trabalha com a sua planilha e ao apresentá-las descobre-se várias versões da verdade, resultados conflitantes. Existe apenas uma versão da verdade!


Na visão da empresa líder do quadrante mágico do gartner, Microsoft, uma solução BI precisa de uma plataforma para consolidação de dados (ETL), uma ferramenta robusta e eficientes para criação de modelos de dados (cubos) ferramentas de analise voltadas para o usuário final e que sejam flexível a ponto de atender níveis de maturidade de diferentes das empresas. Algumas características de uma platarforma de BI são:

- Confiável e Flexível
- Plataforma integrada
- Ambiente familiar
- Capacidade de BI para todos, desafogando a TI mas sem perder o controle

O princípio é a escolha da ferramenta certa para cada nível de maturidade de cada departamento ou usuário.
 
O processo de BI é vivo, está sempre se adequando às mudanças de estratégias das empresas, por isso, a implantação precisa ser rápida ao mesmo tempo robusta para estar pronta para o crescente volume de dados.

Role-Play Dimensions em Modelos Tabulares

Em um modelo de dados, as dimensões dão significado às perspectivas pelas quais os dados serão filtrados e analisados, além disso elas facilitam a leitura e simplificam os modelos trazendo semântica e organização. Normalmente as dimensões são estruturas de dados que possuem um relacionamento de 1 para N com as tabelas de fatos, e se o modelo possui mais de uma tabela de fatos a mesma dimensão pode ser utilizada para ambas, criando assim uma visão compartilhada entre processos diferentes (representados pelas suas tabelas de fatos).

Alguns bons exemplos são dimensões de "Clientes" e "Datas": o cliente poderia estar relacionado com a tabela de fatos do processo de vendas e a tabela de fatos do processo de atendimentos de um SAC, e a dimensão de datas poderia estar relacionada com a data da venda e com a data do atendimento no SAC.

Dimensões que se relacionam com múltiplas tabelas de fatos são relativamente comuns em data warehouses maiores. Porém, existem situações nas quais uma dimensão têm mais de um relacionamento com a mesma tabela de fatos, como por exemplo as dimensões de datas. Uma tabela de vendas pode ter um fato com uma coluna para a data da venda, data do envio, data da entrega, etc. Um processo de atendimento no SAC pode ter data da ligação, data da solução do atendimento, data da resposta ao cliente. Em geral, são tabelas que possuem colunas que armazenam datas para cada fase de um processo.

Essas dimensões são chamadas de "Role-playing Dimensons". Vamos ver como implementar em modelos tabulares no analysis services.

O primeiro passo é garantir que exista um relacionamento N:1 para cada coluna de data na tabela de fatos.

DimDate => OrderDate (ativa)
DimDate => DueDate (inativa)
DimDate => ShipDate (inativa)

Uma questão importante é que os modelos tabulares permitem que apenas um relacionamento esteja ativo por vez, isso garante que as funções DAX funcionem sem ambiguidade sem que seja necessário definir o relacionamento padrão.

Sempre que a tabela de fatos for filtrada pela dimensão de datas as medidas irão aplicar os filtros na coluna de data que estiver com o relacionamento ativo, no exemplo abaixo, não é necessário instruir a função DAX em qual relacionamento se propagar para filtrar as vendas por data, pois OrderDate está com relacionamento ativo.

SalesByOrderDate := SUM ( FactInternetSales[SalesAmount] )


Para fazer com que uma medida considere os relacionamentos inativos temos que explicitar isso utilizando a função USERELANTIONSHIP 


SalesByDueDate :=
CALCULATE (
    SUM ( FactInternetSales[SalesAmount] ),
    USERELATIONSHIP (
        FactInternetSales[DueDateKey],
        DimDate[DateKey]
    )
)


SalesByShipDate :=
CALCULATE (
    SUM ( FactInternetSales[SalesAmount] ),
    USERELATIONSHIP (
        FactInternetSales[ShipDateKey],
        DimDate[DateKey]
    )
)


A função USERELATIONSHIP recebe como argumento as chaves envolvidas no relacionamento. Com isso as medidas irão filtrar corretamente o contexto de datas sem que o filtro de uma data afete o filtro das outras.

Usando relacionamentos inativos em colunas calculadas:

Nos exemplos acima, estou descrevendo como ativar relacionamentos em medidas que usam o contexto de coluna, nos casos em que o contexto é de linha, como em colunas calculadas que usam RELATED para buscar um valor a partir dos relacionamentos, a implementação é diferente.

Não podemos usar CALCULATE em funções de contexto de linha pois o DAX altera automaticamente para contexto de coluna, temos que usar LOOKUP.

A função LOOKUP faz uma relação entre as chaves de data sem que exista uma relação fisicamente criada. É uma espécie de relacionamento virtual.

* onde [DueDateKey]  foi igual a [DateKey] retorna o nome do dia da semana.

FactInternetSales[DayDue] =
LOOKUPVALUE (
    DimDate[EnglishDayNameOfWeek],
    DimDate[DateKey],
    FactInternetSales[DueDateKey]
)


Desta forma nós contornamos a necessidade de usar a função CALCULATE para ativar uma relação inativa e fazer o uso correto da função RELATED.

Fontes:
https://www.sqlbi.com/articles/userelationship-in-calculated-columns/
https://www.youtube.com/watch?v=2BxaUXlx3K4

Particionando Tabelas em SQL Server

O recurso de particionamento de tabelas do SQL Server pode ser muito útil durante o projeto de um data warehouse, pois permite otimizar a alocação de disco através da segmentação grandes tabelas de Fato em grupos de arquivos fisicamente isolados. Com esta funcionalidade, os discos podem ser otimizados para consulta massiva e esquemas de RAID que beneficiem a leitura podem ser usados em partes da tabela que são consultadas com mais frequência.

Em uma tabela de Fatos, sempre existe uma ou mais colunas que marcam a ocorrência de um evento, um atributo de data pode ser usado para o particionamento.

Por exemplo:
- todas as linas com data anterior a 2012 estão alocadas na primeira partição
- todas as linas com data anterior a 2013 estão alocadas na segunda partição
- todas as linas com data anterior a 2014 estão alocadas na terceira partição
- todas as linas com data 2015 ou superior, estão alocadas na quarta partição














* Se a coluna tiver algum valor nulo, este estará alocado na primeira partição.

Função de Particionamento

Para configurar o particionamento no SQL Server, o primeiro passo depois de definir a tabela e coluna que será usada como referência, será a criação da função de particionamento. Ela serve basicamente para definir os limite de cada partição.

-- Cria uma função de particionamento para ser usada em coluna do tipo DATE usando a opção RIGHT
CREATE PARTITION FUNCTION pfSales (DATE)
AS RANGE RIGHT FOR VALUES
('2013-01-01', '2014-01-01', '2015-01-01');

Range LEFT ou RIGHT

O exemplo acima usa a opção RANGE RIGHT para informar a função que deve ser considerada como parte da partição o valor informado e todos os valores maiores do que ele, na opção LEFT, seria o inverso.

Na imagem fica mais claro, na sequência como seria a partição com LEFT e RIGHT

Se fossemos usar a opção LEFT seria:

-- Cria uma função de particionamento para ser usada em coluna do tipo DATE usando a opção LEFT
CREATE PARTITION FUNCTION pfSales (DATE)
AS RANGE LEFT FOR VALUES
('2012-12-31', '2014-31-12', '2015-31-12');

Esquema de Partição

Com a função de particionamento criada, agora é possível definir o esquema que irá mapear os ranges às partições que serão alocadas aos filegroups (previamente criados e associados a arquivos de dados).

-- Cria o esquema que utiliza os limites definidos na função de particionamento e mapeia os filegroups para cada RANGE criado

CREATE PARTITION SCHEME myRangePS
    AS PARTITION pfSales
    TO (test1fg, test2fg, test3fg, test4fg) ;

Tabela Particionada

Com a função e o esquema criados, agora é hora de criar a tabela e apontar para o esquema configurado anteriormente.

-- Cria uma tabela particionada e utiliza a coluna col1 para segmentar de acordo com o esquema 
CREATE TABLE PartitionTable (id int PRIMARY KEY, col1 date))
    ON myRangePS (col1) ;
GO

Em resumo o processo consiste em criar os filegroups, definir a função, o esquema e criar a tabela utilizando o esquema.

Com isso os dados serão alocados em arquivos de dados específicos de acordo com os valores da col1.

Grupos de arquivos e arquivos usados pela partição.

https://www.cathrinewilhelmsen.net/2015/04/12/table-partitioning-in-sql-server/
https://docs.microsoft.com/pt-br/sql/relational-databases/partitions/create-partitioned-tables-and- indexes?view=sql-server-2017

Monitorando Acessos ao Portal do Power BI Report Server

O Power BI Report Server é uma alternativa muito interessante para empresas que optam por permanecer on-premise, com seus dados locais, sem ter a necessidade de adquirir planos de acesso ao serviço na nuvem.

Hoje a maioria das principais funcionalidades do Power BI da nuvem está disponível na versão local, atendendo aos mais variados requisitos de negócios, sendo assim uma alternativa bastante viável.

O PB Report Server herdou toda a estrutura do antigo report server que hoje continua funcionando como um portal para ambos os tipos de relatórios. Além disso, ele é integrado com o AD e pode ser amplamente divulgado e acessado pela empresa, sem limite de usuários.

A medida que o número de usuários aumenta é importante realizar o monitoramento e para isso o report server mantém os dados de acesso registrados na base de dados do próprio gerenciador. Nesse artigo vamos destacar as principais consultas que podemos realizar nestas tabelas.

A tabela ExecutionLogStorage concentra todo o log de acesso, que por padrão é configurado para armazenar os últimos 60 dias. Se você quer armazenar o log completo, informe -1. Todos os dias, às 2:00 o processo de remoção é acionado.

Para alterar essa configuração, acesse as propriedades do serviço e informe o parâmetro ExecutionLogDaysKept.

Basicamente, para monitorar o log, acesse o banco de dados criado no processo de instalação do PB Report Server e utilize as tabelas:

Users: nome dos usuários de AD que possuem acessos configurados;

Catalog: catalog com os metadados dos objetos que podem ser desde pastas, imagens, relatórios paginados e paineis feitos com o power bi;

ExecutionLogStorage: Tabela com o log de acesso aos objetos do catalog


--CONSULTA

SELECT
       A.LogEntryId, --Chave da tabela de log
       A.UserName, --Usuário que acessou o relatório  
       B.path, --Caminho na estrutura de pastas
       B.Name, --Nome do relatório
       A.TimeStart, --Momento que a execução foi realizada
       A.TimeEnd,
       A.TimeProcessing,
      A.Status, --Resultado da execução Erro/Sucesso   
      A.RequestType,
      TipoRequisicao =
              CASE A.RequestType
WHEN 0 THEN 'Interactive' --Representa um acesso manual
WHEN 1 THEN  'Subscription' --Acesso agendado
WHEN 2 THEN 'Refresh Cache' --Acesso agendado
ELSE '-'
       END,
       TipoCatalogo =
   CASE [Type]
WHEN 1 THEN 'Pasta'
WHEN 2 THEN 'Relatório Paginado'
WHEN 3 THEN 'Outros'
WHEN 4 THEN 'Relatório Vinculado'
WHEN 5 THEN 'Fonte de Dados'
WHEN 6 THEN 'Modelo de Relatório'
WHEN 7 THEN 'Parte do Relatório'
WHEN 8 THEN 'Conjunto de Dados Compartilhado'
WHEN 13 THEN 'Power BI' --Novo tipo, adicionado na versão com power bi
ELSE '-'
END

FROM [dbo].[ExecutionLogStorage] A
LEFT JOIN  [dbo].[Catalog] B on A.ReportID = B.ItemID

Com a consulta acima podemos criar diversos tipos de relatórios, monitorar a quantidade de acessos, os acessos mais recentes, os relatórios mais populares entre outras métricas.

Uma dica importante é ter cuidado ao deletar relatórios. Quando a operação é realizada, os registros de log ficam órfãos, quando for subir uma nova versão, sempre substitua o relatório atual, fazendo isso o log não é perdido.


https://blogs.msdn.microsoft.com/shiyangqiu/2016/09/12/reporting-service-execution-log-retention/

Carga Incremental Usando Tabela de Controle

Este padrão utiliza uma tabela de controle com a data da última carga para realizar a carga incremental.

CREATE TABLE dbo.ControlTable
(
SourceTable varchar(50) NOT NULL,
LastLoadID int NOT NULL,
LastLoadDate datetime NOT NULL,
RowsInserted int NOT NULL
)  ON [PRIMARY]
GO


A coluna LastLoadDate é usada como referência nas querys, para que as consultas sejam feitas apenas sob dados com data superior a data do último processamento.

A tabela matém um registro para cada tabela envolvida no processo.

O processo consiste em 3 etapas:

1-Buscar a última data de processamento da tabela
2-Extrair os dados com base na data de referência
3-Atualizar a tabela de controle

Ler a data da última atualização

A tabela de controle armazena uma linha para cada tabela envolvida na carga, a data da última atualização e o número de linhas importadas.

A primeira etapa faz a leitura desta tabela e armazena em uma variável que será usada para filtrar a extração.

A configuração do ResultSet precisa ser "Single Row"

Após configurar a query para buscar a última data de atualização, é necessário mapear o resultado em uma variável.

Extrair dados incrementais 

A segunda etapa consiste em configurar a fonte de dados para buscar os dados desejados a partir da data da última carga.

A consulta é parametrizada com ? para indicar que naquela parte da query serão usados paremetros

O primeiro parâmetro é a variável populada na etapa anterior.

O segundo é a data do processamento do pacote. Ou seja, o processo irá extrair tudo a partir da data da última carga até o momento que o pacote é executado.

Atualizando a tabela de controle

Após a nova carga, é necessário alterar a data a última atualização na tabela de controle para que na próxima carga os dados sejam atualizados a partir do ponto que o processo foi realizado sucesso na última vez.

Atualizando com base na data de execução do pacote

Mapear o parametro da atualização com base na data do processamento do pacote

Fonte: 

Professional SQL Server 2014 Integraton Services

Power BI - Detectando Clusters em Modelos de Dados

Clusters em modelos de dados no Power BI são basicamente agrupamentos que servem para segmentar uma dimensão com base em um algoritmo de agrupamento que difere de outros algoritmos de mineração de dados, como o algoritmo Árvores de Decisão da Microsoft, em que você não precisa designar uma coluna previsível para poder criar um modelo de agrupamento.

O algoritmo de clustering treina o modelo estritamente a partir dos relacionamentos existentes nos dados e dos clusters que o algoritmo identifica.

O algoritmo Microsoft Clustering primeiro identifica os relacionamentos em um conjunto de dados e gera uma série de clusters baseados nesses relacionamentos.

Disponível no mapa de dispersão (scatter chart)

Uma vez criado o cluster, os pontos receberão um cor específica serão adicionada à lista de campos um novo item chamado "cluster" e esse item será adicionado também à legenda.

No nosso exemplo o algorítimo foi configurado para localizar 5 clusters de produtos

Para aprimorar a análise, os clusters podem ser renomeados por exemplo:

Editar "Cluster". Você pode dar nomes amigáveis e controlar a quantidade




Também podemos usar linhas de tendência para criar quadrantes (tipo quadrante mágico do gartner)

Links:
https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-november-feature-summary/

https://docs.microsoft.com/en-us/sql/analysis-services/data-mining/microsoft-clustering-algorithm?view=sql- analysis-services-2017