Hi
I have a pretty vague and "open" question that I would like to get
some comments on, and I beleive that it might be useful to others.

The company I'm working for has a lot of Excel spreadsheets that
different people use, more or less isolated from each other. Now the
company is putting in a business warehouse (At the same time we are
going from thick clients to thin clients), and therefore I have been
asked to look into a matter concerning this. Instead of people using
isolated excel spreadsheets that consist of data/information or that
receive data/information from other sheets or systems, the idea is
that maybe the company as a whole and also the users can work more
effecient if some of the spreadsheets (or the data/information in the
spreadsheets) can be replaced by building OLAP cubes that "get"
data/information from the datawarehouse.

I am not sure how to adress this matter and would therefore be
grateful for comments/suggestions about this! I am a beginner when it
comes to datawarehouses and OLAP. What I want to know are things like:
-Is the above basic idea a good/reasonble one?
-How are OLAP cubes used to get data/information from a datawarehouse
and how can Excel be used to view and work the data/information that
the OLAP cubes supply?
-Is some other program/system needed (I have for example read
something about Essbase) for this matter?

If this text is too vague I can try to be more specific about
something in the matter!
TIA
John

As you mention yourself this a fairly large question.

First of all without knowing the company I would say it's a brilliant
idea...however i'm an self imployed DW consultant - so what
would you expect me to say..

* You have to examine the informtion in the excel sheets - you will propably
find out that some of the information in these sheets
are information already stored in databases somewhere and several users
spend time putting the data into excel - by automating
the transport of the data with the dimensions needed into a datawarehouse
you could "cleanse" the data and get one language
for providing them to the users.
* An example could be the financial data ... lots of users have benefit of
these.
* You mention Essbase...sounds like Oracle...don't know much about it - use
Microsoft Analysis Services .. comes with the Microsoft SQL
Server.
* You will need Analysis (or Essbase) to store data in cubes.
* You say the cube get data from the datawarehouse...would rather put it
this way....transaction system --> relationel dw-bases for cleansing -->
olap-base
* Excel will be able to connect to an Analysis Olap cube right away (from
excel or by using the web component on an intranet) ... but there are also
some third party clients you could use to see the cube data
* When putting the data into the cube some of the calculations previously
done ad hoc could perhaps be done in the olap cube with using MDX
(calculated members)

\Michael Vardinghus

John <mrnilsson (AT) yahoo (DOT) com> wrote

John,

I agree with Michael and would like to advise u just download the free
full working Trials of both Microsoft Analysis Services and for
example a third client tool like Panorama NovaView and you can make
your own OLAP cubes based on data in your own production dwh and
publish test and show it in your organisation. On our website
www.gmsbv.nl you find more info and you can order the free trials.

Regards,
Marco


"Michael Vardinghus" <mivar (AT) wmdata (DOT) dk> wrote

John,
The idea is a very good one and one which many companies have been
successful in. The real critical thing is trying to get to a
consolidated data model from the myriad of spreadsheets which can be
challenging to say the least. Couple this with then trying to
reconcile the resulting cube to the original spreadsheets its can be
non trivial. In one recent case with a large customer we converted a
single spreadsheet with 95 sheets and 80MB in size (with a new
supposedly same version every day) building the cube was relatively
easy as the data was well organised but tracking down the 3 or 4 cells
which had incorrect formulae hence nothing reconciled (and which has
never been spotted before) was a nightmare. The customer now has
spreadsheets that deliver the same output but now consistently and has
about 8 sheets (as 90 of the sheets were variations on a theme) and a
cube they can slice and dice within the environment they know and
love.

Nigel Pendse recently wrote a white paper entitled

"Microsoft Analysis Services + Full Function Add-ins -The perfect
combination?"

where he discussed the subject and outlined some of the best solutions
to the problem.

The outline conclusion was that using a full function add-in and AS
removes many of the issues with Excel and when done well (which is a
non trivial task) provides a very powerful tool.

You can get a copy of the paper either in precis or in full by
emailing info (AT) xlcubed (DOT) com.

I'd reccommend that you take a look if you are getting into this area
as its full of interesting information.

This newsgroup contains quite a few old threads with respect to excel
add-ins so I would also reccomend you taking a look through those.

Hope this helps.


Mark Scanlon
XLCubed