I think I may be in hell?! (EAV Schema)

[ZenRaven]

So here's the deal. I just started with a new company as a SQL Developer/Analyst. I've got a couple years of experience with SQL Server, mostly 2000, on some fairly large and complex databases (or so I thought).

So I get to this new company and the database structure is just wacky. I've never seen anything like this before. After a few google searches I find a bunch of articles on EAV. Yup, that's what I've gotten myself into. On top of that, it seems to be some exploded EAV hybrid, possibly EAV/CR or something I saw. I've dubbed it the ESEASAADSADAVSAVCRS Schema (Entity Subentity Attribute subattribute attributedata subattributedata attributevalue subattributevalue circular reference system). Gotta laugh so you don't cry, right?

As far as I can tell, all of the data they have is submitted from clients, cleaned, aggregated and then used to generate reports which clients in turn subscribe to. From what I've read and seen here, EAV is horrible for reporting (among other things) and they are having performance problems. The guy 2 times before me made a reporting table structure that does all of the aggregations and spits the data into new tables for the reports to run off of. The problem? The reporting table structure is also EAV!

As the original designer left the company, he said the word CUBE. Then comes in the next "SQL Guru" (she was only here for 6 months, can't imagine why). So they start doing upgrades to SQL 2005 and she takes her predecessors advice and starts designing a dimensional model in SSAS based of the reporting database structure. EAV + CUBES = WTF? Did I happen to mention she didn't have any OLAP experience when she started? (neither do I, at least not in a production environment)

So now there's me sitting here 2 weeks in with an EAV database, a pseudo-EAV reporting database, some unfinished cubes, not wanting to touch anything for fear of the whole thing imploding.

Here are some of the factors I must take into account:
1. The company website serves as the client UI and is tightly integrated with the EAV schema.
2. New data sources come and go quite often which means lots of attribute changes to the data
3. After looking at the data with what little SSAS knowledge I have, it seems that going this direction might just be useless. I believe all of the fact/measure data is stored in all of these dynamic attributes and it seems like I would be changing them on every load.
4. I thought about dropping the cube idea and redesigning the reporting database structure to 3NF and then pumping the data from EAV to 3NF for reporting but... uhhh... damn, my mind went blank
4. My brain is fried from looking at this thing so I can't remember what other points I was going to bring up... please give some advice.

 

[nomadicalloy]

I agree with you on this (hell, sorry). More info about crazy EAV/CR Model

http://ycmi.med.yale.edu/nadkarni/eav_CR_contents.htm

This is Signature

http://www.xpherion.com/

 

[ESquared]

Friend,

I feel your pain. But having designed and implemented an EAV production system myself, all I can tell you is that there are situations where it's required. Whether yours is one of them only you can say.

Since at this point it's your job, all you can do is buckle down to get the greatest understanding of the system that you can. If you're truly interested, I can share some of the pre-processing techniques I used to expose my EAV data in views (updateable, insertable, and deletable!) that take the role of traditional wide-column tables.

Some of the people at my job found it very hard to understand even the beginnings of what was going on with my EAV database. Some pretty much understand it and can participate on levels where I've taught them what's going on, but couldn't possibly further the development of the database. The kind of thinking required to work with such a self-referential looping kind of metadata just isn't in them, unfortunately.

Even with those serious drawbacks factored in (and the potential of some poor future developer ending up in exactly your situation), there was no possible way that I could have avoided such a design. (The previous unrealized best design before I came along was looking rather like a spirograph.) Through the careful use of stored procedures, some wheel-reinventing to supply the constraints I needed, views, and pre-processed views (that can be rebuilt on the fly in response to metadata changes), the system is manageable. Modeling real-world objects whose attributes one needs to track may change over time pretty much demands EAV.

It is most definitely not crazy in terms of sanity. It is only crazy in the sense of wonderfully and sublimely complex and subtle.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[Aluminum]

Sounds rough. I would start looking at this problem in terms of goals and risk. What does your management want accomplished with this mess on a short term and long term basis? Also, can you set up a development environment? Essentially, make a copy of this mess to a different server. Since it is a very unique situation, before you implement your improvement (on an incremental basis) test it!

Get another server or environment that is similar that way you do not mess up the production system. Finally, I would set up a versioning system to keep track of backups. Document your changes in the event you mess up the production system. You can always restore the database to it's previous state and evaluate were you went wrong.

Going forward Set up some goals reasonable goals and minimize risk as much as possible. Plug foreword and fix this problem by breaking it into smaller parts and addressing the issues. You stand to gain a great deal of knowledge and self-respect!

Good luck

 

[Airpan]

ZenRaven,
I am by no means a SQL analyst, but I have had my fair share of jobs that were overwhelming to say the least. I agree with Aluminum. You have to kind of take stock... my guess is they are watching to see what your reaction is and how you handle stress. I would think that you have every right to request long-term and short-term goals, as well as supplies to complete the job.
Non illigitamus carborundum, and certainly don't let them see it.

 

[ESquared]

Good words, Aluminum. Probably more helpful than anything I had to say.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]