1

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.

Sandeep Singh Rawat
  • 1,637
  • 1
  • 14
  • 27
  • Could you explain a bit? Where is the data? In a warehouse/database? – Damir Sudarevic Dec 01 '10 at 20:44
  • it is coming from an OData feed. e.g. Products is a feed. http://server/data.svc/Products will return a list of products. All the above mentioned entities are associated with the salesdata feed. – Sandeep Singh Rawat Dec 01 '10 at 21:20
  • One way to achieve this is to write an OLEDB OLAP provider over OData feed and then use excel pivot table to render the data. But it doesn't look like a practical option. – Sandeep Singh Rawat Dec 01 '10 at 21:49

1 Answers1

1

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.

Community
  • 1
  • 1
technomalogical
  • 2,982
  • 2
  • 26
  • 43
  • I'm actually looking for a more generic way of rendering the relational data (similiar to a pivot table). Number of dimensions or hierarchy may extend to a larger number in which case the logic will become too complex. I can do a cell by cell write but if there is lots of data it will be very slow as well. – Sandeep Singh Rawat Dec 01 '10 at 21:32
  • Yes you are right. PowerPivot provides tha capabilities I'm looking for. I need to build a similiar type of extension for Excel. Now I've two options A) either to build an in-memory cube and use OLAP data provider to connect to the Pivot Table or B) find-out a standard algorithm to render the data in a Pivot table like structure. First option seems too heavy to build (though if possible I'd like to build a light weight version of the PowerPivot vertex engine) so I'm actually looking for the option B. – Sandeep Singh Rawat Dec 04 '10 at 11:33