1. A common use of alias is to use the table or column to play simultaneously for several times.
2. A single dimension appears more than one time for more purpose.
3. OBIEE does not support Circular join in BMM layer, so we can overcome from this problem by creating alias table for one of the table in that circular join.
4. OBIEE does not support self join, so we can overcome from this problem by creating alias table.
5. If a fact table having relationship with dimension table on more than one key, then we can create aliases for the dimension table, then we can join fact table to these alias table on different keys.
1. Right click on the Physical layer of the table, New objectà Alias.
2. Give the name of the alias , automatically it assigns the columns of the physical table
3. Even it inherits all the properties of the physical table.
Select the Shared logon check box if you want all users whose queries use the connection pool to access the underlying database using the same user name and password which given in the connection pool.
If this option is selected, then all connections to the database that use the user name and password specified in the connection pool, even if the user has specified a database user name and password in the DSN (or in user configuration).
If this option is not selected, connections through the connection pool use the database user ID and password specified in the DSN or in the user profile.
1. Relationship between the logical tables was expressed by logical joins in BMM layer.
2. Logical joins express the cardinality relationships between logical tables and are a requirement for a valid business model.
3. Specifying the logical table joins is required so that Oracle BI Server can have the necessary metadata to translate logical requests against the business model into SQL queries against the physical data sources. Logical joins help Oracle BI Server understand the relationships between the various pieces of the business model.
4. When a query is sent to Oracle BI Server, the server figures out how to construct physical queries by examining how the logical model is structured.
5. Examining logical joins is an integral part of this process.
6. The Administration Tool considers a table to be a logical fact table if it is at the “many” end of all logical joins that connect it to other logical tables.
1. Relationship between the physical tables and columns are expressed by physical join in the physical layer.
2. A primary key and foreign key relationship defines a one-to-many relationship between two tables.
3. A foreign key is a column or a set of columns in one table that references the primary key columns in another table.
4. The primary key is defined as a column or set of columns where each value is unique and identifies a single row of the table.
5. Keys and joins help Oracle BI Server determine the fact–dimension relationships between tables.
6. The Physical layer typically uses foreign key joins to define relationships, whereas the Business Model and Mapping layer uses logical joins to define relationships.
Measure is normally a numeric value represents business values.
Can have multiple measures in a fact table.
Measures unlike dimensions give the fact numeric value instead of the detailed analysis.
Different types of measures are listed below:
1. Additive measures are measures that can be added across all dimensions. For example customer count in numbers can be added across all dimensions.
2. Semi-additive measures are measures that can be added across some, but not all dimensions. For example the bank account balance is simply a snapshot in time and cannot be added over time. However you could add multiple accounts of the same customer to get the total balance for that customer.
3. Non-additive measures are measures that cannot be added across any dimensions. For example the procurement is simply a snapshot in time and cannot be summed over time. Also you cannot combine procurement for various items.
A fact table that contains the measure of values (Numeric Data of business).
For example, sales amount would be such a measure. This measure is stored in the fact table with the appropriate granularity. For example, it can be sales amount by store by day. In this case, the fact table would contain three columns: A date column, a store column, and a sales amount column.
In Oracle Business Intelligence Fact table have foreign key to get connect with the dimension tables.
1. It provides measure of an entity.
2. Measurement is the amount (aggregate values).
3. Structure of Fact Table - foreign key (fk), Degenerated Dimension and Measurements.
4. Size of Fact Table is larger than Dimension Table.
5. In a schema less number of Fact Tables observed compared to Dimension Tables.
6. Compose of Degenerate Dimension fields act as Primary Key.
7. Values of the fields always in numeric or integer form.