SQL 2005 Pilot for Leading Engineering/Construction Company Improves Performance
Project at a Glance
RDA, in conjunction with Microsoft personnel, detailed the design and implementation of a pilot SQL 2005 business intelligence solution to improve overall business application performance and to improve time of reporting functions for a leading engineering and construction company.
Background
Our client was experiencing slow reporting performance from their EPM system. This was causing an extra load on the EPM database server, resulting in reduced performance for end users. Our client’s reports had been implemented using Crystal Reports operating directly off of the OLTP database used by the EPM system. This architecture put extra demands on the EPM database server and caused the reports to take quite a bit of time to produce, negatively impacting the performance that users of the EPM system experienced.
Solution Detail
RDA was asked to aid Microsoft personnel in the design and implementation of a pilot solution utilizing Microsoft’s business intelligence suite.
Our team’s first task was to separate the reporting process from the OLTP database to remove that load and increase the performance of the EPM system for end users. Our team then addressed the issue of updating data reporting response time. Microsoft SQL Server 2005 was installed and SQL Server Replication Service was configured to move the data in near-real time from the Oracle OLTP database to a SQL Server database setup for reporting. This removed the load from Oracle with no additional negative effect on the server.
A challenge our team faced was that the queries to get data for the reports were complex and performed poorly because of the normalized nature of the database schema and the large tables. The traditional method to fix this would be to re-design the schema to de-normalize it for increased reporting performance. This would have entailed writing an Extract Translate Load (ETL) process to move the data to a different schema and would have increased the cost and time of the implementation. Instead, RDA and Microsoft were able to use SQL Server’s Indexed View technology to persist a de-normalized view of the data specifically for the reports.
Microsoft SQL Server Reporting Services was configured and sample reports written to utilize the new database and mimic existing reports. Report Models were also constructed so that users can use Reporting Services’ Report Builder to produce ad-hoc reports without having to wait for time from technical resources to develop them.
Benefits
- Reports that took upwards of 20 minutes to produce and required many clicks to execute now can be viewed within seconds with a single click.
- Load on the EPM OLTP database server was reduced.
- Users now have the capability to produce ad-hoc reports without IT intervention.