Measuring Data Warehouse Performance

Now that you have a good design (see posts on documentation and data model), it is time to focus on the performance of the data warehouse from both the loading of the data warehouse from the source systems via ETL to the running of the reports and presentation of analytics to the end user.

Below is a checklist of the top performance techniques you can use to enhance the performance of your warehouse.

• Design for Performance – Review your data model design to find performance optimizations.
• Use Bitmap Indexes – a compressed index used on columns where the distinct number of values is limited.
• Use Parallel DML/DDL Execution – use multiple processors to execute a single DML/DDL statement to reduce the loading of data or the creation of indexes for the data warehouse.
• Partition Large Data Structures – allows you to decrease the number of records you are working with by focusing on the subset of data contained within one or more partitions of the large table.
• Compress Data – reduces the overall size of the table allowing better read and data transfer performance.
• Solid State Drives – include Solid State Drives as a part of your tiered storage solution to store and deliver the data most frequently used by the application.
• Remove/Disable Indexes – when loading the data warehouse ensure only necessary indexes are enabled.
• Disable Logging – because the warehouse is not the authoritative source of data, remove all logging for DML and DDL transactions.

These performance techniques are not a panacea for all of your performance issues, but some combination of one or more of these actions can improve the performance of your data warehouse.

In conclusion, focusing on the documentation, design and performance of the data warehouse will lead to successful, long-lasting data warehouse solutions.

Related Posts

• What Are Data Warehouses?
• Top 3 Requirements for Creating a Data Warehouse Solution
• Data Warehousing Documentation Requirements
• Data Model Best Practices for Data Warehousing