Pros and cons of choosing MS OLAP Services 2

[jnicho02]

This was actually a question by Pewo in the 'what is OLAP?' thread, but I though it deserved it's own discussion.<br>
<br>
"why pay a huge sum of money for an OLAP tool when MS provides it in an all-in-one package? The only reason I can come up with is that i) the MS OLAP technology is very new, therefore it has not been proven yet ii) it is free so it cant be that good and iii) anyone wants to add anything in this "

 

[bitbrain]

I do not have experience with MS OLAP but consider the following:<br>
<br>
"OLAP" refers to a specific class of functionality (see previous threads) that end-users/analysts use for decision support (DSS).<br>
<br>
OLAP tools usually sit on top of some sort multi-dimensional database (MDD).<br>
<br>
There are different ways of implementing OLAP/MDD:<br>
<br>
Relational OLAP - ROLAP - uses a conventional RDBMS. Data tables are structured using a star and/or snowflake schema. The OLAP tool needs to understand this type of data structure.<br>
<br>
In the case of ROLAP, the DBMS (which was originally engineered for transaction processing) has been adapted to accommodate DSS.<br>
<br>
Other proprietary tools have been developed that were specifically engineered for DSS. They tend to fall into two categories:<br>
<br>
Some MDD databases pre-calculate aggregate information as the db is loaded. These databases take longer to load but query performance is fast.<br>
<br>
Other MDD database systems store the raw information and calculate aggregates "on the fly" as queries are executed. Load & update tasks are easier but you may need more horsepower to keep the query performance acceptable.<br>
<br>
To the best of my knowledge, Microsoft tools fall into the ROLAP category.<br>
<br>
Which model is best is debatable and depends on a number of variables. I would suggest that for serious DSS you may be better off going with a tool-set that was engineered from the ground-up for DSS.<br>
<br>
Keep in mind also that the bulk of the cost of building a DSS system is not with the software/hardware but with development & support (people).

 

[jnicho02]

Further info on MS OLAP modes.....<br>
<br>
MS OLAP can be set up with MOLAP, ROLAP or HOLAP<br>
<br>
MOLAP - Multidimensional OLAP - all the combinations are pre-calculated<br>
ROLAP - Relational OLAP - an OLAP view is given to a RDBMS<br>
HOLAP -Hybrid OLAP which is a combination of the two. The system decides in advance to pre-calculate some of the combinations.<br>
<br>
bitbrain, I agree with your comparison of MOLAP and ROLAP. Maybe the HOLAP option will allow the "best of both worlds"?

 

[bitbrain]

jnicho02 - being an ex-dba you are probably as well versed in the "other considerations" as anybody is. For example:<br>
<br>
&gt; data volume<br>
&gt; frequency of updates<br>
&gt; data volume of updates<br>
&gt; "window" (time) for applying updates<br>
&gt; number of users<br>
&gt; query volume<br>
&gt; commonly accessed data - common queries<br>
&gt; horsepower of server(s)<br>
<br>
I think this is where data warehousing becomes an art as well as a science. There's no such thing as one best solution.<br>

 

[Guest_imported]

doe's anyone know hoe to get OLAP to display two sets of measures for different periods in one cube? i tried to created privated time dimantions and created a virtual cube but with no luck

anyboby?

 

[jnicho02]

visitor w,
You need to start a new thread if you want to ask a new question. You will also have a better chance at getting an answer as it will be more visible. My Home -->

http://www.geocities.com/jnicho02/

 

[Welshbird]

Answer for Visitor W:

Create two dimensions, named

time.weekly
and
time.monthly
in the same cube, and see what happens. - Should be what you need? - Start a new thread and be more specific if this doesn't help.



Fi.

 

[keefy]

jnicho02,

This is a common question that I have been asked on many client sites (I'm a consultant), and not just for OLAP. The same is also asked of ETL tools when MS SQL Server comes with Data Transformation Services (DTS) built in.

Well, to keep it simple, the Microsoft OLAP offering has traditionally not been anywhere near the standard set by the more established OLAP vendors e.g. Hyperion. The level of functionality has not been as complete, and of course it is tied to SQL Server, making it a non-starter for those applications that are accessing data from other RDBMS systems.

Having said that, the latest version of OLAP Services (since renamed to Analysis Services) embedded into SQL Servder 2000 does represent a significant upgrade in performance and functionality.

So, I would consider it a worthwhile solution if I had already selected SQL Server 2000. Otherwise, I might be persuaded to look elsewhere, particularly if my resulting application requires a deep level of multidimensional functionality.

 

[tresgatos]

why would anyone pay big money for similar products?
Well, as good as Analysis Services is (and it is _very_ good) here's an issue or two:
It has this tendency to lock your enterprise into using MS infrastructure. For example the fact that you are forced to use the MS Lanman based authentication in order to use it. (compare to sql server that at least lets you use separate user ids.)
Also there are scalability concerns. You aren't going to be running MSAS on an E10000 any time soon.
And then there's the issue of getting adequate support from MS for enterprise strategic apps.