Search code examples
data-warehouseolap

OLAP fact table empty records

I'm in the process of designing a fact table for olap lookups. Currently, I won't be allowing the user to run olap queries, like custom dimensions or slicing. I will be creating the queries myself to run specific reports.

My question is on the fact table for these reports. I want to avoid updating existing records, and just insert rows for multiple dimensions. For example:

Two inserts:

YEAR    AMOUNT
2016    1
2016    1

Instead of one insert, check if year=2016 exists, and if so then one update:

YEAR    AMOUNT
2016    2
Solution

  • Please see:

    https://dba.stackexchange.com/questions/138409/fact-table-with-blank-dimensions/138515#138515

    The issue I was facing was trying to put all facts into one table. I learned the best practice is to break up facts into different tables for different granularity, and limit columns to the minimal needed for the fact.

    It's extra work inserting the data, but really pays off during retrieval which is the bulk of the database work.