You design a Business Intelligence (BI) solution by using SQL Server 2008. You create a SQL Server 2008 Analysis Services (SSAS) solution. The solution contains a cube that has a measure named SalesAmount. The measure contains customer sales data for the last six months. The cube has a single partition that has the storage property set to real-time hybrid online analytical processing (HOLAP).
Queries against the cube must return current sales data that is entered one hour before cube processing. The partition takes two hours to process and the response time for the queries is slow. You need to improve the cube processing and query response time.
What should you do?
A.
Change the storage setting of the partition to multidimensional online analytical processing (MOLAP).
B.
Change the storage setting of the partition to real-time relational online analytical processing (ROLAP).
C.
Create a partition for each customer. Set the storage setting of every partition to low-latency multidimensional online analytical processing (MOLAP).
D.
Create a partition for every month. Set the storage setting of the partition for the current month to low-latency multidimensional online analytical processing (MOLAP) and that of the other partitions to MOLAP.
Explanation:
Tip: “to improve the cube processing and query response time” = “every month … MOLAP”Low-Latency MOLAP Detail data and aggregates are in multidimensional storage. When Analysis Services is notified that the aggregates are out-of-date, it waits for a silence interval of ten seconds before beginning processing. It uses a silence override interval of ten minutes. While the cube is processing, queries are sent to a proactive cache. If processing takes longer than 30 minutes, the proactive cache is dropped and queries are sent directly to the relational data source. This provides fast query response, unless processing takes longer than 30 minutes. Maximum latency is 30 minutes. This setting is best in situations where query performance is important but data must remain fairly current.
Medium-Latency MOLAP Detail data and aggregates are in multidimensional storage. When Analysis Services is notified that the aggregates are out-of-date, it waits for a silence interval of ten seconds before it starts processing. It uses a silence override interval of ten minutes. While the cube is processing, queries are sent to a proactive cache. If processing takes longer than four hours, the proactive cache is dropped and queries are sent directly to the relational data source. This provides fast query response, unless processing takes longer than four hours. Maximum latency is four hours. This setting is best in situations where query performance is important and a bit more latency can be tolerated.
Automatic MOLAP Detail data and aggregates are in multidimensional storage. When Analysis Services is notified that the aggregates are out-of-date, it waits for a silence interval of ten seconds before it starts processing. It uses a silence override interval of ten minutes. While the cube is processing, queries are sent to a proactive cache. The proactive cache is not dropped, no matter how long processing takes. This provides fast query response at all times, but it can lead to a large latency if processing is long-running. This setting is best in situations where query performance is the most important factor and a potentially large latency can be tolerated.
(McGraw-Hill – Delivering Business Intelligence with Microsoft SQL Server 2008 (2009))