azure_Azure Analysis Services中的动态分区（表格）

azure

目的 (Objective)

The real-life requirement

现实生活中的需求

Disclaimer: I assume dear Reader, that you are more than familiar with the general concept of partitioning and star schema modeling. The intended audience is people who used to be called BI developers in the past (with a good amount of experience), but they have all sorts of different titles nowadays that I can’t keep up with… I won’t provide a full Visual Studio solution that you can download and just run without any changes or configuration, but I will give you code can be used after parameterizing according to your own environment.

免责声明：亲爱的读者，我想您对分区和星型模式建模的一般概念非常熟悉。 目标受众是过去曾经被称为BI开发人员（ 具有丰富的经验 ）的人，但是如今他们拥有各种各样的头衔，我无法跟上……我不会提供完整的Visual您可以下载Studio解决方案，并且无需进行任何更改或配置即可直接运行它，但是我将为您提供可以根据您自己的环境进行参数化后使用的代码。

So, with that out of the way, let’s start with some nostalgia: who wouldn’t remember all the nice and challenging partitioning exercises for OLAP cubes? 🙂 If you had a huge fact table with hundreds of millions of rows it was at least not an efficient option to do a full process on the measure group every time, but more often it was out of the question.

因此，从某种意义上说，让我们从怀旧开始：谁不记得OLAP多维数据集所有出色而富挑战性的分区练习了？ 🙂如果您有一个庞大的事实表，其中包含成千上万的行，那么，这至少不是每次对度量值组执行完整过程的有效选择，但更多时候是不可能的。

In this example, I have a fact table with 500M+ rows that is updated hourly and I created monthly partitions. It is a neat solution and the actual processing takes about 3-4 minutes every hour, mostly because some big degenerate dimensions I couldn’t push out of scope. The actual measure group processing is usually 1-2 minutes and mostly involves 1-3 partitions.

在此示例中，我有一个包含500M +行的事实表，该表每小时更新一次，并创建每月分区。 这是一个很好的解决方案，实际处理每小时大约需要3-4分钟，这主要是因为我无法排除某些较大的退化尺寸。 实际的度量值组处理通常为1-2分钟，并且主要涉及1-3个分区。

I know OLAP is not dead (so it is said) but not really alive either. One thing is for sure: it is not available as PaaS (Platform as a Service) in Azure. So, if you want SSAS in the Cloud, that’s tabular. I assume migration/redesign from on-premise OLAP Cubes to Azure Tabular models is not uncommon. In the case of a huge table with an implemented partitioning solution, that should be ported as well.

我知道OLAP并没有死（据说），但也没有真正存活。 可以肯定的是：它在Azure中不能作为PaaS（平台即服务）使用。 因此，如果您想在云中使用SSAS，那就是表格 。 我认为从本地OLAP多维数据集到Azure Tabular模型的迁移/重新设计并不少见。 对于具有已实现分区解决方案的大型表，也应将其移植。

Where Visual Studio provided a decent GUI for partitioning in the OLAP world, it’s not the case for tabular. It feels like a beta development environment that has been mostly abandoned because the focus has been shifted to other products (guesses are welcome, I’d say it’s Power BI but I often find the Microsoft roadmap confusing especially with how intensely Azure is extending and gaining an ever growing chunk in Microsoft’s income).

Visual Studio在OLAP世界中为分区提供了不错的GUI，而表格格式则不是这种情况。 感觉就像是一个Beta开发环境，由于重点已转移到其他产品而被放弃了（ 欢迎猜测，我想说的是Power BI，但我经常发现Microsoft的路线图令人困惑，尤其是与Azure的扩展和获取程度有多大的困惑。微软收入中不断增长的一块 ）。

In short: let’s move that dynamic partitioning solution from OLAP into Azure Tabular!

简而言之：让我们将动态分区解决方案从OLAP迁移到Azure Tabular！

Goal

目标

The partitioning solution should accommodate the following requirements:

分区解决方案应满足以下要求：

• Based on a configuration table handle different granularity for partitions (monthly, annual, …)
  根据配置表，为分区处理不同的粒度（每月，每年，…）
• Identify currently existing partitions
  识别当前存在的分区
• Create a list of required partitions (this is mostly used at initialization)
  创建所需分区的列表（主要在初始化时使用）
• Compare existing and required partitions: create / delete if needed
  比较现有分区和所需分区：根据需要创建/删除
• Based on the new set of data (e.g. in a staging table) update/process the relevant partitions
  基于新的数据集（例如在临时表中）更新/处理相关分区
• Keep logs of what should happen and what actually happens
  记录应该发生什么以及实际发生什么

The process of Dynamic Partitioning

动态分区的过程

Used technology

二手技术

My solution consists of the below components:

我的解决方案包含以下组件：

• SQL Server tables and stored procedures
  SQL Server表和存储过程
• SSIS to orchestrate the process
  SSIS协调流程

• 1. C# scripts inside SSIS utilizing TOM (Tabular Object Model) – used in this solution

     SSIS中利用TOM（表格对象模型）的 C＃脚本–在此解决方案中使用

     No, the second one is not Jerry 🙂 I am not sure the two methods would get on well in that cat-mouse relationship…

     不，第二个不是杰里（Jerry）🙂我不确定这两种方法在猫鼠关系中能否相得益彰……

  2. Tabular Model Scripting Language (TMSL) where the objects are defined using JSON format 表格模型脚本语言（TMSL）的 PowerShell，其中使用JSON格式定义对象
• Azure Analysis Services (secure connections to it)
  Azure Analysis Services（到它的安全连接）

Let’s get to it, going through the steps from the diagram one-by-one!

让我们开始吧，一步一步地完成图中的步骤！

动态表格分区 (Dynamic Tabular Partitioning)

Overview

总览

The below objects are used in the solution.

解决方案中使用了以下对象。

Object name	Type	Functionality
ETL_Tabular_Partition_Config	Table	Stores metadata for partitions that are used when defining the new ones
ETL_Tabular_Partition_Grain_Mapping	Table	A simple mapping table between conceptual partition periods (e.g. Fiscal Month) and the corresponding Dim_Date column (e.g. Fiscal_Month_Code), this allows to tune partitioning periods dynamically
Dim_Date	Table	A fairly standard, pre-populated date table
ETL_Tabular_Partitions_Required	Table	The master list of changes for partitions, including all that needs to be created / deleted / processed (updated)
pr_InsertTabularPartitionsRequired	Stored procedure	That’s the heart of the SQL side of dynamic partitioning (details below)
ETL_Tabular_Partitions_Existing	Table	A simple list of partitions that currently exist in the deployed database
pr_InsertTabularPartitionsExisting	Stored procedure	A simple procedure that inserts a row into ETL_Tabular_Partitions_Existing and is called from a C# enumerator that loops through the existing partitions of the tabular database
Tabular_Partition.dtsx	SSIS package	This SSIS package is used as an orchestration of the different components of the project. In this 1st step the pr_InsertTabularPartitionsRequired stored procedure is called

对象名称	类型	功能性
ETL_Tabular_Partition_Config	表	存储定义新分区时使用的分区的元数据
ETL_Tabular_Partition_Grain_Mapping	表	概念分区周期（例如，财政月）和相应的Dim_Date列（例如，Fiscal_Month_Code）之间的简单映射表，这允许动态调整分区周期
点心日期	表	相当标准的预填充日期表
ETL_Tabular_Partitions_Required	表	分区更改的主列表，包括所有需要创建/删除/处理（更新）的更改
pr_InsertTabularPartitionsRequired	存储过程	这是动态分区SQL方面的核心（详细信息如下）
ETL_Tabular_Partitions_Existing	表	部署数据库中当前存在的分区的简单列表
pr_InsertTabularPartitions现有	存储过程	一个简单的过程，将一行插入到ETL_Tabular_Partitions_Existing中，并从C＃枚举器调用，该循环遍历表格数据库的现有分区
Tabular_Partition.dtsx	SSIS套件	此SSIS包用作项目不同组件的编排。 在该1 个工序中的pr_InsertTabularPartitionsRequired存储过程被称为

Date configuration

日期配置

For the date configuration, I use the ETL_Tabular_Partition_Config, the ETL_Tabular_Partition_Grain_Mapping and the Dim_Date table. A simplified version for demo purposes:

对于日期配置，我使用ETL_Tabular_Partition_Config，ETL_Tabular_Partition_Grain_Mapping和Dim_Date表。 出于演示目的的简化版本：

• • This contains one row for each table in a tabular database assuming that only partitioning period is required (e.g. monthly), this can be further enhanced if needed
    假设只需要分区时间（例如每月），则表格数据库中的每个表都包含一行，如果需要，可以进一步增强
  • Tabular_Database_Name and Table_Name are used to identify the objects on the server
    Tabular_Database_Name和Table_Name用于标识服务器上的对象
  • Partition_Name_Prefix is used in the naming of the partitions (e.g. Internet Sales – FY)
    Partition_Name_Prefix用于分区的命名（例如Internet Sales – FY）
  • Source_Object – fact table/view (used as the source of the tabular table)
    Source_Object –事实表/视图（用作表格表的源）
  • source object that is the basis of partitioning (e.g. Transaction_Date_SK) 源对象中作为分区基础的列（例如Transaction_Date_SK）
  • Stage_Table / Stage_Column– these are needed to identify the data range of the incremental dataset that is waiting to be pushed into the fact object from staging
    Stage_Table / Stage_Column –需要这些来标识正在等待从暂存过程推入事实对象的增量数据集的数据范围
  • Partition_Grain – the key piece in this exercise to define the periodicity of the partitioning process (e.g. Fiscal Month)
    Partition_Grain –此练习中的关键部分，用于定义分区过程的周期性（例如，财政月）
  • Partition_Start_Date_SK / Partition_End_Date_SK – used as parameters to calculate the boundary for the list of partitions
    Partition_Start_Date_SK / Partition_End_Date_SK –用作计算分区列表边界的参数
  • Partition_Start_Date / Partition_End_Date – calculated columns in the table as when it comes to dates it often helps to have them in surrogate key and in date format, too (the SK integer values are always unambiguous whereas the date values can be used in T-SQL date functions (e.g. EOMONTH or DATEADD) if needed)
    Partition_Start_Date / Partition_End_Date –表中的计算列涉及日期时，通常也有助于使它们具有代理键和日期格式（SK整数值始终是明确的，而日期值可以在T-SQL日期中使用功能（如需要，例如EOMONTH或DATEADD）
• • To find out how your conceptual partition grain (e.g. Fiscal Month) can be mapped to Dim_Date, i.e. what column in Dim_Date means that
    要了解如何将您的概念分区粒度（例如，财政月）映射到Dim_Date，即Dim_Date中的哪一列表示
  • Partition_Grain is often a user-friendly name where Partitioning_Dim_Date_Column is more technical
    Partition_Grain通常是一个用户友好的名称，其中Partitioning_Dim_Date_Column更具技术性
  • E.g. Fiscal Month and Fiscal_Month_of_Year_Code
    例如，会计月份和会计年度代码
• • If you have a data warehouse of any type, most likely it already contains one so why not use what’s already there? Especially that it can contain a lot of logic around your own company’s fiscal structure.
    如果您有任何类型的数据仓库，那么很可能已经包含一个数据仓库，那么为什么不使用已有的数据仓库呢？ 特别是它可以包含围绕您自己公司的财务结构的许多逻辑。
  • You can create a temp table on the fly or can use built-in T-SQL date functions but that adds an unnecessary complexity to the procedure without any benefit
    您可以动态创建临时表，也可以使用内置的T-SQL日期函数，但这会给过程增加不必要的复杂性，而没有任何好处
  • I included some sample values in the table diagram (usually such examples don’t belong to an entity-relationship diagram) to show what I mean by them
    我在表图中包含了一些示例值（通常这样的示例不属于实体关系图）以显示它们的含义
  • The relationship to Dim_Date is defined using dynamic T-SQL in the stored procedure (see next section)
    与Dim_Date的关系是在存储过程中使用动态T-SQL定义的（请参阅下一节）

检查现有分区–脚本任务 (Check Existing Partitions – script task)

TOM – Tabular Object Model

TOM –表格对象模型

I chose C# for this script’s language and the TOM (Tabular Object Model) objects are required to interact with tabular servers and their objects. To use them some additional references are needed on the server (if you use ADF and SSIS IR in the cloud, these are available according to the Microsoft ADF team) that are part of the SQL Server 2016 Feature Pack. You can find more info about how to install it here:

我选择C＃作为该脚本的语言，并且TOM（表格对象模型）对象是与表格服务器及其对象进行交互所必需的。 要使用它们，服务器上需要一些其他引用（ 如果您在云中使用ADF和SSIS IR，根据Microsoft ADF团队的要求，这些引用是SQL Server 2016 Feature Pack的一部分。 您可以在此处找到有关如何安装的更多信息：

Install, distribute, and reference the Tabular Object Model

安装，分发和引用表格对象模型

And the official TOM Microsoft reference documentation can be very handy:

官方的TOM Microsoft参考文档可能非常方便：

Understanding Tabular Object Model (TOM) in Analysis Services AMO

了解Analysis Services AMO中的表格对象模型（TOM）

The part that is related specifically to the partitions:

与分区特别相关的部分：

Create Tables, Partitions, and Columns in a Tabular model

在表格模型中创建表，分区和列

Variables

变数

The below variables are needed to be passed from the package to the script:

需要将以下变量从包传递到脚本：

• Audit_Key (I use this everywhere, so each execution is logged separately)
  Audit_Key（我在所有地方都使用它，因此每个执行单独记录）
• Tabular_Database_Name
  Tabular_Database_Name
• Tabular_Table_Name
  Tabular_Table_Name
• ConnStr_Configuration_Database – needed for the stored procedure that writes the results of the TOM script into t SQL table
  ConnStr_Configuration_Database –将TOM脚本结果写入t SQL表的存储过程所需
• it should work with an on-prem tabular server as well), multiple forms of authentication can be used (它也应与本地表格服务器一起使用），可以使用多种形式的身份验证（ service principal with username/password in the connection string that is obtained from a key vault during runtime, Managed Service Identity, …), this is a quickly evolving area of Azure 从密钥获得的连接字符串中具有用户名/密码的服务主体）运行时的保管库，托管服务标识等 ），这是Azure的快速发展领域

Make sure you include the above variables, so they can be used in the script later on:

确保包括上述变量，以便稍后可以在脚本中使用它们：

The syntax for referencing them (as it’s not that obvious) is documented here:

引用它们的语法（不太明显）在此处记录：

Using Variables in the Script Task

在脚本任务中使用变量

Main functionality

主要功能

The script itself does nothing else but loops through all existing partitions and calls a stored procedure row-by-row that inserts the details of that partition into a SQL table.

该脚本本身不执行其他任何操作，而是循环遍历所有现有分区并逐行调用存储过程，该存储过程将该分区的详细信息插入到SQL表中。

• Identify existing partitions.TOM.cs (识别现有的partitions.TOM.cs （ I removed all comments that SSIS puts there by default and please make sure you don’t just copy&paste it as the namespace ST_… (line 12) is different for your script task!) 我删除了SSIS默认情况下在其中添加的所有注释，请确保您不要只是复制并粘贴它，因为命名空间ST_…（第12行）与您的脚本任务不同！）
• pr_InsertTabularPartitionsExisting.sql pr_InsertTabularPartitionsExisting.sql
• • ETL_Tabular_Partitions_Existing.sql ETL_Tabular_Partitions_Existing.sql
  • ETL_Tabular_Partitions_Planned.sql ETL_Tabular_Partitions_Planned.sql

SQL整理分区处理方法 (SQL to sort out what to do with partitions)

All this logic is coded into pr_InsertTabularPartitionsRequired (feel free to use a better name if you dislike this one) and in high level it does the following:

所有这些逻辑都编码为pr_InsertTabularPartitionsRequired（如果您不喜欢此名称，请随意使用更好的名称），并在较高级别执行以下操作：

Gray means T-SQL, white is C# (see the previous section), dark grey is putting everything together.

灰色表示T-SQL，白色表示C＃（ 请参阅上一节 ），深灰色表示将所有内容组合在一起。

Here is the code of my procedure, it works assuming you have the three tables defined previously and you configured the values according to your databases / tables / columns.

这是我的过程的代码，假设您已预先定义了三个表，并且已根据数据库/表/列配置了值，则该代码可以正常工作。

pr_InsertTabularPartitionsRequired.sql

pr_InsertTabularPartitionsRequired.sql

It is mostly self-explanatory, and the inline comments can guide you as well. Some additional comments:

它主要是不言自明的，内联注释也可以指导您。 一些其他评论：

• Dynamic SQL must be used because the column that is used from Dim_Date cannot be hardcoded and that is part of the query that extracts the list of date periods from there
  必须使用动态SQL，因为不能对来自Dim_Date的列进行硬编码，并且该列是从中提取日期期间列表的查询的一部分
• The CREATE TABLE is defined outside the dynamic SQL otherwise the scope of it is limited to the execution of that dynamic code and then the temp table is cleaned out of memory thus not usable later in the procedure’s session
  CREATE TABLE是在动态SQL之外定义的，否则它的范围仅限于该动态代码的执行，然后将temp表从内存中清除掉，因此无法在过程的会话中使用
• I use EXECUTE sp_executesql @sql_string instead of EXEC (@sql_string) as best practice though in this case due to the low volume of data both perform satisfactorily but while there are good reasons to use EXECUTE sp_executesql instead of EXEC, the latter doesn’t really have any advantage apart from being quicker to type
  我使用EXECUTE sp_executesql @sql_string代替EXEC（@sql_string）作为最佳实践，尽管在这种情况下，由于数据量较低，两者都令人满意，但是尽管有充分的理由使用EXECUTE sp_executesql代替EXEC，但后者并没有真正的作用。除了打字速度快外，还有其他优势
• cte_partition_config – simply extracts the config data for the required tabular database and table cte_partition_config –只需提取所需表格数据库和表的配置数据
• cte_partition_required – the cross join is used to create as many rows with the config values as many partitions are needed, using the actual date-related information in the partition names. With a simple example: for monthly partitions in 2018 all the metadata for the tabular table needs to be read only once but the month values / descriptions are needed 12 times
• cte_partition_required –交叉联接用于使用分区名称中与日期相关的实际信息来创建具有配置值的行，该行的配置值与所需的分区数相同。 举一个简单的例子：对于2018年的每月分区，表格表的所有元数据只需要读取一次，但是月份值/描述则需要12次
• Populate ETL_Tabular_Partitions_Required – a straightforward comparison between existing (see section 2) and required (or planned) partitions using some set theory 填充ETL_Tabular_Partitions_Required –使用一些集合理论在现有分区（请参阅第2节）和必需分区（或计划分区）之间进行直接比较
  • If a partition doesn’t exist but is required => CREATE it
    如果一个分区不存在但是是必需的=>创建它
  • If it is not required but exists => DELETE it
    如果不是必需的但存在=>删除它
  • If it’s both then (guess what?) it EXISTS 🙂 so at this point it can be left alone
    如果两者都存在（猜测是什么？），则它存在IST，因此，此时可以将其单独放置

Additionally, a WHERE clause for each partition is defined which can be used later when it is time to actually create them.

另外，为每个分区定义了一个WHERE子句，稍后可以在实际创建它们时使用。

• List_Staging_Periods – this last step uses the config data to check what dates (usually that is the lowest level) exists in the data that is staged and was loaded into the final fact table/view to know which partitions need an update. E.g. if your incremental dataset has data only for the last 2 days and you are in the middle of the month, you only need to process the partition for the current month and leave the others as they are List_Staging_Periods –这最后一步使用配置数据检查已暂存的数据中的日期（通常是最低级别），该日期已加载到最终的事实表/视图中，以了解哪些分区需要更新。 例如，如果您的增量数据集仅具有最近两天的数据，并且您处于该月的中旬，则只需处理当月的分区，而其余部分保持不变

创建新分区/删除不需要的分区/进程 (Create new partitions / drop the ones not needed / Process)

Again, back to the C# realm.

再次回到C＃领域。

Code Confusion

代码混乱

One particular inconsistency caught me as I had to spend half an hour to figure out why removing a partition has a different syntax then processing. It might be totally straightforward with people having a .NET background but different than how T-SQL conceptually work.

一个特别的不一致引起了我的注意，因为我不得不花半个小时来弄清楚为什么删除分区的语法与处理语法不同。 对于具有.NET背景但与T-SQL在概念上不同的人，这可能是完全简单的。

Tabular_Table.Partitions.Remove(Convert.ToString(Partition["Partition_Name"]));
 
Tabular_Table.Partitions[Convert.ToString(Partition["Partition_Name"])].RequestRefresh(RefreshType.Full);

Conceptually

从概念上讲

• Deletion – Collection.Action(Member of Collection)
  删除– Collection.Action（集合成员）
• Process – Collection(Member of Collection).Action(ActionType)
  流程–集合（集合成员）.Action（动作类型）

Source query for new partitions

源查询新分区

How to assign the right query for each partition? Yes, we have the WHERE conditions in the ETL_Tabular_Partitions_Required table but the other part of the query is missing which has the date filtering to ensure there are no overlapping partitions. For that I use a trick (I am sure you can think of other ways, but I found this next one easy to implement and maintain): I have a pattern partition in the solution itself under source control. It has to be in line with the up-to-date view/table definitions otherwise the solution can’t be deployed as the query would be incorrect. I just need to make sure it always stays empty. For that a WHERE condition like 1=2 is sufficient enough (as long as the basic arithmetic laws don’t change). Its naming is “table name – pattern”

如何为每个分区分配正确的查询？ 是的，我们在ETL_Tabular_Partitions_Required表中具有WHERE条件，但缺少查询的其他部分，该部分具有日期过滤功能以确保没有重叠的分区。 为此，我使用了一个技巧（ 我相信您可以想到其他方法，但是我发现下一个易于实现和维护 ）：我在源代码控制下的解决方案中有一个模式分区。 它必须与最新的视图/表定义保持一致，否则该解决方案将无法部署，因为查询将不正确。 我只需要确保它始终为空即可。 为此， 只要 1 = 2这样的WHERE条件就足够了（ 只要基本算术定律不变 ）。 它的名称是“ 表名-模式 ”

Then I look for that partition (see the details in the code at the end of the section), extract its source query, strip off the WHERE condition and then when looping through the new partitions, I just append the WHERE clause from the ETL_Tabular_Partitions_Required table.

然后，我寻找该分区（ 请参阅本节末尾的代码中的详细信息 ），提取其源查询，剥离WHERE条件，然后在遍历新分区时，只需从ETL_Tabular_Partitions_Required表中追加WHERE子句。

string Tabular_Table_Name = "your table name";
string Tabular_Partition_Pattern_Name = Tabular_Table_Name + " - pattern";
//connect to tabular model
var Tabular_Server = new Server();
string Tabular_ConnStr = "your connection string";
 
Tabular_Server.Connect(Tabular_ConnStr);
Database Tabular_Db = Tabular_Server.Databases[Tabular_Database_Name];
Model Tabular_Model = Tabular_Db.Model;
Table Tabular_Table = Tabular_Model.Tables[Tabular_Table_Name];
 
Partition Patter_Partition = Tabular_Table.Partitions.Find(Tabular_Partition_Pattern_Name);

Note: I use SQL queries not M ones in my source but here’s the code that helps you get both types from the tabular database’s partition using .NET once you have identified the proper partition that contains the pattern:

注意：我在源代码中使用的不是SQL查询，但下面的代码可帮助您在确定包含模式的适当分区后使用.NET从表格数据库分区中获取两种类型的代码：

For SQL

对于SQL

string Partition_Pattern_Query_SQL=
((Microsoft.AnalysisServices.Tabular.QueryPartitionSource)
(Pattern_Partition.Source.Partition).Source).Query.ToString();

For M

对于M

string Partition_Pattern_Query_M =
((Microsoft.AnalysisServices.Tabular.CalculatedPartitionSource)
(Pattern_Partition.Source.Partition).Source).Query.ToString();

Script steps

脚本步骤

Now I have the first half of the SQL query, I have the building blocks for this last step of the partitioning process:

现在，我有了SQL查询的前半部分，有了分区过程的最后一步的构建块：

1. ETL_Tabular_Partitions_Required table. It stores the action flag that identifies what needs to be done with each of the partitions, too. ETL_Tabular_Partitions_Required表的内容，提取有关所有需要更改的分区的信息。 它还存储了操作标志，该标志也标识了每个分区需要执行的操作。
2. switch between these three options:开关开始循环浏览所有分区：
   1. Create AND process any partition (with the proper SQL query behind it) that’s needed but does not exist yet
      创建并处理需要但尚不存在的任何分区（后面带有适当SQL查询）
   2. Delete partitions that are not needed anymore
      删除不再需要的分区
   3. Process the ones that already exist but has incoming new data
      处理已经存在但有新数据的数据

Don’t forget that after the loop the tabular model must be saved and that is when all the previously issued commands are actually executed at the same time:

不要忘记在循环之后必须保存表格模型，也就是说，实际上同时执行了所有先前发出的命令：

Tabular_Model.SaveChanges();

The code bits that you can customize to use in your own environment:

您可以自定义以在自己的环境中使用的代码位：

PartitionActions.TOM.cs

分区动作.TOM.cs

结语 (Wrap up)

So, by now you should have an understanding of how partitioning works in tabular Azure Analysis Services and not just how the processing can be automated but the creation / removal of the partitions based on configuration data (instead of just defining all the partitions beforehand until e.g. 2030 for all months).

因此，到目前为止，您应该已经了解分区在表格式Azure Analysis Services中的工作方式，不仅是如何自动化处理，而且还基于配置数据创建/删除分区（ 而不是仅预先定义所有分区，直到例如到2030年为止 ）。

The scripts – as I said at the beginning – cannot be used just as they are due to the complexity of the Azure environment and that the solution includes more than just a bunch of SQL tables and queries: .NET scripts and Azure Analysis Services.

正如我一开始所说的那样，由于Azure环境的复杂性，无法使用它们，因为该解决方案不仅仅包含一堆SQL表和查询：.NET脚本和Azure Analysis Services。

I aimed to use generic and descriptive variable and column names, but it could easily happen that I missed the explanation of something that became obvious to me during the development of this solution. In that case please feel free to get in touch with me using the comments section or sending an email to mi_technical@vivaldi.net

我的目标是使用通用的和描述性的变量名和列名，但是很容易发生这种情况，因为我错过了在开发此解决方案时对我来说显而易见的解释。 在这种情况下，请随时使用评论部分与我联系或发送电子邮件至mi_technical@vivaldi.net

Thanks for reading!

谢谢阅读！

翻译自: https://www.sqlshack.com/dynamic-partitioning-in-azure-analysis-services-tabular/

azure