Column count on dimension table

View previous topic View next topic Go down

Column count on dimension table

Post  DMMay on Fri Mar 08, 2013 4:46 pm

I'm currently modeling a star schema for data mart and fairly new to the process. I have one dimension (dim_Request) that has a lot of columns (276). Is there a breaking point in the number of columns recommended for a dimension? Do I need to normalize this dimension to get the column number down?

Thanks

DMMay

DMMay

Posts : 1
Join date : 2013-03-08

View user profile

Back to top Go down

Re: Column count on dimension table

Post  ngalemmo on Fri Mar 08, 2013 5:43 pm

Column count isn't a determining factor per-se. But, you should go through an exercise to see where you may have embedded useful dimensions in the table.

There is also a common design error where dimensions are misused to record events. That is the domain of a fact table. Is your dimension describing the request or is it recording the request?
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Column count on dimension table

Post  LAndrews on Mon Mar 11, 2013 1:28 pm

ngalemmo wrote: Is your dimension describing the request or is it recording the request?

Describing vs Recording - I like it. This is an effective way to differentiate the thinking about dimensions versus facts. Recording is more appropriate than "Measuring".

LAndrews

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

View user profile

Back to top Go down

Re: Column count on dimension table

Post  pnosko on Fri Mar 15, 2013 6:08 am

Yes, describing vs. recording is interesting.

I have a (type 2) dimension that represents a service agreement. In the OLTP system, this entity has a repeating attributes child table for a rate code and effective date. The rate code is a FK to a rate schedule entity table. So this child table is a M:M join table.

From your description, it sounds like this is recording an event. But in the OLTP system, the application allows them to change the rate code for a historical (not most current) effective date.

How would you model this? Should this repeating data be a) flattened in the service agreement dimension or b) create a snowflaked dimension where these "events" are in a child dimension or c) stored as facts? I'm uncertain how a changed (corrected) historical rate code would work in a fact table. We would want to know that this change was made (and when). So I'd prefer to go with option A since the type 2 model would record the event change. The problem with flattening is deciding on the limit (number of repeating entries) since column changes in a dimension are an expensive change.

Thoughts?

pnosko

Posts : 1
Join date : 2013-03-15

View user profile

Back to top Go down

Re: Column count on dimension table

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