SSAS - Why is it a good idea to run reports against the cube vs the data warehouse?

I am trying to convince our reporting person to run all reports against the cube instead of the warehouse.  I have come up with 3 reasons why all reports should go against the cube.  Please let me know your thoughts on this matter.

Do you run SSRS against the cube or the Data Warehouse and why?

These are my reasons:
1)      The data warehouse might be changed during the day due to ETL or other processes running so the data may not be insync and the reporting may be incorrect.
2)      The reports will run slower since the cube has been designed to be very quick access for reporting.
3)      It may slow down any processes we have running when there are reports running against it causing locking issues.
Bodhi108Asked:
Who is Participating?
 
liijaConnect With a Mentor Commented:
We build reports both on cubes and data warehouses. It depends on the project, subject - and the resources...

As you can read from the forum, the answer is not that clear.
A few points against yours:
1) OLAP can also be updated during the day! If your DW is built correctly, there shouldn't be problems with updating it daytime. Normally its updated nightly.
2) Not always, it depends on your data and requirements. Transactional data can and should still be stored in DW - and with proper indexing the performance is not a huge issue on reports from DW. However, it hugely depends on your requirements and data.
3) Reading data from DW shouldn't cause locking issues.

Report development is also a huge question. Keep in mind the learning curve of MDX. Its a big step for any report developer suddenly to start to build MDX reports. Its complex. And there are not that many good articles on how to build complex reports, with complex parameters with MDX. You have to learn that the hard way. It really takes time.

And still, you can't do everything with MDX that you are able to do with Transact SQL. Well, vice versa also. If you are master of MDX you can do wonders with it. But some of your reports might be impossible to convert into MDX. So mind the cost of the conversion also. Could you gain more with less cost with performance tuning your current DW and the reports?
0
 
Tony303Connect With a Mentor Commented:
Here is a good article....and debate...
Pick the good bits from it...

http://www.sqlservercentral.com/Forums/Topic1223405-363-2.aspx

I would prefer to give the report writer cubes.
"There is your data mate, all ready cleaned, arranged nicely and ready to present....off you go."
0
 
ValentinoVConnect With a Mentor BI ConsultantCommented:
What liija said makes a lot of sense, it's not a zero or one story, one is always better than the other one.   No, it all depends on the requirements and specific situation.  Here's one more example.  Imagine the following requirements: develop a report that shows all sales details for product P for the year Y.

Depending on the granularity of your fact tables, this may even not be possible to achieve using the cube.  But let's say you've got all the details in the cube, would it be the most efficient?  I'm afraid not.  A cube is really fast in serving specific requirements: get me the pre-calculated number at dimension intersection ....  With the report requirement above, it would need to get numbers out of a lot of intersections.

However, if you'd use the DWH, the query would be really simple and should also be efficient, is properly designed.  You need one fact table and two dimension tables.  The joins should be fasted thanks to indexes on the FKs.  And when the joins are fast, so will be the filtering.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.