Sincronizando Slicers no Power BI

Slicers são componentes visuais que aplicam filtros por padrão em todos os visuais da página, mas podem ser configurados de forma customizada para afetar partes específicas do painel. São usados quando estamos em busca de um destaque maior para o filtro ou para fazer com que apenas parte dos gráficos sejam impactadas (uma vez que o PB possui uma área de filtros padrão). Além disso, ainda é possível controlar a sincronização entre eles, trazendo maior flexibilidade e atendendo cenários onde as análises estão distribuídas em várias páginas.

Antes que este recurso fosse lançado, ele se transformou em uma das ideias mais requisitadas pelos usuários, mostrando a importância e os vários cenários que ela pode ser aplicada.

O recurso funciona basicamente assim:

Todos os slicers configurados com o mesmo tipo de dado ou atributo do modelo, podem propagar os filtros marcados pelo usuário de um para o outro. Por exemplo: ao alterar o filtro "data" da página 1, outro filtro que usa a mesma coluna "data" na pagina 2 é atualizado com o mesmo valor.

Você pode utilizar por exemplo em uma página inicial que resume alguns indicadores e aplica alguns filtros. Estes filtros uma vez aplicados na pagina inicial, criam o contexto para o usuário navegar nas demais páginas já com os filtros definidos. Isso faria com que a experiência de navegação entre estas páginas ficasse mais fluida e consistente.

Para criar um slice sincronizado:

Acessar a aba "View" e habilitar a janela de sincronização.

Todas as configurações são feitas a partir desta tela.

Neste exemplo temos duas datas diferentes DueDate e OrderDate no mesmo grupo de sincronização "GrupoSincroniaFullDate", isso faz com que fiquem sincronizados na mesma página.

Outro cenário seria sincronizar em páginas diferentes, para isso marque o checkbox da página que o filtro será inserido. Verifique que automaticamente ele foi inserido e o grupo de sincronização configurado.

Com isso, ao alterar o filtro na pagina "Sync Filters" todos os filtros do mesmo grupo de sincronização tanto da página atual quanto das demais páginas serão sincronizados.

Fonte:
https://powerpivotpro.com/2018/02/connect-one-slicer-power-bi-report-pages/
https://prathy.com/2019/02/sync-slicers-sync-slicers-advanced-options/

Análise de Performance e Monitoramento em Modelos Tabular - SSAS e Power BI

Para fazer uma análise mais detalha da performance de modelos Tabular é importante entender o método de armazenamento que essa tecnologia utiliza.

A maioria dos bancos de dados relacionais armazenam fisicamente os dados linha por linha e organiza em páginas de tamanhos específicos (SQL Server 8K), fazendo com que cada página guarde um subconjunto da tabela. Em geral, esse método requer que todas as páginas sejam lidas quando uma agregação é feita utilizando apenas uma coluna. Exemplo, para saber o total de posts todos os arquivos de dados organizados em páginas precisam ser carregadas para obter o resultado.

Já com o método column-orientend que é utilizado pelo SSAS e Power BI, as páginas armazenam as colunas separadamente trazendo maior performance nas agregações, pois para obter um resultado da agregação de uma coluna menos páginas precisam ser lidas. A desvantagem é que essa arquitetura requer maior esforço computacional em casos onde várias colunas estão envolvidas. De fato este método é muito mais eficiente para sistemas analíticos.

Column-oriented

Agora vamos falar especificamente sobre os modelos Tabular do SSAS e Power BI. Eles utilizam a estrutura de dados xVelocity in-memory que implementa o método column-store descrito acima.

Esta arquitetura mantém os dados em colunas altamente compactadas, armazenadas separadamente em dicionários de dados e uma lista de índices que representa dos valores da coluna na sequencia que são armazenados na tabela. São estas estruturas que são carregadas para a memória RAM do servidor quando o modelo é processado.

Por exemplo: a tabela de produtos possui a coluna category que possui apenas dois valores distintos. Neste caso o dicionário irá manter apenas os dados "Accessories, Bikes" e irá criar um índice que representa a sequencia que estes elementos aparecem na tabela de acordo com a posição de cada elemento no dicionário. 

Como podemos ver, o dicionário pode ser a parte que irá demandar mais recursos de armazenamento. Essa arquitetura traz algumas questões importantes que devem ser consideradas durante a construção do modelo:

- Estimar o tamanho do banco de dados column-store não é tão simples quanto em modelos row-oriented (tamanho do registro * numero de linhas) que com apenas alguns exemplos e histórico de uso é possível estimar com precisão.

- O tamanho das tabelas em modelos Tabular podem ser determinados pelos seguintes fatores em ordem de importância:

• Número de colunas
• Tamanho do dicionário (número de itens distintos)
• Tipo de dado das colunas
• Número de linhas

Analisando Performance e Utilização de Memória

Os principais fatores que influenciam a pressão por mais memória são, o próprio modelo, execução de medidas complexas e usuários concorrentes. Veremos como monitorar e fazer alguns ajustes nestes itens.

Modelo

O que podemos fazer para reduzir no tamanho dos dicionários?

A primeira coisa que temos que ter em mente é: Os modelos Tabulares devem ter apenas as colunas que serão usadas para análise ou criação de relacionamentos. 

Temos que identificar as colunas com maior cardinalidade e se possível remover ou segmentar.

Como não podemos remover as colunas usadas nos relacionamentos do modelo, como por exemplos os códigos das dimensões em um modelo estrela, importe apenas as chaves que serão usadas para concretizar relacionamentos. As chaves primárias das tabelas fatos criam dicionários com a quantidade de elementos do tamanho da tabela e podem aumentar muito o tamanho do dicionário.

Em tabelas de fatos não é necessário carregar uma chave primária ou substituta, o modelo cria implicitamente uma coluna "RowNumber" para identificar o registro. Por exemplo, uma medida COUNT(<chave>) pode ser substituída por COUNTROWS(). Isso pode salvar muito espaço em memória.

Não utilize colunas com datetime com horas. Isso irá gerar um dicionário muito grande, formada pela maioria de elementos distintos. Se realmente for necessário, faça a separação do dia e hora em colunas diferentes. A cardinalidade irá diminuir para 365 dias por ano e 24 * 60 na coluna de horas e se precisar calcular a diferença entre duas datas, faça antecipadamente no momento da carga incluindo em uma view por exemplo.

Caso a tabela de fatos tenha algum código da transação que implemente alguma regra ou taxonomia que possa ser pré-determinada, faça a separação em colunas diferentes. Exemplo: os primeiros 3 caracteres de um código da transação representa a categoria de um produto. Identifique e separe esta coluna, isso irá diminuir a cardinalidade.

Identificar se existe alguma coluna que pode ser obtida através da criação de uma medida. Por exemplo: uma tabela de fatos de vendas que tenha a coluna de quantidade vendida e o valor total da venda. Neste caso, a coluna valor total da venda pode ser removida e uma medida DAX pode ser criada para substitui-la.

SUMX ('Internet Sales'; RELATED( Product['Unit Price']) * 'Internet Sales'[Order Quantity])

Analisando o Modelos

A análise dos modelos pode ser feita tanto de cima para baixo, analisando as querys executadas pelos painéis e identificando as mais lentas, quanto de baixo para cima, analisando os metadados do modelo através das DMVs da SSAS que apresentam o consumo de memória e o tamanho das dicionários.

De cima para baixo:

Ative a aba "Performance Analyzer" inicie a gravação e copie a query do visual que está com pior performance.

Abra o programa DAX Studio e se conecte na instância do modelo. 

Ative o botão "Server Timings" e execute a query.

Serão apresentadas as consultas realizadas no modelo, os tempos, a quantidade de linhas, espaço em memória. Estes dados servem de referência para identificar os atributos envolvidos e as medidas que estão afetando a performance. 

De baixo para cima.

Outra abordagem a consulta dos metadados do modelo para identificar o tamanho dos dicionários e espaço em memória ocupados.

Identificando o tamanho do dicionário:

Identificando o espaço ocupado em memória por cada atributo. Perceba que a maioria dos atributos com maior volume de espaço em memória são as datas e as chaves.

A coluna OBJECT_MEMORY_NONSHRINKABLE está em bytes, para converter para KB divida por 1024.

Identificando as sessões de usuário conectadas e o tempo de cpu

Depois desta analise é possível ter uma boa ideia sobre como está o modelo e onde é possível intervir para otimizar o uso dos recursos. 

Com as DMVs podemos ainda construir um painel no próprio power bi para monitoramento destas medidas listando os bancos, as dimensões/atributos com a quantidade de memória e o tamanho dos dicionários.

Fonte: Microsoft Analysis Services 2012 - Understanding xVelocity and DirectQuery

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/