Modeling Descriptive Flex Fields (Attribute Columns) on an Order Line

View previous topic View next topic Go down

Modeling Descriptive Flex Fields (Attribute Columns) on an Order Line

Post  kkgronemeier on Tue Feb 15, 2011 2:24 pm

For an order line that contains 15 generic attribute columns, how would one model all the miscellaneous data elements (i.e Vendor SKU, Customer Part Number, RMA Product Condition, etc) to make it available to end users as part of the order fact table?

While some of the attributes contain discrete data, when comparing all fifteen attributes as a group on a line by line basis 75% of the data is unique. Business users insist that they need this data for detail analysis.

This is Oracle E Business Suite; the meaning of the generic attributes (attribute1, attribute2, etc..) are defined in another set of tables. Our order fact table has a full set of dimensions (Customer, Item, Financial, Date, etc.). What is an acceptable way to provide this miscellaneous data to users? Some individuals in our organization, who are from transactional backgrounds, just want to stick this information in the fact table like a degenerate dimension. This does not appear to be an acceptable solution as we could have 15 or more textual columns in the fact table. Then there is the problem if the decision is made to change the meaning of an attribute at some point, the source system would populate a different data element.

My initial thoughts were to model a solution in this way:

Create a dimension to hold the miscellanous attribute values and a dimension to hold the definition of the attributes. The Attribute Value dimension would be loaded on the fly as the order line is processed, while the Attribute Definition dimension would be maintained much like out other dimensions (change data capture). The order fact would link to the attribute value dimension, while the attribute value dimension would link to the attribute definition dimension.

Order Fact Table => Order Attribute Value Dim => Order Attribute Definition Dim

Any feedback or suggestions is greatly appreciated.

kkgronemeier

Posts : 2
Join date : 2009-10-06

View user profile

Back to top Go down

Re: Modeling Descriptive Flex Fields (Attribute Columns) on an Order Line

Post  Jeff Smith on Wed Feb 16, 2011 9:59 am

I would start by selecting distinct on different combinations of the 15 generic attribute columns to see if there were any combinations had an acceptable number of unique combinations that could go into a Junk Dimension. I would look to see if there were any fields that could act as a Degenerate dimension keys on the fact table.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Modeling Descriptive Flex Fields (Attribute Columns) on an Order Line

Post  BoxesAndLines on Wed Feb 16, 2011 10:20 am

I would look to the ETL to transform generic to specific. Then model appropriately.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Good Input

Post  kkgronemeier on Wed Feb 16, 2011 4:18 pm

Thanks for the input. We'll definitely use your input as we work towards a solution.

kkgronemeier

Posts : 2
Join date : 2009-10-06

View user profile

Back to top Go down

Re: Modeling Descriptive Flex Fields (Attribute Columns) on an Order Line

Post  hang on Wed Feb 16, 2011 5:41 pm

BoxesAndLines wrote:I would look to the ETL to transform generic to specific. Then model appropriately.
Absolutely agreed! One of the important goals of data warehousing is the ease of use. In dimensional modeling, the definition on attributes should be self contained within the dimension table. The attribute definition dimension in your case should belong to metadata area where you document your data elements. Some OLTP systems have all sorts of fancy structure to reduce data redundancy and development work (programming) at great expense on data clarity. Well data warehouse should reverse that trend and make the model as clear as possible.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Modeling Descriptive Flex Fields (Attribute Columns) on an Order Line

Post  ngalemmo on Thu Feb 17, 2011 12:18 am

Agree with B&L.

What you are facing is typical with ANY commercial ERP system. They all have user definable attributes. The key word here is 'definable'. The business decides to use attribute #3 to hold Customer Item Number, so be it. In the DW, model an attribute called Customer Item Number and populate it as such from the source.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Modeling Descriptive Flex Fields (Attribute Columns) on an Order Line

Post  kiran.mv on Thu Mar 10, 2011 5:55 am

ngalemmo wrote:Agree with B&L.

What you are facing is typical with ANY commercial ERP system. They all have user definable attributes. The key word here is 'definable'. The business decides to use attribute #3 to hold Customer Item Number, so be it. In the DW, model an attribute called Customer Item Number and populate it as such from the source.

I am facing a similar situation. Infact, its a bit more complex, in the sense that, the Dimensional model I am designing itself is for generic customer. There are lots of such fields in source system, whose usage is not fixed. It can be used differently by different client, but now we need to design a Dimensional Model containing those columns.

Is there any better way of handling this?

kiran.mv

Posts : 13
Join date : 2011-03-10

View user profile

Back to top Go down

Re: Modeling Descriptive Flex Fields (Attribute Columns) on an Order Line

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum