BO XI3.1 Universe

[former_member213259]

Hello All,

I have report requirements and need to create Universe for the same. I need your valuable input in creating the Universe.

One of the constraints is that there is a limit on the number of records fetched in the WebI report (lets call this MAX_RECORDS).

I have two tables User_Table, User_Access_Data.

Columns in User_Table: C1, C2, C3, C4, C5. Primary Key = C1, C2.

Columns in User_Access_Data: C1, C2, D1, D2, D3, D4, D5, E1, E2, E3. Primary Key = C1, C2, D1, D2, D3, D4, D5.

User_Table and User_Access_Data are joined using the conditions specified below.

User_Table.C1 = User_Access_Data. C1 AND

User_Table.C2 = User_Access_Data.C2.

The Universe should meet the two requirements specified below.

1. One Report requires columns from both tables. For example we need to display columns - C1, C2, D1, D2, D3, D4, D5, C3, C5. We have acheived this through a straight forward join. The report has query filter on C1 and thus we satisfy the MAX_RECORDS constraint.

2. Second Report requires unique values for columns C2, D1, D2, D3, D4, D5, C3, C5. Since we don't have C1 in this case, no query filter is applied and hence we get an error related to MAX_RECORDS.

I have tried few options (derived table, alias, ...) but unable to acheive the desired results with one set of objects. We are using Oracle database.

Appreciate your timely help.

Thanks!

[Former Member · Accepted solution]

Can you explain what exactly you want to achieve using the max records..

If you just want to restrict the number of records that can be fetched on a query on top of universe there is a universe parameter you can just setup for the desired number of records.. This parameter is defaulted to 5000 at the beginning of creation of a new universe..

[former_member213259 · Accepted solution]

The results for the second report are partial because of MAX_RECORDS constraint. Unfortunately we don't have the option to change this value.

My question was aimed at how to design the universe to satify both the reports.

[Former Member · Accepted solution]

Hi,

You said that joins bewteen the tables are based on C1 and C2 columns.(i.e. you must have join the tables with these two columns). Even if you select the object C1 in the report or not, the join should be coming in picture, did you check the sql generated by BO for the same?

gaurav

[former_member213259 · Accepted solution]

Hi,

Yes irrespective of C1, join condition is being used. That is why it is fetching more number of records and exceeding the Max_records limit.
I need some solution where it fetches distinct records when the tables are joined.

[Former Member · Accepted solution]

The default behavior of Webi is not to include any join if any object of that table is not included in the query..  There could be a mandatory or universe level filter or something making that join to appear..

For distinct record to be select from query level you will need to deselect the duplicate option from query properties.. check the screenshot below..

Also make sure query stripping is enabled from report properties..

[former_member213259]

Yes, we have already implemented the same solution and it works fine. Thanks for the soultion!

[Former Member]

Sowrabha S if this issue is resolved for you then please close the thread by marking the correct answer or any other approach which worked for you for the benefit of the forum..

[former_member213259]

Sure. Will do that..