Dynamically select tables for SSAS processing

Question

Post reply

Dynamically select tables for SSAS processing

Chrissy321

SSCoach

Points: 15768
More actions
December 2, 2022 at 10:53 pm

#4121340
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.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80325 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Martin Schoombee SSCoach Points: 19128 More actions · Answer 2

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

Chrissy321 SSCoach Points: 15768 More actions · Answer 3

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