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

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.

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.

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

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

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.

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.

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?