We have a new community! Please visit support.timextender.com
Follow

How to configure Table Partitioning

Table partitioning can be configured both by Date and Custom. I will explain both methods below. 

Set up partitioning by Date

Identify the fact table that you want to configure partitions and go to the table settings (F4) and click on the performance pane.

Partition3.PNG

Click Add new... to create a partition template. 

If you choose the Date - System Field bullet point the menu you will get three options Year, Month and Day. The Use NULL value Conversion means that if the date field is containing NULL values it should use what is added there.

Partition2.PNG

This is what the table is going to be partitioned into. You should take into account the amount of time it will take to create these when you execute the cube. The bigger the date range the more parts will need to be created.

Partition4.PNG

Then you choose the the date field you want it to base the partitions on. It could be any date field. If you choose DW_TimeStamp it will create the partitions on new data, though this only works on incremental load tables.

Partition5.PNG

Lastly, you choose the Date table you have created. It is used to compare the date data.

Partition6.PNG

Then all you need is to deploy/execute the table to complete the changes.

Create a custom partition

Sometimes you do not only want the partition to be based on year, month or day. Perhaps you want dynamic ranges that don't adhere to only one timeframe. Below I will show how you can set up just that.

Go to your partition settings and Add new... partition template. 

 

Change the bullet point to Other - Manual setup. You will now get some different menu points.

Partition9.PNG

You can choose what sort of data type the range should contain. In this case I will choose String.

Partition10.PNG

In the New Range Value text box write A and press the + button. Keep doing this until you have added E. It will then be added to the Range Value area.

11.PNG

In the Advanced area change the Filegroups menu to Per partition.

Partition12.PNG

Set up the ranges

The next step is to create the dynamic ranges.

Start by going to the source table of the table you want to use the partition in. Create a custom text field called Part.

13.PNG

Add some fixed characters.

14.PNG

The next step is to add conditions to the characters. So A becomes equal to today, B becomes equal to current week except today, C becomes equal to current month except current week, D becomes equal to current year except current month and lastly E becomes equal to any data older than current year.

Like this.

15.PNG

I found the condition Here and then re purposed it to work for this. Due to the time of year i made this condition C and D wouldn't get filled. As you can see the rest gets filled out.

16.PNG

Then you pull in the Part field to the Fact table you want to use for partitioning.

Use the partitioning

In the table settings, set the template to point at the manual template we made.

17.PNG

Then add the Part field we created in the Field menu.

18.PNG

 

Finally, deploy/execute to complete the changes. 

 

 

Was this article helpful?
0 out of 0 found this helpful

8 Comments

  • 0
    Avatar
    Sten Lomme

    Is it possible to use Date Partitioning and store on different filegroups?

    How would your manual Partition Ranges be affected when used on tables with History and/or Incremental Loads.

  • 1
    Avatar
    Thomas Lind

    I do not think so. This is mainly used as a way to lower the execute times on the cubes.

    Partitions are created during execution, when you get new data added, it will create a new partition for that. The date type will work fine with incremental load, depending on which date field you are using.

    Whereas the manual one will keep the previous days settings if it runs in incrmental load. So it will be all A's.

    I will try to work out a way to do it anyway.

  • 0
    Avatar
    Sten Lomme

    Looking forward to it :)

  • 0
    Avatar
    Jepsen, Martin Tange DK - NOH

    I don't see how you can edit or delete a template in Discovery Hub. Is there a workaround I don't know about?

  • 0
    Avatar
    Thomas Lind

    Hi
    So a it has been a while since I mentioned finding a solution to the problem with incremental load.

    I just found a way.

    You generate the ranges in another table based on the same original table, but doesn't use incremental load. It off course needs to be only the relevant fields, such as PK's and the date field. It needs to be able to run the execution in a short amount of time, otherwise it won't be feasible.

    I got it to run in 2 seconds for 350,000 rows.

    When you have the range made, you just join it in to the table you want to use partitions on while using the new Keep Lookup Values up to date feature.

    This will update the parts field every time it is run and making it work with incremental load.

  • 1
    Avatar
    Willem Jan Wever

    The instructions state: 

    "In the Advanced area change the Filegroups menu to Per partition."

    However, once deployed it seems the partitions are all mapped to [PRIMARY]. It seems no filegroup mapping is being realised. What am I missing here to get this done?

  • 0
    Avatar
    Peter Faulkner

    @Willem Jan Wever did you ever find the answer?  I would like to use this feature.

  • 0
    Avatar
    Drilon Pollozhani

    @Willem Jan Wever
    If you are using Azure SQL DB, then only the [PRIMARY] filegroup is supported.

Please sign in to leave a comment.