Is a dimension a table or an attribute?

View previous topic View next topic Go down

Is a dimension a table or an attribute?

Post  ryan.shirley on Tue Feb 12, 2013 6:19 pm

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.

ryan.shirley

Posts : 3
Join date : 2011-05-11
Location : Calgary, AB, Canada

View user profile http://ryanshirley.com

Back to top Go down

Re: Is a dimension a table or an attribute?

Post  ngalemmo on Tue Feb 12, 2013 6:46 pm

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

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Is a dimension a table or an attribute?

Post  Jeff Smith on Tue Feb 12, 2013 7:26 pm

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.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Is a dimension a table or an attribute?

Post  BoxesAndLines on Wed Feb 13, 2013 10:39 am

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Is a dimension a table or an attribute?

Post  ryan.shirley on Wed Feb 13, 2013 12:48 pm

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 CustNKEffectiveFromEffectiveTo
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.

ryan.shirley

Posts : 3
Join date : 2011-05-11
Location : Calgary, AB, Canada

View user profile http://ryanshirley.com

Back to top Go down

Re: Is a dimension a table or an attribute?

Post  BoxesAndLines on Wed Feb 13, 2013 1:37 pm

Type 2.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Is a dimension a table or an attribute?

Post  ngalemmo on Wed Feb 13, 2013 2:29 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Is a dimension a table or an attribute?

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum