Accurate total on hand inventory value from analysis in OBIEE

Question

Did I pick wrong dimensions in OBIEE to compute on hand inventory value?  OBIEE version is 11.1.1.9.0.

On-hand-Inventory values computed in OBIEE is approximately 8% less than its true value.

I multiplied the following 2 dimensions and it produced an approximately 8% reduced on-hand-Inventory value:

• "Unit Cost" calculated dimension from "Costing - Inventory Value Real Time" subject area.

• "On-Hand Quantity"calculated dimension from "Inventory - Inventory Balance Real Time" subject area.

I tried with other inventory-value related dimensions listed below but they did not produce accurate values.

Thank you.

Jonathan Chan gmg · Answer

Thank you for the detailed explanation and pointing me to OBIA. I will post my query over there.

Wish you a Happy Seasons in advance.

Best

Gianni Ceresa · Answer

Jonathan Chan gmg wrote:Do you know how to setup the analysis so it shows the most recent value for both "Costed Onhand Amount" and "Costed Onhand Quantity" attributes?

I can't, because of what I said earlier: OBIEE is empty when installed. I can start a new OBIEE right now and I will not find a single thing of what you talk about, nothing will be there.

The question is if you are using OBIA (which is a packaged OBIEE, ODI, datawarehouse model and few other tools, coming with a pre-built RPD and a ton of analysis and dashboard). If yes, there is a forum for OBIA where people have the "same" product as you, with the content, and therefore can provide answers making sense. If you have another product which gave you OBIEE as front-end, the question need to be asked in the forum of that product.

If your OBIEE is fully custom made by your own company, there is nobody else than your company able to help you as we don't have any of your content. Names don't mean anything, there isn't a universal meaning of what "Costed Onhand Amount" is and where it comes from.

Jonathan Chan gmg · Answer

Thanks for reaching out Gianni.

Meanwhile, I just came across a pre-built analysis "Inventory - Onhand Balance" (located in "Shared Folder /Supply Chain Management/Warehouse Operations/Inventory") and it provides inventory on-hand value for all items.  However when I filter the report to show the most recent record for each items, both "Costed Onhand Amount" and "Costed Onhand Quantity" attributes output an aggregated value instead of the value for the most recent records.  Do you know how to setup the analysis so it shows the most recent value for both "Costed Onhand Amount" and "Costed Onhand Quantity" attributes?

For your reference, the filtered I have used are:

1) "Start Date" dimension - MAX("- Inventory Valuation Details - Onhand"."As of Date")

2) Aggregate Rule (Total Row) set to "None" for both "Costed Onhand Amount" dimension and "Costed Onhand Quantity" dimension, per enclosed screenshot.

• I also attempted to set this filter using the following SQL code but it resulted in the below error message:

FILTER("- Inventory Valuation - Onhand"."Costed Onhand Amount" USING (MAX("- Inventory Valuation Details - Onhand"."As of Date")))

Formula syntax is invalid.

[nQSError: 10058] A general error has occurred. Please have your System Administrator look at the log for more details on this error. (HY000)

[nQSError: 43113] Message returned from OBIS. Please have your System Administrator look at the log for more details on this error. (HY000)

[nQSError: 27002] Near <)>: Syntax error Please have your System Administrator look at the log for more details on this error. (HY000)

[nQSError: 26012] . Please have your System Administrator look at the log for more details on this error. (HY000)

SQL Issued: SELECT FILTER("- Inventory Valuation - Onhand"."Costed Onhand Amount" USING (MAX("- Inventory Valuation Details - Onhand"."As of Date") )) FROM "Costing - Inventory Valuation Real Time"

Thank you.