Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

A Reporting System Architecture Expand / Collapse
Author
Message
Posted Monday, January 21, 2008 3:03 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 15, 2014 2:04 PM
Points: 110, Visits: 496
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
Post #445657
Posted Monday, January 21, 2008 3:06 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 25, 2008 5:24 PM
Points: 10, 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!
Post #445660
Posted Monday, January 21, 2008 3:16 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 15, 2014 2:04 PM
Points: 110, Visits: 496
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
Post #445663
Posted Monday, January 21, 2008 3:50 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 22, 2008 2:03 PM
Points: 180, 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



Post #445668
Posted Monday, January 21, 2008 3:58 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 15, 2014 2:04 PM
Points: 110, Visits: 496
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
Post #445673
Posted Monday, January 21, 2008 4:10 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 22, 2008 2:03 PM
Points: 180, 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



Post #445676
Posted Monday, January 21, 2008 4:17 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 15, 2014 2:04 PM
Points: 110, Visits: 496
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
Post #445679
Posted Monday, January 21, 2008 5:40 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 27, 2012 12:10 AM
Points: 12, 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 :)) just how and when that occurs.
Post #445687
Posted Monday, January 21, 2008 6:24 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 1:07 PM
Points: 59, 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.
Post #445690
Posted Monday, January 21, 2008 6:36 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 15, 2014 2:04 PM
Points: 110, Visits: 496
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
Post #445691
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse