Working on a data warehouse at a high volume can cause us performance problems to process or query the SSAS Cube.
To remedy this kind of problem, you will have to partition the cube, but not just any old way, you really need to have a well-defined dynamic SSAS partitioning plan.
Let's see how we can design this plan in a BI project ☻
Before starting the implementation of this partitioning plan we will talk in this 1ère part of this article on the advantages and disadvantages of SSAS partitioning.
Advantage:
The default behavior in SSAS is to create a single partition for a measure group. This is certainly not a good practice when you have very large fact tables.
Citing the advantages of multiple partitions:
- Performance improvement - SSAS can query a smaller amount of data by isolating one or more partitions to be queried rather than the entire measure group. SSAS can also query multiple partitions in parallel.
- Flexible configuration - each partition has its own storage mode, for example: MOLAP, HOLAP or ROLAP and the aggregation design. You can choose the optimal storage mode and aggregation depending on the frequency with which the data is queried (amount of data available, frequency of data modifications, etc.)
- Flexible treatment - a partition can be processed separately or in parallel, you can delete a partition without having to process the cube. If you have a partition for the last week, month, quarter, etc., you can process only the fact table rows that belong to the partition, rather than processing the entire fact table.
- Multiple source tables - each partition can optionally specify its own fact table, which allows it to physically divide large fact tables into several tables.
- Improved scalability - you can take advantage of processing a partition on another SSAS server.
The inconvenients
0, none, walo, nada !! in short go for it, what are you waiting for? Consider designing your partitioning plan by following part two of this article.
Leave a comment