Page 1 of 1

Join problem in FM

Posted: Mon Jul 18, 2016 6:16 pm
by tm1fmuser
Hi,

Anyone caould help me with a possible loop join issue ?

Thanks in advance to anyone willing to at leats advice something,

Re: Join problem in FM

Posted: Wed Jul 20, 2016 9:11 am
by BariAbdul
Hi tm1fmuser,Although name may sound bit similar this is not Cognos Forum rather it is Cognos TM1 Forum.

Please post your query in Cognoise forum under Framework Manager section:

http://www.cognoise.com/
https://www.ibm.com/developerworks/comm ... ags=cognos

http://businessintelligence.ittoolbox.c ... ognos10-l/ Thanks

Re: Join problem in FM

Posted: Wed Jul 20, 2016 11:55 am
by jim wood
Bari,

It is a TM1 forum but we have sections for the likes of BI as TM1 interfaces with it. You'll also notice that we have forums for other products like Essbase. While your advise to post on other forums is sound, I wouldn't go driving people away just because of the title. If we wanted to do that then we'd close this section of the site down.

Fmuser,

It may take a while for you to get a reply here as the posting rate isn't that great, so I would also post this on the sites provided by Bari,

Jim.

Re: Join problem in FM

Posted: Tue Aug 02, 2016 12:53 am
by sachin
tm1fmuser wrote:Hi,

Anyone caould help me with a possible loop join issue ?

Thanks in advance to anyone willing to at leats advice something,
Yes, alias shortcut is a way to address the join traps you see in the FM model. In the (g)olden days of Impromptu, we had the ability to define join weights and that was one way you could force the engine to generate the SQL path you'd want. It went way when Cognos ReportNet came on along with FM. We had put in a ENH request to bring that feature back into FM. It has been several years I last worked on FM. A lot might have changed, I am not sure if the join weights is introduced or not.

You can find dozens of links in Cognoise or IT Toolbox regarding this problem.

Re: Join problem in FM

Posted: Mon Aug 15, 2016 2:32 pm
by st2000
I learning always to avoid modelling relations from Dim to Dim or Fact to Fact, but only let Dims touch Facts and vice versa. You could try to make first a join on all these Dim-tables to get a resultset ready to connect to the Facttable. Wrap this into an entity and join it to the Fact: Dim-Entity 1 .. N Fact. Some more similar stuff could be learned here: http://www.ibm.com/developerworks/data/ ... ge605.html

Note that FM is not a DBMS-Engine, but a tool to model entities under the special FM rules. Cognos generates from this queries in Cognos SQL which afterwards translates to the genuine SQL used by the DBMS. Check out here also: https://books.google.de/books?id=vQwYHs ... r.&f=false