Mathieu Guindon's questions

Eu tenho uma tabela de origem que se parece essencialmente com isso:

• Código de empregado
• Data de início da semana
• Horas Trabalhadas Dia1
• Horas Trabalhadas Dia2
• Horas Trabalhadas Dia3
• Horas Trabalhadas Dia 4
• Horas Trabalhadas Dia5
• Horas TrabalhadasDia6
• Horas TrabalhadasDia7

A tabela real tem algo como 500 colunas numeradas (na verdade não as contei - existem vários e numerosos campos numerados de 1 a 7 e, em seguida, outro punhado numerado de 1 a 25, vezes 7) por dia da semana (não, esse não é o meu projeto) , e atualmente há algo como 38.600 linhas (crescendo a cada semana).

Eu tenho um pacote SSIS que está tentando normalizar esses dados... que atualmente se parece com isso:

Cada "origem" está selecionando um conjunto de colunas numeradas da mesma tabela de origem, e o componente UNION ALL combina as 7 origens em uma, resultando em cerca de 258.900 linhas.

O restante do fluxo de trabalho adiciona algumas colunas calculadas, procura chaves substitutas (por exemplo EmployeeCode, é usado para pesquisar um EmployeeId, e então a data é calculada e usada para procurar um TimeId) e, em seguida, as linhas "modificadas" são atualizadas e as "novas" uns são inseridos em uma tabela normalizada; linhas inalteradas não terminam em lugar nenhum.

Existe alguma maneira melhor (por exemplo, um pouco menos pesado na pressão da memória) para normalizar os dados de origem?

Não sou DBA, mas sendo as coisas como são, tenho que usar o chapéu de DBA e configurar planos de manutenção na minha instância do SQL Server.

Então, por um tempo, tenho feito meu processo noturno do SSIS executar uma tarefa Executar SQL para executar os backups - basicamente executando master.dbo.xp_create_subdirpara garantir que as pastas de destino existam e, em seguida, BACKUP DATABASE [DbName] TO DISK = 'G:\Backups\DbName\DbName.bak' WITH INIT.

Sempre que essa tarefa falhava, o resto do processo era abortado e eu recebia uma notificação, e chegava na manhã seguinte para perceber que a unidade para logs de transações estava cheia, então eu os truncava manualmente e seguia em frente. .. até que a história se repetisse e os logs de transações superassem o espaço em disco disponível novamente.

O script "truncate manual" se parece com isso:

use Staging;
alter database Staging set recovery simple
alter database Staging set recovery full
dbcc shrinkfile ('Staging_log', 0, truncateonly);
go

Então, estou ficando cansado disso e decidi tentar fazer as coisas corretamente , seguir as etapas aqui e criar um plano de manutenção real :

O problema é que nunca fiz isso antes, então tenho algumas perguntas:

• Fazer backup dos logs de transações como esse os truncará automaticamente ou há outra coisa que preciso fazer?
• Posso executar backups de dados e logs de transações simultaneamente? Se não, então qual é a maneira correta de fazer isso?
• Os arquivos de backup estão sendo coletados durante a noite por outro processo que pega todos os arquivos no servidor e os armazena em outro lugar - seria uma boa ideia expirar o conjunto de backup após 2 dias? Eu preciso fazê-los expirar em tudo?
• As tarefas de limpeza removem, respectivamente, os arquivos .bak e .trn "antigos" nas subpastas de G:\Backups. Isso faz sentido?
• Seria melhor fazer isso no SSIS, para que eu possa falhar no meu ETL se/quando os backups falharem? Ou meu processo de ETL deveria se importar?

Desculpe se são muitas perguntas para um post, se necessário, editarei e farei várias perguntas - acho que todas estão intimamente relacionadas.

Eu tenho uma tarefa Executar SQL em meu fluxo de controle do SSIS que está fazendo backup de vários bancos de dados antes de executar o ETL durante a noite:

EXECUTE master.dbo.xp_create_subdir N'G:\Backups\DQ'
GO
EXECUTE master.dbo.xp_create_subdir N'G:\Backups\master'
GO
EXECUTE master.dbo.xp_create_subdir N'G:\Backups\Metadata'
GO
EXECUTE master.dbo.xp_create_subdir N'G:\Backups\NDS'
GO
EXECUTE master.dbo.xp_create_subdir N'G:\Backups\Staging'
GO
BACKUP DATABASE [DQ] TO  DISK = N'G:\Backups\DQ\DQ.bak' WITH  RETAINDAYS = 3, NOFORMAT, NOINIT,  NAME = N'DQ', SKIP, REWIND, NOUNLOAD,  STATS = 10
GO
BACKUP DATABASE [master] TO  DISK = N'G:\Backups\master\master.bak' WITH  RETAINDAYS = 3, NOFORMAT, NOINIT,  NAME = N'master', SKIP, REWIND, NOUNLOAD,  STATS = 10
GO
BACKUP DATABASE [Metadata] TO  DISK = N'G:\Backups\Metadata\Metadata.bak' WITH  RETAINDAYS = 3, NOFORMAT, NOINIT,  NAME = N'Metadata', SKIP, REWIND, NOUNLOAD,  STATS = 10
GO
BACKUP DATABASE [NDS] TO  DISK = N'G:\Backups\NDS\NDS.bak' WITH  RETAINDAYS = 3, NOFORMAT, NOINIT,  NAME = N'NDS', SKIP, REWIND, NOUNLOAD,  STATS = 10
GO
BACKUP DATABASE [Staging] TO  DISK = N'G:\Backups\Staging\Staging.bak' WITH  RETAINDAYS = 3, NOFORMAT, NOINIT,  NAME = N'Staging', SKIP, REWIND, NOUNLOAD,  STATS = 10

A intenção é reter apenas 3 dias e descartar qualquer coisa mais antiga.

O problema é que os arquivos .bak continuam crescendo e crescendo por uma ou duas semanas, até que a unidade G fique cheia e a tarefa Executar SQL falhe, o que interrompe o ETL noturno.

Parece que o .bak aparentemente não está descartando backups antigos; parece que eu poderia voltar até a última vez que excluí todos os backups para liberar algum espaço em disco naquela unidade G:

A questão é simples: parece que minha Tarefa Execute SQL está errada. Como faço para corrigir isso? Ou é outra coisa?

Eu construí um SSIS ETL para integrar várias fontes de dados (uma do MySQL, duas do SQL Server) em um único banco de dados SQL Server relacional e normalizado, que chamei de [NDS].

O SSIS ETL lida com atualizações do tipo 2 e, portanto, o [NDS] gera chaves substitutas e as tabelas SCD incluem um registro de data e hora [_EffectiveFrom] e uma coluna anulável [_EffectiveTo], e há restrições para as chaves naturais e belas chaves estrangeiras que vinculam todas as dados juntos.

Agora, eu queria construir um banco de dados dimensional SSAS a partir dele e não demorou muito para perceber que estava me preparando para um esquema de floco de neve:

Portanto, estou pensando em adicionar um novo banco de dados [DDS] (relacional) para criar a dimensão real e as tabelas de fatos que alimentarão os DSVs para o banco de dados SSAS.

Este banco de dados [DDS] seria tão desnormalizado quanto humanamente possível, de modo a "achatar" os fatos e dimensões (como, [OrderHeaders]+[OrderDetails] em uma tabela de fatos [Orders] e [CustomerStores]+[Customers]+ [SalesReps] em alguma tabela de dimensão [Clientes]) - fazer isso não deve apenas facilitar a construção das hierarquias de dimensão no SSAS, mas também facilitar a criação de um esquema em estrela real.

Eu tenho algumas perguntas embora:

• Posso reutilizar um subconjunto de minhas chaves substitutas existentes? Estou pensando em pegar a chave existente para o nível mais granular e torná-la a chave de dimensão. Essa é uma boa abordagem ou devo simplesmente ignorar as chaves substitutas [NDS] e fazer com que o [DDS] (db relacional) gere um novo conjunto de chaves substitutas?
• Como lidar com SCD? Por exemplo, "Materiais" e "Fornecedores" irão gerar novos registros em [NDS] quando alguns campos específicos mudarem no sistema de origem... Acho que terei que projetar o SSIS ETL para carregar apenas os registros de "última imagem" no banco de dados [DDS] e, em seguida, reimplemente as atualizações do tipo 2 nesse processo, ou seja, trate o [NDS] como um "sistema de origem" que mantém o histórico, enquanto duplica tudo neste banco de dados [DDS]. Mas então, por que eu precisaria manter o histórico no [NDS] e no [DDS]? Claramente algo não está certo.

Estou me preparando para um Big Mess™ ou estou no caminho certo?

Estou configurando meu primeiro banco de dados dimensional com SSAS e tenho essa dimensão [Materials] que precisa de uma hierarquia mais ou menos assim:

[PriceCode    v] --> nullable
  Price Code
  ...
[Material     v]
  Code
  AltCode
  Name
  ...
[Id           v]  --> not actually exposed as a hierarchy level
  DateInserted
  DateUpdated
  DateDeleted
  EffectiveFrom
  EffectiveTo

O problema é que o [PriceCode]atributo pode ser anulado; o DSV tem um FK entre ae [Materials]uma [PriceCodes]tabela e [Materials].[PriceCodeId]é anulável.

Existe uma maneira de ainda definir uma hierarquia em que um atributo anulável é um pai? Eu brinquei com UnkownMember e UnknownMemberName e a configuração NullProcessing da chave de atributo , mas não consegui processar a dimensão.

Pontos de bônus se alguém puder confirmar se estou abordando corretamente o problema da dimensão de mudança lenta, criando um nível de hierarquia com base nas chaves de negócios (ou seja, o Codecampo; a chave natural inclui o EffectiveTocampo, que é nullpara a imagem atual de um registro), e tratando os metadados SCD como um nível próprio.

Tenho dois pacotes SSIS que são executados agendados durante a noite (via SQL Server Agent) como parte de uma implantação SSIS maior, sem problemas. Tudo está usando a autenticação do Windows, e o trabalho agendado pertence a um administrador de sistema (bem, eu) e é executado como a conta de serviço do SQL Server Agent.

Portanto, os dados vão essencialmente da source system ~> transit db ~> staging ~> NDSnoite para o dia.

Os dois pacotes SSIS que me interessam, lidam com as partes transit db ~> staginge staging ~> NDS, respectivamente, para um conjunto específico de dados.

Um usuário de domínio (não-sysadmin) faz algo no source systeme que empurra os dados interessantes para o transit db, então preciso de uma maneira de buscar esses dados atualizados durante o horário de trabalho para atualizar o NDS: foi decidido que a maneira mais simples dessa pessoa acionar esse ETL foi clicando em um botão em uma pasta de trabalho do Excel habilitada para macro, que se conecta ao SQL Server via ODBC (usando autenticação do Windows) e executa um procedimento armazenado.

O procedimento armazenado se parece com isso:

create procedure dbo.UpdateMaterialInventory
as
begin
    execute msdb.dbo.UpdateMaterialInventory;
end

O procedimento armazenado "irmã" em [msdb] se parece com isto:

create procedure dbo.UpdateMaterialInventory
with execute as 'SqlAgentProxy'
as
begin
    execute msdb.dbo.sp_start_job N'NDS-ManualMaterialInventory';
end

Este usuário [SqlAgentProxy] é um usuário do Windows que criei em [msdb] fora do login do usuário do domínio, ao qual dei executepermissão para este UpdateMaterialInventoryprocedimento. Isso evita ter que conceder ao usuário do domínio executepermissão para msdb.dbo.sp_start_job, o que seria excessivo.

O trabalho do SQL Agent NDS-ManualMaterialInventorypertence ao usuário do domínio e tem 2 etapas, cada uma do tipo [SQL Server Integration Services Package], configurada para Executar como SSISProxy .

SSISProxyé um proxy do SQL Server Agent mapeado para o subsistema [SQL Server Integration Services Package], usando o nome da credencial SSISProxyCredentials. O login do usuário do domínio foi adicionado aos principais da conta Proxy .

Eles SSISProxyCredentialsforam criados com a identidade do mesmo usuário de domínio que está executando todo o SSIS ETL durante a noite e sua senha foi verificada quatro vezes.

Agora, se eu executar isso:

execute as login=N'DOMAIN\thatperson'
exec NDS.dbo.UpdateMaterialInventory;
go

Eu recebo esta saída:

Job 'NDS-ManualMaterialInventory' started successfully.

No entanto, a história do trabalho está contando uma história muito menos encorajadora:

The job failed.  The Job was invoked by User DOMAIN\thatperson.
The last step to run was step 1 (Extract).

E detalhes da etapa 1:

Executed as user: {domain user that runs SSIS ETL overnight}.
Microsoft (R) SQL Server Execute Package Utility  Version 12.0.4100.1 for 64-bit
Copyright (C) Microsoft Corporation. All rights reserved.
Started:  2:18:50 PM  Failed to execute IS server package because of error 0x80131904.
Server: {server name}, Package path: \SSISDB\Foo\Bar\foobar.dtsx, Environment reference Id: NULL.
Description: Login failed for user '{domain user that runs SSIS ETL overnight}'.
Source: .Net SqlClient Data Provider 
Started:  2:18:50 PM  Finished: 2:18:51 PM  Elapsed:  0.094 seconds.
The package execution failed.
The step failed.

O trabalho falha e nada é registrado em nenhum lugar.

Se eu alterar o proprietário do trabalho para ser eu mesmo e alterar a execução das etapas para a conta de serviço do SQL Server Agent, o trabalho será executado, bem-sucedido e registrará 1.067 linhas em [Metadata].[dbo].[sysssislog].

Parece que há algo errado com a configuração do proxy/credenciais. Qual parte estou fazendo errado?

Um processo sobre o qual não tenho controle é descartar e recriar tabelas em um banco de dados MySQL todas as noites. Isso não seria um problema (eu acho), se ele recriasse as tabelas de forma idêntica todas as vezes. Mas isso não acontece: todas as manhãs eu entro e abro meu projeto SSIS e recebo novos erros/avisos de validação de metadados em tabelas/colunas aleatórias, porque esse processo noturno está criando varcharcolunas com comprimento que varia dependendo dos dados, então ontem eu tinha uma coluna com comprimento de 90, e hoje a mesma coluna na mesma tabela agora tem comprimento de 208, e amanhã pode ser uma história diferente... ou não.

Este banco de dados MySQL é minha fonte de dados primária - eu tenho meu banco de dados Staging no SQL Server e, com uma conexão de servidor vinculada, estou usando o SSIS para selecionar dados dessas tabelas e nas tabelas do SQL Server (com algumas colunas adicionadas), que Estou truncando antes de preenchê-los com os dados remotos (para que os metadados neste banco de dados de preparação sejam corrigidos).

Por que o SSIS precisa validar metadados externos? Se eu desligar a validação do tempo de design, ele diz que está atrasado para o tempo de execução - isso significa que eu só estaria empurrando o problema do tempo de design para o tempo de execução e ainda obteria um pacote com falha?

Eu tenho um script T-SQL que pode realizar a transferência de dados do MySQL para as tabelas de teste do SQL Server, mas esse script é executado em cerca de 45 minutos enquanto o pacote SSIS, quando todos os metadados estão atualizados, é executado em cerca de 5 minutos - escusado será dizer que prefiro encontrar uma maneira de fazer o SSIS parar de reclamar sobre metadados desatualizados do que usar esse script.

Existe uma maneira de trabalhar com esse metamorfo de uma fonte de dados no SSIS e manter minha sanidade?

Estou projetando um sistema Staging+NDS+DDS Data Warehouse, onde um ETL irá normalizar os dados [Staging]e carregá-los em [NDS], que irá armazenar todo o histórico.

Eu praticamente terminei o script T-SQL que vai criar as tabelas e restrições no [NDS]banco de dados, que contém as tabelas Master e Transactional , que vão alimentar respectivamente as tabelas [DDS] Dimension e Fact no que pretendo ser um esquema em estrela.

Eu me dei as seguintes regras a seguir:

• As dimensões de fornecimento de tabelas [DDS]são prefixadas comDWD_
• Os fatos de fornecimento de tabelas [DDS]são prefixados comDWF_
• As colunas de chave estrangeira são prefixadas comDWK_
• A coluna de chave substituta é prefixada com o mesmo prefixo da tabela. O que significa que a chave substituta é sempre:
  • DWD_Keypara DWD_mesa ou
  • DWF_Keypara uma DWF_mesa.
• As colunas de controle são prefixadas com o mesmo prefixo da tabela. Por exemplo...
  • A DWD_Customerstabela possui colunas de controle:
    • DWD_IsLastImage
    • DWD_EffectiveFrom
    • DWD_EffectiveTo
    • DWD_DateInserted
    • DWD_DateUpdated
    • DWD_DateDeleted
  • A DWF_InvoiceHeaderstabela possui colunas de controle:
    • DWF_DateInserted
    • DWF_DateUpdated
    • DWF_DateDeleted
• As chaves primárias (/chaves substitutas) são sempre prefixadas com PK_seguido pelo nome da tabela (incluindo o prefixo da tabela) - por exemplo PK_DWD_Customers, e PK_DWF_InvoiceHeaders.
• Eu também adicionei uma uniquerestrição nas chaves naturais , e essas são sempre prefixadas com NK_seguidas pelo nome da tabela (incluindo o prefixo da tabela) - por exemplo NK_DWD_Customers, e NK_DWF_InvoiceHeaders.
• As colunas de chave estrangeira são sempre prefixadas com DWK_seguido pelo nome da tabela referenciada (sem seu prefixo) e a palavra "Chave" - ​​por exemplo, DWK_CustomerKey.
• As restrições de chave estrangeira são sempre nomeadas FK_[ParentTableNameWithPrefix]_[ChildTableNameWithPrefix].
• Quando uma tabela possui vários FKs na mesma tabela, o nome da coluna FK é anexado ao nome da restrição, por exemplo, FK_DWD_FiscalCalendar_DWF_OrderDetails_DeliveryDate.

Todas as colunas prefixadas não têm significado comercial e nunca devem aparecer nas exibições; isso me deixa com um design bastante limpo e consistente, e create tablescripts parecidos com este:

create table DWD_SubCategories (
     DWD_Key int not null identity(1,1)
    ,DWD_DateInserted datetime not null
    ,DWD_DateUpdated datetime null
    ,DWK_CategoryKey int not null
    ,Code nvarchar(5) not null
    ,Name nvarchar(50) not null
    ,constraint PK_DWD_SubCategories primary key clustered (DWD_Key asc)
    ,constraint NK_DWD_SubCategories unique (Code)
);

Então, minha pergunta é: há algo que eu deva saber (ou desaprender ) antes de continuar e implementar o ETL para carregar dados neste banco de dados? Alguém herdando este banco de dados iria querer me perseguir e arrancar minha cabeça no futuro? O que devo mudar para evitar isso? A razão pela qual estou perguntando sobre prefixos é porque estou usando DWDe DWF, mas as tabelas não são tecnicamente tabelas de "dimensão" e "fato". Isso é confuso?

Além disso, não tenho certeza sobre o conceito de chave natural - estou correto ao presumir que deve ser uma combinação única de colunas que o sistema de origem pode considerar suas colunas "chave", que posso usar no processo ETL para localizar, digamos , um registro específico para atualizar?