Solved

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

Posted on 2013-02-06
3
764 Views
Last Modified: 2016-02-18
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.
0
Comment
Question by:Bodhi108
3 Comments
 
LVL 12

Assisted Solution

by:Tony303
Tony303 earned 60 total points
ID: 38862502
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
 
LVL 6

Accepted Solution

by:
liija earned 220 total points
ID: 38862803
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
 
LVL 37

Assisted Solution

by:ValentinoV
ValentinoV earned 220 total points
ID: 38863008
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

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data‚Ķ
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now