A Reporting System Architecture

Question

A Reporting System Architecture

GregoryAJackson

SSCrazy

Points: 2794
More actions
January 20, 2008 at 2:55 am

#71061

Comments posted to this topic are about the item A Reporting System Architecture
Gregory A Jackson MBA, CSM

Viewing 15 posts - 1 through 15 (of 51 total)

You must be logged in to reply to this topic. Login to reply

Mike DiRenzo SSCrazy Points: 2013 More actions · Answer 1

Excellent article. Thank you very much. Your opening of how you worked in many places and faced the same issues (reporting, etc.) can, I am sure, be related to by many other IT professionals.

Your concern with middle tier code crashing when adding your audit columns is completely valid, albeit, a customized concern. As you pointed out, every situation is different. For example, some middle tier code have direct attribute (table columns) to property mappings (class/object properties). There must have been a ton of modifications to your sprocs for your situation.

Your detailed model of how you went about this challenge is excellent and again, I thank you very much.

- Mike

GregoryAJackson SSCrazy Points: 2794 More actions · Answer 2

thanks for the comments Mike.

One thing that really helped us was that 100% of our middle tier code, including all of our sprocs is Code Gen'd.

It's been a lot of work creating our code gen templates, but it certainly paid off in this case and allowed us to present the business case to management that we were up to the task of such a massive endeavor....

Cheers,

GAJ

Gregory A Jackson MBA, CSM

agatti SSC Eights! Points: 836 More actions · Answer 3

Ok Gregory, here is the question you've been waiting for. Why did you use mirroring, as opposed to replication, to move the data to the reporting server when a replicated database would have been readable without the need for a snapshot database?

GregoryAJackson SSCrazy Points: 2794 More actions · Answer 4

2 reasons really....

1) I was concerned that Replication would put a load on the Source Server (more so then simple mirroring)

2) the ease of setting up mirroring. literally click a few buttons and you're done

Gregory A Jackson MBA, CSM

DougS Valued Member Points: 66 More actions · Answer 5

You say your procedures were all code gen'd? You mean you had a straight insert,update,delete, and select proc for each table?

Thanks,

Doug

GregoryAJackson SSCrazy Points: 2794 More actions · Answer 6

yes and we also have numerous versions of each "Report" sproc.

Our UI is a bit strange in that the users can pick a sorting and grouping option in the UI. this modifies the eventual output in Crystal.

We used to create dynamic SQL in a single sproc for each report based on the Sorting and Grouping options.

this was difficult to optimize on the SQL Server side.

so now, we create an xml file that lists all the reports by name, lists their parameters, their selects, and their sorting and group by options.

then the code gen process will create 1 version of the reporting sproc for each Sort\Group By combination.

There are TONS of sprocs in our DB, but they all run very quickly now. It's truely a dramatic improvement when compared to our old system.

I'm trying very hard to convince others here that we should migrate ALL grouping and sorting logic out of SQL Server and Towards the UI (Crystal) but so far I've not convinced the others on my team here that it's worth the effort.

hope this all makes sense....

GAJ

Gregory A Jackson MBA, CSM

Jerry Hung SSChampion Points: 12968 More actions · Answer 7

Any reason why you didn't choose "Log Shipping"?

1. It has easy UI feature in SQL Server as well (right-click, Log shipping)

2. It provides Log backup at set intervals (say 15 mins), good for backup purpose if not already done on Production

3. You can auto-restore logs, and the restored database will be READ-ONLY always

but at least you don't have to create snapshot. It's an issue if you need to update it

SQLServerNewbieMCITP: Database Administrator SQL Server 2005

GregoryAJackson SSCrazy Points: 2794 More actions · Answer 8

Good Question,

Log Shipping was one of the other choices\alternatives for us.

I've used log shipping in the past, and in my experience, it seemed fragile...

It would stop shipping logs, and we'd have to reseed quite regularly etc.

this was a main reason that I decided to move to Mirroring as opposed to Log Shipping. It appeared to be more robust.

is this a reality? (honestly, probably not)...

I do have to say though....mirroring is working out quite nicely for us. I really have no complaints.

GAJ

Gregory A Jackson MBA, CSM

don_goodman Say Hey Kid Points: 670 More actions · Answer 9

I question the use of DTS/SSIS. The fact is you can get the data out using simple select statements qualified by your create and edit dates. The only place you will hit blocking is when the OLTP app is updating a record you are reading. But, you hit that whether you are using replication or mirroring or a script with no overhead.

There is peformance downside using DTS or SSIS over T-SQL scripts. Assuming you properly index the OLTP database, then your incremental queries fire dramatically faster than the gui intensive interfaces and/or runtime libraries of the CLR.

Except for the loading routines (which will bite you hard when all your databases get really big) you have the right idea and I WISH everyone used staging and reporting databases for reporting. There are so many opportunities to build servers using instances and memory isolation that there is never a reason NOT to have a reporting database.

Don

GregoryAJackson SSCrazy Points: 2794 More actions · Answer 10

Don,

I dont understand your post regard the negatives to SSIS and regarding blocking....

(I think I might disagree with you but I want to make sure before I reply in that regard...)

Please expand here....

Thanks

GAJ

Gregory A Jackson MBA, CSM

tatiana.vasilevskaya SSC Journeyman Points: 82 More actions · Answer 11

Hello,

I created similar system for DB2 on AS400 copying data to SQL server every day, so we have 24 hours for regular reports and some "live" reports talking directly to DB2. We have lots of SP and functions for hundreds of reports (using Crystal). It is all OK, but now we incorporate out main software, including Reporting system, to the overseas branches and they don't want to maintain and support SP and Functions in the SQL server (too complicated!). Therefore, all their reports have SQL within crystal report's command, which is terrible from my point of view and very restrictive. Your idea about code generated SP could be a brilliant solution for this situation - we supply the report and XML with the code, they just run a report! Can you please give a bit more explanation on that part. I can't ask you give code for SP generating, but at least - where to look for this, how you organize it and what to read? Appreciate a lot!

GregoryAJackson SSCrazy Points: 2794 More actions · Answer 12

great question....

I"m actually thinking of writing a follow up article on the CodeGen'd sprocs.

so we basically have an xml file that lists all reports by name and it lists the parameters for the report, the select(s) for the report, the where clause, the sort and the group by

we then read this xml file and for each report node in the xml we run the report data through a code gen template using Nant.

you should be able to read up on the Use of Nant and CodeGen Templates to get a start. I wish I could just send you our xml and our template file but that might get me in a little hot water here......

please feel free to email me if you have more questions (my email is listed in my profile page).

cheers,

GAJ

Gregory A Jackson MBA, CSM

sunjiulu Ten Centuries Points: 1232 More actions · Answer 13

I am wondering how to handle concurrency of data loading and reporting...

I know you've designed the round robin to switch loading data for every 15 minutes, what if the report last more than 15 minutes to generate, or let's say, just a second before the server1 get it's turn to refresh data, and there is a user report request come in and take 10 second to generate the report, then server 1 has to handle the concurrency of loading data and generating report...

that will lead to sqlserver locking mechnism and isolation level. what's your thought about it?

jiulu

GregoryAJackson SSCrazy Points: 2794 More actions · Answer 14

yeah weve thought about that.

1) all of our Reporting sprocs have isolation level set to read uncommitted so that they dont block. We are no worried about irrepeatable reads, etc.

2) worst case scenario, a user that accidentally got navigated to server that had the rug pulled out from under them, they would get an error and when they retry, they'd be directed to the other reporting db.

3) none of our reports take more then a minute or so to execute. that was a major goal of ours with the reporting system in the first place. Our reports originally were timing out in production all over the place even after 5-10 minutes.....

Great questions

GAJ

Gregory A Jackson MBA, CSM