During the first meeting with one of my prospects (a large Italian tire and cable manufacturer) the IT person responsible of the DW in the company told me: "I do not agree on building a dimension from a large product (item master) table, dimensions over 100,000 members should not be called dimensions, they should be treated as Fact tables." This could be true when I need to track the changes of many attributes, but building a fact from the product creates a big hassle when I need a product dimension to analyze other key performance indicators (KPIs). Any recommendations?


Chuck Kelley’s Answer: This is a common issue with people who do not understand dimensions and facts. I once taught a class where a student was adamant that Customer_Name was a fact, not a dimension. No matter how I tried to explain that facts are measures and dimensions are how you want to look at measures by (i.e., you have the measure (fact) and the metric (dimension)), there was no changing his mind. His basis was “I am Chuck Kelley and that is a Fact.” Of course, then everything becomes a fact! The size of the dimension (from a logical standpoint) should never determine whether something is a fact or a dimension. I think you need to get away from the words “dimension” and “fact” and try to build a model that supports the business requirement. Clay Rehm’s Answer: I cannot see how a true Product would be a fact table. 100,000 rows in a dimension table is not a big deal. For example, a Customer table is a dimension table and can have millions of rows. Anyway, to do proper dimensional analysis, you need a Product dimension table, and if there are changing attributes about that product dimension, create a snowflake table off of the Product table.

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