Partitioning

Tables can become quite big in a Tabular model, when it is getting too big, it is possible to split the table into multiple partitions. A table always has at least one partition. Partitioning just means splitting the table in multiple parts. From the users perspective it still looks like one big table. There are multiple possibilities to set up partitioning.

In EasyTabular it is possible to setup a couple of partition types, which supports a broad range of common use-cases. The following options can be provided:

Parameters	Description
GroupName	Groupname of the partion (if not provided, the name of partitionsouce is used)
PartitionSource	The source table that is queried on the datasource (required)
PartitionSchema	The source schema that is used on the datasource (if not provided the schema from the configuration is used)
PartitionFilter	The filter that is set on the source, only relevant for partitiontype = Filter
PartitionStart	The period where to start
PartitionEnd	The number of partitions to create (Positive whole number)
PartitionSize	The size of every partition, default = 1. For a partition per 2 years, set to 2, for 3 years, set to 3 and so on. Positive whole number expected
PartitionType	The filter type that is applied
PartitionFilterColumn	The column that is used for the filter in the wherestatement. It is possible to apply extra statements like YEAR(datecolumn) for example
HasPartitionLeftOver	A partition is created for all te data outside the scope that is defined. Only data that is > than the last partition is added. This can be turned off (default = true)

Partition Types

Type	Description
All	No filter is applied on the given partitionsource
Filter	Partition filter is applied on the given partitionsource
yyyy	Filter is applied on partition filter column and the year month in (ex. 2021 for 2021) is applied on the column as a filter
yyyyMM	Filter is applied on partition filter column and the year month in (ex. 202111 for november 2021) is applied on the column as a filter
yyyyMMdd	Filter is applied on partition filter column and the year month and day (ex. 20211101 for the 1st of november 2021) is applied on the column as a filter

All

The chosen partition type needs specific settings. The easiest is 'All', which just requires a partition source (a table or other queryable object in the data source). The generator will just add a partition with one simple query without a where statement.

- Table:
  Name: Finance
  Partitions: 
  - PartitionSchema: dm
    PartitionSource: F_FinanceData

Filter

With the type 'filter' it is possible to set a hard filter, that will be added to the query. A couple of examples:

- Table:
  Name: Finance
  Partitions: 
  - PartitionSchema: dm
    PartitionSource: F_FinanceData
    PartitionFilter: Year(TransactionDate) = 2024

yyyy or yyyyMM or yyyyMMdd

With one of those types, it is possible to create a couple of partitions, based on parameters. In this case the example is based on yyyy (Year values). To know how many partitions should be created, the following information is needed:

Parameters	Description
PartitionStart	The period where to start
PartitionEnd	The number of partitions to create (Positive whole number)
PartitionSize	The size of every partition, default = 1. For a partition per 2 years, set to 2, for 3 years, set to 3 and so on. Positive whole number expected
PartitionType	The filter type that is applied
PartitionFilterColumn	The column that is used for the filter in the wherestatement. It is possible to apply extra statements like YEAR(datecolumn) for example
HasPartitionLeftOver	A partition is created for all te data outside the scope that is defined. Only data that is > than the last partition is added. This can be turned off (default = true)

This will create 7 partitions, based on year. 6 partitions with a filter on a specific year from 2020 till 2026. And a 'leftover' partition for everything greater than 2026.

- Table:
  Name: Finance
  Partitions: 
  - PartitionSchema: dm
    PartitionSource: F_FinanceData
    PartitionStart: 2020
    PartitionEnd: 6
    PartitionType: yyyy
    HasPartitionLeftOver: true
    PartitionFilterColumn: YEAR(TransactionDate)

Another example based on per 2-months and no left over partition. This will create 6 partitions for the year 2020 and 6 partitions for the year 2021:

- Table:
  Name: Finance
  Partitions: 
  - PartitionSchema: dm
    PartitionSource: F_FinanceData
    PartitionStart: 202001
    PartitionSize: 2
    PartitionEnd: 12
    PartitionType: yyyyMM
    HasPartitionLeftOver: false
    PartitionFilterColumn: CONCAT(YEAR(TransactionDate), RIGHT(CONCAT('0', MONTH(TransactionDate)),2))

It is possible to create multiple types of partitions per table, so mixing different types is no problem.

Partition Types​

Partition Types