SQL Analysis Services - SSAS

Do you know what the best practices would be regarding SSAS and having real-time reports.

I am not sure if we should be pulling data off our live database or using SSIS to move data periodically to a Data warehouse database before re-processing the cube.

Any advice on what the best approach is would be greatly appreciated.

The most significant balancing act to consider is whether your need for real-time reporting outweighs the performance hit that both your OLAP and OLTP systems will take from using your OLTP data source directly as fact and dimension tables, and then bouncing ROLAP/HOLAP queries off of them. If the tables are all quite small, and the server isn't under heavy load already, then the penalty is probably negligible. If SSAS queries are going to kick off 500MB+ reads, then that's a problem.

And unless you're doing some kind of high-frequency trading, you probably don't need to have your SSAS database that up to date. It seems like SSAS is more useful for big-picture kind of summaries, and if the past few hours of data aren't included yet, it's not going to make a big difference to those running the reports (ask around to be sure, obviously).

We load our data warehouses and process the cubes each night, and that's typically plenty. We also have a few simple reports built against OLTP tables for viewing more targeted, up-to-the-minute data (stuff that's really more OLTP than OLAP in nature).

StackExchange Database Administrators Q#11906, answer score: 4