BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Follow BOB on Twitter! 
Follow BOB on Twitter! (Opens a new window)  

General Notice: BOB is going to retire...please see details here.
General Notice: No events within the next 45 days.

Limit on number of fields in a join?


 
Search this topic... | Search Semantic Layer / Universe Designer... | Search Box
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Author Message
Jacqui_B
Forum Member
Forum Member



Joined: 18 Mar 2009

Posts: 16



PostPosted: Wed Jun 05, 2019 9:01 pm 
Post subject: Limit on number of fields in a join?

Hi all,

I am trying to join a fact table to an alias of itself in IDT. There are 21 key fields that comprise the composite key. It isn't allowing me to select as many as I need and I was wondering if this is by design.

If anyone knows what restrictions might apply I'd be grateful.

Thanks,

Jacqui
Back to top
BarkyBaloo
Principal Member
Principal Member



Joined: 04 Jul 2007

Posts: 171



PostPosted: Thu Jun 06, 2019 12:27 am 
Post subject: Re: Limit on number of fields in a join?

I take it, it must be ORACLE

If so the composite number of columns as a maximum would be 16.

If something like this is happening it may always be best to ask the Database people to create some entries that would allow you to lessen the number of columns referred to.

Sorry


icon_surprised.gif

_________________
BarkyBaloo

Your work is going to fill a large part of your life
And the only way to be truly satisfied is to do what you believe is great work.
And the only way to do great work is to love what you do.
If you haven't found it yet, keep looking.
Don't settle.
As with all matters of the heart
You'll know when you find it.
Back to top
ABILtd
Forum Enthusiast
Forum Enthusiast



Joined: 08 Feb 2006
ASUG Icon
Posts: 1891


flag
PostPosted: Thu Jun 06, 2019 8:16 am 
Post subject: Re: Limit on number of fields in a join?

Any reason that you`re trying to join a fact table to itself?
_________________
BI and Analytics Presales Consultant
------------------------------------------------------
BOXI R1, R2, R3, R3.1, R4.1, R4.2, CE 8, 8.5, 9, 10, SAP Analytics Cloud, Crystal Reports, Lumira, PowerBI, Tableau, JasperServer, iReport, LogiAnalytics, BIRST, Qlikview, Xcelcius, Netezza, T-SQL, PLSQL, DTS, SSIS, BODI, BODS, Kimble etc....
------------------------------------------------------
Back to top
joepeters
Forum Fanatic
Forum Fanatic



Joined: 29 Aug 2002

Posts: 6633
Location: Connecticut, USA


flag
PostPosted: Thu Jun 06, 2019 8:50 am 
Post subject: Re: Limit on number of fields in a join?

I don't think there is a hard limit but just an inefficient process.

I tried it in my environment with a table & alias with 24 columns. First, I created a single join with one column, and then shift-clicked to add other columns to the same join. It slowed down after about 8 columns. I had the heap monitor open, and noticed that the heap was increasing more and more with each column that I added.

Next, I manually composed the entire join statement outside of IDT (in TOAD, actually), then pasted in the statement into the join expression in IDT. This worked, even with 24 columns, with no slowdown.

Finally, instead of creating one join with all the columns, I manually joined each column in the base table to the associated column in the alias. It took a lot of clicking, but it was successful with no slowdown.

So it appears that the issue occurs only when shift-clicking columns in the join dialog.
Back to top
Marek Chladny
Forum Advocate
Forum Advocate



Joined: 27 Nov 2003

Posts: 19319
Location: Bratislava


flag
PostPosted: Thu Jun 06, 2019 9:36 am 
Post subject: Re: Limit on number of fields in a join?

The question still persists:
Why such join is needed? If the fact table is large, the performance of reports will be horrible.

A viable alternative would be to modify a procedure that loads data into the fact table to include calculation of the required field(s) and storing it/them directly in the fact table. Then there would not be a need to calculate them on the fly.

_________________
BO: BI 4.0, 4.1 | XI 3, 3.1 | XI r2 | 6.x | 5.x
DB: Oracle, MS SQL Server, DB2, Teradata, Netezza
HW: Win, Linux and AIX servers
www.perpendulum.com

Latest Blog Posts
• 2013-07-16 Number of reports in a WebI document
• 2013-04-01 Optional prompts in a universe
• 2012-06-08 Calendar table script for Oracle
Back to top
ABILtd
Forum Enthusiast
Forum Enthusiast



Joined: 08 Feb 2006
ASUG Icon
Posts: 1891


flag
PostPosted: Fri Jun 07, 2019 9:52 am 
Post subject: Re: Limit on number of fields in a join?

Marek Chladny wrote:
The question still persists:
Why such join is needed? If the fact table is large, the performance of reports will be horrible.

A viable alternative would be to modify a procedure that loads data into the fact table to include calculation of the required field(s) and storing it/them directly in the fact table. Then there would not be a need to calculate them on the fly.


I`m wondering if whoever has designed this has built a new fact at the same level of granularity so they didn`t have to modify the original ETL to add new measures. I`m thinking you could probably fudge something with aggregate awareness to manage this in a reporting scenario. Agreed it`s fixing a poor design choice though!

_________________
BI and Analytics Presales Consultant
------------------------------------------------------
BOXI R1, R2, R3, R3.1, R4.1, R4.2, CE 8, 8.5, 9, 10, SAP Analytics Cloud, Crystal Reports, Lumira, PowerBI, Tableau, JasperServer, iReport, LogiAnalytics, BIRST, Qlikview, Xcelcius, Netezza, T-SQL, PLSQL, DTS, SSIS, BODI, BODS, Kimble etc....
------------------------------------------------------
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0279 seconds using 17 queries. (SQL 0.0028 Parse 0.0009 Other 0.0242)
CCBot/2.0 (https://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo