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
OBIEE 12c repository modeling question
Hi,
I know OBIEE is meant for dimensional tables with Facts and Dimensions. Someone asked me to try setup a model for a relational tables. So I tried to model it like below. The green table contains Account information. The purple table contains Customer information. The blue table is a bridge/relationship table that joins the two.
The join between green and blue is a complex join. In the physical layer, I see a red line. But in the BMM layer, it doesn't show any lines. Is this normal for complex joins? When I run an analyse with columns from the green and purple tables, I get error: [nQSError: 14025] No fact table exists at the requested level of detail.
I removed the complex join and tested with a foreign key join and I was able to see a line in the BMM layer. When I run the analyse, I don't get any errors. Is my error because of using the complex join? I researched the error on this forum and it seems many people has it. Most of the suggestion is to check the Content tab. But, I don't understand enough on what to check and how to fix it. Or am I wasting my time in modeling relational tables?
Answers
-
Hi,
It depends on what you try to achieve ...
First the joins in BMM are ideally done by hand, when you get them by drag&drop it means you are using the exact same keys as the physical layer, but that's not how things are supposed to be, the idea of the BMM layer is to forget the physical structure of things and think from a business point of view, defining logical keys and logical joins based on the business meaning of your model.
You can perfectly model your tables, it just depends what do you expect: is the blue table acting as the single conformed dimension? Are the green and purple tables part of a single logical fact table and the blue on is there as bridge in the join?
Plan your model first, build it after ...
0 -
+1 to Gianni: an understanding of what you are trying to achieve is crucial. Especially the rleationship table you mention plays a key role. If you draw on a sheet of paper and sketch out "what has to work wit what and how" you'll get a better idea because...
...in your model and description one can already deduce multiple use cases and hence modeling approaches.
a) accounts and customers are facts and the "blue entity" is just a conformed dimension
b) the blue is the M:N relationship table between the other two and hence IS actually the fact table while customers and accounts are the respective starting points for snowflaked dimensons
3.) accounts, contacts and the relationship table are ALL to play the role as facts depending on the use case
0 -
In Physical layer keep that complex join as is and try to create physical join in BMM layer and see how that works.
Gurus may not like but give a try with copy of rpd and let me know.
Thank in advance
0 -
Srini VEERAVALLI wrote:... try to create physical join in BMM layer and see how that works.
Sorry but doesn't exist anymore in OBIEE 12c, that was something doable (when desperate ) in 11g (or 10g, can't remember).
0 -
Complex joins are perfectly valid in the physical layer. It's just that it's not completely clear what should work how or why.
Complex joins are even quite common when you're looking at range joins (date BETWEENS) :-)
0 -
Even if you were desperate that was out in 11 as well :-P
0 -
To close the complex join thing: you didn't see the join in the BMM layer because it's complex and a drag and drop doesn't bring that join to the BMM layer. Foreign keys joins comes when you drag and drop the whole physical model. A complex one doesn't work an an identified foreign key column as it's complex, so you just have to add it by hand (1 click to open the diagram, 1 click to select the "new join" action, 1 click to select the first table, 1 click to select the second table, 1 click on "ok" and 1 click to close the diagram).
0 -
Check in Options!! just need to enable it.
We can still use Foreign key joins in BMM Layer.
Hope you got it. @Gianni Ceresa
0 -
Just in case you want to try with Foreign key joins in BMM Layer, try to enable it from Options.
It's just suggesting you to give a try!
0 -
What would that suggestion change in this instance?
The use case itself is vague / unclear since no one of us knows how these entities should behave in terms of their role in the dimensional model.
0
