Dynamic Partition Creation in SSAS multidimensional using the SSAS Partition Manager |
<
There are plenty of blog entries out there that outline how to dynamically create partitions in SSAS multidimensional databases. However, all of them suffer the same drawback: they always hard-code some part of the logic into the programming language in the form of hard-coded names or hard-coded partition queries. None of them provide a generic solution that you could just pick up and apply to any project. Perhaps this is a reflection of the usual implementation language of choice: SSIS. Let's face it, SSIS is famous for been brittle; packages break when moved to a new environment. They are rarely reusable without modification. Generating SSIS packages from BIML has solved some of these problem, but even BIML does not handle C# script tasks well; it is rather like painting a room through a keyhole!
Like many business intelligence consultants, I take example scripts from project to project, each time extending their functionality a little more or adapting them to meet new requirements. For many years, I have used C# and AMO to create partitions and process cubes. Indeed, I have presented on this at SQLBits. I do find the AMO object model extremely intuitive and C# much easier to code than doing the equivalent partition creation logic in an SSIS package.
After several iterations, I now have an entirely generic piece of C# code which will work in any situation. The only configuration required is to change the content of a set of tables and edit two views so they generate the correct MDX member keys to match the date dimension in your cube. Fortunately, this is very simple and very much in the realm of the average business intelligence consultant as it is pure T-SQL.
Once configured the SSAS Partition Manager will add partitions to all cubes in all databases listed in the SsasPartitions view, so it will work out of the box even in multi-cube, multi-database environments. The application will also delete partitions that are no longer required, thus removing redundant data from the cube.
To illustrate, I have chosen to partition each measure group in the AdventureWorks sample cube by different criteria. So the Internet Customers measure group is partitioned by year, the Internet Sales partition by quarter, Internet Orders and Reseller Orders partitioned by month, the Reseller Orders partitioned by week. Finally, the Sales Order measure group is partitioned by a hybrid scheme whereby years 2014 onwards have monthly partitions and prior years are partitioned by quarter. Of course, all this partitioning is completely over the top for the tiny amount of data held by the AdventureWorks sample cube, but does illustrate the power of the SSAS Partition Manager.
All this is encapsulated in the SsasPartitionDefinitions view which allows you to review your configuration (shown below).
Note that the PartitionStartDate is set to 2011-01-01 for all partition schemes, but this could easily be changed by editing the SsasMeasureGroup table.
The PartitionSliceIsRange column is a powerful new feature of my latest implementation and discussed below.
Correctly setting the partition slice property of each partition helps the storage engine eliminate the partitions that do not contain relevant data. Referred to as partition elimination, this greatly increases the performance of MDX queries as not as much data needs to be read by the SSAS storage engine. This is one of the main reasons why cubes should be partitioned. However, this does rely on the end-user using the dimension upon which the partitioning is based in their MDX queries. Partitioning by the date dimension is the typical approach. But be warned, cubes often contain several date dimensions all playing different roles (e.g. order date, delivery date, ship date etc.), so be careful to base your partitioning strategy on the right date dimension. I have known situations where the cube designer inadvertently picked the wrong date dimension and so the storage engine could never employ partition elimination. Big mistake! Huge!
The auto-slice functionality in SSAS does not always get it right. During processing, SSAS internally identifies the range of data that is contained in each partition by using the Min and Max DataIDs to set the partition slice. Unfortunately, DataIDs are assigned during dimension processing as new members are encountered and we have no explicit control over how they are allocated, so because SSAS just looks at the minimum and maximum value of the DataID in the partition, you can end up reading partitions that don’t contain relevant data. Thus it is always best practice to explicitly assign a value to the partition slice property of each partition.
You can set the partition slice property of a partition to be a single MDX member, or a set of members. However, MDX ranges operators (i.e. :) are not allowed. So, for example, the following two statements work fine:
{[Date].[Calendar].[Month].&[2014]&[4]}
{[Date].[Calendar].[Date].&[20140101],[Date].[Calendar].[Date].&[20140102],[Date].[Calendar].[Date].&[20140103],[Date].[Calendar].[Date].&[20140104],[Date].[Calendar].[Date].&[20140105],-- etc. --,[Date].[Calendar].[Date].&[20140131]}
Whereas the following range statement will cause the partition processing to fail:
{[Date].[Calendar].[Date].&[20140101] : [Date].[Calendar].[Date].&[20140131]}
Of course the above MDX statements were generated from the Adventure Works cube date dimension.
If you get your partition slice wrong, you will get an error such as: "Errors in the OLAP storage engine: The restrictions imposed on partition slice where violated". Note that you will ONLY get such errors if the partition contains over 4096 rows which is the IndexBuildThreshold defined in msmdsrv.ini file. In the Adventure Works database, partitions rarely get above 5000 rows, so the partition slice setting is ignored. In reality, the Adventure Works database has such low data volumes there is no need to partition the cube, but then it is just a sample database!
For many years now, I have used a set of SQL tables to hold the definition of each partitioning scheme required in the cube. A single view then combines the content of these tables with the date dimension to create a list of partitions that need to be created in the cube. This partition definition view even generates the MDX required to set the partition slice property correctly. Thus the SSAS Partition Manager program is entirely generic; it simply connects to the SSAS database and compares the partitions it finds in each measure group with the list of desired partitions in the partition definition view: SsasPartitions.
Instead of holding one row per partition in a static table, we get the partition definition view to combine the underlying partition definition tables to generate the list of partitions we need. A CROSS JOIN to the date dimension simply fills in the gaps. The view is entirely dynamic, giving you all the partitions you need for today. Tomorrow the view will add on any new partitions as they are required by the partitioning scheme: monthly, quarterly, yearly.
This view is read by the SSAS Partition Manager program which compares the partitions in the cube with the ones listed by the view. If the partition does not exist in the cube, then it is created. Note that the C# program does not update existing partitions. It simply creates new ones or deletes redundant partitions.
The clever thing about this view is not only how it uses date dimension to generate a list of all partitions up to the present day, but also uses it again to generate the list of keys for MDX ranges. Note the content of the PartitionSlice column in the case of the Internet Orders measure group. This was generated because the PartitionSliceIsRange flag is set to True (1).
Note that the view is entirely dynamic, giving you all the partitions you need for today. Tomorrow the view will add on any new partitions as they are required by the partitioning scheme: monthly, quarterly, yearly. This is because the SsasPartitions view only generates partitions between the PartitionStartDate and today's date. Unfortunately the AdventureWorks date dimension table ends in 2014, so we never get any new partitions been created as we roll over to the new week, month, quarter or year. Clearly this would not be the case in a true production cube!
Once the SSAS Partition Manager has been run against the AdventureWorks cube, this is what you see in SSMS:
I provide the T-SQL for these database objects as a SQL Server Data Tools (SSDT) project called SsasPartitionDefinition which is part of the download for the SSAS Partition Manager hosted on Codeplex. Note that I include DimDate from the AdventureWorksDW2014 database just so my SSDT project compiles correctly.
The Populate_Partition_Definitions.sql file populates the three partition definition tables with the partitioned measure groups from the AdventureWorks cube. This can easily be adapted to your own cube(s).
Note that this table structure supports many measure groups in many cubes in many databases, so with one run of the SSAS Partition Manager you can create partitions in hundreds of cubes, all with the correct partition slice set!
To get yourself up and running with the SSAS Partition Manager this is all you have to do:
All this is explained in detail on the following page: Configuring the SSAS Partiton Manager
The first step taken by the SSAS Partition Manager is to validate your configuration. Thus it will check that all the measure groups exist in the relevant cubes in the relevant databases. It even checks the measure groups have a template partition with its where clause set to WHERE 1=0. All this is logged along with the creation/deletion of each partition. So check the log files to ensure your initial configuration is correct.
The SSAS Partition Manager is available for download from Codeplex. I would be glad to have your feedback on the project via the project’s discussions tab.
| Комментировать | « Пред. запись — К дневнику — След. запись » | Страницы: [1] [Новые] |
