nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Joins & mapping in LTS

2973433

Hi guys,

Recently I'm dealing with following issue (I will simplify my model in purpose):

I have one LTS "Points" which contains "points" measure and I have following report with 6 attributes and 1 measure: Partner1, Partner2, Transactions, Orders, Billings, Customers, Points.

So when I enter "Points" LTS>General it maps to all these tables and has joins like: Partner1=partner2, Partner1=Transactions, Partner2=Transactions, Transactions=Orders, Billings=Orders, Billings=Customers, Billings=Points.

And it works fine on this report, but...

when I need to create following report with different columns: Partner1, Billings, Points (so "Points" LTS is used too) I want to have only some of all these joins (for example I dont want to have Transactions=Orders join)

However when I look on logs to see what select is executed, I can notice that all the joins (from first report) are used which spoils my report.

Of course I made up these tables and reports but my scenario is based on the same rule.

The thing is that OBI uses unnecessary joins, how can I solve it?

Find more posts tagged with

Accepted answers

All comments

cesar.advincula.o

Hello,

Its not clear to me, you mean that you have an only one LTS, and you have 6 dimension (with one attribute per dimension)?

And you want to get just the join, please, could you give me more detail. Please.

Kind Regards,

2973433

Hello,

as I said I simplified this model but let's say that yes, I have only LTS and 6 dimensions. These dimensions have more attributes but on my first report I use only one attribute from each dimension.

And in second case where I use only some of these attributes I don't want to have in select all those dimensions (as joins) but only the ones which are used in report.

2973433

Any ideas?

I think I can create second LTS (with different joins) and duplicate this measure but it is kinda avoiding problem, not solving it.

asim cholas

Hello,

Could you please elaborate by using join diagrams and RPD objects?

Gianni Ceresa

Hi,

Your 6 dimensions have something in common? Same physical source or something like that?

Is your system more or less standard or you had to change all the settings for some special needs?

As you expected OBIEE must not add in a query unused dimensions, so I'm guessing there must be a link between these dimensions to make OBIEE think it can't have correct values without using all of the 6 dimensions in the query ...

cesar.advincula.o

Hello,

Oracle BI doesnt do a joins implicity,

Oracle do a join, automatically, for example, when you choose one attribute which is the "father table" of a level in your dimension, in that way Oracle BI makes the join implicity to get your attribute of the father in a snokflake schema.

Return to your question, i dont really sure, what is the setting of your model.

You have a 7 Logical Table Source (7 different Physical Table), in other words,

- Dimension 1 - LTS1 - Physical Tabla 1

- Dimension 2 - LTS2 - Physical Tabla 2

- Dimension 3 - LTS3 - Physical Tabla 3

- Dimension 4 - LTS4 - Physical Tabla 4

- Dimension 5 - LTS5 - Physical Tabla 5

- Dimension 6 - LTS6 - Physical Tabla 6

- Fact - LTS7 - Physical Tabla 7

Or, you have a 7 Logical Table Source (only one Physical Table),

- Dimension 1 - LTS1 - Physical Tabla 1

- Dimension 2 - LTS2 - Physical Tabla 1

- Dimension 3 - LTS3 - Physical Tabla 1

- Dimension 4 - LTS4 - Physical Tabla 1

- Dimension 5 - LTS5 - Physical Tabla 1

- Dimension 6 - LTS6 - Physical Tabla 1

- Fact - LTS7 - Physical Tabla 1

Please, let me check, with a screenshot, the setting of your "Physical" and "Logical" star schema.

Kind Regards,

2973433

Ok this is more or less my case:

http://snag.gy/bnooP.jpg

The thing that if I create for example report with columns from Reversals, Redemptions, Redemptionpartner, I would like to use in select only these tables + Transactionsrelations, but instead of this OBI does select with all these tables that are in the picture.

cesar.advincula.o

This is your physical model, and it's OK, the query have to build it, what about your "Logical Star Schema",

Let me try to understand your logical schema, you have a "only one FACT TABLE" with ONLY ONE LTS,

in this LTS do you have (FCT_TRN_REDEMPTIONS, FCT_TRANSACTION_RELATIONS, FCT_REVERSAL)?

And you have two Logical Table Dimension ( LST 1: "DIM_REDEMTIOPARTNER" and LTS 2: "DIM_TRANSACTION")?

Really?

If there is your model, there will be my problem, how ever I try to understand how your model your LOGICAL star Schema, please, do you mind put a screemshoot

of your Logical Schema, for more detail.

As I see this yout physical schema:

Kind Regards,

Thomas Dodds

why are you joining Facts to Facts? think in terms of star schemas. facts are related through conformed dimensions, dimensions are related through a common fact.

2973433

Not exactly, in first post I simplified it a bit.

I have one fact table in business model with measures from accruals, redemptions, reversals. So essentially each table is LTS as well. And additionally two dimensions like on the picture:

snaggy - paste images!

@Thomas Dodds

I know about it but table Transactionrelations is especially created to join fact tables with each other because there no other way to connect them.