4 comments

  1. Why not create views for easy reporting? I used it previously to easily query XML-data, which is a similar problem.

    With views, it’s the resposibility of the original designer/developer to create them and ofcourse he knows best how the underlying model works.

    Robert te Kaat Beantwoorden

  2. Thats a legitimate question, it’s a common practice to use views.
    In the examples I’ve shown, I took one important dimension out of scope : time.
    Mostly when you run a report, you want data between two specific points in time.
    When you use a view, you usually realize this by adding a JOIN/WHERE clause on the BeginDate and EndDate columns.
    This method is efficient way of selecting data, since selecting through a view supports the usage of indexes.

    However, when you use the PIVOT construction I described above, adding a JOIN/WHERE clause _after_ the PIVOT won’t be as efficient. The PIVOT statement will pivot all records before applying the filters, which makes it a very expensive operation. To make a PIVOT efficient, you want to delimited as much records as possible _before_ you pivot. The only way to achieve this, is by making use of variables; i.e. a startdate and enddate.
    Since views don’t support the usage of variables, we were bound to use functions instead.

    The downside is, of course, that the resultset of a tablebased function is a table variable; so filtering the resultset of a tablebased function won’t use any indexes.

    marks Beantwoorden

  3. Very good information here.

    Thanks for sharing.

    Anyway i have some more questions.

    1. Any performance issue using this technique. (if you can provide some matrix like data size, time, should be very good)

    2. What if I don’t use EAV-modeled but using xml to keep data instead, how can you generate report from xml?

    Thanks,

    ensecoz Beantwoorden

  4. Very good exposition of the EAV design.

    I have a question though: is it really necessary to use the PIVOT operator as not all RDBMSes may support it. I have seen another example of ‘pivoting’ using standard SQL like so:

    create view vw(o, name, age, gender)
    as
    select o,
    max(case a when ‘name’ then v end) as name,
    max(case a when ‘age’ then v end) as age,
    max(case a when ‘gender’ then v end) as gender
    from eav2
    group by o
    having age > 3 and name like ‘y%’ order by age;

    (where o is the object id, a is the attribute and v is the value).

    Does this do the same as the PIVOT operator? Does it have the same performance?

    tsaixingwei Beantwoorden