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?