Top 50 Interview Questions with answers for obiee 11g

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

Q2.What are the Key Configuration Files in OBIEE?

Answer:- NQSConfig.ini, NQSCluster.ini, odbc.ini, instanceconfig.xml

Q3.How Time based triggered report can be generated

Using Scheduler - ibots

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

Q5.What r 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

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

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

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

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

b. Reports

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

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

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

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

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

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

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

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

Q18.Is it possible to create a report based on two subject areas  (example : Sales and Supply chain) ?

ANS: 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.

Q19.How to get real physical SQL sent by OBIee to the database

ANS: 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.

Q20.How to configure an OBIee environment ?

ANS: 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.

Q21.In a troubleshooting perspective, how can I see log files ?

ANS: Old method in 10g version consisted in opening log files on the server.
Now, in 11g, log information is consultable in Enterprise Manager.

Q22What is query repository tool?

ANS:   It is utility of Seibel/OBIEE Admin tool
Allows you to examine the repository metadata tool
For example: search for objects based on name, type.
Examine relationship between metadata objects like which column in the presentation layer maps to which table in physical layer

Q23. What is JDK and why do we need it?

ANS:  Java Development Kit (JDK), A software package that contains the minimal set of tools needed to write, compile, debug, and run Java applets.

Oracle doesn’t recommend Opaque Views because of performance considerations, so why/when do we use them?
An opaque view is a physical layer table that consists of select statement. an opaque view should be used only if there is no other solution.

Can you migrate the presentation layer to a different server?
No we have to migrate the whole web & RPD files

Q24.How do you identify what are the dimension tables and how do you decide them during the Business/Data modeling?

ANS: Dimension tables contain descriptions that data analysts use as they query the database. For example, the Store table contains store names and addresses; the Product table contains product packaging information; and the Period table contains month, quarter, and year values. Every table contains a primary key that consists of one or more columns; each row in a table is uniquely identified by its primary-key value or values

 Q25.Why do we have multiple LTS in BMM layer? What is the purpose?

ANS: To improve the performance and query response time.

Q26.What is the full form of RPD?

ANS: There is no full form for RPD as such, it is just a repository file (Rapidfile Database)

Q27.How do i disable cache for only 2 particular tables?

ANS: In the physical layer, right click on the table there we will have the option which says cacheable

Q28.How do you split a table in the RPD given the condition?

ANS: (The condition given was Broker and customer in the same table) Split Broker and customer.
We need to make an alias table in the physical layer.

 Q29.What type of protocol did you use in SAS?

ANS: TCP/IP

 Q30.What is logging level? Where can you set logging levels?

ANS: You can enable logging level for individual users; you cannot configure a logging level for a group.Set the logging level based on the amount of logging you want to do. In normal operations, logging is generally disabled (the logging level is set to 0). If you decide to enable logging, choose a logging level of 1 or 2. These two levels are designed for use by Siebel Analytics Server administrators.
Set Logging Level:
1. In the Administration Tool, select Manage > Security.
2. The Security Manager dialog box appears.
3. Double-click the user’s user ID.
4. The User dialog box appears.
5. Set the logging level by clicking the Up or Down arrows next to the Logging Level field

Q31.What is variable in OBIEE?

ANS: You can use variables in a repository to streamline administrative tasks and modify metadata content dynamically to adjust to a changing data environment. The Administration Tool includes a Variable Manager for defining variables

Q32.What is system variable and non-system variable?

ANS: System variables:
System variables are session variables that the Siebel Analytics Server and Siebel Analytics Web use for specific purposes. System variables have reserved names, which cannot be used for other kinds of variables (such as static or dynamic repository variables, or for non-system session variables). When using these variables in the Web, preface their names with NQ_SESSION. For example, to filter a column on the value of the variable LOGLEVEL set the filter to the Variable NQ_SESSION.LOGLEVEL.

Non-system variables:
A common use for non-system session variables is setting user filters. For example, you could define a non-system variable called SalesRegion that would be initialized to the name of the user’s sales region. You could then set a security filter for all members of a group that would allow them to see only data pertinent to their region.
When using these variables in the Web, preface their names with NQ_SESSION. For example, to filter a column on the value of the variable SalesRegion set the filter to the Variable NQ_SESSION.SalesRegion.

Q33. What are different types of variables? Explain each.

ANS: There are two classes of variables:
1. Repository variables
2. Session variables

Repository variables:
A repository variable has a single value at any point in time. There are two types of repository variables:
Static: This value persists, and does not change until a Siebel Analytics Server administrator decides to change it.
Dynamic: The values are refreshed by data returned from queries. When defining a dynamic repository variable, you will create an initialization block or use a preexisting one that contains a SQL query. You will also set up a schedule that the Siebel Analytics Server will follow to execute the query and periodically refresh the value of the variable.

Session Variables:
Session variables are created and assigned a value when each user logs on. There are two types of session variables:
1.system
2.non-system.

 Q34.What is the cache management? Name all of them and their uses. For Event polling table do you need the table in your physical layer?

ANS: Monitoring and managing the cashe is cache management.There are three ways to do that.
Disable caching for the system.(NSQconfig INI file), Cache persistence time for specified physical tables and Setting event polling table.
Disable caching for the system.(INI NQ config file :
You can disable caching for the whole system by setting the ENABLE parameter to NO in the NQSConfig.INI file and restarting the Siebel Analytics Server. Disabling caching stops all new cache entries and stops any new queries from using the existing cache. Disabling caching allows you to enable it at a later time without losing any entries already stored in the cache.

Cache persistence time for specified physical tables:
You can specify a cacheable attribute for each physical table; that is, if queries involving the specified table can be added to the cache to answer future queries. To enable caching for a particular physical table, select the table in the Physical layer of the Administration Tool and select the option Make table cacheable in the General tab of the Physical Table properties dialog box. You can also use the Cache Persistence Time settings to specify how long the entries for this table should persist in the query cache. This is useful for OLTP data sources and other data sources that are updated frequently, potentially down to every few seconds.

Setting event polling table:
Siebel Analytics Server event polling tables store information about updates in the underlying databases. An application (such as an application that loads data into a data mart) could be configured to add rows to an event polling table each time a database table is updated. The Analytics server polls this table at set intervals and invalidates any cache entries corresponding to the updated tables.
For event polling table ,It is a standalone table and doesn’t require to be joined with other tables in the physical layer.

 Q35.What is Authentication? How many types of authentication.

ANS: Authentication is the process, by which a system verifies, through the use of a user ID and password, that a user has the necessary permissions and authorizations to log in and access data. The Siebel Analytics Server authenticates each connection request it receives.
Operating system authentication
External table authentication
Database authentication
LDAP authentication

Q36.What is object level security?

ANS: There are two types of object level security:
1.      Report Level
2.      Web Level
Repository level: In presentation layer we can set Repository level security by giving permission or deny permission to users/groups to see particular table or column.
Web level: this provides security for objects stored in the OBIEE web catalog, such as dashboards, dashboards pages, folder, and reports you can only view the objects for which you are authorized. For example, a mid-level manager may not be granted access to a dashboard containing summary information for an entire department.

Q37.What is data level security?

ANS: This controls the type an amount of data that you can see in a report. When multiple users run the same report the results that are returned to each depend on their access rights and roles in the organization. For example a sales vice president sees results for all regions, while a sales representative for a particular region sees only data for that region.

Q38. What is the difference between Data Level Security and Object Level Security?

ANS: Data level security controls the type and amount of data that you can see in reports. Object level security provides security for objects stored in the OBIEE web catalog, like dashboards, dashboards pages, folder, and reports.

 Q39.How is security set up in OBIEE?  How many methods of security can be set up in OBIEE…?
ANS: Authentication is process of confirming whether the user is a valid user or not. Is he part of this company? Is he an employee of our external suppliers?
Authorization is process of giving access to different pieces of the OBIEE. One user “UserA” can access dashboards, can create iBots, can schedule reports and can do administrative tasks. Another user “UserB” can see only part of what “UserA” has access to and “UserB” has access to some other tabs of the dashboard that “UserA” does not.
This layer of separating who can access what is done as part of Authorization.

Q40. How does OBIEE handle Authentication?

It’s very flexible and can be integrated to one of your existing technologies like LDAP, Oracle EBS, AD, and Oracle Database. So, do the same username have to exist in OBIEE security layer as part of RPD development? Yes, by default, OBIEE stores list of usernames and passwords and checks incoming credentials against it.
Heard about External Table Authentication? Where does this come into play?
Instead of storing usernames and passwords directly in the RPD, these are stored in the database for better management purposes. This also helps in RPD migration and deployment across multiple environments.

Q41.How is Authorization handled in OBIEE?

ANS: Authorization is done as part of security in Presentation Services.

Q42.Does OBIEE have two layers of security?

ANS: Yes, first at the RPD level and second at the presentation services level.

Q43.What kind of privileges can be granted from presentation services level?

ANS: Access to iBots, certain tabs in the dashboard, delivers, alerts, schedule reports etc etc..

Q44.Why OBIEE security is different compared to other BI tools?

ANS: Because it’s very flexible and can integrate into any existing security architecture an organization has built and reduces the need for one more layer of administration..
Did Oracle OBIEE do a good job of communicating and convincing the user community regarding the security architecture?
Probably not… I understand the complexity behind this and the mere flexibility of the tool makes this even difficult.

Q45.What is the default location of a repository file?

[InstalledDirectory]OracleBIserverRepository

 Q46.What’s XMLA and where is it used for in OBIEE context

ANS: The Provider Services tool that comes with Essbase is used to provide the interface, with Oracle BI Server talking to Essbase through its XMLA interface. (Need to find the answer still)
XML for Analysis (abbreviated as XMLA) is an industry standard for data access in analytical systems, such as OLAP and Data Mining. XMLA is based on other industry standards such as XML, SOAP and HTTP.

 Q47.Can you change the location of your RPD file in your OBIEE Configuration? If Yes, Where would you mention the new location of this RPD file for Bi Server?

ANS: Read the NQSConfig file “Repository Section”. You will easily find the answer. The answer for this is “It cant be changed”.

The repository location can be changed, this must be done when clustering the BI Server. The parameters in the NQSCONFIG.INI file are
REPOSITORY_PUBLISHING_DIRECTORY =
REQUIRE_PUBLISHING_DIRECTORY = YES;

Q48.What kind of joins would you perform in the physical layer of the repository file when opened with Administration tool?

ANS: We cannot have outer joins in Physicla layer. We can outer joins in BMM layer.  In BMM layer – complex join can be full inner join or full outer join or whatever your criteria was,but in physical layer – physical join is always an inner join.

Q49.What are the minimum services needed to load a repository file onto memory and view a dashboard which has reports that have been refreshed on a scheduled basis?

ANS: Oracle BI Java Host, Oracle BI Presentation Server, and Oracle BI Server

Q50.What is Level Based Metrics.How will you create it?

Answer:- Leval-base matrics means, having a measure pinned at a certain level of the dimension. Monthly Total Sales or Quarterly Sales are the examples.
To create a level based measure, create a new logical column based on the original measure (like Sales in the example above). Drag and drop the new logical column to the appropriate level in the Dimension hierarchy (in the above example you will drag and drop it to Month in Time Dimension.

 Q51.What are Global Filter and how thery differ From Column Filter?

Answer:-

Column filter- Simply a filter applied on a column which we can use to restrict our column values while pulling the data or in charts to see the related content.
Global filter- This filter will have impact on across the application.

Q52.What is a materialized view?
ANS: Materialized view is a physical object and replica of the one or more master objects.It will refresh in an intervals.

Q53.Have you ever tried with de-normalized Database to build reports? If yes, How to handle?
ANS: Building reports on de-normalized data is not a best practice it leads to performance issues but we can build the reports. These are reports can’t be used for business analysis because the data will fluctuate non regular intervals.