Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2013-02-06
3
Medium Priority
?
883 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
[X]
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
3 Comments
 
LVL 12

Assisted Solution

by:Tony303
Tony303 earned 240 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 880 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 880 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

597 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