Advice on Measures that have properties on their own

database-designfacttable

I'm looking for advice on how to model a database when the measures have an increasing number of properties – the values of which could be considered dimensions in their own right.

In a simplified case, let's say I have StateId and Sales:

StateId    Sales
----------------
1          20
2          25

Then later I need to store information about what type of measure 'Sales', a custom group it needs to be in, and say, an aggregation rule.

What would be a good way to model this? I could have a Measure table which links to other dimensions that contain Types and Groups.

Id  Name    TypeId  GroupId    Agg
-----------------------------------
1   Sales   1       5          Sum

But then I'd need to store what type of dimension it was, whether it relates to a real dimension or one that relates to a measure.

Curious as to how others have solved the problem of storing meta-data for a measure.

Best Answer

It sounds like you want to put aggregation rules (based on anything, doesn't matter) in the database. Nothing particularly wrong with that, unless you're planning to use it to generate SQL dynamically. That's a slippery surface: proceed with caution, because SQL is much easier to debug when you can see it.

Will the users be able to add to your putative Measure table? If not, you might want to consider statically generating the SQL as stored procedures. That would be easier to understand, both for the human being and likely for the query optimizer.

I'm also a little leery of your question because ISTM you're not quite sure yet what the properties of the measures will be. In such a situation, it's often best to wait until a dozen or so use cases develop. At that point, you'll have a concrete set that you can actually use, with identified properties. Then you'll know enough that I expect the question will answer itself. It wouldn't surprise me, though, if that many cases never do develop, in which event the Measure abstraction will have proved more trouble than it would have been worth, and was fortuitously avoided.

HTH.