Oracle BI Apps - Support for Unit of Measure conversion.

Authored by : Manjunath Gangadhar

Overview

UOM or Unit Of Measure is the method of expressing the quantity of an item so that it can be tracked and accounted for in a system.

A data warehouse that stores and analyzes Items or Products must have a UOM standard defined.

This post describes OBIA requirements, design and implementation of UOM

What is UOM Conversion

Let’s start with an example:

Let’s say on 15th Jan, 2013 100 Ea of Mont Blanc pens were sold. Here Ea is Each which is a Unit of measure that says 10 individual Mont Blanc pens were sold.

On 25th Jan, 2013 10 DZN Mont Blanc Pens were sold

In this example Ea(Each) and DZN(Dozen) are the UOMs for the Item Mon Blanc Pens

Let’s say the user wants to see a report that provides the Total Number Of Mont Blanc Pens sold in Jan 2013. If we just sum the two numbers (100 + 10) and show 110 Total Pens sold, that would be wrong.

Instead the Total should be 220 Ea, since 1 DZN = 12 Ea

This conversion where 1DZN = 12 Ea is defined as the UOM Conversion.

Why is UOM needed in OBIA

OBIA caters to various source systems like EBS, PSFT, Siebel, JDE, Fusion etc. Each system has its unique way of defining UOMs in the OLTP. The systems also define the UOM conversion to aggregate and show data correctly.

Most of our customers implement multiple OLTP systems. This presents a unique opportunity in BI to define a universal conversion as the data flows into OBIA DW.

Source Systems

Let’s take examples of some source systems that OBIA supports.

EBS

EBS allows a “Standard UOM” at the Item Level, defined for the Master Organization. This Standard UOM can be overwritten at the Individual Inventory Org level and is defined as the “Primary UOM”. (Inventory Org is the organization/Location where the Item is physically manufactured or stocked)

EBS also allows UOMs to be classified into various UOM Classes. UOM classes are used to group together units of measure that have similar characteristics or that measure the same dimension. Example: Length, Area, Weight, Volume, Time etc.

The conversions within EBS are of two types;

Inter Class: meaning across classes. These conversions are stored in EBS, from one UOM class to another UOM class at the Base UOM level. The Base UOM code is the lowest UOM Code for the UOM Class e.g., ml for the Volume Class, inches for the Length Class, etc.

Intra Class: meaning within the class. These conversions are stored in the EBS, form one UOM Code to the Base UOM Code within that UOM Class.

Further the UOM conversions can be Item specific (e.g., Pens 1 Box = 100 Ea) or Item Independent (e.g., 1 DZN = 12 Ea)

PeopleSoft

Item is defined with a Standard UOM at the Item Level for the Business Unit (BU). This UOM can be overridden at the individual Inventory Org Level and is defined as the “Primary UOM”.

UOM Conversions are defined in both directions. I.e., UOM A to UOM B, and the vice-a-versa.

Further the UOM conversions can be Item specific (e.g., Pens 1 Box = 100 Ea) or Item Independent (e.g., 1 DZN = 12 Ea)

Fusion

Fusion OLTP UOM design is very similar to EBS. We have similar concepts of UOM Class, InterClass and IntraClass conversions. The EBS concepts of Standard UOM and Primary UOM very well serve the same purpose in Fusion Apps.

Siebel does not have a concept of UOM Conversion.

OBIA Design and Solution

OBIA UOM Extraction Logic

EBS/Fusion – Both the interclass and intraclass conversions related to an Item and conversions which are independent of an item (called as Resource UOMs) are extracted into OBIA.

PSFT – Peoplesoft does not have a concept of UOM class. However the Item Specific and Item independent UOM and related conversions are similar here and both are extracted into OBIA.

Conformed UOM Domain

OBIA Design uses the Conformed Domains Concept for UOM. Since OBIA DW contains data from different source systems, and UOM codes/names could be different across sources, the Conformed Domain for UOM is helpful.

Example – A dozen could be called DZ in EBS and Dzn in PSFT. However when we analyze Facts or quantities along with the related UOM in OBIA, we use a conformed UOM which could be called “Dozen”.

Configuration Manager and UOMs

Let’s dig some more details on UOM Domains. We have a Source Domain called UOM and a Conformed Domain W_UOM. The Source Domains will get populated through the Domains ETL which the user would execute before running the Main ETL. Once the Source Domain UOMs are present in Configuration Manager, the customer has two options to map the source UOMs to Conformed UOMs–

  1. Enter all the Conformed UOM codes into CM and map the Source UOMs to Conformed UOMs. Example – Map DZ of EBS and Dzn of PSFT to the newly created Conformed UOM Dozen.
  2. Use the “Sync to Source” option of CM. By using this method, we can create Conformed UOM codes for all the Source UOMs present in a particular Source system and CM automatically maps them in a 1:1 fashion. This is the preferred method since the conformed UOMs would be similar to that of one of the Source systems that the user has.

In case of multi source environments, the user will have to map the unmapped UOMs from the other Source System(s) to the corresponding Conformed UOMs.

UOM Data in OBIA PS1 (11.1.1.7.1)

W_UOM_CONVERSION_G is a table which stores the UOM Conversions. It stores attributes like From UOM, To UOM and Conversion Rate. Irrespective of whether source systems store both A->B and B->A conversions, this table will have conversion rates captured for both permutations. This table stores the Conformed UOMs.

Master Item and Inventory Item – The Master Item information is captured in W_PRODUCT_D and W_INVENTORY_PRODUCT_D stores the Inventory Item information. As mentioned in previous sections, an UOM defined at Item level is called Standard UOM and UOM defined at Inventory level will be the Primary UOM. Hence, it becomes a no-brainer that W_PRODUCT_D will store the Standard UOM and W_INVENTORY_PRODUCT_D will have Primary UOM in OBIA.

These two tables store the Source value for UOMs. There are reusable Lookups built upon these tables LKP_PRODUCT_BASE_UOM and LKP_INVENTORY_PRODUCT_PRIMARY_UOM. These 2 Lookups have the logic to return the Conformed UOM Code.

Fact Tables will store the Conformed UOM codes. Fact tables will use the Conformed UOMs returned by the LKP_PRODUCT_BASE_UOM and LKP_INVENTORY_PRODUCT_PRIMARY_UOM during ETL for UOM Lookup and UOM Conversion Rate Lookup. The related UOM User Defined Functions (UDFs) are LKP_UOM_CONVERSION_CODE and LKP_UOM_CONVERSION_RATE.

As we have seen, ETL uses Conformed UOM codes at all places. Since the ETL has a dependency on the Source UOM to Conformed UOM Domain mapping, it becomes mandatory that the user does this Domain mapping before running the main ETL.

Pseudo-Code of UOM in OBIA

Fact ETL will have the following pseudo-code for UOM conversion and the conversion rate obtained here will be either used in ETL or RPD to sum up the Quantity related metrics.

Fact Staging

Use the Reusable Lookups (LKP_PRODUCT_BASE_UOM and LKP_INVENTORY_PRODUCT_PRIMARY_UOM). Get the Primary Conformed UOM and Standard Conformed UOM.

->

Pass these as inputs along with Product Id, Inventory Org Id(present in Fact Staging and sourced from Source Tables) to UOM UDFs. (LKP_UOM_CONVERSION_CODE and LKP_UOM_CONVERSION_RATE). UDF’s logic is as mentioned below:

If Primary Rate is null -> Return Standard Rate -> If that is null return default value of 1
If Primary Code is null -> Return Standard Code -> If that is null return the From UOM Code (the input value)

->

Fact Table

Also the Fact Tables might store the UOM Code and in such cases, the UOM stored in Fact Tables will be Conformed UOM codes.

Technical Example

Let’s take a look at a Dashboard page. In Procurement àSpend Analyzerà Item Details dashboard page, we have a report called “Spend Details By Item”. The report shows the spend details for each Item, along with item details, other metrics like Invoice Unit Price (average, minimum, maximum), Invoice Quantity and UOM Code. A screenshot of the report is shown below. The Invoice Quantity metric used the Conversion Rate which was calculated in ETL. Even though the transaction for Invoiced Quantity could have been happened in a different UOM (say Box) we have converted it to Ea and hence the summed up Quantity shows the accurate value as seen in the report.


Screenshot showing the metrics: Invoice Quantity calculation uses the Standard Conversion Rate calculated in ETL as mentioned in section “Pseudo-Code of UOM in OBIA”.

Invoice Quantity calculation:

The report also shows the UOM Code column. This comes from the Fact table itself as shown by the Lineage information below. Fact Tables stores the Conformed UOMs.

The Fact table W_AP_INV_DIST_F stores the Conformed UOM Code in this column. The Conformed UOM Code is evaluated in ETL using an UDF called as Domain Member Map. The pseudo code of the UDF is as shown below. It accepts the Source UOM Code and returns the Conformed UOM code. The user should have done the Source UOM à Conformed UOM mapping as mentioned in section “Configuration Manager and UOMs” for this UDF to work correctly.

DOMAIN_MEMBER_MAP( 'UOM', <Source UOM Value>, #DATASOURCE_NUM_ID, 'W_UOM' )

Conclusion

UOM is widely used across subject areas and it plays an important role in evaluating quantity metrics. We exclusively use Conformed UOMs in Model, ETL and Reporting Layers as explained in this post.

Comments:

...

Posted by James on February 28, 2014 at 02:50 PM PST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Phil Wang-Oracle

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today