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

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

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.

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.

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

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.

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

SK	ReforecastValueType
1	Budget
2	Actual

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.

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.