SSIS SSIS
Using default names for all components in a control flow and a data flow 所有组件都在一个控制流和数据流使用默认名称
I really dislike opening projects and seeing only generic names.我真的不喜欢开放项目和看到的只有通用名称。 I have built some in the past because of insufficient time to document everything, but I hated creating them and later leaving them like that.我已经建立了一些在过去因为没有足够的时间记录一切,但我讨厌他们创造和后来离开他们这样。 I have also seen developers with time on their hands carelessly omitting them.我也看到过他们的手时不小心忽略他们的开发。 The worst practice is to actually rename them to an equally unintelligible, if better, language.最坏的做法是将它们重命名同样难以理解的,如果更好的语言。 Many ETL-out-of-the-box tools actually do that and make subsequent maintenance much harder.许多的ETL出的现成的工具,实际上,并进行后续维护更难。
Performing joins with Merge Join 执行联结与合并联接
Chaining 5 Merge Joins and placing Sort components between them is something SQL Server does faster and with much less resources.链接5合并连接,它们之间的名次排序组件是一些SQL Server的不更快的速度和非常少的资源。 Therefore doing joins in the database is a no-brainer even if your preference lies with a fully SSIS-idised process.因此做在数据库中加入的是一个没有脑子,即使您的偏好在于具有充分的SSIS idised过程。
Not using configurations 不使用配置
Lucky, in Denali we are getting a completely revamped project configurations approach.幸运的是,在迪纳利我们得到一个全新的项目配置方法。 Even if imperfect, Package Configurations are a must for every SSIS project.即使不完善,包配置必须为每一个SSIS项目。 Migrating projects, changing databases, setting variables are just too good to miss.迁移项目,不断变化的数据库,设置变量是太好错过。
SSAS SSAS
Using MDX for everything 使用一切的MDX
MDX has an aura of a language which allows you to do everything much faster than SQL. MDX有一种语言,它允许你做的一切比SQL快得多的光环。 Well, this is not entirely true and in the majority of cases a change in the design will allow avoidance of MDX, which in turn removes a whole layer of complexity allowing tremendous improvements over any programmatical approach.那么,这是不完全正确的,并在大多数情况下,在设计的改变将使避免的MDX,这反过来又删除整层的复杂性,允许任何的程序化方法的巨大进步。
Importing too much dimensional data 导入太多的三维数据
Often developers underestimate the impact of unnecessarily large dimensions with many useless attributes.开发人员通常低估了不必要的大尺寸的影响,许多无用的属性。 Here the best approach is to add more as you need them rather than import everything because we can.在这里,最好的办法是,因为你需要他们,而不是进口的一切,因为我们可以添加更多。 Cubes are smaller and less complex with less attributes, processing is faster, and the most important thing is that the users actually like having less clutter.多维数据集是少属性更小,更复杂,处理速度更快,而且最重要的是,用户实际上喜欢有少杂波。
Leaving NULLs in the relational database 在关系数据库中留下的 NULL
SSAS does not like NULLs. SSAS不喜欢空值。 I have not seen a case when NULLs are necessary in dimension tables.我没有见过的情况下,维度表中的NULL是必要的。 While I would leave a NULL for a measure when there is no data for it, too many of these can be quite harmful and it may be a case of a bloated measure group, as well.虽然我会留下一个空的措施时,有没有数据,这些过多可以非常有害的,它可能是一个臃肿的措施的情况下,以及。
SSRS SSRS
Storing business logic in report queries 业务逻辑存储在报表查询
While we can, we should never store SQL and MDX logic which can be implemented in stored procedures or calculated measures in the reports.虽然我们可以,我们不应该存储SQL和MDX逻辑可以在存储过程或在报告中计算的措施实施。 Maintaining SSRS queries is much harder and less transparent.维护SSRS的查询是更难和透明度较低。 There are performance implications around caching, too. ,也有周围缓存对性能的影响。
Merging data sets in SSRS 合并数据集在SSRS
This is hard to be done, and when it is, it is always bad.这是很难做到的,当它是,它总是坏。 Data sets should be merged in the queries – not in the reports.应合并数据集查询中 - 而不是在报告中。
Building massive reports 构建大规模的报告
In many organizations reports are seen as a transport mechanism to Excel.在许多组织的报告被看作是一种传输机制到Excel。 Users generate large, hundreds of thousands of rows report with a large amount of columns and sometimes even basic calculations.产生较大的用户,数十万行报告用大量的列,有时甚至连基本的的计算。 All this can be done simply and easily on the database level without going through SSRS.这一切都可以在数据库级别上进行简单和容易不通过SSRS的。