Defining SSAS Storage, Partitions, and Aggregations 8. 9. 176 Use SQL Server Profiler to connect to Analysis Services. In SSMS, connect to the Database Engine. Right-click the table dbo.FactInternetSales in the AdventureWorksDW database and choose Open Table to browse its data. 10. Go to the last record in dbo.FactInternetSales and change the value in the Sales Amount col- umn; for example, you might increase it by 1,000. 11. Navigate back to SQL Server Profiler. You should see a trace notification event, and in 10 seconds, additional events should follow, signaling that the server has started processing the MOLAP cache. 12. In the Cube Browser, refresh the report and notice that the Internet Sales Amount total for year 2004 has changed. The reason is that when proactive caching is enabled, the server automatically rebuilds the affected multidimensional structures. Quick Check 1. Why can partitioning improve query performance? 2. What storage mode would you select to leave the detail data in the relational database and store the aggregations in the cube? 3. What types of notifications can you use with proactive caching? Quick Check Answers 1. Partitioning can improve query performance because it can reduce the amount of data the server has to scan to satisfy queries. 2. Hybrid OLAP (HOLAP) doesn't copy the data and stores the aggregations in the cube. 3. Proactive caching supports SQL Server, client-initiated, and scheduled polling notification options. Designing Partition Aggregations Estimated lesson time: 40 minutes No matter how you slice data, the cube always appears to contain every possible aggregated value. In reality, the aggregated values may not be stored in the cube. The server can derive the aggre- gated values in one of three ways: · Assuming that the data has been requested before, the server can retrieve it from an internal cache. · If the data is not found in the cache, the server searches for pre-calculated data aggregations stored in the cube. · If no suitable aggregations are available, the server has no choice but to get the data from the partition and aggregate it on the fly. As you can imagine, designing and building useful aggregations can speed up query response times tremendously. Understanding Aggregations Aggregations are pre-calculated summaries of data. Specifically, an aggregation contains the sum- marized values of all measures in a measure group by a combination of different attributes.