Carga de tabela fato usando o comando T-SQL MERGE

A ideia deste post é demonstrar como fazer uma carga de tabela fato onde seja necessário inserir, atualizar e deletar linhas. Ao invés de realizar os comandos INSERT, UPDATE e DELETE separados podemos através do comando MERGE realizar as 3 operações na mesma query!

No nosso exemplo temos um data warehouse com as seguintes características:

• Recebemos uma vez por mês do sistema de origem os dados consolidados dos últimos 3 meses;
• As novas linhas na origem precisam ser incluídas na nossa base;
• As linhas que tiveram valores modificados precisam ser atualizadas;
• As linhas que forem excluídas da origem também precisam ser removidas da nossa base, porém somente dentro do período que foi enviado.

Abaixo temos a imagem dos dados atuais nossa tabela FATO_VENDAS:

E a seguir os novos dados que recebemos do sistema de origem:

Analisando as diferenças entre as tabelas podemos perceber que tivemos 2 linhas novas, 1 linha atualizada e 2 linhas excluídas, conforme o desenho abaixo:

Para solucionar esta carga de acordo com os requisitos iremos utilizar o comando MERGE da seguinte forma:

/*Criando parâmetros para somente atualizar
linhas dentro do intervalo que veio da origem 
(manter histórico que não sofre alterações fixo)
*/
 
 DECLARE @dt_inicio INT = 
(SELECT MIN(id_data) FROM #tmp_fato_vendas) 
 DECLARE @dt_termino INT = 
(SELECT MAX(id_data) FROM #tmp_fato_vendas)
 
 --Comando MERGE para carga da tabela FATO

MERGE INTO #fato_vendas AS f_TARGET 
USING #tmp_fato_vendas AS f_SOURCE 
ON f_TARGET.id_produto = f_SOURCE.id_produto
AND f_TARGET.id_cliente = f_SOURCE.id_cliente
AND f_TARGET.id_data = f_SOURCE.id_data 
WHEN MATCHED
AND f_TARGET.unidades <> f_SOURCE.unidades 
OR f_TARGET.valor <> f_SOURCE.valor THEN
 --linhas existentes que tiveram modificação
UPDATE
SET f_TARGET.unidades = f_SOURCE.unidades,
    f_TARGET.valor = f_SOURCE.valor,
    f_TARGET.inativo = 0,
    f_TARGET.dt_modificacao = GETDATE() 
WHEN NOT MATCHED BY TARGET THEN 
-- linhas novas
INSERT (id_produto,
        id_cliente,
        id_data,
        unidades,
        valor,
        inativo,
        dt_modificacao)
VALUES
		(f_SOURCE.id_produto,
		f_SOURCE.id_cliente,
		f_SOURCE.id_data,
		f_SOURCE.unidades,
		f_SOURCE.valor,
		0,
		GETDATE())
		
WHEN NOT MATCHED BY SOURCE
AND f_TARGET.id_data BETWEEN @dt_inicio AND @dt_termino THEN 
--linhas existentes que foram apagadas da origem

UPDATE
SET f_TARGET.inativo = 1,
    f_TARGET.dt_modificacao = GETDATE();

Feito o comando MERGE, o resultado final da tabela FATO_VENDAS deverá ficar conforme abaixo:

Observe as duas linhas excluídas ficaram com a coluna [inativo] = 1 (deleção lógica) e que as linhas inseridas/atualizadas tiveram um novo valor para a coluna [dt_modificacao].

O script completo para reproduzir com os mesmos dados do exemplo está disponível no Github.

Referências:

MSSQL Tips – Using MERGE in SQL Server to insert, update and delete at the same time

SQL Server Tutorial .NET – SQL Server MERGE

Microsoft Learn Questions – Merge WHEN NOT MATCHED BY SOURCE

Monitorando a utilização dos relatórios pelo log do Reporting Services

É comum encontrarmos nas empresas uma infinidade de relatórios. Porém, você já parou para pensar quantos realmente são utilizados? E quais demoram mais tempo para carregar, ou seja, tem uma performance pior?

No Reporting Services, ferramenta Microsoft de construção de relatórios, é possível ter acesso a este tipo de informação que pode ser bastante útil para entendermos quais são os relatórios mais importantes e quais são aqueles que podem ser excluídos ou modificados. Para isso, vamos utilizar as informações do log de execução que ficam registradas em algumas tabelas do banco de dados do Reporting Services. A ideia deste post é mostrar basicamente como criar um relatório de monitoramento de utilização, semelhante ao da imagem abaixo:

Banco de dados do Reporting Services

Até a versão do SQL Server 2016 (última lançada quando este post foi escrito), o Reporting Services possui duas opções de instalação: modo nativo e modo integrado ao Sharepoint. Para ambas as opções, após a instalação/ configuração, o Reporting Services gera um banco de dados (que pode ter algumas variações, mas normalmente é chamado de “ReportServer” ou “ReportingService”). É este banco de dados que armazena os logs de uso dos objetos do Reporting Services em geral (data sources, datasets e relatórios).

Por padrão, este banco de dados armazena um histórico de 60 dias de dados de utilização nas suas tabelas. Porém, esta propriedade pode ser alterada através da instância do Reporting Services, conforme descrito neste link.

Consultas SQL para retornar as informações

Para facilitar o acesso aos dados, o banco de dados do Reporting Services possui algumas views que já retornam um conjunto de informações relevantes. A Microsoft recomenda a utilização da view ExecutionLog3, que é a mais recente e possui mais campos. Porém, por questões de compatibilidade, ainda estão disponíveis as views ExecutionLog2 e ExecutionLog, que possuem dados bem semelhantes.

No nosso exemplo, como queremos retornar informações específicas dos relatórios e a view ExecutionLog possui o campo ReportID, vamos utilizá-la para simplificar a consulta.

Abaixo geramos duas consultas que retornam os dados que precisamos para gerar o nosso relatório:

• Query 1: quantidade de execuções de cada relatório

SELECT
  c.Name,
  c.[Path],
  COUNT(*) AS TimesRun
FROM [dbo].[ExecutionLog] AS l
INNER JOIN [dbo].[Catalog] AS c
  ON l.ReportID = C.ItemID
WHERE c.Type = 2
GROUP BY l.ReportId,
         c.Name,
         c.[Path]
ORDER BY TimesRun DESC

• Query 2: tempo médio e data da última execução de cada relatório

SELECT
  ReportID,
  C.Name,
  CAST(AVG(
  (TimeDataRetrieval + TimeProcessing + TimeRendering) / 1000.0)
  AS decimal(10, 2)) AS TotalRenderingTime,
  CAST(MAX(l.TimeStart) AS date) AS [LastRun]
FROM dbo.ExecutionLog AS l
INNER JOIN [dbo].[Catalog] AS c
  ON l.ReportID = C.ItemID
WHERE c.Type = 2
GROUP BY l.ReportId,
         C.Name
ORDER BY TotalRenderingTime DESC

 

Criação do relatório

Para gerar o nosso relatório, iremos utilizar o SQL Data Tools, ferramenta que é um template do Visual Studio para a criação de projetos de banco de dados e BI (Integration Services, Analysis Services e Reporting Services). Vamos selecionar um novo projeto do tipo “Report Server Project” (imagem abaixo):

Em seguida vamos adicionar os seguintes objetos ao nosso projeto:

Data source: conexão com o banco de dados do Reporting Services (SSRS_db.rds);

Datasets: criaremos dois datasets (“DataSet_ExecutionTime.rsd” e “DataSet_TimesRun.rsd”) que terão as consultas “Query 1” e “Query 2”;

Relatório: criaremos um relatório chamado “Report Usage.rdl”, e adicionaremos os dois “shared datasets” ao nosso relatório. Assim, poderemos gerar os seguintes objetos:

• Gráfico de barras contabilizando a quantidade de execuções de cada relatório;
• Tabela com tempo médio de renderização de cada relatório e a última vez que ele foi executado.

A imagem abaixo mostra como ficou o projeto após a criação destes objetos:

 

Por fim, vamos realizar o deploy do nosso projeto para o servidor de relatórios (imagem a seguir), definindo nas propriedades do projeto as URL’s do site do Reporting Services ou do Sharepoint onde ficarão armazenados os objetos do projeto. Assim conseguiremos acompanhar em tempo real (acessando o site onde se encontra o relatório) a frequência de uso dos relatórios e a duração média da cada execução.

A grande vantagem dessa solução é a possibilidade de criar outras formas de visualização personalizadas, modificando as queries para utilizar outras views/ tabelas trazendo outros dados mais relevantes para o tipo de monitoramento que você deseja realizar.

Referências:

SSRS Report Usage Queries

How to Display Report Execution Time in SQL Server Reporting Services (SSRS)

Report Server ExecutionLog and the ExecutionLog3 View

Importando dados JSON da Premier League com SQL Server 2016

Quando pensamos em projetos de BI e Big Data, um dos principais pontos que temos que planejar são as fontes de dados que iremos utilizar, estas que contém os dados que precisamos consumir para gerar as análises e os relatórios. E cada vez mais temos que trabalhar com formatos diferentes, devido às novas maneiras de armazenar as informações que existem.

O SQL Server 2016, visando se adaptar à esta realidade, traz nesta versão diversas funcionalidades e recursos novos, entre eles a possibilidade de manipular arquivos JSON. Podemos não somente importar arquivos, como também escrever consultas para retornar informações destes arquivos JSON e até gerar saídas neste formato, parecido com o que já existe com o formato XML.

Para exemplificar esta funcionalidade, realizei uma consulta à API do Fantasy Premier League 15/16, que pode ser acessada através da URL a seguir, e que gera um arquivo JSON para cada jogador (no exemplo abaixo, dados do jogador de id 170):

http://fantasy.premierleague.com/web/api/elements/170 *

Para quem não conhece, o Fantasy da Premier League é um game onde o desempenho dos jogadores de futebol que jogam no Campeonato Inglês valem pontos, ou seja, eles marcam pontos de acordo com as suas estatísticas. E são exatamente estas estatísticas que nós vamos importar para o SQL Server.

Utilizei o script abaixo para realizar a importação:

—Criando banco de dados de exemplo

CREATE DATABASE Sports

GO

USE Sports

GO

—Criando tabela com coluna DOC no formato JSON

CREATE TABLE Player

(id INT IDENTITY CONSTRAINT PK_JSON PRIMARY KEY,

DOC NVARCHAR(MAX) constraint [Properly formatted JSON] CHECK (ISJSON(DOC)>0));

—Gerando loop para carregar todos os arquivos para o SQL Server

declare @i int = 1

declare @file nvarchar(max)

declare @json nvarchar(MAX)

declare @sql nvarchar(max)

WHILE(@i<724)

BEGIN

SET @file = ”’c:\json\’ + cast(@i as varchar(5)) + ‘.json”’;

SET @sql = ‘INSERT INTO Player

SELECT BulkColumn

FROM OPENROWSET (BULK ‘+@file+‘, SINGLE_CLOB) as j’

SET @i = @i +1;

EXEC sp_executesql @sql

END

Assim, conseguimos ter os arquivos JSON de todos os jogadores dentro do nosso banco de dados do SQL Server 2016!

Podemos, por exemplo, realizando a consulta abaixo, retornar os 10 jogadores do Leicester City que mais fizeram gols no campeonato:

—Consulta para retornar os 10 jogadores do Leicester City que mais marcaram gols

SELECT

TOP 10

JSON_VALUE(doc,‘$.web_name’) AS player_name,

JSON_VALUE(doc,‘$.goals_scored’) AS goals_scored

FROM Player p

WHERE JSON_VALUE(doc,‘$.team_name’) = ‘Leicester’

ORDER BY cast(JSON_VALUE(doc,‘$.goals_scored’)as int) DESC

Resultado (imagem abaixo):

Para facilitar, disponibilizei os códigos e os arquivos utilizados no seguinte diretório do GitHub:

GitHub – Premier League Datasets

* Atualização: houve uma mudança na URL da API do Fantasy Premier League para a temporada 16/17. Em vez da URL antiga (http://fantasy.premierleague.com/web/api/elements/170), utilizar a nova (https://fantasy.premierleague.com/drf/element-summary/170). Para reproduzir o script, utilizar arquivos .json que se encontram no GitHub.

Referência:

Importing JSON files into SQL Server using OPENROWSET (BULK)

Sugestão de leitura relacionada:

JSON support in SQL Server 2016