CHAPTER 7 Managing SSAS Storage, Processing, and Deployment

Chapter Summary

• A measure group stores data physically in partitions. Partitions in a measure group can have different storage modes and aggregation designs.
• SSAS cubes perform best with MOLAP. Consider using HOLAP with very large and infrequently used partitions. Consider ROLAP to implement real-time partitions when you cannot use proactive caching.
• Aggregations are recalculated summaries of data. You might gain a remarkable performance increase by implementing useful aggregations. Useful aggregations are a byproduct of a good dimensional design.
• At design time, use the Aggregation Design Wizard to design aggregations using a cost/benefit algorithm.
• SSAS supports various deployment options to meet different management needs. During design time, use BIDS to deploy and process the changed object automatically.
•  Deploying changes to a test or production environment typically requires more granular deployment. You can use the Deployment Wizard to preserve the existing management settings on the target server.
• Use the Synchronize Database Wizard to sync metadata and database changes between two SSAS databases.
• The SSAS processing architecture supports different processing options to minimize the database processing time. Processing a database fully is the easiest and cleanest way to process all objects contained in the database, but it might also take the longest.
• Proactive caching provides various advanced settings and notification options to meet different data latency needs.

Lesson 1: Defining Measure Group Partitions and Aggregations

1. How can partitioning improve query performance?

Partitioning can improve query performance by reducing the amount of data the server has to scan to satisfy queries.

2. What storage mode would you select to leave the detail data in the relational database and store the aggregations in the cube?

Hybrid OLAP (HoLAP) does not copy the data and stores the aggregations in the cube.

3. What types of notifications can you use with proactive caching?

Proactive caching supports SQL Server, client-initiated, and scheduled polling notification options.

4. How can aggregations improve query performance?

Aggregations can improve query performance because the server can satisfy queries from aggregations rather than having to scan and aggregate the partition data.

5. Which aggregation design option should you use at design time?

At design time, use the Aggregation Design Wizard.

Lesson 2: Deploying SSAS Objects

1. Does BIDS deployment retain the partition design and security settings on the target server?

no, BIDS deployment overwrites the target database.

2. Which deployment option would you use to deploy your local changes to a production database without overwriting the partition and security settings?

To deploy your changes to production, you would use the Deployment Wizard, which gives you more control over the deployment process than BIDS does.

3. Which deployment options can you use to synchronize a production database with a staging database?

The easiest option for synchronizing a production and a staging database is to use the Synchronize Database Wizard. But you could also execute a Synchronization XMLA command or backup and restore the database.

Lesson 3: Processing SSAS Objects

1. What is the difference between the Process update and Process Add options when they apply to dimensions?

The difference between Process Add and Process update is that the Process Add option detects only member inserts and Process update detects all changes.

2. What is the easiest way to process all objects in a database?

The easiest way to process all objects in the database is to process the database with the Process full option.

3. Why would you use the processing query with scheduled polling notifications?

you would use the processing query to support incremental processing of partitions instead of full processing when only new records are added to a partition.

Implementing Low-Latency OLAP and Deployment Strategies

Case scenario

1. You process the Adventure Works cube on a monthly basis as a last step of the ETLprocess that populates the AdventureWorksDW2008 database. However, some businessusers have reported that they would like to analyze sales data in real time. Whena new Internet order is placed in the sales system, a lightweight ETL process entersthe order data into the AdventureWorksDW2008 database. You need to enhance theAdventure Works cube to support low-latency reporting. In addition, based on somepreliminary testing, the QA users have reported inadequate query response timeswhen they browse the Adventure Works cube. You need improve the cube performance.How would you implement a low-latency partition to let business users browsethe daily order data?
2. For the same scenario, what can you do to optimize the cube performance?
3. As an administrator, you follow an iterative design, test, and deploy cycle to implement a cube. You use BIDS to design the cube and test it locally. Periodically, you need to deploy your changes to a test server for user acceptance testing. To handle high reporting volumes, the production SSAS servers are load-balanced. Instead of processing all production cubes individually, you want to process the cube on a staging server and then deploy it to the production servers. Only new members are added to a large dimension, and you need to minimize the dimension processing time. What deployment options would you use to deploy the cube to testing and production environments?
4. For the scenario described in question 3, what processing option would you use to add only new members to a dimension?

1. You could partition the Internet Sales measure group and set up a low-latency partition. The definition slice of the low-latency partition could filter the sales data for the current month only. In addition, you could enable proactive caching on that partition so that you do not have to process it explicitly.

2. Start by optimizing the dimensional design of your cube. Make sure that you have set up correct attribute relationships and have defined useful user hierarchies. Set the Aggregation-Usage property of infrequently used attributes to None. Run the Aggregation Design Wizard to create the preliminary aggregation design. When the cube has been deployed, you can turn on query logging and then use the Usage-Based Optimization Wizard, to tune the aggregations that are designed for the cube.

3. If you want to retain the partitions and security settings on the test server, your best deployment option is to use the Deployment Wizard. Rather than processing the production cubes individually, consider processing the cube on the staging server and then using the Synchronize Database Wizard to synchronize each of the production cubes with the staging cube.