Data Warehousing – Common Mistakes and How to Avoid Them

This is a very glib answer, but getting right to the heart of the matter:

In terms of DDD maybe think of reporting as a Bounded Context?, so rather than thinking in terms of "THE" domain model, you should be willing to think that it's okay to have more than one model. So yes it's okay if the reporting domain has reporting business logic in it, just as it's okay for the transactional domain to have transactional business logic in it.

As to the question of, say SQL stored procedures vs. domain model in application code, the same pros and cons apply for the reporting system as for the transactional system.

Since I see that you added a bounty to the question, I read the question again and noticed that you are asking for specific resource on this, so I thought I'd start with suggesting that you look at other Stack Overflow questions on the matter, and I found this one https://stackoverflow.com/questions/11554231/how-does-domain-driven-design-handle-reporting

The general gist of that one is to use CQRS as a pattern for your system, which is consistent with DDD, and rely on the query side responsibilities as a way to get reporting done, but I'm not sure that is a helpful answer in your case.

I also found this http://www.martinfowler.com/bliki/ReportingDatabase.html, which I found linked from here: http://groups.yahoo.com/neo/groups/domaindrivendesign/conversations/topics/2261

Here's an interesting article from ACM on the matter: http://dl.acm.org/citation.cfm?id=2064685 but it's behind a paywall so I can't actually read it (not an ACM member :( ).

There's also this here answer on a similar question: https://stackoverflow.com/questions/3380431/cqrs-ddd-synching-reporting-database

and this one: http://snape.me/2013/05/03/applying-domain-driven-design-to-data-warehouses/

Hope this helps!

What is being described by the OP is a "sales funnel" scenario, and you want to track movement of (something you have not clearly explained) through this funnel. That "something" might be "a real estate sale" or "hiring a job candidate", for example. The point is, the "something" is a process, and this process is what you are describing in your question.

You don't talk about what the "entity" is in your scenario - and it's not clear if the entity is the:

• "prospect/customer" (the buyer(s) or job candidate(s)) - the people/companies being targeted by the sales team
• "product/service" (the real estate or the job role(s)) - what the "sales team" is selling to the "prospect"
• "sales team" (the realtor(s) or the interviewer(s)) - company's people who would be working on moving the "prospect" through the "funnel"

Regardless, each of these are dimensions to the sales funnel process.

You are thinking of it wrong in terms of "status". What you really are tracking is movement of a "prospect" through stages/steps a "sales funnel". The sales funnel is a process of steps (your "statuses") and a "prospect" moves through 1 or more of those steps.

You CAN model each step in the funnel as it's own FACT and you can have a FUNNEL_TRANSACTIONS_FCT potentially to link them, but that doesn't meet your requirement of a dynamic set of steps (statuses, as you call them).

A prospect moves to a different funnel step when some "action" (transaction) occurs e.g. quote requested, quote provided, quote accepted, order placed, contract is signed, order is canceled, etc. So, let each "status" (as you call it) be a transaction type (which would be a dimension in and of itself - to which new "statuses" can be added, and changes can be made, etc).

It is clear from here that your FACT table rows represent these "actions/transactions", the result of which lands the prospect in some step of the funnel at some certain time.