já usamos o SQL Server há algum tempo em nossa empresa para hospedar todos os dados do aplicativo. Usamos uma configuração típica com dados sendo carregados de várias fontes de dados em uma Staging Area, que posteriormente alimenta diferentes datamarts para seus fins específicos (com dados personalizados armazenados lá).
Porém, do meu ponto de vista, isso gera "silos" de dados, que duplicam muitos dados e dificultam a manutenção de todos de alguma forma síncronos. Além disso, como o número de soluções cresce constantemente, o número de datamarts "silos" cresce proporcionalmente, já que os requisitos são sempre ligeiramente diferentes e temos instruções claras para usar datamarts separados para cada solução.
Os aplicativos usam conexões diretas com os datamarts para consumir e manipular os dados (conforme necessário).
Estava pensando muito sobre isso recentemente e há algumas outras iniciativas em andamento no momento para modernizar nosso backend, então fiz algumas pesquisas e minha ideia é propor o seguinte:
- manter a área de preparação como está (importação simples de dados de diferentes fontes para um datamart). Há um esquema para cada origem e pacotes SSIS correspondentes para carregar esses dados. Tudo isso deveria ficar.
- crie um esquema para cada solução na área de preparação. Os direitos de acesso podem ser gerenciados nesse nível para garantir que os consumidores do aplicativo não possam acessar os "esquemas de importação" ou outros, mas apenas aqueles que pertencem aos aplicativos aos quais eles têm acesso. Assim que o ponto 5 (API) for implementado, a autorização deverá ocorrer ali antes da conexão ao datamart.
- substituir os datamarts "silo" e os trabalhos ETL (pacotes SSIS) que atualmente carregam os datamarts por visualizações materializadas (também conhecidas como "indexadas") nos esquemas recém-definidos
- mover quaisquer tabelas usadas com operações CRUD do antigo datamart do aplicativo para o novo esquema correspondente no datamart "Staging Area" (precisa ser renomeado, pois agora cobre todo o data warehouse em um datamart)
- implementar uma API simples que apresenta solicitações GET para as visualizações definidas e POSTs para operações CRUD necessárias das tabelas do ponto 4
É claro que cada uma dessas etapas tem um grande impacto nas próprias soluções e no local de onde elas consomem os dados. Além disso, é claro que seriam necessárias muitas horas de desenvolvimento. Mas este não é o ponto por enquanto, é mais uma questão geral se isso faz sentido ou como você configuraria isso no SQL Server.
Tenho a sensação de que construímos muito em torno dos dados, o que também dificulta em outras áreas a melhoria/aceleração dos processos de back-end.
Muito obrigado antecipadamente e tudo de bom Benny
No geral, ainda estou um pouco confuso (e parte disso se deve apenas ao fato de não ter sua pilha ETL na minha frente). Mas espero que você encontre algo significativo nesta resposta.
Gerenciar alterações de esquema de uma fonte externa pode ser entediante, dependendo de como você estrutura sua pilha. É assim que normalmente estruturo as coisas quando estou lidando com uma fonte de dados externa:
Trago os dados para seu próprio banco de dados em meu servidor*, nomeado de acordo com o banco de dados de origem de onde vieram (se conhecido), caso contrário, um nome razoável baseado na fonte. Por exemplo, vamos chamá-lo de
SimpleSales. Preservo a estrutura exatamente como a fonte a fornece. Isso torna mais fácil depurar problemas de dados posteriormente (para rastrear se é um problema da fonte de dados versus minhas normalizações ou transformações).Eu crio um esquema com um nome correspondente ao banco de dados criado na etapa 1, em meu banco de dados primário. Por exemplo:
CREATE SCHEMA SimpleSales;Nesse novo esquema em meu banco de dados primário, crio visualizações (regulares) que fazem referência aos objetos nativos do banco de dados de origem. Geralmente essas visualizações são individuais com os objetos de origem. Eu apenas crio visualizações para os objetos que irei consumir. É aqui que normalizo o nome do próprio objeto e seus nomes de colunas e tipos de dados.
Finalmente, se eu tiver uma lógica de negócios especial que preciso incorporar no topo dessas colunas, por exemplo, para transformá-las, farei isso em outra visualização em um esquema mais específico (no meu banco de dados primário) relacionado ao aplicativo/domínio que está indo consumir aquele objeto transformado. Por exemplo, se
SimpleSales.SalesOrdersfor minha visualização normalizada em meu banco de dados primário e eu precisar aplicar uma lógica dedicada para meu aplicativo de faturamento automatizado, terei um esquema como o qualAutoInvoicecrio minhas visualizações dedicadas, comoAutoInvoice.Customers. Se for a lógica de negócios global que se aplica na maioria dos casos/muitos aplicativos diferentes, na verdade eu uso um esquema chamadoCommon. Eu também uso oCommonesquema para unificar dados relacionados de diferentes sistemas externos. Por exemplo, se eu tivesse dois sistemas externos diferentes que mantivessem dados de vendas, como informações de clientes, teria umaCommom.Customersvisualização que extrai a lista distinta de clientes de ambas as fontes.(Opcional) Se eu precisar melhorar o desempenho específico de algo e a solução for usar uma visualização indexada, isso provavelmente terá que acontecer no banco de dados de origem. Normalmente crio um único esquema, com o nome do banco de dados primário, no banco de dados de origem, para colocar essas personalizações. Por exemplo, no
SimpleSalesbanco de dados, posso ter umPrimaryDatabaseesquema, e é para ondeSimpleSalesiriam minhas visualizações indexadas para os objetos.As visualizações (quando não são abusadas) são uma ótima ferramenta para adicionar uma camada de abstração. Além de uma coluna com dependências sendo renomeadas ou removidas, outras alterações de esquema nas tabelas raiz geralmente não quebram sua pilha de consumo quando você usa visualizações como objetos para consumo. E quando há uma alteração significativa no esquema, é fácil atualizar apenas sua visualização raiz no banco de dados primário para contabilizar a alteração.
Além disso, lembre-se das limitações das Visualizações indexadas . Não ser capaz de usar junções externas ou auto-junções, além de muitas outras limitações, torna-as um pouco mais específicas para quando forem aplicáveis para resolver um problema. É por isso que não os uso como padrão na estruturação inicial dos dados.
Para casos mais complexos, funções com valor de tabela e procedimentos armazenados também podem ser utilizados e até mesmo consumidos de forma semelhante às visualizações. Mas isso é outro capítulo para outro dia.
*No que diz respeito a como realmente ingerir os dados em seu banco de dados de origem, depende apenas da tecnologia com a qual você se sente mais confortável, da taxa com que os dados precisam ser atualizados e do tamanho dos dados. SSIS é um pouco desatualizado e complexo, IMO. Em meus empreendimentos, tive a sorte de poder utilizar a Replicação, na maioria das vezes, para sincronização de dados em tempo real. Alternativamente, se os dados não forem muito grandes e não precisarem ser atualizados em tempo real, o processo pode ser tão simples quanto o T-SQL puro que insere os dados em uma nova tabela, renomeia (ou descarta se você não precisa do histórico) a tabela existente e, finalmente, renomeia a nova tabela com o nome da tabela antiga. Algo que pode ser feito em menos de 10 linhas de código.
Acho que o que estou entendendo da pergunta é que o principal problema é a aparente duplicação de dados em diferentes "data marts".
Pode haver uma tensão nesta área entre produzir um todo totalmente integrado utilizando tabelas comuns, tanto quanto possível, e manter um grau razoável de modularidade para diferentes desenvolvimentos de relatórios.
A consequência da integração total de tudo é que cada alteração proposta nas tabelas comuns acaba por se tornar titânica no âmbito das possíveis implicações.
Para evitar esse tipo de esforço incontrolável, recursos ad-hoc e arranjos excepcionais se infiltram, mas infelizmente o fazem de uma forma mal organizada e às vezes mal localizada (com recursos de uso único ou dados entrando em áreas "comuns") , e muitas vezes os esquemas de nomenclatura também ficam tensos além da compreensão.
Torna-se também difícil manter módulos de relatórios individuais no futuro, uma vez que pequenos ajustes que necessitam de se desviar de uma fonte comum original podem exigir que uma série de coisas sejam separadas do conjunto comum para que o processamento possa agora ser alterado de forma independente.
Mas esta divisão acaba por ser feita num tempo distante da análise e desenvolvimento original do módulo, e talvez nem mesmo pela mesma pessoa - inevitavelmente causando mais riscos, mais transtornos para os testes em uso acumulados, e mais habilidade e exigir do desenvolvedor o que poderia ter sido uma tarefa muito menor se todas as entradas e processamento já tivessem sido independentes.
A noção de que o esforço de manutenção aumenta com uma duplicação modesta também é imaginária na minha experiência.
Pode parecer que se você descobrir uma falha no código que foi duplicada em muitos lugares, ela será mais difícil de corrigir do que se fosse um único trecho de código compartilhado por muitas saídas.
Na realidade, essas correções em pipelines de relatórios complicados muitas vezes exigem que todo o código downstream seja revisado para saber se são necessários ajustes consequentes. É muito mais fácil fazer essa correção de forma incremental, módulo por módulo, na ordem de importância percebida da correção para cada módulo, do que ter que revisar (e talvez ajustar) absolutamente tudo em um sistema de relatórios antes que uma correção possa ser confirmada.
A minha experiência é que, em sistemas de relatórios, é muito mais viável e resiliente aceitar alguma duplicação de dados em prol de uma modularidade e independência claras.