Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED (but open to suggestions)] Help With Multiplicative Effect

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED (but open to suggestions)] Help With Multiplicative Effect
 Login/Join
 
Dan Pinault
Guru
posted
This may be more of a database management question than a WebFOCUS question but since I'm trying to see if I can solve the issue with a WebFOCUS Managed Join I thought I'd post it here.

This message has been edited. Last edited by: Dan Pinault,


7.7.05M/7.7.03 HF6 on Windows Server 2003 SP2 output to whatever is required.
 
Posts: 393 | Location: St. Paul, MN | Registered: November 06, 2007Report This Post
susannah
Expert
posted Hide Post
seems odd, Dan
Does your OEM_INFO dimtable have more than 1 row for each OEM_ID? (double check this)
You say that only join to the OEM INFO table causes the multiple records, yes?
Do you have
JOIN ... TO UNIQUE OEM_ID IN OEM_INFO

you're doing the joins inside the master, yes?
What about taking them out of the master, and doing them in join statements until you can nail this sucker down...

ps..really nice problem layout technique, by the way.



In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Dan Pinault
Guru
posted Hide Post
Thanks Susannah. Yes, it is possible that a BaseModel is built by more than one OEM. I thought about that after I created the example but I didn't want to go back and change it (lazy). I guess a better example would have been to say that as soon as there are multiple instances of BASE_MOD_ID I get the Multiplicative Effect. So, just doing...
SUM R_QTY
BY R_COUNTRY
BY PROD_YEAR

would cause the problem.

Yes, the joins are being created in the synonym. I added the segments via metadata import so the PK/FK relationship would be recognized. I'll try doing the joins in a procedure and see what happens.

Finally, I tried creating a bridge table between the Base Model Table and Registration Table that consists of existing distinct combinations of BASE_MOD_ID and REG_ID. I guess that isn't doing anything for me that I didn't have set up in the original structure.

Stay tuned...


7.7.05M/7.7.03 HF6 on Windows Server 2003 SP2 output to whatever is required.
 
Posts: 393 | Location: St. Paul, MN | Registered: November 06, 2007Report This Post
Dan Pinault
Guru
posted Hide Post
Well, I need to move on. It's either that or pull my hair out!

I've got to give a demo of this particular application in 2 days and there is a lot of work to do on the launch pages and such.

For the time being I just created a SQL View that basically functions like a big flat file. We're talking about less than 100,000 rows so I'm not too worried about performance.

However, I DO need to figure out this many-to-many relationship thing pretty soon! I guess that's what I get for being monogamous for so long - Wink

Cheers,

Dan


7.7.05M/7.7.03 HF6 on Windows Server 2003 SP2 output to whatever is required.
 
Posts: 393 | Location: St. Paul, MN | Registered: November 06, 2007Report This Post
Francis Mariani
Expert
posted Hide Post
Without digging into your specific tables, I prefer to create MS SQL Server views for these type of joins instead of joining the tables in FOCUS.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
RSquared
Guru
posted Hide Post
Dan,

Just for a test, have you tried doing a simple join of 2 tables, holding the output and then joining the output to the other table? This may give you a better idea of where the multiplicity comes from.


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Report This Post
Dan Pinault
Guru
posted Hide Post
Francis - I like doing the joins in the WebFOCUS synonym because, if they are set up right, WebFOCUS will pass a SQL join command only for those tables required for the specific request. This makes queries more efficient. With large tables this can be very tangible. My understanding of SQL Views is that all the joins are issued every time you make a request from the view regardless of which columns are included in the request. I could be wrong about that.

RSquared - When I have a chance to test this I will try your idea.

Thanks all!

Dan


7.7.05M/7.7.03 HF6 on Windows Server 2003 SP2 output to whatever is required.
 
Posts: 393 | Location: St. Paul, MN | Registered: November 06, 2007Report This Post
Francis Mariani
Expert
posted Hide Post
Dan,

I was digging around the documentation I have downloaded over the years and came across this old chestnut, Information Builders Systems Journal, March/April 1997
quote:
Have you ever heard the expression “the multiplicative effect,” and thought it had something to do with higher mathematics?
I don't know if this will help at all, but it might...


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Dan Pinault
Guru
posted Hide Post
That's great. Thanks! There is also an article on it in the Winter 2009 WebFOCUS Newsletter about it (also by Art Greenhaus.)

It was this article that got me thinking that I could use a FOCUS-Managed Join to get around my issue.


7.7.05M/7.7.03 HF6 on Windows Server 2003 SP2 output to whatever is required.
 
Posts: 393 | Location: St. Paul, MN | Registered: November 06, 2007Report This Post
susannah
Expert
posted Hide Post
wow
who knew there was a winter 09 issue!
(i subscribed, but that has never worked in living memory.)
thanks!



In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Francis Mariani
Expert
posted Hide Post
Yup, I keep subscribing every couple of months or so, hoping it'll take one day.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
GinnyJakes
Expert
posted Hide Post
Ditto!

Kerry, you might want to ask someone about this.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Dan Pinault
Guru
posted Hide Post
Same here. I only found the article after searching for 'multiplicative effect'.

Every time I 'stumble' upon the newsletter I'm always glad I did!


7.7.05M/7.7.03 HF6 on Windows Server 2003 SP2 output to whatever is required.
 
Posts: 393 | Location: St. Paul, MN | Registered: November 06, 2007Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED (but open to suggestions)] Help With Multiplicative Effect

Copyright © 1996-2020 Information Builders