CODEWITHSUNDEEP
ssisssasdata-warehouse
m0g
m0g

Reputation: 969

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?

Upvotes: 2

Views: 1040

Answers (1)

Bill Anton
Bill Anton

Reputation: 2970

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.

Upvotes: 3

Related Questions