private void AddTable(Tom.Table table)
{
var partitions = table.Partitions;
Tom.PartitionSourceType tableType = (partitions?.Count > 0) ? (partitions[0].SourceType) : Tom.PartitionSourceType.None;
bool isCalculatedTable = (tableType == Tom.PartitionSourceType.Calculated);
bool isCalculationGroup = (tableType == Tom.PartitionSourceType.CalculationGroup);
var partitionSource = (isCalculatedTable) ? partitions[0].Source as Tom.CalculatedPartitionSource : null;
Dax.Metadata.Table daxTable = new Dax.Metadata.Table(DaxModel)
{
TableName = new Dax.Metadata.DaxName(table.Name),
IsHidden = table.IsHidden,
IsPrivate = table.IsPrivate,
IsLocalDateTable = (table.Annotations.FirstOrDefault(a => a.Name == "__PBI_LocalDateTable" && a.Value == "true") != null),
IsTemplateDateTable = (table.Annotations.FirstOrDefault(a => a.Name == "__PBI_TemplateDateTable" && a.Value == "true") != null),
TableExpression = Dax.Metadata.DaxExpression.GetExpression(isCalculatedTable ? partitionSource.Expression : null),
TableType = isCalculatedTable ? Table.TableSourceType.CalculatedTable.ToString() :
(isCalculationGroup ? Table.TableSourceType.CalculationGroup.ToString() : null),
Description = table.Description
};
foreach (var column in table.Columns)
{
AddColumn(daxTable, column);
}
foreach (var measure in table.Measures)
{
AddMeasure(daxTable, measure);
}
foreach (var hierarchy in table.Hierarchies)
{
AddUserHierarchy(daxTable, hierarchy);
}
// Add calculation groups and calculation items
if (table.CalculationGroup != null)
{
var calcGroup = new CalculationGroup(daxTable)
{
Precedence = table.CalculationGroup.Precedence
};
foreach (var calcItem in table.CalculationGroup.CalculationItems)
{
AddCalculationItem(calcGroup, calcItem);
}
daxTable.CalculationGroup = calcGroup;
// Set the first column of the table that is not a RowNumber as a calculation group attribute
foreach (var column in daxTable.Columns)
{
if (!column.IsRowNumber)
{
column.IsCalculationGroupAttribute = true;
break;
}
}
}
DaxModel.Tables.Add(daxTable);
}
/// <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 CalculatedTable CreateFromMetadata(Model parent, TOM.Table metadataObject)
{
if (metadataObject.GetSourceType() != TOM.PartitionSourceType.Calculated)
{
throw new ArgumentException("Provided metadataObject is not a Calculated Table.");
}
// Generate a new LineageTag if an object with the provided lineage tag already exists:
if (!string.IsNullOrEmpty(metadataObject.LineageTag))
{
if (parent.Handler.CompatibilityLevel < 1540)
{
metadataObject.LineageTag = null;
}
else if (parent.MetadataObject.Tables.FindByLineageTag(metadataObject.LineageTag) != metadataObject)
{
metadataObject.LineageTag = Guid.NewGuid().ToString();
}
}
var obj = new CalculatedTable(metadataObject);
parent.Tables.Add(obj);
obj.Init();
return(obj);
}
public new static CalculationGroupTable CreateNew(Model parent, string name = null)
{
parent.DiscourageImplicitMeasures = true;
var metadataObject = new TOM.Table()
{
CalculationGroup = new TOM.CalculationGroup()
};
if (parent.Model.Database.CompatibilityLevel >= 1540)
{
metadataObject.LineageTag = Guid.NewGuid().ToString();
}
var nameCol = new TOM.DataColumn {
Name = "Name", DataType = TOM.DataType.String, SourceColumn = "Name"
};
var ordinalCol = new TOM.DataColumn {
Name = "Ordinal", DataType = TOM.DataType.Int64, IsHidden = true, SourceColumn = "Ordinal"
};
metadataObject.Columns.Add(nameCol);
metadataObject.Columns.Add(ordinalCol);
nameCol.SortByColumn = ordinalCol;
metadataObject.Partitions.Add(new TOM.Partition {
Source = new TOM.CalculationGroupSource(), Mode = TOM.ModeType.Import
});
metadataObject.Name = parent.Tables.GetNewName(string.IsNullOrWhiteSpace(name) ? "New Calculation Group" : name);
return(CreateFromMetadata(parent, metadataObject));
}
public static Table GetTable(TabModel.Table t, List <TabModel.Measure> unsupportedMeasures)
{
var table = new Table()
{
Name = t.Name,
Columns = new List <Column>(),
Measures = new List <Measure>()
};
foreach (var c in t.Columns)
{
table.Columns.Add(GetColumn(c));
}
foreach (var m in t.Measures)
{
if (!unsupportedMeasures.Contains(m))
{
table.Measures.Add(
new Measure
{
Name = m.Name,
Expression = m.Expression,
IsHidden = m.IsHidden,
FormatString = m.FormatString,
Description = m.Description
}
);
}
}
return(table);
}
/// <summary>
/// Initializes a new instance of the Table class using multiple parameters.
/// </summary>
/// <param name="parentTabularModel">TabularModel object that the Table object belongs to.</param>
/// <param name="tomTable">Tabular Object Model Table object abtstracted by the Table class.</param>
public Table(TabularModel parentTabularModel, Tom.Table tomTable) : base(tomTable, parentTabularModel)
{
_parentTabularModel = parentTabularModel;
_tomTable = tomTable;
PopulateProperties();
}
private static Microsoft.AnalysisServices.Tabular.Table CreateTable(string tableName, string tableDescription)
{
var table = new Microsoft.AnalysisServices.Tabular.Table()
{
Name = tableName,
Description = tableDescription
};
return(table);
}
/// <summary>
/// Create a relationship for the Table object, with validation to ensure referential integrity.
/// </summary>
/// <param name="relationshipSource">Relationship object from the source tabular model.</param>
/// <param name="toTomTableSource">Tabular Object Model Table representing "to table" in the relationship.</param>
/// <param name="relationshipName">Name of the relationship to be created.</param>
/// <param name="warningMessage">Warning message to return to caller.</param>
/// <returns>Boolean indicating if creation was successful.</returns>
public bool CreateRelationshipWithValidation(Relationship relationshipSource, Tom.Table toTomTableSource, string relationshipName, ref string warningMessage)
{
SingleColumnRelationship tabularRelationshipSource = relationshipSource.TomRelationship;
// Check if "to" table exists (don't need to check "from" table as we are in the "from" table) ...
if (!_parentTabularModel.Tables.ContainsName(toTomTableSource.Name))
{
warningMessage = $"Unable to create Relationship {relationshipName} because (considering changes) parent table not found in target model.";
return(false);
}
// does the required child column exist? In this case need to check child column as user might have skipped Update of table meaning columns are out of sync.
if (!_tomTable.Columns.ContainsName(tabularRelationshipSource.FromColumn.Name))
{
warningMessage = $"Unable to create Relationship {relationshipName} because (considering changes) child column not found in target model.";
return(false);
}
// does the required "to" column exist?
Tom.Table toTableTarget = _parentTabularModel.Tables.FindByName(tabularRelationshipSource.ToTable.Name).TomTable;
if (
(toTableTarget == null) ||
(!toTableTarget.Columns.ContainsName(tabularRelationshipSource.ToColumn.Name))
)
{
warningMessage = $"Unable to create Relationship {relationshipName} because (considering changes) parent column not found in target model.";
return(false);
}
if (this.IsCalculationGroup || _parentTabularModel.Tables.FindByName(tabularRelationshipSource.ToTable.Name).IsCalculationGroup)
{
warningMessage = $"Unable to create Relationship {relationshipName} because one or more tables is a calculation group.";
return(false);
}
// Delete the target relationship with same tables/columns if still there. Not using RemoveByInternalName in case internal name is actually different.
if (this.Relationships.ContainsName(relationshipSource.Name))
{
warningMessage = $"Unable to create Relationship {relationshipName} because (considering changes) relationship already exists in target model.";
return(false);
}
// at this point we know we will update the relationship
SingleColumnRelationship relationshipTarget = new SingleColumnRelationship();
tabularRelationshipSource.CopyTo(relationshipTarget);
//decouple from original table to the current one
relationshipTarget.FromColumn = this.TomTable.Columns.Find(relationshipTarget.FromColumn.Name);
relationshipTarget.ToColumn = toTableTarget.Columns.Find(relationshipTarget.ToColumn.Name);
CreateRelationship(relationshipTarget);
return(true);
}
public static ReferenceTable Create(TOM.Table table)
{
return(new ReferenceTable
{
Name = table.Name,
Description = table.Description,
Columns = table.Columns.Select(c => ReferenceColumn.Create(c)).ToArray(),
Measures = table.Measures.Select(m => ReferenceMeasure.Create(m)).ToArray(),
Hierarchies = table.Hierarchies.Select(h => ReferenceHierarchy.Create(h)).ToArray()
});
}
static void AddSalesRegionMeasures()
{
// DAX query to be submitted totabuar database engine
String query = "EVALUATE( VALUES(Customers[Sales Region]) )";
AdomdConnection adomdConnection = new AdomdConnection(connectString);
adomdConnection.Open();
AdomdCommand adomdCommand = new AdomdCommand(query, adomdConnection);
AdomdDataReader reader = adomdCommand.ExecuteReader();
// open connection use TOM to create new measures
TOM.Server server = new TOM.Server();
server.Connect(connectString);
TOM.Model model = server.Databases[0].Model;
TOM.Table salesTable = model.Tables["Sales"];
String measureDescription = "Auto Measures";
// delete any previously created "Auto" measures
foreach (TOM.Measure m in salesTable.Measures)
{
if (m.Description == measureDescription)
{
salesTable.Measures.Remove(m);
model.SaveChanges();
}
}
// Create the new measures
while (reader.Read())
{
String SalesRegion = reader.GetValue(0).ToString();
String measureName = $"{SalesRegion} Sales";
TOM.Measure measure = new TOM.Measure()
{
Name = measureName,
Description = measureDescription,
DisplayFolder = "Auto Measures",
FormatString = "$#,##0",
Expression = $@"CALCULATE( SUM(Sales[SalesAmount]), Customers[Sales Region] = ""{SalesRegion}"" )"
};
salesTable.Measures.Add(measure);
}
model.SaveChanges();
reader.Dispose();
adomdConnection.Close();
}
internal new static CalculatedTable CreateFromMetadata(Model parent, TOM.Table metadataObject)
{
if (metadataObject.GetSourceType() != TOM.PartitionSourceType.Calculated)
{
throw new ArgumentException("Provided metadataObject is not a Calculated Table.");
}
var obj = new CalculatedTable(metadataObject);
parent.Tables.Add(obj);
obj.Init();
return(obj);
}
internal new static CalculationGroupTable CreateFromMetadata(Model parent, TOM.Table metadataObject)
{
if (metadataObject.GetSourceType() != TOM.PartitionSourceType.CalculationGroup)
{
throw new ArgumentException("Provided metadataObject is not a Calculation Group Table.");
}
var obj = new CalculationGroupTable(metadataObject);
parent.Tables.Add(obj);
obj.Init();
//obj.NameField = new CalculationGroupAttribute(obj.DataColumns.First());
return(obj);
}
public new static CalculationGroupTable CreateNew(Model parent, string name = null)
{
parent.DiscourageImplicitMeasures = true;
var metadataObject = new TOM.Table()
{
CalculationGroup = new TOM.CalculationGroup()
};
metadataObject.Columns.Add(new TOM.DataColumn {
Name = "Attribute", DataType = TOM.DataType.String
});
metadataObject.Partitions.Add(new TOM.Partition {
Source = new TOM.CalculationGroupSource()
});
metadataObject.Name = parent.Tables.GetNewName(string.IsNullOrWhiteSpace(name) ? "New Calculation Group" : name);
return(CreateFromMetadata(parent, metadataObject));
}
/// <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 obj = new CalculatedTable(metadataObject);
parent.Tables.Add(obj);
obj.Init();
if (!string.IsNullOrWhiteSpace(expression))
{
obj.Expression = expression;
}
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);
}
/* 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);
}
public Table(SSAS14.Table table)
{
this.table14 = table;
}
protected CalculatedTable(TOM.Table tableMetadataObject) : base(tableMetadataObject)
{
}
public static void CreateAnalysisServiceDatabase(string svr, string db, List <Table> tblList)
{
string ConnectionString = "DataSource=.\\MSSQLAS";
//
// The using syntax ensures the correct use of the
// Microsoft.AnalysisServices.Tabular.Server object.
//
using (Server server = new Server())
{
server.Connect(ConnectionString);
//
// Generate a new database name and use GetNewName
// to ensure the database name is unique.
//
string newDatabaseName =
server.Databases.GetNewName("AW Handmade v1.1");
//
// Instantiate a new
// Microsoft.AnalysisServices.Tabular.Database object.
//
var dbWithDataSource = new Database()
{
Name = newDatabaseName,
ID = newDatabaseName,
CompatibilityLevel = 1200,
StorageEngineUsed = StorageEngineUsed.TabularMetadata,
};
//
// Add a Microsoft.AnalysisServices.Tabular.Model object to the
// database, which acts as a root for all other Tabular metadata objects.
//
dbWithDataSource.Model = new Model()
{
Name = "AW Handmade Model",
Description = "A Tabular data model at the 1200 compatibility level."
};
//
// Add a Microsoft.AnalysisServices.Tabular.ProviderDataSource object
// to the data Model object created in the previous step. The connection
// string of the data source object in this example
// points to an instance of the AdventureWorks2014 SQL Server database.
//
dbWithDataSource.Model.DataSources.Add(new ProviderDataSource()
{
Name = "SQL Server Data Source Example",
Description = "A data source definition that uses explicit Windows credentials for authentication against SQL Server.",
ConnectionString = "Provider=SQLNCLI11;Data Source=" + svr + ";Initial Catalog=" + db + ";Integrated Security=SSPI;Persist Security Info=false",
ImpersonationMode = Microsoft.AnalysisServices.Tabular.ImpersonationMode.ImpersonateAccount,
Account = @".\duy",
Password = "******",
});
//
// Add the new database object to the server's
// Databases connection and submit the changes
// with full expansion to the server.
//
foreach (Table tbl in tblList)
{
Microsoft.AnalysisServices.Tabular.Table ttbl = new Microsoft.AnalysisServices.Tabular.Table();
ttbl.Name = tbl.TableName;
var sel = SelectedColumns(svr, db, tbl);
foreach (Microsoft.AnalysisServices.Tabular.DataColumn dc in sel)
{
ttbl.Columns.Add(dc);
}
ttbl.Partitions.Add(new Partition()
{
Name = "All",
Source = new QueryPartitionSource()
{
DataSource = dbWithDataSource.Model.DataSources["SQL Server Data Source Example"],
Query = "Select * from " + ttbl.Name
}
});
dbWithDataSource.Model.Tables.Add(ttbl);
}
server.Databases.Add(dbWithDataSource);
dbWithDataSource.Update(UpdateOptions.ExpandFull);
Console.Write("Database ");
Console.ForegroundColor = ConsoleColor.Yellow;
Console.Write(dbWithDataSource.Name);
Console.ResetColor();
Console.WriteLine(" created successfully.");
Console.WriteLine("The data model includes the following data source definitions:");
Console.ForegroundColor = ConsoleColor.Yellow;
foreach (DataSource ds in dbWithDataSource.Model.DataSources)
{
Console.WriteLine("\tData source name:\t\t{0}", ds.Name);
Console.WriteLine("\tData source description:\t{0}", ds.Description);
}
Console.ResetColor();
Console.WriteLine();
}
Console.WriteLine("Press Enter to close this console window.");
Console.ReadLine();
}
/*
* [Browsable(false)]
* public CalculationGroupAttribute NameField { get; private set; }
* [Browsable(false)]
* public CalculationGroupAttribute OrdinalField { get; private set; }
*/
CalculationGroupTable(TOM.Table table) : base(table)
{
}
public static bool IsCalculatedOrCalculationGroup(this TOM.Table table)
{
var sourceType = GetSourceType(table);
return(sourceType == TOM.PartitionSourceType.Calculated || sourceType == TOM.PartitionSourceType.CalculationGroup);
}
public static TOM.PartitionSourceType GetSourceType(this TOM.Table table)
{
return(table.Partitions.FirstOrDefault()?.SourceType ?? TOM.PartitionSourceType.None);
}
