DATA WIREHOUSE

DATA WIREHOUSE

Conceitos.

O conceito de Data Wirehouse surgiu da necessidade de integrar dados corporativos espalhados em diferentes máquinas e sistemas operacionais, para torná-los acessíveis a todos os usuários dos níveis decisórios (NAVARRO, 1996). Entretanto, essa integração deve ser feita com uma seleção cuidadosa e otimizada dos dados já que a prioridade na utilização do ambiente do DW é o processamento de consultas e não o processamento de transações. A Figura 1 ilustra toda a estrutura interna que o ambiente de DW representa.

Figura 1: Estrutura interna do DW

Descrição.

“Um Data Warehouse é uma coleção de dados orientados a assuntos, integrados, variáveis com o tempo, não voláteis, para suporte ao processo gerencial de tomada de decisão.”(INMON, 1997, p.14)

Pode-se destacar as principais características de um Data Warehouse (que no português significa, literalmente armazém de dados) como a orientação a assunto, a padronização dos dados (integração), os dados não se perdem nos processos do banco de dados (volatilidade), armazenagem dos registros em uma série temporal, os dados são sumarizados (resumidos), a não necessidade de normalização e um conjunto de informações sobre os dados armazenados (metadados).

Tabela 1: Características de um Data Warehouse

Característica	Descrição
Orientação a assunto	Dados organizados de modo como o usuário se refere a ele
Integração	Inconsistências são removidas e informações conflitantes, isto é, os dados são “limpos”
Não volátil	Somente leitura de dados, dados não atualizado por usuários.
Séries Históricas	Os dados estão em séries históricas, não somente na situação corrente.
Resumidos	Os dados operacionais estão/são agregados.
Abrangência	Mantêm uma série temporal, considerando que mais dados serão armazenados.
Não normalizado	Dados podem ser redundantes.
Metadados	Dados sobre dados.

Arquitetura do Data Warehouse.

A arquitetura do Data Warehouse pode variar conforme o tipo de assunto abordado, isso ocorre devido as necessidades que variam de empresa para empresa.

Um exemplo de arquitetura de um DW pode ser visto na figura 1 com seus componentes descritos a seguir:

Figura 2: Arquitetura do projeto de DW (Figura adaptada de KIMBALL (1998).

Camada de banco de dados: Corresponde aos dados dos sistemas operacionais da organização, junto com dados provenientes de outras fontes externas que serão tratados e integrados para compor o DW. Pode ser, por exemplo, o banco de dados dos produtos de uma empresa ou dos fornecedores.

Camada de acesso aos dados: Esta camada é responsável pela ligação entre as 20 ferramentas de acesso à informação e os bancos de dados operacionais (comunica-se com diferentes SGBDs). Pode ser a representação dos dados fontes no ambiente de DW ou o ponto de comunicação entre as ferramentas de manipulação de dados com os dados do DW.

Camada de gerenciamento de replicação: Esta camada inclui todos os processos necessários para selecionar, editar, resumir, combinar e carregar o DW e as correspondentes informações de acesso a partir das bases operacionais. Exemplificando, seriam os módulos de integração dos dados, podendo ser um cálculo de média de um dado em uma tabela fonte para ser armazenado no DW.

Camada de dados intermediários: Gerencia o transporte de informações pelo ambiente de redes. É usada para isolar aplicações, operacionais ou informacionais, e entregá-las em locais e tempos determinados.

Camada do DW: O DW corresponde aos dados usados para fins ”informacionais” (analíticos). Em alguns casos, DW é simplesmente uma visão lógica ou virtual dos dados, podendo de fato não envolver o armazenamento destes dados. Como exemplo, poderia ser um DW que visa analisar o Clima de uma região, tendo como fonte de dados a média da temperatura das regiões estudadas (seguindo um hierarquia: bacia hidrográfica, sub-bacias, ponto de medição).

Camada de acesso à informação: É a camada com a qual os usuários finais interagem. Representa as ferramentas que o usuário utiliza para navegar pelos dados de DW. Podemos citar uma ferramenta, produzida pela Oracle, Oracle 9i Discoverer. Também envolve o hardware e software utilizado para obtenção de relatórios, planilhas, gráficos e outros.

Camada de gerenciamento de processos: Está envolvida com o controle das diversas tarefas a serem realizadas para construir e manter as informações do dicionário de dados e do DW. Contribui para manter o DW atualizado e consistente. É o gerente de todo o DW, um exemplo de aplicativo que executa essa tarefa é a ferramenta Oracle 9i Warehouse Builder.

Camada de metadados (dicionários de dados): Metadados são as informações sobre os dados mantidos pela organização. Para poder manter a funcionalidade de um ambiente de DW é necessário ter disponível uma grande variedade de metadados, desde

dados sobre as visões dos usuários até dados sobre os bancos de dados operacionais.

§ Arquitetura Genérica

A arquitetura genérica compreende a camada dos dados operacionais que serão acessados pela camada de acesso a dados. As camadas de gerenciamento de processos, transporte e data warehouse são responsáveis por distribuir os dados e estão no centro da arquitetura. A camada de acesso à informação possibilita a extração das informações do DW utilizando um conjunto de ferramentas.

Figura 3: Arquitetura genética DW.

§ Arquitetura de Duas Camadas

A arquitetura de duas camadas utiliza um servidor, juntamente com aplicações front end, que são ferramentas que realizam operações sobre os dados consultados e os transformam em informações úteis para os usuários, os componentes back end são ferramentas responsáveis pela extração, limpeza e cargas dos dados, mais conhecidas como ETL também são utilizadas neste tipo de arquitetura.

Figura 4: Arquitetura duas camadas DW.

§ Arquitetura de Três Camadas

A arquitetura de três camadas suporta vários usuários e serviços devido a sua flexibilidade, as informações ficam armazenadas em várias camadas. Na primeira camada estão as interfaces que trabalham com o usuário, onde geralmente são gráficas. Na segunda camada estão os servidores de banco de dados e aplicações e, por isso, têm a necessidade de ter um acesso eficiente e veloz aos dados compartilhados, e na última ficam armazenadas as fontes de dados. A arquitetura de três camadas é a mais utilizada pelos analistas.

Figura 5: Arquitetura três camadas DW.

Ferramentas de acesso.

As maneira em que os dados são extraídos e integrados com cada processo distinto do DW. As funções para a transformação dos dados são:

• Extração: retirada dos dados dos sistemas transacionais e armazenagem na área de Data Stage;

• Carga de dimensões processadas: realimentação do processo para garantir a representação correta dos dados em novo formato.

• Carga, Replicação e Recuperação: quando pronto, o dado é carregado no Data Mart correspondente e são criados (ou atualizados) índices para melhorar a performance das consulta.

• Alimentação: apresenta as visões do Data Mart de acordo com as necessidades dos usuários.

• Carga dos resultados dos modelos: serve para realimentar possíveis modificações no Data Mart, caso este não esteja adequado a aplicação que o utiliza.

Para as operações de consultas destaca-se:

a) cadastrar consulta: define consultas gerenciais baseadas na modelagem dimensional do cubo de decisão;

b) visualizar e configurar consultas: acesso e configuração sobre as consultas cadastradas.

Exemplos de Banco de dados para Data Warehouse.

· Adabas D - Software AG

· Advanced Pick - Pick Systems

· Broadbase Server - Broadbase Information Systems

· DB2 - IBM

· Fast-Count DBMS - MegaPlex Software

· HOPS - HOPS International

· Microsoft SQL Server - Microsoft

· MK Platform - Synergistics Services Group

· Model 204 - Computer Corp. of America

· NonStop SQL - Tandem

· Ngram Transform-DB - Triada

Entre outros.

Os principais passos para criar um modelo.

1. Desenvolver um modelo conceitual da atividade do negócio ou processo a ser suportado por um banco de dados;

2. Usar esse modelo de mais alto nível para criar o modelo lógico de dados. O modelo

lógico provê muito mais detalhes e é independente da implementação;

3. Transformar o modelo lógico em um modelo físico detalhado com cada implementação do banco de dados. O modelo físico é chamado de esquema.

Para os novos bancos de dados, o primeiro passo é definir a atividade do negócio ou o processo. Isso envolve ajustamento de dados (incluindo entrevistas com usuários potenciais) e um entendimento do processo do negócio a ser suportado.

Em um DW o modelo conceitual é baseado em fatos, dimensões e hierarquias. A informação em um DW é usada para pesquisa completa, sumarização e organização dos dados. Ou seja, estas informações consistem em uma base que dá suporte a análise e busca do conhecimento.

Características:

• A informação é vista em intervalos de tempo, preferencialmente em transações individuais;

• A informação é disposta numa visão global do negócio;

O objetivo da modelagem de dados é converter os conceitos da organização em uma forma que pode ser desenvolvida em uma estrutura de dados, refletindo o modelo da organização, de modo que a informação possa ser extraída por usuários do sistema.

Para as operações de montagem de projeto destacam-se os seguintes casos de uso:

a) cadastrar Data Warehouse: cria um novo projeto de DW baseado no modelo dimensional estrela;

b) cadastrar dimensão: grava as definições referentes a uma dimensão bem como seus atributos e chave primária;

c) cadastrar cubo: grava as definições referentes a um cubo de decisão bem como seus indicadores e dimensões relacionadas.

Exemplo de ferramentas.

A ferramenta para a construção de um DW conta com dois atores: o usuário de consultas e o administrador projetista. Neste trabalho, a ênfase principal é com as funções disponibilizadas ao administrador, uma vez que ao usuário caberá apenas a tarefa de efetuar as consultas.

As operações realizadas pelo administrador podem ser divididas nos seguintes processos: montagem do projeto de DW, consultas, metadados e recursos adicionais da ferramenta.

Outros recursos adicionais da ferramenta são:

a) exportar metadados: exporta todas as definições referentes a um projeto de DW em padrão XML,

b) importar metadados: importa para o sistema um novo projeto de DW gerado em XML;

c) visualizar agendamento: apresenta ao administrador todos os agendamentos de fontes do dia corrente que ainda estão em aberto para processamento;

d) visualizar log de mensagens: mostra as principais ocorrências dentro do sistema como informações de importação, erro e tratamento de exceções;

e) limpar Data Warehouse: processa limpeza de dados e do conteúdo dos projetos de DW do sistema;

f) cadastrar usuário: cria novos usuários para acesso ao sistema.

Exemplos de utilização.

Desenvolver um sistema de Data Warehouse não é uma tarefa simples, ela requer o entendimento de vários conceitos e domínio de tecnologias que devem ser conciliadas para trabalhar em conjunto. Sistemas de Apoio a Decisão (SAD), redes de computadores, servidores, Sistemas de Gerenciamento de Banco de Dados (SGBD) são exemplos de tecnologias que devem ser integradas.

Comandos e Conceitos do SQL

Comando e Conceitos do SQL

1) O que significa a sigla SQL? Qual a finalidade dessa linguagem? Como ela se divide? Quais são os comandos principais de cada divisão? Explique-os resumidamente.

R: A sigla SQL significa Structured Query Language, ou Linguagem de Consulta Estruturada. SQL apresenta uma série de comandos que permitem à definição dos dados (chamada DDL – Data Definition Language), que é composta, entre outros, pelos comandos CREATE, que é destinado a criação do Banco de Dados, das Tabelas que o compõe, além das relações existentes entre as tabelas. Ela é dividida em:

DML - Linguagem de Manipulação de Dados - Primeiro há os elementos da DML (Data Manipulation Language - Linguagem de Manipulação de Dados). A DML é um subconjunto da linguagem usada para inserir, atualizar e apagar dados.

• INSERT é usada para inserir um registro (formalmente uma tupla) a uma tabela existente.

• UPDATE para mudar os valores de dados em uma ou mais linhas da tabela existente.
• DELETE permite remover linhas existentes de uma tabela.

DDL - Linguagem de Definição de Dados - O segundo grupo é a DDL (Data Definition Language - Linguagem de Definição de Dados). Uma DDL permite ao utilizador definir tabelas novas e elementos associados. A maioria dos bancos de dados de SQL comerciais tem extensões proprietárias no DDL.

• CREATE cria um objeto (uma Tabela, por exemplo) dentro da base de dados.
• DROP apaga um objeto do banco de dados.

DCL - Linguagem de Controle de Dados - O terceiro grupo é o DCL (Data Control Language - Linguagem de Controle de Dados). DCL controla os aspectos de autorização de dados e licenças de usuários para controlar quem tem acesso para ver ou manipular dados dentro do banco de dados.

Duas palavras-chaves da DCL:

• GRANT - autoriza ao usuário executar ou setar operações.
• REVOKE - remove ou restringe a capacidade de um usuário de executar operações.

Outros comandos DCL:

• ALTER PASSWORD
• CREATE SYNONYM

DTL - Linguagem de Transação de Dados

• BEGIN WORK (ou START TRANSACTION, dependendo do dialeto SQL) pode ser usado para marcar o começo de uma transação de banco de dados que pode ser completada ou não.
• COMMIT envia todos os dados das mudanças permanentemente.
• ROLLBACK faz com que as mudanças nos dados existentes desde que o último COMMIT ou ROLLBACK sejam descartadas.

DQL - Linguagem de Consulta de Dados - Embora tenha apenas um comando, a DQL é a parte da SQL mais utilizada. O comando SELECT permite ao usuário especificar uma consulta ("query") como uma descrição do resultado desejado. Esse comando é composto de várias cláusulas e opções, possibilitando elaborar consultas das mais simples às mais elaboradas.

2) Quais são os tipos de dados que a linguagem SQL padrão (ANSI) suporta? Explique cada um resumidamente.

R: TINYINT: Valores numéricos inteiros variando de 0 até 256

SMALLINT: Valores numéricos inteiros variando de –32.768 até 32.767

INT: Valores numéricos inteiros variando de -2.147.483.648 até 2.147.483.647

BIGINT: Valores numéricos inteiros variando de –92.23.372.036.854.775.808 até 9.223.372.036.854.775.807

BIT: Somente pode assumir os valores 0 ou 1. Utilizado para armazenar valores lógicos.

DECIMAL(I,D) e NUMERIC(I,D): Armazenam valores numéricos inteiros com casas decimais utilizando precisão. I deve ser substituído pela quantidade de dígitos total do número e D deve ser substituído pela quantidade de dígitos da parte decimal (após a vírgula). DECIMAL e NUMERIC possuem a mesma funcionalidade, porém DECIMAL faz parte do padrão ANSI e NUMERIC é mantido por compatibilidade.

SMALLMONEY: Valores numéricos decimais variando de -214.748,3648 até 214.748,3647

MONEY: Valores numéricos decimais variando de -922.337.203.685.477,5808 até 922.337.203.685.477,5807

REAL: Valores numéricos aproximados com precisão de ponto flutuante, indo de -3.40E + 38 até 3.40E + 38

FLOAT: Valores numéricos aproximados com precisão de ponto flutuante, indo de -1.79E + 308 até 1.79E + 308

SMALLDATETIME: Armazena hora e data variando de 1 de janeiro de 1900 até 6 de junho de 2079. A precisão de hora é armazenada até os segundos.

DATETIME: Armazena hora e data variando de 1 de janeiro de 1753 até 31 de Dezembro de 9999. A precisão de hora é armazenada até os centésimos de segundos.

CHAR(N): Armazena N caracteres fixos (até 8.000) no formato não Unicode. Se a quantidade de caracteres armazenada no campo for menor que o tamanho total especificado em N, o resto do campo é preenchido com espaços em branco.

VARCHAR(N): Armazena N caracteres (até 8.000) no formato não Unicode. Se a quantidade de caracteres armazenada no campo for menor que o tamanho total especificado em N, o resto do campo não é preenchido.

TEXT: Armazena caracteres (até 2.147.483.647) no formato não Unicode. Se a quantidade de caracteres armazenada no campo for menor que 2.147.483.647, o resto do campo não é preenchido. Procure não utilizar este tipo de dado diretamente, pois existem funções específicas para trabalhar com este tipo de dado.

NCHAR(N): Armazenam N caracteres fixos (até 4.000) no formato Unicode. Se a quantidade de caracteres armazenada no campo for menor que o tamanho total especificado em N, o resto do campo é preenchido com espaços em branco.

NVARCHAR(N): Armazenam N caracteres (até 4.000) no formato Unicode. Se a quantidade de caracteres armazenada no campo for menor que o tamanho total especificado em N, o resto do campo não é preenchido.

NTEXT: Armazena caracteres (até 1.073.741.823) no formato Unicode. Se a quantidade de caracteres armazenada no campo for menor que 1.073.741.823, o resto do campo não é preenchido. Procure não utilizar este tipo de dado diretamente, pois existem funções específicas para trabalhar com este tipo de dado.

3) Explique o comando SELECT. Sua sintaxe, características básicas, Cláusula Where, operadores, conectores, subconsultas, funções de agregação, cláusula order by, group by, having e compute by.

R: SELECT é usada para selecionar um subconjunto de tuplas de uma relação as quais devem satisfazer uma condição de seleção.

SELECT {A₁, A₂,..., A_N | * } FROM R₁, R₂, ...,R_N WHERE P

EQUIVALE A

ΠA₁, A₂,..., A_N (Σ_P (R₁ X R₂ X...X R_N))

ONDE CADA A_I REPRESENTA UM ATRIBUTO E CADA R_I É UMA RELAÇÃO.

Caso a cláusula where seja omitida, o predicado P é verdadeiro. A lista A1, A2,..., An de atributos pode ser substituída por um asterisco (*) para selecionar todos os atributos de todas as relações presentes na cláusula from.

4) O que é uma visão (view) em SQL? Explique suas propriedades, utilidades e os comandos para a sua manipulação.

R: Uma visão(view) é qualquer relação que não faz parte do modelo lógico do banco de dados, mas é visível ao usuário. Sua propriedade da visão é composta com linhas e colunas como se fosse uma tabela real, a utilidade esta ligada ao aumento de segurança obtendo assim uma visão limitada e controlada dos dados que podem ser obtidos e a performance que utilizada consulta otimizada.

5) Os que são procedimentos armazenados (stored procedure)? Para que servem, o que eles permite fazer e quais comandos podemos utilizar no seu corpo e para criá-los?

R: São procedimentos executados no servidor, ele serve como dispensamento de banco de dados eles permitem encapsular tarefas repetitivas aceita parâmetros de entrada e retorno um valor de status o que acaba reduzindo o trafego na rede e melhorando a performance

Comandos utilizados:

Create procedure busca
@nomedebusca varchar(50)
as
select nome1,nome2
from nome_da tabela
where nome = @nomebusca

6) Os que são gatilhos (TRIGGERS)? Para que servem, quais as vantagens na sua utilização, como funcionam e como criá-los?

R: Os Gatilhos ou TRIGGERS são maneiras para executar um programa quando o evento associado ocorrer, ela mantem a consistência dos dados ou então propaga as alterações de dados de uma tabela para outras, sua vantagem é que quando ocorrer alguma alteração na tabela ele é disparado e deixa gravadas todas as informações do usuário que fez a alteração, eles são criados e funcionam da seguinte forma:

CREATE TRIGGER nome_do_gatilho ON dono.nome_da_tabela
FOR INSERT (ou UPDATE OU DELETE)
As
Código da execução