Star/Dimension Model question:Should DIM tables have only 1 attribute? 3

[jw970170]

Hi,

I am just starting learning about the star model and am wondering if a dimension table can contain only one attribute. For example, check out the tables below

FACT_TABLE
StreamTypeID (foreign Key)
Value

DIM_STREAM_TYPE
StreamTypeID (key)
StreamType

The DIM_STREAM_TYPE table really only contains one attribute, StreamType (StreamTypeID is just a generated number used as a surrogate key). And suppose that StreamType could contain one of 10 values. It is a dimension. I could wish to view my data as a sum of the first stream type, or second stream type, etc. However, I could just as easily include the dimension in the fact table. How would one generally handle this situation?

 

[MDXer]

A dimension table can contain as many attributes about a dimension member as are required. Depending on the Olap or reporting product being used you can often expose these Member attributes for various types of reporting.

A good example would be a product dimension

If you Structured your dimension is a fashion where product was the leaf level yo0u may carry Product color as an attribute of product by exposing Color you would then be able to report on Red vs Blue of the same product without having to carry a level under Product.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[Z3]

The short answer for you is YES, you can have a dimension table with one attribute and that's very common. You still save space and memory when StreamType presumably as a text string compares to it's ID in number format.

 

[johnherman]

The dimension should contain all the attributes which are associated with that dimension (entity) at that level of hierarchy (if applicable).

For instance, the Customer Dimension could contain the customer's credit rating, credit limit, and terms. It might not contain the customer address if the customer has more than one address (several locations, bill-to and ship-to addresses, etc). The Customer Dimension might or might not contain Customer Contacts and Phone numbers for the same reason.

For more info on Star Schemas, see faq353-5189


-------------------------
The reasonable man adapts himself to the world. The unreasonable one persists in trying to adapt the world to himself. Therefore all progress depends on the unreasonable man. - George Bernard Shaw

 

[teccum]

Generally speaking you can have as many attributes within the dimension tables, but sometimes even though you design your schema irrespective of the OLAP tools, you might have to adjust your model as per the tool you are using.

teccum