How's the performance of your BI or reporting tool? Are you waiting more than 10 seconds for a report to complete ? 30 seconds ? Do you have to make selections for the report before you even see what you’re loading?
A good Business Intelligence tool or reporting tool renders reports within 7 seconds. The best ones perform closer to 3 seconds. Why? Because anything more is just unacceptable to the user.
At CALUMO we believe that report viewers [people] should be able to open a report by clicking a link or menu item and have it display in 3 seconds.
Lets look at the what's happening behind the scenes of that report. The report is a template. That template has data derived from a database like SAP, GP, NAV, Salesforce.com etc.. The data on the report could be the result of hundreds of thousands of transactions, thousands of consolidation points and millions of calculations. It must still display fast. And if there is an update, say a new forecast number (just one perhaps) or a new journal, that number needs to be displayed with no extra wait time. AND that report must be able to change the data you’re looking by drilling down, selecting different time periods or selecting different offices, divisions, whatever and display. All in about 3 seconds.
All of which sounds like a big deal. But with today's modern technology, all of that is within reach - IF you've chosen the correct tool and if your reports are architected well.
If you're waiting more than 7 seconds for your report to display data, it might be for one of two main reasons:
1. Hundreds of data queries per report
This is a big report performance killer. Many reporting tools will send a data query, often a T-SQL query to a database for each row of data on a report. These reports are row and column based data selections. This means, each row and sometimes column combination becomes a single T-SQL query to a relational database. For example, a 50-row report will execute 50 queries assuming the column configuration is simple. More complex columns will create and execute n more queries. Multiple queries to a relational database slows performance of the back-end database. In turn, this slows down your report response. This is because there are many discrete queries being made in a linear fashion depending on the number of cores in the CPU and how the threading of the server is configured. Therefore, in many cases, each query must complete before the next runs. If you add multiple users to this process – users and queries are queued and report rendering can take several minutes.
A good reporting tool like CALUMO however will, in most cases, run 2 queries per report. 1 query is for metadata which is extremely fast. The other query is for data. A standard CALUMO report will review the data required from the report and construct a single MDX statement to execute for data. This one act reduced resources and therefore time required to display are report significantly reduced when compared to other technologies. More complex reports such as reports drawing data from 2 cubes will require a query per cube but this is still a significant cost saving over multi-query tools.
2. Cube Technology = 100x faster
Most reporting tools use the old school relational database for reporting. BI using a relational database is excellent for large sets of transactions. But relational technology is hopeless for aggregations, slice and dice and SPEED! So if you combine hundreds of queries per report to a relational database no matter how awesome you have designed it, with in-memory tables or otherwise, they are going to be slower than cubes coupled with minimal data queries. This is why CALUMO uses cube technology for reporting, ad-hoc analytics and planning. Cubes deliver data 100 times faster than standard relational table based reporting. Data view queries, dynamic consolidations of companies, entities, departments, accounts, time etc., where the data is changed are near instant.
So if you’re waiting more than 10 seconds for a report from your BI tool, or worse more than 30 seconds, or you have to make selections for the report before you even see what you’re loading, turn it off and get a cube based and low query count reporting tool.