/// <summary>
/// Creates a new Calculated Table and adds it to the specified Model.
/// Also creates the underlying metadataobject and adds it to the TOM tree.
/// </summary>
public static CalculatedTable CreateNew(Model parent, string name = null, string expression = null)
{
var metadataObject = new TOM.Table();
if (parent.Model.Database.CompatibilityLevel >= 1540)
{
metadataObject.LineageTag = Guid.NewGuid().ToString();
}
metadataObject.Name = parent.Tables.GetNewName(string.IsNullOrWhiteSpace(name) ? "New Calculated Table" : name);
var tomPartition = new TOM.Partition();
tomPartition.Mode = TOM.ModeType.Import;
tomPartition.Name = metadataObject.Name;
tomPartition.Source = new TOM.CalculatedPartitionSource();
metadataObject.Partitions.Add(tomPartition);
var obj = new CalculatedTable(metadataObject);
parent.Tables.Add(obj);
obj.Init();
if (!string.IsNullOrWhiteSpace(expression))
{
obj.Expression = expression;
}
return(obj);
}
internal new static MPartition CreateFromMetadata(Table parent, TOM.Partition metadataObject)
{
var obj = new MPartition(metadataObject);
parent.Partitions.Add(obj);
obj.Init();
return(obj);
}
internal static void CreateCalculatedTablePartition(CalculatedTable calcTable)
{
var tomPartition = new TOM.Partition();
tomPartition.Name = calcTable.Name;
var partition = new Partition(tomPartition);
calcTable.Partitions.Add(partition);
partition.Init();
partition.MetadataObject.Source = new TOM.CalculatedPartitionSource();
}
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);
}
public new static MPartition CreateNew(Table parent, string name = null)
{
if (TabularModelHandler.Singleton.UsePowerBIGovernance && !PowerBI.PowerBIGovernance.AllowCreate(typeof(MPartition)))
{
throw new InvalidOperationException(string.Format(Messages.CannotCreatePowerBIObject, typeof(MPartition).GetTypeName()));
}
var metadataObject = new TOM.Partition();
metadataObject.Name = parent.Partitions.GetNewName(string.IsNullOrWhiteSpace(name) ? "New " + typeof(MPartition).GetTypeName() : name);
var obj = new MPartition(metadataObject);
parent.Partitions.Add(obj);
obj.Init();
return(obj);
}
static partial void InitMetadata(TOM.Table metadataObject, Model parent)
{
if (metadataObject.Partitions.Count > 0)
{
return;
}
var modelHasStructuredDataSources = parent.DataSources.Any(ds => ds.Type == DataSourceType.Structured);
var modelHasPowerQueryPartitions = parent.Tables.Any(t => t.Partitions.Any(p => p.SourceType == PartitionSourceType.M));
var modelHasProviderDataSources = parent.DataSources.Any(ds => ds.Type == DataSourceType.Provider);
// Ensure the table is initialized with a single partition. If the model does not contain any Provider data sources, the type
// of partition added to the table is determind by the following logic:
if (parent.Handler.CompatibilityLevel >= 1400 && !modelHasProviderDataSources &&
(modelHasStructuredDataSources || modelHasPowerQueryPartitions || parent.Handler.Settings.UsePowerQueryPartitionsByDefault)
)
{
var tomPartition = new TOM.Partition
{
Name = metadataObject.Name,
Source = new TOM.MPartitionSource()
};
metadataObject.Partitions.Add(tomPartition);
}
else
{
var tomPartition = new TOM.Partition
{
Name = metadataObject.Name
};
var qps = new TOM.QueryPartitionSource();
tomPartition.Source = qps;
if (!parent.DataSources.Any(ds => ds.Type == DataSourceType.Provider))
{
parent.AddDataSource();
}
qps.DataSource = parent.DataSources.FirstOrDefault(ds => ds.Type == DataSourceType.Provider).MetadataObject;
metadataObject.Partitions.Add(tomPartition);
}
}
/// <summary>
/// Creates a new Calculated Table and adds it to the specified Model.
/// Also creates the underlying metadataobject and adds it to the TOM tree.
/// </summary>
public static CalculatedTable CreateNew(Model parent, string name = null, string expression = null)
{
var metadataObject = new TOM.Table();
metadataObject.Name = parent.Tables.GetNewName(string.IsNullOrWhiteSpace(name) ? "New Calculated Table" : name);
var tomPartition = new TOM.Partition();
tomPartition.Mode = TOM.ModeType.Import;
tomPartition.Name = metadataObject.Name;
tomPartition.Source = new TOM.CalculatedPartitionSource();
metadataObject.Partitions.Add(tomPartition);
var obj = new CalculatedTable(metadataObject);
parent.Tables.Add(obj);
obj.Init();
if (!string.IsNullOrWhiteSpace(expression))
{
obj.Expression = expression;
}
return(obj);
}
protected MPartition(TOM.Partition metadataObject) : base(metadataObject)
{
}
protected PolicyRangePartition(TOM.Partition metadataObject) : base(metadataObject)
{
}
protected MPartition(TOM.Partition metadataObject) : base(metadataObject)
{
metadataObject.Source = new TOM.MPartitionSource();
}
/* Complete feature list, to be noted when complete support added
* - Database
* - Direct Query
* - Datasources (Done)
* - Tables (Done)
* - Translation of table (Done)
* - Columns (Done)
* - Translation of Column (Done)
* - Hierarchies (Done)
* - Translation of Hierarchies (Done)
* - Levels (Done)
* - Translation of Levels (Done)
* - Measures (Done)
* - Translation of Measures (Done)
* - KPI's
* - Perspectives (Done)
* - Roles (Done)
* - Row Level Security (Done)
* - Members (Done)
* - Relationships (Done)
*/
/// <summary>
/// Generates a 1200 Tabular Database, based on the provided 1103 Database.
/// </summary>
/// <param name="AMODatabase">The 1103 Database to create 1200 model from</param>
/// <param name="AddTabularEditorAnnotation">Whether to add an annotation to allow compatibility with TabularEditor</param>
/// <returns></returns>
public static TOM.Database Database(AMO.Database AMODatabase, bool AddTabularEditorAnnotation = true)
{
//Create the database
TOM.Database TOMDatabase = new TOM.Database(AMODatabase.Name);
//Create the model
TOMDatabase.Model = new TOM.Model();
TOMDatabase.Model.Name = AMODatabase.Cubes[0].Name;
#region DataSources
foreach (AMO.DataSource AMODataSource in AMODatabase.DataSources)
{
//Create the data source. We use the ProviderDataSource specifically
TOM.ProviderDataSource TOMDataSource = new TOM.ProviderDataSource
{
Description = AMODataSource.Description,
ConnectionString = AMODataSource.ConnectionString,
MaxConnections = AMODataSource.MaxActiveConnections,
Name = AMODataSource.Name,
Provider = AMODataSource.ManagedProvider,
Timeout = (int)AMODataSource.Timeout.TotalSeconds
};
//Convert AMO ImpersonationMode enum to TOM ImpersonationMode enum
switch (AMODataSource.ImpersonationInfo.ImpersonationMode)
{
case AMO.ImpersonationMode.Default:
TOMDataSource.ImpersonationMode = TOM.ImpersonationMode.Default;
break;
case AMO.ImpersonationMode.ImpersonateAccount:
TOMDataSource.ImpersonationMode = TOM.ImpersonationMode.ImpersonateAccount;
break;
case AMO.ImpersonationMode.ImpersonateAnonymous:
TOMDataSource.ImpersonationMode = TOM.ImpersonationMode.ImpersonateAnonymous;
break;
case AMO.ImpersonationMode.ImpersonateCurrentUser:
TOMDataSource.ImpersonationMode = TOM.ImpersonationMode.ImpersonateCurrentUser;
break;
case AMO.ImpersonationMode.ImpersonateServiceAccount:
TOMDataSource.ImpersonationMode = TOM.ImpersonationMode.ImpersonateServiceAccount;
break;
case AMO.ImpersonationMode.ImpersonateUnattendedAccount:
TOMDataSource.ImpersonationMode = TOM.ImpersonationMode.ImpersonateUnattendedAccount;
break;
}
//Convert AMO Isolation enum to TOM Isolation enum
switch (AMODataSource.Isolation)
{
case AMO.DataSourceIsolation.ReadCommitted:
TOMDataSource.Isolation = TOM.DatasourceIsolation.ReadCommitted;
break;
case AMO.DataSourceIsolation.Snapshot:
TOMDataSource.Isolation = TOM.DatasourceIsolation.Snapshot;
break;
}
//Add the DataSource
TOMDatabase.Model.DataSources.Add(TOMDataSource);
}
#endregion
foreach (AMO.Dimension Dimension in AMODatabase.Dimensions)
{
//Create the table
TOM.Table TOMTable = new TOM.Table();
TOMTable.Description = Dimension.Description;
TOMTable.Name = Dimension.Name;
TOMTable.IsHidden = !AMODatabase.Cubes[0].Dimensions.FindByName(Dimension.Name).Visible;
//Add Translations
foreach (AMO.Translation AMOTranslation in Dimension.Translations)
{
TranslationHelper.AddTOMTranslation(TOMDatabase, TOMTable, AMOTranslation);
}
#region Columns
foreach (AMO.DimensionAttribute Attribute in Dimension.Attributes)
{
if (Attribute.Type != AMO.AttributeType.RowNumber)
{
//Declare "generic" TOM Column, to be assigned to the "specific" column for reuse in
// assigning common properties
TOM.Column TOMColumn;
if (Attribute.NameColumn.Source is AMO.ExpressionBinding)
{
//Create the Calculated Column, and set specific properties
TOM.CalculatedColumn CalculatedColumn = new TOM.CalculatedColumn();
CalculatedColumn.Expression = ((AMO.ExpressionBinding)Attribute.NameColumn.Source).Expression;
//Set as TOMColumn so generic properties can be applied later
TOMColumn = CalculatedColumn;
}
else
{
//Create the Data Column, and set specific properties
TOM.DataColumn DataColumn = new TOM.DataColumn();
DataColumn.SourceColumn = ((AMO.ColumnBinding)Attribute.NameColumn.Source).ColumnID;
DataColumn.IsKey = Attribute.Usage == AMO.AttributeUsage.Key;
DataColumn.IsNullable = Attribute.KeyColumns[0].NullProcessing != AMO.NullProcessing.Error;
//Set as TOMColumn so generic properties can be applied later
TOMColumn = DataColumn;
}
//Generic Properties, shared between both Data Columns and Calculated Columns
TOMColumn.Name = Attribute.Name;
TOMColumn.Description = Attribute.Description;
TOMColumn.DisplayFolder = Attribute.AttributeHierarchyDisplayFolder;
TOMColumn.FormatString = Attribute.FormatString;
TOMColumn.IsHidden = !Attribute.AttributeHierarchyVisible;
TOMColumn.DataType = DataTypeHelper.ToTOMDataType(Attribute.KeyColumns[0].DataType);
//Add translations
foreach (AMO.Translation AMOTranslation in Attribute.Translations)
{
TranslationHelper.AddTOMTranslation(TOMDatabase, TOMColumn, AMOTranslation);
}
//Finally, add the Column to the Table
TOMTable.Columns.Add(TOMColumn);
}
}
//Add sort by columns last
//This is because we cannot add sort by columns referring to columns which do not exist yet
foreach (AMO.DimensionAttribute Attribute in Dimension.Attributes)
{
if (Attribute.Type != AMO.AttributeType.RowNumber && Attribute.OrderByAttribute != null)
{
TOMTable.Columns[Attribute.Name].SortByColumn = TOMTable.Columns[Attribute.OrderByAttribute.Name];
}
}
#endregion
//Add the Table
TOMDatabase.Model.Tables.Add(TOMTable);
#region Hierarchies
foreach (AMO.Hierarchy AMOHierarchy in Dimension.Hierarchies)
{
//Create the hierarchy
TOM.Hierarchy TOMHierarchy = new TOM.Hierarchy();
TOMHierarchy.Name = AMOHierarchy.Name;
TOMHierarchy.Description = AMOHierarchy.Description;
TOMHierarchy.DisplayFolder = AMOHierarchy.DisplayFolder;
//AMO Hierarchies are always visible, from what I can tell
TOMHierarchy.IsHidden = false;
//Add translations
foreach (AMO.Translation AMOTranslation in AMOHierarchy.Translations)
{
TranslationHelper.AddTOMTranslation(TOMDatabase, TOMHierarchy, AMOTranslation);
}
foreach (AMO.Level AMOLevel in AMOHierarchy.Levels)
{
//Create the level
TOM.Level TOMLevel = new TOM.Level();
TOMLevel.Name = AMOLevel.Name;
TOMLevel.Description = AMOLevel.Description;
TOMLevel.Column = TOMTable.Columns[AMOLevel.SourceAttribute.Name];
//Add translations
foreach (AMO.Translation AMOTranslation in AMOLevel.Translations)
{
TranslationHelper.AddTOMTranslation(TOMDatabase, TOMLevel, AMOTranslation);
}
//Add the Level
TOMHierarchy.Levels.Add(TOMLevel);
}
//Add the Hierarchy
TOMTable.Hierarchies.Add(TOMHierarchy);
}
#endregion
#region Partitions
foreach (AMO.Partition AMOPartition in AMODatabase.Cubes[0].MeasureGroups.GetByName(TOMTable.Name).Partitions)
{
//Create the partition
TOM.Partition TOMPartition = new TOM.Partition
{
Description = AMOPartition.Description,
//Add the query
Source = new TOM.QueryPartitionSource
{
DataSource = TOMDatabase.Model.DataSources[AMOPartition.DataSource.Name],
Query = ((AMO.QueryBinding)AMOPartition.Source).QueryDefinition
}
};
//Add the Partition
TOMTable.Partitions.Add(TOMPartition);
}
#endregion
}
#region Measures
foreach (AMO.Command Command in AMODatabase.Cubes[0].MdxScripts[0].Commands)
{
List <MDXCommand> Commands = MDXCommandParser.GetCommands(Command.Text);
if (Commands.Count > 0)
{
MDXCommand MainCommand = Commands[0];
if (MainCommand.Type == CommandType.CreateMeasure)
{
List <MDXString> Strings = MDXCommandParser.GetStrings(MainCommand.LHS);
//Throw exception if we do not have a valid CREATE MEASURE command.
/* We do not care if a command is claid, only if we can parse it.
* We need two strings:
* - A single quoted string, representing the table name
* - A square bracketed string, representing the measure name
* As long as they are present, we consider it valid.
*/
if (Strings.Count < 2 || Strings[0].Type != StringType.SingleQuote || Strings[1].Type != StringType.SquareBracket)
{
throw new System.Exception("A CREATE MEASURE statement must at least have two delimited elements (one table, one measure name)");
}
//First, single quoted string, is table name
string TableName = Strings[0].Text;
//Then, square-bracket delimited string is the measure name.
string MeasureName = Strings[1].Text;
//Find the existing CalculationProperty, containing the relevant properties of the Measure, if it exists
AMO.CalculationProperty CalculationProperty = AMODatabase.Cubes[0].MdxScripts[0].CalculationProperties.Find("[" + MeasureName.Replace("]", "]]") + "]");
//Create the Measure
TOM.Measure TOMMeasure = new TOM.Measure
{
Name = MeasureName,
Expression = MainCommand.RHS,
Description = CalculationProperty?.Description,
DisplayFolder = CalculationProperty?.DisplayFolder,
//AMO format string is wrapped in single quotes, so we need to get rid of them here
FormatString = CalculationProperty?.FormatString.Substring(1, CalculationProperty.FormatString.Length - 2),
IsHidden = CalculationProperty == null ? true : !CalculationProperty.Visible
};
//Add Translations
if (CalculationProperty != null)
{
foreach (AMO.Translation AMOTranslation in CalculationProperty.Translations)
{
TranslationHelper.AddTOMTranslation(TOMDatabase, TOMMeasure, AMOTranslation);
}
}
//Add the Measure
TOMDatabase.Model.Tables[TableName].Measures.Add(TOMMeasure);
}
}
}
#endregion
#region Perspectives
foreach (AMO.Perspective AMOPerspective in AMODatabase.Cubes[0].Perspectives)
{
//Create the Perspective
TOM.Perspective TOMPerspective = new TOM.Perspective
{
Name = AMOPerspective.Name,
Description = AMOPerspective.Description
};
foreach (AMO.PerspectiveDimension AMOPerspectiveDimension in AMOPerspective.Dimensions)
{
//Find the TOM Table equivelant to the AMO Perspective Dimension
TOM.Table TOMTable = TOMDatabase.Model.Tables[AMOPerspectiveDimension.Dimension.Name];
//Create the Perspective Table
TOM.PerspectiveTable TOMPerspectiveTable = new TOM.PerspectiveTable {
Table = TOMTable
};
//Add Columns
foreach (AMO.PerspectiveAttribute PerspectiveAttribute in AMOPerspectiveDimension.Attributes)
{
TOMPerspectiveTable.PerspectiveColumns.Add(new TOM.PerspectiveColumn
{
Column = TOMTable.Columns[PerspectiveAttribute.Attribute.Name]
});
}
//Add Hierarchies
foreach (AMO.PerspectiveHierarchy PerspectiveHierarchy in AMOPerspectiveDimension.Hierarchies)
{
TOMPerspectiveTable.PerspectiveHierarchies.Add(new TOM.PerspectiveHierarchy
{
Hierarchy = TOMTable.Hierarchies[PerspectiveHierarchy.Hierarchy.Name]
});
}
//Add Measures
foreach (AMO.PerspectiveCalculation PerspectiveCalculation in AMOPerspective.Calculations)
{
foreach (TOM.Measure TOMMeasure in TOMTable.Measures)
{
if (
PerspectiveCalculation.Type == AMO.PerspectiveCalculationType.Member &&
PerspectiveCalculation.Name == "[Measures].[" + TOMMeasure.Name + "]"
)
{
TOMPerspectiveTable.PerspectiveMeasures.Add(new TOM.PerspectiveMeasure {
Measure = TOMMeasure
});
}
}
}
//Add the Perspective Table
TOMPerspective.PerspectiveTables.Add(TOMPerspectiveTable);
}
//Add the Perspective
TOMDatabase.Model.Perspectives.Add(TOMPerspective);
}
#endregion
#region Roles
foreach (AMO.Role AMORole in AMODatabase.Roles)
{
//Create the Role
TOM.ModelRole TOMRole = new TOM.ModelRole
{
Name = AMORole.Name,
Description = AMORole.Description
};
//Determine the ModelPermission from the equivelant DatabasePermission
foreach (AMO.DatabasePermission Permission in AMODatabase.DatabasePermissions)
{
if (Permission.Role.Name == AMORole.Name)
{
if (Permission.Administer)
{
TOMRole.ModelPermission = TOM.ModelPermission.Administrator;
}
else if (Permission.Read == AMO.ReadAccess.Allowed && Permission.Process)
{
TOMRole.ModelPermission = TOM.ModelPermission.ReadRefresh;
}
else if (Permission.Read == AMO.ReadAccess.Allowed)
{
TOMRole.ModelPermission = TOM.ModelPermission.Read;
}
else if (Permission.Process)
{
TOMRole.ModelPermission = TOM.ModelPermission.Refresh;
}
else
{
TOMRole.ModelPermission = TOM.ModelPermission.None;
}
}
}
//Add the Row Level Security
foreach (AMO.Dimension Dimension in AMODatabase.Dimensions)
{
foreach (AMO.DimensionPermission DimensionPermission in Dimension.DimensionPermissions)
{
if (DimensionPermission.Role.Name == TOMRole.Name)
{
//Create the Table Permission
TOM.TablePermission TablePermission = new TOM.TablePermission
{
Table = TOMDatabase.Model.Tables[Dimension.Name],
FilterExpression = DimensionPermission.AllowedRowsExpression
};
//Add the Table Permission to the Role
TOMRole.TablePermissions.Add(TablePermission);
}
}
}
//Add Role Members
foreach (AMO.RoleMember AMOMember in AMORole.Members)
{
//Create the Role Member
TOM.ModelRoleMember TOMMember = new TOM.WindowsModelRoleMember
{
MemberID = AMOMember.Sid,
MemberName = AMOMember.Name
};
//Add the Member to the Role
TOMRole.Members.Add(TOMMember);
}
//Add the Role to the Database
TOMDatabase.Model.Roles.Add(TOMRole);
}
#endregion
#region Relationships
foreach (AMO.Dimension Dimension in AMODatabase.Dimensions)
{
foreach (AMO.Relationship AMORelationship in Dimension.Relationships)
{
//Get To and From columns
AMO.Dimension FromDimension = AMODatabase.Dimensions[AMORelationship.FromRelationshipEnd.DimensionID];
AMO.Dimension ToDimension = AMODatabase.Dimensions[AMORelationship.ToRelationshipEnd.DimensionID];
TOM.Table FromTable = TOMDatabase.Model.Tables[FromDimension.Name];
TOM.Column FromColumn = FromTable.Columns[FromDimension.Attributes[AMORelationship.FromRelationshipEnd.Attributes[0].AttributeID].Name];
TOM.Table ToTable = TOMDatabase.Model.Tables[ToDimension.Name];
TOM.Column ToColumn = ToTable.Columns[ToDimension.Attributes[AMORelationship.ToRelationshipEnd.Attributes[0].AttributeID].Name];
//Create the Relationship
TOM.SingleColumnRelationship TOMRelationship = new TOM.SingleColumnRelationship
{
FromColumn = FromColumn,
ToColumn = ToColumn,
//Set IsActive to false, and update later
IsActive = false
};
//Check if Relationship is active
foreach (AMO.MeasureGroupDimension MeasureGroupDimension in AMODatabase.Cubes[0].MeasureGroups.GetByName(FromTable.Name).Dimensions)
{
if (MeasureGroupDimension is AMO.ReferenceMeasureGroupDimension)
{
if (((AMO.ReferenceMeasureGroupDimension)MeasureGroupDimension).RelationshipID == AMORelationship.ID)
{
TOMRelationship.IsActive = true;
}
}
}
//Add the Relationship to the Database
TOMDatabase.Model.Relationships.Add(TOMRelationship);
}
}
#endregion
//Add TabularEditor compatibility annotation if necessary
if (AddTabularEditorAnnotation)
{
TOMDatabase.Model.Annotations.Add(new TOM.Annotation {
Name = "TabularEditor_CompatibilityVersion",
Value = AMODatabase.CompatibilityLevel.ToString()
});
}
//TODO: Handle KPIs
return(TOMDatabase);
}