BIAPPS consultant Role- Interview Questions

1) Expalin your roles and responsibilties?

2) Difference between Rank and Dense Rank?

RANK, ROW_NUMBER and also DENSE_RANK are very useful for taking a set of rows and ordering them in a defined manner, whilst giving each row a “position value”. They Differ based on the approach taken to define the value of their position in the set of output rows. In some circumstances, they may all give the same value however, dependent on the data, they may differ.An example based on the SCOTT.EMP table, helps to illustrate…

SELECT empno,   sal,  RANK() OVER(ORDER BY sal) rank_position,      DENSE_RANK() OVER(ORDER BY sal) dense_rank_position, ROW_NUMBER() OVER(ORDER BY sal) row_number_position

FROM   emp

which returns, on my 11gR1 database…

EMPNO        SAL RANK_POSITION  DENSE_RANK_POSITION  ROW_NUMBER_POSITION

---------- ---------- ------------- ------------------- -------------------

7369        800             1                   1                   1

7900        950             2                   2                   2

7876       1100             3                   3                   3

7521       1250             4                   4                   4

7654       1250             4                   4                   5

7934       1300             6                   5                   6

7844       1500             7                   6                   7

7499       1600             8                   7                   8

7782       2450             9                   8                   9

7698       2850            10                   9                  10

7566       2975            11                  10                  11

7788       3000            12                  11                  12

7902       3000            12                  11                  13

7839       5000            14                  12                  14

14 rows selected.

Notice that RANK has given the two employees with SAL = 1250, the same position value of 4 and the two employees with SAL=3000, the same position value of 12. Notice also that RANK skips position values 5 and 13 as it has two entries for 4 and 12 respectively. RANK uses all numbers between 1 and 14, except 5 and 13. RANK has both repeats and gaps in it’s ordering.DENSE_RANK is similar to RANK, in that it gives the two employees with SAL=1250, the same position value of 4, but then it does not skip over position value 5 – it simply carries on at position 5 for the next values. DENSE_RANK uses, for the position values, all numbers between 1 and 12, without leaving any out, and using 4 and 11 twice. DENSE_RANK has no gaps in it’s ordering, only repeats.ROW_NUMBER gives each row a unique position value and consequently uses all the numbers between 1 and 14. ROW_NUMBER has no gaps or repeats in it’s ordering. Note that the position value on ROW_NUMBER is not deterministic, since the ORDER BY clause only has SAL in it. If you want to ensure the order is the same each time, you need to add further columns to the ORDER BY clause.

3) Explain Obiee Architecture?

4) Differences between 11G and 10 G?

A Database Repository must be created  before installing OBIEE by using Repository Creation Utility(RCU) tool

OBIEE 11g uses a WEBLOGIC server as Applictaion server whereas OBIEE 10g uses OC4J

Many configuration settings (such as uploading a repository into BI server) can be done using EM

OBIEE 11g displays table names and COLUMN NAMES while mapping whereas OBIEE 10g displays only table names

OBIEE 11g join is done from fact to dimension where as in 10g join is done from dimension to fact

In 10g Users and Groups(or Roles) are created in repository whereas in 11g Users and Groups are created in EM

Groups  no longer exist and are replaced by Application roles. Data level security is implemented by using Application roles to which users belong to.
In Presentation Catalog, AuthenticatedUser role is used instead of Everyone group

LTS priority ordering is introduced

We can model LOOK UP TABLE is repository

Presentation layer Hierarchies are introduced

New time series functions PERIOD ROLLING and AGGREGATE AT is introduced

TIME SERIES functions can be created in the front end also

AGGREGATE PERSISTENCE WIZARD creates indexes automatically

SESSION VARIABLES are intialised only when they are used

In addition to the existing views, Map View is introduced in 11g

RAGGED and SKIPPED HIERARCHY are supported in 11g

PARENT CHILD Hierarchy is introduced

Presentation Variable can hold multiple values

KPI's and SCORECARD's are introduced in 11g

ACTION LINKS, MASTER-DETAIL reports, SELECTION STEPS are introduced

SELECT_PHYSICAL is supported

REQUESTS are renamed as ANLAYSIS

IBOTS are renamed as AGENTS

CHARTS are renamed as GRAPHS

5) Performance tuning techniques?

1 implement caching mechanism
2. use aggregates
3. use aggregate navigation
4. limit the number of initialisation blocks
5. turn off logging
6. carry out calculations in database
7. use materialized views if possible
8. use database hints
9. alter the NQSONFIG.ini parameters

6) Explain different types of Cache Mechanism's?

Ø  The use of an Oracle BI Server event polling table (event table) is a way to notify the Oracle BI Server that one or more physical tables have been updated and then that the query cache entries are stale.An event polling table(S_NQ_EPT) is a way to notify the Oracle BI Server that one or more physical tables have been updated. Each row that is added to an event table describes a single update event. The cache system reads rows from, or polls, the event table, extracts the physical table information from the rows, and purges cache entries that reference those physical tables.Finally it truncate the Event Table(S_NQ_EPT).

Ø  Call SAPurgeAllCache()--Purging the whole cache.

Ø  Purging the cache by table

Ø  Purging the cache by database

7) What is complex join And foreign key join?

8) Can you use complex join in physical layer?

Complex join is used in a physical layer when we want to use join expressions. For ex: we want to use greater than or less than operator.

9) Types of Security?

Ø  Data Level Security

Ø  Object Level Security

10) How do you implement Data level Security?

Data Level security is implemented in the case when the user logging in views the data which he/ she has the access to. This includes to include a system session variable 'USER'to capture the user's logging credentials and apply it to the reports query.

I am having three tables for serving their purpose.
1. Dimension Table - DIM2
2. Fact Table  - FACT1
3. Security Table -SECURITY_1

Data present in the above tables is shown below-

Next we need to import the three tables in the RPD and do the below joins-

Joins with Dimension and Security table in physical Layer

"DIM2"."COUNTRYID" = "SECURITY_1"."COUNTRYID"

Joins with Fact and Security table in physical Layer

"FACT1"."EMPID" = "SECURITY_1"."EMPID"

Joins with Fact and Security table in physical Layer

"DIM2"."COUNTRYID" ="FACT1"."COUNTRY_ID"


In the BMM layer for the dimension table we need to make the below changes. This change is used to apply when the user selects only the dimension column. So that the values which he/ she has the access will be shown after making the joins with the security table. For example- On clicking of the Prompt values.

And the below change in the Content Tab -where clause

In the BMM layer for the fact table we need to make the below changes. Data will be restricted after making joins with the security table and joins with the dimension  table. You can try removing the joins with the dimension and security tables which is not mandatory, since we are joining with the fact and security tables.

And the below change in the Content Tab -where clause

Now its time to view our reports in answers. When the user logs in with 100 as empid then the user is allowed to view only his/ her data.

With the Fact column-

When the user with 200 empid logs in-

Prompt value shown for the user with empid 200

Below are the queries generated-

For Dimension Security

WITH SAWITH0 AS
  (SELECT DISTINCT T222731.COUNTRY_NAME AS c1
  FROM OBIEE_DBA.SECURITY_1 T222728,
    OBIEE_DBA.DIM2 T222731
  WHERE ( T222728.EMPID = '100'
  AND T222728.COUNTRYID = T222731.COUNTRYID )
  )
SELECT DISTINCT 0 AS c1, D1.c1 AS c2 FROM SAWITH0 D1 ORDER BY c2

With Fact Dimension columns with Security tables joins. With user with login id as 100

WITH SAWITH0 AS
  (SELECT DISTINCT T222731.COUNTRY_NAME AS c1,
    T222734.SALARY                      AS c2
  FROM OBIEE_DBA.SECURITY_1 T222728,
    OBIEE_DBA.DIM2 T222731,
    OBIEE_DBA.FACT1 T222734
  WHERE ( T222728.EMPID = T222734.EMPID
  AND T222728.EMPID     = '100'
  AND T222728.COUNTRYID = T222731.COUNTRYID
  AND T222731.COUNTRYID = T222734.COUNTRY_ID )
  )
SELECT DISTINCT 0 AS c1,
  D1.c1           AS c2,
  D1.c2           AS c3
FROM SAWITH0 D1
ORDER BY c2,
  c3

With user with login id as 200

WITH SAWITH0 AS
  (SELECT DISTINCT T222731.COUNTRY_NAME AS c1,
    T222734.SALARY                      AS c2
  FROM OBIEE_DBA.SECURITY_1 T222728,
    OBIEE_DBA.DIM2 T222731,
    OBIEE_DBA.FACT1 T222734
  WHERE ( T222728.EMPID = T222734.EMPID
  AND T222728.EMPID     = '200'
  AND T222728.COUNTRYID = T222731.COUNTRYID
  AND T222731.COUNTRYID = T222734.COUNTRY_ID )
  )
SELECT DISTINCT 0 AS c1,
  D1.c1           AS c2,
  D1.c2           AS c3
FROM SAWITH0 D1
ORDER BY c2,
  c3

11) What is Fragmentation?

We use fragmentation when we have fact or dimensional data in one or more different tables or data is splitted in different data sources. Then each logical table source represents one data segment

12) What are variables? Have you worked on variables? Give me examples

Ø  Repository Variables

Ø  Session Variables

13) What do you know about DAC?

14) Give me examples of active transformation which will decrease the number of ports in Targets?

An active transformation can change the number of rows that pass through it, but a passive transformation can not change the number of rows that pass through it

15) What is the load strategy you followed?

16) SCD types?

17) Explain about MUDE?

MUDE  (Multi-user development environment) is used for multi-user development of the repository. 

Create a shared directory on the network for Multi-user Development (MUD).
Open the RPD to use in MUD. From Tools->Options, setup the MUD directory to point to the above directory.
Define projects within the RPD to allow multiple users to develop within their subject area or Facts.
Save and move the RPD to the shared directory setup in point 1.
When users work in the MUD mode, they open the admin tool and start with
MUD ->Checkout to check out the project they need to work on (not use the File open as you would Usually do).
After completely the development, user checking the changes back to the network and merge the changes.

18) What are the different utilities you have worked on?

19) How many dimensions and facts you have worked on?

Sales Subject area:- 4 dimensions and 1 fact table

20) Explain SCD Type 2 method?

21) tell about the features you have worked on DAC?

22) What is Opaque View?

OPAQUE VIEWS are the tables that are created with join or other query data that contain SELECT query o/p. The opaque views make logical understanding simple for implementation but they are heavy performance constraints. They are only used when there is no other way to get the final solution.