SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A Reporting System Architecture


A Reporting System Architecture

Author
Message
GregoryAJackson
GregoryAJackson
Mr or Mrs. 500
Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)

Group: General Forum Members
Points: 596 Visits: 506
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
tatiana.vasilevskaya
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 35
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
GregoryAJackson
Mr or Mrs. 500
Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)

Group: General Forum Members
Points: 596 Visits: 506
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
sunjiulu
Old Hand
Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)

Group: General Forum Members
Points: 398 Visits: 35
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
GregoryAJackson
Mr or Mrs. 500
Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)

Group: General Forum Members
Points: 596 Visits: 506
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
sunjiulu
sunjiulu
Old Hand
Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)Old Hand (398 reputation)

Group: General Forum Members
Points: 398 Visits: 35
did you find the root cause of why production server take more than 5 minutes and the reporting server take less than 1? caused by CPU contention? or the "read uncommit" solved the locking issue? if it's the later one, then the performance issue can be solved by transation level turn on "read uncommit".

sometime report just take too long by joining tables, when table grow, the cardition join will get worse and worse, have to think about de-normalization and BI/OLAP solution at that point.

just a thought.

jiulu



GregoryAJackson
GregoryAJackson
Mr or Mrs. 500
Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)

Group: General Forum Members
Points: 596 Visits: 506
the reason for our production reporting issues were MANY

1) lack of proper indexes
2) poorly written sprocs
3) way too many joins
4) too much logic in the report sprocs
5) poorly designed DB
6) blocking
7) IO subsystem not configured correctly
8) Huge use of User defined functions in the reports forcing non-set based queries
9) Reports were using dynamica sql to create the correct sorting and grouping options
10) etc etc etc

not only have we moved the reports into their own little world to shield the OLTP system from their potential damage, we have also greatly optimized all the reports and we have way more indexes in the Reporting DBs.

Furthermore, our ETL Jobs pre-calculate many of the aggregated values and the Reporting DB is denormalized to a great extent.

it's a complete redesign literally apples to oranges


GAJ

Gregory A Jackson MBA, CSM
David Cook-169090
David Cook-169090
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 60
How do you synchronise Reporting1 and Reporting2? If Reporting1 is toggled as the 'loading' DB, and therefore Reporting2 is the 'live' DB, then at some point (after the load is finished?) Reporting2 will need the transactions just loaded into Reporting1; it isn't clear (to me anyway Smile) just how and when that occurs.
Eric Stimpson
Eric Stimpson
SSC Veteran
SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)

Group: General Forum Members
Points: 225 Visits: 115
I had started to write a somewhat similar article just last week. Although my concerns and constraints are somewhat different, and final solution completely different, I enjoyed your article. Here's how my situation differs from yours:

* Ad hoc reporting must not cause locks for OLTP transactions.
* Live data for reporting. There can be no delay between transactions and reporting.
* No improvement in report performance necessary, no budget for new hardware.

So the main issue we are correcting is that Office (SQL Query), Access, and Crystal all connect to SQL Server and have data isolation levels at "read committed" rather than the much more OLTP friendly "read uncommitted." As a result, it takes a table lock to return a table of data...

So our solution has been to create another database which is a schema copy of the OLTP database, where every view and table from the OLTP database is a view of the original table in the "reporting" database. Refreshing the schema has been automated so that any table or view change on the OLTP database (which are frequent as our live system is still under constant development) are refreshed in the reporting database on a scheduled basis (2x per day).

We've seen a dramatic improvement in performance due to the fact that the reporting system no longer generates locks and we can now continue to offer allow ad hoc queries from the system users, allowing our business to remain nimble and freeing the development team from a never-ending list of new data requests. This is admittedly more of a brute force solution then a highly engineered data-warehouse could provide, but I think it offers many of the advantages of more expensive solutions, without the cost of massive software re-engineering or severely limiting access to the data.
GregoryAJackson
GregoryAJackson
Mr or Mrs. 500
Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)

Group: General Forum Members
Points: 596 Visits: 506
Rpt1 and Rpt2 are not aware of each other.

They are never synched with each other. Each of them are individually and seperately synched with the Prod OLTP system.

basically we have a table that tracks the last updated date for each db. Then our synch scripts run and catch each db up with changes since they were last updated....

hope this makes a little more sense.


GAJ

Gregory A Jackson MBA, CSM
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search