Cognos BI users migrating reports from Cognos Impromptu may have to deal with reports which had used Hotfiles. This blog contains some suggestions on how to implement similar functionality in IBM Cognos BI when you migrate to Cognos.
There are several reasons that I have seen where Hotfiles were used in Cognos Impromptu and there are probably more than the ones mentioned here. The first two have simple solutions in the Cognos BI environment but the third is not that obvious.
Prompt Selection Lists
Hotfiles were commonly used to populate prompt lists, although in later Impromptu versions catalog folders could be used. In Cognos BI, the solution is to create Query Subjects in the Framework Manager model which will be used as the data query for prompt lists created from database values.
External Data
Since Cognos Impromptu only allows a single database connection when running a report, it was common for external data to be persisted with Hotfiles. This data could then be joined with the database tables used together in a report. Cognos BI supports multiple database connections in a report so no workaround is needed.
Aggregated Data
A very common usage I’ve seen for Hotfiles is to pre-aggregate data for inclusion in a number of reports. While this type of aggregation is best done in the data warehouse, this presented a problem for Impromptu developers in large environments where changes to the warehouse were tightly controlled and usually added to the end of the priority list. Data pre-aggregation can be accomplished in Cognos BI by creating aggregation Query Subjects in Framework Manager which can then be joined to the dimensional data in the reports. The problem with this approach is that the aggregation is performed for every report execution, which can be expensive with a large data set.
1. Create an Aggregation Report in Report Studio
Fortunately Cognos BI provides a way to persist data from reports by saving the output to an XML format, very similar to the way that Hotfiles work in Impromptu. For an example, let’s create a simple report which aggregates some order fact data by Area:
2. Add the Report to a Job
Once the report has been tested, then next step is to create a job to run the report. Running the report from a job allows you to not only schedule execution but also save the report output to the file system. Admin Guide: How to save report outputs to a file system
In the job step properties dialog, set the output type as XML and select ‘Save to the file system’. Set the name and location of the file.
When the job runs it will create the output file with the locale appended to the name, for example: order-facts-by-area-en-ca.xml
3. Define an XML data source in IBM Cognos Administration
In IBM Cognos Administration create an XML data source using the location and name of the output file. The example below uses the local file system but in an enterprise environment you will probably want to use a UNC location (\\server\share\folder\file). Admin Guide: XML Data Sources
4. Import data source into Framework Manager
The data source can now be imported into Framework Manager using the ‘Run Metadata Wizard… option in the project view content menu.
FM User Guide: Import Metadata Using XML as a Data Source
The final step will be to make joins to the dimensional data after which reports using these aggregations will run very quickly.
