Question on OLAP cubes storage modes

Hi, all experts here,

Thank you very much for your kind attention.

I am having a question on the SSAS2005 OLAP Cubes storage modes. We know SSAS2005 supports 3 different storage modes: ROLAP, MOLAP, HOLAP.

Do all these three storage modes of cubes store data in another physica analysis services databases which are inrelative from their data warehouse (in case they are built on top of the data warehouse)? (so it does not matter at all even we remove the data warehouse?)

Thank you very much in advance for your help and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

Pl refer to BOL for detail explanation.

ROLAP - Data is in data warehouse (relational database). The data warehouse cannot be removed in this case

MOLAP - The data warehouse can be removed as both the data and aggregations are stored in the SSAS server. But if you want to see the updated data, then u may need the data warehouse.

HOLAP - The data warehouse cannot be removed in this case

|||

Hi, Arun,

Thank you very much for your kind and very helpful advices.

But the question is: in MOLAP mode, will it to a big extent the MOLAP cubes will slow down in processing once the underlying data warehouse is updated (in this case, any changes to the underlying data warehouse is informed to the MOLAP Cube, even we have set paralle cube partitions for that)

With best regards,

Yours sincerely,

|||

Depends on type of update you are performing. One way is to enable proactive caching to update the data based on some criteria - but the time taken depends on the amount of data read (plus also other criteria)

for ex, in one of our cubes, we use SQL CLR procs to perform incremental updates to partitions. It is very fast and takes only few seconds to update the MOLAP partitions. We trigger the sproc using messages from SQL broker.

|||

Hi, Arun,

Thank you very much for your kind advices. So as you kindly advised, in you case, you first of all perform updates to the source relational database partitions with SQL CLR procedures. Then again perform the updates to MOLAP partitions which takes only a few seconds. Is that right? It is a very interesting way to do that and I would say very advanced as well (assume I am right in understanding your advices for that.).

Thanks a lot again.

With best regards,

Yours sincerely,

|||

Its partially right. The updates to data warehouse (relational database) happens through many scenarios - SSIS packages, stored procedures etc.

But, I call a SQL server SP towards end of each of the events. That SP would post message to SQL broker Queue. (The purpose of doing this is to make all the process asynchronous). The target queue has an activation SP that will then invoke the neccesary CLR SP which would do all updates to SSAS.

The CLR SP uses AMO to do all updates to the cube. It also fires some XMLA queries to do some quick updates.

The above process works very well and the advantage is that its quite fast.

|||

Hi, Arun,

Thanks a lot for your kind advices and detailed explanation.

I guess it is really up to the specific problem. Anyway, very good advices.

Thanks again.

With best regards,

Yours sincerely,

Question on granularity of OLAP cubes

Hi. all experts here,

Thank you very much for your kind attention.

As we all know one of the most significant benefits of OLAP cubes are to preaggregate the data based on different levels of the OLAP cubes. Therefore, in order to optimize the query performance against the huge OLAP data source, we can design the granularity for a OLAP cube and when necessary we can refer back to the underlying relational database sources. But the question here is: Is there any best practices on granularity of the OLAP cubes?

Hope my question is clear for your kind advices and help.

And I am looking forward to hearing from you shortly for your kind advices and thanks a lot in advance.

With best regards,

Yours sincerely,

Hi Helen,

U need to go through with OLAP Design Best Practices for Analysis Services 2005 or Data Warehouse Design Considerations

Bhudev

Question on cube and dimension deployment in production

Hi, all experts here,

Would any of you here shed me any light on how could we deploy cubes and dimensions to other server rather than the one where they are created? And also what permissions at least are needed to be set up for them to be accessed in production enviornment?

Hope it is clear for your help.

Thanks a lot and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

Hi Helen,

Take a look at the functionality of the Deployment Wizard: http://msdn2.microsoft.com/en-us/library/ms176121.aspx

Regarding permissions, do you mean administrative access or the ability to query data? In either case BOL is again the best place to start: http://msdn2.microsoft.com/en-us/library/ms174517.aspx. Or do you mean the ability just to connect to the cube at all? In which case this new paper on connectivity problems might help - it discusses everything might might stop you from connecting:

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/CISQL2005ASCS.mspx

Regards,

Chris

|||

Hi, Chris,

Thanks a lot for your very kind help. Definitely they've been very helpful to clear up my confusions and questions on that.

Yes, I am concerning about being able to access the cubes and dimensions after they are deployed to other production environment.

Thanks a lot again.

With best regards,

Yours sincerely,

Question on attribute relationships and hierarchies on SQL Server 2005 AS Cubes

Hi, all experts here,

Thank you very much for your kind attention.

I am having a question on the attribute relationships and hierarchies in the cubes. It is a must to create the attribute relationships for each levels in any hierarchies in order to let the UDM to get the correct aggregates over different hierarchies? (e.g. within hierarchy H, levels are a-b-c-d. Do we have to create the attribute relationships for all the levels?, e.g. we have to create the attribute relationship between a and b, b and c, c and d? ). Hope my question is clear for your help and I am looking forward to hearing from you shortly.

Thanks a lot.

With best regards,

Yours sincerely,

Hello. Correct! Your cubes will benefit in performance if you add attribute relations. The way you have described this is correct but you will only need to create the in your natural or user hierarchies(the middle pane in the dimension editor).

HTH

Thomas Ivarsson

|||

Hi, Thomas,

Thanks a lot.

With best regards,

Yours sincerely,

Question on Action on SQL Server 2005 Analysis services OLAP Cubes in Excel 2007

Hi, all experts here,

I am having a question on actions implemented on SQL Server 2005 Analysis Services OLAP cubes, will SQL Server 2005 reporting services report action implemented on those cubes can also be available from Excel 2007?

Hope my question is clear for your advices and help and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

Hello Helen. Actions(SSRS2005 Actions and others) are supported in Excel 2007.

You can find out more about this in this document: http://www.microsoft.com/downloads/details.aspx?FamilyId=2D779CD5-EEB2-43E9-BDFA-641ED89EDB6C&displaylang=en

HTH

Thomas Ivarsson

|||

Hi, Thomas,

Thanks a lot for your help. It's been very helpful.

With best regards,

Yours sincerely,