CODEWITHSUNDEEP
ssasdata-warehouse
maguy
maguy

Reputation: 1699

Thoughts on dimension measures for BI

I am working with a consultant who recommends creating a measure dimension and then adding the measure dimension key to our fact table.

I can see how this can make adding new measures easier by just adding rows instead of physically creating columns in the fact table. I can also see how this can add work to the ETL process, adds another join to the star schema, one generic column in fact table to hold all measure data etc.

I'm interested in how others have dealt with this situation. We currently have close to twenty measures.

Upvotes: 3

Views: 680

Answers (2)

ConcernedOfTunbridgeWells
ConcernedOfTunbridgeWells

Reputation: 66682

SSAS will do this, and I know of a major vendor of insurance policy administration software that provided a M.I. solution for their system that works like this. You do get some flexibility from the approach in that you can add measures without having to deploy a build of the cube, although for 20 measures I don't think you need to worry about that.

'Measures' is essentially another dimension (and often referred to as such in the documentation). I believe SSAS uses a largely column-oriented structure behind the scenes.

However, a naive application of this approach does have some issues that could come and bite you to a greater or lesser extent.

  • You only have one measure, [Value], [Amount] or whatever it's called. If your tool won't let you inject calculated measures at the front-end then you can't sort the whole data set on the value of one of your attribute types. ProClarity and report builder >=2.0 will do this but Excel won't.

  • You can't do ratios or other calculated measures in this way. You will have to either embed them in the cube script (meaning you need to deploy a build to add them) or use a tool that lets you define them in the client.

  • Although it doesn't make a lot of differece to the cube it will be slow to query on the database and increase storage requirements. It's also fiddly to query on the database.

Upvotes: 1

Pondlife
Pondlife

Reputation: 16260

Instinctively, I don't like it: it's the EAV model, which is not very popular (you can Google the reasons why).

  • The EAV model is generally considered to be a headache to query and maintain
  • Different measures go together with different dimensions; this approach could easily turn into "one giant fact table for everything" instead of multiple smaller fact tables for specific reporting areas
  • I suspect you would end up creating views to give the appearance of multiple fact tables anyway
  • You will multiply the number of rows in your fact table by the number of measures, resulting in a much bigger physical table
  • Even with a good indexing/partitioning scheme, queries that include more than one measure will have to read a lot more rows to get the data
  • What about measures with different data types?
  • Is this easily supported in your reporting tool?

I'm sure there are other issues, but those are the ones that come to mind immediately. As a rule of thumb, if someone suggests an EAV implementation in any context, you should be very wary and ask them exactly what advantages it offers and how it will be managed as the data and complexity increase. But I think you've already identified some key areas of concern.

Upvotes: 3

Related Questions