cpeters5
asked on
EAV model used in datawarehouse design?
I am designing a database for a potentially large data warehouse. Several of the fact tables may contain over a hundred columns. Some of these columns may be sparse since data extracted from various sources may have different attribute. We are debating between
1) Keep all columns and deal with sparseness. A few colleagues of mine worried about tables being sparse, but I don't see any problem with it. Perhaps I misses some important thing here. Would the width (number of columns) of a table have any impact on performance that much?
or
2) Move sparse columns into an EAV table. Queries will be more expensive, but how much more? Would you use this model in your own data warehouse?
I am not familiar enough with datawarehouse performance and think I would post this question ot the gurus in this community.
Thanks for your comments in advance!
pax
1) Keep all columns and deal with sparseness. A few colleagues of mine worried about tables being sparse, but I don't see any problem with it. Perhaps I misses some important thing here. Would the width (number of columns) of a table have any impact on performance that much?
or
2) Move sparse columns into an EAV table. Queries will be more expensive, but how much more? Would you use this model in your own data warehouse?
I am not familiar enough with datawarehouse performance and think I would post this question ot the gurus in this community.
Thanks for your comments in advance!
pax
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>> We did start with normalized schema, then we denormalized to form a wide fact table.
That's unfortunate. Are you familiar with DW design practices? Are you following a model such as Inmon or Kimball?
>> I can see that EAV is appropriate when the attributes may change over time. Is there any other reason why we should use EAV in a datawarehose project?
No to both of those things.
That's unfortunate. Are you familiar with DW design practices? Are you following a model such as Inmon or Kimball?
>> I can see that EAV is appropriate when the attributes may change over time. Is there any other reason why we should use EAV in a datawarehose project?
No to both of those things.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
We did start with normalized schema, then we denormalized to form a wide fact table. We wanted to avoid table joins. The frontend will be a BI product, probably Business Object.
As per NULL values, it has alwasy been my understanding that using variable size variable should remove this concern. I just wanted to confirm my understanding with the expert here.
I can see that EAV is appropriate when the attributes may change over time. Is there any other reason why we should use EAV in a datawarehose project?