1215
Chapter 53: Building Multidimensional Cubes in Analysis Services with MDX
53
members, resulting in more detailed analysis or even integration of the analysis application
into a larger data management framework.Drill-through actions use cube data to display drill-through actions. Versions prior to 2008 required access to the
underlying relational data to provide the display of detail data.Partitions
Partitions are the unit of storage in Analysis Services. Initially, the Cube Designer creates a
single MOLAP partition for each measure group.Partition Sizing
Cube development normally begins by using a small but representative slice of the data, yet
production volumes are frequently quite large. You need a partitioning strategy to manage
data, beginning with the amount of data to be kept online and the size of the partitions
that hold that data.The amount of data to be kept online is a trade-off between the desire for access to histori-
cal data and the cost of storing that data. You can partition data in many possible ways,
but a time-based approach is widely used, usually keeping either a year’s or a month’s worth
of data in a single partition. For partitions populated on the front end, the size of the par-
tition is important for the time it takes to process. While partitions are deleted at the back
end, the size of the partition is important for the amount of data it removes at one time.Matching the partition size and retention between the relational database and Analysis
Services is a simple and effective approach. As long as the aggregation design is consistent
across partitions, Analysis Services enables smaller partitions to be merged, keeping the
overall count at a manageable level.Best Practice
Take time to consider retention, processing, and partitioning strategies before an application goes
into production. When in place, changes may be expensive given the large quantities of data involved.Creating Partitions
The key to accurate partitions is including every data row exactly once. Because it is the
combination of all partitions that is reported by the cube, including rows multiple times
infl ates the results.c53.indd 1215c53.indd 1215 7/31/2012 10:30:25 AM7/31/2012 10:30:25 AM
http://www.it-ebooks.info