Save money by incorporating best practices for Data Warehousing

  • Article

I just read an article called Best Practices for Data Warehousing with SQL Server 2008 that is worth your reading. The article covers some of the most important data warehousing features in SQL Server 2008 and outlines best practices for using them effectively. In addition, it covers some of the more general best practices for creating a successful data warehouse project.

Make your data warehouse a cost-effective investment

The introduction explains that data warehouses have been being built in one form or another for over 20 years. Studies show that half of all data warehouse projects show a Significant Return on Investment while others fail. Why do the nonperformers fail? The article suggests that not everyone in the field is aware of the best practices.

I would add that the success of a data warehouse is also largely dependent on people, policies, dynamics (politics), and lots of other complexities that exist in large organizations. Sometimes these complexities are made more traversable when somebody creates enough value for the business. The section “Best Practices: Creating Value for Your Business” gives good pointers. We all could do a better job at highlighting the business value of our IT projects.

Other sections of the article include:

  • Best Practices: Initial Design
  • Best Practices: Specifying Hardware
  • Best Practices: Simplify the ETL Process and Improve Performance
  • Best Practices: General
  • Best Practices: Date/time
  • Best Practices: Compression and Encryption
  • Best Practices: Partitioning
  • Best Practice: Manage Multiple Servers Uniformly
    • Use MOLAP writeback instead of ROLAP writeback. This caught my attention because PerformancePoint Server uses writeback only for what-if analyses, when changes are submitted to a model; they are then written back to the database, which then refreshes the cube. To improve performance, PPS also uses pro-active caching, which is another Enterprise-only feature of SSAS. 
  • Best Practices: Data Presentation
  • Best Practices: Performance
  • Best Practices: System Architecture and Performance