BO4 - all tables appearing in from clause in converted .unx but not in the equivalent bo4 .unv

[former_member272336]

Hi,

We have a bo4 universe which was old xi universe.

A simple query generated against the .unv behaves as expected whereas the .unx one has all table sin the data foundation layer in the from clause.

At first thought this was if some join not included/excluded but all joins included  or excluded in all contexts.

Also see the multiple paths for some reason.

Any thoughts on what causing this behaviour in the .unx equivalent of the .unv ( .unv behaving as expected).

Thanks

[Former Member · Accepted solution]

Hi Philip,

Lets start with some basic steps to troubleshoot.

1. Do an integrity check on the data foundation layer and see if there are any errors.

2. Compare the universe parameters in both UNV (File --> Parameters) and UNX business layer.

3. Do you get all tables if you put a single object in query panel and check SQL?

4. While editing any one object, do you see all tables under "Extra tables" option?

5. Did you get any error while converting the UNV to UNX?

Regards,

Yuvraj

[former_member272336 · Accepted solution]

Hi Yuvraj,

Thanks for reply and suggestions.

Tracked this down to loops within the contexts - in bo4 unv file loops still exist in contexts but correct sql gets generated i.e. just with the tables required, if have loop in context in .unx uit shows all tables in the data foundation layer in the sql.

Thanks

Philip

[Former Member · Accepted solution]

Hi Phillip,

It is quite strange that there are loops within context.

Did you do an integrity check on UNV before converting it to UNX?

I find IDT more intelligent than UDT . In many cases which I have experienced, it simply gives incompatible objects error in case there is a loop and not checked.

May be UDT allowed it due to some unknown reasons.

Please redefine the contexts in order to fix it.

Regards,

Yuvraj

[Former Member]

Hi Philip,

I would expect the query to use all the tables in from clause only when the objects selected in the query are from two tables that somehow are linked through all the tables.

Did you have any shortcut joins in UNV that got converted into regular joins?

[former_member272336]

Hi,

Thanks for reply.   no shortcut joins.  It looks like idt when encounters any loop in context generates all tables in from clause (not just ones related to th eloop) whereas udt doesn't have this behaviour.

Thanks

[mhmohammed]

Hi Philip,

Are both the objects in that query coming from a single context? What about the Multiple SQL Statements for each measure in the Query Options of the Universe? Is it checked on unchecked? And also the Aggregate Navigation, is it compatibility/incompatibility setup correctly?

Thanks,
Mahboob Mohammed

[former_member272336]

Thanks for replies.

All joins and objects within single context - this an old legacy unv developed by someone else and works in sense that generates correct sql when unv.

Have seen this behaviour before in idt in that if any loops in a context infers all tables in data foundation layer in the sql.