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.
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.
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.
