Real TimeOBIEE Questions sample

Date format issue while export to excel format 

Answers-->column propeties-->custom CSS style options(bottom of the window)-->check the custom css style -->Give the following date format
----mso-number-format:"mm\/dd\/yyyy\ hh\:mm\:ss AM\/PM" or " mso-number-format:\@"
Change the format based on you requirement.

When i logged into the analytics not able to see all the options available for administrator?
First logged in as a administrator into Em, manage the privileges which is required for the user.

I want to display the 4 narrative views in a single compound layout,but first narrative view width is 1000 and the other 3 views width should  be 500.Is it possible?
No,With a single compound view we can't achieve.

How can i sort values of column in subject area not in report ?
We can sort the column in 3 ways
1.Ascending order
2.Descending order
3.Based on another column
In Rpd we can sort the column based on the another column
BMM layer--->properties of the column_sort-->General--->Sort order column ---->select the column_base(column_sort will be sorted based on the column_base).

Can we call multiple action from a single actionlink?
Yes,We can call the multiple action links from a single action link based on the conditions.

What is the difference between joins in physical layer and BMM layer?
Both are same,but can give the complex conditions in BMM layer.The Query always fire from physical layer only.

Real Time Scenarios in OBIEE

 Real Time Scenarios in OBIEE

Real Time Scenarios in OBIEE

I have 2 tables in DataBase table1 and table2

a)Imported together with keys and foreign keys?

bJoins will be automatically established in the physical layer.

b)Imported together only with keys?

Ans:-Keys will remain but joins not

c)Dragged the 2 tables from physical layer to BMM layer(along with Joins),which act like fact table and which act like dimension table?

Ans:-Primary key-----Dimension

          Foreign key-------Fact

d)Same as (c) but without  Joins?

Ans:-Both act like fact tables in BMM layer

You recently changed the columns of your presentation catalog as your manager wants to enforce naming standards for all customer facing applications. What happens to all the dashboard requests written prior to this change?Do they function properly or do they appear broken?”If yes,they will function”,How does they work?”If not,reports appear broken”,what can you do to fix this?Give examples?

Ans:-If alias table is available for presentation table then all the reports work fine.

I have configured the repository(3 layers) and saved.I have generated some reports and saved.If I change the presentation catalog name then it is showing  that you don’t have permissions to access this subject area in answers.How can you fix this?

Ans:-Goto Advanced tab(In answers)àChange the subject area name and set XML it will automatically display the subject area.

For More

Interview Questions on OBIEE
Inteview Questions On OBIEE

OBIEE Report/Dashboard Performance Tuning

You come Morning to the office And all the users are complaining about the OBIEE reporting system is very slow (dashboards). What is your approach to resolve the issue?

Ans: 

1. First check NQServer.log 

2. Admin Tool -> click Session Manager. Check if there is any bottleneck and accordingly resolve.

      Refresh the sessions.........

3. Analytics or Answers -> Administration-> Manage Sessions

 Check the physical query and execute from the back-end(BE).

Performance Tuning at real time

Performance Tuning is the huge topic to answer;

let’s take step by step approach as follows...
       Check whether the same query is running or anyone update the query.If the same query is running then follow the steps given below,

• When you open the dashboard page, first figure out whether Prompt or Report is taking more time
• If it is prompt, check for any multi select prompts which are taking time to load all values.. (best practice is set default values).
• If Report is taking longer time, set some default filters and query for just couple of records and check the report performance.
• Take physical SQL from the session log (if SQL is not generated check for log level whether it is less than 2).
• Run this Physical SQL in the TOAD or any SQL Editor.
• Check Explain plan for the cost of the query.
• Hash Joins/Cartesian Joins are always kills performance.
• Some-times, Force Inner-join also helps to force the tables to have inner-join between two tables.
• Check are there any "Full Table Scans" happening instead of Index Scan.
• Consult your DBA for the Stats.

OBIEE real time questions

Interview questions on OBIEE

Best Practices of Presentation Layer in OBIEE

1. Presentation catalog should map to one Business model & mapping layer(BMM) layer objects only.
2. Use parent folders and sub-folders to group facts and similar dimensions together.
3. Avoid the use of aliases when a new presentation column is created.
4. The presentation columns in a table should be sorted alphabetically if no specific order is asked by the customer.
5. Make proper use of the permissions in this layer like authentication and authorization.
6. Don’t use special symbols (“) in column name, though its permitted.
7. Presentation columns should not have the same name as presentation table.
8. Eliminate unneeded objects to reduce user confusion.
9. Limit number of objects in folder to 7-12 (for performance tuning only).
10. Use object description field to convey information to users when they hover the mouse in answers on a presentation column.
11. Keep names short to have space on reports and give the meaningful table names and columns names to identify easily on subject areas. Remove primary key columns and other unnecessary columns that does not going to use in the creation of the reports.
12. Try to create separate folder for each data mart(HR, operation, SCM, sales) if it is coming from same logical layer.
13. Get costumer sign off of the presentation layer structure before building reports. This will avoid later replacements of columns which affects the reports constructed.

BMM Layer Best practices in OBIEE

Physical Layer Best practices in OBIEE

Best Practices of BMM layer in OBIEE

1. Always run Global Consistency Check before releasing a repository:

• Whenever we make changes to a repository, always be sure to run Global Consistency Check. It is bad practice to release a repository that still contains consistency check errors. In some cases, consistency errors prevent Oracle BI server from loading the repository. Use the Consistency chec k manager to identify and debug check messages.

Note: Whenever you do Consistency check , Right Click the Changed Business Model Object and go for Check consistency rather than using the Global Consistency Check. This will minimize the time needed for Consistency Check.

2. Minimize the use of Snow-flakes. Always go for Star Schema’s:

• Every logical fact table must join to atleast one logical dimension table.
• Note that when the source is a fully de-normalized table or flat file, you must map its physical fact columns to one or more logical fact tables, and its physical dimension columns to logical dimension tables.

3. Create dimension hierarchies for every Dimension in the Business Model:

• Even if a meaningful hierarchy definition cannot be thought of, just create one with the Grand Total Level and Detail Level. For Dimension Hierarchies the ‘Number of Elements at this level’ should increase from 1 at grand Total to the corresponding distinct values at each level. This can be approximate values; need not be the exact ones. Define keys at each level of the Hierarchy.

4. Don’t keep unwanted physical columns in the Logical Layer:

5. Give Meaning ful Names to the Logical columns. Avoid assigning a logical column the same name as a logical table or Business Model Object:

6. Logical Fact & Dimension table columns:

• Always assign a primary key for logical dimension tables. All logical dimension columns should be renamed in a way that is meaningful to users.
• Bring only required columns into BMM layer for reporting.
• Do not assign logical primary key for logical fact columns.
• Create “dummy” measures to group facts.

7. Use Multi User Development Environment:

• Use the Multi-User Development facility if there are multiple developers. Multiple developers to connect “online” to the same repository file and Making changes is not recommended.
• Multi User Development allows user to define a series of projects with in the repository file, where each project is a subset of the entire repository. If developers want to make changes, they can check out a project to a local machine make and test the changes, and then check the modifications back into the master repository file.

8. Level-based Metrics:

• When creating level-based measures, make sure that all appropriate fact sources map to the appropriate level in the hierarchy using aggregation content. You set up aggregation content in the levels tab of the Logical Column dialog for the measure. 
• Note that this is different from the content tab of the Logical dialog for the measure. Note that this is different from the content tab of the logical table source dialog, which is used to specify the grain of the source tables to which it maps. You only need to set up aggregation content in the levels tab of the logical column dialog for level-based measures. For measures that are not level based, leave the logical level field bank.

9. Separate business model:

• Even if you have only a single data source or schema in the physical layer, or you have only one physical data source for the repository, it is still good practice to break out the physical objects into multiple business models in the BMM layer to represent the independent areas of functionality.

10. Arrange the logical columns alphabetically if customer does not require any. This will save time when you revisit:

11. Fix the warnings if any, don’t ignore it:

12. Performance Tuning:

• Minimize the use of conditional checks and ‘CASE WHEN’ usage in the formula of Logical columns. This will affect performance. Instead make proper use of the where clause content filter of the LTS if the condition applies to all the columns/ measures in the logical table.
• Make proper distinction between count and count distinct. If you are counting on a unique value column don’t use count distinct. This will affect performance.
• Avoid dimensions in Fact tables and avoid measures in dimension tables.

Best practices of Physical layer in OBIEE
Best Practices of Presentation Layer in OBIEE

Real time Interview Questions on OBIEE - 3

1.What you did in physical layer,BMM layer and Presentation layer?

Ans: Physical layer:Physical layer is a connection between database and repository by using connection pool.We can import the data from the different data sources, no.of connection pools, aliases like  tables and columns, duplicate tables, opaque views and physical joins etc.

BMM layer:Also called as Logical layer, Actually we implemented the logic here.We can do the calculations, create the logical tables and columns, establish the joins like physical and logical or complex joins, dimensions, level-based-metrics etc.

Presentation layer:We can show only the content which we want to show the end-user, We can implement authorization and authentication, Implicit fact column and sort order column etc.

2.What are Level-Based-Metrics(LBM)?How you use in your project?

Ans: Level-based-metrics show the measures at different levels based on the dimension hierarchy. Generally LBMs  will use to display the data at month, week and day level. To show this we will create the alias measure columns. Explain more details from your project.

3.What is Dimension Hierarchies?Explain the procedure to create the Hierarchy? 

Ans: Below link give the complete procedure for creating the dimension hierarchy..

http://oracletechnotalk.blogspot.in/2012/04/creation-of-hierarchy-in-obiee.html

4.What is Sort order columns?

Ans: We can sort the columns at 2 levels

1. Repository:At the presentation layer,sort the one column values based on another column

2. Answers:At the criteria tab->columns->sort the column values either ascending or descending order based on the requirement.

5.Rate yourself in SQL?

Ans: Here you need answer very carefully because based on your rating they are ready to fire the questions on SQL. You have the minimum knowledge in SQL like select statement, joins, performance concepts etc.

6.What is Implicit fact table and Implicit fact column?

Ans: A common fact table for dimension tables is called as Implicit fact table. An Implicit fact column is a column in a Implicit fact table, we can select the best path between the dimensions. Implicit fact column is decided in presentation layer.

7.Can we handle the nulls?

Ans: Yes,we can handle either in repository and answers.

ifnull(column_name,0)->at expression of the measure column

8.What is the use of connection pool?

Ans: We can create different no.of connection pools in the project. It has the database connection details, no.of minimum users can access the application.

9.What is security in OBIEE?

Ans: Security in OBIEE can be implemented in 2 ways.

1. Authentication:Verify and validate the users against the system. 

2. Authorization:Validate the users against the Data.

10.What is Authentication?How can we implement?

Ans: Verify and validate the users against the system.We can implement the Authentication in 4 ways

1. External table

2. Operating system

3. Data base

4. LDAP

11.What is Authorization?How can we implement?

Ans: Validate the users against the Data. Data can be limited the users by

1. Object level

2. Data level

3. Reports/Dashboard level

For More Interview Questions on OBIEE

http://oracletechnotalk.blogspot.in/2012/05/real-time-interview-questions-on-obiee_10.html

http://oracletechnotalk.blogspot.in/2012/05/real-time-interview-questions-on-obiee.html

  

Real time interview questions on OBIEE-2

Did u get the data from any other data sources? If yes how?

Yes,ofcourse client will provide the data in different formats like excel sheets, files, cubes etc. We can import the data by creating the drivers for data sources.

Can i import the data directly from other sources to repository?

yes, we can directly import the data to repository

How to set the logging level in repository?

manage->identity->goto the user->set the logging level

The logging level for developer:min 2

The logging level for Administrator:upto 7

In production phase the logging level should be disabled. We can set the logging level for a specific request at answers

Have you ever worked with ETL-tools?

As a reporting developer, i know the flow of ETL process. I never worked on that.The ETL people takecare of everything.

Which type of schema you are used in your project?

Actually most of the projects are designed like a star-schema.My project is also used star-schema as a data model.

What is star and snow-flake schema?

For complete details about star and snow-flake schema

http://oracletechnotalk.blogspot.in/2012/04/what-is-datawarehouse-datawarehouse-is.html

What is fact and dimension table?

The detailed description of fact and dimension tables

http://oracletechnotalk.blogspot.in/2012/04/what-is-datawarehouse-datawarehouse-is.html

What is the flow of your porject?

Every project must follow the software development life cycle.

Generally,the porject flow may like requirements gathering, Development(ETL and Report), testing and Production.The flow of project may differ from project to project based on domain like retail, telecom, healthcare, construction etc.

The complete knowledge on SDLC

http://oracletechnotalk.blogspot.in/2012/04/obiee-application-development-life.html

What is Dataware housing?

http://oracletechnotalk.blogspot.in/2012/04/what-is-datawarehouse-datawarehouse-is.html

Rate yourself in SQL?

OBIEE developer must have the knowledge on sql because he need to test his own stuff.Minimum knowledge of SQL is required for dataware housing developers.The rating should be dependent on the person knowledge.

Real time interview questions on OBIEE-1

Tell me About yourself?

This is the major interview question for freshers and experienced. Everyone should prepare before going to the interview.

Experienced: Tell ur name with experience what you have with current and previous organization and then explain the porject what you done. If they expecting more then give your educational details but no need to mention the family back ground.

What are your roles and responsibilities in your current project?

As a OBIEE developer,the reponsibilities like repository configuration and reports generation based on the client requirements. At the end we need to implement the security.

From where you start your work in daily routine?

Daily i got a mail from my reporting manager with a attached document. It includes all the requirements and details of the work. 

What is the architecture of OBIEE?

http://oracletechnotalk.blogspot.in/search?q=OBIEE+architecture

Explain the 3 layers of the repository?

http://oracletechnotalk.blogspot.in/search?q=OBIEE+architecture

What is connection pool and how many connection pools you have in your project?What is the purpose?

Actually connection pool is a link between repository and database. For more details use the link

http://oracletechnotalk.blogspot.in/search?q=connection+pool

We can use multiple connection pools for to increase the performance. The no.of connection pools may dependent on the project. We created different connection pools for

security implementation,users and variables creation etc.

What is the difference between Obiee 10g & 11g?

OBIEE 10g:

• We have 2 joins, foreign key join and complex join.
• We have only level-based hierarchy in the logical layer.
• All the configuration settings will be done at NQSconfig file.

OBIEE 11g:

• we have only 1 join, new join.
• We have different hierarchies; level-based hierarchies, parent-child hierarchies, ragged hierarchies, skipped hierarchies.
• All the configuration settings will be done at the enterprise manager.
• we have option like look-up table.
• In answers,the chart view renamed as graph.

which version of OBIEE you are using?

Go to help in the repository of your project. There we can get the exact version of OBIEE.

What is the difference between OCI and ODBC?

OCI will be specific to the oracle database but the ODBC connection, we can use with any datasource.

Have you ever worked on Joins?If yes how?

Yes, In physical layer i worked on joins based on the data-modelling document.

For more real time interview questions on OBIEE

For more concepts 

creation of hierarchy in OBIEE

Creation of Hierarchy in OBIEE

Dimension Hierarchy:Display the data in different levels is called as hierarchy.We can create the hierarchies by using dimensions only.For example time dimension, geography, customers etc.Hierarchy can be created either manually or by using wizard.This is the one of the most useful feature available in OBIEE for data analysis at different levels.

Pre-requisites for dimension hierarchy:

• Creation of Levels
• Assigning columns
• Defining Keys

Time Dimension Hierarchy is the one of the most useful hierarchy in real-time application.Lets work on that:
Creating hierarchy using Wizard:

Right click on the table->create dimension

 Automatically it will create the dimension

 We get only two level s one is total n other is detail level.

Creating a dimension hierarchy Manually 

right click on the schema sh -> new object -> Dimension

Give the name of the dimension(times dim)

Click OK.

Creating levels(right click on hierarchy(times dim)  -> new object -> logical level

give the name for level as year 

Click OK.

Right click on level(year) -> new object -> child level.

Give the name (quarter)

Click OK.

Same process for creating the levels on month,week and day.

Assigning columns (double clcik on the colunm(CALENDAR_YEAR)-> levels -> select year

Click OK.

The column will be added in hierarchy of year level.

Same for quarter,month,week n day.

 

 Define keys(right click oncolumn(CALENDER_YEAR) -> new logical level key.

  

  

Click ok

Same process for the quarter,month,week and day 

Save the RPD.

At Presentation Services or Answers  

 Add the columns -> click on display results

When we click on the CALENDER_YEAR -> it will display the CALENDER_QUARTER_DESC  

click on CALENDER_QUARTER_DESC à it display CALENDER_MONTH_DESC

It display till the last level of hierarchy

Difference between the OBIEE 10g and 11g while creation of Dimension hierarchy

There is no differences between OBIEE 10g and 11g while creation of dimension hierarchy but the new features are added to the latest version.The look and Feel of the wizard may change but the overview is same. 

The Advanced concepts in the hierarchy we will discuss in later posts.

OBIEE Application Development Life cycle

Application Life-cycle:
            This is the End-to End process of an application in the organisation (From requirements gathering to production support).
There are 4 phases in the life-cycle

1. Requirements Gathering
2. Development
3. Testing
4. Production or Support

1. Requirements Gathering
Business analyst(BA): He is the person or a mediator between the client and the organisation.

• First Business Analyst is going to interact with the client to know about the client business and pain-areas, from that business analysis BA will prepare the document called as Business requirement document or BRD or FSD(Functional Specification document) or SPEC.
• From the BRD,the development team will prepare the HLD and LLD.

HLD(High-level-document): Divide the BRD into different modules for requirement analysis, prepared by developers.
LLD(Low-level-document): Gives the detailed information about each entity in the BRD or HLD, prepared by developers.

• GAP Analysis:This is the comparision between client requirement and reporting tool features like is the tool fulfill all the requirements of the client or not.
• POC(Proof of Concept):This is the dummy or a duplicate model where the development team will cook the data manually and prepares a sample model which is going to send for the client's approval.

Note:The end product should be same with the POC.
2. Development 
There are 2 parts in development

• ETL development: ETL developers will prepare a data-model with all dimensions and facts.Also build an integrated dataware-house from the heterogeneous data sources.
• Reporting development:Once the DWH is built,the reporters will configure the repository and generate the reports as per the client's requirement.

3. Testing
Tesing is the major part in any application or product development.There are 3 types of testing in the OBIEE application development.

• Unit Testing:The developer will test the own stuff.
• Peer-Reviews:Test the application within the organisation as the application developed.
• UAT(User Acceptance Test):This is a client test testing,the application will be tested by some other company.

4. Production or Support
The company need to give the application support for one month by default.After that based on the client's decision, the development company or some other company will give the production support.


OBIEE architecture
working with OBIEE

Configuration and Log files in OBIEE

Configuration files:These are the default files for every tool, it will store the default settings of the tool. We can change the settings based on the requirement. Configuration files of OBIEE are

Configuration files of RPD:

NQSconfig.ini: We can configure the RPD into BI server, setting-up the cache parameters, security by-passing, usage-tracking, setting up the time-zone, date, time-format,language and buffer stack size properties.

DBfeatures.ini: We can enable/disable the database features.

NQSCluster.ini: Maintains the servers in the machine(When multiple servers existed on the same machine).By default in disable mode.

Log files of RPD: Log files are Informative files of anything.

NQServer.log: It will give the information about the BI server like when server starts and stops and why server is not started.
NQSQuery.log:Information about the query manger like how much time it will take to execute the query and performance of the query.
NQSAdministration.log:Information about the administration tool like problems with administration tool and all.
NQS Scheduler.log:Information about the schedulers like administration tool and analytics.


Configuation files of Analytics :

Instanceconfig.xml: It will store all the settings of presentation service or Analytics like reports, dashboards and all the elements .

Web-catalog: Web catalog is a folder,it will store the all the settings of a application.Maintains an individual  catalog for application is the best-practice for deploying the application.

In OBIEE 11g,all the configuration settings will be done at enterprise manager. Enterprise manager is a third-party server, here all the settings are manages like configuration of RPD, cache management, users and groups, web catalog settings.

Interview Questions


Dataware housing Basics

Physical Layer Best practices in OBIEE

PHYSICAL LAYER

• Don't import foreign keys from the database.
• Avoid circular joins by using alias tables.
• Use a different connection pool for different data sources.
• Use a different connection pool for variables,data sources and security configuration.
• Establish the joins in the physical layer based on the data-modelling or client requirement.
• Use the max no.of users for each connection pool.
• Delete the unused physical objects from the layer by using Remove unused physical object utility.
• Update the row-count by using Update physical layer to check the database connection.
• Avoid hints in the physical table level
• Don't establish join between fact and fact table
• Avoid to create a view or physical select statement in the physical layer
• Remove the unused connection pools,it might lead to BI server crash as it continuously ping to the connection.
• Better to disable the database features in the DB-features configuration file.
• Always use a data-ware house or OLAP(online analytical processing) system as a database for geenrating reports.
• Use proper naming convention for physical table and alias table also.
• Always define your catalog under projects.
• Ensure to check "execute queries asynchronously" option in the connection pool details.

BMM Layer best Practices

Presentation Layer Best Practices

Physical Layer in OBIEE

Physical Layer:The name itself is saying,it is having the direct relation between the repository and database.This is the one o f the most important layer in the repository.The query will be executed from the physical layer only.Joins in the other layers should be same with the physical layer.We can't rename the object names in the physical layer.In this layer we can import the data,creating connection pools,alias tables,duplicate tables,opaque views, keys and joins.

Data sources:We can Import the data from different Data sources like oracle, my-sql, excel sheets, XML data and cubes(OLAP and ESS base) by using different drivers. Data will be imported with key or without keys.

Connection pool:We can create a multiple connection pools for single repository to increase the performance.These are used for accessing users,variables and implementing the security etc.

Tables:Alias and duplicate tables will be created in the physical layer.

Alias table:It is copy of parent table and always functionally depending on the parent table.Alias table will be created for re-use the same table for different purpose.

Duplicate table:It is a copy of parent table but don't have the  relation,once created.

Keys:If u import the tables without keys,here we can create the keys based on the data-modelling.The joins will be established in the physical layer only based on the primary and foreign key relation.

Opaque views:Opaque view is physical select statement,which does not occupy any space in the repository.We can deploy or un-deploy from repository to database.

Data-ware housing basics

Dataware house is a 

Non-volatile: Permanent data(stores historical data or past data)

Subject-oriented: Data will be stored according to the subjects(like data-marts)

Integrated:Make a single source from heterogeneous sources

Time-variant: Data changed over a period of time

Dataware Housing: It is process of storing and retrieving data from warehouse.

Steps Involved from database to datawarehouse:

1. Extract the data from the heterogeneous sources and load into staging area(Source Dependent Loading)

2. Apply transformations on staging area and load into datawarehouse (Source Independent Loading)

3. Generate the reports from the datawarehouse by using a reporting tools like OBIEE, Cognos etc.

We can generate the reports from the OLTP or databases but the performance may decrease because the no.of  joins are more.

why we do reporting?

Reporting is an essential event for analysis of  the data of the specific organisation. Anyone can analyse like management, users, partners etc.We can forecast the values by using the reports or reporting tool. Reporting will be exist in all domains like Retail, Tele-communication, Insurance, Banking, Education, Public sector industries or factories and private sector industries.Analysis of data is everywhere like small-scale, medium-scale and large-scale industries. 

Basic definitions in data-ware housing

Dataware housing Interview Questions


Introduction to OBIEE