advertisement

Improving Reporting Performance

60 %
40 %
advertisement
Information about Improving Reporting Performance

Published on July 7, 2008

Author: businessintelligence

Source: slideshare.net

Description

A Common Problem:
- My Reports run slow
- Reports take 3 hours to run
- We don’t have enough time to run our reports
- It takes 5 minutes to view the first page!

As the report processing time increases, so the frustration level.
advertisement

Increasing Reporting Performance

A Common Problem Why do my reports run so slow? This report takes 3 hours to run! We don’t have enough time to run our reports. It takes 5 minutes to view the first page! Report Processing Time Frustration Level

Why do my reports run so slow?

This report takes 3 hours to run!

We don’t have enough time to run our reports.

It takes 5 minutes to view the first page!

What are the Variables that Affect Performance? CPU speed Hyper threading and multi-core Operating system Network speed Database resources Number of users How active are users On demand (live data) versus pre-scheduled (cached) SDK And the biggest variable of all… the report itself

CPU speed

Hyper threading and multi-core

Operating system

Network speed

Database resources

Number of users

How active are users

On demand (live data) versus pre-scheduled (cached)

SDK

And the biggest variable of all… the report itself

Report Design for Performance Question: What factors affect report performance? Answer: Time Large Report 22MB Small Report 224KB Process Time Images Charts Data Less More

Images

Charts

Data

Question: What factors affect report performance? Answer: Time Report Design for Performance Complex Report Simple Report Process Time Formulas Grouping Sub Reports Data Providers Less More

Formulas

Grouping

Sub Reports

Data Providers

Google also has limitations

Hardware Solution

Software Solution

Optimizing Report Design Please note: Any semblance between the speaker and model represented is purely coincidental 3.8 MB .5 MB Right-Sizing A typical Report

The most common type of report Relational data Based on database tables SQL generated by Query Expert Checklist Is the generated SQL the most efficient query that can be used to return the required data set? Is the data set returned in a reasonable time frame? Are the database structures optimized for efficient data retrieval? (i.e. indices, keys, etc.) Is the database server itself optimized? Improving Report Performance Data source: Scenario 1

The most common type of report

Relational data

Based on database tables

SQL generated by Query Expert

Checklist

Is the generated SQL the most efficient query that can be used to return the required data set?

Is the data set returned in a reasonable time frame?

Are the database structures optimized for efficient data retrieval? (i.e. indices, keys, etc.)

Is the database server itself optimized?

SQL command report Relational data Based on SQL command SQL created by report designer May or may not include parameters Stored procedure report Relational data Based on a stored procedure Stored procedure created by report designer/DBA Checklist Is the SQL in the command or stored procedure the most efficient query that can be used to return the required data set? Are parameters used to push record selection back to database server? Is the data set returned in a reasonable time frame? Are the database structures and/or stored procedure optimized for efficient data retrieval? (i.e. indices, keys, etc.) Is the database server itself optimized? Improving Report Performance Data source: Scenario 2

SQL command report

Relational data

Based on SQL command

SQL created by report designer

May or may not include parameters

Stored procedure report

Relational data

Based on a stored procedure

Stored procedure created by report designer/DBA

Checklist

Is the SQL in the command or stored procedure the most efficient query that can be used to return the required data set?

Are parameters used to push record selection back to database server?

Is the data set returned in a reasonable time frame?

Are the database structures and/or stored procedure optimized for efficient data retrieval? (i.e. indices, keys, etc.)

Is the database server itself optimized?

OLAP report 1KEY OLAP data source SQL Server Analysis Services Checklist Is the OLAP cube the most efficient structure that can be used to return the required data set? Is the data set returned in a reasonable time frame? Is the OLAP server itself optimized? Improving Report Performance Data source: Scenario 3

OLAP report

1KEY OLAP data source

SQL Server Analysis Services

Checklist

Is the OLAP cube the most efficient structure that can be used to return the required data set?

Is the data set returned in a reasonable time frame?

Is the OLAP server itself optimized?

Continuous monitoring and improvement is key Know when to scale your hardware architecture Know when to break reports apart Make the most of schedule reporting & OLAP Encourage appropriate use of features and functionality Arriving Good Performance

Continuous monitoring and improvement is key

Know when to scale your hardware architecture

Know when to break reports apart

Make the most of schedule reporting & OLAP

Encourage appropriate use of features and functionality

Thank You Sanjay Mehta CEO MAIA Intelligence [email_address]

Sanjay Mehta

CEO

MAIA Intelligence

[email_address]

Add a comment

Related pages

Code On Time: Reports / Improving Reporting Performance

Microsoft Report Viewer is the lightweight reporting engine used by generated apps to produce data reports in PDF, Word, Excel, and TIFF format.
Read more

Improving Reporting Performance - MerlinXL - powered by ...

Improving Reporting Performance. When building reports, particularly large reports consider the following points: 1. Prefix with the dimension . Although ...
Read more

Troubleshooting Report Performance - technet.microsoft.com

This topic describes ways that you can help improve report performance. To troubleshoot report performance, use Reporting Services log files to determine ...
Read more

5 Tips on How to Improve Management Reporting | Vantage ...

management reporting, Performance Improvement. ... Here are some tips on how to improve management reporting: 1. ... Permanently Improving Cash Flow
Read more

Planning for Scalability and Performance with Reporting ...

Improving Performance with SQL Server 2005 Indexed Views. Integration ... Planning for Scalability and Performance with Reporting Services 462 KB
Read more

improving performance on a report - social.msdn.microsoft.com

Hi Kvd, Report server performance is affected by a combination of factors. The total time to generate a reporting server report (RDL) can be ...
Read more

Tips to improve performance of MS Reporting service ...

Tips to improve performance of MS Reporting service reports (SSRS). Photo credit: ... Kimberly L. Tripp: Improving *my* SQL skills through your questions!
Read more

Reports / Improving Reporting Performance

Microsoft Report Viewer is the lightweight reporting engine used by generated apps to produce data reports in PDF, Word, Excel, and TIFF format.
Read more

Improve business reporting - PwC UK

CEO's are increasingly dissatisfied with the quality of management information and performance reporting. The ability to make quick and confident decisions ...
Read more