Microsoft Analysis Services
One of the well known Excel-friendly OLAP product is Analysis Services, which is included with Microsoft SQL Server. Excel 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 and BIXL.
Cognos TM1 and PowerOLAP
Two other Excel-friendly OLAP products offer a sharp contrast to Analysis Services. 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.
- Cognos TM1, developed by Applix Corporation now acquired by IBM, probably was the first OLAP product. TM1 offers approximately 30 read-write spreadsheet functions.
- PowerOLAP, from PARIS Technologies 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: =GETDATA(database, cube, member1, member2, …)
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: =GETDATA(“Finance”, “GL”, “1234”, “Southwest”, “Jul-2006”)
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; Excel MVP. Website: exceluser.com
