Building a Summary table off an SSAS Cube?

View previous topic View next topic Go down

Building a Summary table off an SSAS Cube?

Post  gowest on Tue Jul 09, 2013 10:57 am

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).



gowest

Posts : 1
Join date : 2013-07-09

View user profile

Back to top Go down

Re: Building a Summary table off an SSAS Cube?

Post  Mike Honey on Wed Jul 10, 2013 11:56 pm

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
avatar
Mike Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

View user profile http://www.mangasolutions.com

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum