Importing Objects from FM into Other FM Models - Drawbacks

There are so many issues when trying to use this feature provided by Cognos. For a simple activity I tried importing 2 Data Source Query Subjects from Physical Layer of Model A into Test Layer of Model B. But the objects got imported into Physical Layer of Model A. To add on to my woes, it also imported a whole lot of other objects that had no dependency on the imported Query Subjects. These objects were Functions. So don't know if this has to do only with functions.

Another issue I found with this approach is that relationships between imported objects also get imported. And unlike Data Source based imports where users are prompted to choose to either create relationships or not, in case of model based imports this option is not provided requiring me to manually look up the imported objects and clean out those not required.

Copies of source model packages were also created which may be because the imported objects are part of the source model packages.

This is a powerful feature when you want to save time by re-using objects designed in other models but with all the issues listed out above, it seems to be a painful feature to use for the time being, until Cognos fixes these bugs. What can make this feature easier to use apart from fixing of the bugs would be options to select the kind of objects to import like 'relationships', 'calculations' etc. that may be dependent on the imported objects rather than having Cognos import all such dependent objects.

Folders/Namespaces and Loops

As we all know loops are a complete no-no in Cognos. So having said that, I didn't want to write up this article, but thought that someone out there might benefit in knowing how folders/name spaces impact loops.

I stumbled on this while trying to help a colleague on his FM model. The model was based on an ER data model and not a dimensional data model. So loops were bound to be present but luckily there were just 12 tables to deal with, still a developer's nightmare. The issue the user was facing was with unpredictable queries getting generated which was bound to occur with loops.

I am simplifying the scenario here. Lets say there are 5 tables A, B, C, D and E. A is joined to D through B and C. A is also joined to D through E.

A-B-C-D
A-E-D

As you can see there are now 2 ways to get to D from A. If a user were to pull in items from A and D, Cognos may or may not include B/c or E. Assuming Cognos takes the shortest path (think I read this somewhere, but I may be wrong), it shouldn't have included B and C, but in the user's case it was.

After digging we found that the query subject E was placed inside a sub-folder under the main folder. So when object E was moved to the main folder where all the other objects were residing the query took the path through E.

So here's my assumption on how Cognos evaluates loops. It looks for the shortest path in the same folder as most of the objects involved in the query and only when such a path doesn't exist it evaluates other paths that involve other folders and name spaces.
 

Remap to New Source in FM

Very recently found this option really handy in FM. This option is real useful when you have a model already built and at a later date there are changes to the tables being used with the possibility of an existing table getting dropped and the information being made available in another table or in a new table.

Dropping a data source query subject would invalidate your business layer and in earlier versions of Cognos this would result in re-designing and re-developing certain sections of the model. This has now been made easy with the "Remap to new source" option available on model query subjects. With this option you can remap individual columns in a model query subject to other columns in your physical layer.

You can also remap multiple columns at one go by setting options to match columns from the source to target and then drag all columns from the data source query subject to the model query subject and Cognos will automatically remap the various columns based on the options set. This is useful when your query subjects contain a huge number of columns.



 

Calculations in Dimensional Query Subjects

A long time back while working on a relational ad-hoc package request I ran into an issue. I have been unable to explain the cause for it and the solution involved a work-aound that I am unhappy with. I am posting it here hoping that someone might be able to offer a better solution or even validate my reasoning on the issue.

Let us assume the ad-hoc package involves the query subjects - Product, Revenue and Forecast. Product behaves like a dimension due to being on the 1 side of the 1-N relationship to the other 2 query subjects. I have created a calculation say Calc A in the Product Query subject. The package also has a stand-alone calculation say Calc B defined as Revenue / calc A.

In Query Studio, when you drag Product and Calc B the data is off. The SQL generated by Cognos involves a min on Calc A even if aggregates are set to Total or Sum.Including the function Total inside of Calc A also doesn't help. No determinants are involved as the fact tables are at the same granularity and join to the dimensions at the same granularity.

My assumption on this issue now is that Cognos treats Product as a Dimension and hence includes a Min on the dimensional data item.

My work-around to the above issue was to create a shortcut to Product and join it to Product with a cardinality to make this behave like a fact. This has the downside of the table being queried twice and involves a stitched query when calc B is pulled into the query. Since the Dimension table in my case was not huge there wasn't much performance impact.

I will otherwise have to manipulate cardinalities to make Cognos treat Product as a fact which is not advisable as it impacts other queries and still will not remove the stitched query. Any thoughts on this?
  

Macro Prompts in FM Model for Adhoc Packages

Working on creating an Adhoc Package for use in Query Studio. The package needs to include base metrics and compound metrics and users need to be prompted for Date range and other Dimensions on pulling any of the metrics.

For the prompts, I included filters in the model query subject and built of the metrics. But what I noticed was that, only the base metrics prompt the users and if you drag a compound metric you are not prompted the first time in Query Studio until you hit the re-run button.

Work-around is to use macro filters rather than ?parameter? filters.
.