BusinessObjects Board

Are there any limitations on the number of joins in a Universe or number of dimensions in a folder?

building reports
,
#1

I’m building a fairly big (for me) universe to produce a WebI folder with 250+ fields, joining 6 derived tables that themselves are pretty crazy SQL imo (although solidly built I might add).

The data is being flattened out to show 1 row per student and all 8 semesters at college, stretched out.
Such as 1 record with these columns: GPA_Fall_Semester_Freshman, GPA_Spring_Semester_Freshman, etc. through to GPA_Spring_Semester_Senior. Plus many other columns (in series of 8, 1 column per semester over 4 years)

I’ve attached 1 of the derived tables as an example. It’s the same logic duplicated 8 times, 1 inline table for each of 8 semesters.

I’m confident the joins and SQL logic are accurate and I am testing it out as I build it.

But just wondering about limitations of any sort, on the SQL front and then presenting close to 300 fields, then downloading to Excel.
Conduct data, SQL.pdf (64.7 KB)

#2

250 objects in a universe would be absolutely tiny compared to some of the ones I’ve seen – no worries there. It’s just metadata, so you’re not consuming any additional resources by adding more objects (although your users may get overwhelmed if there are too many, and/or if they are poorly organized).

I think there is a limit in the character size of derived tables, or at least there was. 32,768 if I remember correctly. If all your derived tables are similar to the one you posted, then you should be ok there. But even though you’re under the limit, I would suggest considering using database views rather than derived tables. That’ll let the database better optimize the query, and will make your universe much simpler.

When considering the size of the generated WebI and Excel output, you’ll need to consider the number of rows – if it’s 300 columns by 10,000 rows, you should have no problem. If it’s 10,000,000 rows, you might have some performance issues.

1 Like
#3

Thanks, very helpful.

your users may get overwhelmed if there are too many, and/or if they are poorly organized

  • good point, I’m working on that

I would suggest considering using database views rather than derived tables

  • another good point. If only the database team would allow me to do so … but there may be some other options

consider the number of rows

  • good point # 3, it’s only going to be 30,000 rows max x 300 columns

Thx!

#4

another good point. If only the database team would allow me to do so … but there may be some other options

Letting the DBAs get angry at you for submitting very long complex SQL queries is a good way to justify the creation of views!

1 Like