-
Notifications
You must be signed in to change notification settings - Fork 1
/
TabularDeployer.cs
488 lines (429 loc) · 24.6 KB
/
TabularDeployer.cs
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
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
// Contents derived from https://github.com/TabularEditor/TabularEditor/blob/885ff9f84f6497b7958a402854d959d747e0e0a2/TOMWrapper/TOMWrapper/TabularDeployer.cs
namespace Corvus.Deployment.PowerBi.Cli.Commands.Models
{
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading;
using Microsoft.AnalysisServices;
using TOM = Microsoft.AnalysisServices.Tabular;
using Newtonsoft.Json.Linq;
public class TabularDeployer
{
public static string GetTMSL(TOM.Database db, TOM.Server server, string targetDatabaseName, DeploymentOptions options, bool includeRestricted = false)
{
if (db == null) throw new ArgumentNullException("db");
if (string.IsNullOrWhiteSpace(targetDatabaseName)) throw new ArgumentNullException("targetDatabaseName");
if (options.DeployRoleMembers && !options.DeployRoles) throw new ArgumentException("Cannot deploy Role Members when Role deployment is disabled.");
if (server.Databases.ContainsName(targetDatabaseName) && options.DeployMode == DeploymentMode.CreateDatabase) throw new ArgumentException("The specified database already exists.");
string tmsl;
if (!server.Databases.ContainsName(targetDatabaseName)) tmsl = DeployNewTMSL(db, targetDatabaseName, options, includeRestricted, server.CompatibilityMode);
else tmsl = DeployExistingTMSL(db, server, targetDatabaseName, options, includeRestricted, server.CompatibilityMode);
return tmsl;
}
public static DeploymentResult Deploy(TOM.Database db, string targetConnectionString, string targetDatabaseName)
{
return internalDeploy(db, targetConnectionString, targetDatabaseName, DeploymentOptions.Default, CancellationToken.None);
}
public static DeploymentResult Deploy(TOM.Database db, string targetConnectionString, string targetDatabaseName, DeploymentOptions options, CancellationToken cancellationToken = default)
{
return internalDeploy(db, targetConnectionString, targetDatabaseName, options, cancellationToken);
}
/// <summary>
/// Deploys the specified database to the specified target server and database ID, using the specified options.
/// Returns a list of DAX errors (if any) on objects inside the database, in case the deployment was successful.
/// </summary>
/// <param name="db"></param>
/// <param name="targetConnectionString"></param>
/// <param name="targetDatabaseName"></param>
/// <param name="options"></param>
/// <returns></returns>
internal static DeploymentResult internalDeploy(TOM.Database db, string targetConnectionString, string targetDatabaseName, DeploymentOptions options, CancellationToken cancellationToken)
{
if (string.IsNullOrWhiteSpace(targetConnectionString)) throw new ArgumentNullException("targetConnectionString");
var destinationServer = new TOM.Server();
Console.WriteLine($"Connecting to XMLA endpoint: {TabularConnection.StripSensitive(TabularConnection.StripApplicationName(targetConnectionString))}");
destinationServer.Connect(targetConnectionString);
if (!destinationServer.SupportedCompatibilityLevels.Contains(db.CompatibilityLevel.ToString()))
throw new DeploymentException($"The specified server does not support Compatibility Level {db.CompatibilityLevel}");
var tmsl = GetTMSL(db, destinationServer, targetDatabaseName, options, true);
cancellationToken.Register(destinationServer.CancelCommand);
Console.WriteLine($"Deploying model to database: {targetDatabaseName}");
var result = destinationServer.Execute(tmsl);
if (result.ContainsErrors)
{
throw new DeploymentException(string.Join("\n", result.Cast<XmlaResult>().SelectMany(r => r.Messages.Cast<XmlaMessage>().Select(m => m.Description)).ToArray()));
}
// Refresh the server object to make sure we get an updated list of databases, in case a new database was made:
destinationServer.Refresh();
// Fully refresh the deployed database object, to make sure we get updated error messages for the full object tree:
Console.WriteLine("Checking deployment status...");
var deployedDB = destinationServer.Databases.GetByName(targetDatabaseName);
deployedDB.Refresh(true);
return GetLastDeploymentResults(deployedDB);
}
public static DeploymentResult GetLastDeploymentResults(TOM.Database database)
{
return
new DeploymentResult(
TabularModelHandler.CheckErrors(database).Select(t => string.Format("Error on {0}: {1}", GetName(t.Item1), t.Item2)),
TabularModelHandler.GetObjectsNotReady(database).Where(t => t.Item2 == TOM.ObjectState.DependencyError || t.Item2 == TOM.ObjectState.EvaluationError || t.Item2 == TOM.ObjectState.SemanticError)
.Select(t => string.Format("Warning! Object not in \"Ready\"-state: {0} ({1})", GetName(t.Item1), t.Item2.ToString())),
TabularModelHandler.GetObjectsNotReady(database).Where(t => t.Item2 == TOM.ObjectState.CalculationNeeded || t.Item2 == TOM.ObjectState.NoData || t.Item2 == TOM.ObjectState.Incomplete)
.Select(t => string.Format("Information: Unprocessed object: {0} ({1})", GetName(t.Item1), t.Item2.ToString())),
database.Server
);
}
private static string GetName(TOM.NamedMetadataObject obj)
{
if (obj is TOM.Hierarchy) return string.Format("hierarchy {0}[{1}]", GetName((obj as TOM.Hierarchy).Table), obj.Name);
if (obj is TOM.Measure) return string.Format("measure {0}[{1}]", GetName((obj as TOM.Measure).Table), obj.Name);
if (obj is TOM.Column) return string.Format("column {0}[{1}]", GetName((obj as TOM.Column).Table), obj.Name);
if (obj is TOM.Partition) return string.Format("partition '{0}' on table {1}", obj.Name, GetName((obj as TOM.Partition).Table));
if (obj is TOM.Table) return string.Format("'{0}'", obj.Name);
else return string.Format("{0} '{1}'", ((ObjectType)obj.ObjectType).GetTypeName(), obj.Name);
}
internal static string DeployNewTMSL(TOM.Database db, string targetDatabaseName, DeploymentOptions options, bool includeRestricted, CompatibilityMode compatibilityMode)
{
var rawTmsl = TOM.JsonScripter.ScriptCreateOrReplace(db, includeRestricted);
var jTmsl = JObject.Parse(rawTmsl);
if (jTmsl["createOrReplace"]["database"]["compatibilityMode"] != null)
{
jTmsl["createOrReplace"]["database"]["compatibilityMode"] = compatibilityMode.ToString();
}
return jTmsl.TransformCreateTmsl(targetDatabaseName, options).FixCalcGroupMetadata(db).ToString();
}
private static JToken GetNamedObj(JToken collection, string objectName)
{
if (collection == null) return null;
return (collection as JArray).FirstOrDefault(t => t.Value<string>("name").EqualsI(objectName));
}
internal static string DeployExistingTMSL(TOM.Database db, TOM.Server server, string destinationName, DeploymentOptions options, bool includeRestricted, CompatibilityMode compatibilityMode)
{
var orgDb = server.Databases.GetByName(destinationName);
orgDb.Refresh(true);
var orgTables = orgDb.Model.Tables;
var newTables = db.Model.Tables;
var tmslJson = TOM.JsonScripter.ScriptCreateOrReplace(db, includeRestricted);
var tmsl = JObject.Parse(tmslJson).TransformCreateOrReplaceTmsl(db, orgDb, options).FixCalcGroupMetadata(db);
if (tmsl["createOrReplace"]["database"]["compatibilityMode"] != null)
{
tmsl["createOrReplace"]["database"]["compatibilityMode"] = compatibilityMode.ToString();
}
var orgTmsl = tmsl.DeepClone();
var tmslModel = tmsl["createOrReplace"]["database"]["model"] as JObject;
bool needsTwoStepCreateOrReplace = false;
// Detect tables/columns that are change from imported to calculated or vice versa:
foreach (var newTable in newTables)
{
if (!orgTables.ContainsName(newTable.Name)) continue;
var orgTable = orgTables[newTable.Name];
// Remove tables that were changed from calculated to imported or vice versa:
if (orgTable.IsCalculated() != newTable.IsCalculated())
{
GetNamedObj(tmslModel["tables"], newTable.Name).Remove();
// Make sure we remove all metadata that points to this table as well
// Note, we should be careful not to remove any objects that can hold
// processed data:
if (tmslModel["perspectives"] != null) foreach (JObject perspective in tmslModel["perspectives"])
GetNamedObj(perspective["tables"], newTable.Name)?.Remove();
if (tmslModel["cultures"] != null) foreach (JObject culture in tmslModel["cultures"])
GetNamedObj(culture["translations"]?["model"]?["tables"], newTable.Name)?.Remove();
if (tmslModel["relationships"] != null) foreach (JObject relationship in tmslModel["relationships"].Where(r => r.Value<string>("fromTable").EqualsI(newTable.Name)
|| r.Value<string>("toTable").EqualsI(newTable.Name)).ToList())
relationship.Remove();
if (tmslModel["roles"] != null) foreach (JObject modelRole in tmslModel["roles"])
GetNamedObj(modelRole["tablePermissions"], newTable.Name)?.Remove();
// Todo: Variants, Alternates, (other objects that can reference a table?)
needsTwoStepCreateOrReplace = true;
continue;
}
foreach (var newColumn in newTable.Columns)
{
if (newColumn.Type == TOM.ColumnType.RowNumber
|| newColumn.Type == TOM.ColumnType.CalculatedTableColumn
|| !orgTable.Columns.ContainsName(newColumn.Name)) continue;
var orgColumn = orgTable.Columns[newColumn.Name];
// Remove columns that were changed from calculated to data or vice versa:
if (orgColumn.Type != newColumn.Type)
{
var table = GetNamedObj(tmslModel["tables"], newTable.Name);
GetNamedObj(table["columns"], newColumn.Name).Remove();
// Make sure we remove all references to this column as well:
if (tmslModel["perspectives"] != null) foreach (JObject perspective in tmslModel["perspectives"])
{
var tablePerspective = GetNamedObj(perspective["tables"], newTable.Name);
if (tablePerspective == null) continue;
GetNamedObj(tablePerspective["columns"], newColumn.Name)?.Remove();
}
if (tmslModel["cultures"] != null) foreach (JObject culture in tmslModel["cultures"])
{
var tableTranslation = GetNamedObj(culture["translations"]?["model"]?["tables"], newTable.Name);
if (tableTranslation == null) continue;
GetNamedObj(tableTranslation["columns"], newColumn.Name)?.Remove();
}
if (table["columns"] != null) foreach (JObject column in table["columns"].Where(c => c.Value<string>("sortByColumn").EqualsI(newColumn.Name)))
{
column["sortByColumn"].Remove();
}
if (table["hierarchies"] != null) foreach (JObject hierarchy in table["hierarchies"].Where(h => h["levels"].Any(l => l.Value<string>("column").EqualsI(newColumn.Name))).ToList())
{
hierarchy.Remove();
}
if (tmslModel["relationships"] != null) foreach (JObject relationship in tmslModel["relationships"].Where(r => r.Value<string>("fromColumn").EqualsI(newColumn.Name)
|| r.Value<string>("toColumn").EqualsI(newColumn.Name)).ToList())
{
relationship.Remove();
}
if (tmslModel["roles"] != null) foreach (JObject modelRole in tmslModel["roles"])
GetNamedObj(modelRole["tablePermissions"], newTable.Name)?.Remove();
// Todo: Variants, Alternates, (other objects that can reference a column?)
needsTwoStepCreateOrReplace = true;
continue;
}
}
}
if (needsTwoStepCreateOrReplace)
{
return new JObject(
new JProperty("sequence",
new JObject(
new JProperty("operations",
new JArray(tmsl, orgTmsl))))).ToString();
}
return tmsl.ToString();
}
}
public class DeploymentException : Exception
{
public DeploymentException(string message) : base(message)
{
}
}
public class DeploymentResult
{
public readonly IReadOnlyList<string> Issues;
public readonly IReadOnlyList<string> Warnings;
public readonly IReadOnlyList<string> Unprocessed;
public readonly TOM.Server DestinationServer;
public DeploymentResult(IEnumerable<string> issues, IEnumerable<string> warnings, IEnumerable<string> unprocessed, TOM.Server destinationServer)
{
Issues = issues.ToList();
Warnings = warnings.ToList();
Unprocessed = unprocessed.ToList();
DestinationServer = destinationServer;
}
}
public class DeploymentOptions
{
public DeploymentMode DeployMode = DeploymentMode.CreateOrAlter;
public bool DeployConnections = false;
public bool DeployPartitions = false;
public bool SkipRefreshPolicyPartitions = false;
public bool DeployRoles = true;
public bool DeployRoleMembers = false;
/// <summary>
/// Default deployment. Does not overwrite connections, partitions or role members.
/// </summary>
public static DeploymentOptions Default = new DeploymentOptions();
/// <summary>
/// Full deployment.
/// </summary>
public static DeploymentOptions Full = new DeploymentOptions() { DeployConnections = true, DeployPartitions = true, DeployRoles = true, DeployRoleMembers = true, SkipRefreshPolicyPartitions = false };
/// <summary>
/// StructureOnly deployment. Does not overwrite roles or role members.
/// </summary>
public static DeploymentOptions StructureOnly = new DeploymentOptions() { DeployRoles = false };
public DeploymentOptions Clone()
{
return new DeploymentOptions
{
DeployMode = DeployMode,
DeployConnections = DeployConnections,
DeployPartitions = DeployPartitions,
DeployRoleMembers = DeployRoleMembers,
DeployRoles = DeployRoles,
SkipRefreshPolicyPartitions = SkipRefreshPolicyPartitions
};
}
}
static class TabularDeployerHelpers
{
/// <summary>
/// This takes care of an issue in AS where calc group columns need to appear in a specific order
/// See issue: https://github.com/otykier/TabularEditor/issues/411
/// </summary>
/// <param name="tmslJObj"></param>
/// <param name="db"></param>
/// <returns></returns>
public static JObject FixCalcGroupMetadata(this JObject tmslJObj, TOM.Database db)
{
if (db.CompatibilityLevel < 1470) return tmslJObj;
var tables = (tmslJObj.First as JProperty).Value["database"]["model"]["tables"];
foreach (var cg in db.Model.Tables.Where(t => t.CalculationGroup != null))
{
var cgJson = tables.First(t => t.Value<string>("name") == cg.Name);
var cgJsonColumns = cgJson["columns"] as JArray;
if (cgJsonColumns != null && cgJsonColumns.Count >= 2)
{
var ordinalCol = cgJsonColumns.FirstOrDefault(c => c.PropEquals("sourceColumn", "ordinal"));
if (ordinalCol != null)
{
ordinalCol.Remove();
cgJsonColumns.Insert(0, ordinalCol);
}
var nameCol = cgJsonColumns.FirstOrDefault(c => c.PropEquals("sourceColumn", "name"));
if (nameCol != null)
{
nameCol.Remove();
cgJsonColumns.Insert(0, nameCol);
}
}
}
return tmslJObj;
}
/// <summary>
/// Returns true if the given JObject contains a string property with the specified name and value.
/// </summary>
private static bool PropEquals(this JToken token, string propertyName, string propertyValue)
{
var property = token.Value<string>(propertyName);
if (property == null) return false;
return property.Equals(propertyValue, StringComparison.OrdinalIgnoreCase);
}
/// <summary>
/// This method transforms a JObject representing a CreateOrReplace TMSL script, so that the script points
/// to the correct database to be overwritten, and that the correct ID and Name properties are set. In
/// addition, the method will replace any Roles, RoleMembers, Data Sources and Partitions in the TMSL with
/// the corresponding TMSL from the specified orgDb, depending on the provided DeploymentOptions.
/// </summary>
public static JObject TransformCreateOrReplaceTmsl(this JObject tmslJObj, TOM.Database db, TOM.Database destDb, DeploymentOptions options)
{
// Deployment target / existing database (note that TMSL uses the NAME of an existing database, not the ID, to identify the object)
tmslJObj["createOrReplace"]["object"]["database"] = destDb.Name;
tmslJObj["createOrReplace"]["database"]["id"] = destDb.ID;
tmslJObj["createOrReplace"]["database"]["name"] = destDb.Name;
var model = tmslJObj.SelectToken("createOrReplace.database.model");
var roles = model["roles"] as JArray;
if (!options.DeployRoles)
{
// Remove roles if present and add original:
roles = new JArray();
model["roles"] = roles;
foreach (var role in destDb.Model.Roles) roles.Add(JObject.Parse(TOM.JsonSerializer.SerializeObject(role)));
}
else if (roles != null && !options.DeployRoleMembers)
{
foreach (var role in roles)
{
var members = new JArray();
role["members"] = members;
// Remove members if present and add original:
var roleName = role["name"].Value<string>();
if (destDb.Model.Roles.Contains(roleName))
{
foreach (var member in destDb.Model.Roles[roleName].Members)
members.Add(JObject.Parse(TOM.JsonSerializer.SerializeObject(member)));
}
}
}
else if (roles != null && options.DeployRoleMembers)
{
foreach (var role in roles)
{
if (role["members"] is JArray members)
{
foreach (JObject member in members)
{
if (member.TryGetValue("identityProvider", out JToken value) && value.ToString() == "AzureAD")
{
member.Remove("memberId");
}
}
}
}
}
if (!options.DeployConnections)
{
// Replace existing data sources with those in the target DB:
// TODO: Can we do anything to retain credentials on PowerQuery data sources?
var dataSources = model["dataSources"] as JArray;
foreach (var orgDataSource in destDb.Model.DataSources)
{
dataSources.FirstOrDefault(d => d.Value<string>("name").EqualsI(orgDataSource.Name))?.Remove();
dataSources.Add(JObject.Parse(TOM.JsonSerializer.SerializeObject(orgDataSource)));
}
}
if (!options.DeployPartitions || options.SkipRefreshPolicyPartitions)
{
var tables = tmslJObj.SelectToken("createOrReplace.database.model.tables") as JArray;
foreach (var table in tables)
{
var tableName = table["name"].Value<string>();
if (db.Model.Tables[tableName].IsCalculatedOrCalculationGroup()) continue;
if (destDb.Model.Tables.Contains(tableName))
{
var t = destDb.Model.Tables[tableName];
if (t.IsCalculatedOrCalculationGroup()) continue;
// If destination partition is not a policyrange
if (!options.DeployPartitions || options.SkipRefreshPolicyPartitions && t.GetSourceType() == TOM.PartitionSourceType.PolicyRange)
{
// Retain existing partitions on destination:
var partitions = new JArray();
table["partitions"] = partitions;
foreach (var pt in t.Partitions) partitions.Add(JObject.Parse(TOM.JsonSerializer.SerializeObject(pt)));
}
}
}
}
return tmslJObj;
}
/// <summary>
/// This method transforms a JObject representing a Create TMSL script, so that the database is deployed
/// using the proper ID and Name values. In addition, of the DeploymentOptions specify that roles should
/// not be deployed, they are stripped from the TMSL script.
/// </summary>
public static JObject TransformCreateTmsl(this JObject tmslJObj, string targetDatabaseName, DeploymentOptions options)
{
tmslJObj["createOrReplace"]["object"]["database"] = targetDatabaseName;
tmslJObj["createOrReplace"]["database"]["id"] = targetDatabaseName;
tmslJObj["createOrReplace"]["database"]["name"] = targetDatabaseName;
var roles = tmslJObj.SelectToken("createOrReplace.database.model.roles") as JArray;
if (!options.DeployRoles)
{
// Remove roles if present
if (roles != null) roles.Clear();
}
else if (roles != null && !options.DeployRoleMembers)
{
foreach (var role in roles)
{
var members = new JArray();
role["members"] = members;
}
}
else if (roles != null && options.DeployRoleMembers)
{
foreach (var role in roles)
{
if (role["members"] is JArray members)
{
foreach (JObject member in members)
{
if (member.TryGetValue("identityProvider", out JToken value) && value.ToString() == "AzureAD")
{
member.Remove("memberId");
}
}
}
}
}
return tmslJObj;
}
}
public enum DeploymentMode
{
CreateDatabase,
CreateOrAlter
}
}