How to join GL - Account Analysis and Financials - AP Expense

Aline Teixeira

The client has a workbook in FDI and uses the subject area “GL - Account Analysis”. It will be necessary to include a column with the Invoice Account Description - this information is in Financials - AP Expense.

After trying to perform the join between the two I got the error below:

(I've also tried redoing it in a simpler way, by setting an invoice, using distribution metrics. Is there a workaround for this?)

Error during query processing (SQLExecDirectW).
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred.
Please have your
service administrator review this error. (HY000)
State: HY000. Code: 43275. [nQSError: 43275] Message returned from OBIS [ecid:ad775d2c-d10b-41df-bde4-59cf8527c081-002585dc,0:1:98:6 ts:2025-03-18T17:47:50.513+00:00].
Please have your
service administrator review this error. (HY000)
State: HY000. Code: 43119. [nQSError: 43119] Query Failed:
Please have your
service administrator review this error. (HY000)
State: HY000. Code: 14025. [nQSError: 14025] No fact table exists at the requested level of detail: [,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,[

1. Dim - GL Journal Details.Journal Header ID
2. , Dim - GL Journal Details.Journal Line Number
3. , Dim - GL Journal Details.Line Description],,,,,,,,,,,,,,,,,,,,,,,Detail,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,Detail,,[
4. Dim - GL Segment4.Segment Name
5. , Dim - GL Segment4.Segment Description
6. , Dim - GL Segment4.Segment Key],,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,[
7. Dim - GL Account.Concatenated Segments],,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,Detail,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,[
8. Dim - AP Expense Distribution Details.Distribution Description],,,,,,,,,,,,,Detail,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,Detail,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,[
9. Dim - Balancing Segment.Balancing Segment Name
10. , Dim - Balancing Segment.Balancing Segment Description
11. , Dim - Balancing Segment.Balancing Segment Key],,,,,Detail,,[
12. Dim - Natural Account.Natural Account Description
13. , Dim - Natural Account.Natural Account Name
14. , Dim - Natural Account.Natural Account Key],,,,,,,,,,,,,,,,,,,Detail,,,,,,,,,,,,,,,,,,,,,,,,,,,,,Detail,,,,,,,,,,,,,,,,,,,,,,,,,,,,Accounting Period,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,[
15. Dim - Custom_DW_FA_Y_CAA_APP_GL__ACCOUNT_ANALYSIS.Invoice Date
16. , Dim - Custom_DW_FA_Y_CAA_APP_GL__ACCOUNT_ANALYSIS.Invoice Number],,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,].
    Please have your
    service administrator review this error. (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 - AP Expense Distribution Details.Distribution Description, Dim - Balancing Segment.Balancing Segment Description, Dim - Balancing Segment.Balancing Segment Key, Dim - Balancing Segment.Balancing Segment Name, Dim - Cost Center.Cost Center Code, Dim - Cost Center.Cost Center Value Set Code, Dim - Created By.Login Name, Dim - Custom_DW_FA_Y_CAA_APP_GL__ACCOUNT_ANALYSIS.Invoice Date, Dim - Custom_DW_FA_Y_CAA_APP_GL__ACCOUNT_ANALYSIS.Invoice Number, Dim - GL Account.Concatenated Segments, Dim - GL Accounting Date Calendar.GL Accounting Calendar Name, Dim - GL Accounting Date Calendar.GL Accounting Period Sort Key, Dim - GL Journal Category.Journal Category Code, Dim - GL Journal Details.Journal Header ID, Dim - GL Journal Details.Journal Line Number, Dim - GL Journal Details.Line Description, Dim - GL Journal Source.Journal Source Code, Dim - GL Posted Date Fiscal Calendar.Adjustment Period Flag, Dim - GL Posted Date Fiscal Calendar.Posted Date, Dim - GL Posted Date Fiscal Calendar.Posted Fiscal Calendar Name, Dim - GL Posted Status.Posted Status Code, Dim - GL Segment4.Segment Description, Dim - GL Segment4.Segment Key, Dim - GL Segment4.Segment Name, Dim - Ledger.Ledger Key, Dim - Natural Account.Natural Account Description, Dim - Natural Account.Natural Account Key, Dim - Natural Account.Natural Account Name, Dim - Supplier.Party Key, case when Dim - GL Journal Source.Journal Source Name = 'Payables' then Dim - Supplier.Supplier Name || ' | ' || Dim - Custom_DW_FA_Y_CAA_APP_GL__ACCOUNT_ANALYSIS.Invoice Number || ' | ' || cast(Dim - Custom_DW_FA_Y_CAA_APP_GL__ACCOUNT_ANALYSIS.Invoice Date as CHARACTER ( 30 )) else Dim - GL Journal Details.Line Description end
    Please have your
    service administrator review this error. (HY000)
    SQL Issued: SET VARIABLE ENABLE_DIMENSIONALITY = 1;
    
    

Jamie Anderson-Oracle

Welcome to the Oracle Analytics Community, @Aline Teixeira !

Theoretically, something to keep in mind is that in order for data to be included in the same analysis (like adding an Invoice Account Description column to the GL detail), it must come from the same Subject Area.

To address this, Fusion Data Intelligence has a feature called Configurable Account Analysis. This is an administrative step that essentially combines measures/attributes from multiple subject areas that may be referenced together often into what is essentially a new custom subject area . From there, workbook authors can use this new custom subject area to build their report.

As I understand it (this is where I'll lean on my more technical colleagues for potential correction) - if every single measure & attribute offered out-of-the-box in FDI were all included and joined within the same subject area, you'd likely encounter significant latency issues in generating your analysis since each measure and attribute is trying to relate itself to each other measure and attribute (If you try to do this in OTBI in the Fusion ERP Application - you'll experience this). That is why the feature has been set up in this manner.

Jamie Anderson-Oracle

Adding this note as well since you mentioned that you're currently using the GL Account Analysis feature:

https://community.oracle.com/products/oracleanalytics/discussion/comment/63574#Comment_63574