5

Existing setup SQL Server 2005 with standard EAV table that users want to slice and dice with a BI tool (Cognos).

Is there any hope that we can transform this data into a format that we can report off of? Google has led me to believe there is no hope. I'd like to believe that some kind of solution is out there.

Improve this question
3
  • 2
    Why on earth are you using EAV for stuff you want to do reporting on? EAV is often a design smell in the relational db world.
    – HLGEM
    Commented Jul 2, 2012 at 17:23
  • It's not that I want to, trust me. We have a system that keeps profile information as Key-Value pairs, designed as such to allow the maintaining department to modify what attributes/form elements there are as needed. They have tasked me to report off that data so they can get useful information, e.g. How many users live in this zipcode, like dogs, and have 2 children? If zipcode, pets, and number of children are keys/attributes, how can I report this in a usable fashion?
    – IronicMuffin
    Commented Jul 2, 2012 at 17:45
  • 1
    You would have to join to the table 3 times, once for each condition you are looking for.
    – HLGEM
    Commented Jul 2, 2012 at 17:56

2 Answers 2

Reset to default
6

Well...my solution is as follows:

I used a dynamic pivot table in a stored procedure. The stored procedure called a View that I created which denormalizes the data. I think used MS Query to hook the stored procedure up to Excel 2010. This loads it up into a nicely formatted table that allows the end user to sort and filter to their heart's content.

With 200,000 records, the query takes about 20-30 seconds to run... I pre-loaded the spreadsheet and set it to refresh in the background at set intervals.

They have it in their hands now, but not long enough to have any feedback. With all the tools I've tried and researched...this really is the only one that has met all my requirements...which is slightly frightening to me. Hope this helps anyone who stumbles across this question.

Improve this answer
1
  • 2
    And please add a link to this qeuestion to anyone who asks if they should use an EAV design for their database!
    – HLGEM
    Commented Aug 1, 2012 at 20:16
5

EAV is a nightmare for BI tools.

I've found a few places that build automated processes that generate a "pivoted" view of the EAV table, as an ETL process daily which drops & recreates the table, with columns for each key.

However, depending on how your BI tool works, you will still have to manually add the new attributes that are created by the department. You could build a notification process to your team to let you know when the business creates a new attribute.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.