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     Fact Value get multiplied

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Fact Value get multiplied
 Login/Join
 
Praveen Kumar
Member
posted
Hi All,

Gud morning. In WebFocus,I have created a Master file in which Fact table FACT1 is joined with a dimension table DIM1 and through this dimension table, I have joined another Fact table FACT2.

Both the fact tables (FACT1 and FACT2) are referencing the same dimension table DIM1 now.

While reporting, in a single report, when I insert any attribute from DIM1 and a value from Fact1 and also a value from FACT2 tables, the values are getting multiplied. Why is it so?

My join conditions are correct and If I take an attribute from DIM1 and a value from any one of the fact table, my values are correct.

Can anyone say how to correct this in 8.0.0.3?

Thank You.
 
Posts: 28 | Registered: December 28, 2011Report This Post
Waz
Expert
posted Hide Post
I assume you are SUMming the fields.

The only logical solution is that the introduction of the DIM1 fields is returne multiple records from the join.

Can you post the code, and have you checked the SQL being generated ?


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Tony A
Expert
posted Hide Post
quote:
when I insert any attribute from DIM1 and a value from Fact1 and also a value from FACT2 tables, the values are getting multiplied. Why is it so?

As Waz infers, this is likely to be due to a cartesian product. Understand the data contents as well as the way in which you are joining the sources.

T


In FOCUS
since 1986		WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
njsden
Virtuoso
posted Hide Post
You seem to be doing an indirect JOIN from FACT1 to FACT2 via the DIM table, and unless you can guarantee that each record from FACT1 has one and only one matching record in FACT2, having a cartesian product is almost unavoidable and this is not a WebFOCUS issue, is just the natural behaviour of SQL.

As Waz and Tony A have pointed out, you have to be aware of the data you're dealing with.

Can you query the database tables directly via an SQL client? If so, try joining FACT1 to DIM1 to FACT2 directly in SQL and you'll likely see the very same results.


Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
WF_IL
Platinum Member
posted Hide Post
Hi

Another approach would be (if suitable to your business requirements) is to structure the joins as multi fact with shared dimensions (via a 2 root cluster master file, supported since 7704 ) so then you can create a report SUM fields from fact1 and fact2 BY+WHERE fields from the shared dimension

or you can create manually 2 queries and then MATCH or Join them:
TABLE 1: join fact1+DIM table: SUM fields from fact1, BY+WHERE on
Dimension table -> HOLD1

Table 2: join fact2+DIM table: SUM fields from fact2, BY+WHERE on Dimension table -> HOLD2

Join or Match HOLD1 or HOLD2 using the common fields from the Dimension table


Yours,
Eran
SRL Products

http://www.srl.co.il

 
Posts: 97 | Location: Tel Aviv, Israel | Registered: November 20, 2005Report This Post
Francis Mariani
Expert
posted Hide Post
If these are SQL database tables, turn SQL traces on - you will most likely get a "multiplicative effect" message which suggests there's something wrong with a JOIN.


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
j.gross
Virtuoso
posted Hide Post
quote:
a 2 root cluster master file, supported since 7704


Eran --

Haven't heard of that before. Where is it documented?


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
WF_IL
Platinum Member
posted Hide Post
Hi

I don't know where its documented, however in WF8 (8002+ )you can create sample data and a cluster master with multiple parent segments
from the WFRS console->Applications -> new -> samples -> webfocus retail demo


Yours,
Eran
SRL Products

http://www.srl.co.il

 
Posts: 97 | Location: Tel Aviv, Israel | Registered: November 20, 2005Report 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     Fact Value get multiplied

Copyright © 1996-2020 Information Builders