CONSULTING
SOLUTIONS
PARTNERS
RESOURCES
CORPORATE
Twitter Facebook LinkedIn
Email [email protected]
Phone +1 713.457.7474

RESOURCES CATEGORY: IBM Cognos Blog

IBM Cognos TM1 Feeders

BLG

What Are Feeders?

In a sparse cube with relatively few values compared to the number of cells in the cube, it can cause a severe drag on system performance to require the cube to calculate every cell when only a few values will result from those calculations. To combat this, TM1 allows you to use the SKIPCHECK function to tell the rules to only calculate values for cells that will result in non-zero values.

To put it briefly, feeders are merely markers signifying to the rules which values are to be calculated and which are to be ignored. Think of it in the context of a cubeview. The feeders essentially mark each cell that needs to be calculated. The rules then come behind that and actually perform the calculations on only the marked cells.

Feeder Misconceptions (Or, How To Overfeed)

One of the most common misconceptions – especially when you’re writing your first rules – is to feed all parts of a calculation. Take the following rule – calculating an employee’s monthly bonus in a generic headcount cube – as an example:

[‘Monthly Bonus’] = N: ([‘Annual Salary’] * DB(‘Calendar’, !Year, !Period, ‘Monthly’, ‘Percent of Year’) ) * [‘Bonus %’];

I’ve found that many developers will assume they need to feed all parts of the calculation. So, for the above example, they will feed “Monthly Bonus” with “Annual Salary” and “Bonus %” from the same cube, and “Percent of Year” from the “Calendar” cube. However, all they’re doing is “marking” the cell for calculation three separate times when all they had to do was mark it once. This is adding three times the amount of processing time per cell. Extrapolate that out to the number of cells in your cube and you’re talking about a serious performance issue in your model.

So the cell only needs to be fed once, and to do this in the most efficient way possible you feed it with the part of the calculation that will most likely signify that the cell will result in a value of zero.

To elaborate on this point a bit: what universal logic can we grasp that will help us in determining the best possible feeder? Well, first you have to separate these logical truths into two categories: multiplication/division calculations, and addition/subtraction calculations.

Feeders for Multiplication and Division Calculations

Multiplication and division calculations are our best friends. In these calculations, we truly need only one feeder. Take a simple example of calculating a monthly FICA amount at the most basic level – Monthly Salary * FICA %. If either of these amounts equals zero, the FICA amount is going to be zero.

So we take this logic and conclude that, in fact, we only need one feeder – the one most likely to be zero. The FICA percentage is a single number (a lot of companies take them as two – Social Security and Medicare, but for simplicity’s sake we’ll assume that it’s one percentage), provided by the Social Security Administration every year. This percentage will never be zero, so we can rule it out as a possible feeder. The Monthly Salary, however, is a good feeder because unless our company has an employee who loves his job so much that he’s willing to work for free, it should be a pretty good indicator that we need to calculate that employee’s FICA %.

This same logic can be applied to division calculations. If the numerator is zero, the calculation must result in zero. If the denominator is zero, the calculation is illogical because you cannot divide anything by zero, and this is handled by TM1 as a zero.

Feeders for Addition and Subtraction Calculations

Feeders for addition and subtraction calculations, while not trickier, are slightly more involved. Take FICA for instance, and now assume that we do prefer to split FICA into its’ two parts – Social Security and Medicare. If we didn’t want to include these as children of a consolidation, we would calculate “FICA” as:

[‘FICA’] = N: [‘Social Security’] + [‘Medicare’];

However, if either “Social Security” or “Medicare” were to be zero, this would not guarantee that “FICA” will be zero for the obvious reason that x + 0 = x. The solution to this, unfortunately for us, is to feed both “Social Security” and “Medicare”. The same can be said for subtraction calculations, as x – 0 = x. With this in mind, let’s examine each of the parts of the above rule to see which we should use to feed “Monthly Bonus”:

  • Annual Salary: every employee should have a salary, so if we run across a “Monthly Bonus” cell belonging to an employee slot that does not have a salary, we can safely assume they will have no monthly bonus since any value multiplied by a zero will result in a zero. But this does not necessarily mean they will have a Monthly Bonus since not all employees receive a bonus, so let’s continue to the other parts of the calculation.
  • Percent of Year: we can instantly eliminate this as an option because there will never be a non-zero “Percent of Year” amount. If we were to use this as a feeder every cell would be fed, negating the entire advantage of feeders.
  • Bonus %: assuming every employee who receives a bonus has their own “Bonus %” populated, this is a great indication that “Monthly Bonus” for a given employee will need to be calculated. We should use “Bonus %” to feed “Monthly Bonus”.

[‘Bonus %’] => [‘Monthly Bonus’];

However, another misconception is that a feeder even needs to be a part of the fed calculation. This is not at all true. Remember, you’re just “marking” a fed cell, in the easiest and most efficient way possible.

Say that we need to derive an hourly pay rate from an annual salary:

[‘Hourly Rate’] = N: [‘Annual Salary’] \ DB(‘Calendar’, !Year, !Period, ‘Annual’, ‘Hours’);

Based on our prior analysis of “Monthly Bonus”, we can eliminate “Hours” in the Calendar cube as a contender to feed “Hourly Rate”. We could definitely use “Annual Salary” to feed these cells.

However, say we have another element called “FTE”. This is basically used to signify a valid employee with a value of 1, and could also be used to calculate part-time employees with a value less than 1 – say, 0.5. But no matter what specific value it is, if it’s greater than zero it signifies a valid employee. And since any valid employee will have some value in “Annual Salary”, “FTE” would also be a great feeder for “Hourly Rate”:

[‘FTE’] => [‘Hourly Rate’];

At this point you might be asking yourself, “Why wouldn’t I just use ‘Annual Salary’?” Well, in the instance of a single rule such as “Hourly Rate”, yes it might not make much sense to confuse the situation by using a value outside of the components of the calculation you’re feeding. However, consider the situation (a very likely one) where you have a number of these elements that you can assume every valid employee will have in all situations. Instead of feeding them with parts of their calculations, which will all be different and obviously dependent upon how they’re calculated, you can potentially feed them all with “FTE”:

[‘FTE’] => [‘Hourly Rate’],
[‘Social Security’],
[‘Medicare’],
[‘FUTA’],
[‘SUI’];

This example is a purely aesthetic one, but it could potentially help performance in the event you are pulling amounts from another cube, one-for-one with no calculation on the amount. We’ll discuss this further in the following section on inter-cube feeders.

Inter-Cube Feeders and How to Avoid Them

In my experience inter-cube feeders are the biggest drag on performance, and they should be avoided if possible. Continuing our example with “FTE”, we can use this value to aid us in pulling amounts from another cube. Let’s say our originating cube (“PositionInfo”) is just a snapshot in time of employees (i.e. it does not have time dimensions) and we need to pull those values into our “Headcount” cube and apply them to Period and Year. We can feed “FTE” in the “Headcount” cube like this:

[‘FTE’] => DB(‘Headcount’, ‘Fcst Years’, ‘Year’, !Scenario, !Company, !CostCenter, !Location, !Employee, !EmpInfo);

It (hopefully) goes without saying that when you’re dealing with inter-cube feeders you’re going to run into situations where your dimensions do not match and you’re forced to feed to consolidations, as we do in this example when we feed to all forecast years in the “Year” dimension and all months in the “Period” dimension. Feeding to consolidations is probably the single biggest reason as to why you should avoid inter-cube feeders as much as possible. When you’re feeding to a consolidation such as “Fcst Years”, you’re actually creating multiple feeders, equal to the amount of elements in the consolidation. To illustrate this, imagine we actually have time dimensions in our originating cube (“PositionInfo”); we would feed the “Headcount” cube with one feeder:

[‘FTE’] => DB(‘Headcount’, !Year, !Period, !Scenario, !Company, !CostCenter, !Location, !Employee, !EmpInfo);

However, when we feed to “Fcst Years”, we’re essentially creating the following feeders:

[‘FTE’] =>

DB(‘Headcount’, ‘2011’, !Period, !Scenario, !Company, !CostCenter, !Location, !Employee, !EmpInfo),
DB(‘Headcount’, ‘2012’, !Period, !Scenario, !Company, !CostCenter, !Location, !Employee, !EmpInfo),
DB(‘Headcount’, ‘2013’, !Period, !Scenario, !Company, !CostCenter, !Location, !Employee, !EmpInfo),
DB(‘Headcount’, ‘2014’, !Period, !Scenario, !Company, !CostCenter, !Location, !Employee, !EmpInfo);

As with feeding each component of a calculation, this creates a huge strain on performance and should be avoided. But it cannot always be avoided, and so you should take actions that minimize the adverse affects. So let us operate under the assumption that if we can feed our great “universal” feeder (“FTE”) from the “PositionInfo” cube to the “Headcount” cube, we can then create all our other feeders inside the “Headcount” cube. Here would be our feeder from the “FTE” element in the “PositionInfo” cube to the “FTE” element in the “Headcount” cube:

[‘FTE’] => DB(‘Headcount’, ‘Fcst Years’, ‘Year’, !Scenario, !Company, !CostCenter, !Location, !Employee, ‘FTE’);

We would then be able to use that newly-fed “FTE” element in the “Headcount” cube to feed all of the elements for which we otherwise would have written inter-cube feeders:

[‘FTE’] => [‘Hourly Rate’],
[‘Social Security’],
[‘Medicare’],
[‘FUTA’],
[‘SUI’];

Conclusion

Hopefully this can help some of you to improve your model’s performance through making your feeders more efficient. We’re never going to completely eliminate the headaches that come with feeders, but we can at least minimize the heartburn that comes with a poorly-performing system.

Business Intelligence (BI)

BLG

Business intelligence (BI), once a competitive differentiator, is now a commodity. Most companies have implemented BI solutions that provide historical reporting, dashboarding, metrics and scorecarding for past events. Companies know what has happened but the ability to know what will happen will be the competitive advantage that companies need to excel in this volatile and ultra-competitive environment. Predictability is the next step in the evolutionary process of Business Intelligence.

Traditional Business Intelligence and data warehousing focus on strategic, long term decision support. While strategic Business Intelligence continues to be a requirement to support long range vision, Predictive Business Intelligence (PBI) takes business Intelligence beyond a process that has traditionally looked backwards and has been reactive in nature. PBI empowers the enterprise in realizing competitive advantages and provides the business with the necessary agility to meet the challenges of today’s rapidly changing business environment by mitigating risks and maximizing opportunities. PBI greatly improves both long term strategic decision making and near team operational decisions.

The ability to make Predictive strategic decisions will separate enabled companies from their competition enabling them to capitalize on opportunities and reduce exposure to risk. Statistical analysis on operational and transactional data will provide insightful information on business trends and enable the business to make strategic decisions quickly and more effectively. For example, when a retail chain is determining whether to establish a presence in an unfamiliar territory, it could utilize growth data from other locations and combine it with the local data as well as current projection data to provide support for the decision making. The data might be sketchy and sparse, but statistical analysis will offer a sound basis for decision making. Other examples can be found in oil & gas exploration or pharmaceutical development projects for instance. These projects often entail long development cycles and considerable up-front cost the outcome of which has significant impact on the overall performance of the business. Predictive decision making will enable those companies to analyze more data in order to gain a complete view of the business cases and leverage proven statistical models to aid those impactful decisions. The completeness and quality of the data analyzed are critical to determine the accuracy of the prediction. Although companies in these verticals go to great lengths to develop elaborate risk management models to address common concerns, predictive business intelligence In support of existing risk management processes, provides a richer set of data and more interactive analysis to ensure a better outcome.

 

Cognos BI 10 Migration and Lifecycle Manager

BLG

At PREDICTif Solutions, we have successfully migrated Cognos 8.4 to Cognos BI 10. The migration was pretty straight forward.   We imported Cognos 8 content to Cognos BI 10 and verified it through Lifecycle Manager. We discovered that about 85% of the reports migrated successfully without any special attention and only a handful of reports required the same amount of attention as the most complex reports from a Cognos 7 to Cognos 8 migration.

By far, the most time consuming part of the migration process was verifying reports.  We used Lifecycle Manager (Upgrade Manager in previous releases) to speed up this process.   Lifecycle Manager allowed for a systematic way to compare original and updated reports automatically. If there were any differences between each version of Cognos, reports could be compared side-by-side and any discrepancies are marked in red.

Lifecycle Manager is an invaluable tool, but it does have its limitations that require some additional effort. Other than multiple report formats, it is not designed to verify a report on multiple scenarios. A user could run the report multiple times with different scenarios, but without history, there is no way to keep track of previous results.   There is also a similar limitation in dealing with multiple data sources. Normally, if there were multiple data sources in Cognos BI, the user is prompted to choose which data source to use. However, Lifecycle Manager does not prompt the user. The only way to process the report in Lifecycle Manager would be to disable multiple data sources in Cognos Administration or manually set the report in Cognos BI to use a specific data source.   Still, both of these limitations can be further minimized by having multiple projects Lifecycle Manager. Different reports scenarios or data sources can be accounted for by different projects.

How to consolidate data in TM1

BLG

When loading data into a TM1 cube, the Turbo Integrator command used is CellPutN.  This sends a numeric value to a specific cell in your cube.   This command does not consolidate values, if the cells you are loading to have a value, it will be over written by the new value you send.  If you are loading two or more records to the same cell, each value will be over written by the next only the value from the last record from data load file will be retained.

For example, below is the data you want to send to the cube.

ACCOUNT STORE NUM COST CENTER CURRENCY LOCATION YEAR MONTH DATA
10050 C1 MKT USD NYC 2011 JAN 1000
10050 C1 MKT USD NYC 2011 JAN 500

 

Note that all cells (dimensions) for these two rows are the same but the data is different. By using the syntax CellPutN, the result for Account 10050 will show as 500 not the desired consolidated value of 1500.  To accumulate data as you load, you need to add the existing value from the cube to the data you are loading before loading the data to the cube.  Using the TI function CELLGETN to retrieve numeric data from a cell in the cube and then use CellPutN to send the consolidated value back to the cube.

TI Solution in two steps:

vDATA = vDATA + CELLGETN ( ‘FINANCE’,ACCOUNT,STORENUM,COSTCENTER,CURRENCY,LOCATION,YEAR,MONTH )

CELLPUTN( vDATA ,’FINANCE’, ACCOUNT,STORENUM, COSTCENTER, CURRENCY, LOCATION, YEAR , MONTH )

email [email protected]
phone +1 713.457.7474
Twitter Facebook LinkedIn
© PREDICTif Solutions. All rights reserved.