I was recently working with a client and saw an interesting approach to a classic problem dealing with holidays as a dimension. Now maybe this is a common solution, but I hadn’t seen it before, so I thought I’d share. This same solution could be used for similar problems as well.
The goal is to be able to analyze the impact of holidays on various measures. Imagine you are analyzing sales or hours worked. Knowing if a particular day is a holiday is pretty important to understanding spikes in the data.
A first approach might be to have the holiday as a property in the date dimension. That would only work if the data you are dealing with has the same holiday for all data that points back to that particular date. This isn’t even a true case for the United States, where some states have their own holidays, much less on a global scale.
So what this client did, was solve the problem at the ETL level. For each fact, they check with the client calendar and see if the data is for a holiday or not and then set it in the fact table as a degenerate dimension. You could have a separate dimension as well, but they decided to avoid the join that would get created with Mondrian. Just make sure to index that column so you don’t do a table scan when grabbing the members.
A simple solution to what at first might appear to be a complex problem. I like that.
A quiet, maybe too quiet, new feature of Analyzer in Pentaho 4.8 was the addition of the AnalyzerBusinessGroup annotation. This annotation will let you specify that a measure should go into a specific group rather than be lumped in with a bunch of measures. If you have just a few measures, it’s not that big a deal. But many users have a lot of measures that can be categorized and it would be nicer to have them in separate groups. I have not tried this with dimensions, but if you define them correctly it seems that it would be overkill. I also suspect that Mondrian 4′s Measure Groups will make this obsolete, but don’t know that for a fact.
Using the feature is very simple. Just add an annotation to the measure and specify the AnalyzerBusinessGroup. For example:
results in the following (using the Steel Wheels example):