What are the best practice for big fact table?

What are the best practice for big fact table?

Most of these tips are based on experiences building large data warehouses on SQL Server 2005.

• 1 – Consider partitioning large fact tables.
• 3 – Choose partition grain carefully.
• 4 – Design dimension tables appropriately.
• 5 – Write effective queries for partition elimination.
• 6 – Use Sliding Window technique to maintain data.

What is a fact table example?

An example of a fact table. In the schema below, we have a fact table FACT_SALES that has a grain that gives us the number of units sold by date, by store, and product. All other tables such as DIM_DATE , DIM_STORE and DIM_PRODUCT are dimensions tables. This schema is known as the star schema.

How can partitioning the fact table improve performance?

Partitioning of the fact table is one of the option to improve performance. While a compression aggregates data this optimization is changing the structure of the data on the database. By partitioning the data the fact table is physically split into multiple tables. There is a couple advantages with partitions.

How to improve performance by compressing the fact table?

Even with “high performance hardware” the datavolume in a datwarehouse can get big enough that everyday loaded InfoCubes cause perfromance issues. For that reason bw provides a couple features that help to increase performance. Compressing the fact table is one option that optimized the access to basis infocubes. What is a compression exactly?

How to improve the performance of the date column?

Rather than implementing the date filter at the report level with the date column, you can improve the performance of the table (with totals displayed) by implementing the date filter within the fact table source query or view object such as the following predicate:

How can sap reduce the size of the fact table?

Those packages do not allow an aggregation and therefore each datapackage is limited within those boundaries. An aggregation of the data across datapackages by removing the request id can shrink the datavolume of the factable dramatically without any downfall from a business perspective.