What are Hotfiles and How Do I Use Them?

This FAQ discusses the use of Impromptu Hotfiles. It is maintained by griffindm. Click on the link below the FAQ to send me a message on corrections or additions that others may find useful.

A hotfile is a locally stored data file. It is created with the æSave AsÆ option of Impromptu. It can be read directly by Impromptu, or it can be included in catalogs. It provides a way to read data when disconnected from the LAN or database server. In this way it is similar to a æSnapshotÆ, which is a stored copy of the report data, saved with the report itself. A snapshot, however, can only be used with the report with which it is saved.

Hotfiles can be automatically created by macros, which can also open the final report that uses them. This makes the process transparent to the end-user.

Primary Uses of Hotfiles

To combine data from different data sources: You can create a catalog using Access tables, create a hotfile from that catalog, and then combine that hotfile with data from an Oracle, SQL Server, or other database platform. You can also combine data from Excel spreadsheets, either directly using the MS ODBC driver, or by using the save as dBase file option within Excel. These .dbf files can be used in an Impromptu catalog as well.

To resolve multiple 1-to-many relationships: If you need create a report that requires a 1-to-many relationship from a primary transaction æfactÆ table, and combine this information with a summary result from a second table, you will need to take creative action if the report grouping is not based on the column used as the ægroup byÆ clause for the second æsummaryÆ result. An example might be a report that shows the revenue by organization, and also shows the number of active employees by this organization. One approach to resolving this might be to create a report that produces a hotfile having one row per organization and the summary total of active employees. This hotfile can then be easily used with reports that show other data by organization.

To improve performance of reports that need less detail than the lowest level of the database table: In some cases a ætwo passÆ report can be considerably faster than a single report. This is especially important if the report uses local functions. This can be determined by looking at the SQL statement generated by the report under the menu option Report | Query | Profile û SQL. If there are calculations or filters in the report that do not appear in the SQL, AND the report uses less detail than the table itself, consider the alternative of creating a summary-only report that saves a much reduced row count to the hotfile, and then use a second report to incorporate the local functions. A Costpoint ERP example might be a report to show average labor rates by GLC or PLC. The base LAB_SUM table might have 200,000 rows for a YTD report. Using a summary report that creates a ægroup byÆ clause in the SQL might reduce this to as few as 1,000 rows in the hotfile. Local functions are particularly slow and operate on each row returned to the report. I have decreased run time on some reports from as much as 100 minutes to as few as 90 seconds. This use of hotfiles should be of special attention to users with large databases and users of Oracle, which processes æIf-Then-ElseÆ statements locally within Impromptu unless the automatic If-Then-Else to Decode setting is turned on.

To distribute data in a low-bandwidth Wide Area Network (WAN): In situations where remote users need to access data from a central database over limited bandwidth connections (56-256Kbps), a scheduled process to create a hotfile and copy it to each of the remote locations can result in much improved performance for specific reports.

Hotfile Limitations

While hotfiles can resolve a number of reporting issues, there are several important caveats to keep in mind. Hotfiles are only created in æoverwriteÆ mode. You cannot append to an existing hotfile. This limits their use in situations where you need to create a history of the values in a table that have since been overwritten. Hotfiles are not true database tables, and do not benefit from the power of a database server or the use of indexes. This limits their use on extremely large data result sets. I do not recommend them when they contain in excess of 50,000 rows, and their performance can begin to flag at considerably lower numbers.

There are also stability issues when retrieving data from hotfiles as the number of hotfiles in the report grows. There is no absolute maximum number, but my experience has been that five hotfiles is pushing the limits, and seven or more will often result in GPFÆs from Impromptu.

Alternatives to Hotfiles

For combining data from other sources: If is not important to keep the additional data in its original form, I often recommend creating a database table, outside of the application schema (i.e. not created with the Deltek ID), and importing the data into the table. This can easily be done from Excel spreadsheets via MS Access. The MS Access product also gives you an easy method to update this data directly within the database table in a spreadsheet-like ægridÆ. This solution avoids macros to update the new table and takes full advantage of the database server.

For creating ætwo passÆ report efficiency: Consider creating a database view that does the summary function on the database dynamically as required. A database view takes little space on the database server, and can create summary data æon the flyÆ as required by reports. They appear as normal database tables to Impromptu. An example would be a view on CostpointÆs Proj_Bill_Hs to return a single row with the inception to data amount billed by project. This approach can also resolve some of the multiple 1-to-many relationship dilemmas that crop up.

Smart Hotfiles. From Impromptu version 6 on, Cognos has included the capability to place a report into the catalog. While not immediately obvious, this is done as if the report were a hotfile. This allows the report to be processed before and second report based on its resultant data is run, and the first report simply feeds data into the second report. This works well, but there have been issues with outer-join support in the early releases of Impromptu since then.