Architectural Design Question ?

We currently have a number of SSAS 2000 physical cubes and a single KPI virtual cube that our finance department reports from.A colleague is prototyping a new approach in SSAS 2005 and I’m looking for some feedback.

Please see http://www.dailyware.com/SSAS2005Design.jpg for a high level overview.The lines in red illustrate custom .Net applications that push aggregated data from cubes to some other reporting source (SQL tables, XML?) that our finance department will report from.

Thought that come to my mind include: many points of failure, significant custom development required, none-mainstream, high maintenance cost….

Thanks,

Gary

Same as in AS2000 , in AS2005 you can consolidate several different cubes into a single one using new feature called Linked Measure groups.

Another point here is: you should look into providing your end users not only with static reports, but give them ability for Ad-hoc analysis of your data. There are quite a few applications talking directly to Analysis Services.
For instance, you will see great improvements in Pivot Tables in upcoming release of Office 2007.

As for the building redundancy into your system, you can use ether NLB clustering or MSCS ( microsoft clustering services ) solutions.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

I agree...my concern with the proposed design is the fact that it will make it difficult to use several tools coming out later this year. Moreover, our users have requirements to drill into data and this model makes that difficult.

Thanks for the input,

Gary

|||Let me ask another question....do you consider the attached design to be very non-mainstream and possibly difficult to support?|||

To save maitanance costs assosiated with having many cubes, many customers choose to create a single cube ( where possible) with multiple measure groups.

This is one of the main advantages switiching to AS2005, you can bring several fact tables into a single cube and point your reports and client appications to it.

You would start splitting cube into separate once if your calculation logic becomes complex and begins to slow down query performance.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi Gary,

Edward already provided detailed feedback - I would just add that, what jumped out at me was the KPI (SQL/XML) layer between the cubes and reporting applications. What is its purpose - you can easily create static reports directly from cubes, in addition to the ad hoc analytic capabilities that Edward mentioned?

What I can conceive of, in the context of KPI's, is the need for supporting metadata beyond what is provided for KPI's in AS 2005. Things like the role of a KPI within the framework of a customized Performance Management Scorecard - an example of this would be the Business Scorecard Manager:

http://office.microsoft.com/en-us/assistance/HA012225141033.aspx