Search code examples
ssisssasdata-warehouse

Creating fact tables

I understand the logic behind fact tables and what it should contain. What I would like to know is how to create these fact tables.

Can these fact tables be a view or named query or does it have to be an actual table? What are the advantages/disadvantages of doing one or the other way?

Solution

  • Yes, technically, a fact table can be a view or named query.

    Advantages

    • no additional disk space required :-)
    • zero data-availability latency (data is available as soon as it is inserted into your source)

    Disadvantages

    • does not scale
    • slower query response speed (varies based on complexity of view definition)
    • does not scale
    • increased maintenance complexity (this is HUGE btw)
    • does not scale
    • reduced flexibility
    • no surrogate keys
    • no type2 SCDs
    • indexing issues
    • does not scale

    FWIW - I've build a small star-schema (3 fact tables, 10 dimensions) using views but it was simply for a proof of concept and I strongly oppose this for production use.