Analysis Services querstion... 1

[SQLBI]

Hi,

My organisation is currently looking for an OLAP solution to provide both Financial and Operational data analysis.

Presently all of our data resides in a SQL Server 2000 DB and ideally we want individuals at all levels of technical ability to be able to access this data according to their requirements.

I have exrtensive experience of Hyperion Essbase and Business Objects, but feel that within SQL Server lies the ability to deliver most if not all of this functionality at a fraction of the cost of the dedicated OLAP/Warehousing tools.

Therefore, i would appreciate it if any of you could provide answers to the following;

1. Is Analysis Services simply Microsoft's attempt to get into the OLAP market, or is it a method of warehousing worthy of consideration?

2. Does SQL Server allow for automated emails to be sent to various individuals according to a return value derived from a Stored Procedure/Trigger?

3. Is MS Excel/MS Query the only tool required for an end user to access data stored within a particular OLAP cube?

That's it for now, although i imagine that answers to these questions will raise other questions.

Thanks in advance.



Leigh Moore
Business Systems Manager
Vets4Pets Veterinary Group

http://www.vets.co.uk

 

[MDXer]

I'll take a stab at answering your questions. Not having worked with EssBase I can't do comparisons.

1. Is Analysis Services simply Microsoft's attempt to get into the OLAP market, or is it a method of warehousing worthy of consideration?

Analysis Services (AS) is very worthy of Considerations. Analysis Services now leads the market in olap implementations. Analysis services has a number of features that make it worth consideration in large implementations.

1)When processing to a MOLAP datastore AS does not build Null values into the cube resulting in a Smaller cube than the size of the warehouse.
2)AS allows you to target aggregations based upon a number of criteria such as query time, user, number of times a query is ran.
3)AS allows data partitioning. You can specify a partition to target a specific block of data and only process the needed partitions, reducing process time.
4)DataSlicing - Is where you specify what the partition represents. This aids in query performance in that only partitions that contain data relevant to a query are accessed.
5)I have heard a number of Essbase developers comment on the ease of use once they have learned the interface.
6)DSO - DSO is the analsysis services API. It is a very easy to use api allowing for the quick development of automated administrative tasks.
7)Remote Partitions - allows partitions of a ube to be placed on servers other than that which the cube is on. This allows more resources to target a partition in extremely large cubes.

While Analysis Services does not aid in the creation of the datawarehouse, but rather builds cubes from your warehouse (which can sit on a number of source platforms), it does best when the warehouse is based on MS SQL Server.

Personally having worked on one of the largest documented AS implementations I have seen the performance in a worst case scenario. Our Warehouse was 2 Billion rows on an annualized basis with 2 years history, Total warehouse was approx 1 Terabyte, we had one cube that was built from all data in the warehouse which resulted in a 250gb cube after numerous aggregations had been applied. Reporting was between sub second and 5 seconds except in the case of TopN type reports. Other cubes were between 150mb and 2.5gb after aggregations and all performed with subsecond queries.

2. Does SQL Server allow for automated emails to be sent to various individuals according to a return value derived from a Stored Procedure/Trigger?

Yes sql server has the ability to send automated emails. the xp_sendmail extended stored procedure can be embeded in T-SQL to send messages. DTS also has a built in SendMail task which can be used in the DTS workflow.

3. Is MS Excel/MS Query the only tool required for an end user to access data stored within a particular OLAP cube?

No there are many adhoc query and reporting tools that can be used to extract data from AS cubes. Cognos, Buisness Objects, Proclarity and AlphaBlox are some of the larger named tools. In my experience there is no best tool but rather what is best for the needs. Each tool comes with it's own niceities and problems. There are also many smaller excel like tools availale. Analysis services also makes use of the MDX query languange which through the use of some tools, xmla can be used to generate complex datareporting.

From info released it appears that in the Yukon release of SQL that the OLAP and Relational aspects of the microsoft platform become more tightly intergrated and many of the complaints and limitations of Analysis services are addressed.

Hope this helps.



"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[johnherman]

Analysis Services is not a piece of junk. It is a fine piece of workmanship, mostly because it was originally built by someone other than Microsoft !!!

I expect Analysis Services to keep getting better and better and the price (included with SQL Svr) is unbeatable.

Sometimes the grass is greener on the other side because there is more manure there - original.

 

[MDXer]

Analysis Services was originally written by Panorama Software in Isreal. Panorama Currently writes Novaview one of the best and most powerful Analysis Services reporting tools.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[SQLBI]

Thanks for the input guys... might not need Essbase/Business Objects and save GB£30,000 in the long run.


Leigh Moore
Business Systems Manager
Vets4Pets Veterinary Group

http://www.vets.co.uk

 

[pabloj]

Unless you need special modules from EssBase (like Planning and Budget) you'll be perfectly fine with AS, I've done Sales and Financial reporting with it and is excellent.
The same is for BO, and you can use it as a reporting tool over AS with some advanced features not present in Excel, like prompts.

Stick to your guns