Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 17 Oracle Analytics Lounge
- 218 Oracle Analytics News
- 44 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 81 Oracle Analytics Trainings
- 15 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Joins & mapping in LTS
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?
Answers
-
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,
0 -
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.
0 -
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.
0 -
Hello,
Could you please elaborate by using join diagrams and RPD objects?
0 -
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 ...
0 -
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,
0 -
Ok this is more or less my case:
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.
0 -
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,
0 -
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.
0 -
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:
@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.
0
