SQL Maniacs – SQL Server 2016 Temporal Tables

Pessoal, na última quarta feira tive a honra de participar novamente de mais um evento do SQL Maniacs a convite do MVP em SQL Server Vitor Fava. Dessa vez estive falando sobre a mais nova feature do SQL Server 2016 denominada Temporal Tables.

– Desde já agradeço ao Vitor pela sua disponibilidade e predisposição em sempre ajudar a comunidade! –

Através desta tecnologia é possível armazenar dados hostóricos em ambiente OLTP de maneira simples. Com isso é possível realizar rollback de transações já comitadas, fazer consultas históricas em ambiente OLTP e realizar análises de Slowly Changing Dimension em ambientes de Business Intelligence.

Além de explorar as possibilidades da feature, foi realizado também um comparativo com o Change Data Capture e suas principais diferenças.

Segue abaixo o vídeo da apresentação que está no canal do youtube do SQL Maniacs e, se você quiser realizar os testes, clique no LINK para realizar o download da apresentação de slide e e SSMS Project com os scripts usados. 

Espero poder ajudá-los!

Report Services 2016 / Datazen Beginning – Parte 10

Neste post da Série Datazen Beginning você irá aprender sobre a construção Dashboards usando o Datazen Publisher e aprenderá também a publicá-lo dentro do Datazen Server.


Depois de você ter aprendido sobre os tipos de Data Views e Dashboard Runtime na Parte 9 do conjunto de posts e também, na Parte 8, entender o funcionamento de cada um dos tipos de componentes da aba Layout View dentro do Publisher, agora você irá usar alguns para criar o primeiro Dashboard e irá publicá-lo dentro do Servidor Core do Datazen para visualizá-lo através do Datazen View (Computador, Tablet e Celular) e/ou Web.

Usaremos para criação do Dashboard que irá prover a análise das vendas de USA contidas no banco de dados AdventureWorksDW2014 o Data View Quantidade de Vendas por Cliente que você irá criar usando a query abaixo conforme aprendeu na Parte 5 do conjunto de posts.

</em>
SELECT dc.FirstName + ' '
+ CASE WHEN MiddleName IS NULL THEN ''
ELSE MiddleName END + ' '
+ dc.LastName AS NomeCompleto, 
dp.EnglishProductName Produto, 
dg.EnglishCountryRegionName AS Pais, 
dg.StateProvinceName AS Estado,
dg.City AS Cidade,
dod.CalendarYear AS Ano, 
dod.EnglishMonthName AS Mes, 
dod.FullDateAlternateKey AS DataVenda, 
f.ShipDate AS DataEnvio,
f.SalesOrderNumber AS OrdemVenda,
1 AS QtdVendas, 
f.UnitPrice AS ValorVenda
FROM FactInternetSales f 
JOIN DimProduct dp ON dp.ProductKey = f.ProductKey 
JOIN DimCustomer dc ON dc.CustomerKey = f.CustomerKey 
JOIN DimSalesTerritory dst ON dst.SalesTerritoryKey = f.SalesTerritoryKey 
JOIN DimDate dod ON dod.DateKey = f.OrderDateKey
JOIN DimGeography dg ON dg.SalesTerritoryKey = dst.SalesTerritoryKey 
AND dg.GeographyKey = dc.GeographyKey
WHERE dg.EnglishCountryRegionName = 'United States'

Observação – Partirei do pressuposto de que você já está familiarizado com as interfaces do Datazen Publisher. Caso não esteja, sugiro que você leia a Parte 7 do conjunto de posts.


Abra o Publisher e conecte-se ao servidor normalmente, conforme fez na Parte 7 do conjunto de posts.

Feito isso, clique com o botão direito dentro da pagina principal da ferramenta e clique em Create. Em seguinda você terá duas opções.

A primeira permitirá a criação de um Dashboard do zero. A segunda permite a seleção de um Dashboard já existente para que você possa efetuar o desenvolvimento do novo Painel a partir desse selecionado – Isso não sobrescreverá o Painel. A ferramenta irá efetuar uma cópia do Dashboard existente.

Selecione a primeira opção – Start from on empty dashboard.

02

A próxima etapa será, dentro do painel Layout View, no canto esquerdo da tela, selecionar o primeiro Componente que você irá usar para criar o Dashboard de análise de vendas da Adventure Works. O Componente será o Time Navigator.

03

Cada componente do Datazen possui uma porção de configurações que podem ser efetuadas. Elas ficam localizadas na parte inferior da tela do Publisher.

Você irá efetuar a configuração nas seguintes opções:

Time Level é responsável por permitir graficamente o Drill Down entre a hierarquia de tempo. Ou seja, se configurado com a opção default que é o drill somente até o mês, ainda que você tenha no Data View a informação de dia, semana ou hora, por exemplo, o componente não permitirá que o faça. Configure conforme a imagem a baixo:

04

O Time Range Presets configura quais os níveis hierárquicos da data serão disponibilizados para seleção no canto esquerdo do componente. Por padrão o componente utiliza 4 opções que são Todos – uma análise agregada pelo período de tempo total, Último Ano, Últimos Seis Meses e Último TrimestreVocê irá selecionar somente a opção All – Todos.

05

Agora você irá inserir no painel dois Selection List conforme abaixo:

06

Nas configurações de cada um dos Selection List defina como Title para o componente do canto superior esquerdo a lable Selecione um Pais para análise:, e, para o Selection List de baixo a lable Selecione um Estado para análise:.

Desmarque a opção Allow Select All para o Seletor de País. no Seletor de Estado, no campo Select All Text: preencha com o texto Todos os Estados.

Feito isso, arraste para o Dashboard o componente Number. Nas opções de configuração, defina o Title como Valor Vendido: conforme a imagem abaixo:

08

Adicione no Dashboard também o componente Gradient Heat Map da lista Maps. Renomeie o campo Title para Valores Vendidos por Estado – USA: e, em Map, selecione USA. Isso significa que o mapa que será configurado é o dos Estados Unidos conforme abaixo.

9

Agora arraste para o Painel o componente Simple DataGrid preencha o campo Title com Analítico de Vendas Realizadas – Adventure Works.

Entenda uma importante opção dos DataGridsÉ possível que você apresente os dados nos DataGrids, tanto assim como eles vem do Data View, ou agregando-os através de uma coluna text ou através de uma coluna de Data. Isso é definido marcando as opções Aggregate by Time e Aggregate by a Field. Caso você queira apresentar os dados assim como eles vierem da fonte, deixe ambas as opções desmarcadas.

Para a criação desse dashboard, marque a opção Aggregate by a Field.

Na configuração Row Numbers selecione a opção Hide. Essa opção serve para mostrar uma espécie de coluna ID default do Datazen no canto esquerdo grid.

10

Renomeie o Dashboard para Analise de Vendas USA – Adventure Works.

No canto superior direito, existem dois botões. Um deles serve para que você possa configurar uma opção de paleta de cor para o Dashboard.

11

O botão que fica na lateral esquerda ao que você usa para definição de paleta serve para que você consiga configurar um dos MAIORES DIFERENCIAIS do Datazen: A apresentação do Dashboard em dispositivos móveis.

12-0

Selecione a opção Tablet e você verá que a tela irá renderizar e apresentará o grid no formato de um Tablet. Dessa maneira, você só precisará arrastar e redimensionar os componentes que já foram configurados no Dashboard Master (Web).

12

Mude novamente para a opção Phone e configure o Dashboard para apresentação em celular.

12_1

Feito isso, o Layout do Dashboard estará pronto para todas as plataformas.


Agora que você desenvolveu a interface gráfica, você precisará configurar os componentes com os dados do Data View Quantidade de Vendas por Cliente que você criou anteriormente.

Para isso, na aba Data View, no canto superior direito da tela, selecione a opção Add Data.

13

Caso você não tenha configurado a pasta de trabalho do Datazen Publisher, uma tela de mensagem se abrirá pedindo que você o faça.

14

Basta que você clique em Choose Folder e escolha uma pasta. Como foi explicado na Parte 7 do conjunto de posts, essa pasta será usada caso você queira salvar localmente o Dashboard que criou.

Depois disso, na tela que irá abrir você terá duas opções de seleção. Uma delas é construir o Dashboard com base em dados advindos de um arquivo Excel local ou apartir de um Data View do Server. Selecione a segunda opção.

15

Feito isso será possível visualizar todos os Data Connections que estiverem disponíveis no Datazen Server – levando em consideração, claro, que o usuário que está logado no Publisher, tenha acesso a eles.

16

Selecione na proxima tela o Data View Quantidade de Vendas por Cliente.


Depois de capturar o Data View que será usado no Dashboard, você precisará configurar os dados que serão mostrados através dos componentes.

Selecione o componente Selection List que você criou para seleção de Pais.

Esse componente será responsável por filtrar os dados apresentados no Dashboard de acordo com que for selecionado. No caso atual, irá realizar o filtro com base no País ou Estado que o usuário selecionar.

Em Data Properties, na opção Keys:, Escolha o Data View QuantidadedeVendasporCliente. na seleção ao lado você irá escolher a coluna de lookup. Essa coluna será responsável por filtrar os views que participam do Dashboard de acordo com o que for selecionado na Lista de Seleção. Escola a coluna Pais.

Ao lado, em Options…, marque o check box Time Navigator 1. Ao selecionar essa opção você está configurado o Selection List para ser filtrado pelo Time Navigator.

Na seleção opção de Labels:, selecione também a coluna de Pais. Será essa informação que irá ser apresentada graficamente nas opções de seleção.

O canto esquerdo, em Tables Filtered by Selecione um País para Análise:, marque o check box do Data View QuantidadedeVendasporCliente e selecione a coluna Pais como coluna de Lookup.

19

Faça o mesmo para o Selection List que você nomeou como Selecione um Estado para Análise:, porém, em Keys e Labels, selecione a coluna Estado e, na configuração de coluna de lookup do Data View QuantidadedeVendasporCliente, escolha também a coluna Estado.

A unica diferença aqui é que, em Options… da configuração de Keys, você irá marcar dois check box: Um para o Time Navigator e outro para o Selection List configurado para seleção de Pais. Faça conforme imagem abaixo:

20

Agora selecione no canto esquerdo o Time Navigator. Selecione o Data View QuantidadedeVendasporCliente. Na segunda opção de seleção, você irá configurar a métrica que irá preencher o componente. Marque somente o Check box da métrica QtdVendas.

Em Options… será possível que você escolha os componentes que serão filtrados pelo tempo selecionado no componente de tempo. Marque as duas opções que são referentes às duas Listas de Seleção – Pais e Estado.

Configure o componente conforme imagem abaixo:

18

Selecione o componente Number nomeado por Valor Vendido:.

Em sua lista de configurações, escolha o Data View QuantidadedeVendasporCliente e ao lado, nas métricas, escolha QtdVendas e em Options… selecione todos os Check box conforme abaixo:

21

Agora você irá configurar o componente de Mapa. Em suas configurações escolha o Data View QuantidadedeVendasporCliente, em Keys escolha a coluna Estado. Essa coluna será responsável por mostrar os dados de vendas em cada estado do mapa dos Estados Unidos.

Observe que se você tivesse um outro mapa de país, como o lookup é realizado por nome e não por latitude e longitude, o painel não iria apresentar os dados.

Em Options…, Selecione todos os Check box e em Values, selecione a coluna ValorVenda conforme abaixo:

22

Agora você irá configurar o último componente do Dashboard. Selecione no canto esquerdo o DataGrid.

Em Data for the Grid View: selecione o Data View QuantidadedeVendasporClente e, em Options…, Marque todos os Check box. Em Field to Aggregate By: Selecione a opção NomeCompleto.

Em Columns, no canto inferior direito, marque o check box referente as colunas OrdemVenda, QtdVendas e ValorVenda.

Em Options… da coluna QtdVendas, deixe a opção String Format com valor default – General – e altere a Aggregation Function para SUM. Já em Options… da coluna ValorVenda, Altere o String Format para DefaultCurrency e a Aggregation Function para SUM.

Configure conforme abaixo:

23


Agora que a configuração de Layout e de Data View foi realizada, você irá efetuar as configurações da aba Dashboard Settings.

Em Local Settings, na opção Dashboard Culture: selecione a opção Portuguese (Brasil).

Marque os Check box Enable client data caching e Encrypt data stored on the client caso não esteja selecionado conforme imagem abaixo:

24

Em Server Settings, na opção Server Group, clique em Create New Group… e crie um grupo de Dashboards dentro do Datazen Server. Nomeie o grupo de Análise de Vendas.

25

Na aba Run Preview, visualize o Dashboard em execução, antes de publicá-lo.

27

Feito isso, clique no botão de voltar no canto superior esquerdo do Dashboard, depois clique com o botão direito na tela e selecione a opção Publisher to Server.

28

Feito isso, confirme as informações na próxima janela e clique em Publisher.

29

Feito isso, esse Painel estará disponível para acesso em qualquer dispositivo, seja eme móvel ou web.


No próximo post da série você irá aprender como as opções de segurança para Dashboards e KPIs.

Criando Branding’s customizados

Neste post você irá aprender a customizar o seu Datazen Viewer e Datazen Publisher de modo a apresentar para os usuários a logo e cores da empresa, por Servidor ou por Hub.

Obs – Para que seja possível o entendimento desde conteúdo, aconselho que você leia anteriormente o conjunto de posts Datazen Beginning.


A tela de todos os Viewers, sejam eles mobile ou web, vêm por default com a logo, background e cores relacionadas ao Datazen.

Captura de Tela 2015-11-10 às 14.40.44

Importante – Para que seja possível efetuar essa alteração, a não ser que você tenha um bom conhecimento de designer, será necessário da ajuda do profissional de Web Designer da sua instituição.

zip_256Efetue o download do arquivo ao lado, descompacte-o e substitua as logos do Datazen pelas logos de sua instituição. Caso queira poderá também alterar as propriedades dos arquivos .css, .html e .json.

É MUITO IMPORTANTE que você se atente ao seguinte:

  1. TODOS os arquivos devem permanecer com os MESMOS NOMES. Caso você altere algum dos nomes dos arquivos, ao fazer o upload do branding, o serviço interno do Datazen irá dar um erro e você não conseguirá excluir esse brand do Server ou Hub e muito menos excluir o Hub (se for o caso). Até agora, a única solução que encontrei pra esse problema foi reinstalar todo o serviço;
  2.  Você só poderá ter um branding customizado por Hub e apenas um por Server.

Feito as alterações, você precisará recompactar todos os arquivos (um total de 64) em um arquivo .zip. Esse arquivo .zip pode ser renomeado de acordo com sua intenção.

Após o arquivo .zip criado será necessário realizar o seu upload no painel de controle. Basta ir na aba do Painel de Administração denominada Branding.

Captura de Tela 2015-11-10 às 15.01.41Caso você queira realizar o upload do branding criado dentro do Server, ele poderá ser utilizado em todos os hubs, tanto os existentes quanto os que podem vir a ser criados. Você pode também realizar o upload por Hub.

Depois de realizado o upload é possível também realizar a exclusão ou o Download do pacote .zip de arquivos.

Captura de Tela 2015-11-10 às 16.04.40

Uma outra questão a ser observada é que, caso você use um Branding customizado por Hub, caso um usuário tenha acesso a mais de um Hub, será necessário que seja selecionado o Branding de um dos Hubs dos quais ele tem acesso para ser visualizado.


Para mostrar o Hub customizado nos aplicativos Viewers, caso isso não ocorra por default, Você precisará clicar com o botão direito dentro do background do app e selecionar a opção Manage.

01

Feito isso, acesse a aba Branding e escolha o Brand personalizado que deseja utilizar.

02


Segue um exemplo de um dos clientes os qual já implementei a ferramenta.

03 (1)

 

 

Report Services 2016 / Datazen Beginning – Parte 8

Neste post da série você irá aprender sobre os tipos de componentes gráficos que estão disponíveis para desenvolvimento de Dashboards dentro da ferramenta Datazen Publisher.


Na Parte 7 do conjunto de posts você conheceu a ferramenta usada para criação e publicação de Dashboards do Microsoft Datazen, o Datazen Publisher.

Nesta etapa, você irá conhecer mais sobre a interface gráfica da ferramenta entendendo o funcionamento de cada um dos tipos de componentes localizados na aba Layout View.


Captura de Tela 2015-11-06 às 16.31.55 Os Navegadores são responsáveis por filtrar os dados apresentados nas demais visualizações. O Time Navigator é usado para Tempo e o Scorecard Grid e Selection List para seleção de um filtro específico.


Captura de Tela 2015-11-06 às 16.32.03Os Gauges são nada mais que KPIs dentro de um Dashboard. É possível fazer análises de números, relacionando-os com valores de comparação e verificar graficamente, através das cores ou dos ponteiros esse resultado (Para saber mais sobre KPIs veja a Parte 6).

Captura de Tela 2015-11-06 às 16.32.21

Os Charts são interfaces para construção de análises gráficas. É possível configurá-los com base em um tempo, em categorias os totalizadores.

Você pode também utilizar gráficos para efetuar comparações de valores. São os Comparison Charts.


Captura de Tela 2015-11-06 às 16.32.30Os Maps são usados para configuração de análises geográficas. Um ponto positivo é que você não precisa ter em sua base dimensional dados geográficos. O Datazen configura os mapas com base nos nomes das regiões.


Captura de Tela 2015-11-06 às 16.32.40

Os Data Grids são tabelas analíticas que mostram os dados propostos de forma tabular. É possível criar indicadores e gráficos por resultado dentro de um Data Grid usando os Indicator e Chart DataGrid.


No próximo post da série você aprenderá mais sobre os Dashboards Runtime e também sobre os tipos de Data Views que podem ser criados através do Datazen Enterprise Server.

SSIS – Criando seu próprio SCD 4

Neste post da série você irá aprender a usar alguns componentes da ferramenta de ETL e integração de dados da Microsoft, o Integration Services, para criar um fluxo de carga de dimensões personalizado.


Para conseguir manter a abstração de SGBD, não utilizando queries complexas para efetuar as cargas, e ainda assim otimizar o processo de ETL da dimensão de Clientes da Light LTDA, você precisará criar o seu próprio fluxo de SCD, utilizando outros componentes disponíveis no Integration Services.

Para iniciar a criação do novo fluxo de ETL, primeiramente crie uma nova dimensão chamada DM_CLIENTES_v2 para efetuar a nova carga. utilize o código abaixo.

USE OLAP
GO 

CREATE TABLE DM_CLIENTES_v2 ( 
    SK INT NOT NULL IDENTITY (1,1),
    ID INT,
    NOME VARCHAR(25),
    CPF VARCHAR(14),
    EMAIL VARCHAR(25),
    DDD CHAR(2),
    TELEFONE CHAR(8),
    DATA_INICIO DATE,
    DATA_FIM DATE)
GO

Em solution explorer clique com o botão direito em SSIS Packages e crie um novo pacote. Arraste um novo Data Flow Task da barra de ferramentas, para o Control Flow. Dê um duplo clique e, novamente da barra de ferramentas, arraste para dentro do campo em branco do Data Flow os seguintes componentes:

  • Dois componentes OLE DB SOURCE – Serão responsáveis pelas conexões com a tabela CLIENTES do banco OLTP e com a Dimensão DM_CLIENTES_v2 do banco OLAP;
  • Dois componentes SORT – O componente SORT é responsável, como o próprio nome já diz, por ordenar os registros de uma origem ligada a ele de acordo com a(s) chave(s) selecionada(s);
  • Um componente MERGE JOIN – Esse componente será responsável por concatenar as colunas da tabela de origem (CLIENTES) com as colunas da tabela de destino (DM_CLIENTES_v2) de acordo com o(s) campos(s) ordenado(s) pelos sorts;
  • Um componente CONDITIONAL SPLIT – Esse componente é responsável por filtrar os dados conforme os parâmetros passados. Nesse fluxo, ele separará os novos registros (as linhas que existem na tabela de Clientes mas não estão na dimensão), daqueles a serem atualizados dentro da dimensão DM_CLIENTES_v2 (seja com relação ao update ou a inserção de um novo registro histórico);
  • Um componente DERIVED COLUMN – Esse componente será responsável por criar a data de inicio e de fim que fará o versionamento dos dados históricos da dimensão DM_CLIENTES_v2;
  • Um componente OLE DB DESTINATION – Esse componente servirá para que possamos fazer a inserção dos novos registros na dimensão DM_CLIENTES_v2;
  • Três componentes OLE DB COMMAND  Esses componentes serão responsáveis por fazer o update nos campos que precisam ser atualizados e por inserir os novos registros históricos, atualizando esses registros com as respectivas datas de inicio e fim.

Captura de Tela 2015-10-13 às 17.00.37


Comece definindo cada um dos OLE DB Source. O primeiro deve estar configurado na base de dados OLTP, na tabela Clientes, chame-o de Origem. O segundo deve estar configurado na base de dados OLAP, na dimensão DM_CLIENTES_v2, chame-o, por sua vez, de Destino. Ligue cada um dos OLE DB Source em um operador Sort.

Captura de Tela 2015-10-13 às 17.08.59

Agora configure cada um dos Sorts para ordenar os registros pelo campo ID.

Captura de Tela 2015-10-13 às 17.09.59

Renomeie cada um deles (Segundo o OLE DB Source em que estiver ligado) de Ordena_Origem e Ordena_Destino.  Ligue o Sort Ordena_Origem ao componente Merge Join. Na janela que se abrirá, escolha a opção Merge join left output.

Captura de Tela 2015-10-13 às 17.11.37

Ligue também o Sort Ordena_Destino ao Merge Join. feito isso, abra o componente Merge Join. Ele servirá para concatenar as colunas da tabela Clientes com as colunas da dimensão DM_Clientes, ordenando-as através da chave definida nos Sorts. É necessário que você altere a opção join type de Inner join, para Left outer join. Isso se faz necessário para trazermos os registros que estão na Origem, mas ainda não foram inseridos no Destino.

Uma boa prática, já que as colunas da Origem e as colunas do Destino têm o mesmo nome, é concatenar no Output das colunas derivadas do Sort Ordena_Destino, o sufixo _Dest para que não venhamos a fazer confusão nas configurações seguintes. Veja na figura abaixo.

Captura de Tela 2015-10-13 às 17.16.09

Seguindo com a construção do fluxo de ETL, ligaremos o componente Merge Join ao componente Derived Column. Renomeie esse componente para Cria_Datas. Abra-o e configure  duas novas colunas para o fluxo. Uma chamada Data_Inicio e a outra Data_Fim. Use o código abaixo em ambas as colunas na aba Expression.

(DT_DBTIMESTAMP)(@[System::StartTime])

Agora, ligue o Cria_Datas ao componente Conditional Split. Esse permitirá que os dados sejam divididos para que se possa trabalhar separadamente com cada situação. Configure as três linhas no Conditional Split seguindo a figura abaixo.

Captura de Tela 2015-10-13 às 17.39.27

Após ter configurado cada uma das três linhas de código do Conditional Split, você o ligará ao componente OLE DB Destination. Será apresentada uma janela com a opção de você fazer esse link pela condição Insert, Update ou Histórico. Escolha a opção Insert. Configure este componente para receber os novos registros, ignorando a coluna SK (ela é IDENTITY) e a coluna DT_FIM, logo esses registros não possuem esse dado pois são novos.

Captura de Tela 2015-10-13 às 17.47.37

Agora faça outra ligação do Conditional Split para o primeiro OLE DB Command. Selecione a opção UPDATE na janela de configurações.

Esse componente merece uma atenção especial pois, além de sua configuração ser um pouco mais complexa que a dos demais componentes, ele é o principal componente do fluxo por ser o responsável, tanto pelos updates, quanto pela atualização do versionamento dos registros através das colunas DATA_INICIO e DATA_FIM, criando assim os registros históricos. Ele funciona através de parâmetros e queries ANSI.

Ao abrir a tela do componente OLE DB Command, ele estará na aba de conexões. Você precisará indicar o banco o qual deseja fazer mudanças. Ness caso, faça conexão com o database OLAP.

Na aba de propriedades do componente, você se preocupará somente com a última definição: SqlCommand. Nesse campo você escreverá a query que será responsável pela mudança que você desejar executar nos seus através do ETL.

Nesse caso você usará o script abaixo.

UPDATE DM_CLIENTES2 SET DATA_INICIO = ?, NOME = ? WHERE ID = ? AND DATA_FIM IS NULL; 

Essa query é um simples de update, construída no padrão ANSI que fará a atualização dos campos DATA_INICIO, NOME com base no ID da dimensão DM_CLIENTES_v2.

Na próxima aba, você irá mapear que campos os parâmetros receberam para que possam atualizar o campo DATA_INICIO e NOME da Dimensão DM_CLIENTES_v2.

Os parâmetros são definidos na sequência exata de como são configurados na query. Ligue o Param_0 ao campo DT_INICIO (campo que foi definido no Derived Column antes de começarmos a configurar o OLE DB Command), o Param_1 à coluna NOME e o Param_2 à coluna ID (note que estamos usando os campos que vieram da origem e foram concatenados aos campos do destino com o auxilio do componente Merge Join).

Feito isso, você já tem configurado no fluxo os novos registros que serão adicionados diariamente pelo banco OLTP da Light LTDA e também as possíveis mudanças nos nomes dos Clientes, que possam vir a ocorrer devido algum tipo de erro de cadastro.


Agora você irá configurar a etapa final do fluxo que é a inserção dos registros históricos. Ligue o componente Conditional Split ao próximo OLE DB Command e na janela de configurações selecione a ultima opção disponível, HISTORICO. Conecte no banco de dados OLAP.

Usaremos o código abaixo para configurar na aba de propriedades do componente a opção SqlCommand.

UPDATE DM_CLIENTES_v2 SET DATA_FIM = ? WHERE ID = ? AND DATA_FIM IS NULL;

Essa query também realizará um update, e também é construída em padrão ANSI. Por sua vez, será responsável pela atualização do campo DATA_FIM dos registros que formarão o conteúdo histórico da dimensão DM_CLIENTES_v2.

Como já fora dito, basta seguir uma sequência lógica para configurar os parâmetros aos campos devidos. O primeiro parâmetro fora definido como sendo o novo valor da coluna DATA_FIM, logo o ligaremos aos campo DATA_FIM. O segundo ligaremos à coluna ID.

O próximo passo é a configuração do OLE DB Command que fará a inserção do novo registro histórico. Você usará a query abaixo para configurar a opção SqlCommand.

INSERT INTO DM_CLIENTES_v2 (
    ID,NOME,CPF,EMAIL,DDD,TELEFONE,DATA_INICIO )
VALUES (?,?,?,?,?,?,?);

Esta é a última query (também em ANSI) de configuração do nosso fluxo de SCD que está sendo criado. Essa query de INSERT irá depositar na dimensão DM_CLIENTES_v2 todos os registros que ainda não foram adicionados no Data Warehouse da Light LTDA.

Agora apenas configure os parâmetros de forma sequencial à forma que a query foi construída ligando cada um deles ao campo responsável por popular cada coluna da DM_CLIENTES_v2. O fluxo ficará conforme a figura abaixo.

Captura de Tela 2015-10-13 às 18.17.03

Dessa forma, resumidamente, o Fluxo de SCD criado funciona assim:

  1. O fluxo lê os dados do OLTP e da Dimensão que está no OLAP;
  2. Em seguida ordena esses dados pela chave primária da dimensão (nesse caso o campo ID);
  3. Acontece no componente Merge Join nada mais que uma união entre os campos da origem e do destino. É formada uma grande tabela. Isso serve para que o proximo componente possa, com base na comparação entre um campo da origem e o mesmo campo no destino, decidir se aquela linha de registro irá ser inserida, atualizada, transformada em histórico ou se nem mesmo participará do resto do fluxo;
  4. O Conditional Split funciona como um Case da liguagem SQL. Ele irá decidir como cada linha de registro vai ser tratada com base na comparação entre o registro na origem e aquele mesmo registro do destino. Caso naquela linha de registro o campo de ID da origem estiver preenchido e o ID do destino não, ele direciona o dado para ser inserido na Dimensão. Funcionará no mesmo raciocínio com o fluxo que direciona para o Update e para o Histórico.

Se executada, a carga de ETL que foi criada será capaz de inserir todos os 500 mil registros da tabela Clientes na dimensão DM_CLIENTES_v2, em apenas aproximadamente 7 segundos.


Como foi demonstrado, o componente Slowly Changing Dimension do Integration Services, apesar de cumprir com o prometido de manter o versionamento dos dados históricos, deixa muito a desejar com relação a performance.

Não fora demonstradas atualizações e inserções de registros, mas, caso seja de seu interesse, altere dados ou insira novas linhas no banco de dados OLTP da Light LTDA e observe como o SSIS irá trabalhar em ambas as situações. A performance é incomparável.

Sendo assim, seguindo os conceitos de SCDs e o passo a passo de criação do fluxo de ETL de Slowly Changing Dimension, será possível ajustá-lo às realidades de qualquer situação existente no mercado.

SSIS – Criando seu próprio SCD 3

Neste post da série você irá aprender a configurar o componente Slowly Changing Dimension na ferramenta de Integração de Dados da Microsoft, o Integration Services.


Vamos agora iniciar os testes de carga da Dimensão de Clientes da Light LTDA. Utilizaremos em primeiro momento, o componente Slowly Changing Dimension do Integration Services.

Para que isso seja possível, precisamos criar primeiramente um projeto na ferramenta de ETL da Microsoft. Feito isso, abra o seu package default e arrastar da barra de ferramentas o componente Data Flow Task para o Control Flow.

Renomeie o componente para teste_1 e em seguida, abra-o. Após isso, vá até a barra de ferramentas e arraste para dentro do Data Flow Task, os componentes OLE DB Source e Slowly Changing Dimension. A tela da ferramenta deverá estar conforme abaixo.

Captura de Tela 2015-10-13 às 16.14.45

O círculo vermelho, no canto direito de cada um dos componentes, sinaliza que eles precisam ser configurados.

O componente OLE DB Source será responsável pela conexão com o banco de dados OLTP da Light LTDA.

Obs – Como exemplo, foi utilizado o OLE BD Source, porque foi utilizado o SQL Server. Caso você necessite extraia os dados de outro Database, ou mesmo de vários, o Integration Services tem a possibilidade de se conectar via ODBC a uma grande variedade de bancos.

Após configurado o componente de conexão à fonte de dados, você precisará ligá-lo ao componente Slowly Changing Dimension.

Ao abrir o componente SCD, será apresentada uma janela que lhe permitirá configurar as colunas de entrada de dados (colunas que virão da tabela Clientes do banco OLTP), as colunas da Dimensão de Clientes e a(s) chave(s) lookup. Configure conforme a figura abaixo.

Captura de Tela 2015-10-13 às 16.21.02

Após isso, você poderá selecionar na próxima janela de configuração os tipos de Slowly Changing Dimension que usará na carga da dimensão de Clientes.

É possível, através deste componente, manter os dados da coluna imutáveis (Fixed attibute), executar um update nos registros da coluna (Changing attibute), ou manter o histórico de alterações dos dados da coluna (Historical attribute).

Nesse caso, os dados de CPF do cliente não serão alterados, logo, esses serão códigos únicos por pessoa. O NOME do cliente foi marcado como Changing attibute porque, caso haja algum erro no cadastro, será possível corrigi-lo em uma carga futura. Já os campos DDD, TELEFONE e EMAIL foram definidos como Historical attribute, pois queremos guardar um histórico de todos os telefones e e-mails de nossos clientes. A tela de configuração ficará como na imagem a seguir.

Captura de Tela 2015-10-13 às 16.23.24

Avance na configuração do próximo passo do componente. Na tela de opções dos atributos históricos, selecione a opção use start and end dates to identify current and expired recordes e, para a opção Start date column, escolha o campo DATA_INICIAL e para a opção End date column, o campo DATA_FIM (esses campos guardarão a data de inicio e de fim de cada um dos registros históricos da dimensão).

Captura de Tela 2015-10-13 às 16.25.25

No próximo passo, desabilite a opção de suporte a membros inferidos. Ao final da configuração do componente, o próprio Slowly Changing Dimension se encarregará de construir todo o fluxo que fará a carga da nossa dimensão de Clientes da Light LTDA. O fluxo de ETL ficará conforme a imagem abaixo.

Captura de Tela 2015-10-13 às 16.28.14

Essa rotina de carga da dimensão DM_CLIENTES, se executada, terminará em um tempo médio de 2 horas e 25 minutos.


No próximo post da série você aprenderá a usar alguns componentes da Ferramenta de integração de Dados e ETL da Microsoft, o Integration Services, para criar um fluxo de carga de dados personalizado para popular a Dimensão de clientes.

SSIS – Criando seu próprio SCD 2

Nesse post da série você irá preparar o ambiente de testes para que seja possível criarmos os fluxos de carga de dados da dimensão de Cliente de nosso Data Warehouse fictício da Light LTDA.


Criando o ambiente de testes OLTP e OLAP

Um OLTP (On-line Transaction Processing) é que um sistema de banco de dados que trabalha com os dados em tempo real.

Geralmente estes SGBDs estão ligados a uma aplicação, recebendo constantemente os dados inseridos, alterados e excluídos desse sistema pelo usuário.

Já um On-line Analytical Processing (OLAP), como o próprio nome já diz, é um ambiente responsável por guardar dados analíticos, um Data Warehouse.


Vamos iniciar a criação do nosso ambiente de testes. Você irá criar a tabela, que guarda os dados dos clientes no banco OLTP de uma grande empresa de vendas fictícia, chamada Light LTDA, usando o código abaixo.

CREATE DATABASE OLTP
GO

USE OLTP
GO 

CREATE TABLE CLIENTES ( 
    ID INT NOT NULL IDENTITY (1,1),
    NOME VARCHAR(25),
    CPF VARCHAR(14),
    EMAIL VARCHAR(25),
    DDD CHAR(2),
    TELEFONE CHAR(8),
    DATA_CADASTRO DATE)
GO

Depois de criada a base de dados e a tabela de clientes, é necessário inserir os seus registros. Você adicionará à essa tabela 500 mil dados de clientes no ano de 2013 usando o código abaixo.

DECLARE @n INT, @n1 INT, @n2 INT, @n3 INT, @n4 INT, 
        @n5 INT, @n6 INT, @n7 INT, @n8 INT, @n9 INT 
DECLARE @d1 INT, @d2 INT
DECLARE @nome VARCHAR(21), @email VARCHAR(20), @cpf VARCHAR(14) 
DECLARE @ddd CHAR(2), @telefone CHAR(8)
DECLARE @data DATE
DECLARE @contador INT

SET @contador = 0

WHILE @contador < 500000

BEGIN	
-- Gera nome do cliente aleatório.
SET @nome = SUBSTRING(CONVERT(VARCHAR(40), NEWID()),0,21);

-- Gera CPF do cliente aleatório.
SET @n = 9;
SET @n1 = CAST((@n + 1) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n2 = CAST((@n + 1) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n3 = CAST((@n + 1) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n4 = CAST((@n + 1) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n5 = CAST((@n + 1) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n6 = CAST((@n + 1) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n7 = CAST((@n + 1) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n8 = CAST((@n + 1) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @n9 = CAST((@n + 1) * RAND(CAST(NEWID() AS VARBINARY )) AS INT)
SET @d1 = @n9*2+@n8*3+@n7*4+@n6*5+@n5*6+@n4*7+@n3*8+@n2*9+@n1*10;
SET @d1 = 11 - (@d1%11);

IF (@d1 >= 10) 
SET @d1 = 0
SET @d2 = @d1*2+@n9*3+@n8*4+@n7*5+@n6*6+@n5*7+@n4*8+@n3*9+@n2*10+@n1*11;
SET @d2 = 11 - ( @d2%11 );

IF (@d2 >= 10) 
SET @d2 = 0;
SET @cpf = CAST(@n1 AS VARCHAR(1)) + 
CAST(@n2 AS VARCHAR(1)) + 
CAST(@n3 AS VARCHAR(1)) + '.' + 
CAST(@n4 AS VARCHAR(1)) + 
CAST(@n5 AS VARCHAR(1)) + 
CAST(@n6 AS VARCHAR(1)) + '.' +
CAST(@n7 AS VARCHAR(1)) + 
CAST(@n8 AS VARCHAR(1)) + 
CAST(@n9 AS VARCHAR(1)) + '-' +
CAST(@d1 AS VARCHAR(1)) + 
45 CAST(@d2 AS VARCHAR(1))

-- Gera email do cliente aleatório.
SET @email = SUBSTRING(CONVERT(varchar(40), NEWID()),0,11) + 
'@email.com';

-- Gera data de cadastro do cliente aleatória.
SET @data = CONVERT(DATE, CONVERT(VARCHAR(15),'2013-' 
+CONVERT(VARCHAR(5),(CONVERT(INT,RAND()*12))+1) + '-' + 
CONVERT(VARCHAR(5),(CONVERT(INT,RAND()*27))+1) ))

-- Gera DDD do cliente aleatório.
SET @ddd = CAST(@n8 AS VARCHAR(1)) +
CAST(@n4 AS VARCHAR(1))

-- Gera telefone do cliente aleatório.
SET @telefone = CAST(@n6 AS VARCHAR(1)) +
CAST(@n9 AS VARCHAR(1)) +
CAST(@n2 AS VARCHAR(1)) + 
CAST(@n4 AS VARCHAR(1)) +
CAST(@n1 AS VARCHAR(1)) +
CAST(@n5 AS VARCHAR(1)) +
CAST(@n8 AS VARCHAR(1)) +
CAST(@n3 AS VARCHAR(1)) 

-- Popula a tabela Clientes.
INSERT INTO CLIENTES VALUES (
    @nome ,
    @cpf ,
    @email ,
    @ddd ,
    @telefone ,
    @data )

SET @contador = @contador + 1 

END

Por último, você precisará criar o banco OLAP e a dimensão de Clientes de um dos Data Marts da instituição Light LTDA. Isso será possível utilizando o script abaixo.

CREATE DATABASE OLAP
GO
 
USE OLAP
GO 

CREATE TABLE DM_CLIENTES ( 
     SK NOT NULL IDENTITY (1,1), 
     ID INT NOT NULL,
     NOME VARCHAR(25),
     CPF VARCHAR(14),
     EMAIL VARCHAR(25),
     DDD CHAR(2),
     TELEFONE CHAR(8),
     DATA_INICIO DATE
     DATA_FIM DATE )
GO

Obs – Para quem está iniciando os estudos em Business Intelligence, os Data Marts são pequenas partes de um Data Warehouse formados por tabelas denominadas Fatos e Dimensões. Basicamente, as Dimensões são tabelas que contém atributos relacionados a determinado assunto. Já as tabelas de Fato contém as chaves primários das dimensões (as Foreign Key) e medidas, métricas e valores.


No Próximo post da série você irá aprender a configurar o componente Slowly Changing Dimension na ferramenta de ETL Integration Services.