Exposing an EAV database model as a mart for visualisation

We are using an EAV based data model to store data from files that we read in. All the data is related to a master record. We need to visualise this master record (charts, aggregations, reporting etc) and using EAV based model directly for this would be very slow. We need to pivot EAV into a flat structure where each record is on a single row.

The problem that we have is that the attributes/properties (which are then used as columns in mart) need to be flexible. Is there a way to design a mart which is pivoted as much as possible so is performant but is also flexible in that adding/removing attributes/properties does not require changing mart (and associated ETL).
LVL 83
CodeCruiserAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Generally speaking: No.

Cause dimension and fact tables have fixed structure.

In your concrete case: When your EAV attributes are only used in dimensions, then you may use dynamic SQL to flatten it. But this requires to add columns to your dimensions when necessary. The problem here maybe the number of attributes.

Using a dynamic SQL aproach for fact tables is dangerous, cause different attributes may have a different grain. Thus automatically adding them to a fact table will render your fact table in such a case use-less.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CodeCruiserAuthor Commented:
Thanks for your response. Number of attributes is a problem as we have more than 2000!
0
ste5anSenior DeveloperCommented:
You should evaluate sparse columns (wide tables).
Otherwise you need to group the attributes by some meaning full category and split your dimension into appropriate chunks.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

CodeCruiserAuthor Commented:
Yes we are using sparse columns already. We do have a mart in the form that you suggested but flexibility/extensibility is a problem. We cannot recreate it using dynamic SQL as we have millions of rows of data and only want to sync latest changes to mart.
0
ste5anSenior DeveloperCommented:
Millions of rows in the dimensions?
0
CodeCruiserAuthor Commented:
Fact
0
ste5anSenior DeveloperCommented:
Well, you need to group the attributes by the grain of the value. Then you can create junk dimensions with attributes of the same value grain and place the values in one fact row. So you'll get also multiple fact tables. It's basically a manual partitioning of your data.
Another grouping criteria is which attributes are queried together.

btw, just for curiosity: How many distinct entities, distinct attributes and distinct values do you have in your model.

p.s. EAV models should only be used for spares attribute distribution. Maybe this is the problem..
0
CodeCruiserAuthor Commented:
Entities: Millions, attributes=1000s, values=millions (mostly floats)
0
CodeCruiserAuthor Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.