Dynamic Partitioning in Tabular SSAS Model

I recommend to create only 2 partitions:

• 1. Partition: All data older than 1 month (date filter is dynamic)
• 2. Partition: All data is newer than 1 month (date filter is dynamic)

Then it is enough to process 2 partition each day (a scheduled job). And process 1. partition in each month.

https://docs.microsoft.com/en-us/analysis-services/tabular-models/partitions-ssas-tabular?view=asallproducts-allversions 

Any suggestions on how we can dynamically change the date in the tabular model partition for the following query in the partition:

SELECT [dbo].[Sales].* FROM [dbo].[Sales] WHERE (([OrderDateKey] >= '20190101') AND ([OrderDateKey] <= '20191231'))

We do not want to manually change the query in the partition. Is there a way to dynamically pass the dates to this partition query?

It depends on the logic of your partition sliding window and the frequency of refresh.
For example if you do my example:
1. Partition1 (old partition), processes each month.

SELECT * 
FROM [dbo].[Sales] 
WHERE [OrderDateKey] < CAST(CONVERT(VARCHAR(8), CAST(CONCAT(YEAR(GETDATE()), '-', MONTH(GETDATE()), '-', 1) AS DATE), 112) AS INT)

Select allOpen in new window

2. Partition2 (new partition), processes every day:

SELECT *
FROM [dbo].[Sales]
WHERE [OrderDateKey] >= CAST(CONVERT(VARCHAR(8), CAST(CONCAT(YEAR(GETDATE()), '-', MONTH(GETDATE()), '-', 1) AS DATE), 112) AS INT)

Select allOpen in new window

So the cutting date is always the 1st day of month for a whole month. Then on the first day of the next month it will be automatically change to the next month.

This is interesting. Should I implement this logic inside the Visual Studio tabular model partition?

Yes, then you will have always 2 partitions:

• Partition1 contains very old data (older than 1 month)
• Partition2 contains data added recently (newer than 1 month)

In Visual Studio you will have a partition designer where you can define these criterias:

Here you can find some more info about this: https://stackoverflow.com/questions/47433553/ssas-tabular-model-partition-refresh-and-schedule

Awesome. How about the following scenario:

Partition 1: Full Load
Partition 2: Monthly Load
Partition 3: Weekly Load
Partition 4: Daily Load
Partition 5: Hourly Load

Based on these partitions, how can we dynamically refresh data in the tabular model without hard coding the dates??

This sounds that you need a nearly real time data in your tabular model but your plan is to comlicated.
Then it is better to have 3 partitions:

• Partition 1: for very old data (process in each month)
• Partition 2: for the last month (process in each day)
• Partition 3: for the last day (process in each hour!)

But it is better to set Partition 3 to not to process but as ROLAP storage type, then it will be a real time processing of source data.

• For Partition 1: for very old data (to process in each month) >> Select * From TableName WHERE date < -------- (ow to dynamically refer this date and time in the query without hard coding)?
• For Partition 2: for the last month (to process in each day) >> Select * From TableName WHERE date <  CAST(CONVERT(VARCHAR(8), CAST(CONCAT(YEAR(GETDATE()), '-', MONTH(GETDATE()), '-', 1) AS DATE), 112) AS INT)  (Is that correct?)
• For Partition 3: for the last day (to process in each hour!) >> Select * From TableName WHERE date < --------- (how to dynamically refer this date and time in the query without hard coding)?

For the Partition1 (before current month), I have this query as you suggested:
SELECT  * FROM [dbo].[ORDER_SP]
WHERE [RECORDUPDATEDDATETIME] <
CAST(CONVERT(VARCHAR(8), CAST(CONCAT(YEAR(GETDATE()), '-',
MONTH(GETDATE()), '-', 1) AS DATE), 112) AS INT)

However, the column RECORDUPDATEDDATETIME has datetime data type and I have the following error
message:

Msg 8115, Level 16, State 2, Line 4
Arithmetic overflow error converting expression to data type datetime.

Any clues?

I have added this as a separate quest:

https://www.experts-exchange.com/questions/29207485/Arithmetic-overflow-error-converting-expression-to-data-type-datetime.html#questionAdd

It now works:

SELECT  * FROM [dbo].[ORDER_SP]
WHERE [RECORDUPDATEDDATETIME] <
CAST(CONVERT(VARCHAR(8), CAST(CONCAT(YEAR(GETDATE()), '-',
MONTH(GETDATE()), '-', 1) AS DATE), 112) AS DATE)

Can you look at the following related question I just opened:

https://www.experts-exchange.com/questions/29207489/What-is-the-best-way-to-automate-the-process-of-refreshing-tabular-partitions-data-and-how-to-accomplish-it.html

Can you please look into this:

https://www.experts-exchange.com/questions/29204465/Can-we-implement-SSAS-tabular-model-row-level-security-for-non-domain-external-users-or-guest-users-without-domain.html