InetSoft Webinar: Interactivity Built Into a Report

This is the continuation of the transcript of a Webinar hosted by InetSoft on the topic of "Best Practices for Reporting." The speaker is Mark Flaherty, CMO at InetSoft.

So again, having that interactivity built into a report, being able to bind the variables to the reports that can change the nature of the data, meaning the data coming back whether you need a subset or a specific orientation will make that report much more flexible and useful for that user. Again, precision is key.

Also another thing to note here is what not to show in the report. Although it's tempting to set up a report that contains every piece of information available, omitting information that’s not important to the user allows important information to be accessed more easily. Ensuring that the reports answers specific questions, allowing the user to exclude the information they don’t want is always going to be better because again that saves them work in the end, and ultimately it's going to be better on the database as well. And what I mean is that it's going to perform better. There is going to be less data being extracted, and again it's more precise.

So in this process if it's not already pre-configured in the report, allow the user the chance to adjust these things based on date-time range, based on selected variables, based on perhaps selected analyses, and value ranges.

And of course once the report is generated, there should be options for filtering and collapsible sections. There should be certain sections that can be ignored easily. So an example, an index allows the user to find needed items quickly. And other than that, I would say that we can probably move on into the information design. Databases are near and dear to our hearts. And one of the things that’s important in the whole process of report building is the query building task. On the left, this is an example of an execution plan that you can generate using InetSoft. So this was derived off of a query that was running against an Oracle database.

Most RDBMS platforms, Oracle, SQL server, DB2 and some of these other that are out there like MySQL, they have the ability to produce an execution plan. So definitely review the SQL plan or the execution plan if it is available to you. I know in some cases like with Microsoft Access, you won't get a plan but for most mainstream RDBMS databases, you are going to get that feature available to you if it's provisioned in the database.

But I guess my first tip when it comes to query building is whether or not to use a WHERE clause in a Select statement to know the narrow the number of rows return. This is important. The WHERE clause is always important not only in the use of a join but in general. If you don’t use a WHERE clause then the database will perform a table scan of your table. That means it’s going to read every single record from that table and return all the rows. So in some cases you may want to return all the rows and not use a WHERE clause, but if you don’t need all the rows returned, using a WHERE clause can limit the number of rows retrieved.

So DBAs are constantly looking at the performance of ad hoc queries and batch queries that are running on a transactional system. This is going to help that cause because we are introducing a best practice in query design early on. By returning the data that you don’t need, you are causing the database to perform I/O, input and output that it doesn’t need to, and it's wasting database resources. It increases network traffic which can also lead to reduced performance and if the table is very large, a full table scan will lock the table during a time consuming scan. So what happens there, other users are prevented from accessing it. There is a lock in place, and it hurts the concurrency of the application.

Another commonly used clause or statement in SQL is the UNION statement and by default it performs the equivalent of a Select DISTINCT on the final results set, okay. So what the UNION does is in other words, it takes the results of two like record sets, combines them and then performs a DISTINCT in order to eliminate any duplicate rows. This process occurs even if there are no duplicate records in the final record set.

Read what InetSoft customers and partners have said about their selection of Style Scope for their solution for dashboard reporting.

So if you know that there aren’t any duplicate records and this presents a problem for your application then by all means use the UNION statement to eliminate those duplicate rows. So let me say that again. If you know that there are going to be duplicate records, and if there is going to be a challenge with this, then by all means use the UNION statement, and eliminate those duplicate rows.

But if you know that there will never be any duplicate rows, if you know that the raw dataset is distinct, and this presents no problem for you or your application then you should use the UNION ALL instead, instead of UNION. And the advantage of UNION ALL is that it does not perform that Select DISTINCT at that final result set which saves a lot of unnecessary database resources from being used.