The previous post looked at how to process SSAS Tabular models with TMSL. Since SQL Server adds new JSON capabilities in 2016, let’s look at how to convert TMSL JSON to a Table with OPENJSON. OPENJSON is a new function in SQL Server 2016 that, per Microsoft:
OPENJSON is a table-valued function that parses JSON text and returns objects and properties from the JSON input as rows and columns.
In short, OPENJSON converts JSON text to a table. Since TMSL is JSON, this function can convert a SSAS Tabular processing script into a table. This could be useful if you wanted to document a SSAS processing schedule.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 |
DECLARE @json NVARCHAR(max) = N'{ "sequence": { "operations": [ { "refresh": { "type": "dataOnly", "objects": [ { "database": "DatabaseName", "table": "Table1Name", "partition": "Table1Partition" }, { "database": "DatabaseName", "table": "Table2Name", "partition": "Table2Partition" }, { "database": "DatabaseName", "table": "Table3Name" }, { "database": "DatabaseName", "table": "Table4Name" } ] } }, { "refresh": { "type": "calculate", "objects": [ { "database": "DatabaseName" } ] } } ] } }'; WITH jsonCTE AS ( SELECT [key] ,[value] FROM OPENJSON(@json, '$.sequence.operations') ) SELECT jsonCTE.[key] + 1 AS ProcessingStep ,RefreshSequence.RefreshType ,Operations.[database] AS Model ,Operations.[table] AS [Table] ,Operations.[partition] AS [Partition] FROM jsonCTE CROSS APPLY OPENJSON (jsonCTE.[value]) WITH( RefreshType nvarchar(200) '$.refresh.type' ,RefreshObjects nvarchar(max) '$.refresh.objects' AS JSON ) AS RefreshSequence CROSS APPLY OPENJSON (RefreshSequence.RefreshObjects) WITH( [database] nvarchar(200) ,[table] nvarchar(200) ,[partition] nvarchar(200) ) AS Operations |
When you run the script, the results will look like this:
The new OPENJSON syntax takes some getting used to, but it makes working with JSON much easier than working with XML, in my opinion. In this example, you first use the function to open the “operations” array, second for the objects within the array, and thirdly for the “objects” array within those objects. To join these expanded objects together, the CROSS APPLY function is used.
As I mentioned, this takes some getting used to. If you’re new to the OPENJSON function, this is perhaps not the best introductory example. I’d suggest commenting out various parts and running intermediate queries to get a feel for what the results look like at each step.
1 Comment