We are running out of extension field in fact tables (of a star schema dimensional model) and considering the creation of extension fact tables for additional extension fields. We are getting to the point where it is almost impossible to fit any new data elements into the fact tables we currently have. My proposal is that we consider creating “extension” or “snowflake” fact tables to hold additional attributes for a given subject matter. This would avoid the problems associated with embedding extra columns in the current fact tables when upgrades to the Informatica data model are required." Can you please provide me with your recommendation and/or the "dos" and "don'ts" of such a design strategy?


Chuck Kelley’s Answer: I have to admit that I do not know what an extension field means. I will assume that it means you are adding a new column (extending the table). I guess the other possibility is that you are using a product that allows you to “extend” their basic structures or they have a fixed number of “extension” fields already defined and you have almost used them all. So, using my assumption, unless your fact table is starting to approach 2,000 columns (the limit of some relational databases), I don’t think that I would break up a fact table, if indeed those facts are comparable items. However, the term snowflake is not appropriate for the fact table. A snowflake is a normalization of the dimensions. It should not be possible to normalize the fact table. If you can in your system, then I would expect your star schema is not constructed properly. As for breaking up the fact table, you could always do this; just make sure that all the dimension keys are still valid for these new fact measures. The only thing that you should watch out for is that some end-user products do not deal well with multiple fact tables sharing the same dimensions. It creates loops in their joins and they will not be able to generate the SQL to run.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access