Datawarehouse Architect

What are the Physical Layer Objects 1. Database 2. Connection pool 3. Schema folder 4. Tables 5. Columns 6. Keys Screen shots for your reference Physical Layer Objects in OBIEE 11g

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. Steps: 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. Screen shots for your reference: Create an Al

DSN – Data Source Name, the name which suggests connecting or relates the database. Steps to Create DSN: 1. Open control panel and go to Administrative toolsà ODBC Administrator. 2. Go to the second tab “system DSN”. 3. Click on Add and select the driver of the database which you are using currently. 4. Give the Credentials first assign name for the Data Source Name. 5. Give the TNS entry to connect the data source. 6. Database User name and then click test connection. 7. Give the credentials in test connection, user name and password of data base. 8. Check the connection is success or not if yes then click ok. Screen shots for you reference: DSN for OBIEE 11g DSN for OBIEE 11g DSN for OBIEE 11g DSN for OBIEE 11g

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. Screen shots for your reference: Shared Log On option in OBIEE 11g

1. Go to the location D:\11G\instances\instance1\config\OracleBIServerComponent\coreapplication_obis1 2. and find the NQSConfig.INI file, move down to the section “Query Result Cache Section” Enable =yes. 3. Login to Console ( http://localhost:7001/EM ) 4. Go to Business Intelligence -> Core Application -> Select Capacity Management tab -> Select Performance tab below capacity management tab -> Click 'Lock and Edit Configuration' -> Deselect the 'Cache enabled' option -> Click Apply -> After that Click 'Activate Changes' 5. Restart the services. Screen shots for your reference: Cache in OBIEE 11g Cache in OBIEE 11g

Connection pool is defined as the connection between the data source and Oracle bi server. Connecting database using the interface like OCI, odbc, etc. to the Admin tool RPD to serve the information of data. Screen shots for Your Reference:

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. Physic

1. Complex Join in the BMM layer represents the relationship between the logical tables and its columns. 2. It makes the best decision about sql request to generate based on the logical query request. 3. Use of complex joins, can create one to many relationships and not the logical foreign key. 4. We can’t use the expression builder in the complex join because it will be grey out. You can only use the types of joins like inner or outer. 5. Logical joins are automatically created if both of the following statements are true: 6. You create the logical tables by simultaneously dragging all required physical tables to the Business Model and Mapping layer. 7. The logical joins are the same as the joins in the Physical layer 8. Logical tables in a BMM layer can have multiple logical tables sources (LTS). Complex join actually is an intelligent join between LTSs of two logical tables in BMM Layer.

Normalized: It is the process of effectively organize the data in to the database. Eliminating redundant data storing the same data for more times. Stores only related data into the database. It is the process of structuring relational database schema such that most ambiguity is removed. De – Normalized: It is process to increase the database performance. It involves adding redundant data to a normalized database to reduce certain types of problems with database queries that combine data from various tables into a single table.

OLTP: Online transactional transaction processing It is characterized by large number of rows of data’s per second. For each and every transaction of data will get store for time to time. In OLTP database there is detailed and current data, and schema used to store transactional databases is the entity model (usually 3NF). All the transaction in OLTP normally will consider as Normalized. OLAP: Online Analytical Processing It is characterized by very low volume of transactional data. Normally OLAP will consider as De-normalized. All the transactions are aggregated to view in very less rows of data. In OLAP database there is aggregated, historical data, stored in multi-dimensional schemas (usually star schema).

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.