Top 24 Obiee Interview Questions

Obiee Interview Questions

Obiee Interview Questions

1) What is Obiee?

Obiee stands for Oracle Business Intelligence Enterprise Edition (OBIEE). It is a business intelligence system for the enterprise that delivers abilities for reporting, online analytical processing (OLAP), ad hoc query and analysis dashboards, and scorecards.

2) Mention what are the main features of OBIEE?

Key features of OBIEE includes,

1. Hierarchy Drilling

2. Scheduled Report Generation

3. Graphical Reporting (Charts, Pivots, Gauges, )

4. Ad Hoc Analysis

5. Global support and development capability

3) Mention what is the end to end lifecycle of OBIEE?

The end to end life cycle of OBIEE is,

1. Collecting Business Requirements

2. Identify source systems

3. Design ETL to load data to the Data Warehouse

4. Creating repository

5. Create dashboards and reports

6. Setting up security (LDAP or External table)

7. Decide on aggregations or caching mechanism based on performance

8. Testing and QA.

4) Explain how OBIEE architecture works?

OBIEE architecture works in following way,

1. A request is made by the Users and sent to the Presentation Server.

2. The Presentation Server transforms the request into logical SQL and sent to the BI server.

3. The BI server transforms logical SQL into physical SQL and sent it to the database

4. The result gets back to the user through the same path

5) Mention what are the main components of OBIEE?

OBIEE components are divided mainly into tow types, Server Components and Client Components. The client component is further divided into web-based obiee client and non-web based client.

Server Components	Client Components
	Web Based OBIEE Client	Non- Web based Client
Oracle BI (OBIEE) Server	Interactive Dashboards	OBIEE Administration
Oracle Presentation Server	Oracle Delivers	ODBC Client
Application Server	BI Publisher
Scheduler	BI Presentation Service Administrator
Cluster Controller	Answers
Oracle BI (OBIEE) Server	Disconnected Analytics
Oracle Presentation Server	MS Office Plugin

6) Mention what is ODBC Client is used for?

ODBC Client is used to connect to the database and execute SQL commands.

7) Mention what kind of sources can be connected to OBIEE?

Sources like Relational Databases, Cubes or Flat files can be connected to Obiee with following constraints,

·          The sources must be modeled as snowflakes or star schemas for efficient result

·          In the case of multiple sources connecting directly in OBIee, it has to be used

8) Mention what all information does OBIEE repository contains?

OBIEE repository contains information about the application environment like,

·          Data Modeling

·          SQL Information

·          Caching

·          Connectivity Information

·          Aggregate Navigation

·          Security

9) Mention what are the three layers of OBIEE repository?

The three layers of OBIEE repository are,

·          Physical Layer (Schema Design): It contains information about the data sources

·          Business Model Layer: It outlines the business or logical model of objects and their mapping between business model and Schema in the physical layer

·          Presentation Layer: Presentation layer is used to provide customized views of Business Model in Business Model layer to users. 

Obiee Interview Questions

10) Mention what does physical layer is used for?

Physical layer is used for,

·          Importing data

·          Creating Aliases

·          Building physical joins

·          Setting up connection pool and its properties

·          Enabling/ Disabling cache for individual table

11) Mention what is LTS in OBIEE?

LTS or Logical Table Source is created when you drag and drop a column from a table that is not presently being used in your logical table. A physical table containing such column is referred as LTS.

12) Mention what is session variable in OBIEE?

A session variable can be a system variable and non-system variable. It is initialized at login time for each user. System variable uses NQ_SESSION.(system reserved variable). Examples of non-system variables are user defined filters etc.

13) Mention what are the different log files in OBIEE?

Different log files in OBIEE includes,

·          NQServer.log

·          NQQuery.log

·          NQSAdminTool.log, etc.

14) Mention what are the Key Configuration Files in OBIEE?

Key Configuration Files in OBIEE are,

·          NQSConfig.ini

·          NQSCluster.ini

·          odbc.ini

·          instanceconfig.xml

15) Mention what are the security providers used by OBIEE?

Security providers used by OBIEE are,

·          Authentication provider to authenticate users

·          Policy store provider is used to access privileges on all applications except for BI Presentation Services

·          Credential store provider is used to store credentials used internally by the BI application

16) Mention what is the difference between logical table source and logical table?

The difference between logical table source and the logical table is that a logical table consists of one or more logical table source. The mapping between  logical column and physical columns are done in this element.

17) Mention how many server instances can coexist in an OBIEE cluster?

Maximum 16 server instances coexist in an OBIEE cluster.

18) Mention whether it is possible to create an Outer join in an OBIEE physical layer?

In an OBIEE physical layer, it is not possible to create the Outer join.  You will create the join type in the Business Layer.

19) Mention how to bypass the server authentication?

To bypass the server authentication in NQSConfig.ini and instance config.xml, BYPASS SERVER AUTHENTICATION = YES.

20) Mention what is ibot?

Ibot is a scheduling agent, used to schedule reports to be sent across various devices such as email, pager, mobile, other devices, etc.

21) Mention how to get real physical SQL sent by OBIee to the database?

To get real physical SQL sent by OBIee to the database, check following details

1. Verify the value of your session variable LOGLEVEL (5 is fine)

2. Go into Administration -> Manage sessions

3. Find your query (at the bottom of the list), and click on “View log.”

4. Below the logical SQL, you will see the physical SQL.

22) Mention what does a Fact table consist of?

A Fact table consists of two types of columns

·          Facts and

·          Foreign key to dimension tables

23) Mention how to hide certain columns from a user?

To hide certain columns from a user,

·          Do not add the column in the report

·          Do not add the column in the presentation layer

24) Mention how you will change the port of Obiee?

You can change port of Obiee by changing its port in instanceconfig.xml

1 Read More »

Top 24 T-SQL interview Questions

T-Sql Interview Questions

T-SQL Interview Questions

1) what is T-SQL?

T-SQL  stands for Transact Structured Query Language. It is an extension of SQL functionality supported by Microsoft SQL Server and Sybase ASE.

2) Mention what is the difference between SQL and T-SQL?

The difference between SQL and TSQL is that SQL is a query language to operate on sets, while TSQL is a proprietary procedural language used by MS SQL Server.  Also, T-SQL has a different implementation of DELETE and UPDATE than SQL.

3) Mention how tsql statements can be written and submitted to the Database engine?

Tsql statements can be written and submitted to the Database engine in following ways,

·          By using the SQLcmd Utility

·          By using the SQL Server Management Studio

·          By connecting from an application that you create

4) Mention what is “GO” in T-SQL?

“GO” is not a Transact-SQL statement but a batch separator. It is a command identified by the sqlcmd and osql utilities and SQL Server Management Studio Code editor.  SQL Server utilities read “GO” as a signal that they should send the current batch of TSQL statements to an instance of SQL Server.

5) Mention what is the difference between TRUNCATE and DELETE statement?

The difference between TRUNCATE and DELETE statement is that,

·          TRUNCATE is used for unconditional removal of data records from Tables. Truncate Operations are not logged.

·          DELETE is used for conditional removal of data records from Tables. These operations are logged.

6) Mention how does a local variable is defined using T-SQL?

A local variable is defined using TSQL by using statement “DECLARE” and the name of the local variable should begin with “@” sign as the first character of its name. For example, integer CNT we will define local variable as,

DECLARE @CNT INT

7) Mention what does the T-SQL command IDENT_CURRENT does?

The TSQL command IDENT_CURRENT returns the last identity value produced for a specified table or view.  The last identity value created can be for any session and any scope.

8) Mention what does the T-SQL command IDENT_INCR does?

TSQL command IDENT_INCR returns the increment value mentioned during the formation of an identity column in a table or view that has an identity column.

9) Mention if it is possible to import data directly from T-SQL commands without using SQL Server Integration Services? If yes, what are the commands?

Yes, it is possible to import data directly from T-SQL commands without using SQL Server Integration Services.  These commands include,

·          BCP

·          OpenRowSet

·          Bulk Insert

·          OPENQUERY

·          OPENDATASOURCE

·          Linked Servers

10) Mention what is sub-query?

A sub-query is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.

A sub-query can be used with the statements like Update, select, delete and insert with the operators like =, >, <, >=,<=, etc.

11) Mention what are dynamic queries in T-SQL?

Dynamic queries in T-SQL are those queries designed on the fly/ at run time using variables or using CTE or other sources.  We use EXECUTE function or SP_EXECUTESQLStored Procedure to execute such queries.

12) Mention what are ROLLUP and CUBE in T-SQL?

Rollup and cube are the grouping sets used along with GROUP BY clause to generate summarized aggregations. These are mainly used for Data Audits and Report Generation.

13) Mention what are the maximum number of rows that can be constructed by inserting rows directly in VALUE list?

The maximum number of rows that can be constructed by inserting rows directly in VALUE list is 1000.

14) Mention what is TOP in TSQL?

TOP limits the rows returned in a query result set to a specified number of rows or percentage of rows in SQL Server. When TOP is used in combination with the ORDERBY clause, the result set is limited to the first N number of ordered rows. Otherwise, it retrieves the first N number of rows in an undefined order.

15) Mention what are the Join Types in TSQL?

Join Types in TSQL are,

·          Inner join

·          Outer join

·          Left outer join

·          Right outer join

·          Left outer join with Exclusions

·          Right outer join with Exclusions

·          Full outer join

·          Full outer joins with Exclusions

·          Cross join

16) Mention what are the T String functions available in TSQL?

T String functions available in TSQL are,

·          Left

·          Right

·          Ltrim

·          Rtrim

·          Substring

·          Replace

·          Stuff

17) Mention what is the syntax used for partition in TSQL?

In TSQL, the syntax used for partition is,

[ database_name. ] $PARTITION.partition_function_name(expression)

18) Mention what is the syntax for using SQL_Variant_Property?

The syntax for using SQL_Variant_Property is,

SQL_Variant_Property (expression, property)

19) Mention what is OFFSET-FETCH filter in tsql?

In tsql OFFSET-FETCH filter is designed similar to TOP but with an extra element.  It helps to define how many rows you want to skip before specifying how many rows you want to filter.

20) Mention what is uncommittable transactions?

When an error occurs in a transaction within a TRY block, and if the error is not serious it enters into a status open and Uncommittable. In uncommittable state, the transactions cannot perform any action that would generate a write to the transaction log.

21) Mention what is Sp_pkeys?

Sp_pkeys is part of  Catalog Stored Procedures and returns primary key information for a single table in the current database.

Sytax for Sp_pkeys is sp_pkeys [ @table_name = ‘Tbale name.’ ]

22) Mention how to do backup entire database?

To backup entire database, use following syntax;

BACKUP DATABASE { database_name }

TO backup_device [ ,…n ]

[ MIRROR TO clause ]

[ WITH { DIFFERENTIAL | [ ,…n ] } ];

23) Mention what are the limitations of IDENTITY column?

The limitations of the IDENTITY column is that column values cannot be updated once generated. Also, it may require to specify this column as a PRIMARY KEY, as such, there is a possibility of duplication of values within a table.  Identity property is applicable for integer based column only.

24) Mention what is the use of SET statement in TSQL?

In TSQL, SET statement allows you to change the current session handling of specific information like: system language, dateformat, lock timeout, rowcount and so on.

1 Read More »