Cognos Framework Manager

Calculating Prior period in Cognos BI

by

Calculating Prior period in Cognos BI

Have you ever needed prior periods or last period within a report? For financial reporting, these calculations are a must and also a headache for report writers. This is the first issue you have to contend with when calculating last and prior is current. I know that line seems strange, but in order to calculate the prior period or the last period, you have to know that current period. Now, you can take the easy way out and ask the user to tell you the current period via a prompt, but what happens when the report has to be run on a schedule and you can’t rely upon a user?  See … you need to calculate the value.

Step one – Calculate the current date

Date prompting is pretty straight forward in IBM Cognos Report Studio and there are a bunch of functions available to manipulate a date field. If you want to get the current date, you will use the current date function. It looks weird when you create data items based on the current data function because it creates what looks like a hardcoded date. However, it is not hardcoded but will change each day the report is run.  More importantly, if you try to type in the exact same thing into the data item and expect the date to update when the report runs…get ready to stare at the screen, because it won’t work. I know it looks the same, but when you bring over the current data function, it does something in the background that you cannot replicate with a hardcoded date.

Step two – Get Year Part

Now that you have the current date, you may need to get the year portion of the date in order to calculate the fiscal year. This is really important when fiscal calendars do not match with the Gregorian calendar. If you have ever tried to extract the year part of a date within IBM Cognos, you know that it can’t really be done with a standard function. Before my dba’s get all worked up and correct me, let me just say that I am aware of a little trick … don’t steal my thunder I am getting there. What I was saying is that IBM Cognos functions for a Sql database does not have year as a function built in, however; you can still use it in an expression. To extract the year part out of a date field, use year(date value) and that will return the year part. If you need to add to the year, you can nest the add_year function as using a variation of this calculation add_year(-1,year(date value))

Putting it all together

Once you have the current date and can extract the various components of a date such as year, month, day; you can calculate last and prior very easily for your financial reporting. Of course, experienced dba’s will point out that we could build a flag within framework manager that would identify the current period, week, month, and year instead asking report writers to calculate it individually. In many ways, the dba is right and these values should be created in framework manager and available to report writers, but I am a realist and know what sometimes the right thing to do is not always the easiest or common thing done within a business.

For more information on this or other topics, email us at Sales@LodestarSolutions.com.

Improving Framework Manager Performance

by

Framework Manager (FM) is the engine of IBM Cognos Business Intelligence. It is one of the intermediaries between BI and your data sources. The foundation of FM is that it can help you visualize your data and prepare it for reporting. However, Framework Manager is only as good as its data source.

Top 5 things you can do to improve Framework Manager performance:

1. Indexing – creating proper indexing will help FM traverse your data source more efficiently. Although FM can use a database that is not indexed, it will impact performance as the database grows.

2. Normalization – Framework Manager can work with different data structures, but works best when the sources are normalized. Don’t worry about trying to achieve the highest form of normalization – 3rd to 4th will do the trick.

3. Star Schema – Using a star schema will ensure FM creates the most efficient database request. In the rare case, you can also use a Snowflake Schema and still achieve good results.

4. Consolidate – If you have to put together multiple tables in order derive a single value, you might consider doing such work in the database and then present the results in a table for FM. Framework Manager can join multiple tables, however, the more layers you build the longer it takes to return a value. Performance is further compounded by the amount of data that must be returned.

5. Required Elements Only – When building FM only, bring in the elements you are going to use. If you have fields in a view that are not used in the report request, FM will still build the query command with all the fields in the view.

Improving Framework Manager performance is fundamental in creating a successful Business Intelligence environment. Feel free to contact us at Lodestar Solutions for more information at Sales@LodestarSolutions.com.

X