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.
Monday, July 18, 2011
Subscribe to:
Post Comments (Atom)
Posts
1 comment:
interesting, I think this is correct. Actually, I think it will try to do some optimization and use the least expensive operation, This is usually going to be the one with the shortest join path. But it could also be something as random as the first one you added to the model. Some sql output would be interesting here to determine this specifically.
Post a Comment