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

Power BI Desktop Update [até Abril de 2019]

Nesta release foi removida em definitivo a área do antigo espaço dos filtros onde havia certa redundância nas funcionalidades. Agora todos os recursos estão na aba exclusiva para filtros e o recurso está bem lapidado.

Antes os filtros do painel também apareciam junto com área de configuração dos campos.

Agora os filtros estão todos em um único local com recursos completos de formatação e controles para habilitar, desabilitar, bloquear, renomear, etc...

Especificamente nesta versão foram incluídas as opções de alterar o título dos filtros para garantir a criação de nomes mais amigáveis para o usuário.

Agora também temos a opção de bloquear a alteração do tipo de filtro entre basic e advenced mode.

Esta opção é configurada para o relatório todo e deve ser alterada nas opções gerais do relatório.

Uso de expressões em títulos de gráficos, botões, url, imagens.

Até então os títulos atribuídos aos elementos gráficos e às urls associadas a botões eram estáticos e não eram afetados pelo contexto do painel . Agora eles podem ser dinâmicos baseados em medidas DAX que afetam o contexto de filtro.

Essa novidade cria uma grande expectativa, a possibilidade de ampliar significativamente as opções de customização de propriedades. Isso é muito útil e já existe no antigo report server e agora está sendo incorporada ao power bi aos poucos.

Exemplo de medida usada em um título de gráfico.

O título do gráfico apresenta o texto com a classe selecionada.

Para acessar as propriedades e atribuir a nova medida:

Após a configuração, o campo da propriedade irá apresentar um ícone no lugar do valor, indicando que a propriedade é dinâmica.

Essa customização vale para as URLs. Agora  ações, botões, imagens, formas também permitem usar medidas para dinamizar o valor da propriedade para criar urls diferentes dependendo do contexto do painel.

Também foi disponibilizada a opção de fazer drillthrough entre diferentes relatórios, até então estava restrito a paginas do mesmo relatório, agora o conceito de drill foi ampliado para fora.

Key Influencers visual agora também suporta campos com valores contínuos como alvos para análise

Essas são apenas algumas das principais novidades desta versão.

Vídeo:

Power BI Desktop Update [até março de 2019]

Neste mês, algumas funcionalidades apontadas como importantes pelos usuários no Power BI Ideas  foram disponibilizadas na release. Entre elas estão: filtros únicos em slicers, novo formato para mapas, customização dos balões de tooltips, nova tela de modelagem de dados, entre outros. Então vamos a elas.

Single-Select

Essa configuração garante que apenas um valor seja selecionado em um filtro. Até a versão anterior isso não era possível, com ctrl, o usuário conseguia fazer seleção múltipla, o que pode ser um problema em alguns casos onde medidas DAX utilizam o resultado de seleções únicas em filtros.

Outro benefício de se usar essa configuração nos filtros, surge quando o usuário utiliza o formato dropdown. Até então ao selecionar um item, a lista permanecia aberta e o usuário tinha que obrigatoriamente fecha-lá apos seleção. Agora com esta versão, ao clicar no item a lista é recolhida automaticamente.

Modelo de mapa de Calor

Com esta versão de mapa o resultado final para análises que apresentam muitos pontos passa a ser visualmente mais agradável, destacando melhor, onde realmente existem valores maiores, poluindo bem menos a tela com excesso de pontos. Veja.

Configuração padrão.

Com Heap Map.

Filtar a partir das categorias dos gráficos

Em muitos casos, selecionar uma categoria em um gráfico de barras pode ser difícil, pois o tamanho proporcional pode ser muito pequeno para o clique. A partir desta versão, o item pode ser destacado pelo próprio label da categoria.

A falta de um recurso como este também fazia com que não fosse possível filtrar todas as medidas envolvidas em uma categoria de um gráfico ao mesmo tempo, apenas uma medida de cada vez poderia ser selecionada, com essa atualização, toda a categoria, com todas as medidas podem ser selecionadas ao mesmo tempo em um clique.

Formatação do tooltip padrão

O tooltip padrão com fundo preto e fonte branca agora pode ser alterado para ser suavizado ou combinado com as cores do painel.

Link para URL externa:

Esse recurso permite que seja incluído botões, formas ou imagens com ações que direcionam o usuário para uma URL externa. Isso pode ser útil para direcionar para páginas web com manuais, para fontes ou artigos que complementam a análise apresentada no painel.

Tela de Modelagem de Dados

Nova tela para modelagem traz a opção de configurar as propriedades das tabelas e atributos na mesma pagina, até então isso era feito alternando com a tela Data.

Além disso, essa nova tela traz a possibilidade de criar visualizações parciais do modelo, com conjuntos de tabelas específicas simplificando a visualização de grandes modelos de dados.

A experiência de modelagem dados do power bi vem se aprimorando e se aproximando do Data Tools, onde os modelos do Analysis Services Tabular são configurados.

Organizar em pastas: Uma das novas propriedades chamada "Display Folder" ajuda organizar os atributos do modelo em pastas.

Novas funções DAX

Buscar uma string dentro de outra: https://dax.guide/containsstring/ | https://dax.guide/containsstringexact/
Retornar um valor com base em chaves equivalentes: https://dax.guide/lookupvalue/
Conta itens diferentes mas não considera black como item valido: https://dax.guide/distinctcountnoblank/

Custom Visual

Power Slicer: Um custom visual interessante  que permite maior controle sob as customizações visuais de um slice. Além disso possui recurso de hierarquia de atributos e configuração valores padrao utilizando JavaScritp.

Exemplo: marcar o mês com base na data atual.

Vídeo:

Fontes:
https://github.com/Microsoft/powerbi-desktop-samples/tree/master/2019
https://powerbi.microsoft.com/pt-br/blog/power-bi-desktop-march-2019-feature-summary/