Should I use a degenerate dimension or create a junk dimension?

View previous topic View next topic Go down

Should I use a degenerate dimension or create a junk dimension?

Post  min.emerg on Fri Feb 25, 2011 7:06 am

Hi everyone,

I have a FACT table that contains reforecast data. Reforecasts are part 'actual' transaction data, part 'budgeted' data, both of which are stored in the same reforecast FACT table. I want to add a bit field to the FACT table to distinguish between reforecast values that are 'actuals' and those that are 'budgets'. Would it be suitable to add a degenerate dimension to the FACT table in the form of a bit field, or would a better approach be to move this bit field to a junk dimension (containing two records, one for each combination) and then add a foreign key in the FACT table that links to it?

Thanks

min.emerg

Posts : 39
Join date : 2011-02-25

View user profile

Back to top Go down

Re: Should I use a degenerate dimension or create a junk dimension?

Post  hang on Fri Feb 25, 2011 7:23 am

Firstly I would not call the kind of dimension you described a junk dimension as it's a normal dimension with a single attribute.

But more importantly, you should not mix actual and budgeted values into single measure column in the fact table as they are different types of measures. If they share the same dimensionality and are of the same grain (eg. both daily or monthly) in the fact table, they should be in two separate measure columns of the same fact table. Otherwise they need to be in separate fact tables.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Should I use a degenerate dimension or create a junk dimension?

Post  Jeff Smith on Fri Feb 25, 2011 9:54 am

I can see why you might want to have a single measure with actual and forecast/budget data and something differentiating the 2 - it would make it easy to create a line graph with a single line where the actual is one color and the budget is a different color.

I would be inclined to keep the values in the seperate columns. But this would make it a little harder to create the graph because you would potentially have 0s in the actual for future periods. If you have the date for the current data somewhere in the datebase (I usually have a little table that has the current Year/Month or Week), you can create a virtual column with the combined data either in a view, in the reporting software's interface, or in the query used to create the graph.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Should I use a degenerate dimension or create a junk dimension?

Post  min.emerg on Mon Feb 28, 2011 2:24 am

Hi Hang and Jeff,

Thanks for your prompt responses, and apologies for my delayed one.

The two components of the FACT table (actuals and budget) both have exactly the same granularity and dimensionality.

I understand the reasoning that you put forward - that I should rather have two measure columns, one for the actual component and one for the budget component. However, I would imagine that I would still require either a degenerate dimension or additional dimension to distinguish between actuals and budgets (seeing as both can have legitimate values of 0 for a single record). If not, what value, for instance, would be put in an actual's "budget" measure column (besides 0)? Zero might be suitable as it will not affect the aggregation.

Thanks again.

min.emerg

Posts : 39
Join date : 2011-02-25

View user profile

Back to top Go down

Re: Should I use a degenerate dimension or create a junk dimension?

Post  hang on Mon Feb 28, 2011 9:35 pm

min.emerg wrote:However, I would imagine that I would still require either a degenerate dimension or additional dimension to distinguish between actuals and budgets

Why? Actual and budget are in separate columns and the column name will tell you wich is what. Using zero for non-applicables is fine as it will certainly work with aggregates.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

future maintainability

Post  flwr on Thu Mar 03, 2011 8:29 am

Dear,

There would not be much difference for choosing either one of the suggested approaches. Maybe when your fact table becomes very large, there might be a difference in processing time. But I prefer maintainability above design tuned for optimal performance in the long end.

So, at this stage in your design process, I'd go for a degenerated dimension.

If you choose for a junk dimension with 1 attribute, sooner or later you might want to add another "flag" or "junk" attribute to your junk dimension. This would probably lead to reassigning your foreign keys to the junk dimension rows.

If you go for the degenerated dimension now, you can always revert to a junk dimension with one of more attributes.

flwr

Posts : 1
Join date : 2011-03-03

View user profile http://www.kohera.be

Back to top Go down

Re: Should I use a degenerate dimension or create a junk dimension?

Post  VHF on Thu Mar 03, 2011 6:20 pm

If the reforecast has only one value per fiscal period--either an actual or a budget value--then I don't see a problem with using a single measure field to hold either type of value as was orignally proposed.

An advantage to using a standard dimenssion (with a single attribute) is that there would be a description of the type of value to use to support reporting or analysis. With only two records required, the primary key could be a 1-byte integer, so you wouldn't incurr any space penalty in the fact table over a DD or a boolean flag.

DimReforecastValueType

SKReforecastValueType
1Budget
2Actual


Last edited by VHF on Thu Mar 03, 2011 6:21 pm; edited 1 time in total (Reason for editing : clarification)

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Re: Should I use a degenerate dimension or create a junk dimension?

Post  Ashish Mishra on Fri Mar 04, 2011 9:28 am

If gain and dimension is same for both the information than it will be wise to kepp them in separate column of same fact table as this will allow
the ease of down the line reporting specially reports that have comparison between these two measures.

Keeping both of these measures in same column might case some issue while doing comparison reports.

Ashish Mishra

Posts : 11
Join date : 2011-02-22

View user profile

Back to top Go down

Re: Should I use a degenerate dimension or create a junk dimension?

Post  hang on Fri Mar 04, 2011 7:11 pm

flwr wrote:If you choose for a junk dimension with 1 attribute, sooner or later you might want to add another "flag" or "junk" attribute to your junk dimension. This would probably lead to reassigning your foreign keys to the junk dimension rows.

If you go for the degenerated dimension now, you can always revert to a junk dimension with one of more attributes.
Firstly the dimension in this regard does not exist, so there is nothing to junk about. Secondly, a flag or any low cardinality attribute should not be thrown into any fact table and call them degenerate dimension. The attributes of those kinds should either belong to a dimension, junk or non junk, and have a SK in the fact table.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Should I use a degenerate dimension or create a junk dimension?

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