Results 1 to 5 of 5
  1. #1
    Join Date
    May 2007
    Posts
    3

    OLAP design with Key Value Pairs

    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>

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2007
    Posts
    3
    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.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by JamesMichaelWest
    I'm not sure what the EAV model is, so I'm researching that right now
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2007
    Posts
    3
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •