public ExternalChangeTrace(TOM.Database database, string applicationName, Action <TOM.TraceEventArgs> onExternalChangeCallback) { this.applicationName = applicationName; this.onExternalChangeCallback = onExternalChangeCallback; this.database = database; this.databaseNameWhenTraceStarted = database.Name; }
internal static List <Tuple <TOM.NamedMetadataObject, string> > CheckErrors(TOM.Database database) { var result = new List <Tuple <TOM.NamedMetadataObject, string> >(); foreach (var t in database.Model.Tables) { result.AddRange(t.Measures.Where(m => !string.IsNullOrEmpty(m.ErrorMessage)).Select(m => new Tuple <TOM.NamedMetadataObject, string>(m, m.ErrorMessage))); if (database.CompatibilityLevel >= 1400) { result.AddRange(t.Measures.Where(m => !string.IsNullOrEmpty(m.DetailRowsDefinition?.ErrorMessage)).Select(m => new Tuple <TOM.NamedMetadataObject, string>(m, "Detail rows expression: " + m.DetailRowsDefinition.ErrorMessage))); } result.AddRange(t.Columns.Where(c => !string.IsNullOrEmpty(c.ErrorMessage)).Select(c => new Tuple <TOM.NamedMetadataObject, string>(c, c.ErrorMessage))); result.AddRange(t.Partitions.Where(p => !string.IsNullOrEmpty(p.ErrorMessage)).Select(p => new Tuple <TOM.NamedMetadataObject, string>(p, p.ErrorMessage))); if (database.CompatibilityLevel >= 1470 && t.CalculationGroup != null) { result.AddRange(t.CalculationGroup.CalculationItems.Where(ci => !string.IsNullOrEmpty(ci.ErrorMessage)).Select(ci => new Tuple <TOM.NamedMetadataObject, string>(ci, ci.ErrorMessage))); result.AddRange(t.CalculationGroup.CalculationItems.Where(ci => !string.IsNullOrEmpty(ci.FormatStringDefinition?.ErrorMessage)).Select(ci => new Tuple <TOM.NamedMetadataObject, string>(ci, "Format string expression: " + ci.FormatStringDefinition.ErrorMessage))); } } foreach (var r in database.Model.Roles) { result.AddRange(r.TablePermissions.Where(tp => !string.IsNullOrEmpty(tp.ErrorMessage)).Select(tp => new Tuple <TOM.NamedMetadataObject, string>(tp, tp.ErrorMessage))); } return(result); }
public static void RemoveTabularEditorTag(this TOM.Database database) { if (database.Model.Annotations.Contains(TabularEditorTag)) { database.Model.Annotations.Remove(TabularEditorTag); } }
/// <summary> /// Adds a translation for the TOM object based on the supplied AMOTranslation. /// Creates the relevant culture if it does not exist. /// </summary> /// <param name="TOMDatabase">The TOM Database to add the Translation to</param> /// <param name="TOMObject">The TOM Object the translation is being added for</param> /// <param name="AMOTranslation">The AMO Translation being transferred to the TOM Database</param> public static void AddTOMTranslation(TOM.Database TOMDatabase, TOM.MetadataObject TOMObject, AMO.Translation AMOTranslation) { string CultureName = TranslationHelper.GetCultureNameFromLCID(AMOTranslation.Language); //Add Culture if it does not exist if (!TOMDatabase.Model.Cultures.ContainsName(CultureName)) { TOMDatabase.Model.Cultures.Add(new TOM.Culture { Name = CultureName }); } //Get existing culture TOM.Culture TOMCulture = TOMDatabase.Model.Cultures.Find(CultureName); //Add the various translated properties to the translation TOMCulture.ObjectTranslations.Add( new TOM.ObjectTranslation { Object = TOMObject, Property = TOM.TranslatedProperty.Caption, Value = AMOTranslation.Caption } ); TOMCulture.ObjectTranslations.Add( new TOM.ObjectTranslation { Object = TOMObject, Property = TOM.TranslatedProperty.Description, Value = AMOTranslation.Description } ); TOMCulture.ObjectTranslations.Add( new TOM.ObjectTranslation { Object = TOMObject, Property = TOM.TranslatedProperty.DisplayFolder, Value = AMOTranslation.DisplayFolder } ); }
/// <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 succesful. /// </summary> /// <param name="db"></param> /// <param name="targetConnectionString"></param> /// <param name="targetDatabaseName"></param> /// <param name="options"></param> /// <returns></returns> internal static DeploymentResult Deploy(TOM.Database db, string targetConnectionString, string targetDatabaseName, DeploymentOptions options, CancellationToken cancellationToken) { if (string.IsNullOrWhiteSpace(targetConnectionString)) { throw new ArgumentNullException("targetConnectionString"); } var destinationServer = new TOM.Server(); 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); 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: var deployedDB = destinationServer.Databases.GetByName(targetDatabaseName); deployedDB.Refresh(true); return(GetLastDeploymentResults(deployedDB)); }
/// <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); }
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; db.AddTabularEditorTag(); 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); }
private void cboDatabaseName_SelectedIndexChanged(object sender, EventArgs e) { cboCubeName.Items.Clear(); cboCubeName.SelectedItem = null; //OLAPDatabase = OLAPServer.Databases[cboDatabaseName.SelectedItem.ToString()]; OLAPDatabase = OLAPServer.Databases[(cboDatabaseName.SelectedItem as ComboboxItem).Value.ToString()]; TOMDb = TOMServer.Databases[(cboDatabaseName.SelectedItem as ComboboxItem).Value.ToString()]; TabularCompatibilityLevel = TOMDb.CompatibilityLevel; if (TabularCompatibilityLevel < 1200) { cboCubeName.Enabled = true; foreach (AMO.Cube OLAPCube in OLAPDatabase.Cubes) { cboCubeName.Items.Add(OLAPCube.Name); } if (cboCubeName.Items.Count == 1) { cboCubeName.SelectedItem = cboCubeName.Items[0]; } } else { cboCubeName.Enabled = false; txtFileName.Text = cboDatabaseName.SelectedItem.ToString(); } }
/// <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 JObject TransformCreateOrReplaceTmsl(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") as JObject; if (!options.DeployRoles) { ReplaceRolesFromDestination(model, destDb.Model); } else if (!options.DeployRoleMembers) { ReplaceRoleMembersFromDestination(model, destDb.Model); } if (options.RemoveRoleMemberIds) { RemoveRoleMemberIDs(model); } if (!options.DeployConnections) { ReplaceDataSourcesFromDestination(model, destDb.Model); } if (!options.DeployPartitions || options.SkipRefreshPolicyPartitions) { ReplacePartitionsFromDestination(model, db.Model.Tables, destDb.Model, options); } return(tmslJObj); }
/// <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 succesful. /// </summary> /// <param name="db"></param> /// <param name="targetConnectionString"></param> /// <param name="targetDatabaseID"></param> /// <param name="options"></param> /// <returns></returns> internal static DeploymentResult Deploy(TOM.Database db, string targetConnectionString, string targetDatabaseID, DeploymentOptions options, CancellationToken cancellationToken) { if (string.IsNullOrWhiteSpace(targetConnectionString)) { throw new ArgumentNullException("targetConnectionString"); } var s = new TOM.Server(); s.Connect(targetConnectionString); var tmsl = GetTMSL(db, s, targetDatabaseID, options, true); cancellationToken.Register(s.CancelCommand); var result = s.Execute(tmsl); if (result.ContainsErrors) { throw new Exception(string.Join("\n", result.Cast <XmlaResult>().SelectMany(r => r.Messages.Cast <XmlaMessage>().Select(m => m.Description)).ToArray())); } s.Refresh(); var deployedDB = s.Databases[targetDatabaseID]; return (new DeploymentResult( TabularModelHandler.CheckErrors(deployedDB).Select(t => string.Format("Error on {0}: {1}", GetName(t.Item1), t.Item2)), TabularModelHandler.GetObjectsNotReady(deployedDB).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(deployedDB).Where(t => t.Item2 == TOM.ObjectState.CalculationNeeded || t.Item2 == TOM.ObjectState.NoData) .Select(t => string.Format("Information: Unprocessed object: {0} ({1})", GetName(t.Item1), t.Item2.ToString())) )); }
public static string GetTMSL(TOM.Database db, TOM.Server server, string targetDatabaseID, DeploymentOptions options, bool includeRestricted = false) { if (db == null) { throw new ArgumentNullException("db"); } if (string.IsNullOrWhiteSpace(targetDatabaseID)) { throw new ArgumentNullException("targetDatabaseID"); } if (options.DeployRoleMembers && !options.DeployRoles) { throw new ArgumentException("Cannot deploy Role Members when Role deployment is disabled."); } if (server.Databases.Contains(targetDatabaseID) && options.DeployMode == DeploymentMode.CreateDatabase) { throw new ArgumentException("The specified database already exists."); } if (!server.Databases.Contains(targetDatabaseID)) { return(DeployNewTMSL(db, targetDatabaseID, options, includeRestricted)); } else { return(DeployExistingTMSL(db, server, targetDatabaseID, options, includeRestricted)); } // TODO: Check if invalid CalculatedTableColumn perspectives/translations can give us any issues here // Should likely be handled similar to what we do in TabularModelHandler.SaveDB() }
/// <summary> /// Creates a new blank Tabular Model /// </summary> public TabularModelHandler(int compatibilityLevel = 1200, TabularModelHandlerSettings settings = null, bool pbiDatasetModel = false) { Settings = settings ?? TabularModelHandlerSettings.Default; Singleton = this; server = null; database = new TOM.Database("SemanticModel") { CompatibilityLevel = compatibilityLevel, CompatibilityMode = pbiDatasetModel ? Microsoft.AnalysisServices.CompatibilityMode.PowerBI : Microsoft.AnalysisServices.CompatibilityMode.AnalysisServices }; CompatibilityLevel = compatibilityLevel; database.Model = new TOM.Model(); if (pbiDatasetModel) { database.Model.DefaultPowerBIDataSourceVersion = TOM.PowerBIDataSourceVersion.PowerBI_V3; } SourceType = ModelSourceType.File; Source = "Model.bim"; Status = "Succesfully created new model."; Init(); UndoManager.Enabled = true; PowerBIGovernance.UpdateGovernanceMode(this); }
private void ProcessAnalysisServicesDatabase() { var accessToken = GetAccessToken("https://" + AasService); var startTime = DateTime.UtcNow; using (Tab.Server aas = new Tab.Server()) { string connStr = $"Provider=MSOLAP;Data Source=asazure://{AasService}/{AasServer};Password={accessToken};Persist Security Info=True;Impersonation Level=Delegate;"; aas.Connect(connStr); Tab.Database db = null; if (!aas.Databases.ContainsName(AasDatabase)) { // note: in case database is not found, verify service principal has *admin* rights - read/process rights are insufficent throw new ApplicationException($"Database '{AasDatabase}' not found. Make sure service principal has database Admin rights."); } db = aas.Databases[AasDatabase]; db.Model.RequestRefresh(Tab.RefreshType.Full); db.Model.SaveChanges(); // commit executes the refresh aas.Disconnect(); } var processTime = DateTime.UtcNow.Subtract(startTime); _logger.LogInformation($"database processed in {processTime.TotalSeconds:n1} s"); }
internal Database(Model model, Microsoft.AnalysisServices.Core.Database tomDatabase) { Debug.Assert(tomDatabase != null); var db = tomDatabase as TOM.Database; TOMDatabase = db; _model = model; }
internal static string DeployNewTMSL(TOM.Database db, string newDbId, DeploymentOptions options, bool includeRestricted) { var rawTmsl = TOM.JsonScripter.ScriptCreate(db, includeRestricted); var jTmsl = JObject.Parse(rawTmsl); return(jTmsl.TransformCreateTmsl(newDbId, options).FixCalcGroupMetadata(db).ToString()); }
private static string DeployNewTMSL(TOM.Database db, string newDbId, DeploymentOptions options, bool includeRestricted) { var rawTmsl = TOM.JsonScripter.ScriptCreate(db, includeRestricted); var jTmsl = JObject.Parse(rawTmsl); return(TransformCreateTmsl(jTmsl, newDbId, options).ToString()); }
public static void RemoveTabularEditorTag(this TOM.Database database) { const string annotationName = "__TEdtr"; if (database.Model.Annotations.Contains(annotationName)) { database.Model.Annotations.Remove(annotationName); } }
internal Database(Model model, Microsoft.AnalysisServices.Core.Database tomDatabase) { var db = tomDatabase as TOM.Database; TOMDatabase = db; _model = model; _name = tomDatabase.Name; _id = tomDatabase.ID; }
public static ReferenceCulture Create(TOM.Database database) { return(new ReferenceCulture { Name = database.Name, Id = database.ID, Model = ReferenceModel.Create(database.Model) }); }
private static string DeployExistingTMSL(TOM.Database db, TOM.Server server, string dbId, DeploymentOptions options, bool includeRestricted) { var rawTmsl = TOM.JsonScripter.ScriptCreateOrReplace(db, includeRestricted); var orgDb = server.Databases[dbId]; var jTmsl = JObject.Parse(rawTmsl); return(TransformCreateOrReplaceTmsl(jTmsl, orgDb, options).ToString()); }
/// <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.serverName = serverName; _disableUpdates = true; Settings = settings ?? TabularModelHandlerSettings.Default; Singleton = this; 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.GetByName(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(); Model.ClearTabularEditorAnnotations(); _disableUpdates = false; UndoManager.Enabled = true; PowerBIGovernance.UpdateGovernanceMode(this); CheckErrors(); trace = new ExternalChangeTrace(database, applicationName, XEventCallback); if (Settings.ChangeDetectionLocalServers) { trace.Start(); } }
public static void AddTabularEditorTag(this TOM.Database database) { if (!database.Model.Annotations.Contains(TabularEditorTag)) { var annotation = new TOM.Annotation() { Name = TabularEditorTag, Value = "1" }; database.Model.Annotations.Add(annotation); } }
public static AsServerType GetServerType(this TOM.Database database) { var server = database.Server; if (server == null) { return(AsServerType.NotConnected); } return(server.GetServerType()); }
private static Microsoft.AnalysisServices.Tabular.Database CreateDatabase(string databaseName) { var database = new Microsoft.AnalysisServices.Tabular.Database() { Name = databaseName, ID = databaseName, CompatibilityLevel = 1200, StorageEngineUsed = StorageEngineUsed.TabularMetadata, }; return(database); }
internal static List <Tuple <TOM.NamedMetadataObject, string> > CheckErrors(TOM.Database database) { var result = new List <Tuple <TOM.NamedMetadataObject, string> >(); foreach (var t in database.Model.Tables) { result.AddRange(t.Measures.Where(m => !string.IsNullOrEmpty(m.ErrorMessage)).Select(m => new Tuple <TOM.NamedMetadataObject, string>(m, m.ErrorMessage))); result.AddRange(t.Columns.Where(c => !string.IsNullOrEmpty(c.ErrorMessage)).Select(c => new Tuple <TOM.NamedMetadataObject, string>(c, c.ErrorMessage))); result.AddRange(t.Partitions.Where(p => !string.IsNullOrEmpty(p.ErrorMessage)).Select(p => new Tuple <TOM.NamedMetadataObject, string>(p, p.ErrorMessage))); } return(result); }
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 Microsoft.AnalysisServices.Tabular.Partition CreatePartition(Microsoft.AnalysisServices.Tabular.Database database, string name, string dataSourceName, string query) { Microsoft.AnalysisServices.Tabular.Partition partition = new Microsoft.AnalysisServices.Tabular.Partition(); partition.Name = name; partition.Source = new QueryPartitionSource() { DataSource = database.Model.DataSources.Where(s => s.Name == dataSourceName).FirstOrDefault(), Query = query, }; return(partition); }
internal static string DeployNewTMSL(TOM.Database db, string targetDatabaseName, DeploymentOptions options, bool includeRestricted, Microsoft.AnalysisServices.CompatibilityMode compatibilityMode) { var rawTmsl = TOM.JsonScripter.ScriptCreate(db, includeRestricted); var jTmsl = JObject.Parse(rawTmsl); if (jTmsl["create"]["database"]["compatibilityMode"] != null) { jTmsl["create"]["database"]["compatibilityMode"] = compatibilityMode.ToString(); } return(jTmsl.TransformCreateTmsl(targetDatabaseName, options).FixCalcGroupMetadata(db).ToString()); }
public static void Deploy(TOM.Database db, TOM.Server s, string targetDatabaseID, DeploymentOptions options) { var tmsl = GetTMSL(db, s, targetDatabaseID, options, true); var result = s.Execute(tmsl); if (result.ContainsErrors) { throw new Exception(string.Join("\n", result.Cast <XmlaResult>().SelectMany(r => r.Messages.Cast <XmlaMessage>().Select(m => m.Description)).ToArray())); } s.Refresh(); var deployedDB = s.Databases[targetDatabaseID]; }
/// <summary> /// Writes a TOM database to the specified location /// </summary> /// <param name="FileLocation">The location to write the file to</param> /// <param name="Database">The database to serialise</param> public static void WriteToFile(string FileLocation, TOM.Database Database) { string SerialisedDatabase = TOM.JsonSerializer.SerializeDatabase(Database, new TOM.SerializeOptions { IgnoreInferredObjects = true, IgnoreInferredProperties = true, IgnoreTimestamps = true, IncludeRestrictedInformation = false, SplitMultilineStrings = true }); System.IO.File.WriteAllText(FileLocation, SerialisedDatabase); }