More Tree Mapping
By Tim Dexter-Oracle on Oct 23, 2008
Following on from yesterday's intro to tree maps we now get into the nitty gritty of the Flex component and BIP.
The flex component is not that nitty really, its very easy to use, the main entry for it :
is pretty simple. It needs to know your data source (dataProvider.) The labelField, what text to put in the rectangle, the weightField, this governs how big the rectangle is. The colorFunction - how to colour the rectangles and finally the dataTipFunction, this is a popup when folks hover over a rectangle to show them info about the underlying data.
The component download comes with a bunch of samples, I took the stock market example and bent it to my will, cos Im strong like that!
Both the color functions and dataTipFunctions are provided, they are easy enough to understand and can be modified easily. josh provides some good getting started doc.
All sounds very easy and the component is, its the data it needs or more importantly the data structure it needs thats a bit more tricky.
Those of you that know and use Publisher (standalone/BIEE) will know that if you give Publisher a piece of SQL it will return you XML. Its flat XML, we call it 'ROWSET/ROW' - the treemap component can not use it - it actually can, but you get some groovy results. The sample data Josh works with is simple enough:
<node label="Mail" data="100">
<node label="Inbox" data="70"/>
<node label="Personal Folder" data="10">
<node label="Business" data="2"/>
<node label="Demo" data="3"/>
<node label="Saved Mail" data="5" />
<node label="Sent" data="15"/>
<node label="Trash" data="5"/>
But you'll notice that there are elements and attributes, you will also remember that Publisher does not generate attributes on its own. It appears the component needs a hierarchy with attribute values.
We can get Publisher to generate the attributes but we need some help, I had to use SQL XML. I have written about this in the past, its very powerful if a little fiddly to get the XMLELEMENT and XMLATTRIBUTES commands in the right spot. I managed to get a structure that worked with the tree map component with the following SQL XML.
XMLATTRIBUTEs( e.SCHOOL_NAME as NAME,
sum(CASE E.ATTEND_USE WHEN 'A' THEN 1 END) as "ABSENT",
count(e.stu_id) as "TOTAL_STUDENTS",
round((sum(CASE E.ATTEND_USE WHEN 'A' THEN 1 END) / count(e.stu_id)),3) as "PCT_ABS",
round(0.14 - (sum(CASE E.ATTEND_USE WHEN 'A' THEN 1 END) / count(e.stu_id)),3) as "DIFF_FROM_TARGET"
).getClobVal() as SCHOOLS
from ENROLLMENT e,
where e.sch_cd = t.sch_cd
and t.type = 'H'
and e.calendar_dt = to_date('10/24/2007','MM/DD/YYYY')
group by e.SCHOOL_NAME
Lots of heavy lifting going on in there but it basically generates:
<SCHOOL NAME="Dauphin High School" ABSENT="223" TOTAL_STUDENTS="1925" PCT_ABS=".116" DIFF_FROM_TARGET=".024">
. . .
We can not get rid of the ROWSET/ROW elements but we can live with that. All we need to do is specify the path to the SCHOOL level i.e.
I have yet to investigate if we can use a data template to avoid the extraneous elements. The component will allow you to have multiple levels of data just like a tree so you can build outputs like this.