I've never used OLAP but it was recommended to me as a possible solution to
a business problem. The recommendation came from a non-technical person, so
before I jump into it I'd like some direction as to whether it is even a
possibility.

The problem is around the reports on a large relational database. We
started off by creating stored procedures that would summarize the data for
reports. As reporting needs grew, these procs became very ugly with
multiple temp tables and cursors to resolve relational data in oreder to
return a nice flat dataset for the reporting engine (Crystal). Then the
data started to grow and the reports started to slow down exponentially, so
we created caching mechanisms so that the relational data wasn't being
reported on in real time. Now the reports are slowing down again due to
the ever increasing amount of data and we are looking for a better method to
get the reporting data we need. Is OLAP the way to go?

We also have a mechanism where a client can "extend" the tables with their
own data. We write the data column name they wish to add into a row of our
CustomField table and use another table to track what data they populate it
with (CustomFieldData table). When the data is reported on, our stored
procs read the CustomField table and tack on each custom field as a column
in the report dataset and then populate the data from the CustomFieldData
table. For example, if the client added "EyeColor" as a custom field to the
user data, the user stored proc will return a dataset with columns
representing all of our standard user fields but will also have a column
called "EyeColor". Other clients would get different columns in their data
depending on what custom fields they have specified. We could make a
mechanism that ensures this data is stored in columns somewhere instead of
in rows in 2 linked tables, but it would be preferable to use it as is.
However, I cannot see anywhere in OLAP where you can define columns based on
expressions or based on queries. Is there anyway to do this in OLAP?

yeah throw away crystal; get a couple of olap servers (small 2-proc
machines; not ENTIRELY hard drive dependent; i would be most concerned
about memory capacity)

MDX-- the language that you're missing-- it is a friggin billion times
more powerful than any other language in the world (SQL, VB, C++.. ALL
of these pale in comparison to what's possible with MDX)

and then?? the punchline?

OFFICE WEB COMPONENTS for presentation.

take the XmlData property and jam it into a db.

pretty simple stuff; but quite powerful to have webbased pivotTables