Materialized View vs SSAS Cube

4
votes

Here is current scenario - We have 3 tables in Oracle DB (with millions of records) which are being used to generate SSRS reports. These reports are displaying complex data calculation such as deviations, median etc.

  • SSRS fetch data using stored procs in oracle (joining all the 3 tables) based on date parameters
  • Calculations are performed in SSRS and data is displayed in tables and charts

Now, for small date duration, report is getting generated quite fast, so no issues there. When date range is big like a week or 2-3 months, report takes lot of time to process and most of the time it gets timed out as well.

To resolve this issue, I am thinking to remove calculations from SSRS and move them to DB level. Where we can have pre-calculated data which will be served to SSRS reports for faster report generation.

In order to do this, I can see 2 options -

  • Oracle Materialized Views

  • SSAS Cube

I have never used Materialized Views before, so I am a bit skeptical about its performance specially FAST REFRESH issues.

What way would you prefer? MV or SSAS or mix of both?

2
oraclereporting-servicesssascubematerialized-views
tek-tips.com/viewthread.cfm?qid=471338 – Snowlockk
Questions: 1. How often is your data refreshed. 2. Define "a long time" 3. What version of Oracle? 4. What hardware do you have; ie number of CPU cores, observed (as opposed to theoretical) IO bandwidth. 5. Is this a dimensional model? Can you provide some example queries? – BobC
1. Data is refreshed once a day. 2. usually 10-15 mins. 3. 11g. 4. Not sure, its on company's enterprise infra. 5. Its not a dimension model, just plain relational db – Prateek Singh
imo use a mat view, complete refresh off hours. – tbone

2 Answers

3
votes

Data models (SSAS) are great for organizing data, consolidating business logic, and defining how calculations behave in different scopes. They are generally faster to query than the raw data which is what you currently have. There is some caching involved, but you still have to query the data and wait for it to be processed. Models are also most appropriate when you have multiple reports that will be using a common set of data.

With a materialized view, you can shift the heavy lifting of calculation time to the scheduled refresh. Think of it as essentially the same as creating a new table that is refreshed by a procedure. This will greatly improve query times for the report especially if the date column you're filtering on is indexed. Also, the development and maintenance requirements are much lower for this than a model.

So, based on your specifications I would suggest the materialized view.

2
votes

I would concur with the Materialized View (MV) approach. Depending on the amount and type (insert vs update vs delete) would determine if a fast refresh is possible or practical. Counter intuitively, a FULL refresh is often a better approach, since you can better take advantage of set based SQL processing, together with parallelism to build the MV.