Categories
- All Categories
- 75 Oracle Analytics News
- 7 Oracle Analytics Videos
- 14K Oracle Analytics Forums
- 5.2K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 40 Oracle Analytics Trainings
- 59 Oracle Analytics Data Visualizations
- 2 Oracle Analytics Data Visualizations Challenge
- 3 Oracle Analytics Career
- 4 Oracle Analytics Industry
- Find Partners
- For Partners
Where should I create calculated field for table joins?
Hi all,
I am relatively new to using Oracle and working with RPD.
I have 4 tables from different Environments (No common key between them except the Unit/department) So I am making a unique join between them all based on Unit + department names .
the tables come from the database with no concatenated column, where should I create this column in each table in order to join them ? Physical Layer ?
- please note : tables come from the cloud to the database and from there I want to bring them to OBIEE
Thank you !
Answers
-
Hi,
You don't need to create columns, you just need to write the join expressions in your join. The joins should be defined in the physical layer, if you want to create columns to make your joins simple "columnA" = "columnB" then you need to create views of your physical tables either in the database or use SQL queries instead of tables.
I would say to not do that and just write your join expression directly in the join condition. You have an expression editor button that open the expression builder where you can write your join logic (with the concatenation of columns or whatever else you need to do).
0 -
Dear Gianni,
Thank you for the assistance!
I have one more question : Do I have to make joins between RPD tables(In physical layer) or is there an option to make relationships like other tools do (Power-BI/Tableau) ? I mean relationship between columns from different tables 🙂
As of my understanding BMM layer doesn't support snow-flake schema ,is this true ? then how do you cope with this situation ? what does it mean if I get a table(in BMM layer) from two table sources which don't have a join between them or a relationship? will the model work fine ?(I have seen an example online of something similar )
thank you !!
0 -
A business model is a star-schema model (it can be multiple stars as well). But it is only a logical representation, it has nothing to do with the physical structure of your data.
A physical snowflake you turn it into a star schema by joining together the 2+ tables being the snowflake into the logical table source of one of the logical tables. If you google the topic you should find a number of references explaining that in detail (because it works like that for 15+ years).
The technical definitional of the join, the real piece of code, is defined on the physical objects. The logical objects only says that there is a connection, but doesn't define its formula, it only says the business behavior: inner join or outer join. The tool will automatically find what physical join it must use to represent the logical joins.
You really need to adopt the layers approach: the physical layer defines what things exist, where they are, how to connect to them and how they can be joined one with the other.
Then you build a logical model that doesn't care at all about your physical structures, the logical model must match the business needs you are trying to solve: it should use business names and define everything from a business point of view.
It isn't the job of your business users to know what a snowflake is, how to make a join between tables etc. This is why the business model is a star schema: the most simple and obvious to use model. Something all your business users can easily understand: they have measures and then they have dimensions with attributes. Databases, tables, columns, joins expressions, snowflake aren't their problem at all.
0 -
"I have one more question : Do I have to make joins between RPD tables(In physical layer) or is there an option to make relationships like other tools do (Power-BI/Tableau) ? I mean relationship between columns from different tables 🙂"
Additional to Gianni's input:
That's precisely the difference between the physical layer of the RPD and the logical layer. The physical layer defines which technical path the join needs to take between tables (or views, or hardcoded SQL statements,...). In your case sth like "TableA"."ColumnRepresentingDepartment" = "TableB"."SomethingElseButIt'sAlsoADepartment". Or concatenated using the formula editor.
The business model (or logical) layer then defines the relationship between entities. You may have a logical business entity which in your business language is called "Customer" even though it's sources from a CRM system where the base table is called "CONTACT", a billing system where it's "CLIENT" and an Excel sheet where someone put "Full Name". That entity "Customer" then has a relationship with a logical fact like "Sales".
Now this relationship can change if you have role-playing tables (both on the fact or dimension side) and doesn't always have to be the same as its physical representation. Furthermore when doing flat table modeling with a flat base table containing ALL the information, both fact and dimension, then you literally don't have a physical self-join (pointless waste of performance) but you will still have a logical fact and a logical dimension which have a relationship with each other.
The relationship concept is in what's now Oracle Analytics exists since 1997 and predates even the fever dreams of the guys who decades later started doing PowerBI or Tableau
0 -
Dear Christian,
I am sorry, I am having a hard time understanding the RPD way of working .
Does BMM layer support snowflake ? if not, then you have to convert the snowflake to a star schema?
in this case there are two options : 1) Bring the columns from the second dimension(Level 2) into the main dimension (in this case oracle does the connection in the physical layer by itself)
2) include the second dimension(Level 2) as another source table for the LTS in BMM layer...
What are the differences between the two? I searched the whole Internet for an answer and nothing .
About other tools, I would say that at least they have many tutorials online the opposite of Oracle which is sad .
0
