Professional Microsoft® SQL Server® Analysis Services 2008 with MDX

21.5. No Latency Scenario

In this short latency scenario you are in charge of an eCommerce site that provides customers' links to the most up-to-date products on the Web, which when sold, provide you with a commission. Your Internet affiliates are adding additional products to your catalog electronically, and at this point you are at 2.3 million product SKUs and the number is rising. Meanwhile, your partition data is changing frequently, and you have large numbers of members in the product dimensions. What does a BI application developer do?

21.5.1. Real-Time ROLAP Storage Option

The recommended solution here would be to set up the measure group and dimension data (which are frequently changing) to use ROLAP storage mode so that data is automatically retrieved from the relational data store as needed by the user. Working in this way does not come without a price. Indeed, although it is definitely a useful storage mode, the query performance of ROLAP mode is much slower than that of the MOLAP mode. In general we would always recommend a MOLAP solution for large dimensions, but if your dimension members are constantly changing and these changes need to be reflected immediately to end users, ROLAP would be a better option. This is because the data is being retrieved directly from your relational data source, which often requires over-the-net communication. Yes, you could go with HOLAP, but performance depends largely on your aggregated data and how frequently it is impacted due ...