Classificando as Transformações no Data Flow do Integration Service (SSIS)

Como não estou com o notebook que contém a VM de SQL Server 2008, não tenho como completar a série de artigos que fala sobre o tipo de dados hierárquico. Já que estou ministrando o curso 2795 sobre Design de ETL, vou escrever sobre o Integration Service (SSIS).

No DTS do SQL Server 2000 transformar dados significa escrever um VB Script executado linha a linha durante a importação, o que obviamente gerava sérios problemas de desempenho. Devido a esta limitação no DTS, para médios e grandes volumes de dados, se utilizava uma área intermediária chamada “Staging” dividindo o processo de importação em duas fases: Origem – Staging e Staging – Destino. Na primeira fase (Origem – Staging) carregavam-se os dados sem transformar, centralizando em uma única base intermediária no SQL Server destino. Na segunda fase (Staging – Destino) instruções T-SQL eram utilizadas para transformar os dados, obtendo melhor desempenho. Devido a esta prática muito comum no DTS, este não era classificado como uma ferramenta de ETL (Extract Transform Loan) e sim como ELT (Extract Loan Transform).

A área Data Flow representa a principal novidade no SSIS, onde construímos um fluxo de dados com objetivo de importar realizando transformações com desempenho, classificando o SSIS como uma real ferramenta de ETL. Mas para se obter o máximo de desempenho durante o Data Flow é fundamentar compreender alguns conceitos como buffers e a classificação das transformações.

No Data Flow Buffer é a coleção de colunas que flui de uma transformação para a próxima, sendo classificado como de entrada ou de saída:
- Source: contém apenas buffer de saída.
- Transformations: contém buffer de entrada e saída.
- Destination: contém apenas buffer de entrada.

As transformações podem ser classificadas de dois modos:

1) Síncrona X Assíncrona
Uma transformação é considerada Síncrona quando ela utiliza o mesmo buffer que a transformação anterior. Isto é possível porque o buffer de entrada e saída possui sempre a mesma quantidade de linhas. Já uma transformação Assíncrona possui buffer de entrada com quantidade de linhas diferente do buffer de saída, criando um novo buffer na sua saída.

Fica claro que transformações Síncronas proporcionam melhor desempenho e redução no consumo de recursos.

2) Sem Bloqueio, Semi Bloqueio e Com Bloqueio
Uma transformação Sem Bloqueio é aquela que não retém as linhas para executar o algoritmo, liberando a linha imediatamente. Como Exemplo a transformação “Derived Column” aplica o algoritmo linha a linha sem retenção.
Transformações de Semi Bloqueio retém parcialmente o fluxo devido a funcionalidade implementada pelo algoritmo. A transformação “Merge Join” é um exemplo de retenção parcial, pois implementa o algoritmo balance line para realizar o join de dois fluxos de dados.
Já as transformações Com Bloqueio retêm o fluxo todo, liberando as linhas no final da execução do algoritmo.

Segue abaixo tabela resumindo as principais características das transformações:



Um bom desempenho no Data Flow está diretamente relacionado ao uso de transformações Sem Bloqueio, além de evitar ao máximo as transformações de Semi Bloqueio e com Bloqueio. Segue abaixo a relação das tarefas:



Até o próximo post onde irei retomar a série dos novos tipos de dados.
Landry.

Novos tipos de dados do SQL 2008: HIERARCHYID (Parte 1)

Este artigo foi escrito utilizando o CTP de Novembro do SQL Server 2008.

Minha esposa acabou de entrar no Teatro com meus dois filhos (Luaninha e Erick) e fiquei no restaurante para trabalhar em um projeto que está começando a atrasar.... é um projeto de BI e a fase atual de documentação do ETL é a mais chata!!! Após a primeira garrafa de Bohemia (muiiiito gelada!), resolvi escrever um post, só para relaxar.

Vou escrever sobre o novo tipo de dados hierárquico em dois posts, no primeiro veremos como criar e manipular hierarquias no SQL Server 2005, no próximo veremos as novidades no SQL Server 2008, OK?

Hierarquia no SQL Server 2005
Vamos utilizar como exemplo a hierarquia da figura abaixo:



A abordagem tradicional para lidar com hierarquia utiliza auto-relacionamento, veja o script abaixo que cria no SQL Server 2005 a hierarquia da figura acima:

USE tempdb
go

CREATE TABLE Funcionario(
FuncionarioID int not null primary key,
Nome varchar(40) not null,
Cargo varchar(20) not null,
ChefeID int null)
go

INSERT Funcionario VALUES (1,'Jose','Presidente',null)
INSERT Funcionario VALUES (2,'Maria','Diretor',1)
INSERT Funcionario VALUES (3,'Pedro','Diretor',1)
INSERT Funcionario VALUES (4,'Ana','Gerente',2)
INSERT Funcionario VALUES (5,'Lucia','Gerente',2)
INSERT Funcionario VALUES (6,'Ronaldo','Gerente',3)
INSERT Funcionario VALUES (7,'Marcio','Coordenador',5)
go



Algumas operações comuns em hierarquia podem ser bem complexas no SQL Server 2005, como retornar todos os subordinados de um funcionário. Na consulta abaixo vamos retornar todos os subordinados da Diretora Maria, utilizando CTE com recursividade:

WITH Organograma (FuncionarioID, Nome, Cargo, ChefeID)
AS (
-- Cria a ancora para estabelecer ponto inicial
SELECT FuncionarioID, Nome, Cargo, ChefeID FROM Funcionario
WHERE Nome = 'Maria'
UNION ALL
-- Cria recursividade
SELECT f.FuncionarioID, f.Nome, f.Cargo, f.ChefeID
FROM Funcionario f JOIN Organograma o
ON f.ChefeID = o.FuncionarioID)

SELECT * FROM Organograma
go



Para incluir um novo funcionário Gerente no lugar de Ana, deslocando-a para Coordenadora, será necessário fazer vários UPDATEs nos seus subordinados.

No próximo post veremos como ficou mais fácil manipular hierarquias no SQL Server 2008, Até lá.
Landry.

Novos tipos de dados do SQL 2008: DATETIMEOFFSET

Este artigo foi escrito utilizando o CTP de Novembro do SQL Server 2008.

Nos dois últimos posts (http://sqlserver-brasil.blogspot.com/2008/04/novos-tipos-de-dados-do-sql-2008-date-e.html e http://sqlserver-brasil.blogspot.com/2008/04/novos-tipos-de-dados-do-sql-2008.html), escrevi sobre três tipos de dados novos para Data e Hora (DATE, TIME e DATETIME2). Neste post irei completar a série destinada aos novos tipos de dado Date e Time, escrevendo sobre suporte a Time Zone com o novo DATETIMEOFFSET.

DATETIMEOFFSET
Este tipo de dados é similar ao DATETIME2 onde é informada a precisão da hora, com acréscimo do Time Zone no intervalo de -14:00 a +14:00. Veja o exemplo abaixo:

DECLARE @dt DATETIMEOFFSET(0)
SET @dt = '20080415 22:00:00 -3:00' -- Brasilia

DECLARE @dt1 DATETIMEOFFSET(0)
SET @dt1 = '20080415 22:00:00 +9:00' -- Tokio

SELECT DATEDIFF(hh,@dt,@Dt1) 'Diferença Fuso Brasilia e Tokio'

-- Resultado abaixo:

Diferença Fuso Brasilia e Tokio
-------------------------------------------
-12

Vamos comparar agora todos os novos tipos de dados Data e Hora com os antigos DATETIME e SAMLLDATETIME:

SELECT CAST('20080120 20:30:05.1234567 +5:0' as DATE)
-- Resultado DATE: 2008-01-20

SELECT CAST('20080120 20:30:05.1234567 +5:0' as TIME(7))
-- Resultado TIME: 20:30:05.1234567

SELECT CAST('20080120 20:30:05.123' as SMALLDATETIME)
-- Resultado SMALLDATETIME: 2008-01-20 20:30:00.000

SELECT CAST('20080120 20:30:05.123' as DATETIME)
-- Resultado DATETIME: 2008-01-20 20:30:05.123

SELECT CAST('20080120 20:30:05.1234567 +5:0' as DATETIME2(7))
-- Resultado DATETIME2: 2008-01-20 20:30:05.1234567

SELECT CAST('20080120 20:30:05.1234567 +5:0' as DATETIMEOFFSET(7))
-- Resultado DATETIMEOFFSET: 2008-01-20 20:30:05.1234567 +05:00

No próximo post vou mostrar o tipo de dado hierarquico, Até lá.
Landry.

Novos tipos de dados do SQL 2008: DATETIME2

Este artigo foi escrito utilizando o CTP de Novembro do SQL Server 2008.

No último post, o primeiro da série que irá tratar dos novos tipos de dados no SQL Server 2008, falei sobre a tão esperada separação da data e hora nos novos tipos de dados DATA e TIME (http://sqlserver-brasil.blogspot.com/2008/04/novos-tipos-de-dados-do-sql-2008-date-e.html). Neste post vou apresentar uma extensão do tipo de dado DATETIME: DATETIME2.

DATETIME2
O tipo de dado DATETIME2 proporciona um melhor controle na precisão da HORA, além de aumentar o intervalo de datas aceito. Veja nas tabelas a seguir a comparação entre DATETIME2 e DATETIME.

Como exemplo execute o script abaixo:

DECLARE @DataHora3 DATETIME2(3)
SET @DataHora3 = '2004-02-27 16:14:00.1234567'
SELECT [DATETIME2(3)] = @DataHora3

DECLARE @DataHora7 DATETIME2(7)
SET @DataHora7 = '2004-02-27 16:14:00.1234567'
SELECT [DATETIME2(7)] = @DataHora7

-- Resultado abaixo:

DATETIME2(3)
-----------------------
2004-02-27 16:14:00.123

DATETIME2(7)
---------------------------
2004-02-27 16:14:00.1234567

No próximo post vou mostrar o tipo de dado TATETIMEOFFSET.
Até lá,

Landry.

Novos tipos de dados do SQL 2008: Date e Time

Até a versão 2005 o SQL Server disponibilizava apenas dois tipos de dados para data e hora (DATETIME e SMALLDATETIME), armazenando a data sempre junto da hora. Armazenar a data e hora em conjunto gera algumas dificuldades quando a necessidade da aplicação é de apenas manipular a data. Vamos explorar algumas situações que geram dificuldade ao manipular a data junto da hora e depois veremos as novidades no SQL Server 2008.

O script abaixo criar uma tabela de Vendas contendo uma coluna DATETIME e depois inclui algumas linhas.

USE tempdb
go

CREATE TABLE Vendas (
IDVenda int not null,
DataVenda datetime not null,
Cliente char(4000) not null,
TotalVenda decimal(10,2))

-- Inclui duas linhas para utilizar no filtro WHERE das consultas
INSERT Vendas VALUES
(3,'20070303 12:00:00.000','Ana',320.00)

INSERT Vendas VALUES
(3,'20070303 00:00:00.000','Pedro',120.00)

-- Inclui 10.000 linhas
DECLARE @i int
SET @i = 1
WHILE @i <= 10000 BEGIN

INSERT Vendas VALUES
(@i,getdate()-@i,'Cliente ' + ltrim(str(@i)),10.00 + @i)

SET @i = @i + 1
END
go

CREATE INDEX ix_Vendas_DataVenda
ON Vendas(DataVenda)
go

Para retornar todas as vendas de um determinado dia, não importando a hora, você terá certa dificuldade. Utilizando uma comparação simples de data, o SQL Server retornará apenas as vendas de meia noite (hora padrão), como podemos observar na consulta abaixo:

SELECT * FROM Vendas WHERE DataVenda = '20070303'

Uma opção para retornar todas as vendas de um dia é utilizar a função CONVERT, extraindo apenas a porção data da coluna DataVenda e comparando com o dia. O problema desta solução é utilizar função em coluna na cláusula WHERE, inviabilizando o uso de índice (Index Seek) gerando problema de desempenho. A solução é entrar com um intervalo de datas, evitando o uso de função em coluna. Para comparar o volume de I/O vamos utilizar SET STATISTICS IO ON e o plano de execução gráfico.

SET STATISTICS IO ON

SELECT * FROM Vendas
WHERE convert(char(8),DataVenda,112) = '20070303'
-- Table 'Vendas'. Scan count 1, logical reads 29
-- Plano de Execução: Index Scan

SELECT * FROM Vendas
WHERE DataVenda >= '20070303' and DataVenda < '20070304'
-- Table 'Vendas'. Scan count 1, logical reads 5
-- Plano de Execução: Index Seek

O SQL Server 2008, finalmente, disponibiliza tipos de dados onde data e hora ficam separados, são eles DATE e TIME.

Vamos alterar a estrutura da tabela Vendas, criando uma nova coluna HoraVenda do tipo de dado TIME e depois iremos alterar a coluna DataVenda para o tipo de dados DATE.

DROP INDEX Vendas.ix_Vendas_DataVenda

-- Cria a coluna HoraVenda do tipo de dados TIME e
-- preenche com as horas existentes na coluna DataVenda
ALTER TABLE Vendas ADD HoraVenda time null
go
UPDATE Vendas SET HoraVenda = DataVenda

-- Altera a coluna DataVenda para o tipo de dados DATE
ALTER TABLE Vendas alter column DataVenda date not null

CREATE INDEX ix_Vendas_DataVenda
ON Vendas(DataVenda)

Agora ficou mais fácil retornar as vendas de um determinado dia utilizando comparação simples, veja as estatísticas da consulta abaixo.

SELECT * FROM Vendas WHERE DataVenda = '20070303'
-- Table 'Vendas'. Scan count 1, logical reads 5
-- Plano de Execução: Index Seek

Até o próximo post.
Landry

Novos tipos de dados do SQL Server 2008

Vou iniciar uma seqüência de artigos sobre os novos tipos de dados no SQL Server 2008, segue abaixo uma breve descrição:

- DATE: armazena somente a data.
- TIME: armazena somente a hora.
- DATETIMEOFFSET: armazena data e hora obedecendo timezone.
- DATETIME2: similar ao DATETIME, porém com precisão variável.
- GEOMETRY: dados espaciais utilizando representação plana da Terra ("Flat Earth").
- GEOGRAPHY: dados espaciais utilizando representação redonda da Terra ("round earth").
- HIERARCHYID: representa hierarquias.

Os primeiros quatro tipos de dados (DATE, TIME, DATETIMEOFFSET, DATETIME2) são tipos regulares do SQL Server, já os três últimos foram desenvolvidos como UDT (User Defined Type) com .NET.

No próximo post vou falar dos dois primeiros tipos de dados: DATE e TIME.
Até lá, Landry.

Reduzindo o espaço ocupado em um Banco de Dados utilizando Vardecimal no SQL Server 2005 SP2

Neste artigo irei mostrar uma novidade no armazenamento do tipo de dados DECIMAL disponível a partir do Service Pack 2 do SQL Server 2005. Este novo recurso pode reduzir o tamanho da linha com consequente redução na ocupação do banco de dados, proporcionando melhor desempenho. Porém a adoção deste novo recurso deverá ser considerado com muito cuidado como veremos neste artigo.

Durante muito tempo o recurso de armazenamento variável ficou restrito aos tipos de dados string (varchar, nvarchar, text e ntext) e binário (varbinary e image), restando aos tipos de dados numéricos o armazenamento em tamanho fixo. O grande benefício do armazenamento variável é a redução do tamanho total da linha proporcionando uma melhor ocupação da página de dados (Data Page 8Kb), com conseqüente redução de I/O e melhora no desempenho.
Se uma coluna NomeCliente for do tipo de dado Varchar(100) o SQL Server só irá ocupar um byte por caractere incluído pelo usuário mais dois bytes do ponteiro que irá indicar o término da coluna dentro da linha. Por exemplo, se o usuário incluir “Maria Lucia” na coluna acima o SQL Server irá alocar 11 bytes referentes a cada caractere do nome e 2 bytes para o ponteiro, somando 13 bytes no total!

Já uma coluna decimal é armazenada na área da linha destinada a colunas de tamanho fixo, ocupando espaço de acordo com a tabela abaixo:

Observando uma coluna Decimal é muito comum termos uma precisão muito maior do que utilizamos na maior parte dos dados, sendo claro o benefício do armazenamento variável na redução do espaço ocupado e conseqüente melhora no desempenho.
Para comprovar a redução do espaço ocupado vamos criar um banco de dados contendo uma tabela com uma coluna Decimal, utilize o script abaixo.

Create database DBvardecimal
alter database DBvardecimal set recovery simple
go
use DBvardecimal
go

Create table TBdecimal (

col1 int identity(1,1),col2 char(500),col3 decimal(22,6))

declare @cont int,@val char(500)
set @cont = 1
while @cont <= 100000 begin

set @val = 'Linha - ' + convert(varchar(20),@cont)

insert TBdecimal (col2,col3) values (@val,12.2)

set @cont = @cont + 1

end

go

Foram incluídas 100.000 linhas na tabela, utilize o script abaixo para retornar o espaço ocupado pela tabela após a inclusão das linhas.

sp_spaceused 'TBdecimal'
-- Linhas: 100000
-- Reservado: 61640 KB
-- Dados: 61600 KB

As 100.000 linhas da tabela estão ocupando 61.600 KB em 7.700 páginas de dados (Data Pages 8KB). Agora vamos habilitar o armazenamento variável para o tipo de dado Decimal utilizando o script abaixo.

EXEC sp_db_vardecimal_storage_format 'DBvardecimal', 'ON'
EXEC sp_tableoption 'TBdecimal', 'vardecimal storage format', 1

O primeiro EXEC habilita o armazenamento Vardecimal no Banco de Dados DBvardecimal utilizando a Stored Procedure sp_db_vardecimal_storage_format e o segundo habilita o armazenamento Vardecimal na tabela TBdecimal. Se a Stored Procedure sp_db_vardecimal_storage_format for executada sem parâmetro ela retorna a lista dos Bancos de Dados na Instância indicando indicando se o armazenamento Vardecimal está habilitado ou não.

Basta rodar novamente a Stored Procedure sp_spaceused e comparar os resultados, veja a diferença na tabela abaixo.

Para determinar se a tabela foi habilitada para o armazenamento Vardecimal utilize a consulta abaixo.

SELECT name, object_id, type_desc FROM sys.objects
WHERE OBJECTPROPERTY(object_id,'TableHasVarDecimalStorageFormat') = 1

A estrutura da linha foi alterada para comportar o tamanho variável no tipo de dados Decimal, por isso o Relational Engine das edições anteriores ao Service Pack 2 não conseguem manipular Bancos de Dados com Vardecimal habilitado.

Se você tentar fazer um Attach pelo Management Studio de um Banco de Dados com Vardecimal habilitado em um SQL Server 2005 SP1 irá receber a seguinte mensagem de erro: “An error occurred when attaching the database(s). Click the hyperlink in the Message column for details.

No log da Instância irá aparecer a mensagem: “The database ‘DBvardecimal’ cannot be opened because it is version 612. This server supports version 611 and earlier. A downgrade path is not supported”.

Já no Restore a mensagem de erro será: “The database was backed up on a Server running version 9.00.3042. That version is incompatible with this server, which is running version 9.00.2047…”.

Muito cuidado! Uma vez habilitado o armazenamento Vardecimal não poderemos mais retornar ao Service Pack 1, por isso testem bastante as aplicações antes de adotar o armazenamento Vardecimal.

Até a próximo post,

Landry.