Oracle Transactional Business Intelligence

Home› Oracle Analytics› Oracle Analytics Forums› More Solutions› Oracle Transactional Business Intelligence

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Workforce Performance - Performance Document Eligibility Real time

121
Views
4
Comments
Jagdish Varadraj
Jagdish Varadraj Rank 3 - Community Apprentice
in Oracle Transactional Business Intelligence

Summary

Assignment Level DFF Attribute1 value in Workforce Performance - Performance Document Eligibility Real time

Content

Hi All

How can i bring Assignment Level DFF Attribute1 value in Workforce Performance - Performance Document Eligibility Real time Subject Areas. 

When i try to add one more Subject Area "Wrokformance Management - Assignment Real time" and add the Attribute1 the OTBI errors out. 

Any clue what would be the reason.

Thanks
Jag

Tagged:

Comments

  • Mihaela M
    Mihaela M Rank 3 - Community Apprentice

    Hi Jag. What is that attribute and what is that error?

    Can you share the logical SQL of the analysis?

  • Jagdish Varadraj
    Jagdish Varadraj Rank 3 - Community Apprentice

    Hi Mihaela

    Basically i want a OTBI report which gives me the person name, number, Performance Document Name, Eligibility along with their Assignment Level DFF (ASS_ATTRIBUTE1) for this i have to have two Subject Areas and below is the logical SQL for it but i get error "State: HY000. Code: 14025. [nQSError: 14025] No fact table exists at the requested level of detail: ].

     

    SELECT 0 s_0, "Workforce Management - Worker Assignment Real Time"."Worker Assignment Details"."PER_ASG_DF_BUSINESS_TITLE_" s_1, 
                  "Workforce Performance - Performance Document Eligibility Real Time"."Performance Document Eligibility Details"."Eligibility" s_2, 
            "Workforce Performance - Performance Document Eligibility Real Time"."Performance Document"."Performance Document Period Name" s_3,
            "Workforce Performance - Performance Document Eligibility Real Time"."Worker"."Employee Name" s_5, 
            DESCRIPTOR_IDOF("Workforce Performance - Performance Document Eligibility Real Time"."Performance Document Eligibility Details"."Eligibility") s_7 
    FROM "Workforce Performance - Performance Document Eligibility Real Time"

    Then i managed to write a BI report using the direct tables and i am good now I will share the Sql in the next reply 

  • Jagdish Varadraj
    Jagdish Varadraj Rank 3 - Community Apprentice

    Hi Mihaela

    Basically i want a OTBI report which gives me the person name, number, Performance Document Name, Eligibility along with their Assignment Level DFF (ASS_ATTRIBUTE1) for this i have to have two Subject Areas and below is the logical SQL for it but i get error "State: HY000. Code: 14025. [nQSError: 14025] No fact table exists at the requested level of detail: ].

     

    SELECT 0 s_0, "Workforce Management - Worker Assignment Real Time"."Worker Assignment Details"."PER_ASG_DF_BUSINESS_TITLE_" s_1, 
                  "Workforce Performance - Performance Document Eligibility Real Time"."Performance Document Eligibility Details"."Eligibility" s_2, 
            "Workforce Performance - Performance Document Eligibility Real Time"."Performance Document"."Performance Document Period Name" s_3,
            "Workforce Performance - Performance Document Eligibility Real Time"."Worker"."Employee Name" s_5, 
            DESCRIPTOR_IDOF("Workforce Performance - Performance Document Eligibility Real Time"."Performance Document Eligibility Details"."Eligibility") s_7 
    FROM "Workforce Performance - Performance Document Eligibility Real Time"

    Then i managed to write a BI report using the direct tables and i am good now I will share the Sql in the next reply 

  • Jagdish Varadraj
    Jagdish Varadraj Rank 3 - Community Apprentice
    SELECT  t1.person_number Person_Number,
             t2.full_name Full_Name,
             t9.name Job_Name,
    t11.full_name  Manager_Name,
    t3.ass_attribute1 Business_Title,
             t6.customary_name Perf_Document,
             t8.elig_flag Eligibility,
    t12.segment4 Region
    -- t4.status_code Status_Code 
        FROM per_all_people_f t1,
             per_person_names_f t2,
    per_all_assignments_m t3,
    -- hra_evaluations t4,
    hra_tmpl_periods_b t5,
    hra_tmpl_periods_tl t6,
    ben_elig_obj_f t7,
    ben_elig_rslt_f t8,
    per_jobs_f_tl t9,
    per_assignment_supervisors_f t10,
    per_person_names_f t11,
    per_people_groups t12
       WHERE 1 = 1
         AND t1.person_id             = t2.person_id
         AND t1.person_id             = t3.person_id 
       --  AND t3.assignment_id         = t4.assignment_id
       --  AND t4.template_defn_id      = t5.template_defn_id
         AND t3.assignment_id         = t8.assignment_id
         AND t8.elig_obj_id           = t7.elig_obj_id 
         AND t7.column_name           = t6.tmpl_period_id
         AND t6.tmpl_period_id        = t5.tmpl_period_id       
         AND TRUNC(SYSDATE) BETWEEN t1.effective_start_date AND t1.effective_end_date
         AND TRUNC(SYSDATE) BETWEEN t2.effective_start_date AND t2.effective_end_date
         AND TRUNC(SYSDATE) BETWEEN t3.effective_start_date AND t3.effective_end_date
         AND TRUNC(SYSDATE) BETWEEN t7.effective_start_date AND t7.effective_end_date
         AND TRUNC(SYSDATE) BETWEEN t8.effective_start_date AND t8.effective_end_date
         AND t7.table_name            = 'TEMPLATE_PERIOD'
        -- AND t3.assignment_number     = 'E15406'
         AND t2.name_type             = 'GLOBAL'
         AND t3.assignment_type       = 'E'
        -- AND t6.customary_name        LIKE '2020%'
         AND t3.job_id                = t9.job_id
         AND t3.assignment_id         = t10.assignment_id
         AND t10.manager_id           = t11.person_id
         AND t11.name_type            = 'GLOBAL'
         AND t10.manager_type         = 'LINE_MANAGER'
      --   AND t4.status_code           IN ('COMP','INPROG')
          AND t8.elig_flag             = 'Y'
         AND t6.customary_name        = '2020 Leadership Model (Talent Dashboard Only)'
         AND t3.people_group_id = t12.people_group_id
       -- AND t2.full_name = 'Alkhatib, Anas'
         AND TRUNC(SYSDATE) BETWEEN t9.effective_start_date AND t9.effective_end_date
         AND TRUNC(SYSDATE) BETWEEN t10.effective_start_date AND t10.effective_end_date
         AND TRUNC(SYSDATE) BETWEEN t11.effective_start_date AND t11.effective_end_date
         ORDER BY t1.person_number