vba - Rendering STAR schema in Excel (a 2-Dimension table) -

^{please move appropriate forum if doesn't belong here.}

i've data feed represents multidimensional data in star schema. e.g. /products /salesyear /salescontact /region /salesdata

now want render data in simple tabular view

example

                             2005   2006   2007 product1                      category1                  27m$   30m$   35m$  category2                  9m$    1m$    11m$ product2                      category1                  27m$   30m$   35m$  category2                  9m$    1m$    11m$ 

are there standard algorithm or techniques can used display kind of data?

[edit]

what need efficient method build in-memory cube powerpivot @ smaller scale.

i think use modified version of answer @dick kusleika: convert row columns of data column multiple rows in excel 2007. note solution not nested rows under product1/product2 have above, guess pretty modify solution handle 2 row headings: column a contain product name , column b contain category.

---

edit: misunderstood , thought trying data out of format, not in to format.

if have excel 2010, powerpivot plugin can consume odata fields directly (found answer on odata.org consumers page. if have older excel, might still able pull data in get external data web. may need throw proxy page (asp.net, php, whatever you're comfortable with) in-between understands json , transform html table. get external data web understand how read data standard table.

once have data in normalized sheet in excel, should matter of inserting pivot table uses range it's data source.