1

my question is:

Should business logic be stored in the data warehouse data store? Or should it only exist in the reporting applications?

To give a more concrete example:

In the CRM operational system there are abstract concepts of leads and opportunities, and lead statuses and opportunity stages. These concepts are very common, well understood, and used across different CRM tools.

And business users can legitimately ask questions like, what is the conversion rate for all leads from status "raw lead" to status "inside sales qualified lead".

Then for the data warehouse designer, the question becomes: 1. When someone changes a lead status from x to y after a call, should that be a row in a fact table somewhere? (perhaps in the same row as the call) If yes, then we are storing abstract business concepts, rather than just real world events, in a fact table. 2. And the bigger question is, should the data warehouse even be aware of terms like "leads" and "opportunities"? If yes-- 2. Should the fact table have columns for old_status and new_status? 3. Or, should status change be handled as a slowly changing dimension?

If business logic is stored directly in the data warehouse, a lot of business questions become easier to ask. (lead status conversion metrics, opportunity stage advancement velocity metrics, and etc) But it seems more brittle to changes, is more complex to implement, and perhaps pollute the "verbs are facts and nouns are dimensions" paradigm.

How should I approach this design, and what are the best practices and guiding principles here?

Max

  • Why wouldn't you store business information data in a BI warehouse? Seems to me the exact scenario for a BI warehouse. – Max Vernon Sep 18 '14 at 1:08
  • @MaxVernon The question here is whether to store both real-world business events and abstract business information, or only real-world events. Certain events can cause changes in abstract business definitions, but the criteria may change over time. So there's always the risk to accidentally tie the DW to operational definitions. – Max Sep 18 '14 at 17:40
  • Isn't the status of the lead more suitably modeled by a SCD Type II or III property? – nsousa Sep 19 '14 at 8:48
2

My answer is a big YES - you should store business logic in data warehouse. That is one of the ideas of data warehouses in the first place. If you have tens of reports showing or filtering leads imagine if a rule to qualify someone as lead would change. Also, what if you have to access DWH data with different tools/systems - you'd need to replicate all the logic. Lastly, calculating this kind of labels can be time consuming and that's why you'd like to have everything precalculated in DWH for the reports to run fast.

To answer your first question - yes, you should go with SCD. There are couple apporaches and you should choose the one that suits best your requirements - it's all about how users want to analyze data (this is where the desing should start.

1

My approach has always been to store as much business logic in the database layer. The reason for this is that if you change the reporting layer, then you need to rewrite all the business rules. It is more likely that you'll change the reporting layer, than the database layer as this is what your department sees, not the database. Also I'm seeing many companies use multiple different BI reporting solutions which means the database is the only place you can store your rules without repeating them. When I'm talking about the database layer here, I'm also referring to cubes.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.