Derived Tables in Universe Designer and Web Intelligence 3.1.

[Former Member]

Good day Experts,

I trust you are all well. I need to borrow your expertise.

I am working with Business Objects 3.1.5 - Universe Designer and Web Intelligence (Oracle database as a source).

I am creating a universe with derived tables for a query - using two tables with a date restriction in the derived table definition i.e. want data for only a specific time range. Now Parsing is OK for the derived tables and the Integrity Check is OK on the universe. But the issue is that when I use the derived tables in a query and execute the report returns an empty data set - NO ERROR.

However when I copy the SQL from the reporting tool - WebI and execute on SQL developer I get a data output and when I use data from physical tables in the report I get a data output as well. *confused for days*

I  am not sure what I am missing but I think the issue might be with the derived tables - just don't know what exactly.

All and every contribution is welcome.

Thank you in advance.

Regards,

B Mpunga

[Former Member]

Hi,

Just an expansion on the above question.

In the universe I have 4 class (with objects) - 2 classes based on physical tables and 2  classes based on derived tables.

Now, when I create a query on WebI using the objects that belong to the class based on the physical tables I get a data output. However, when I edit the query and add objects on the result pane (in WebI) the belong to class(es) based on the derived tables (without removing the objects I used before and retrieved data) I don't get a data output - an empty set is returned.

I hope this adds a bit of context.

Kind Regards,

B

[Former Member]

Hi Babalo,

Do you get data in case you take objects only from derived table class?

Does the columns used to join physical table and derived table has matching values? I mean to say is the join getting satisfied?

Adding to this, is the data type same for both the columns (on which join is set) or is there any typecasting required?

Regards,

Yuvraj

[Former Member]

Good day Yuvraj,

Thank you for your response.

Basically, I have 2 classes based on physical tables:

• Product (P); and
• Time/Calendar (T/C).

Two classed based on derived tables:

• Stock on Hand (SoH); and
• Stock Movements (SM)

The derived tables SQL code selects (Quantity and Selling Price) from the SoH table and joins to the T/C table with conditions and a Where clause to restrict the data output to the last 2 months (the same applies to the SM derived table).

Example code below:

SELECT  q.sm_table _no, q.tim_cal_no, q.quantity, q.selling_price

FROM    tim_cal_table p,

              sm_table q

WHERE  q.tim_cal_no= p.tim_cal_no

AND        p.day between to_char(sysdate - 60,'DD/MON/YY') and to_char(sysdate - 1,'DD/MON/YY');

Now, the SoH table and the SM table are joined (in the universe schema) to the P table and T/C table using Primary Keys (which are shared by all the tables - physical and derived i.e. foreign keys on the SoH and SM tables used in the derived tables).

Now to answer your questions:

1. Do you get data in case you take objects only from derived table class?NO
2. Does the columns used to join physical table and derived table has matching values? YES. I mean to say is the join getting satisfied? YES. Checked Universe Integrity - structure, objects, joins, conditions, loops, context and cardinalities - everything is OK.
3. Adding to this, is the data type same for both the columns (on which join is set) or is there any typecasting required? YES - checked the data types of the primary/foreign keys that join the tables and they all match, except for one on the SM table -  the foreign key has 6 set for precision (instead of 5 as per the other columns), however the data type is Numeric so this shouldn't be an issue.

I hope this helps to clarify the nature of the problem.

Thank you.

Regards,

B

[Former Member]

Hi Babalo,

So when you try to use the objects only from the derived table, you do not get data in Webi.

When you copy this SQL (generated from objects only from derived table) and run against database, do you get data? I think no.

The code of the derived table should be checked as it is not giving data. You need to check the conditions and joins used in it and check why it is not giving data.

Share the code of derived table so that we could look into it.

Regards,

Yuvraj

[former_member4998]

HI

Data Foundation --> Select the Derive table --> Double Click and copy the derived table code and run in oracle SQL developer and check whether you are getting data or not.

Data Foundation --> Select the Derive table --> Show Table Values and check able to see the data or not.

If you able to see the data. .check any query filters used in the WebI report.

[Former Member]

Good day,

The code is fine, I get a data output when I execute on SQL developer. Well, no filters on the report - I am building the report myself. 

I downloaded and read the BOBJ 3.1 Universe Designer documentation (or at least what was relevant to derived tables and my problem) and I couldn't find anything. I thought it was maybe a matter of multiple where clauses returning an empty set (because , according to the documentation,  when you add a Where clause to the definition of an object, the restriction is added to the restrictions set by the joins using the AND operator but I don't have any restrictions on the object definitions and I also checked the SQL code outputted by WebI - no AND operator added.

I figured there is some functionality I missed or a feature I should activate or something when defining derived tables - followed the derived table creation process step by step and couldn't pick up anything. I even reset the parameters on the universe to default values, still nothing.

Thanks,

B

[TammyPowlas]

Moved to the Semantic Layer space

[Former Member]

Thank you Tammy - much appreciated.

I tried to find this space after the discussion was initially rejected by the moderator and I didn't do a good job, struggle a bit with navigating SCN - took the closet space to my issue, so to say, I could find  .

Regards,

B