,

Ironside Tech Tip: Show Cube Sizes in BI Reports

cube sizes concept block stack
/by

Cubes (including Cognos PowerCubes and Dynamic Cubes, as well as other file-based cube technologies) are well-known, commonly used, and valuable data sources in reporting.  As specially indexed binary files, report performance with cubes is generally robust, however when cube files grow very large (or there is high simultaneous demand reading from the cube), response time can and will decrease. That’s why it can sometimes be useful to show cube sizes on BI reports to let authors know when a cube’s size could be affecting performance.

Recently, one of our IBM Cognos Report Studio class participants asked how a report author could determine the file size of a cube. In her particular instance, their group was having trouble understanding how or why a particular cube was performing slowly.  If the authors were able to know that certain cubes were getting excessively large, the authors then could intelligently strategize with the modelers and/or cube designers about alternate cube creation, filtering, or distribution strategies.

As we have seen so many times, “the answer with IBM Cognos is almost always usually yes! The question is how?” as I like to say in my classes, and this solution —  with  help from the Cognos administrator and some simple server side scripting,  combined with a simple HTML hyperlink  inserted into a report, can easily answer this question.

Steps to Show Cube Sizes

In order for this solution to work, the cube modelers and Cognos administrators must first institute a not well-known “best practice” of copying and placing identical cubes on each individual report server.  In other words, instead of one cube located on one file server, the cube is copied to every report server in the Cognos environment. In a Windows server environment, the data source location for each of these copies would begin with C:<path-to-cube+directory> 

Employing this identical cubes technique offers both benefits and detriments:

Benefits

  • Performance in accessing cubes improves because data is not being queried across and passing through the network but instead over the backplane of the server directly into the report
  • Multiple simultaneous requests to the same cube/package are by default evenly sent to each report server/cube

Detriments

  • If cubes are currently in only one directory/server location, they need to be distributed to each report server (see next)
  • Cube updates or rebuilds have to be distributed manually (or through scripting) to each report server
  • A scheduled batch file (Windows) or shell script (UNIX) has to be written to write out the directory listing containing the cubes to a file housed in the Cognos Server

Once the cubes are in a report server directory, the server administrator needs to write a script and schedule it. The script’s syntax will change based on your operating system.

Windows Server Batch File Syntax

cd <Directory-Containing-Cubes)
dir *.mdc > {COGNOS-MOUNT-POINT}\c10_64\webcontent\samples\cubes.txt

UNIX Shell Script

cd <Directory-Containing-Cubes)
ls -l *.mdc > {COGNOS-MOUNT-POINT}\c10_64\webcontent\samples\cubes.txt

These scripts both write out the directory listing of all the PowerCubes (.mdc files) on that server into a directory readable by Cognos reports (you can also leave off the *.mdc to show all elements in the directory).

Report authors then need to simply add the following code into an HTML element on the report page to see the directory listing with your cube sizes:

<a href=../samples/cubes.txt>Cube File Directory Listing</a>

Running the Report

When you run a report with the code in place, your output will appear as follows.

link to cube sizes in report

Clicking the hyperlink displays a list of cube sizes similar to the one shown below.

cube sizes list output

Ironside offers comprehensive, expert instruction in all facets of IBM Cognos software. If you’d like to dig deeper into the possibilities available in Report Studio or any other Cognos module, sign up for a public class today. You can also schedule a private class for a group at your home location, or even let us design you a custom course that provides relevant learning examples based on your organization’s actual data.