tech tips

Latest Tech Tips

6/01/09 - Where is this number coming from?

How did my forecasted days sales outstanding increase 250% in one month? As a user and administrator of Cognos Planning products you may come across numbers in your Contributor applications that you have no clue as to where they came from. The data is buried somewhere within the model. Maybe you inherited the system from someone else or a consultant built the model without a lot of involvement from you. Maybe you built the model a couple of years ago and, if like me, have a hard time remembering what I did two weeks ago let alone two years ago. But you need to find where the number is coming from and fix it fast. Where do you start? Administration links, D-links, Access Tables, Analyst to Contributor links, System Links, etc.

When looking for the source of a number I look at the Analyst model first because you may have an administration link that targets the cell but if the cell is also the target of an intra-model d-link, the d-link will take priority and the administration link will not populate the cell.

In Analyst go to the library used as the Contributor template and open the cube that has the item you are investigating. If the item is a calculation, edit the d-list or just hit F7 to see the calculation. Be aware of calculation priorities in the cell. It is best to select the same items in each of the dimensions of the cube as was viewed in the Contributor tab to make sure it has the same calculation priority.

If the item in question is a detail item drill down into the cell to see if it is populated via a intra-model d-link by hitting the drill down icon or pressing F9. Even if nothing is returned by performing the drill down, look at all the d-links running into the cube. Sometimes a cell is not populated directly by a d-link but it is populated indirectly by a link targeting a calculation that is broken back into the cell.

Sometimes you may have multiple links targeting a cell. Look in the d-cube Update section to see which d-links are actually being executed and in which order. Also take note of the Mode while looking at the d-links. Is it Fill, Substitute, Add, Subtract and in the d-link options does it use any scaling?

You may have a complex model with a long daisy chain of calculations and d-links. If this is the case it is very helpful to create a data flow diagram. Work backwards and add items used in calculations and items linked in from other cubes.

Items in question may be coming from hidden cubes or cells in the model. To make it easier to trace the data, make the hidden items writeable or readable by adjusting the access tables in the CAC and GTP. If this is not possible because other users are using the system or adjusting the access tables would be very difficult; create A>C d-links from the Contributor model into the template model in Analyst. Just select one or two e-list items for investigation. You do not need to bring back the data for all the items in the e-list. While referring to your data flow diagram you will see the data in the model and you will be able to detect where the data looks off.

If you find a detail cell in the model that is causing your problems and it is not a writeable cell, you need to find the source populating the cell. There are four places to look: Analyst to Contributor d-links, Administration links, System Links, and text files imported into Contributor. After you find the source of your questionable data, look into the source to determine the validity of the data. You may find an issue with your source data or the links used to bring in your source data. In that case fix it and reload. Sometimes you will find the data is correct and you just might have to explain to your boss that days sales outstanding did increase 250%.

Want some more tech tips? Click on the below articles.

3/20/09 - Planning Error Log files