Is a dimension a table or an attribute?

[ryan.shirley]

After working in dimensional modelling for quite a while and reading the toolkit books, the forums, the web, etc. one stubborn question continues to knock my confidence in categorizing dimensions into "types". Are dimensions truly collections of attributes, such as in a table? Or are they the attributes themselves?

The book is actually a little ambiguous on this because it does not explicitly forbid having both Type 2 and Type 1 attributes in the same table. I have seen this very frequently in practice but I do not know whether to simply call the attributes Type 1 and Type 2, or whether having this combination makes the dimension *table* something else - like a type 6.

A pointer to official Kimball doctrine that solves this would be hugely appreciated and rewarded with much good karma.

[ngalemmo]

A dimension is a table that contains attributes. Type describes how the attributes are maintained and the basic structure of the table.

[Jeff Smith]

Actually, a Dimension Table is a Table. A Dimension is a collection of attributes. In a Star Schema database, the Dimensions are in tables and a Table can contain more than one dimension.

You can put dimensions with the fact, but it wouldn't be a star schema.

Cubes use dimensions.

[BoxesAndLines]

In the OLTP world, normalization is your friend. It defines how the tables should be structured based on relational theory to reduce redundancy within the database. The OLAP world is quite different. There are only best practices and guidelines. This leads to the wide range of dimensional models seen in the industry. Have you seen IBM's Healthcare Dimensional model? All the metrics are in the dimensions. My best advice is to not throw normalization out of the window as it is still useful in identifying related groupings of attributes. Read Kimball's modeling book and leverage his design patterns. With practice comes confidence that your design will work successfully.

[ryan.shirley]

In a simple case where the dimensional model is implemented as tables on an RDBMS, what would be the "type" of the dimension I mentioned above. There are type 2 attributes that cause a new row when they change, but there are also separate type 1 attributes that maintain the same (current) value across all rows. e.g.

DimCustomerId	Province	CurrentPhone	CustNK	EffectiveFrom	EffectiveTo
001	Alberta	403-777-7777	111111	2013-01-01	2013-01-15
002	B.C.	403-777-7777	111111	2013-01-16	9999-12-31

In the table above, Province is type 2 but CurrentPhone is type 1. If the phone number changes, all rows are updated. There are no type 3 attributes. So what "type" of dimension is this?

Thank you for the replies.

[BoxesAndLines]

Type 2.

[ngalemmo]

The Type primarily describes the structure of the table.

Type 1 has one row per member (natural key).
Type 3 has one row per member with historical values for one or more attributes.
Type 2 has multiple rows per member covering attribute states for different points in time.

All dimension tables fit into one of these three structures.

There are variations like the one you cited, and various people have felt they needed different numbers, but all that does is confuse things. Frankly, I've seen different definitions for the same numbers (above 3) and have a hard time figuring out what someone means by a type 4 or 6 or whatever.

[Sponsored content]