I have 3 Dimension table - 2 are in one schema and last is another schema. Using this 3 dimension tables, I need to create a logical fact table.
So, my question is whether we can create this fact table by joining these 3 dimension table which are in 2 different schema s ?
2 dimensional tables - AV, Outage are there in one subject area and other one Company is in another subject area. All these share a common schema. while I try to complex join company with outage, it does nt allow me. The obvious reason is both belong to different subject area.
The only way I found the solution is to move the company table from the present subject area to the other subject area where the other 2 tables are present. Am I correct? or anyway is there to solve the issue.
Subject area is never an issue.
Presentation layer is used to group the tables in to similar business lines.
Its just for the users understanding or requirrment.
You can use tables from different subject area to create a report. However the BMM and Physical join is given to those tables in the RPD. It is mandatory to join the tables in the BMM layer and Physical layer to use in the analysis.
Hope this clarifies.
An Obviously if you think perticular table is used in anaother subject are, then you need to add that table to subject area in the RPD.(Which makes sense)
Mark if correct,
you are correct. We can use tables from different subject area to create a report. However, my question was related to rpd design. Sorry, I was not very clear about the queries earlier.
Here is the whole scenario in the physical layer of the rpd
Table name Databse name Connection pool name Schema name
AV AV_PXRPAM AVAILABILITY CRMODDEV
OUTAGE AV_PXRPAM AVAILABILITY CRMODDEV
COMPANY PXRPAM PXRPAM_POOL CRMODDEV
AV and OUTAGE have the joins already. I want to make a join between COMPANY with OUTAGE. And then I want to include a column from each of above tables to the logical fact table in the BMM layer. then I want to do a star schema with the logical fact table to the above 3 tables in the BMM layer.