What is a bridge table ?
Answer:- If you want to connect two tables where there is no relation ship you can use a thrid bridge table for connecting them which will have common columns in both tables, this is used in BMM Layer.
How Time based triggered report can be generated
Using Scheduler – ibots
What is ibot
Ibot is an scheduling agent, used to schedule reports to be sent across various devices such as email, pager, mobile, other devices etc
What are the diff types of joins that are possible in OBIEE RPD
Complex Join – to use multiple conditions, such A.ROW_WID = B.ROW_WID AND / OR A.A_WID = B.B_WID like that
Natural Join / Equi Join
Difference between a session and repo variable
Session variables are similar to dynamic repository variables in that they obtain their values from initialization blocks. Unlike dynamic repository variables, however, the initialization of session variables is not scheduled. When a user begins a session, the Oracle BI Server creates new instances of session variables and initializes them.
A repository variable has a single value at any point in time. Repository variables can be used instead of literals or constants in Expression Builder in the Administration Tool. The Oracle BI Server substitutes the value of the repository variable for the variable itself in the metadata.
Unlike a repository variable, there are as many instances of a session variable as there are active sessions on the Oracle BI Server. Each instance of a session variable could be initialized to a different value.
(or)
Session variables can have different values for each user, repository variables have the same value for everyone.
There are two types of session variables:
- System session variables: out of the box variables like USER
- Non-system session variables: user-defined variables, can have different values for each user and are typically used to customize user experience , for example you could have a SALES_REGION variable to show only data relevant to the user’s region.
Repository variables also have 2 subtypes:
- Static repository variable: Fixed value that can only be changed with the admin tool, can be used to replace hardcoded values like database schemas and user names in connection pools.
- Dynamic repository variable: These hold values that apply to any user, for instance currency exchange rates.
Both System variables and dynamic repository variables get their values from initialization blocks that can contain SQL.
What are the Key Configuration Files in OBIEE?
Answer:- NQSConfig.ini, NQSCluster.ini, odbc.ini, instanceconfig.xml
What is task scheme and In production… if users are running the report..but not getting the data… what could be the reason and list all the checks step by step.. How u will resolve the problem
whether the issue is specific to this user / general. If general, then you might want to check the joins, referential integrity between tables. If specific, then you may have to check his security authorization, business model filters, session variable initialization / any query timing limitations, number of connection pool parameters etc.
Are you Looking for Oracle OBIEE Training? Please Enroll for Demo Oracle OBIEE..! |
How can u use saved filters and where will you save the filters
Shared Folders/XYZ Folder/ It contains two folders
a.Prompts – saved filters will be stored here.
- Reports
Have you worked in dashboard..when u run a dashboard… how will u stop the dashboard report run automatically
By clicking on the cancel button, you can stop a specific report placed on the dashboard
What is a surrogate key
A surrogate key is an artificially generated key, usually a number. A surrogate key, in the level aggregate table, simplifies this join and removes unnecessary columns (level primary key) from the fact table, resulting in a smaller-sized fact table. Adding surrogate keys to the dimension (level) aggregate tables can simplify joins to the fact tables and might improve query performance. Additionally, a surrogate key makes sure that each aggregate table has a unique identifier.
Couple of table names in Oracle GL,AR..ect
Talk in detail using below material..this talks of entire flow as to which table is used for what
AR
To be in the position where you need to handle and process a payment in Receivables, you need to have a buyer/payer (most times this is a customer but there are exceptions). Customer records are stored in the HZ_CUST_ACCOUNTS and HZ_PARTIES tables. Each customer needs to have a site (a location/address of business) for which information is stored in HZ_CUST_ACCT_SITES_ALL and HZ_PARTY_SITES_ALL.
When a customer purchases goods or services from your company, an invoice is generated for the customer. These invoice transactions are recorded in RA_CUSTOMER_TRX_ALL (invoice headers) and RA_CUSTOMER_TRX_LINES_ALL (invoice lines).
When the customer makes a payment, this generates new transactions. These are recorded in AR_CASH_RECEIPTS_ALL and AR_CASH_RECEIPT_HISTORY. If there is adjustment to an invoice, this is recorded in AR_ADJUSTMENTS.
Sometimes payments are received in batches, where a single payment is for multiple invoices. These batch payments have records in AR_BATCHES.
The AR_PAYMENT_SCHEDULE table holds one record per payment. Therefore, for payments that pay an invoice in full, there will only be one record related to that invoice. However, if payments for an invoice are broken up into a payment plan, or if a partial payment is received for an invoice, additional records will be generated in this table for each payment.
GL
GL_INTERFACE
This table holds financial transactions (journals) transferred from other Oracle Applications modules and external systems.
GL_JE_BATCHES
This table identifies a “batch” of journals that are related and processed together. Each batch contains one or more journals.
GL_JE_HEADER
Each journal has one journal header and one record this table
GL_JE_LINES
Each journal has one or more journal lines and are tied together by the journal header
Some other important supporting tables in the GL data model include:
GL_CODE_COMBINATIONS
This is the Accounting Flexfield table and it stores the chart of accounts values, and so the table contains the valid GL account combinations allowed in the system, along with other relevant information about the accounts.
GL_LEDGERS
This table stores all the Ledgers and Ledger Sets in the Oracle GL system
GL_PERIODS
This table stores information about the accounting periods defined in the Oracle GL system. Each row contains information such as, start date, end date of the period, the period type, the fiscal year, and the period number.
How can i have two different columns from two different tables or subject areas
Using confirmed dimensions, you can fetch multiple metrics across various facts, but the join should be of the same grain / same level of data detail.
What is a request variable and in a table how can i get the distinct record from the table… and If I say ‘distinct all’..what data will u get
So exactly what is a request variable? Well, put succinctly:
A request variable is a variable that is used to temporarily override the value of a session variable.
And, with a little more precision, a request variable is:
A variable that is defined within the Presentation Services;
A variable whose scope (page or dashboard) is that of the associated dashboard prompt that defines the request variable and its value;
A variable whose name should not match that of a repository variable or a security system session variable; and
A variable that overrides the value of the corresponding session variable (should it exist) for the purpose of BI Server request parsing and physical SQL construction for all requests that are issued from dashboard pages that fall within the scope of the variable’s definition.
Note, using a request variable does not change the actual value of a session variable – neither temporarily nor permanently. It simply appears to change the value for certain purposes during the execution of a request.
When u run optimization and suppose u get some number….so how u know that the number is too bad or too good….
Well, this is situation dependent.. Only way is to check with source numbers
What is explain plan
From the BI side, you can basically use 2 types of hints
The Index hint instructs the optimizer to scan a specified index rather than a table.
- The Leading hint forces the optimizer to build the join order of a query with a specified table.
Importantly, the Explain Plan contains the following information regarding your query.
Ordering of the tables referenced
Access method for each table
Join method for each join operation
Data operations, such as filter, sort or aggregation
And in addition:
Optimization (Cost and Cardinality)
Partitioning
Parallel Execution
What are aggregations and if we have to do it on physical layer side then how u do it
You can use the Aggregate Persistence Wizard to create the SQL file that will be used to create aggregate tables and map them into the metadata.
Do not use aggregate persistence against tables with active Virtual Private Database (VPD) security filters. There is a possibility that the aggregate information might be persisted without the VPD filter, posing a security risk.
The Aggregate Persistence Wizard is a nifty utility for creating, modelling and populating aggregate tables available in OBIEE 10g and 11g. This utility is especially useful to quickly build a single aggregation layer upon federated heterogeneous physical sources. OBIEE will create, model and populate aggregate dimensions and facts based upon the intelligence you have built into the RPD. This avoids re-writing the same logic into your ETL.
Are you Looking for Oracle OBIEE Online Training? Please Enroll for Demo Oracle OBIEE..! |
What kind of sources can be connected to OBIee ?
ANS: OBIee can be plugged on Relational Databases, Cubes or Flat files, with the following constraints :
The sources must be modeled as star schemas or snowflakes schemas in order to have an efficient and safe behavior.
In case of multiple sources directly in OBIee used together, be aware that OBIee will conduct the needed joins itself, and OBIee is not a database.
Is it possible to create a report based on two subject areas (example : Sales and Supply chain) ?
Theoretically, yes. In practice, the two subject area must come from the same business model in the repository, which is quite useless. Then, we can consider the two following alternatives :
Create an analysis with “combine request”: one subrequest by subject area. Note that it’s tricky to implement for standard users.
Set up a transverse subject area, that will gather tables from both business topics.
How to get real physical SQL sent by OBIee to the database
First of all, check the value of your session variable LOGLEVEL.
5 is fine.
Then, go into Administration -> Manage sessions.
Find your query (at the bottom of the list), and click on “View log”.
If you hit the cache, you will see “cache hit on query”. Otherwise, you will see the physical SQL below the logical SQL.
How to configure an OBIee environment ?
In OBIee 11g, main configuration items are managed in Enterprise Manager (web interface).
However, some configurations are still located in configuration files, like NQSConfig.ini, NQSCluster.ini, odbc.ini, and instanceconfig.xml.
