OLAP design with Key Value Pairs

JamesMichaelWest · #1 (**permalink**) 05-04-07, 11:23

HTML Code:

<html>
<body>
<p>If someone could please help us out here. We have inherited a 
bad database design for a large scale project that is tightly 
coupled to various applications. So something we can’t change but 
something we have to make useful.  At this point we have we have 
spent weeks trying to come up with a solution but so far have just 
not been able to make it work the way we want. 

The basic problem is that we have a key-value-pair structured table 
with millions of records in it that store all of the data. This table
needs to be pivoted, so that the key names become columns and the 
values become values for the pivoted columns. The value is both 
string and numeric, but stored as a varchar.  The way that the data 
is grouped within this table is that each key is actually a field 
name for a particular project. There are about 150 different projects
each with distinct and shared field names. There is roughly 3000 
field names all together. Any column needs to be able to be reported
on, to either get a string value when appropriate, or an 
aggregation of numeric values when appropriate. </p>

<p>A sample of the data might look like:</p>

<p>ProjectFields</p>
<table>
<tr>
    <td>ProjectID</td>
    <td>FieldID</td>
    <td>FieldName</td>
</tr>
<tr>
    <td>1A</td>
    <td>1</td>
    <td>PrjName</td>
</tr>
<tr>
    <td>1A</td>
    <td>2</td>
    <td>Type</td>
</tr>
<tr>
    <td>1A</td>
    <td>3</td>
    <td>Due Date</td>
</tr>
<tr>
    <td>2A</td>
    <td>4</td>
    <td>PrjName</td>
</tr>
<tr>
    <td>2A</td>
    <td>5</td>
    <td>Ounces</td>
</tr>
<tr>
    <td>2A</td>
    <td>6</td>
    <td>Type</td>
</tr>
<tr>
    <td>2A</td>
    <td>7</td>
    <td>Company</td>
</tr>
</table>
		
		
<p>FieldValues</p>

<table>

<tr>
    <td>ElementID</td>
    <td>FieldID</td>
    <td>FieldValue</td>
</tr>
<tr>
    <td>1</td>
    <td>1</td>
    <td>My Project</td>
</tr>
<tr>
    <td>1</td>
    <td>2</td>
    <td>A</td>
</tr>
<tr>
    <td>1</td>
    <td>3</td>
    <td>Yesterday</td>
</tr>
<tr>
    <td>2</td>
    <td>4</td>
    <td>HisProject</td>
</tr>
<tr>
    <td>2</td>
    <td>5</td>
    <td>3.5</td>
</tr>
<tr>
    <td>2</td>
    <td>6</td>
    <td>Gold</td>
</tr>
<tr>
    <td>2</td>
    <td>7</td>
    <td>S.T.S</td>
</tr>
<tr>
    <td>3</td>
    <td>1</td>
    <td>Last Project</td>
</tr>
<tr>
    <td>3</td>
    <td>2</td>
    <td>B</td>
</tr>
<tr>
    <td>3</td>
    <td>3</td>
    <td>10/10/2008</td>
</tr>
</table>	
		
<p>And an output report might look like:</p>
<table>
    <tr>
        <td>PrjName</td>
        <td>Type</td>
        <td>DueDate</td>
    </tr>
        <tr>
        <td>My Project</td>
        <td>A</td>
        <td>Yesterday</td>
    </tr>
        <tr>
        <td>Last Project</td>
        <td>B</td>
        <td>10/10/2008</td>
    </tr>
</table>	
		
<p>
So we can’t make a make a de-normalized table out of it because there
 would be too many columns. We have created 150 different views that 
 extract the data the way we want, but I don’t think that will help 
 out in our cube either. In short, we’ve tried countless ways in both 
 SQL 2000 and 2005, linked cubes, virtual cubes, etc. But no tool has 
 been able to overcome our lack of experience just yet. Can someone 
 give us an idea, or are we just going down the wrong path trying to cube this?
</p>
</body>
</html>

r937 · #2 (**permalink**) 05-04-07, 16:01

without being able to offer you anything other than condolences, i would like to point out that your situation is a prime example of the evils of the Entity Attribute Value (EAV) model

what would be the purpose of the cube? what kind of information are you hoping to obtain that is not being provided by your 150 views?

JamesMichaelWest · #3 (**permalink**) 05-04-07, 18:11

Thanks

I'm not sure what the EAV model is, so I'm researching that right now, but the Wikipedia definition seems to be just about right.

The idea behind all of this was that we would deliver a reporting solution that would be able to report on anything without the client needing to write any SQL, do to much leg work, etc. The 150 views report on everything that they need, just not in a way that they would use. The very defined requirements for this project state "give us everything anyway we want it for ad hoc reporting". That's because they are a pass through entity who will be asked for something at anytime, and they never know before hand what that something might be.

I think that we might have found a solution though. We are dynamically creating a query which pivots this info (using the SQL Server 2005 pivot statment pretty cool). This gives us a query with 2100 columns. We might have to turn this into a table but the max there is 1000 columns (max 4000 for a query). Its being run through OLAP right now, still not sure if its going to work though. I am utterly suprised/disgusted that I have made a query with 2000 columns..

I thought the limit was 250.

r937 · #4 (**permalink**) 05-04-07, 18:43

Quote:

Originally Posted by JamesMichaelWest

I'm not sure what the EAV model is, so I'm researching that right now

JamesMichaelWest · #5 (**permalink**) 05-09-07, 12:41

Thanks, that was some good info. I have been troubled with these types of designs in the past (just never to this degree), but I did not know that they've already been named.

Internet Services

Web Development

Digital Marketing

Consumer Tech