Fazendo leitura de Campo LOB – Oracle

Segue:

select dbms_lob.substr( campo_clob, 4000, 1 ) from tabela;

 

[]s

Introdução ao Business Intelligence:Arquitetura de Data WareHouse (DW) e Modelagem Dimensional

Construindo um Data WareHouse: Modelagem Dimensional

Tabela Fatos

Em resumo, são armazenados medidas, métricas, acontecimentos ou valores

Tabela Fatos – 3 tipos de medidas

· aditivas: são numéricas e são somadas com relação às dimensões existentes, por exemplo: quantidade, valor total dos itens.

· semi-aditivas: são numéricas, mas não podem ser somadas com relação a todas as dimensões, por exemplo: o estoque é totalizado ao longo da produto, porém ao longo das dimensões loja e data não existe o menor sentido está totalização.

· não-aditivas: dados não numéricos, por exemplo: carro1, carro2, carro3.

Tabela Fatos – Tipos de tabelas

Transaction Fact Table

Tipo de tabela de fatos em que a granularidade é uma linha para o menor nível de detalhe capturado por uma transação. Um registro numa TFT está presente somente se um evento de transação ocorre

Periodic Snapshot Fact Table

Um tipo de tabela de fatos que representa desempenho de negócio ao fim de cada período de tempo regular previsível. Snapshots diários e mensais são comuns.

Accumulating Snapshot Fact Table

Tipo de tabela de fato com datas múltiplas representando os principais pontos de controle de um processo ou pipeline relativamente de curta duração. Um

registro é inserido numa tabela de fatos instantâneos acumulados apenas uma vez,

quando o item que representa é inicialmente criado.

Tipos de Modelos Dimensionais

- O Modelo Estrela (Star Schema)

No modelo estrela todas as tabelas relacionam-se diretamente com a tabela de fatos, sendo assim as tabelas dimensionais devem conter todas as descrições que são necessária para definir uma classe, exemplo:

Este modelo é chamado de estrela porque a tabela de fatos fica ao centro cercada das tabelas dimensionais com o formato parecido de uma estrela. Mas o ponto forte a fixar é que as dimensões não são normalizadas.

- O Modelo Floco de Neve (Snow Flake)

No modelo Floco as tabelas dimensionais relacionam-se com a tabela de fatos, mas algumas dimensões relacionam-se apenas entre elas visando diminuir o espaço ocupado por estas tabelas. Exemplo:

Granularidade

Nível de detalhe da informação

Surrogate Key:

Número inteiro seqüencial. No caso das Datas, pode ser usado ou não. Em datas pode-se usar, por exemplo, o tipo date ou um numérico representando a data como YYYYMMDD, por exemplo. Utilizado para unir as dimensões às tabelas de Fatos.

Business Key

Pode ter vários atributos (chave composta) identifica o registro do sistema de origem e não se relaciona com a fato

Tabela Dimensão

descrições, características, localidade, detalhamento.

Tabela Dimensão – tipos de tabelas

• Simples – Apenas descritiva
• Hierárquica – Os atributos podem compor uma hierarquia

Slow-change dimension

Dificilmente sofre alterações no decorrer do tempo, exemplo estado civil

Junk Dimension

Uma dimensão abstrata com a decodificação de um grupo de flags e indicadores de baixa cardinalidade, portanto removendo os flags da tabela de fatos.

Degenerated Dimension

Uma chave de dimensão, como o número de uma transação, número de fatura, de tíquete, que não tenha nenhum atributo, portanto não se junta com uma tabela de dimensão.

Bridge Table

Uma tabela com uma chave multiparte capturando um relacionamento muitos-para muitos. Serve como uma ponte entre a tabela de fatos e a tabela de dimensão

de forma a permitir dimensões multivaloradas ou hierarquias

Centipede Fact Table

Uma tabela de fatos com muitas dimensões (mais do que 20) levando a uma esquema que lembra uma centopéia. Centopéias tipicamente resultam

quando projetistas tentam representar relacionamentos hierárquicos com uma

proliferação de dimensões separadas ao invés de aninhá-las numa única dimensão.

Quatro passos para criar um modelo dimensional

1. Selecionar o processo de negócio que será modelado
2. Definir grau de granularidade
3. Definir as dimensões para cada linha do fato
4. Identificar os fatos numéricos para cada linha do fato

[]s

Retornando o valor do IDENTITY – Sql Server

O Identity funciona como a Sequence no Oracle, uma vez indicado para a coluna de uma tabela que ela é identity ao inserir registros nessa tabela automaticamente iniciara o funcionamento da sequence.

Vamos aos exemplos:

Primeiro criaremos a tabela TbNotaFiscal onde o campo de código da nota fiscal será identity:

Create table TbNotaFiscal (
CdNotaFiscal int not null identity,
VrTotal numeric(10,4) null)

go

Agora vamos incluir uma nota fiscal:

insert into TbNotaFiscal  (VrTotal) values (10.20)
go

1 record(s) affected

Perceba que no insert acima não foi passado o código da Nota Fiscal, o mesmo foi gerado automaticamente:

Select * from TbNotaFiscal

CdNotaFiscal     VrTotal   
-----------------         ----------
1                         10,2    

Agora como podemos indentificar a posição atual da sequence ou último valor corrente. No Oracle podemos facilmente identicar através de consulta na sessão( select Nome_Sequence.currval from dual) mas e no Sql Server?

Temos 3 maneiras de indentificar qual foi o último identity adicionado na tabela, vamos exemplificar:

@@IDENTITY

Sua função é retornar o ultimo identity inserido em qualquer tabela do bloco em execução.

insert into TbNotaFiscal values (50)
go
Select @@identity
go
select * from TbNotaFiscal
go

1 record(s) affected

column1   
----------
2          Após ter inserido o registro mostra qual o último identity

1 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 16/ms]

CdNotaFiscal     VrTotal   
---------------         ----------
1                     10,2      
2                     50        

2 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]

Ainda no exemplo @@Identity, e se a tabela TbNotaFiscal tivesse uma trigger que insere em uma tabela de Log o nome do usuário que criou essa Nota Fiscal. Se essa tabela de Log também possuir um campo indentity, qual seria o valor retornado para @@Identity? Vamos lá.

Primeiro criaremos a tabela de Log:

Create Table TbNotaFiscalLog (
CdNotaFiscal int not null identity,
VrTotal numeric(10,4) null,
NmUsuario varchar(100) null)

Agora vamos criar a Trigger:

CREATE TRIGGER trg_NotaFiscalLog
ON TbNotaFiscal
AFTER INSERT
AS
    INSERT INTO TbNotaFiscalLog (VrTotal, NmUsuario)
    SELECT VrTotal, USER_NAME()
    FROM INSERTED

Vamos agora inserir um registro na tabela de Nota Fiscal e ver qual o valor retornado em @@Identity:

insert into TbNotaFiscal values (100)
go
Select @@identity
go
Select * from TbNotaFiscal
go

1 record(s) affected

1 record(s) affected

column1   
----------
1          Veja que o valor retornado foi o da tabela TbNotaFiscalLog -  executado pela trigger

1 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]

CdNotaFiscal     VrTotal   
---------------          ----------
1                      10,2      
2                      50        
3                     100       

3 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]

 

SCOPE_IDENTITY()

Retorna o último identity do bloco atual que esta em evidência, ou seja, utilizando nosso mesmo exemplo ao inserir um registro na tabela TbNotaFiscal não seria mais apresentado o identity gerado no insert da trigger e sim do bloco atual e execução. Exemplos:

insert into TbNotaFiscal values (200)
go
Select scope_identity()
go
Select * from TbNotaFiscal
go

1 record(s) affected

1 record(s) affected

column1   
----------
4         Veja que agora o valor apresentado foi o da tabela TbNotaFiscal e não mais o gerado na trigger pela tabela de Log

1 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]

CdNotaFiscal     VrTotal   
---------------           ----------
1                       10,2      
2                       50        
3                      100       
4                      200       

4 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]

 

IDENT_CURRENT()

Permite passar como parametro o nome da tabela retornando o último valor de indentity para a ela. Exemplo:

insert into TbNotaFiscal values (300)
go
Select ident_current('TbNotaFiscal')
go
select ident_current('TbNotaFiscalLog')
go
Select * from TbNotaFiscal
go
select * from TbNotaFiscalLog
go

1 record(s) affected

1 record(s) affected

column1   
----------
5          Identity da tabela TbNotaFiscal

1 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]

column1   
----------
3          Identity da tabela TbNotaFiscalLog

1 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]

CdNotaFiscal     VrTotal   
---------------          ----------
1                      10,2      
2                      50        
3                     100       
4                     200       
5                     300       

5 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]

CdNotaFiscal     VrTotal     NmUsuario   
---------------           ----------      ------------
1                       100           TESTE       
2                       200           TESTE   
3                       300           TESTE    

3 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]

É isso ai….

[]s

Diferença entre NOLOCK e WITH (NOLOCK)

 

A hint “nolock” utilizadas em consulta, permite que seja realizado a leitura suja das informações, ou seja, permite a leitura de dados de uma transação que ainda não foi efetuado Commit/Rollback.

O ‘WITH’ logo depois o nome da tabela é recomendada, mas opcional:

CORRETO

SELECT * FROM TBNOTAFISCAL WITH (NOLOCK)   

CORRETO

SELECT * FROM TBNOTAFISCAL (NOLOCK)  

A partir do SQL Server 2005, a sintaxe do NOLOCK passou a exigir o uso do ‘WITH’ quando se é utilizado multiplos hins na consulta, no exemplo a seguir é utilizado o hint NOLOCK junto com um hint que força a utilização de um indice.

ERRADO

SELECT * FROM TBNOTAFISCAL (INDEX=INDNF, NOLOCK) 

Incorrect syntax near 'INDEX'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

CORRETO

SELECT * FROM TBNOTAFISCAL WITH (INDEX=INDNF, NOLOCK) 

ERRADO

SELECT * FROM TBNOTAFISCAL NOLOCK

No exemplo acimo não será utilizado o hint NOLOCK e sim criado um alias para a tabela

Segue um Video com exemplos de utilização do NOLOCK

Consulta retorna CPF formatado - Oracle

Segue consulta para retornar CPF no formato padrão:

select regexp_replace('29820131855', '^(\d{3})(\d{3})(\d{3})(\d{2})$', '\1.\2.\3-\4') CPF from dual

CPF

298.201.318-55

[]s

Retorna última string do texto - Oracle

 

select substr('Fabio Martinez',instr('Fabio Martinez',' ',-1)+1) ultima from dual;

ULTIMA

Martinez

      

[]s

Habilitando 1 sessão por usuário

Como fazer para que um usuário possa abrir apenas 1 sessão por vez?

É o seguinte se o usuário Scott conectar no banco, enquanto este usuário estiver conectado outra pessoa não poderá conectar com o usuário Scott,  ou seja, o usuário Scott (Ativo) só poderá ter 1 sessão aberta no banco de dados. Para isso devemos configurar o seguinte parametro:

 

ALTER SYSTEM SET SESSIONS_PER_USER = 1;

 

[]s

Introdução ao Business Intelligence:Arquitetura de Data WareHouse (DW) e Modelagem Dimensional

1. Projetos de BI – Gerenciamento e planejamento

· Entender os motivos da criação do BI

· Avaliar expectativas

· Definir estratégias de entrega

· Definir riscos

· Definir escopo

· Avaliar recursos – humanos, financeiros e tecnológicos

2. Projetos de BI – Definição de requisitos

· Definição de requisitos – levantamento de dados, processos, medidas, usuários chaves, validação de requisitos

· Processo de negócio – Entendimento e identificação dos processos de negócio para modelagem da estrutura dimensional

· Requisitos comerciais – São as medidas e dimensões padrões para a maioria dos negócios, exemplo – ICMS,IPI,cliente, cidade, produto,etc..

3. Projetos de BI – Banco de dados

· Definição do banco de dados – SQLServer, Oracle, Sybase, IQ

· Recurso com conhecimento no banco de dados adotado no Projeto

4. Projetos de BI – Hardware e Software

· Selecionar servidor com capacidade de resposta compatível com o desejado

· Escolha do Banco de dados que será utilizado no projeto

· Escolha de ferramenta para modelagem de dados

· Escolha de ferramenta ETL

· Ferramenta OLAP

· Ferramenta para Relatório

· Definição de nível de segurança da informação

· Scorecard

5. Projetos de BI – Arquitetura Desejada

· extrair / importar dados das fontes

· tratar qualidade dos dados

· organizá-los num repositório (DW)

· estruturá-los para acesso (DM)

· visualizar / trabalhar os dados, e

· prover e manter metadados

6. Projetos de BI – Arquitetura DW

a. Arquitetura Top Down

Usada quando a empresa cria um DW e depois parte para a segmentação, dividindo o DW em áreas menores gerando assim pequenos bancos orientados por assuntos aos departamentos.

b. Arquitetura Bottom-Up

É a situação inversa da top-down. A empresa prefere primeiro criar um DM para uma única área. A partir da visualização dos primeiros resultados parte para outra área e assim sucessivamente até resultar em um DW.

c. Arquitetura Barramento

Criação de banco de dados consistente para obter consistência, ou seja, estabelecer método incremental. Com barramento pode-se implementar data marts separados por diferentes grupos, basta desenvolver conjunto mestre de fatos e dimensões padronizados. A Implementação de data marts será à medida que forem construídos e serão “encaixados” no DW, fazendo com que equipes independentes possam trabalhar de forma paralela devido à padronização da arquitetura.

7. Projeto de BI – Arquitetura de Hardware

• Centralizada: Único servidor
• Distribuída: Vários servidores
• Servidor Remoto: Data Center
• Servidor Local: Rede local
• Cloud Computer

8. Projeto de BI – Recursos

Serão necessários recursos para desempenhar as seguintes atividades de acordo com a etapa do projeto:

• Iniciação – termo de abertura e cronograma
• Concepção – requisitos, regras de negócio, modelo conceitual, dicionário, origem de dados e gaps, expectativas de volumes e cronograma das próximas fases
• Construção – protótipo, modelos lógico e físico, regras de processo, estimativa de volumes e layout da interface
• Teste e Homologação – Base de dados para teste, mapeamento, interface e programas
• Encerramento – manual do sistema, treinamento, operação assistida, termo de encerramento e reunião de encerramento

 

 

[]s

Quem esta conectado no SqlServer?

Segue uma consulta que permite identificar IP, Programa executado, Host e Login das sessões do Bando de Dados

 

SELECT  c.client_net_address,
        e.program_name,
        e.host_name,
        e.login_name
FROM sys.dm_exec_sessions AS e
INNER JOIN sys.dm_exec_connections AS c ON e.session_id = c.session_id
ORDER BY c.client_net_address,  e.program_name;

 

[]s

Introdução ao Business Intelligence (BI) e Modelagem Dimensional

1. Hierarquia dos sistemas de Informação

2. Sistemas operacionais

Sistemas operacionais possuem estabilidade de dados volátil com atualizações constantes em tempo real sempre procurando mostrar a informação atual, onde o armazenamento desses dados são de acordo com o tempo previsto para o negócio. Exemplos, notas fiscais devem ser armazenadas durante cinco anos.

Sistemas operacionais devem estar preparados para estarem disponíveis em torno de 20 horas por dia 6 dias da semana sendo em alguns casos necessário disponibilidade de 24X7.

No que diz respeito à estrutura de banco de dados dos sistemas operacionais esta focado na agilidade e no processamento dos dados. Estão preparados para aceitar inclusão, alteração e exclusão de dados disponibilizando relatórios pré definidos com informações integras.

3. Sistemas Analíticos

Sistemas analíticos possuem estabilidade de dados estáveis com atualizações periódicas, ou seja, as atualizações são realizadas em um período pré definido de acordo com as necessidades do negócio não tendo alterações de dados somente inclusões. Dessa maneira, os fatos são apresentados quando ocorrem como se fosse uma foto do momento em que ocorreu.

O tempo de armazenamento dos dados dos sistemas analíticos são geralmente longos, para possibilitar uma análise de tendência, em média são armazenadas entre 5 a 10 anos tendo disponibilidade de serviço menor do que os sistemas operacionais, normalmente estão disponíveis em certos períodos do dia ou da semana (de acordo com o negócio).

Com relação à estrutura de banco de dados para sistemas analíticos, são projetados para gerar relatórios iterativos de natureza ad-hoc tendo como característica grande volume de dados redundantes (por não ser normalizado) com foco na qualidade das informações.

4. Sistemas Transacionais

Sistemas transacionais, também conhecidos como OLTP – Online Transactional Processing - são sistemas que se baseiam em transações. Alguns exemplos deste tipo de sistemas são:

· Sistemas Contábeis;

· Aplicações de Cadastro;

· Sistemas de Compra, Estoque, Inventário;

· ERPs, CRMs.

O processo inicial de informatização de qualquer empresa é baseado no desenvolvimento e na implantação de Sistemas transacionais (também chamados de operacionais). Estes sistemas atendem em geral à área administrativo\financeira, sistemas como folha de pagamento, contabilidade, controle de estoques, contas a pagar e a receber, faturamento, etc., são exemplos de Sistemas transacionais.

As principais funções e características dos sistemas transacionais são:

o Coletar os dados operacionais existentes nos documentos operacionais das organizações

o Ordenar os dados de modo a facilitar o acesso a eles;

o Permitir consultas que permitam retratar diferentes aspectos das operações;

o Gerar relatórios operacionais

o Fornecer informações para tomada de decisões

o A maioria das fontes de dados é interna e a saída é destinada principalmente a um público interno

o Os dados recebidos e produzidos são organizados e formatados de modo padrão.

o Modelo de bando de dados relacional normalizado

Em resumo, os sistemas transacionais se caracterizam pela alta taxa de atualização, grandes volumes de dados e acessos pontuais, ou seja, pesquisas cujo resultado seja de pequeno volume (até milhares de linhas, mas preferencialmente menos).

5. Definição de BI

O Conceito de A Inteligência Empresarial, ou Business Intelligence (BI), surgiu na década de 80 e tem com conceito as habilidades das organizações para acessar dados e explorar as informações normalmente contidas em um Data Warehouse/Data Mart.

Tem como objetivo auxiliar pessoas por meio do tratamento da base de dados existentes e o aprimoramento das informações para tomada de decisão, ou seja, em meio de uma enorme quantidade de informações que são “despejadas” nos sistemas diariamente, necessitamos de critérios para selecionarmos e organizarmos os dados que são mais interessantes para o nível gerencial, onde gerentes e diretores devem ter informações rápidas e relevantes.

6. Modelo de dados Dimensional

O modelo dimensional é utilizado na maioria das vezes em banco de dados para Data Warehouse que diferem do modelo de dados relacional. É uma forma de modelagem onde as informações podem ser representadas como um cubo, sendo possível fatiar este cubo e consultar cada dimensão para extrair detalhes mais aprofundados.

A modelagem é dividida em dimensões, que servem de perspectivas de análise em qualquer assunto da organização, ou seja, forma pela quais analistas de negócios, gerentes e executivos analisam as informações

Esse tipo de modelagem tem dois modelos MODELO ESTRELA (STAR SCHEMA) e MODELO FLOCO DE NEVE (SNOW FLAKE).

· Modelo Estrela: Mais simples de entender, nesse modelo todas as dimensões relacionam-se diretamente com a fato.

· Modelo Floco de Neve: Visa normalizar o banco com dimensões auxiliares.

7. Motivos que levam empresas ao desenvolvimento de BI

São vários os motivos que levam as empresas ao desenvolvimento de BI, uma delas é a dificuldade na geração de relatórios e necessidade de informações diferenciadas

8. Conceito de Data Warehouse

Pode ser traduzido como “depósito de dados”. Sua função principal é o armazenamento de informações de um banco de dados referente a uma ou mais atividades de uma empresa de forma consolidada, voltada à tomada de decisões. É como um agrupamento inteligente de dados de uma mesma fonte.

O Data Warehouse possibilita que os relatórios sejam exibidos dinamicamente de acordo com a necessidade, focando pontos estratégicos. Seu objetivo é trabalhar com uma grande quantidade de informação e principalmente dados históricos.

O que torna o Data Warehouse poderoso é que as informações que estão armazenadas em vários sistemas, planilhas e arquivos espalhados por todos os setores da empresa, são reunidos em um banco de dados dimensional.

Como desvantagem no Data Warehouse, temos seu custo, projetos muito longos, dificuldades para validação e dificuldade de escopo.

9. Arquitetura Top Down

Usada quando a empresa cria um DW e depois parte para a segmentação, dividindo o DW em áreas menores gerando assim pequenos bancos orientados por assuntos aos departamentos.

10. Arquitetura Bottom-Up

É a situação inversa da top-down. A empresa prefere primeiro criar um DM para uma única área. A partir da visualização dos primeiros resultados parte para outra área e assim sucessivamente até resultar em um DW.

11. Arquitetura Barramento

Criação de banco de dados consistente para obter consistência, ou seja, estabelecer método incremental. Com barramento pode-se implementar data marts separados por diferentes grupos, basta desenvolver conjunto mestre de fatos e dimensões padronizados. A Implementação de data marts será à medida que forem construídos e serão “encaixados” no DW, fazendo com que equipes independentes possam trabalhar de forma paralela devido à padronização da arquitetura.

12. Os quatro componentes de DW/DM segundo Kimball

· Sistema operacionais de origem – ERP, planilha Excel, arquivos texto, etc.

· Data staging área – Filtro e padronização dos dados, ou seja, é a área de extração e transformação dos dados que serão carregados no DW

· Área de apresentação dos dados – local onde os dados ficam disponíveis e organizados para apresentação ao usuário

· Ferramenta de acesso aos dados – data mining, OLAP, Scorecards, Dashboards

13. Projetos de BI – Gerenciamento e planejamento

· Entender os motivos da criação do BI

· Avaliar expectativas

· Definir estratégias de entrega

· Definir riscos

· Definir escopo

· Avaliar recursos – humanos, financeiros e tecnológicos

14. Projetos de BI – Definição de requisitos

· Definição de requisitos – levantamento de dados, processos, medidas, usuários chaves, validação de requisitos

· Processo de negócio – Entendimento e identificação dos processos de negócio para modelagem da estrutura dimensional

· Requisitos comerciais – São as medidas e dimensões padrões para a maioria dos negócios, exemplo – ICMS,IPI,cliente, cidade, produto,etc..

 

[]s