DIFFERENCE BETWEEN ROUTER AND FILTER

Router	Filter
Router transformation divides the incoming records into multiple groups based on some condition. Such groups can be mutually inclusive (Different groups may contain same record)	Filter transformation restricts or blocks the incoming record set based on one given condition.
Router transformation itself does not block any record. If a certain record does not match any of the routing conditions, the record is routed to default group	Filter transformation does not have a default group. If one record does not match filter condition, the record is blocked
Router acts like CASE.. WHEN statement in SQL (Or Switch().. Case statement in C)	Filter acts like WHERE condition is SQL.

DIFFERENCES BETWEEN CONNECTED AND UNCONNECTED LOOKUP

The differences are illustrated in the below table

Connected Lookup	Unconnected Lookup
Connected lookup participates in dataflow and receives input directly from the pipeline	Unconnected lookup receives input values from the result of a LKP: expression in another transformation
Connected lookup can use both dynamic and static cache	Unconnected Lookup cache can NOT be dynamic
Connected lookup can return more than one column value ( output port )	Unconnected Lookup can return only one column value i.e. output port
Connected lookup caches all lookup columns	Unconnected lookup caches only the lookup output ports in the lookup conditions and the return port
Supports user-defined default values (i.e. value to return when lookup conditions are not satisfied)	Does not support user defined default values

HOW TO IDENTIFY THE OBIEE REPORTS WHICH ARE NOT USED FROM LONG TIME

How to create catalog report:
1.First open File-> Open Catalog 

2.Then enter the credentials and click on OK and then again OK on the pop up that comes.

 3.Expand Catalog Root and select Shared Folder
 4.Then in the toolbar expand Tools -> Create Report and this appears.

 5.Select ‘Select Type to report on’ as ‘Analysis’ and in the ‘Available Columns’ list select ‘Name’, ‘Path’ and bring them to ‘Columns in the Report’ sectionCheck the Distinct option as well.Then Click Ok.

 6.In the Report Preview add the Path to “Save Report to” to store the Excel and select Excel Format checkbox and then click on OK.

How to create report in Answers for used reports:
 1). Login to production BI .

  2). Click New->Analysis and select Usage Tracking Subject area
  3). In Usage Tracking Subject area, expand Topic folder and select Query Type and Webcat Path columns

  4). Add a valued ‘Report’ to the Query Type column filter and click OK

  5). Add ‘%/users/%’ by selecting Operator as ‘is not LIKE (pattern match)’ for Webcat Path column and click OK

  6). Click on Results Tab

 7). After getting the report click on  icon and select Excel->Excel 2003+ to download the report to excel Below Highlighted Report got executed, as a result it has entry in S_NQ_ACCT table thus showing in the usage tracking report.  There are other reports in the same path but they never got executed.

ASCP QUERY TO FIND SAFETY STOCK QUANTITY FOR AN ITEM AND ORGANIZATION

SELECT DISTINCT C.name                                      ORGANIZATION,
                D.organization_code,
                B.segment1                                  ITEM,
                ( safety_stock_quantity / conversion_rate )
                SAFETY_STOCK_QUANTITY,
                A.last_update_date,
                SQ_UOM.from_uom_code                        UOM
FROM   mtl_safety_stocks A,
       mtl_system_items_b B,
       hr_all_organization_units C,
       mtl_parameters D,
       (SELECT Substr (from_uom_code, 0, 3)    FROM_UOM_CODE,
               Substr (to_uom_code, 0, 3)      TO_UOM_CODE,
               inventory_item_id,
               organization_id,
               Substr (primary_uom_code, 0, 3) PRIMARY_UOM_CODE,
               conversion_rate
        FROM   (SELECT mtl_uom_class_conversions.to_uom_code     FROM_UOM_CODE,
                       mtl_uom_class_conversions.from_uom_code   TO_UOM_CODE,
                       mtl_system_items_b.inventory_item_id
                       INVENTORY_ITEM_ID,
                       mtl_system_items_b.organization_id        ORGANIZATION_ID
                       ,
mtl_system_items_b.primary_uom_code       PRIMARY_UOM_CODE,
mtl_uom_class_conversions.conversion_rate CONVERSION_RATE
FROM   apps.mtl_system_items_b MTL_SYSTEM_ITEMS_B,
apps.mtl_uom_class_conversions MTL_UOM_CLASS_CONVERSIONS
WHERE  ( 1 = 1 )
AND ( mtl_system_items_b.inventory_item_id =
          mtl_uom_class_conversions.inventory_item_id )
UNION ALL
SELECT mtl_units_of_measure.uom_code           FROM_UOM_CODE,
mtl_system_items_b.primary_uom_code     TO_UOM_CODE,
mtl_system_items_b.inventory_item_id    INVENTORY_ITEM_ID,
mtl_system_items_b.organization_id      ORGANIZATION_ID,
mtl_system_items_b.primary_uom_code     PRIMARY_UOM_CODE,
1 / mtl_uom_conversions.conversion_rate CONVERSION_RATE
FROM   apps.mtl_system_items_b MTL_SYSTEM_ITEMS_B,
apps.mtl_uom_conversions MTL_UOM_CONVERSIONS,
apps.mtl_units_of_measure MTL_UNITS_OF_MEASURE
WHERE  ( 1 = 1 )
AND ( mtl_uom_conversions.uom_class =
      mtl_units_of_measure.uom_class )
AND ( mtl_system_items_b.primary_uom_code =
    mtl_uom_conversions.uom_code )
AND ( mtl_units_of_measure.base_uom_flag = 'Y' )
AND ( mtl_uom_conversions.inventory_item_id = 0 ))) SQ_UOM
WHERE  A.inventory_item_id = B.inventory_item_id
       AND A.organization_id = B.organization_id
       AND B.organization_id = C.organization_id
       AND A.inventory_item_id = SQ_UOM.inventory_item_id
       AND A.organization_id = SQ_UOM.organization_id
       AND B.primary_uom_code = SQ_UOM.to_uom_code
       AND SQ_UOM.from_uom_code = 'GAL'
       AND C.organization_id = D.organization_id
       AND B.segment1 IN ( 'ENTER THE ITEM NAME  HERE' )
       AND D.organization_code IN ( 'ENTER THE ORG CODE HERE' )
ORDER  BY 1,
          2 DESC; 

DAC INTERVIEW QUESTIONS

1. Explain the DAC Flow?

2. How do you setup depth in DAC?

3. How do you make Temporary mapping run before the Fact Mapping?

4. How to make a mapping to run always in full mode?

5. what is refresh date and prune days explain?

6. what is micro ETL?

7. How many execution plans can be run from DAC?
Answer. DAC can handle multiple execution plans as long as the source and target tables are not running simentaniously in Execution plans.

8. What are the performance tuning techniques used in DAC?

9. What is the DAC version you have used.

OBIEE INTERVIEW QUESTIONS

1. OBIEE Fact tables do not have common dimension as shown below.

Answer can be found @ http://www.kpipartners.com/blog/bid/83120/What-To-Do-When-OBIEE-Fact-Tables-Do-Not-Join-to-All-Dimension-Tables

2. how to filter OBIEE report using prompt values like 1-10,20-30,30-40,>30 etc...,
Answer:

step1: create variable prompt as shown below

Step2: create a  case statement filter as below and pass the presentation variable

case when  cast("Batch"."Batch Number" as int)<10  then '1 - 10' when cast("Batch"."Batch Number" as int)<20  then '11 - 20' else '>20' end is equal /is in '@{Dinesh}'

3..We have text prompt (BUDGET %) , where we enter % values like (30,40 etc) and the report should get filtered based the values entered in the prompt? The report is having Budget % as a metric and there are multiple reports where we are using this metric. How do you filter as per the text prompt to all the reports in the dashboard?
Ans. We have create BUDGET % metric in RPD using session variable as below

          BUDGET%= (BUDGET*100)/(Session_Variable)

        Now at the report level we can use a request variable to override the session variable and this will be applied to all the reports in the Dashboard.

Note:Request variable is used to override session variable and it is defined at report prompt level
         The request variable name should be same as the session variable.


4. what happens if 2 or more people checkout the MUDE RPD.
Ans, In this case the first person should do refresh subset before checking in his changes.

5.What is the difference between Parent child hierarchy and level based Hierarchy?
Ans @ https://www.rittmanmead.com/blog/2010/08/oracle-bi-ee-11g-parent-child-hierarchies-differing-aggregations/
       @ https://www.tutorialspoint.com/obiee/obiee_dimension_hierarchies.htm

6. How do you display multiple measures in stacked bar chart?
Ans. For this we  need to create union report for each metric.

7. How do you create a MTD metric and the prompt should only apply for the MTD metric?
Ans. In this case also we need to create a union report and apply filter condition for the MTD metric only, so that it will alone get filtered.

8. What is the complex report which you have created in  your career?
Ans. we can explain about stacked bar chart scenario, or anything else.

9. What is ACT As, impersonation in OBIEE?
Ans. @ https://docs.oracle.com/middleware/12212/biee/BIESC/GUID-59844BCC-0EB0-4FDE-8011-75E7C8AEA82A.htm#BIESC1617

10. What is Usage tracking in OBIEE?
Ans. @ http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/bi/bi1116/ut/ut.html

11. What are the types of security in OBIEE?
Ans. LDAP and External Authentication.

12. How do you implement LDAP security in OBIEE?
Ans. @http://www.redstk.com/welcome-to-obiee12c-configuring-external-ldap-authentication-part-1/

13.What are the Time series functions in OBIEE 11g and 12C?
Ans. @ http://obieetutorialguide.blogspot.in/2012/03/time-series-functions-obiee.html

           Forecast function is introduced in OBIEE 12C, Please find more information @ http://obieeil.blogspot.in/2016/01/obiee-12c-advanced-analytic-part-3.html

14.What are the different variables in OBIEE?

15. What is request variable?

16. What are the different types of OBIEE cache management?

17. What are the important configuration files in OBIEE?

18. What is meant by level based measure?

19. Why should we have subject areas to Model RPD when we have Direct Database request?

         

QLIK VIEW OBJECT PROPERTIES

value showing frequency in Sheet objects:

Right click on the sheet object and select properties.
Go to General Tab and Check the Show Frequency box.

The number of times a value is repeating will be seen in the list box.
We can also display the value percentage.

Changing the sort order:

Right click on the sheet object and select properties.
Check the Sort Tab and see various options available.

Changing the Number format:

Right click on the sheet object and select properties.
Check the Number Tab and see various options available.

Aligning the values:

Right click on the sheet object and select properties.
Check the Presentation Tab and see various options available.

ASCP QUERY TO FETCH LATEST PLAN AND COLLECTION RUN DATES

Query to fetch Plan Latest run date:

SELECT MAX(ACTUAL_COMPLETION_DATE),ARGUMENT1 INSTANCE_CODE,ARGUMENT2 PLAN_ID
FROM FND_CONCURRENT_REQUESTS A, FND_CONCURRENT_PROGRAMS_TL B
WHERE A.CONCURRENT_PROGRAM_ID = B.CONCURRENT_PROGRAM_ID
AND B.USER_CONCURRENT_PROGRAM_NAME = 'LAUNCH SUPPLY CHAIN PLANNING PROCESS'
--AND ARGUMENT1 = '' --INTANCE_CODE AND ARGUMENT2='' -- PLAN_ID
AND PHASE_CODE='C' AND STATUS_CODE='C'
GROUP BY ARGUMENT1,ARGUMENT2
;

This has to be joined with ebs table FND_APPS_SYSTEM.NAME to get exact instance and plan

-------------------------------------------------------------------------------------------------------------------------------------------------------
Query to fetch Collection Latest run date:

SELECT MAX(ACTUAL_COMPLETION_DATE),C.INSTANCE_CODE
FROM FND_CONCURRENT_REQUESTS A, FND_CONCURRENT_PROGRAMS_TL B,MSC_APPS_INSTANCES C
WHERE A.CONCURRENT_PROGRAM_ID = B.CONCURRENT_PROGRAM_ID
AND A.ARGUMENT1=TO_CHAR(C.INSTANCE_ID)
AND B.USER_CONCURRENT_PROGRAM_NAME = 'PLANNING DATA PULL'
--AND ARGUMENT1 = '' --INTANCE_ID NO PLAN_ID ATTRIBUTE FOR THIS CONCURRENT PROGRAM
AND PHASE_CODE='C' AND STATUS_CODE='C'
GROUP BY C.INSTANCE_CODE
;

SELECT MAX(ACTUAL_COMPLETION_DATE),C.INSTANCE_CODE
FROM FND_CONCURRENT_REQUESTS A, FND_CONCURRENT_PROGRAMS_TL B,MSC_APPS_INSTANCES C
WHERE A.CONCURRENT_PROGRAM_ID = B.CONCURRENT_PROGRAM_ID
AND A.ARGUMENT1=TO_CHAR(C.INSTANCE_ID)
AND B.USER_CONCURRENT_PROGRAM_NAME = 'PLANNING ODS LOAD'
--AND ARGUMENT1 = '' --INTANCE_ID NO PLAN_ID ATTRIBUTE FOR THIS CONCURRENT PROGRAM
AND PHASE_CODE='C' AND STATUS_CODE='C'
GROUP BY C.INSTANCE_CODE
;

This has to be joined with ebs table FND_APPS_SYSTEM.NAME to get exact instance and plan

WHAT ARE LINKED OBJECTS IN QLIK VIEW

If you want several objects to have the same layout properties, you can use linked objects. These are objects that share all properties with the exception of size, position and display state (minimized, normal or maximized). When you change the properties of one object the change is immediately reflected in the other linked objects. Linked objects can reside on the same sheet or on
different sheets.

To create a linked object:

1 Right-click a sheet object and select Copy to Clipboard - Object.

2 Right-click somewhere on the sheet (or on a different sheet) and select Paste Sheet Object as Link.

QLIK VIEW SHEETS

sheet can be considered the most basic component in QlikView. A document usually contains several sheets, which is useful when one wants to achieve a more structured layout. Any sheet object can be put on any sheet. The sheets will, however, still be logically connected, i.e. a selection made on
one sheet will affect all sheet objects on all other sheets.

Each sheet has a tab attached to it. Containing the name of the sheet, the tab helps you find the sheet you are looking for. By clicking on a tab, you activate the sheet attached to it. You recognize an active tab from the bold text.

Adding a sheet:

1. Choose Add Sheet from the Layout menu. A new sheet appears.
2. Click somewhere on the empty sheet with the right mouse button and choose Properties from the menu that appears. The Sheet Properties dialog will now appear.

Moving a sheet:

 Click on the tab of your newly created sheet withthe right mouse button. From the float menu that
opens, choose Promote Sheet it will move one sheet forward, click  demote it will move one sheet backward.

Adding new sheet objects:

If you right-click somewhere on the sheet , then select New Sheet Object, you see this list
of all the sheet objects that can be used in QlikView.

Moving a sheet object:

To move a sheet object we can use mouse or CTRL+Arrow or CTRL+SHIFT+Arrow.
Also we can undo our changes by using CTRL+Z.

QLIK VIEW LOCKING AND UNLOCKING SELECTIONS

The logic of QlikView by default replaces a previous selection with the new selection
if the previous selection is in conflict with the new selection.

To prevent this, selections may be locked. Locked cells are blue. A selection in conflict
with a locked selection will not be performed.

1. Choose Lock from the Selections menu or from the toolbar. This will lock all selections, preventing them from being cleared by mistake.
2. Select an excluded value in another list box and note that it is not possible.
3. To unlock all selections, choose Unlock from the Selections menu or from the toolbar.
4. It is also possible to lock fields individually by right clicking on the selection value.

INTRODUCTION TO QLIK VIEW

QlikView is a software that enables all kinds of users from beginners to experts to
retrieve and assimilate data easily from any source: databases like SQL Server or
Oracle as well as Excel, XML or text files. Enterprise applications such as SAP may
also be used as data source for a QlikView analysis.

QlikView offers a wide variety of graphs, charts and tables in different formats to
present your data the way you want.

Creating the interface is very easy and does not require help from the IT department. Any graphic
or table can be printed or exported to other programs for further processing.

Different types of Qlik View

1. QlikView standalone is freeware and can only be used in one system.
2. QlikView Server and QlikView Publisher that can be used for centralized management of          QlikView applications, for automated updates and for distribution of documents to several users.

OBIEE TO_DATETIME function

TO_DateTime is secret function in OBIEE which is rarely known.

It is similar to TO_DATE function in SQL.

This function is not visible in OBIEE Answers, but you can use this.

Syntax :

TO_DATETIME('string1', 'DateTime_formatting_string')

Example:

case when "Test Result Dates/Times"."Test Begin Date"=to_datetime(CAST('1/1/1901' AS CHAR),'MM/DD/YYYY') THEN NULL ELSE "Test Result Dates/Times"."Test Begin Date"  END

HOW TO SET CUSTOM DATE FORMAT IN SQL USING NLS DATE FORMAT

We can execute the below script for displaying custom date format in sql. We can change the date format as per our requirement by changing the format string highlighted in bold.

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

IF you want to set it by default while you login, you can write a trigger as below.

CREATE OR REPLACE TRIGGER DATE_TRIG
AFTER LOGON ON DATABASE
BEGIN
EXECUTE IMMEDIATE 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
END;

HOW TO SET UP CUSTOM DATETIME FORMAT IN SQL DEVELOPER

1. GO TO Menu -->Tools --> Preferences
2. In Preferences dialog --> select Database --> NLS from the left panel.
3. In NLS Parameter list --> set DD-MON-YY HH24:MI:SS in Date Format field.                                                                                                                                                                                                                 
4. 

HOW TO DISPLAY SPACE BETWEEN SYMBOL(LIKE $) AND THE METRIC VALUE IN OBIEE

Use  CSS Style: word-spacing: 5em in Column Format Values Properties to 

display space between symbol and Number.

Also set the Data Format to Custom and  Custom Nemeric Format to "$ #.#0" 

For example if we want to display space between $ and Amount we can use this

and this will display value as "$ 5000" .

This can be used to display space betwen any symbols.

EBS QUERY TO FIND PARENT ITEM AND ITS RELATED SKEW ITEMS

The below query will return the skew items(Packaging Item) for a parent item.
Join any of your tables to mtl_system_items_b and it will return the skew items.

SELECT MSIB.SEGMENT1  PARENT_ITEM,

       MSIB1.SEGMENT1 SKEW_ITEM

FROM   MTL_SYSTEM_ITEMS_B MSIB,

       MTL_CATEGORIES_B_KFV B,

       MTL_CATEGORY_SETS_TL C,

       MTL_ITEM_CATEGORIES D,

       MTL_SYSTEM_ITEMS_B MSIB1

WHERE  1 = 1

       AND B.CONCATENATED_SEGMENTS = MSIB.SEGMENT1

       AND C.CATEGORY_SET_NAME LIKE  '%PARENT CODE'

       AND C.LANGUAGE = 'US'

       AND D.CATEGORY_ID = B.CATEGORY_ID

       AND D.CATEGORY_SET_ID = C.CATEGORY_SET_ID

       AND D.ORGANIZATION_ID = MSIB.ORGANIZATION_ID

       AND D.INVENTORY_ITEM_ID <> MSIB.INVENTORY_ITEM_ID

       AND D.INVENTORY_ITEM_ID = MSIB1.INVENTORY_ITEM_ID

       AND D.ORGANIZATION_ID = MSIB1.ORGANIZATION_ID;