Best Practices for Msbi Essays
· Extracting data from source systems, transforming it, and then loading it into a data warehouse
· Structuring the data in the warehouse as either third normal form tables or in a star/snowflake schema that is not normalized
· Moving the data into data marts, where it is often managed by a multidimensional engine
· Reporting in its broadest sense, which takes place from data in the warehouse and/or the data marts: reporting can take the form of everything from printed output and Microsoft Office Excel® spreadsheets through rapid multidimensional analysis to data mining.
SQL Server 2008 provides all the tools necessary to perform these tasks [MMD07].
· SQL Server …show more content…
Simplify the transformation process by using Data Profiling tasks
The new Data Profiling task in Integration Services can be used to initially understand the nature of the source data for design purposes (see Designing Your Data Warehouse/BI Solution). However, the profiles it produces can also be used to apply business rules to data as part of the transformation process. Suppose, for example, the business rule says that the data from a particular source is acceptable only if the number of nulls does not exceed 1%. The profiles produced by a Data Profiling task can be used to apply this rule.
Note that Data Profiling tasks profile SQL Server tables; data in other locations must be loaded into staging tables before it can be profiled.
Terminate all SQL statements with a semi-colon in SQL Server 2008
Prior to SQL Server 2005, Transact-SQL was relatively relaxed about semi-colons; now some statements (including MERGE) require this terminator. If you terminate all SQL statements with a semi-colon, you avoid problems when the use of a semi-colon is obligatory.
If you cannot tolerate downtime, consider using