December 2, 2022 at 10:53 pm
Hello Folks,
Analysis Server 15.0.35.22
I am currently processing my full database via SQL Server Agent job
{
"refresh": {
"type": "full",
"objects": [
{
"database": "Model1"
}
]
}
}
I'd like to get more granular and only process certain tables using table based logic.
DROP TABLE IF EXISTS #ModelObjects
CREATE TABLE #ModelObjects
(
Model VARCHAR(20),
ModelTable VARCHAR(20),
ProcessingGroup TINYINT
)
INSERT INTO #ModelObjects (Model,ModelTable,ProcessingGroup) VALUES ('Model1','Transaction',1)
INSERT INTO #ModelObjects (Model,ModelTable,ProcessingGroup) VALUES ('Model1','Date',1)
INSERT INTO #ModelObjects (Model,ModelTable,ProcessingGroup) VALUES ('Model1','Contact',2)
SELECT * FROM #ModelObjects WHERE ProcessingGroup = 1
So for ProcessingGroup = 1 I only want to process tables in that processing group.
{
"refresh": {
"type": "full",
"objects": [
{
"database": "Model1",
"table": "Transaction"
},
{
"database": "Model1",
"table": "Date"
}
]
}
}
Any thoughts would be appreciated.
December 3, 2022 at 11:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
December 5, 2022 at 3:35 pm
Here's a few articles/blog posts that describe techniques you could use to make this happen:
https://bennyaustin.com/2014/02/20/dynamic-processing/
https://www.sqlservercentral.com/articles/dynamically-process-ssas-cube-from-ssis-package
December 5, 2022 at 5:43 pm
Thanks Martin, That certainly looks viable. Since I am more of a TSQL rather than a SSIS proponent I probably will go in this direction.
--https://www.mssqltips.com/sqlservertip/2790/dynamic-xmla-using-tsql-for-sql-server-analysis-services/
DECLARE @myXMLA NVARCHAR(MAX)
SET @myXMLA =N'
{
"refresh": {
"type": "full",
"objects": [
{
"database": "Model1",
"table": "Transaction"
},
{
"database": "Model1",
"table": "Date"
}
]
}
}
'
SELECT @myXMLA
EXEC (@myXMLA) AT [SSAS];--Execute at my linked SSAS server
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy