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

^{Please move to an appropriate forum if it doesn't belong here.}

I've a data feed that represents some multidimensional data in star schema. e.g. /Products /SalesYear /SalesContact /Region /Salesdata

Now I want to render this data in a 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 any standard algorithm 开发者_高级运维or techniques that can be used to display this kind of data?

[EDIT]

What I need essentially is an efficient method to build an in-memory cube like powerpivot does but at a smaller scale.

I think you could use a modified version of this answer by @Dick Kusleika: Convert row with columns of data into column with multiple rows in Excel 2007. Note that this solution does not the nested rows under Product1/Product2 that you have above, but my guess is you could pretty easily modify the solution to handle two row headings: column A would contain product name and column B would contain the category.

EDIT: I misunderstood and thought you were trying to get the data out of that format, not in to that format.

If you have Excel 2010, the PowerPivot plugin can consume OData fields directly (found the answer on the OData.org consumers page. If you have an older Excel, you might still be able to pull the data in with Get External Data From Web. You may need to throw a proxy page (ASP.NET, PHP, whatever you're comfortable with) in-between that understands JSON and transform it into an HTML table. Get External Data From Web will definitely understand how to read data from a standard table.

Once you have the data in a normalized sheet in Excel, it should only be a matter of inserting a Pivot Table that uses that range as it's data source.