Designing Business Intelligence Solutions with Microsoft SQL Server — Question 2

You are designing a partitioning strategy for a SQL Server Analysis Services (SSAS) cube. New data is loaded in real-time into the data warehouse that feeds the cube. Between 10 million and 15 million rows of data are loaded into the main fact table each day from a Microsoft Azure SQL Database.
You have the following requirements:
✑ Maximize cube query performance during business hours.
✑ Ensure that data is available in the cube as soon as possible after it is loaded into the data warehouse.
You need to design a partitioning strategy that meets the requirements.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

Answer options

• A. Partition the cube by day for history, using hybrid OLAP (HOLAP) storage mode. Create a daily partition that uses multidimensional OLAP (MOLAP) storage mode during the day. Reprocess the partition incrementally during the day.
• B. Partition the cube by week for history. Create a daily partition that uses multidimensional OLAP (MOLAP) storage mode. Process the partition periodically to add new data.
• C. Partition the cube by day for history, using multidimensional OLAP (MOLAP) storage mode. Create a daily partition that uses proactive caching during the day. Reprocess the partition in full MOLAP storage mode at night
• D. Partition the cube by day for history, using multidimensional OLAP (MOLAP) storage mode. Create a daily partition that uses relational OLAP (ROLAP) storage

Correct answer: A

Explanation

Option A is the best choice because it combines hybrid OLAP (HOLAP) for historical data with daily partitions that allow for fast query performance and incremental reprocessing during business hours. The other options either do not maximize performance during the day or do not ensure data availability as quickly after loading, which does not meet the requirements as effectively.