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

Missing data when multiple companies being selected

Received Response
2
Views
4
Comments
Bouman, Arie
Bouman, Arie Rank 3 - Community Apprentice
edited Aug 13, 2024 5:19PM in Oracle Analytics Cloud and Server

Hi,

I'm facing following problem. With below mentioned data model I get data when selecting company 00001, I get data when selecting company 00004, I get data when selecting multiple items from which I'm sure they should return data for both companies 00001 and 00004 but I only get data for company 00001 when I don't select anything specific although I expect data for both company 00001 and 00004.

Can somebody give me a clue where to look for please?

SELECT
   0 s_0,
   "ERP Procurement"."Companies"."CompanyNr" CompanyNr,
   "ERP Procurement"."Contract Attributes"."ContractNr" ContractNr,
   "ERP Procurement"."Contract Attributes"."DateEnd" DateEnd,
   "ERP Procurement"."Contract Attributes"."FreightHandlingCode" FreightHandlingCode,
   "ERP Procurement"."Products"."ItemNumber" ItemNumber,
   "ERP Procurement"."Products"."PriceUoM" PriceUoM,
   "ERP Procurement"."Products"."SubGroup" SubGroup,
   CAST(NULL AS DATE) NullAsDate,
   DESCRIPTOR_IDOF("ERP Procurement"."Products"."SubGroup") IDSubgroup,
   "ERP Procurement"."Contract Facts"."AvgDayPrice" AvgDayPrice,
   "ERP Procurement"."Contract Facts"."QuantityAvailable" QuantityAvailable,
   "ERP Procurement"."Contract Facts"."QuantityAvailable"*("ERP Procurement"."Contract Facts"."AvgDayPrice"-case "ERP Procurement"."Products"."PriceUoM"
when 'KG' then "ERP Procurement"."Contract Facts"."ContractPrice"
when 'LT' then "ERP Procurement"."Contract Facts"."ContractPrice"
when 'EA' then "ERP Procurement"."Contract Facts"."ContractPrice"
else "ERP Procurement"."Contract Facts"."ContractPrice"/1000 end
) QuantityAvailableXCp_DP,
"ERP Procurement"."Suppliers"."SupplierNr" SupplierNr,

   case "ERP Procurement"."Products"."PriceUoM"
when 'KG' then "ERP Procurement"."Contract Facts"."ContractPrice"
when 'LT' then "ERP Procurement"."Contract Facts"."ContractPrice"
when 'EA' then "ERP Procurement"."Contract Facts"."ContractPrice"
else "ERP Procurement"."Contract Facts"."ContractPrice"/1000 end ContractPrice
FROM "ERP Procurement"
WHERE
"Contract Facts"."QuantityAvailable" > 0 AND "Companies"."CompanyNr" IN ('00001', '00004') AND "Products"."StockingTypeCode" = 'P' AND "Products"."GLClassCode" IN ('100', '400') AND "Products"."SubGroupCode" NOT IN ('121', '171', '132', '136', '130', '165')

ORDER BY 1, 2 ASC NULLS LAST, 8 ASC NULLS LAST, 6 ASC NULLS LAST, 7 ASC NULLS LAST, 3 ASC NULLS LAST, 4 ASC NULLS LAST, 5 ASC NULLS LAST
FETCH FIRST 65001 ROWS ONLY

Thanking in advance,

Regards,

Arie

Answers

  • Joel
    Joel Rank 8 - Analytics Strategist

    Is there row-level security on your underlying database? Have you checked the SQL that OBIEE generates? Have you run that directly on the database as the user defined in the connection pool?

  • Gianni Ceresa
    Gianni Ceresa Mod

    You posted the LSQL (logical SQL) of your analysis, this doesn't tell you much as you can't test it directly in your database.

    Find the physical query that OBIEE generate and look at that one, the answer of what happen is there (and it also cover the point raised by Joel, if there is some row-level security the physical query is supposed to show the filters).

  • Bouman, Arie
    Bouman, Arie Rank 3 - Community Apprentice

    Dear Joel and Gianni,

    Thank you for your quick reply. I will have a look and keep you informed.

  • Bouman, Arie
    Bouman, Arie Rank 3 - Community Apprentice

    Appeared to be an error in the datamodel caused by unknown reasons. Problem solved by rebuilding the datamodel and not using the logical SQL but creating queries directly to the database.