Excel-friendly OLAP products
From OLAP
The best-known Excel-friendly OLAP product is Analysis Services, which is included with Microsoft SQL Server. Excel 2007 includes a variety of spreadsheet functions that read data from Analysis Services, but they don’t write back to Analysis Services. Even users of Excel XP, and earlier versions, need to use one of several 3rd-party Excel add-ins to use spreadsheet functions that offer read-write access to Analysis Services. These products include xlCubed (http://www.xlCubed.com), IntelligentApps (http://www.IntelligentApps.com), and BIXL (http://www.bixl.com).
Two other Excel-friendly OLAP products offer a sharp contrast to Analysis Services. My tests show that both products return data to Excel about 100 times faster than Analysis Services does. Unlike Analysis Services, both products can be administered by knowledgeable users, rather than the IT Department.
- TM1, from Applix Corporation (http://www.applix.com), probably was the first OLAP product. TM1 offers approximately 30 read-write spreadsheet functions.
- PowerOLAP, from PARIS Technologies (http://www.olap.com) works much like TM1. The company has partnered with a subsidiary of Hitachi, Ltd. to produce a Japanese version of their product. PowerOLAP offers more than 60 read-write spreadsheet functions.
Each vendor’s spreadsheet functions work slightly differently. But the most-used function for each product looks something like this:
<div style="text-align: center;">=GETDATA(database, cube, member1, member2, …)</div>
To illustrate, this spreadsheet formula could return a number from a cube named “GL” in the Finance database, for account 1234, from the Southwest division, for July, 2006:
<div style="text-align: center;">=GETDATA(“Finance”, “GL”, “1234”, “Southwest”, “Jul-2006”)</div>
Like all Excel formulas, this formula typically would contain cell addresses or range names, not the literal values for each argument.
Citation
Content provided by Charley Kyd of (http://www.exceluser.com)