Olá pessoal!
Após a criação das fatos e dimensões do meu data warehouse, estive pensando em como poderia gerenciar as primary e foreign keys do meu banco e não foi tão difícil ou complexo como pensei.
A única dificuldade aparente seria garantir que as chaves foram criadas de forma consistente, uma vez que estamos realizando a operação fora dos ‘domínios’ do banco e mais, o comando SQL no PDI nem sempre apresenta a consistência necessária e garantir essa operação é fundamental para o bom desenvolvimento de queries e consistência do warehouse.
Porém, por incrível que pareça, a transformação SQLScript tem se mostrado consistente e então, com esse cenário, mostro como estou gerenciando a criação das minhas PK’s e FK’s no data warehouse.
CRIAÇÃO DOS JOBS
Os jobs são até bem simples de se criar, o que devemos nos atentar é quanto a ordem de execução e aos nomes das chaves e constraints. Além disso, é muito importante que se preste atenção às colunas que serão ‘ligadas’ para não relacionar de forma incorreta a sua tabela fato com as dimensões.
Abaixo, este é o job que deleta as chaves. Perceba que são comandos em SQL separados, cada um para uma fato.
Acredito que colocando em um único script também funcione, mas, seria mais difícil de gerenciar; a intenção aqui não é essa.
E aqui, a mesma ideia, com a diferença que estamos criando as chaves.
É de extrema importância que se saiba das ordens de criação e deleção das chaves. Isso porque existe um mecanismo no banco que garante a restrição de integridade e por isso, você não pode começar deletando as chaves pela dimensão, assim como não pode começar criando as chaves pela fato.
Note que no meu job de delete o script que fica responsável por deletar as primary keys da dimensão será executado ao final, e o contrário para criação; na segunda imagem.
CRIANDO O SCRIPT – PRIMARY KEYS E FOREIGN KEYS
Para criar as PK’s e FK’s basta alterar a tabela e criar as constraints para a coluna que se deseja declarar como chave primária e estrangeira (na tabela de destino).
Este é o script para criação das primaries keys.
E aqui, para criação das foreign keys na tabela fato.
Cada constraint precisa de um nome único, É MUITO IMPORTANTE TER ISSO EM MENTE NA CRIAÇÃO DAS CHAVES.
Para deletar as chaves, não há muito o que explicar, o comando é basicamente o mesmo. Só não esqueça de deletar primeiro na tabela fato para depois deletar nas dimensões.
Para não ficar muito grande, cortei parte do comando neste exemplo. Aqui, estou deletando as chaves primárias na tabela dimensão, como podem ver na parte do comando de ALTER TABLE.
ORDEM DE EXECUÇÃO
Quanto à ordem de execução no seu ETL é importante destacar que a primeira criação terá de ser feita a mão.
Como as cargas são mais rápidas quando não temos PK’s e FK’s nas tabelas, no meu ETL coloquei os scripts de deleção das chaves antes de iniciar qualquer carga, seja na stage, seja no banco.
Mas para essa automatização não se esqueça de executar primeiro o job de criação das chaves e após, tente executar de forma automatizada, como neste exemplo.
EXTRA – CONFERINDO AS KEYS DAS TABELAS FATO E DIMENSÃO
Pensando numa forma de garantir a conferência das chaves criadas sem precisar ficar acessando o banco para tal, fiz uma pesquisa sobre como criar uma query simples que consulta no banco de dados as PK’s e FK’s.
Claro, a query é bem básica e o intuito aqui é apenas plantar a ideia da possibilidade de criar a opção.
Deste modo, criei uma query que realiza uma consulta nas views de sistema do SQL Server, coloquei em um job e criei uma transformação que executa e salva este resultado em um arquivo CSV.
Essa é a estrutura do meu job com a transformação e abaixo, a estrutura para gravar no csv. Que também é bem simples.
Aqui está o exemplo do resultado da query resumido. Nesta query, ela informa o tipo de constraint, a coluna que pertence e onde ela é FK e PK no banco.
Como disse, essa query é provisória e futuramente, pretendo aprimorar e retornar com uma query mais precisa.
Esta foi a estrutura do ETL após a implementação do job de consulta das constraints.
CONCLUSÃO
Este foi mais um post que trata da criação do portfólio e de como automatizar no ETL a criação das chaves primárias e estrangeiras.
É importante destacar que é bem provável que vá precisar de permissão de usuário para realizar tal tarefa; consulte seu DBA!!
No geral, embora simples, foi bem detalhista. Criar chaves no banco é mais trabalhoso do que se pensa e o pior é garantir a integridade.
Ainda faltam duas configurações nas chaves estrangeiras que dizem respeito ao comportamento quando houver update e delete na fato. Irei abordar em outro post.
Espero que este post os ajude e se gostou, compartilhe com seus amigos!
Saúde!
Deus os abençoe!
Link para o arquivo sql, aqui!
ManageBI 