Load Cubes Incrementally

Partitioning is only available when using SQL Server Developer and Enterprise editions if your SQL Server version is older than 2016 SP1. Starting with SQL Server 2016 SP1 partitioning became standard in all editions of SQL Server.

1. First you will need to create a partition template. To do this, right-click the project name at the top of the project tree. Hover over Advanced, then select Add Partition Template.

   

2. Name the partition. Keep Date - System field selected. Next select 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. If you want to partition by something other than date, select Other - Manual setup. The Use NULL value Conversion option is used when the date field contains NULL values. It will then insert whatever you input into the box below the option. When you're finished click OK.

   

3. In the Data Warehouse, right-click the fact table(s) that the cube you want to incrementally load is using and select Table Settings.

   

4. Go to the Performance tab. Check the Enable physical valid table checkbox. In the Table partitioning section select the template you just created. Select the field date field you want 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. Finally select the Time table that will be used. If you do not have a valid Time table in the Data Warehouse you will need to create one. Note: If you do create a new Time table, you will need to make it match the one you have setup in the Staging database.

   

5. If you need to create a new Time table select Add new. An add Date table window will open. Name the Date table differently than Date. In this example we called it Date Partition. Make sure all the settings match those in the Date table in the Staging database. Then click OK.

   

   

   After everything has been setup, click Apply and then OK. The table name should now be red in color. Right-click it and Deploy and Execute to commit the changes.

   

6. Once that completes, go to the Cubes tab and right-click the cube you want to incrementally load and Edit the cube. Check the Incremental Load box. Make sure you have setup partitioning on all of the fact tables feeding your cube. You can see a list of them in the Fact Tables section of this window. Click OK when finished.

   

   

7. Deploy and Execute the entire OLAP server.