I would like some suggestions on reporting.
As I understand it business rules should be kept in one place and the best place for them is in the middle tier BO's.
This is the situation.
A problem I have is that many times users want to run reports and on these reports they want to see some of the same values available in the application.
Now most of those values do not exist is the DB. They are the result of many business rules being enforced and the rules being enforced even change based upon the user’s actions within the application. An example is "Students Current Average". This is a very complicated calculation based on the student’s scores for assignments. It takes into consideration things such as what assignments are included? What is the assignment averaging method employed by the teacher? What is the score calculation method employed by the class? Does the district dictate this calculation method thus overriding the one selected by the teacher? How are incompletes handled? How are past due assignments handled? And on and on.
Reporting this for an individual student and class or even the students in the class is straight forward. I use the objects and a report generator that can use objects.
But now some are asking to be able to do this for an entire district! (BTW these are averaged scores, not Grades you see on report cards. Grades are a totally different aspect that is accounted for by the application/BO's.)
The idea of loading all the students, all the classes, all the assignments, all the scores, all the district preferences, all the teachers and their preferences (for each class) into memory and doing these calculations is a bit scary. (Did I mention the fact this is a web app and we want to be able to serve most of the schools in a state with one web/application server? And that the response times should be kept really low?)
Of course the answer is to use the power of the RDBMS. But if I do that I must now place some of the business rules in another place, the DB. Even if I was willing to do this (and I am not) the idea of writing the T-SQL statements to replicate what was done in .NET classes using various nifty OOP abilities and some nice patterns is daunting to say the least.
I do have one idea. Use my BO's in SQL Server 2005. I think all I would have to do is modify the CSLA DatPortal_Fetch to retrieve from the context I am in (to limit overhead) then create a result set to return from the DB call.
Is this doable? Anybody else try this? Any other ideas?
The OOP answer would be to make all of your data access flow through your objects. I'm somewhat more forgiving when it comes to reporting and typically allow a more direct flow of data between those tiers, often generating the report on the business/data tiers and then remoting the formatted results back to the presentation tier.
Since you need complex calculations in your reports, my recommendation would be to consider de-normalizing the data and actually persisting the resulting values in your database. If you can guarantee that the only times when the calculations need to be made is when a record is being edited, this solution often works fine. The overhead of re-implementing the calculations or object bloat required for re-using your existing objects when more limited data is needed may outweigh the storage needs for a couple more numeric field columns in your database. Naturally you are more of an expert in your domain model than I, so YMMV.
Jim Wooley
http://devauthority.com/blogs/jwooley/default.aspx
First I found out that what I proposed is not even possible. SQL server has a very limited set of System DLLs you can add to it assemblies list. System.Runtime.Remoting is not one of them.
Actually, you can try to add it but it says it needs a reference to System.Web. If you try to add System.Web it says it needs System.EnterpriseServices. If you try to add System.EnterpriseServices it says you need System.Runtime.Remoting!
Now how is that possible?
If it were not for this I think this might be a very good solution for these types of problems.
Jim
Normally I agree with you. A little de-normalization can make many things easier. But in this case persisting the calculated values is not really an option. The values are a result of many different factors and the number of averages that could be calculated for a single student is huge and not at all predictable.
This is one of those times where what the customer is asking for is completely un-realistic and even harmful to themselves. They are asking for "averages" with no context. The number produced will be misleading. I could see where a student gets one of these averages and it end up being much different from the grade he/she gets. Or a administrator looks and sees low averages but in fact the grades ends up being high due to a number of reasons.
Oh-well. Computers are not always good things.
I think I will just dump out a spreadsheet of scores and let them do what they will. Or use the DevExpress PivotGrid contorl. That thing is slick.
Copyright (c) Marimer LLC