Dimension fields depend on other fields

View previous topic View next topic Go down

Dimension fields depend on other fields

Post  rendybjunior on Tue Nov 04, 2014 7:39 am

I am creating dimension of product properties for sales facts.

Property of product depend of product type. For example:
- Type = smartphone. Properties = model, OS, size
- Type = book. Properties = author, title

How dimension should be for this case?

Should I create dimension which contain ALL properties? In this case dimension content will be sparse, there will be many null values.
Code:
|----------------------------------------------------|
| DimKey | Type | Model | OS | Size | AUTHOR | TITLE |

OR, should I create dimension for each? In this case sales fact will have many FKs.
Code:
|-------------------------------------------------------------|
| FactKey | Quantity | Total | Book_FK | Smartphone_FK | .... |

Is there any other way to do this?

rendybjunior

Posts : 7
Join date : 2014-09-30

View user profile

Back to top Go down

Re: Dimension fields depend on other fields

Post  ngalemmo on Tue Nov 04, 2014 12:50 pm

You could use sub-types. That is, a product dimension with the common, generic attributes, as well as sub-type dimensions with additional columns for specific types of products. The primary key would be the same for the product dimension and it's sub-type, so you need only a single key in the fact.

If someone is doing an analysis of products, they would just join to product and work with the common attributes. If someone is doing an analysis of books they would use the product and book dimensions. Since the book dimension only contains books, it would naturally filter out all non-book products from the query.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Dimension fields depend on other fields

Post  rendybjunior on Tue Nov 04, 2014 10:23 pm

awesome. thanks!

rendybjunior

Posts : 7
Join date : 2014-09-30

View user profile

Back to top Go down

Re: Dimension fields depend on other fields

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