How to model dimension data including dynamic fields from the OLTP system?

View previous topic View next topic Go down

How to model dimension data including dynamic fields from the OLTP system?

Post  JM on Wed Sep 25, 2013 3:23 am

By dynamic fields I mean attributes that have been added dynamically by the users, using the OLTP system.

Example:
Using the OLTP system, users can add new fields related to product.
Those fields will be stored in a different table, and all new fields are actually added
as rows in one table and their values assoicated with each product are stored in a third table.

Example:

Product (ProductId, Name)
ProductExtraField (ExtraFieldId, ExtraFieldName)
ProductExtraFieldValue (ProductId, ExtraFieldId, ExtraFieldValue)

ExtraFieldValue is actually a text value, but used for search to filter out relevant products.
(it can contain int, boolean, lists, txt etc. but they all are beeing stored in a single text string)

When it comes to dimensional modelling, a Dim_Product will be made, but how do we model dynamic fields like this?

Should ProductExtraFieldValue become a fact table with a bridge to Dim_Product, which we have to search in - and without any real measures?
Should it become a separate dimension, which then will consist of attributes from all of these tables (huge dimension...)?
Should we flatten out the ProductExtraField in a separate dimension with hardcoded column (less flexible)?

ps. consider millions of rows in the Dim_Product, and each product can have 100+ extra fields releated.
Of course different extra fields dependent on the product group, category etc. So, the number of extra
fields can be several 100. In total, the potential could be 50-100 MILL combinations.

Any recomondations or experiences?

JM

Posts : 1
Join date : 2013-09-25

View user profile

Back to top Go down

Re: How to model dimension data including dynamic fields from the OLTP system?

Post  BoxesAndLines on Wed Sep 25, 2013 9:11 am

No magic pills on this one. Here's the same problem posted awhile back, http://forum.kimballgroup.com/t2363-designing-data-marts-from-an-eav-data-source#10526
avatar
BoxesAndLines

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

View user profile

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