/// <summary>
/// Connects to a SQL Server 2016 Analysis Services instance and loads a tabular model
/// from one of the deployed databases on the instance.
/// </summary>
/// <param name="serverName"></param>
/// <param name="databaseName"></param>
public TabularModelHandler(string serverName, string databaseName, TabularModelHandlerSettings settings = null) : this(settings)
{
this.serverName = serverName;
_disableUpdates = true;
server = new TOM.Server();
var connectionString = TabularConnection.GetConnectionString(serverName, applicationName);
server.Connect(connectionString);
if (databaseName == null)
{
if (server.Databases.Count >= 1)
{
database = server.Databases[0];
}
else
{
throw new InvalidOperationException("This instance does not contain any databases, or the user does not have access.");
}
}
else
{
database = server.Databases.FindByName(databaseName);
if (database == null)
{
database = server.Databases[databaseName];
}
}
if (CompatibilityLevel < 1200)
{
throw new InvalidOperationException("Only databases with Compatibility Level 1200 or higher can be loaded in Tabular Editor.");
}
SourceType = ModelSourceType.Database;
Source = database.Server.Name + "." + database.Name;
Status = "Connected succesfully.";
Version = database.Version;
Init();
UndoManager.Suspend();
Model.ClearTabularEditorAnnotations();
_disableUpdates = false;
UndoManager.Resume();
PowerBIGovernance.UpdateGovernanceMode();
CheckErrors();
try
{
ExternalChangeTrace.Cleanup();
trace = new ExternalChangeTrace(database, applicationName, XEventCallback);
if (Settings.ChangeDetectionLocalServers)
{
trace.Start();
}
}
catch (Exception ex)
{
Log("Exception while configuring AS trace: " + ex.Message);
}
}
private static void DynamicRoleCreation(string roleName,
string serverAddress, string databaseId, string filterExpression)
{
string ConnectionString = @"Provider=MSOLAP;Data Source=" + serverAddress +
@";Initial Catalog=" + databaseId + ";Integrated Security=SSPI;ImpersonationLevel = Impersonate; persist security info = True; ";
var RoleIDNamePair = new Dictionary <int, string>();
// Get Roles Where Rolename == 'NewlycreatedRoleName=@Param'
var GetRolesIDXMLA = @"<Batch xmlns
=""http://schemas.microsoft.com/analysisservices/2003/engine""
Transaction=""true""><Discover xmlns = ""urn:schemas-microsoft-com:xmlanalysis""><RequestType>TMSCHEMA_ROLES</RequestType><Restrictions/><Properties/></Discover
></Batch>";
// Take Param: @RoleName for use in XMLA to create role
var xmlaCreateRole = @"<Batch Transaction=""false""
xmlns=""http://schemas.microsoft.com/analysisservices/2003/engine""><Create
xmlns=""http://schemas.microsoft.com/analysisservices/2014/engine""><DatabaseID>" + databaseId + @"</DatabaseID><Roles><xs:schema xmlns:xs=""http://www.w3.org/2001/XMLSchema""
xmlns:sql=""urn:schemas-microsoft-com:xmlsql""><xs:element><xs:complexType><xs:sequence><xs:element
type=""row""/></xs:sequence></xs:complexType></xs:element><xs:complexType
name=""row""><xs:sequence><xs:element name=""Name"" type=""xs:string"" sql:field=""Name""
minOccurs=""0""/><xs:element name=""Description"" type=""xs:string""
sql:field=""Description"" minOccurs=""0""/><xs:element name=""ModelPermission""
type=""xs:long"" sql:field=""ModelPermission""
minOccurs=""0""/></xs:sequence></xs:complexType></xs:schema><row xmlns=""urn:schemasmicrosoft-com:xmlanalysis:rowset""><Name>" + roleName + @"</Name><ModelPermission>2</ModelPermission></row></Ro
les></Create></Batch>";
// Get Role ID Where Rolename == @PRoleName: XMLA??
var objServer = new Microsoft.AnalysisServices.Tabular.Server();
objServer.Connect(ConnectionString);
objServer.Execute(xmlaCreateRole);
var reader = objServer.ExecuteReader(GetRolesIDXMLA, out XmlaResultCollection
resultsOut, null, true);
// Add all roles to Cached List
while (reader.Read())
{
RoleIDNamePair.Add(int.Parse(reader[0].ToString()), reader[2].ToString());
}
reader.Close();
reader.Dispose();
var RoleIDByRoleName = "";//= RoleIDNamePair.Single(s => s.Value == roleName).Key.ToString();
foreach (var pair in RoleIDNamePair)
{
if (pair.Value == roleName)
{
RoleIDByRoleName = pair.Key.ToString();
break;
}
}
var AddFilterTorole = @"<Batch Transaction=""false""
xmlns=""http://schemas.microsoft.com/analysisservices/2003/engine""><Create
xmlns=""http://schemas.microsoft.com/analysisservices/2014/engine""><DatabaseID>" + databaseId + @"</DatabaseID><TablePermissions><xs:schema
xmlns:xs=""http://www.w3.org/2001/XMLSchema"" xmlns:sql=""urn:schemas-microsoft-com:xmlsql""><xs:element><xs:complexType><xs:sequence><xs:element
type=""row""/></xs:sequence></xs:complexType></xs:element><xs:complexType
name=""row""><xs:sequence><xs:element name=""RoleID"" type=""xs:unsignedLong""
sql:field=""RoleID"" minOccurs=""0""/><xs:element name=""RoleID.Role"" type=""xs:string""
sql:field=""RoleID.Role"" minOccurs=""0""/><xs:element name=""TableID""
type=""xs:unsignedLong"" sql:field=""TableID"" minOccurs=""0""/><xs:element
name=""TableID.Table"" type=""xs:string"" sql:field=""TableID.Table""
minOccurs=""0""/><xs:element name=""FilterExpression"" type=""xs:string""
sql:field=""FilterExpression"" minOccurs=""0""/><xs:element name=""MetadataPermission""
type=""xs:long"" sql:field=""MetadataPermission""
minOccurs=""0""/></xs:sequence></xs:complexType></xs:schema><row xmlns=""urn:schemasmicrosoft-com:xml-analysis:rowset""><RoleID>" + RoleIDByRoleName +
@"</RoleID><TableID>364</TableID><FilterExpression>" + filterExpression + @"</FilterExpression
></row></TablePermissions></Create><SequencePoint
xmlns=""http://schemas.microsoft.com/analysisservices/2014/engine""><DatabaseID>" +
databaseId + @"</DatabaseID></SequencePoint></Batch>";
var applyRoleQuery = AddFilterTorole;
objServer.Execute(applyRoleQuery);
}
internal static string DeployExistingTMSL(TOM.Database db, TOM.Server server, string dbId, DeploymentOptions options, bool includeRestricted)
{
var orgDb = server.Databases[dbId];
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);
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.GetSourceType() != newTable.GetSourceType())
{
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());
}
/// <summary>
/// Loads an Analysis Services tabular database (Compatibility Level 1200 or newer) from a file
/// or folder.
/// </summary>
/// <param name="path"></param>
public TabularModelHandler(string path, TabularModelHandlerSettings settings = null)
{
Settings = settings ?? TabularModelHandlerSettings.Default;
Singleton = this;
server = null;
var fi = new FileInfo(path);
string data;
if (fi.Exists && fi.Extension == ".pbit")
{
pbit = new PowerBiTemplate(path);
data = pbit.ModelJson;
SourceType = ModelSourceType.Pbit;
Source = path;
}
else
if (!fi.Exists || fi.Name == "database.json")
{
if (fi.Name == "database.json")
{
path = fi.DirectoryName;
}
if (Directory.Exists(path))
{
data = CombineFolderJson(path);
}
else
{
throw new FileNotFoundException();
}
SourceType = ModelSourceType.Folder;
Source = path;
}
else
{
data = File.ReadAllText(path);
SourceType = ModelSourceType.File;
Source = path;
}
database = TOM.JsonSerializer.DeserializeDatabase(data);
CompatibilityLevel = database.CompatibilityLevel;
Status = "File loaded succesfully.";
Init();
var serializeOptionsAnnotation = Model.GetAnnotation("TabularEditor_SerializeOptions");
if (serializeOptionsAnnotation != null)
{
SerializeOptions = JsonConvert.DeserializeObject <SerializeOptions>(serializeOptionsAnnotation);
}
// Check if translations / perspectives are stored locally in the model:
if (SourceType == ModelSourceType.Folder && (SerializeOptions.LocalTranslations || SerializeOptions.LocalPerspectives))
{
UndoManager.Enabled = false;
BeginUpdate("Apply translations and perspectives from annotations");
var translationsJson = Model.GetAnnotation("TabularEditor_Cultures");
if (SerializeOptions.LocalTranslations && translationsJson != null)
{
Model.Cultures.FromJson(translationsJson);
foreach (var item in AllTranslatableObjects)
{
item.LoadTranslations();
}
}
var perspectivesJson = Model.GetAnnotation("TabularEditor_Perspectives");
if (SerializeOptions.LocalPerspectives && perspectivesJson != null)
{
Model.Perspectives.FromJson(perspectivesJson);
foreach (var item in AllPerspectiveObjects)
{
item.LoadPerspectives();
}
}
EndUpdate();
UndoManager.Enabled = true;
}
}
void Deploy(string serverName, string databaseID, int doDeploy)
{
// Perform direct save:
if (serverName == null)
{
var nextSwitch = upperArgList.Skip(doDeploy + 1).FirstOrDefault();
var deploySwitches = new[] { "-L", "-LOGIN", "-O", "-OVERWRITE", "-C", "-CONNECTIONS", "-P", "-PARTITIONS", "-Y", "-SKIPPOLICY", "-R", "-ROLES", "-M", "-MEMBERS", "-X", "-XMLA" };
if (deploySwitches.Contains(nextSwitch))
{
Error("Invalid argument syntax.");
OutputUsage();
throw new CommandLineException();
}
Console.WriteLine("Saving model metadata back to source...");
if (Handler.SourceType == ModelSourceType.Database)
{
try
{
Handler.SaveDB();
Console.WriteLine("Model metadata saved.");
var deploymentResult = Handler.GetLastDeploymentResults();
foreach (var err in deploymentResult.Issues)
{
if (errorOnDaxErr)
{
Error(err);
}
else
{
Warning(err);
}
}
foreach (var err in deploymentResult.Warnings)
{
Warning(err);
}
foreach (var err in deploymentResult.Unprocessed)
{
if (warnOnUnprocessed)
{
Warning(err);
}
else
{
Console.WriteLine(err);
}
}
}
catch (Exception ex)
{
Error("Save failed: " + ex.Message);
}
}
else
{
try
{
Handler.Save(Handler.Source, Handler.SourceType == ModelSourceType.Folder ? SaveFormat.TabularEditorFolder : Handler.SourceType == ModelSourceType.Pbit ? SaveFormat.PowerBiTemplate : SaveFormat.ModelSchemaOnly, Handler.SerializeOptions, true);
Console.WriteLine("Model metadata saved.");
}
catch (Exception ex)
{
Error("Save failed: " + ex.Message);
}
}
throw new CommandLineException();
}
var conn = upperArgList.IndexOf("-CONNECTIONS");
if (conn == -1)
{
conn = upperArgList.IndexOf("-C");
}
if (conn > -1)
{
var replaces = argList.Skip(conn + 1).TakeWhile(s => s[0] != '-').ToList();
if (replaces.Count > 0 && replaces.Count % 2 == 0)
{
// Placeholder replacing:
for (var index = 0; index < replaces.Count; index = index + 2)
{
replaceMap.Add(replaces[index], replaces[index + 1]);
}
}
}
string userName = null;
string password = null;
var options = DeploymentOptions.StructureOnly;
var switches = argList.Skip(doDeploy + 1).Where(arg => arg.StartsWith("-")).Select(arg => arg.ToUpper()).ToList();
if (string.IsNullOrEmpty(serverName) || string.IsNullOrEmpty(databaseID))
{
Error("Invalid argument syntax.\n");
OutputUsage();
throw new CommandLineException();
}
if (switches.Contains("-L") || switches.Contains("-LOGIN"))
{
var switchPos = upperArgList.IndexOf("-LOGIN"); if (switchPos == -1)
{
switchPos = upperArgList.IndexOf("-L");
}
userName = argList.Skip(switchPos + 1).FirstOrDefault(); if (userName != null && userName.StartsWith("-"))
{
userName = null;
}
password = argList.Skip(switchPos + 2).FirstOrDefault(); if (password != null && password.StartsWith("-"))
{
password = null;
}
if (string.IsNullOrEmpty(userName) || string.IsNullOrEmpty(password))
{
Error("Missing username or password.\n");
OutputUsage();
throw new CommandLineException();
}
switches.Remove("-L"); switches.Remove("-LOGIN");
}
if (switches.Contains("-O") || switches.Contains("-OVERWRITE"))
{
options.DeployMode = DeploymentMode.CreateOrAlter;
switches.Remove("-O"); switches.Remove("-OVERWRITE");
}
else
{
options.DeployMode = DeploymentMode.CreateDatabase;
}
if (switches.Contains("-P") || switches.Contains("-PARTITIONS"))
{
options.DeployPartitions = true;
switches.Remove("-P"); switches.Remove("-PARTITIONS");
if (switches.Contains("-Y") || switches.Contains("-SKIPPOLICY"))
{
options.SkipRefreshPolicyPartitions = true;
switches.Remove("-Y"); switches.Remove("-SKIPPOLICY");
}
}
if (switches.Contains("-C") || switches.Contains("-CONNECTIONS"))
{
options.DeployConnections = true;
switches.Remove("-C"); switches.Remove("-CONNECTIONS");
}
if (switches.Contains("-R") || switches.Contains("-ROLES"))
{
options.DeployRoles = true;
switches.Remove("-R"); switches.Remove("-ROLES");
if (switches.Contains("-M") || switches.Contains("-MEMBERS"))
{
options.DeployRoleMembers = true;
switches.Remove("-M"); switches.Remove("-MEMBERS");
}
}
var xmla_scripting_only = switches.Contains("-X") || switches.Contains("-XMLA");
string xmla_script_file = null;
if (xmla_scripting_only)
{
var switchPos = upperArgList.IndexOf("-XMLA"); if (switchPos == -1)
{
switchPos = upperArgList.IndexOf("-X");
}
xmla_script_file = argList.Skip(switchPos + 1).FirstOrDefault(); if (String.IsNullOrWhiteSpace(xmla_script_file) || xmla_script_file.StartsWith("-"))
{
xmla_script_file = null;
}
if (string.IsNullOrEmpty(xmla_script_file))
{
Error("Missing xmla_script_file.\n");
OutputUsage();
throw new CommandLineException();
}
switches.Remove("-X");
switches.Remove("-XMLA");
}
try
{
if (replaceMap.Count > 0)
{
Console.WriteLine("Switching connection string placeholders...");
foreach (var map in replaceMap)
{
Handler.Model.DataSources.SetPlaceholder(map.Key, map.Value);
}
}
var cs = string.IsNullOrEmpty(userName) ? TabularConnection.GetConnectionString(serverName, Program.ApplicationName) :
TabularConnection.GetConnectionString(serverName, userName, password, Program.ApplicationName);
if (xmla_scripting_only)
{
Console.WriteLine("Generating XMLA/TMSL script...");
var s = new TOM.Server();
s.Connect(cs);
var xmla = TabularDeployer.GetTMSL(Handler.Database, s, databaseID, options);
using (var sw = new StreamWriter(xmla_script_file))
{
sw.Write(xmla);
}
Console.WriteLine("XMLA/TMSL script generated.");
}
else
{
Console.WriteLine("Deploying...");
Handler.Model.UpdateDeploymentMetadata(DeploymentModeMetadata.CLI);
var deploymentResult = TabularDeployer.Deploy(Handler, cs, databaseID, options);
Console.WriteLine("Deployment succeeded.");
foreach (var err in deploymentResult.Issues)
{
if (errorOnDaxErr)
{
Error(err);
}
else
{
Warning(err);
}
}
foreach (var err in deploymentResult.Warnings)
{
Warning(err);
}
foreach (var err in deploymentResult.Unprocessed)
{
if (warnOnUnprocessed)
{
Warning(err);
}
else
{
Console.WriteLine(err);
}
}
}
}
catch (Exception ex)
{
Error($"{(xmla_scripting_only ? "Script generation" : "Deployment")} failed! {ex.Message}");
}
}
public DeploymentResult(IEnumerable <string> issues, IEnumerable <string> warnings, IEnumerable <string> unprocessed, TOM.Server destinationServer)
{
this.Issues = issues.ToList();
this.Warnings = warnings.ToList();
this.Unprocessed = unprocessed.ToList();
this.DestinationServer = destinationServer;
}
static bool HandleCommandLine(string[] args)
{
var upperArgList = args.Select(arg => arg.ToUpper()).ToList();
var argList = args.Select(arg => arg).ToList();
if (upperArgList.Contains("-?") || upperArgList.Contains("/?") || upperArgList.Contains("-H") || upperArgList.Contains("/H") || upperArgList.Contains("HELP"))
{
OutputUsage();
return(true);
}
enableVSTS = upperArgList.IndexOf("-VSTS") > -1 || upperArgList.IndexOf("-V") > -1;
var warnOnUnprocessed = upperArgList.IndexOf("-WARN") > -1 || upperArgList.IndexOf("-W") > -1;
var errorOnDaxErr = upperArgList.IndexOf("-ERR") > -1 || upperArgList.IndexOf("-E") > -1;
TabularModelHandler h;
if (args.Length == 2 || args[2].StartsWith("-"))
{
// File argument provided (either alone or with switches), i.e.:
// TabularEditor.exe myfile.bim
// TabularEditor.exe myfile.bim -...
if (!File.Exists(args[1]) && !File.Exists(args[1] + "\\database.json"))
{
Error("File not found: {0}", args[1]);
return(true);
}
else
{
// If nothing else was specified on the command-line, open the UI:
if (args.Length == 2)
{
return(false);
}
}
try
{
h = new TOMWrapper.TabularModelHandler(args[1]);
}
catch (Exception e)
{
Error("Error loading file: " + e.Message);
return(true);
}
}
else if (args.Length == 3 || args[3].StartsWith("-"))
{
// Server + Database argument provided (either alone or with switches), i.e.:
// TabularEditor.exe localhost AdventureWorks
// TabularEditor.exe localhost AdventureWorks -...
// If nothing else was specified on the command-line, open the UI:
if (args.Length == 3)
{
return(false);
}
try
{
h = new TOMWrapper.TabularModelHandler(args[1], args[2]);
}
catch (Exception e)
{
Error("Error loading model: " + e.Message);
return(true);
}
}
else
{
// Otherwise, it's nonsensical
return(false);
}
string script = null;
string scriptFile = null;
var doTestRun = upperArgList.IndexOf("-T");
string testRunFile = null;
if (doTestRun == -1)
{
doTestRun = upperArgList.IndexOf("-TRX");
}
if (doTestRun > -1)
{
if (upperArgList.Count <= doTestRun || upperArgList[doTestRun + 1].StartsWith("-"))
{
Error("Invalid argument syntax.\n");
OutputUsage();
return(true);
}
testRun = new TestRun(h.Database?.Name ?? h.Source);
testRunFile = argList[doTestRun + 1];
}
var doScript = upperArgList.IndexOf("-SCRIPT");
if (doScript == -1)
{
doScript = upperArgList.IndexOf("-S");
}
if (doScript > -1)
{
if (upperArgList.Count <= doScript)
{
Error("Invalid argument syntax.\n");
OutputUsage();
return(true);
}
scriptFile = argList[doScript + 1];
if (!File.Exists(scriptFile))
{
Error("Specified script file not found.\n");
return(true);
}
script = File.ReadAllText(scriptFile);
}
var doCheckDs = upperArgList.IndexOf("-SCHEMACHECK");
if (doCheckDs == -1)
{
doCheckDs = upperArgList.IndexOf("-SC");
}
string saveToFolderOutputPath = null;
string saveToFolderReplaceId = null;
var doSaveToFolder = upperArgList.IndexOf("-FOLDER");
if (doSaveToFolder == -1)
{
doSaveToFolder = upperArgList.IndexOf("-F");
}
if (doSaveToFolder > -1)
{
if (upperArgList.Count <= doSaveToFolder)
{
Error("Invalid argument syntax.\n");
OutputUsage();
return(true);
}
saveToFolderOutputPath = argList[doSaveToFolder + 1];
if (doSaveToFolder + 2 < argList.Count && !argList[doSaveToFolder + 2].StartsWith("-"))
{
saveToFolderReplaceId = argList[doSaveToFolder + 2];
}
var directoryName = new FileInfo(saveToFolderOutputPath).Directory.FullName;
Directory.CreateDirectory(saveToFolderOutputPath);
}
string buildOutputPath = null;
string buildReplaceId = null;
var doSave = upperArgList.IndexOf("-BUILD");
if (doSave == -1)
{
doSave = upperArgList.IndexOf("-B");
}
if (doSave == -1)
{
doSave = upperArgList.IndexOf("-BIM");
}
if (doSave > -1)
{
if (upperArgList.Count <= doSave)
{
Error("Invalid argument syntax.\n");
OutputUsage();
return(true);
}
buildOutputPath = argList[doSave + 1];
if (doSave + 2 < argList.Count && !argList[doSave + 2].StartsWith("-"))
{
buildReplaceId = argList[doSave + 2];
}
var directoryName = new FileInfo(buildOutputPath).Directory.FullName;
Directory.CreateDirectory(directoryName);
}
if (doSaveToFolder > -1 && doSave > -1)
{
Error("-FOLDER and -BUILD arguments are mutually exclusive.\n");
OutputUsage();
return(true);
}
// Load model:
Console.WriteLine("Loading model...");
if (!string.IsNullOrEmpty(script))
{
Console.WriteLine("Executing script...");
System.CodeDom.Compiler.CompilerResults result;
Scripting.ScriptOutputForm.Reset(false);
var dyn = ScriptEngine.CompileScript(script, out result);
//nUnit.StartSuite("Script Compilation");
if (result.Errors.Count > 0)
{
Error("Script compilation errors:");
var errIndex = 0;
foreach (System.CodeDom.Compiler.CompilerError err in result.Errors)
{
errIndex++;
ErrorX(err.ErrorText, scriptFile, err.Line, err.Column, err.ErrorNumber);
//nUnit.Failure("Script Compilation", $"Compilation Error #{errIndex}", err.ErrorText, $"{scriptFile} line {err.Line}, column {err.Column}");
}
return(true);
}
try
{
h.BeginUpdate("script");
dyn.Invoke(h.Model, null);
h.EndUpdateAll();
}
catch (Exception ex)
{
Error("Script execution error: " + ex.Message);
return(true);
}
}
if (doCheckDs > -1)
{
Console.WriteLine("Checking source schema...");
ScriptHelper.SchemaCheck(h.Model);
}
if (!string.IsNullOrEmpty(buildOutputPath))
{
Console.WriteLine("Building Model.bim file...");
if (buildReplaceId != null)
{
h.Database.Name = buildReplaceId; h.Database.ID = buildReplaceId;
}
h.Save(buildOutputPath, SaveFormat.ModelSchemaOnly, SerializeOptions.Default);
}
else if (!string.IsNullOrEmpty(saveToFolderOutputPath))
{
Console.WriteLine("Saving Model.bim file to Folder Output Path ...");
if (buildReplaceId != null)
{
h.Database.Name = buildReplaceId; h.Database.ID = buildReplaceId;
}
//Note the last parameter, we use whatever SerializeOptions are already in the file
h.Save(saveToFolderOutputPath, SaveFormat.TabularEditorFolder, null, true);
}
var replaceMap = new Dictionary <string, string>();
var analyze = upperArgList.IndexOf("-ANALYZE");
if (analyze == -1)
{
analyze = upperArgList.IndexOf("-A");
}
if (analyze > -1)
{
var rulefile = analyze + 1 < argList.Count ? argList[analyze + 1] : "";
if (rulefile.StartsWith("-") || string.IsNullOrEmpty(rulefile))
{
rulefile = null;
}
Console.WriteLine("Running Best Practice Analyzer...");
Console.WriteLine("=================================");
var analyzer = new BPA.Analyzer();
analyzer.SetModel(h.Model, h.SourceType == ModelSourceType.Database ? null : FileSystemHelper.DirectoryFromPath(h.Source));
BPA.BestPracticeCollection suppliedRules = null;
if (!string.IsNullOrEmpty(rulefile))
{
if (!File.Exists(rulefile))
{
Error("Rulefile not found: {0}", rulefile);
return(true);
}
try
{
suppliedRules = BPA.BestPracticeCollection.GetCollectionFromFile(Environment.CurrentDirectory, rulefile);
}
catch
{
Error("Invalid rulefile: {0}", rulefile);
return(true);
}
}
IEnumerable <BPA.AnalyzerResult> bpaResults;
if (suppliedRules == null)
{
bpaResults = analyzer.AnalyzeAll();
}
else
{
var effectiveRules = analyzer.GetEffectiveRules(false, false, true, true, suppliedRules);
bpaResults = analyzer.Analyze(effectiveRules);
}
bool none = true;
foreach (var res in bpaResults.Where(r => !r.Ignored))
{
if (res.InvalidCompatibilityLevel)
{
Console.WriteLine("Skipping rule '{0}' as it does not apply to Compatibility Level {1}.", res.RuleName, h.CompatibilityLevel);
}
else
if (res.RuleHasError)
{
none = false;
Error("Error on rule '{0}': {1}", res.RuleName, res.RuleError);
}
else
{
none = false;
if (res.Object != null)
{
var text = string.Format("{0} {1} violates rule \"{2}\"",
res.Object.GetTypeName(),
(res.Object as IDaxObject)?.DaxObjectFullName ?? res.ObjectName,
res.RuleName
);
if (res.Rule.Severity <= 1)
{
Console.WriteLine(text);
}
else if (res.Rule.Severity == 2)
{
Warning(text);
}
else if (res.Rule.Severity >= 3)
{
Error(text);
}
}
}
}
if (none)
{
Console.WriteLine("No objects in violation of Best Practices.");
}
Console.WriteLine("=================================");
}
var deploy = upperArgList.IndexOf("-DEPLOY");
if (deploy == -1)
{
deploy = upperArgList.IndexOf("-D");
}
if (deploy > -1)
{
var serverName = argList.Skip(deploy + 1).FirstOrDefault(); if (serverName != null && serverName.StartsWith("-"))
{
serverName = null;
}
var databaseID = argList.Skip(deploy + 2).FirstOrDefault(); if (databaseID != null && databaseID.StartsWith("-"))
{
databaseID = null;
}
var conn = upperArgList.IndexOf("-CONNECTIONS");
if (conn == -1)
{
conn = upperArgList.IndexOf("-C");
}
if (conn > -1)
{
var replaces = argList.Skip(conn + 1).TakeWhile(s => s[0] != '-').ToList();
if (replaces.Count > 0 && replaces.Count % 2 == 0)
{
// Placeholder replacing:
for (var index = 0; index < replaces.Count; index = index + 2)
{
replaceMap.Add(replaces[index], replaces[index + 1]);
}
}
}
string userName = null;
string password = null;
var options = DeploymentOptions.StructureOnly;
var switches = args.Skip(deploy + 1).Where(arg => arg.StartsWith("-")).Select(arg => arg.ToUpper()).ToList();
if (string.IsNullOrEmpty(serverName) || string.IsNullOrEmpty(databaseID))
{
Error("Invalid argument syntax.\n");
OutputUsage();
return(true);
}
if (switches.Contains("-L") || switches.Contains("-LOGIN"))
{
var switchPos = upperArgList.IndexOf("-LOGIN"); if (switchPos == -1)
{
switchPos = upperArgList.IndexOf("-L");
}
userName = argList.Skip(switchPos + 1).FirstOrDefault(); if (userName != null && userName.StartsWith("-"))
{
userName = null;
}
password = argList.Skip(switchPos + 2).FirstOrDefault(); if (password != null && password.StartsWith("-"))
{
password = null;
}
if (string.IsNullOrEmpty(userName) || string.IsNullOrEmpty(password))
{
Error("Missing username or password.\n");
OutputUsage();
return(true);
}
switches.Remove("-L"); switches.Remove("-LOGIN");
}
if (switches.Contains("-O") || switches.Contains("-OVERWRITE"))
{
options.DeployMode = DeploymentMode.CreateOrAlter;
switches.Remove("-O"); switches.Remove("-OVERWRITE");
}
else
{
options.DeployMode = DeploymentMode.CreateDatabase;
}
if (switches.Contains("-P") || switches.Contains("-PARTITIONS"))
{
options.DeployPartitions = true;
switches.Remove("-P"); switches.Remove("-PARTITIONS");
}
if (switches.Contains("-C") || switches.Contains("-CONNECTIONS"))
{
options.DeployConnections = true;
switches.Remove("-C"); switches.Remove("-CONNECTIONS");
}
if (switches.Contains("-R") || switches.Contains("-ROLES"))
{
options.DeployRoles = true;
switches.Remove("-R"); switches.Remove("-ROLES");
if (switches.Contains("-M") || switches.Contains("-MEMBERS"))
{
options.DeployRoleMembers = true;
switches.Remove("-M"); switches.Remove("-MEMBERS");
}
}
var xmla_scripting_only = switches.Contains("-X") || switches.Contains("-XMLA");
string xmla_script_file = null;
if (xmla_scripting_only)
{
var switchPos = upperArgList.IndexOf("-XMLA"); if (switchPos == -1)
{
switchPos = upperArgList.IndexOf("-X");
}
xmla_script_file = argList.Skip(switchPos + 1).FirstOrDefault(); if (String.IsNullOrWhiteSpace(xmla_script_file) || xmla_script_file.StartsWith("-"))
{
xmla_script_file = null;
}
if (string.IsNullOrEmpty(xmla_script_file))
{
Error("Missing xmla_script_file.\n");
OutputUsage();
return(true);
}
switches.Remove("-X");
switches.Remove("-XMLA");
}
/*if(switches.Count > 0)
* {
* Error("Unknown switch {0}\n", switches[0]);
* OutputUsage();
* return true;
* }*/
try
{
if (replaceMap.Count > 0)
{
Console.WriteLine("Switching connection string placeholders...");
foreach (var map in replaceMap)
{
h.Model.DataSources.SetPlaceholder(map.Key, map.Value);
}
}
var cs = string.IsNullOrEmpty(userName) ? TabularConnection.GetConnectionString(serverName) :
TabularConnection.GetConnectionString(serverName, userName, password);
if (xmla_scripting_only)
{
Console.WriteLine("Generating XMLA/TMSL script...");
var s = new TOM.Server();
s.Connect(cs);
var xmla = TabularDeployer.GetTMSL(h.Database, s, databaseID, options);
using (var sw = new StreamWriter(xmla_script_file))
{
sw.Write(xmla);
}
Console.WriteLine("XMLA/TMSL script generated.");
}
else
{
Console.WriteLine("Deploying...");
UpdateDeploymentMetadata(h.Model, DeploymentModeMetadata.CLI);
var deploymentResult = TabularDeployer.Deploy(h, cs, databaseID, options);
Console.WriteLine("Deployment succeeded.");
foreach (var err in deploymentResult.Issues)
{
if (errorOnDaxErr)
{
Error(err);
}
else
{
Warning(err);
}
}
foreach (var err in deploymentResult.Warnings)
{
Warning(err);
}
foreach (var err in deploymentResult.Unprocessed)
{
if (warnOnUnprocessed)
{
Warning(err);
}
else
{
Console.WriteLine(err);
}
}
}
}
catch (Exception ex)
{
Error($"{(xmla_scripting_only ? "Script generation" : "Deployment")} failed! {ex.Message}");
}
}
if (testRun != null)
{
testRun.SerializeAsVSTest(testRunFile);
Console.WriteLine("VSTest XML file saved: " + testRunFile);
}
return(true);
}
private static string DeployExistingTMSL(TOM.Database db, TOM.Server server, string dbId, DeploymentOptions options)
{
var orgDb = server.Databases[dbId];
var rawScript = TOM.JsonScripter.ScriptCreateOrReplace(db);
var jObj = JObject.Parse(rawScript);
jObj["createOrReplace"]["object"]["database"] = dbId;
jObj["createOrReplace"]["database"]["id"] = dbId;
jObj["createOrReplace"]["database"]["name"] = orgDb.Name;
var model = jObj.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 orgDb.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 (orgDb.Model.Roles.Contains(roleName))
{
foreach (var member in orgDb.Model.Roles[roleName].Members)
{
members.Add(JObject.Parse(TOM.JsonSerializer.SerializeObject(member)));
}
}
}
}
if (!options.DeployConnections)
{
// Remove dataSources if present
var dataSources = new JArray();
model["dataSources"] = dataSources;
foreach (var ds in orgDb.Model.DataSources)
{
dataSources.Add(JObject.Parse(TOM.JsonSerializer.SerializeObject(ds)));
}
}
if (!options.DeployPartitions)
{
var tables = jObj.SelectToken("createOrReplace.database.model.tables") as JArray;
foreach (var table in tables)
{
var tableName = table["name"].Value <string>();
if (orgDb.Model.Tables.Contains(tableName))
{
var t = orgDb.Model.Tables[tableName];
var partitions = new JArray();
table["partitions"] = partitions;
foreach (var pt in t.Partitions)
{
partitions.Add(JObject.Parse(TOM.JsonSerializer.SerializeObject(pt)));
}
}
}
}
return(jObj.ToString());
}
public static SortedDictionary <string, double> ExecuteGroupByDad(List <string> slicerList, Dictionary <string, object> filterList, DeviceType deviceType)
{
SortedDictionary <string, double> dadDictionary = new SortedDictionary <string, double>();
var algtelPassword = KeyVaultUtil.GetSecretInPlaintext(KeyVaultUtil.SharedAccountName);
string userId = KeyVaultUtil.SharedAccountName + "@microsoft.com";
string ssasServer = "asazure://centralus.asazure.windows.net/datapipelinesaas";
string ConnectionString = $"Password={algtelPassword};Persist Security Info=True;User ID={userId};Data Source = " + ssasServer + ";";
using (Microsoft.AnalysisServices.Tabular.Server server = new Microsoft.AnalysisServices.Tabular.Server())
{
server.Connect(ConnectionString);
string databaseName = deviceType == DeviceType.Hololens ? "HoloLensHackathon" : "OasisHackathon";
Microsoft.AnalysisServices.Tabular.Database tabularDatabase = null;
tabularDatabase = server.Databases.FindByName(databaseName);
string tableName = deviceType == DeviceType.Hololens ? "HoloLens Product Engagement" : "Oasis Product Engagement";
var oasisProductEngagamentTable = tabularDatabase.Model.Tables.Find(tableName);
string measureName = deviceType == DeviceType.Hololens ? "DAD (R28)" : "PC DAD (R28)";
var dadMeasure = oasisProductEngagamentTable.Measures.Where(e => e.Name == measureName).FirstOrDefault();
StringBuilder stbr = new StringBuilder();
foreach (var slicer in slicerList)
{
stbr.Append($"'{tableName}'[" + slicer + "]");
stbr.Append(" , ");
}
string str = stbr.ToString();
str = str.TrimEnd();
str = str.Remove(str.LastIndexOf(","));
string newTableName = (filterList.Count > 0) ? GetStringForGroupBy(tableName, filterList) : $"'{tableName}'";
string queryString = $"EVALUATE SUMMARIZE({newTableName}, {str}, \"Group by measure\"," + dadMeasure.Expression + ")";
string msolapConnectionString = $"Provider=MSOLAP;Data Source={ssasServer};Initial Catalog={databaseName};User ID = {userId};Password = {algtelPassword};Persist Security Info=True; Impersonation Level=Impersonate;";
using (var connection = new OleDbConnection(msolapConnectionString))
{
connection.Open();
using (var command = new OleDbCommand(queryString, connection))
{
using (var reader = command.ExecuteReader())
{
int columns = reader.FieldCount;
while (reader.Read())
{
string keyString = string.Empty;
for (int i = 0; i < columns - 1; i++)
{
keyString += reader[i].ToString() + ", ";
}
keyString = keyString.TrimEnd();
keyString = keyString.Remove(keyString.LastIndexOf(","));
double value = double.Parse(reader[columns - 1].ToString());
value = Math.Round(value, 0);
dadDictionary.Add(keyString, value);
}
}
}
}
}
return(dadDictionary);
}
public static void CreateNewDadMeasure(string measureName, string measureDescription, Dictionary <string, object> filterKeyValuePairs, DeviceType deviceType)
{
var algtelPassword = KeyVaultUtil.GetSecretInPlaintext(KeyVaultUtil.SharedAccountName);
string userId = KeyVaultUtil.SharedAccountName + "@microsoft.com";
string ssasServer = "asazure://centralus.asazure.windows.net/datapipelinesaas";
string ConnectionString = $"Password={algtelPassword};Persist Security Info=True;User ID={userId};Data Source = " + ssasServer + ";";
using (Microsoft.AnalysisServices.Tabular.Server server = new Microsoft.AnalysisServices.Tabular.Server())
{
server.Connect(ConnectionString);
string databaseName = deviceType == DeviceType.Hololens ? "HoloLensHackathon" : "OasisHackathon";
Microsoft.AnalysisServices.Tabular.Database tabularDatabase = null;
tabularDatabase = server.Databases.FindByName(databaseName);
string tableName = deviceType == DeviceType.Hololens ? "HoloLens Product Engagement" : "Oasis Product Engagement";
var oasisProductEngagementTable = tabularDatabase.Model.Tables.Find(tableName);
var newDadMeasure = oasisProductEngagementTable.Measures.Where(e => e.Name == measureName).FirstOrDefault();
if (newDadMeasure != null)
{
oasisProductEngagementTable.Measures.Remove(newDadMeasure);
}
StringBuilder stbr = new StringBuilder();
foreach (var k in filterKeyValuePairs)
{
if (k.Key == "Is Mainstream")
{
stbr.Append($"'{tableName}'[" + k.Key + "] = " + k.Value);
}
else
{
stbr.Append($"'{tableName}'[" + k.Key + "] = \"" + k.Value + "\"");
}
stbr.Append(" , ");
}
string str = stbr.ToString();
str = str.TrimEnd();
str = str.Remove(str.LastIndexOf(","));
string oldDadMeasureName = deviceType == DeviceType.Hololens ? "DAD (R28)" : "PC DAD (R28)";
var oldDadMeasure = oasisProductEngagementTable.Measures.Where(e => e.Name == oldDadMeasureName).FirstOrDefault();
string newDadMeasureExpression = string.Empty;
if (deviceType == DeviceType.Hololens)
{
newDadMeasureExpression = GetLatestDadExpression(oldDadMeasure.Expression, filterKeyValuePairs, tableName);
}
else
{
string oldDadMeasureExpression = oldDadMeasure.Expression;
oldDadMeasureExpression = oldDadMeasureExpression.Substring(oldDadMeasureExpression.IndexOf("AVERAGEX"));
oldDadMeasureExpression = oldDadMeasureExpression.Remove(oldDadMeasureExpression.LastIndexOf(")"));
newDadMeasureExpression = GetLatestDadExpression(oldDadMeasureExpression, filterKeyValuePairs, tableName);
}
string displayFolder = deviceType == DeviceType.Hololens ? @"[Measures]\Device Count" : @"[Measures]\PC Counting";
oasisProductEngagementTable.Measures.Add(CreateMeasure(measureName, newDadMeasureExpression, measureDescription, displayFolder));
tabularDatabase.Model.SaveChanges();
}
}