填充报告/数据仓库数据库的策略

我们的一般流程是:

1. 将数据从源表复制到 完全相同的表 加载数据库中的结构
2. 将数据转换为临时数据 具有相同结构的表 作为最终事实/维度表
3. 将临时表中的数据复制到 事实/维度表

SSIS适合于步骤1,它或多或少是一个1:1复制过程,具有一些基本的数据类型映射和字符串转换。

对于第2步,我们使用存储的procs,.net和python的混合。大多数逻辑都在过程中,比如在外部代码中进行繁重的解析。纯TSQL的主要好处是,转换通常依赖于加载数据库中的其他数据,例如,在SQL联接中使用映射表比在外部脚本中执行逐行查找过程(即使使用缓存)快得多。诚然,这只是我的经验,程序化处理可能对Syour数据集更好。

在一些情况下,我们需要做一些复杂的分析(DNA序列),而TSQL并不是一个可行的解决方案。所以这就是我们使用外部.NET或Python代码来完成工作的地方。我想我们可以在.NET过程/函数中完成这一切,并将其保存在数据库中,但是还需要其他外部连接,所以单独的程序是有意义的。

步骤3是一系列插入…选择…声明:很快。

总之,使用最好的工具来完成这项工作,不要担心把事情弄混。一个或多个SSIS包是将存储过程、可执行文件以及其他需要执行的操作链接在一起的好方法,因此您可以在一个地方设计、执行和记录整个加载过程。如果这是一个巨大的过程,您可以使用子包。

我知道你所说的TSQL感觉很尴尬(实际上,我发现它比其他任何东西都更重复),但是对于数据驱动的操作来说,它非常非常快。所以我的感觉是,在TSQL中进行数据处理,在外部代码中进行字符串处理或其他复杂的操作。

我再看一眼SSIS。虽然有一个学习曲线,但它可以非常灵活。它支持许多不同的操作数据的方法,包括存储过程、ActiveX脚本和各种操作文件的方法。它能够处理错误并通过电子邮件或日志提供通知。基本上,它应该能够处理几乎所有的事情。另一种选择是定制应用程序,它可能会有更多的工作(ssis已经包含了很多基础知识),而且仍然很脆弱——对数据结构的任何更改都需要重新编译和重新部署。我认为更改您的SSIS包可能更容易。对于一些更复杂的逻辑,您甚至可能需要使用多个阶段-一个定制的C控制台程序稍微操作数据,然后一个SSIS包将其加载到数据库。

SSIS学习起来有点痛苦,当然有一些技巧可以充分利用它,但我认为这是值得的投资。一本或两本好的参考书可能是一笔不错的投资(Wrox的专家SQLServer2005集成服务也不错)。

我将研究ETL(提取/转换/加载)最佳实践。你要问的是购买和建造,一种特定的产品和一种特定的技术。可能需要先备份几个步骤。

几个注意事项:

• 提供良好的ETL有很多微妙的技巧:使其运行得非常快、易于管理、处理规则级别的审核结果、支持高可用性甚至可靠的恢复,甚至被用作报告解决方案(而不是数据库备份)的恢复过程。
• 你可以建立自己的ETL。缺点是,商业ETL解决方案有预构建的适配器(您可能无论如何都不需要),而且定制的ETL解决方案往往会失败,因为很少有开发人员熟悉所涉及的批处理模式(请参阅您现有的体系结构)。由于ETL模式没有很好的文档记录,所以除非您引入一个在这方面非常有经验的开发人员,否则不太可能成功地编写自己的ETL解决方案。
• 当查看商业解决方案时,请注意元数据和审计结果是解决方案中最有价值的部分:基于GUI的转换构建器实际上并不比仅仅编写代码更有效率,但在维护方面,元数据可能比读取代码更有效率。
• 复杂的环境很难用单个ETL产品解决,因为网络访问、性能、延迟、数据格式、安全性或其他与您的ETL工具不兼容的要求。因此,不管怎样,定制和商业的组合通常都会产生效果。
• 如果需要支持或关键功能,像Pentaho这样的开源解决方案实际上是商业解决方案。

所以,如果从商业应用程序中提取数据,如果需求(性能等)很难满足,或者如果你有一个初级或不可靠的编程团队,我可能会选择商业产品。否则你可以自己写。在这种情况下,我会得到一本ETL书籍或顾问帮助理解典型的功能和方法。