Data Warehouse Physical Design

Data Warehouse Physical Design

Data files guidelines

  • Create files with an initial size 
    • Based on the eventual size of the objects that will be stored on them 
    • This pre-allocates sequential disk blocks and helps avoid fragmentation. 
  • Disable autogrowth 
    • If you begin to run out of space in a data file, it is more efficient to explicitly increase the file size by a large amount rather than rely on incremental autogrowth. 

Filegroups guidelines

  • Create at least one filegroup in addition to the primary one, and then set it as the default filegroup so you can separate data tables from system tables.
  • Create dedicated filegroups for extremely large fact tables and using them to place those fact tables on their own logical disks.
  • If some tables in the data warehouse are loaded on a different schedule from others, consider using filegroups to separate the tables into groups that can be backed up independently.
  • If you intend to partition a large fact table, create a filegroup for each one so that older, stable rows can be backed up, and then set as read-only.

Staging tables

  • Separate staging database  
    • Create it on a logical disk distinct from the data warehouse files. 
  • Into the data warehouse database 
    • Create a file and filegroup for them on a logical disk 
    • Separate from the fact and dimension tables. 
    • An exception to the previous guideline is made for staging tables that will be switched with partitions to perform fast loads. 
      • These must be created on the same filegroup as the partition with which they will be switched.

TempDB

  • To avoid fragmentation of data files 
    • Place it on a dedicated logical disk 
    • Set its initial size based on how much it is likely to be used. 
  • Set the growth increment to be quite large to ensure that performance is not interrupted by frequent growth of TempDB. 
  • Creating multiple files for TempDB to help minimize contention during page free space (PFS) scans as temporary objects are created and dropped.

Transaction logs

  • Set the transaction mode of the Data Warehouse, Staging Database and TempDB to Simple 
  • Helps to avoid having to truncate transaction logs 
  • Additionally, most of the inserts in a data warehouse are typically performed as bulk load operations which are not logged. 
  • To avoid disk resource conflicts between data warehouse I/O and logging, place the transaction log files for all databases on a dedicated logical disk.

Data Compression

  • SQL Server Enterprise Edition supports data compression at both page and row level.
  • Data compression benefits in a data warehouse
    • Reduced storage requirements.
    • Improved query performance 
  • Best practices for data compression in a data warehouse 
    • Use page compression on all dimension tables and fact table partitions. 
    • If performance is CPU-bound, revert to row compression on frequently accessed partitions.

Table Partitioning

  • Improved query performance 
  • More granular manageability
  • Improved data load performance
  • Best practices for partitioning in a DW
    • Partition Large Fact Tables
    • Partition on an incrementing date key
    • Design the partition scheme for ETL and manageability.
    • Maintain an empty partition at the start and end of the table

Indexes in DW

  • Indexes maximize query performance 
  • Planning Indexes is the most important part of the database design process 
  • Some inexperienced BI professionals are tempted to create many indexes on all tables to support queries.

Dimension table indexes 

  • Create a clustered index on the surrogate key column.
    • This column is used to join the dimension table to fact tables, and a clustered index will help the query optimizer minimize the number of reads required to filter fact rows.
  • Create a non-clustered index on the alternate key column and include the SCD current flag, start date, and end date columns.
    • This index will improve the performance of lookup operations during ETL data loads that need to handle slowly-changing dimensions.
  • Create nonclustered indexes on frequently searched attributes, and consider including all members of a hierarchy in a single index.

Fact table indexes

  • Create a clustered index on the most commonly-searched date key.
    • Date ranges are the most common filtering criteria in the most data warehouse workloads, so a clustered index on this key should be particularly effective in improving overall query performance.
  • Create nonclustered indexes on other, frequently-searched dimension keys.
  • Columnstore index on all columns.

Using Views in a DW

  • Create a view for each dimension and fact table with NOLOCK query hint in the view definition Create views with a user-friendly view and column names
  • Do not include metadata columns in views
  • Create views to combine snowflake dimension tables
  • Partition-align indexed views
  • Use the SCHEMABINDING option
  • Security

Comments

Post a Comment

Popular posts from this blog

Maxpooling vs minpooling vs average pooling

Image
Pooling is performed in neural networks to reduce variance and computation complexity. Many times, beginners blindly use a pooling method without knowing the reason for using it. Here is a comparison of three basic pooling methods that are widely used. The three types of pooling operations are: Max pooling: The maximum pixel value of the batch is selected. Min pooling: The minimum pixel value of the batch is selected. Average pooling: The average value of all the pixels in the batch is selected. The batch here means a group of pixels of size equal to the filter size which is decided based on the size of the image. In the following example, a filter of 9x9 is chosen. The output of the pooling method varies with the varying value of the filter size. The operations are illustrated through the following figures. Average, Max and Min pooling of size 9x9 applied on an image We cannot say that a particular pooling method is better over others generally. The choice of pooling operation is made...
Read more

Percentiles, Deciles, and Quartiles

Image
Percentiles Percentile: the value below which a percentage of data falls. Example: You are the fourth tallest person in a group of 20 80% of people are shorter than you: That means you are at the  80th percentile . If your height is 1.85m then "1.85m" is the 80th percentile height in that group. In Order Have the data  in order , so you know which values are above and below. To calculate percentiles of height: have the data in height order (sorted by height). To calculate percentiles of age: have the data in age order. And so on. Grouped Data When the data is grouped: Add up all percentages  below  the score, plus  half  the percentage  at  the score. Example: You Score a B! In the test 12% got D, 50% got C, 30% got B and 8% got A You got a B, so add up all the 12% that got D, all the 50% that got C, half of the 30% that got B, for a total percentile of 12% + 50% + 15% =  77% In other words you...
Read more

Data Warehouse Vs Data Mart

Image
What is Data Warehouse? A Data Warehouse collects and manages data from varied sources to provide meaningful business insights. It is a collection of data that is separate from the operational systems and supports the decision making of the company. In Data Warehouse, data is stored from a historical perspective. The data in the warehouse is extracted from multiple functional units. It is checked, cleansed and then integrated with the Data warehouse system. Data warehouse used a very fast computer system having large storage capacity. This tool can answer any complex queries relating to data. What is Data Mart? A data mart is a simple form of a Data Warehouse. It is focused on a single subject. Data Mart draws data from only a few sources. These sources may be central Data warehouse, internal operational systems, or external data sources. A Data Mart is an index and extraction system. It is an important subset of a data warehouse. It is subject-oriented, and it is desig...
Read more