To "Excel" or not to "Excel"? 1

[SQLUser]

Hi,

Can anyone tell me where Excel lacks as an OLAP reporting tool? What are the major show-stoppers?

I know for sure that Excel is very popular among users and requires minimal training. I have a project coming up and I'm contemplating on using Excel.


Thanks!
X-)

[sig][/sig]

 

[Guest_imported]

excel is an excellent platform since most people in your office are already familiar with it.

 

[gkotrick]

One issue that I ran into using Excel's OLAP is that you have few options when creating aggregations on columns. For example, you cannot create an average or a percent of a column using the wizard. This can cause column total information to be useless if you use certain formulas in the source database within the rows. For example, Col1 / Col2 = Col3 would essentially return a percentage in Col3. This formula cannot be duplicated when it comes time to generate the column total which I believe gives you the options of SUM, MIN, MAX and COUNT.

Will you be using the OLAP wizard against a relational database or an OLAP database? I've had no problems with the wizard against relational databases and the macro recorder can be used to gain some insight as to what happens in order to create the cube. You can then "tweak" the code as needed or use the code to automate the process. If you are going against an OLAP database the macro recorder records nothing.

 

[Guest_imported]

There is an Excel front end application in the links of this forum

http://www.visualolap.com

cheers

 

[olapman]

Hello SQLUser,

Excel as an OLAP reporting tool does have its' issues.

1. The first issue is where are you going to get the data from. If the data only resides in Excel, than you are limited to the Excel memory space, thus reducing the amount of data you can look at. If the data resides out-side of Excel, like SQL Server, than you do not have this restriction.

2. Excel is the most popular analytical interface for OLAP today. That is, is is used by "power users" Using and creating an Excel Pivot table is not for executive users, but for those familiar with Excel. There are limitations within the pivot table enviroment include: read-only access; fixed grid display; limited formatting; etc..

3. One popular approach is to use an Excel OLAP add-in that extends the Power of Excel to be used as an OLAP reporting tool. There are many products with different price points in this space, some work specifically with Microsofts OLAP Services Product offering.

4. If the data resides in another OLAP database, you may be able to access the data from within Excel and report on it if, and it is a big IF, it supports OLE DB for OLAP. Meaning, that it will connect to an Service Provider. There aren't many out there. The list is basically the Microsoft OLAP and SQL product and Applix TM1 Server.

Hope this info helps.

OLAPman

 

[SQLUser]

Thanks!

 

[Peterstr]

If you are interested in reading single cell values and dimension member properties from a cube as well as writing back values to cube leaf-cells, have a look into ReadWriteCube for Excel (

http://w1.319.telia.com/~u31915637).

Works on cubes built by AS 2K.

Regards,
Peter