Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

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

Error using column selector

Received Response
101
Views
12
Comments
aPsikus
aPsikus Rank 6 - Analytics Lead
edited Aug 13, 2024 5:30PM in Oracle Analytics Cloud and Server

Hi,

On the report with column selector we are receiving error: State: HY000. Code: 15018. [nQSError: 15018] Incorrectly defined logical table source (for fact table Fact Workforce) does not contain mapping for [Dim Location.Region]. (HY000)

Report contains two column selectors. Error is displayed when on first we select: Work Address - Region and on second we select Work Address - Country. Both are from dim_location.

I could imagine that maybe Fact table is not aggregated to one of the selected levels.

Based on the error looks like something is not correctly set in the RPD.

But interesting thing is that if we select column selectors opposite: on first Work Address - Country and on second Work Address - Region, report is displayed correctly.

What can causing error? What setting do I miss?

«1

Answers

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    Hi,

    incorrect logical table source likely means that you have multiple LTS for your dimension to fact relationship for pre-aggregation.

    My educated guess (not having sight of your rpd) would be that if you look at the columns you have used in the report then some of them are unmapped for a level / LTS.

  • aPsikus
    aPsikus Rank 6 - Analytics Lead

    Hi Robert,

    Could it be for example that Hierarchy for Location is defined that way that Country and Region are on the same level?

    Honestly saying, even if we have different LTS used, measures are defined from single LTS. But to be sure, is there any fast way to check such duplicates?

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    Hi APsikus,

    I am not saying duplicates I am saying that you have unmapped columns at a level of detail.

    If you click on the column definitions then if (say) you have 2 (say) tables in the LTS then you will see 2 rows also for every physical mapping, assuming the columns you are looking at can be used with the corresponding fact / fact agg tables.

  • aPsikus
    aPsikus Rank 6 - Analytics Lead

    Do you mean here?

    unmapped.JPG

    I have checked all of them. They are hierarchies, but any of them contains Region in levels or details.

    Region is only in Location Hierarchy, and in Country level. And I'm wondering if this can be an issue.

    location_hier.JPG

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    Yes, if you have the unmapped columns in your report at the level of detail that they are unmapped then it will error.

    Check also that you content levels are set correctly on the FACT / Dimension LTS.

  • aPsikus
    aPsikus Rank 6 - Analytics Lead 
    Robert Angel wrote:Yes, if you have the unmapped columns in your report at the level of detail that they are unmapped then it will error.Check also that you content levels are set correctly on the FACT / Dimension LTS.

    But once again Robert,

    why it works if I will choose Work Address County on first and Work Address Region on second column selector, but do not work in opposite.

    If you assumption would be right, it should not work at all. Otherwise not logical at all.

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    No, I would not expect the order of columns to have that effect, have you put diagnostics up to full and compared what is logged in the two cases?

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    Do you also get the same behavior using a straight tabular view, does the order of the columns work / yield the error then?

  • Sagar Tippe
    Sagar Tippe Rank 4 - Community Specialist

    Hi,

    1. Try to create individual reports based on those 2 columns. If working fine, follow step 2.
    2. Create another report which will have both these dimensional columns and your measures. From this report, you will get to know that whether granularity of all these columns returns data of not.
    3. If this returns data, check the query log to compare it with your previously created report.
    4. If not, the issue is surely at RPD level. Fix that. or else paste the query log / error here with the data model in short..

    Awaiting your trial and error results.. Good luck..

    Thnx,

    Sagar Tippe

  • aPsikus
    aPsikus Rank 6 - Analytics Lead

    @Sagar Tippe,

    Here are the findings.

    I have recreated the report by removing column selectors.

    Report contains 6 measures and is created as pivot table. On first column there was Total applied.

    First I have tried to receive error.

    When I have changed order of dimensions (country with region) I get error.

    When I have turned off totals on region, which was first column, there was no error.

    So this was first finding.

    I have decided to check if all measures are the problem.

    Checking one by one, I have noticed that only three of them are giving error.

    Measure giving error

    Name - "HR Event Facts"."Voluntary Turnover Rate - Current Period"

    Formula - CASE  WHEN   "WORKFORCE"."Fact Workforce"."12 Months Avg ActiveWorkforce" <> 0 THEN 100.0d * "WORKFORCE"."Fact Workforce Events"."# of Vol Terms 12 Months Rolling"/ "WORKFORCE"."Fact Workforce"."12 Months Avg ActiveWorkforce" END

    Measure itself is placed in BMM, in Logical table which do not have any LTS.

    Does it mean that I need to check in details components of this formula to find the one which can giving error?