Hi, all experts here,
Thank you very much for your kind attention.
I am having a question on ROLAP and MOLAP storage modes. In ROLAP mode, we store all data in source relational databases and though then we can gain near real-time data for the queries. But with MOLAP and Proactive caching, we can solve this problem by setting update notifications to the MOLAP cubes. Also, with HOLAP, we can combine ROLAP and MOLAP (store leaf data in source relational database, while aggregations in MOLAP format), thus we can actually get the near real-time data for the queries as well once there is any changes to the underlying source relational database.
I am just a bit confused about the distinct benefts we can differenciate between these aspects regarding MOLAP with proactive caching and HOLAP. (not convinced about the reason to use ROLAP storage mode).
I hope my question is clear for your help and thanks a lot in advance for your advices and help for that and I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
Hello again Helen. MOLAP is the best choice for performance. HOLAP will give you wrong results if you have aggregations in the cube because the leaf records in the fact table will change but the aggregations will not. Avoid HOLAP.
Seems like you have SSAS2005 built on top of your transaction systems(OLTP) and no data warehouse/mart?
If you need real time reports build something with Reporting Services instead of using ROLAP or proactive caching.
I am not sure that MOLAP with proactive caching is a possible scenario. I think it is ROLAP with proactive caching, else you will have a MOLAP cube that only does processing 24 hours a day.
HTH
Thomas Ivarsson
|||Hi, Thomas,
Thank you very much for your kind advices.
I thought the reason to have HOLAP is because they store the leaf data in source relational database, so once they have any changes to the source relational database, then the aggregations in the MOLAP format will be updated automatically. But then now it seemes like there is no reason to have HOLAP as well. Why not just use MOLAP, and keep the source relational database, set the proactive caching for this MOLAP to be near real-time?
Hope my thinking is right. And your further advices are most welcome. I am looking forward to hearing from you shortly.
Thanks again.
With best regards,
Yours sincerely,
|||I'm a little confused by this thread, but here goes.
HOLAP is a wonderful option for keeping query processing times and cube population times down. Performance tends to degrade when querying at the leaf level because you are using ROLAP. With ROLAP, SSAS translates your MDX query into various SQL queries that it submits against your relational database and then uses the results to generate a cellset to return. The tradeoff is cube population times are lowest with ROLAP. MOLAP is the best for query performance but worst for cube population times.
Both MOLAP and HOLAP can get out of synch with your relational database but both can be configured to support proactive caching. MOLAP and HOLAP handle proactive caching differently. I personally find the HOLAP option more appealing, but read the Books Online topic "Choosing a Standard Storage Setting " for yourself to find what works best in your situation.
Regarding SSRS vs. SSAS, I'm not sure why the comparison. SSRS works against both relational and multidimensional database technologies, e.g. SQL Server Database Enging and SSAS. You can have data sets from both types of technologies in a single report if you need to go that route. The proactive caching and storage options above are transparent to any data consumer, be it SSRS, Proclarity, Excel, etc.
Hope that helps,
Bryan
Hello helen. You already have another answere but I thought I should make some comments.
HOLAP does not include any automatic updates of aggregations so when the fact data changes the leaf transactions and the aggregations will be out of synch. If you have a data warehouse that is updated every day, but only once, then this will be no problem but you can choose MOLAP anyway.
MOLAP with proactive caching means that you will have a new version of the cube being built in the background when ever SSAS2005 detects a change in the data. Another way to do the same is to run an incremental update of the cube and the dimensions in a scheduled job that runs several times during the day.
With SSRS2005 you can write SQL-queries against a relational data store and have a real time report if you query your ERP-system(an approach that I do not recommend).
HTH
Thomas Ivarsson
|||Hi, Brian,
Thanks a lot for your very advanced and helpful advices.
With best regards,
Yours sincerely,
|||Hi, Thomas,
Again, your suggestions have been really helpful as well. It gave me more ideas on the OLAP cubes storage modes and help me to have more ideas on choosing very interesting storage modes.
With best regards,
Yours sincerely,
