How to model dimension data including dynamic fields from the OLTP system?
2 posters
Page 1 of 1
How to model dimension data including dynamic fields from the OLTP system?
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?
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
Re: How to model dimension data including dynamic fields from the OLTP system?
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
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
![-](https://2img.net/i/empty.gif)
» Need to create Dynamic dimension for use via BI system
» OLTP Dailty Pricing tables: How best to model ceiling, floor and average daily price data?
» Dimension fields depend on other fields
» How to dimension model this operational data
» Dealing with a terrible OLTP system. Long, but I'd appreciate some expert guidance.
» OLTP Dailty Pricing tables: How best to model ceiling, floor and average daily price data?
» Dimension fields depend on other fields
» How to dimension model this operational data
» Dealing with a terrible OLTP system. Long, but I'd appreciate some expert guidance.
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|