Reports in OLAP vs. Excel downloads

[anovak]

We have OLAP working with PS and it is acknowledged that without Admin
privileges, one cannot create a permanent OLAP view. And I don't
warm up to the idea of giving others that rigtht in the server. The
only thing a user can do once they finish their last drag and drop is
save what their model to an Excel spreadsheet, which can be saved to
their desktop and changed / refreshed later on because the data hooks
are still there.

A colleague of mine makes the following statements and I'd like to
know from others' experiences if this is really all true or if it's
just a matter of learning more about how Excel and pivot tables work.
See below....

Please comment on their comments and also comment on a 3rd party
reporting tool....

Thanks,
Andy Novak
UNT

"The issue is that the OLAP cube reporting is so much nicer and
problem free than using reports exported to Excel. For example, the
report that you originally created, when exported to Excel, lost its
ability to “expand” the left most column. I’m attaching a report I
made for user "xyz". It looks real nice, but if they want to drill
down to a specific team they loses her column formatting – just a
hassle you don’t have when you work directly from OLAP. Another quirk
I found is that, in OLAP, I could add or delete columns at will; for
example I originally had Task List Level 02 and 03 and exported it to
Excel. There I colored the columns to match the original report. I
then found that while, in OLAP, the whole report printed in portrait
mode, it was just too much to print in Excel, even in landscape. Now
here is the hitch, I could not delete Task List Level 03 as the field
list only recognized Task. So I delete Task (which deleted 02 & 03)
and reentered just Level 02, but then I lost my formatting . . .
Another thing is that, in Excel I tried to change the title
“RBS_R_Assignment” to “team” or something like that that was more
friendly, but since it was an integrated part of the pivot table I
could not (but could in OLAP). Also, column and row headers come out
in odd places; for example “actual work” was on the left and column,
when it was really a data items in the right columns (not so in OLAP);
I got around it by formatting the title in white, which effectively
made it disappear. The bottom line is that working in OLAP is quick
and fun and working in Excel in clunky and frustrating. The OLAP
reporting tool is such a nice tool, it’s a shame not to let it shine.
Do you know if there is a 3rd party reporting tool?"

 

[Marc Soester [MVP]]

Hi Andy,

where to start.......

There are a couple of things that I would tell the user. Firstly, access the
cube with Excel is essentially the same than accessing the cube with the Data
Analyser. The data analyser is the office web component for Excel Pivot Table
and you will see (especially if you use Excel 2007) that you have much
greater formating capabillity. To answer your users question.
Excel should not loose the abillity to expand. Yes you can change the task
list levels in your excel pivot table view, that is the whole idea to use
pivot tables.

Pivot Tables ( OLAP ) is not so much for printing reports, but more for
analysing the data. It allows you to slice and dice and drill down.
It may be true that when using Excel instead of the data analyser that the
data flow is a little slower, but that very much depens on what level the
user drills down to. The more Data the longer it takes, but my personal
experience is that Excel does not make a big differnce in regards to
performance.

At the end of the day, Data Analyser uses nothing else but the office web
compontents for Excel which is essentially the same then Excel iteself.

There are a couple of 3rd party reporting tools, but I personally think that
Excel is actually very nice to use.

Hope this helps