Building a Summary table off an SSAS Cube?

Hi,

Came across the wierdest thing today at a client site and wondered if I am going crazy or they have!

If it wasn't bad enough that I walk in to find the client has spent close on three years trying to deliver a DW and has not delivered one report to the business yet.

I have said bin the project and start again but they aren't listening.

Anyway I come across a presentation layer with facts and dimensions. They take this data and load it to SSAS adding many, many calculations to the atomic database level data.

Report writers are struggling to get very far with their crosstab style reports, due to design and performance, so they have asked for aggregates. Seems fair to me but the proposal is to build aggregate tables from the SSAS data! By the way I have no idea why the cuve isn;t ok for the report writers to use, I have to ask why it's there if they aren't using it.

Has anyone ever come across this approach, of bulding summary tables off summary cubes? (It's cetrtainly a new one on me with over 18years in the BI industry).

I know the proposed architecture (SSAS -> SQL) is technically possible, but I've never seen it attempted either. I would never attempt it.

IMO SSAS is not an ideal source for more complex reporting, but they should be able to get some simple reports completed. I usually start with deliberately simplistic tools like Excel Pivot Tables, to quickly get the data in front of non-technical users and validate the ETL and calculation logic.

I would move all atomic level calulcations back into the ETL and presentation layer SQL database. Then you have many more options for reporting. SQL queries against a Star schema can scale a long way with sensible indexing, avoiding the need for aggregate tables. Its usually more practical to use SQL to provide data for more complex reporting, e.g. charts, dashboards, exception reports.

Good luck!
Mike