Syed Kaleem
asked on
Dynamic Partitioning in Tabular SSAS Model
I have implemented the incremental loading of data through SSIS. I am looking for a step by step guide to do the following:
1. Dynamic partition creation based on certain parameters such as dates or a combination of certain related columns
2. Incremental refresh of data in the SSAS tabular model based on the latest partition created
Thanks
1. Dynamic partition creation based on certain parameters such as dates or a combination of certain related columns
2. Incremental refresh of data in the SSAS tabular model based on the latest partition created
Thanks
ASKER
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?
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.
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)
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)
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.
ASKER
This is interesting. Should I implement this logic inside the Visual Studio tabular model partition?
Yes, then you will have always 2 partitions:
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
- 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
ASKER
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??
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:
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!)
ASKER
- 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)?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
ASKER
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
https://www.experts-exchange.com/questions/29207485/Arithmetic-overflow-error-converting-expression-to-data-type-datetime.html#questionAdd
ASKER
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)
SELECT * FROM [dbo].[ORDER_SP]
WHERE [RECORDUPDATEDDATETIME] <
CAST(CONVERT(VARCHAR(8), CAST(CONCAT(YEAR(GETDATE()), '-',
MONTH(GETDATE()), '-', 1) AS DATE), 112) AS DATE)
ASKER
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
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
- 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