44

First, I want to say this seems to be a neglected question/area, so if this question needs improvement, help me make this a great question that can benefit others! I'm looking for advice and help from people who have implemented solutions that solve this problem, not just ideas to try.

In my experience, there are two sides of an application - the "task" side, which is largely domain driven and is where the users interact richly with the domain model (the "engine" of the application) and the reporting side, where users get data based on what happens on the task side.

On the task side, it's clear that an application with a rich domain model should have business logic in the domain model and the database should be used mostly for persistence. Separation of concerns, every book is written about it, we know what to do, awesome.

What about the reporting side? Are data warehouses acceptable, or are they bad design because they incorporate business logic in the database and the very data itself? In order to aggregate the data from the database into data warehouse data, you must have applied business logic and rules to the data, and that logic and rules didn't come from your domain model, it came from your data aggregating processes. Is that wrong?

I work on large financial and project management applications where the business logic is extensive. When reporting on this data, I will often have a LOT of aggregations to do to pull the information required for the report/dashboard, and the aggregations have a lot of business logic in them. For performance sake, I have been doing it with highly aggregated tables and stored procedures.

As an example, let's say a report/dashboard is needed to show a list of active projects (imagine 10,000 projects). Each project will need a set of metrics shown with it, for example:

  1. total budget
  2. effort to date
  3. burn rate
  4. budget exhaustion date at current burn rate
  5. etc.

Each of these involves a lot of business logic. And I'm not just talking about multiplying numbers or some simple logic. I'm talking about in order to get the budget, you have to apply a rate sheet with 500 different rates, one for each employee's time (on some projects, other's have a multiplier), applying expenses and any appropriate markup, etc. The logic is extensive. It took a lot of aggregating and query tuning to get this data in a reasonable amount of time for the client.

Should this be run through the domain first? What about performance? Even with straight SQL queries, I'm barely getting this data fast enough for the client to display in a reasonable amount of time. I can't imagine trying to get this data to the client fast enough if I am rehydrating all these domain objects, and mixing and matching and aggregating their data in the application layer, or trying to aggregate the data in the application.

It seems in these cases that SQL is good at crunching data, and why not use it? But then you have business logic outside your domain model. Any change to the business logic will have to be changed in your domain model and your reporting aggregation schemes.

I'm really at a loss for how to design the reporting/dashboard part of any application with respect to domain driven design and good practices.

I added the MVC tag because MVC is the design flavor du jour and I am using it in my current design, but can't figure out how the reporting data fits into this type of application.

I'm looking for any help in this area - books, design patterns, key words to google, articles, anything. I can't find any information on this topic.

EDIT AND ANOTHER EXAMPLE

Another perfect example I ran across today. Customer wants a report for Customer Sales Team. They want what seems like a simple metric:

For each Sales person, what is their annual sales to date?

But that is complicated. Each sales person participated in multiple sales opportunities. Some they won, some they didn't. In each sales opportunity, there are multiple sales people who are each allocated a percentage of credit for the sale per their role and participation. So now imagine going through the domain for this...the amount of object rehydration you would have to do to pull this data from the database for every Sales person:

Get all the SalesPeople->
For each get their SalesOpportunities->
For each get their percentage of the sale and calculate their Sales amount
then Add up all their SalesOpportunity Sales amount.

And that's ONE metric. Or you can write a SQL query which can do it quickly and efficiently and tune it to be fast.

EDIT 2 - CQRS Pattern

I've read about the CQRS Pattern and, while intriguing, even Martin Fowler says it's not tested. So how has this problem BEEN solved in the past. This has to have been faced by everyone at some point or another. What is an established or well-worn approach with a track record of success?

Edit 3 - Reporting Systems/Tools

Another thing to consider in this context is reporting tools. Reporting Services/Crystal Reports, Analysis Services and Cognoscenti, etc. all expect data from SQL/database. I doubt your data will come through your business later for these. And yet they and others like them are a vital part of the reporting in a lot of large systems. How is the data for these properly handled where there is even business logic in the data source for these systems as well as possibly in the reports themselves?

migrated from stackoverflow.com Jan 23 '14 at 7:20

This question came from our site for professional and enthusiast programmers.

  • 2
  • 3
    Sorry didn't mean to. The mod told me to repost here, but then apparently was able to migrate the same question so I got two. Sorry about that. – richard Jan 23 '14 at 9:38
  • I'm confused. Has no one done this? No one faces this issue? – richard Jan 29 '14 at 23:08
  • Isn't your 'separation of concerns' re task/report sides a bit theoretical? You could say the reporting side has business rules too, so you cannot avoid putting business logic into the entire chain. Whatever BI tool you use, you will have to create intermediate results all the way from the input tasks to the reporting stage (defining aggregate objects and so on). Then it boils down to the question where to crunch what. Maybe you can approach the problem with a piramid (with the top cut off) or funnel metaphor. – Jan Doggen Jan 30 '14 at 20:25
  • @JanDoggen That's exactly my point. The BI tool will HAVE to have BL logic in it. Now I'm duplicating the BL that is in the rich domain of my software product. Is that OK? – richard Jan 30 '14 at 20:32

11 Answers 11

16

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!

  • Hi @RibaldEddie. Thanks for the reply. Doesn't seem glib to me. So you are saying it's ok to treat the stored procedures as the domain layer for the reporting Bounded Context? – richard Jan 23 '14 at 7:35
  • If you have good reason to do that in your situation then that's fine. Personally I'm not sure that I would use SP in any case except maybe for some data validation or clean up, otherwise I would tend to shy away from that in every case. – RibaldEddie Jan 23 '14 at 16:34
4

My understanding from your question is this: Application for daily task has

View >> Controller >> Model (BL) >> Database (Data)

Application for reporting purpose

View >> Controller >> Model >> Database (Data + BL)

So change in BL for 'task application' will lead to change in 'reporting' BL too. That is your real problem right? Well thats OK to do changes twice, that pain you have to take anyway. The reason is both the BLs are separated by their respective concerns. One is for fetching data and one for aggregrating data. Also, your original BL and aggregrating BL will be written in different technology or language (C#/java and SQL proc). There is no escape to it.

Lets take another example not related to reporting specifically. Suppose a company XXX tracks mails of all users for interpretation and sell that info to marketing companies. Now it will have one BL for interpretation and one BL for aggregrating data for marketing companies. Concerns are different for both the BLs. Tomorrow if their BL changes such that mails coming from Cuba should be ignored, then business logic will be changed at both sides.

3

Reporting is a bounded context, or a subdomain, to speak loosely. It solves a business need of gathering/aggregating data and processing it to gain business intelligence.

How you implement this subdomain will likely be a balance between the (most) architecturally correct way you can do this, and what your infrastructure will allow. I like to start on the former side, and move towards the latter only as necessary.

You can probably split this up into two primary problems that you are solving:

  1. Aggregating, or warehousing data. This should process some data source, and combine the information in such a way that it is stored in another data source.

  2. Querying the aggregated datasource to provide business intelligence.

Neither of those problems reference any specific database or storage engine. Your domain layer should just be dealing with interfaces, implemented in your infrastructure layer by various storage adapters.

You may have various workers or some scheduled job run, that is split into a few moving parts:

  • Something to query
  • Something to aggregate
  • Something to store

Hopefully you can see some of the CQRS shine through there.

On the reporting side, it should only need to do querying, but never directly into the database. Go through your interfaces and through your domain layer here. This is not the same problem domain as your primary tasks, but there should still be some logic here you want to adhere to.

As soon as you dive directly into the database, you depend on it more, and it may eventually interfere with your original application's data needs.

Also, at least for me, I definitely prefer writing tests and developing in code rather than queries or stored procedures. I also like not locking myself into specific tools until absolutely necessary.

3
+250

Retrieving large amounts of information over wide area networks, including the Internet, is problematic due to issues arising from latency of response, lack of direct memory access to data serving resources, and fault tolerance.

This question describes a design pattern for solving the issues of handling results from queries that return large amounts of data. Typically these queries would be made by a client process across a wide area network (or Internet), with one or more middle-tiers, to a relational database residing on a remote server.

The solution involves implementing a combination of data retrieval strategies, including the use of iterators for traversing data sets and providing an appropriate level of abstraction to the client, double-buffering of data subsets, multi-threaded data retrieval, and query slicing.

  • Not sure how this relates to my question or how it got 3 votes so fast. Also did you mean to include a link here? – richard Jan 30 '14 at 20:34
  • 2
    Looks like the bounty was auto-awarded to this answer. This answer seems like gibberish to me and I would NOT have awarded this the bounty. – richard Nov 1 '16 at 16:32
2

It's typical to separate operational/transactional data stores from reporting. The latter may have requirements to keep data for legal reasons (e.g. seven years of financial data for financial auditing), and you don't want all that in your transactional data store.

So you'll partition your transactional data by some time measure (weekly, monthly, quarterly, yearly) and move older partitions out into your reporting/history data store via ETL. It may or may not be a data warehouse with a star schema and dimensions. You'd use data warehousing reporting tools to do ad hoc queries and roll ups and batch jobs to generate periodic reports.

I would not recommend reporting against your transactional data store.

If you prefer to press on, here are more thoughts:

  1. "Best" is subjective and what works.
  2. I'd buy a reporting product rather than write them myself.
  3. If you're using a relational database, then SQL is the only game in town.
  4. Stored procedures depend on whether you have the skills to write them.

Project management software that you use in house? I'd buy before I'd build. Something like Rally and Microsoft Project.

  • Thanks @duffymo. This data isn't just stored for legal reasons. It's tons and tons of data that is used and reported on regularly. The company lives and dies by the reports and dashboards. It's project management software after all. What is the best way to supply these reports and dashboards with the data? Aggregating and pulling it out with SQL? It's ok for the business logic to be in the Store Procedures for this? All my questions still unanswered! – richard Jan 22 '14 at 18:34
  • You do have an answer - data warehouse. Sounds like that wasn't what you wanted to hear. See above for edits. – duffymo Jan 22 '14 at 18:42
  • So then it is fine for the business logic that is in the domain to be duplicated in the dts and data warehouse? Also, then pulling that data out, do I use any sort of domain model at all? Or just pull the data out with stored procedures and display in the view? To address your points above: I can't buy a reporting product...the reason I am writing this is the company has specific needs not met by any reporting product. I am using a relational database and have very good SQL skills. But I don't want to default to what I am good at, I want to do what is good design. – richard Jan 22 '14 at 18:47
  • Re: buy before you build - you can't force a company to mold their business to the software when they want software built to fit their business. Rally and MS Project don't fit everyone's project management needs. At all. – richard Jan 22 '14 at 19:04
  • Can't force, of course. But every business gets to decide what's in their self-interest. If you aren't in the business of selling project management software it's in your interest to assess whether it's better to buy it. Just like accounting software. Who in their right mind would write a general ledger from scratch? – duffymo Jan 22 '14 at 20:18
2

First some terminology, what you call the task side is known as Transactional and the Reporting side is Analytics.

You've already mentioned CQRS which is a great approach but there is little documented practical application of the approach.

What has been heavily tested is supplementing your Transactional processing with an Analytical processing engine. This is sometimes referred to as Data Warehousing or Data Cubes. The biggest gotcha with regard to analytics is that attempting to run queries against your transactional data in real-time is inefficient at best because it's really only possible to optimize a database for reading or writing. For transactions, you want high write speeds to avoid delays in processing/doing things. For reporting, you want high read speeds so decisions can be made.

How to account for these issues? The simplest approach to understand is using a flattened schema for your reporting and ETL (extract transform load) to shuttle data from the normalized transactional schema to the denormalized analytical schema. The ETL is run via an agent on a regular basis and preloads the analytics table so that it's ready for a quick read from your reporting engine.

A great book to get up to speed on data warehousing is the Data Warehouse Toolkit by Ralph Kimball. For a more hands on approach. Download the trial of SQL Server and pick up the Microsoft Data Warehouse toolkit which takes the general discussion of the first book but shows how to apply the concepts using SQL Server.

There are several linked books from those pages that go into more details about ETL, Star Schema Design, BI, Dashboards and other topics to help you go along.

The quickest way to get from where you are to where you want to be, is to hire a BI Expert and shadow him while he implements what you need.

  • Hi Mike. I am very familiar with datawarehousing and BI, having been doing it for 15 years. My question deals with how to handle this in a domain driven design context. Are datawarehouses ok? Or are they an adulteration of your domain business layer? If the answer is build a data warehouse and pull the data from there, there is a lot of literature and advice out there for that. But then you are duplicating business logic outside of your domain. Is that ok? That's my question. – richard Jan 27 '14 at 23:23
  • Like I mentioned CQRS addresses that need well by separating the repository into a Command (transactional) and Query (reporting) side. But even without the other trappings of CQRS, the data warehouse and etl are clients of your domain but they do not modify it. So the BL is still contained within the domain. – Michael Brown Jan 28 '14 at 22:08
  • 1
    They don't modify the domain...so then all ETL processes and data transformations to create the data for the data warehouse have to go through your domain? Otherwise your BL is duplicated in all the logic of your ETL processes. – richard Jan 28 '14 at 22:15
  • 1
    Yes I would say that an ETL should IDEALLY use the domain directly. That would allow you to avoid brittle tools that have to be rewritten with every internal change to the database. – Michael Brown Jan 28 '14 at 22:28
2

What about the reporting side? Are data warehouses acceptable, or are they bad design because they incorporate business logic in the database and the very data itself?

I don't think you are talking about business logic, this is more reporting logic. What do users do with the information on this screen, is it simply for status updates? Your domain model is used to model transactional operations, reporting is a different concern. Pulling the data from SQL Server or putting it into a data warehouse is fine for reporting scenarios.

Your domain model should enforce the invariants of your domain such as a project member can't book to the same project at the same time, or can only book x number of hours a week. Or you can't book to this project as it is complete etc etc the state of your domain model (the data) can be copied for reporting separately.

To improve query performance you can use a materialised view. When an operation is committed against your model (e.g. Book 4 hour of this persons time to project x) and it is successful it can throw an event that you can then store in a reporting database and make the necessary calculates for your report. It will then be very fast to query on it.

Keep your transaction and reporting contexts separate, a relational database was built for reporting a domain model was not.

EDIT

Useful blog post on the subject http://se-thinking.blogspot.se/2012/08/how-to-handle-reporting-with-domain.html

1

report/dashboard is needed to show a list of active projects

State of each projects should be stored as static, per-calculated and well-formatted information in the database and any simulations should be handled on the client as WebApp.

budget exhaustion date at current burn rate

this type of projection should not be run on demand. Managing this information upon request, like perform calculations on resources, rate, tasks, milestones, etc, will result in an extensive use of the calculation layer without any re-use of these results for future calls.

Imagining a distributed environment (private or public cloud), you will get the enormous costs in the layer of computation, low use of database, and total lack of cache.

Should this be run through the domain first? What about performance?

The design of your software should include the ability to perform the normalization of the calculations necessary to obtain the required result during "data input", not during the reading. This approach greatly reduces the use of computing resources, and above all creates tables that could be considered as "read only" by the customer. This is the first step to create a caching mechanism solid and simple.

So a search first, before completing the software architecture, it could be Distributed Cache System.

(request:aggregation) != 1:1

My consideration is therefore (for both first and second example), try to understand when it is proper to normalize the data, having as objective to reduce the aggregations per client requests. Which can not be 1:1 (request: aggregation) if one goal is obtain a sustainable system.

Distribute the calculation on the client

Another question, before finishing the design of the software, it could be, how much normalization, we want to delegate the client's browser?

It was named MV*, it is true that it is fashionable today, in addition to this, one of its purposes is to create WebApp (single page app), which can be considered the present of many complex applications (and fortunately for bills that we pay to the cloud provider, these are executed in the client).

My conclusion is therefore to:

  1. Understanding how many operations are really necessary to carry out the presentation of the data;

  2. Analyze how many of these can be done in background (and then distributed through a cache system, after their normalization);

  3. Understanding how many operations can be executed in the client, getting the configuration of the projects, running it on Views on the WebApp and thus reduce the calculation performed in back-end;

  • Hi Marcocs, thanks for your answer. Two problems I see with doing pre-aggregations on the client side is that 1. you have a LOT of actions that could result in a precalculation and 2. There could be a LOT of precalculations needed. Put the two together and you get really heavy resource use. For example...the budget will have to be recalculated when A. any bill rate on the budget changes (this could be triggered by a number of things...a user action, or a scheduled rollover to a new rate for example the rates change at the beginning of a new fiscal year), or B. The composition of the budget... – richard Jan 25 '14 at 22:02
  • ...changes for example hours added or subtracted. Etc. The list goes on. As far as #2 the aggregations needed...tomorrow the customer needs to see aggregations by region, then they want to see by employee, or by city, or industry, or any other crazy attribute on the project or related entity. Will you pre-aggregate all of these? If so, now you are creating an OLAP engine...Also, do these aggregations belong stored in the project Object in the domain? When you present the data, when will you use a calculated value vs the precalculated value. Etc. Have you made this work in a real world app? – richard Jan 25 '14 at 22:05
  • I'm interested in this approach but it presents a lot of problems to my mind. – richard Jan 25 '14 at 22:07
  • I have an earnings distribution system up & running, my problem was to show the current status of earnings, based on data generated by the sub-networks of agents (including withdrawals , deposits, jackpot,..). The sub-networks, they use their balance constantly, this increases/decrees the profits of the networks father.The distribution of earnings is carried out periodically every Monday, problem was to show in real time the evolution of the gain, and update the virtual budget of all networks. – marcocs Jan 25 '14 at 22:55
  • To avoid aggregations across the networks, and distribute all values in real time whenever a request is made, a temporary-deployment process is executed continuously to normalize the earnings of the networks.Each time you make a request, calculated values are summed by aggregating the values ​​that are not included in the temporary deployment (I just work with last-update-item). The table of transactions (which obviously is the one that suffers the load in this application), was handled with table partitions. – marcocs Jan 25 '14 at 22:55
1

Use cache for query, use domain for caching.

There is a feature called "top users" on stackoverflow. You may find a line on the buttom of top users page, says"Only non community-wiki questions and answers are included in these totals (updated daily)". This indicates the data is cached.

But why?

For performance issue maybe. Maybe they have the same concern with leaking domain logic("Only non community-wiki questions and answers are included in these totals" in this case).

How?

I don't really know how they did this, so here is just a guess :)

First, we need to find target question/answers. A scheduling task could work, just fetch all potential targets.

Second, let's look at only one question/answer. Is it a non community-wiki one? Is it within 30 days? It's quite easy to answer with domain models. Count the votes and cache them if satisfied.

Now we have the cache, they're the output of domain derivations. The query is fast and easy because there is only simple criteria to be applied.

What if the results need to be more "real time"?

Events may do the help. Instead of triggering the caching with a scheduling task, we may split the process into many sub-processes. For example, when someone votes on hippoom's answer, we publish an event triggering the update of the hippoom's top users cache. In this case, we may see frequent quick small tasks.

Is CQRS neccessary?

Neither with scheduling task approach nor with events approach. But cqrs does have an advantage. The cache is usually highly displaying oriented, if some items are not required at first, we may not calculate and cache them at all. CQRS with event sourcing helps reconsititute cache for historical data by replaying events.

Some related questions:
1.https://stackoverflow.com/questions/21152958/how-to-handle-summary-report-in-cqrs 2.https://stackoverflow.com/questions/19414951/how-to-use-rich-domain-with-massive-operations/19416703#19416703

Hope it helps :)

1

It's 4 years later and I just found this question again, and I have what is, to me, the answer.

Depending on your application and it's specific needs, your domain/transaction database and your reporting can be separate "systems" or "engines", or they can be serviced by one system. They should, however, be logically separate - which means they use different means of retrieving and providing data to the UI.

I prefer them to be physically separate (in addition to being logically separate), but a lot of times you start them out together (physically) and then, as the application matures, you separate them.

Either way, again, they should be logically different. It's ok to duplicate business logic in the reporting system. The important thing is that the reporting system get to the same answer as the domain system - but chances are it will get there via different means. For example, your domain system will have a ton of very strict business rules implemented in procedural code (likely). The reporting system could implement those same rules when it reads the data but would do so via SET based code (eg. SQL).

Here's what an evolution of your application's architecture might realistically look like as it evolves:

Level 1 - Logically separated domain and reporting systems, but still in the same codebase and database

Level 1 - Logically separated domain and reporting systems, but still in the same codebase

Level 2 - Logically separated domain and reporting systems, but separate databases now, with syncing.

Level 2 - Logically separated domain and reporting systems, but separate databases now

Level 3 - Logically and Physically Separated domain and reporting systems, and separate databases with syncing.

Level 3 - Logically and Physically Separated domain and reporting systems, and separate databases with syncing.

The main idea is that reporting and domain have radically different needs. Different data profiles (reads vs writes and updates frequency), different performance requirements, etc. So they need to be differently implemented, and that necessitates some duplication of business logic.

It's up to your business to devise a way to keep the business logic of the domain and reporting systems up to date with each other.

0

Disclaimer:
I'm quite inexperienced in applications with domain models.
I understand all the concepts, and I've already been thinking for a long time about how to apply these concepts to the applications I'm working on (which ARE domain-rich, but lack OO, actual domain models etc.).
This question is one of the key problems that I faced as well. I have an idea how to solve this, but as I just said...it's just an idea that I came up with.
I didn't implement it in an actual project yet, but I don't see a reason why it shouldn't work, though.


Now that I've made that clear, here's what I came up with - I'll use your first example (the project metrics) to explain:

When someone edits a project, you're loading and saving it via your domain model anyway.
In this moment, you have all the information loaded to calculate all your metrics (total budget, effort to date etc.) for this project.

You can calculate this in the domain model, and save it to the database with the rest of the domain model.
So the Project class in your domain model will have some properties like TotalBudget, EffortToDate etc., and there also will be columns with those names in the database tables where your domain model is stored (in the same tables, or in a separate table...doesn't matter).

Of course, you need to do a one-time run to calculate the value for all existing projects as you are starting with this. But after that, the data is automatically updated with the current calculated values each time a project is edited via the domain model.

So whenever you need any kind report, all the required data is already there (pre-calculated) and you can just do something like this:

select ProjectName, TotalBudget, EffortToDate from Projects where TotalBudget > X

It doesn't matter whether you get the data directly from the tables where the domain model is stored, or if you somehow extract the data to a second database, to a data warehouse or whatever:

  1. If your reporting store is different from your actual data store, you can just copy the data from the "domain model tables"
  2. If you directly query your actual data store, the data is already there and you don't need to calculate anything

In either case, the business logic for the calculations is in exactly one place: the domain model.
You don't need it anywhere else, so it's not necessary to duplicate it.

  • Hi Christian, I'm glad to see I'm not the only one struggling with this. Thanks for your answer. See my comments on Marcocs answer for the problems I see with this approach. Any ideas on dealing with those would be appreciated! – richard Jan 25 '14 at 22:07

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.