Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2013-02-06
3
796 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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

839 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