Multiple Subject Areas

Virgilio Cintron

Summary

Report fails when adding fields from two different subject areas

Content

I am pulling fields starting from the Workforce Management - Worker Assignment Event Real Time area but once I bring in anything from Workforce Management - Person Real Time it gives me an error. Is there a join or something I need to change in order to get fields from both subject areas at time?

Joel Acha11111

What error do you get? You may have to try creating a union report. 

Virgilio Cintron

Thanks for the help. Here is the error I get:

View Display Error

Odbc driver returned an error (SQLExecDirectW).   Error Details Error Codes: OPR4ONWY:U9IM8TAC:U9IM8TAC:U9IM8TAC:U9IM8TAC:U9IM8TAC:U9IM8TAC:OI2DL65P State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. (HY000) State: HY000. Code: 43113. [nQSError: 43113] Message returned from OBIS. (HY000) State: HY000. Code: 43119. [nQSError: 43119] Query Failed: (HY000) State: HY000. Code: 14025. [nQSError: 14025] No fact table exists at the requested level of detail: [,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,Details,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,[Dim - Assignment Event Details.PER_ASG_DF_BUSINESS_TITLE_],,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,Dim - HR Action Detail,[Dim - HR Action Reason.Action Reason],,,,,,,,,,,,[Dim - Person Names.Middle Names],,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,[Dim - Employee.Person Number, Dim - Employee.Employee Email Address, Dim - Employee.Employee Supervisor Name, Dim - Employee.Employee Hire Date, Dim - Employee.Assignment Category, Dim - Employee.Assignment Status, Dim - Employee.Employee Display Name, Dim - Employee.Email Type Code, Dim - Employee.Email Type, Dim - Employee.Manager E-Mail Address, Dim - Employee.Bargaining Unit, Dim - Employee.Assignment Status Type Identifier, Dim - Employee.Assignment Category Code, Dim - Employee.Bargaining Unit Code],,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,Jobs Detail,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,Employee Location Detail,Details,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, (HY000) State: HY000. Code: 14025. ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,]. (HY000) State: HY000. Code: 14081. [nQSError: 14081] You may be able to evaluate this query if you remove one of the following column references: Dim - Assignment Event Details.PER_ASG_DF_BUSINESS_TITLE_, Dim - Department.Department Name, Dim - Employee.Assignment Category, Dim - Employee.Assignment Category Code, Dim - Employee.Assignment Status, Dim - Employee.Assignment Status Type Identifier, Dim - Employee.Bargaining Unit, Dim - Employee.Bargaining Unit Code, Dim - Employee.Email Type, Dim - Employee.Email Type Code, Dim - Employee.Employee Display Name, Dim - Employee.Employee Email Address, Dim - Employee.Employee Hire Date, Dim - Employee.Employee Supervisor Name, Dim - Employee.Manager E-Mail Address, Dim - Employee.Person Number, Dim - HR Action Reason.Action Reason, Dim - HR Action.Action Code, Dim - Job.Job Key, Dim - Job.Job Name, Dim - Legal Employer.Name, Dim - Person Names.Middle Names, Dim - Worker Location.Worker Location Name (HY000) SQL Issued: SET VARIABLE PREFERRED_CURRENCY='User Preferred Currency 1',PARAM_EFFECTIVE_DATE='4712-01-01';SELECT 0 s_0, "Workforce Management - Person Real Time"."Person Names"."Middle Names" s_1, "Workforce Management - Worker Assignment Event Real Time"."Assignment Event Details"."PER_ASG_DF_BUSINESS_TITLE_" s_2, "Workforce Management - Worker Assignment Event Real Time"."Department"."Department Name" s_3, "Workforce Management - Worker Assignment Event Real Time"."HR Action Reason"."Action Reason" s_4, "Workforce Management - Worker Assignment Event Real Time"."HR Action"."Action Code" s_5, "Workforce Management - Worker Assignment Event Real Time"."Legal Employer"."Name" s_6, "Workforce Management - Worker Assignment Event Real Time"."Location"."Worker Location Name" s_7, "Workforce Management - Worker Assignment Event Real Time"."Worker"."Assignment Category" s_8, "Workforce Management - Worker Assignment Event Real Time"."Worker"."Assignment Status" s_9, "Workforce Management - Worker Assignment Event Real Time"."Worker"."Bargaining Unit" s_10, "Workforce Management - Worker Assignment Event Real Time"."Worker"."Employee Display Name" s_11, "Workforce Management - Worker Assignment Event Real Time"."Worker"."Employee Email Address" s_12, "Workforce Management - Worker Assignment Event Real Time"."Worker"."Manager E-Mail Address" s_13, "Workforce Management - Worker Assignment Event Real Time"."Worker"."Manager Name" s_14, "Workforce Management - Worker Assignment Event Real Time"."Worker"."Person Number" s_15, "Workforce Management - Worker Assignment Real Time"."Job"."Job Name" s_16, "Workforce Management - Worker Assignment Real Time"."Worker"."Email Type" s_17, "Workforce Management - Worker Assignment Real Time"."Worker"."Employee Hire Date" s_18, DESCRIPTOR_IDOF("Workforce Management - Worker Assignment Event Real Time"."Worker"."Assignment Category") s_19, DESCRIPTOR_IDOF("Workforce Management - Worker Assignment Event Real Time"."Worker"."Assignment Status") s_20, DESCRIPTOR_IDOF("Workforce Management - Worker Assignment Event Real Time"."Worker"."Bargaining Unit") s_21, DESCRIPTOR_IDOF("Workforce Management - Worker Assignment Real Time"."Job"."Job Name") s_22, DESCRIPTOR_IDOF("Workforce Management - Worker Assignment Real Time"."Worker"."Email Type") s_23 FROM "Workforce Management - Worker Assignment Event Real Time" WHERE (("HR Action"."Action Code" IN ('HIRE', 'HIRE_ADD_WORK_RELATION', 'REHIRE'))) FETCH FIRST 75001 ROWS ONLY

Joel Acha11111

This error indicates that you’re creating an analysis with multiple measures from more than 1 fact and there are some non confirmed dimensions. 

You will need to modify your repository to add content levels, logical dimensions and hierarchies. 

Mark_Daynes

Looks like OTBI for HCM to me ... so no RPD editing.

Have a look at this brilliant post from Julian Challenger and scroll down to 'commonly raised topics'

https://cloudcustomerconnect.oracle.com/posts/c04a4fe44d

Mark

 

 

1.0.0.20

slgott

Try adding a fact from each dimension of each SA. If you don't actually need the facts, you can hide them in the criteria or exclude them from the results. Additionally, in the Advanced properties, click the "Dimensionality" box and Apply SQL. This may solve the problem. Hope this helps!

Christian Berg -Oracle

Typical OBIA issue - the RPD is just not up to standards.

You can cheat your way around this by either using the check-bix that Stephanie mentioned or by setting the variable ENABLE_DIMENSIONALITY to  1.

Careful though: both approaches might mess with level-based measures (ref 1908906.1) so use wisely.

siddharth Dang-147675

I would recommend creating a union report from both the subject areas. Else if you know the back-end tables and the report output is a table you can also create a Datamodel and create a BI publisher report as well.

 

Regards,

Siddharth Dang

Monique

Putting this out there in case it helps save somebody the frustration and heartache I experienced over the last day...I was reporting across two subject areas:  Workforce Management - Worker Assignment Event Real Time & Workforce Management - Person Real Time

Dropped in facts (measures), changed the dimensionality (mentioned above), everything worked fine in my report as I expected.  So with that all happy and in place, I started building "Case When" statements to audit and ensure that our work schedules were assigned appropriately.  This is where the wheels came off.  Any time I tried to write a Case When Query using work schedule (in Person Real Time) and contrasting with Division/Department (in Worker Assignment Event RT) the report would take my SQL but would error when running (same as OP above)..  

After fruitless searches (one of which led me here), I got the idea to set up my Case When statement to FIRST run through two facts  from the two subject areas (see below).  

CASE WHEN "Worker Assignment"."Head Count" IS NOT NULL AND "Workforce Management - Person Real Time"."Person"."Person Count" IS NOT NULL

I then followed it up with more "When" statements to form my real query (i.e. ensuring people in x schedule are not from x department).  IT WORKED.  Hoping it's stable and will work for one of you if needed.  

Srinivas Pendyala-Oracle

add the measure columns from both subject areas to avoid the error.

Monique

@Srinivas Pendyala-Oracle if you were replying to me...my report did have measures from both subject areas. The report with both subject areas would run correctly, until I tried writing "Case when" logic that pulled from both subject areas. If the logic included variables from both subject areas, it would generate an error.

To fix it, I incorporated measures from both subject areas in the Case When logic I was writing. While it was unnecessary from a pure logic perspective, it was essential to get the report to run correctly.