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

OBIEE Architecture

OBIEE(Oracle Business Intelligence Enterprise Edition) latest version is 11.1.1.5 or 6.It is a reporting tool for generate the reports to analyse the data.OBIEE has 2 default servers BI server and Presentation server.It has 6 components in the architecture

1.Presentation services(Answers or Analysis)

2.Presentation server

3. Repository

4. BI server

5. Database connection(ODBC (Open database connectivity) or OCI (Oracle call Interface))

6. Enterprise Data ware House

Presentation Services:This is a front-end page for users or clients.User can see the reports from the Answers or Analysis  page.It contains answers, dashboards, account details privileges of the specific users.We can the reports and dashboards based on the client requirement.By starting the presentation server,we can operate the presentation services.

Repository:It is also called as RPD or RCU.Metadata settings are configured in the rpd level.It has 3 layers

Physical layer:It has the direct connection with the database.We can apply the joins between fact tables and dimension tables based on the data-modelling.

Business model and mapping layer:The logical changes will be done here like creating logical joins(complex joins),new columns or tables,hierarchies etc.

Presentation layer:The objects will be visible to the users what placed in the presentation layer.We can apply the security setting in the presentation layer.

We can operate the Repository settings by starting the BI server.we can open the RPD in either online or offline mode

Database connection:This is the database connection between database and OBIEE.The database connection  is either ODBC or OCI.

Data-ware House: Data-ware House is a huge data storage space for retrieving data.

OBIEE architecture interview questions

MINUS Operator in SQL

It will display output unique to first query.

Syntax:
<Component query >
MINUS
<Component query>


Example:
SELECT job FROM emp WHERE deptno = 10
MINUS
SELECT job FROM emp WHERE deptno = 20;


-- The above query displays the job present only in first query, should not be in the result of second query.


Click on for practice questions
Click on for interview questions

INTERSECT Operator in SQL

It returns all rows common to multiple queries.

Syntax:
<Component query>
INTERSECT
<Compnent query>;

Key points:
Reversing the order of component queries does not result a change in output.
NULL values are not ignored.

Example:
SELECT job FROM emp WHERE deptno = 10
INTERSECT
SELECT job FROM emp WHERE deptno = 20;

-- The above query display the common job in 10th and 20th departments.

click on for practice questions
click on for interview questions

UNION ALL operator in SQL

The UNION ALL returns results from both queries with duplications.
It will combine the output of two or more queries.

Syntax:
<Component query >
UNION ALL
<Component query>

Keypoints:
Output is not sorted by default.

UNION Operator practice questions

Display the deptno, job of the employees working in 10th and 20 th departments.

SELECT Deptno,job FROM emp WHERE deptno = 10

UNION

SELECT Deptno,job FROM emp WHERE deptno = 20;

Write a query to display the 10th and 30th departments employee names with their numbers in order with ename.

SELECT Empno, Ename FROM emp WHERE deptno = 10

UNION

SELECT Empno, Ename FROM emp WHERE deptno = 30 

ORDER BY 2

Click on for interview questions

UNION Operator in SQL

The UNION operator returns all the rows selected by either query. It is used when output is to combine all rows from multiple tables in the query.

It eliminates duplicate rows in the final result.

Syntax:
<Component query>
UNION
<Component query>;

key points: 

• Semi colon should be placed at the end of the last component query.
• NULL values are considered during duplicate check.
• Output is sorted in ascending order according to the first column of SELECT clause.
• The IN operator has a higher precedence than the UNION operator.

Example:

SELECT job FROM emp WHERE deptno = 10

UNION

SELECT job FROM emp WHERE deptno = 20;

-- The above query displays the distinct jobs of employees working in 10th and 20th department.

Click on for practice queries
Click on for interview queries

set operators

The results of two or more component queries can be combined into one result using SET Operators.They combine the output in the vertical manner.

Queries  containing SET Operators are called Compound queries.

All SET Operators have equal precedence. If a SQL statement contains multiple SET Operators, the oracle server evaluates them from left (top) to right (bottom).

Different types of SET Operators are

• UNION
• UNION ALL
• INTERSECT
• MINUS

The number of columns and datatypes of the columns being selected must be identical in all the select statements used in the query. The names of the columns need not be identical.

General Syntax:

<Component query>
{UNION | UNION ALL | MINUS | INTERSECT}
<Compnent query> 

Basic dataware-housing interview questions

What is datawarehouse?
Datawarehouse is a

• Non-volatile
• Integrated
• Subject-oriented
• Time-varient

database system

What are the steps involved in loading a  datawarehouse?
There are 3 steps in datawarehousing,

• data-extraction from the OLTP system
• Apply the required transformations on the data
• Load the data into datawarehouse

What is fact table?
Fact table is  a table, which is having the multiple foreign keys of the dimension tables and measures.Fact table is also called as child table having dimension codes and facts/measures.

What is dimension table?
Dimension table is a table,which is having the descriptive information of an entity.This is also called as master table having the primary key.
Always the data flow from dimension table to fact table.

What is data-modelling?
The process of storing and retrieving the data from datawarehouse is called as data-modelling.

What is star schema and snow-flake schema?What is the difference between those?
Star is one fact and many dimensions where as Snowflake design is one fact and many dimensions and dimensions can have additional dim relations.

No.of joins in the snow-flake schema is more compare to star schema.

Difference between OLTP and OLAP systems?
OLTP(Online transactional process):

Dynamic data
volatile
Normalised data
Efficient for data storing
OLAP(Online Analytical process):

90%static,10%Dynamic data
Non-volatile
De-normalised data
Efficient for data retrieving

What are different types of dimensions?

• confirmed dimension
• slowly changing dimension
• rapidly changing dimension
• De-generated dimension

What are different types of fact tables?

• Additive fact table
• semi-additive fact table
• Non-additive or fact-less fact table

What is Federated Query?

A query is fired across multiple sources is called as federated query.

like

select cust_first_name,revenue from customers,sales

customers->SQL,sales->DB2 data sources

OBIEE architecture interview questions and answers

What are the major components in the OBIEE architecture?
a. Client
b. Presentation services(along with cache)
c. Repository
d. Database connection
e. Enterprise datawarehouse
f. Scheduler(along with cache)

What are the different layers of OBIEE Repository?
a. Physical Layer
b. Business Model and Mapping Layer
c. Presentation Layer

What are the folders available in OBIEE?

• Oracle BI(Administration)
• Oracle BIdata(analytics)

The folder names may change with the OBIEE 11g.

What are the Key Configuration Files in OBIEE

• NQSConfig.ini,
• DBfeatures.ini,
• NQSCluster.ini,
• odbc.ini,
• instanceconfig.xml

What are the different Log files in OBIEE?

• NQServer.log
• NQSQuery.log
• NQSAdministration.log
• NQS Scheduler.log

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?
BI server (Administration Tool)
Presentation Server (Analytics or Answers)
Scheduler server (Scheduling the jobs)

What is the location of the administration configuration files?
drive:\OBIEE 11g\instances\instance1\config\oracleBIservercomponent\
coreapplication_obis1\file name

What is the location of the repository?
drive:\OBIEE 11g\instances\instance1\bifoundation\oracleBIservercomponent\
coreapplication_obis1\repository

What is the location of the presentation services configuration files?
drive:\OBIEE 11g\instances\instance1\config\oracleBIpresentationservices
component\coreapplication_obis1\instanceconfig.xml

What is the location of the administration log files?
C:\OBIEE11g\instances\instance1\diagnostics\logs

basic Pl/SQL question and answers

Describe the block structure of Pl/SQL.
Ans: Declaration, begin, exception, end.

What is an anonymous block?
Ans: unnamed Pl/SQL block.

What is Pl/SQL collection?
Pl/SQL table, varray, Pl/SQL array, etc.

How can you tell if an UPDATE updated no rows?
Ans: SQL%NOTFOUND.

How can you tell if a SELECT returned no rows.
Ans: NO_DATA_FOUND exception.

How do you run Native Dynamic SQL? 
Ans: Execute immediate.

SQL interview question and answers

What is SQL?
SQL, SEQUEL (Structured English Query Language), is a language for RDBMS (Relational Database Management Systems). SQL was developed by IBM Corporation.


How many categories of data types?
Oracle supports the following categories of data types:
Oracle Built-in Datatypes.
ANSI, DB2, and SQL/DS Datatypes.
User-Defined Types.
Oracle-Supplied Types.


What are the Oracle Built-in Data Types?
There are 20 Oracle built-in data types, divided into 6 groups:
Character Datatypes - CHAR, NCHAR, NVARCHAR2, VARCHAR2
Number Datatypes - NUMBER, BINARY_FLOAT, BINARY_DOUBLE
Long and Row Datatypes - LONG, LONG RAW, RAW
Datetime Datatypes - DATE, TIMESTAMP, INTERVAL YEAR TO MONTH,
INTERVAL DAY TO SECOND
Large Object Datatypes - BLOB, CLOB, NCLOB, BFILE
Row ID Datatypes - ROWID, UROWID


What Are the Differences between CHAR and NCHAR?
Both CHAR and NCHAR are fixed length character data types. But they have the
following differences:
CHAR's size is specified in bytes by default.
NCHAR's size is specified in characters by default. A character could be 1
byte to 4 bytes long depending on the character set used.
NCHAR stores characters in Unicode.


What Are the Differences between CHAR and VARCHAR2?
The main differences between CHAR and VARCHAR2 are:
CHAR stores values in fixed lengths. Values are padded with space characters
to match the specified length.
VARCHAR2 stores values in variable lengths. Values are not padded with any
characters.


What Are the Differences between NUMBER and BINARY_FLOAT?
The main differences between NUMBER and BINARY_FLOAT are:
NUMBER stores values as fixed-point numbers using 1 to 22 bytes.
BINARY_FLOAT stores values as single precision floating-point numbers.


What Are the Differences between DATE and TIMESTAMP?
The main differences between DATE and TIMESTAMP are:
DATE stores values as century, year, month, date, hour, minute, and second.
TIMESTAMP stores values as year, month, day, hour, minute, second, and
fractional seconds.


What Are the Differences between INTERVAL YEAR TO MONTH and
INTERVAL DAY TO SECOND?
The main differences between INTERVAL YEAR TO MONTH and INTERVAL DAY TO
SECOND are:
INTERVAL YEAR TO MONTH stores values as time intervals at the month
level.
INTERVAL DAY TO SECOND stores values as time intervals at the fractional
seconds level.


What Are the Differences between BLOB and CLOB?
The main differences between BLOB and CLOB are:
BLOB stores values as LOB (Large OBject) in bitstreams.
CLOB stores values as LOB (Large OBject) in character steams


What Are the ANSI Data Types Supported in Oracle?
The following ANSI data types are supported in Oracle:
CHARACTER(n) / CHAR(n)
CHARACTER VARYING(n) / CHAR VARYING(n)
NATIONAL CHARACTER(n) / NATIONAL CHAR(n) / NCHAR(n)
NATIONAL CHARACTER VARYING(n) / NATIONAL CHAR VARYING(n) / NCHAR
VARYING(n)
NUMERIC(p,s)
DECIMAL(p,s)
INTEGER / INT
SMALLINT
FLOAT
DOUBLE PRECISION
REAL

SQL interview question and answers

To see current user name
Ans: Sql> show user;

How to change sql prompt name
Ans: Sql> set sqlprompt "Scott>"

How to switch to DOS prompt
Ans: sql> host


How do i eliminate the duplicate rows?
Ans: delete from table_name where rowid not in (
select max(rowid) from table group by duplicate_value_field_name);


How do i display row number with records?
Ans: Select rownum. ename from emp;


To view installed oracle version information
sql> select banner from v$version;


display the number value in words
Ans: select sal, (to_char(to_date(sal,'j'),'jsp')) from emp


what is the output of SIGN function?
Ans: 1 for positive value, 0 for zero, -1 for negative value


What are the more common pseudoo-colums?
Ans: sysdate, user, uid, curval, nextval, rowid, rownum


which date function returns number value?
months_between

SQL Practice questions 2

Display the names of employees who are working as clerks,salesman or analyst and drawing a salary more than 3000?

Ans: select ename from emp where job='CLERK' OR

JOB='SALESMAN' OR

JOB='ANALYST' AND SAL>3000;

Display the names of the employees who are working in the company for the past 5 years?

Ans: select ename from emp where

to_char(sysdate,'YYYY')-to_char(hiredate,'YYYY')>=5;

Display the list of employcees who have joined the company before 30-JUN-90 or after 31-DEC-90?

Ans:  select ename from emp where hiredate < '30-JUN-1990' or hiredate

> '31-DEC-90';

Display current Date?

Ans: select sysdate from dual;

Display the list of all users in your database(use catalog table)?

Ans: select username from all_users;

Display the names of all tables from current user?

Ans: select tname from tab;

jobs in oracle corporation of india

Applications Developer 1 -  https://irecruitment.oracle.com/OA_HTML/OA.jsp?OAFunc=IRC_VIS_VAC_DISPLAY&OAMC=R&p_svid=1751769

Product Manager/Strategy 4-ProdDev - https://irecruitment.oracle.com/OA_HTML/OA.jsp?OAFunc=IRC_VIS_VAC_DISPLAY&OAMC=R&p_svid=1753795

QA Analyst 3-ProdDev - https://irecruitment.oracle.com/OA_HTML/OA.jsp?OAFunc=IRC_VIS_VAC_DISPLAY&OAMC=R&p_svid=1751769

Database Administrator 3-IT - https://irecruitment.oracle.com/OA_HTML/OA.jsp?OAFunc=IRC_VIS_VAC_DISPLAY&OAMC=R&p_svid=1755750

Telesales Representative I - https://irecruitment.oracle.com/OA_HTML/OA.jsp?OAFunc=IRC_VIS_VAC_DISPLAY&OAMC=R&p_svid=1753719

SQL practice questions 1

Display the details of all employees?

Ans: Select * from emp;

Display the department information from department table?

Ans: select * from dept;

Display the name and job for all the employees?

Ans: select ename,job from emp;

Display the name and salary for all the employees?

Ans: select ename,sal from emp;

Display the employee no and totalsalary for all the employees?

Ans: select empno,sal+comm as total from emp group by empno;

Display the employee name and annual salary for all employees.

Ans: select ename,sal * 12 as annual salary from emp;

Display the names of all the employees who are working in depart number 10.

Ans: select emame from emp where deptno=10;

Display the names of all the employees who are working as clerks and drawing a salary more than 3000.

Ans: select ename from emp where job='CLERKS' and sal>3000;

Display the employee number and name who are earning comm?

Ans: select empno,ename from emp where comm is not null;

Display the employee number and name who do not earn any comm?

Ans: select empno,ename from emp where comm is null;

Candidate should learn the topics in OBIEE(Oracle Business Intelligence Enterprise Edition)

Candidate should have the basic database knowledge and ETL(Extraction,Transformation and Load) process(Any tool of ETL),then learn the concepts of OBIEE. OBIEE is a reporting tool,to generate the reports for data analysis.This is reporting tool used most frequently. The concepts of OBIEE includes 

I. Administrator.

• OBIEE and BI Server Architecture.
• Data warehouse Basics.
• Repository basics, structure, features, and functions - Using BI Admin tool.
• Building a Physical Layer (Data sources, connection pool, tables, Keys, Aliases and opaque views).
• Best Practices to follow while building a Physical Repository.
• Building a Business Model (Logical Table, Column, Sources).
• Building a time dimension hierarchy and other dimensions.
• Defining logical models and building measures.
• Learning the Business Model properties.
• Building the presentation layer in Repository.
• Create Presentation layer objects and setting up appropriate properties.
• Testing and validating a repository.
• Deploying the repository to the BI Server.

II. Answers

• Introduction to presentation services  or Analytics - Answers, Dashboards and Delivers.
• Creating reports, filters, formulas, prompts in Answers.
• Exploring Charts and graphs in Answers.
• Creating multiple views of the same report.
• Creating pivot tables and other views.
• Dashboard basics and exploring dashboard editor.

III. Security - Administrator

• Introduction to Security in BI Admin tool and presentation server Administration.
• Creating Users and Groups. Permissions for users and groups.
• Setting up different connection pools, Query limits and timing restrictions.
• Variables – Repository, Session and Presentation Variables.
• Advanced concepts in presentation server.
• Using variables during design and developments.