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 List <Microsoft.AnalysisServices.Tabular.DataColumn> SelectedColumns(string svr, string db, Table table)
{
List <Microsoft.AnalysisServices.Tabular.DataColumn> colSelections = new List <Microsoft.AnalysisServices.Tabular.DataColumn>();
OleDbConnection myAccessConn = null;
string strConn1 = "Provider = SQLOLEDB; Data Source = " + svr + ";Initial Catalog=" + db + "; Integrated Security = SSPI; Persist Security Info = false";
using (myAccessConn = new OleDbConnection(strConn1))
{
myAccessConn.Open();
var col = myAccessConn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new[] { null, table.TableSchema, table.TableName, null });
foreach (DataRow dr in col.Rows)
{
var column = new Microsoft.AnalysisServices.Tabular.DataColumn()
{
Name = dr["COLUMN_NAME"].ToString(),
DataType = (Microsoft.AnalysisServices.Tabular.DataType)(ChangeDataType(Convert.ToInt32(dr["DATA_TYPE"]))),
};
Console.WriteLine(dr["COLUMN_NAME"].ToString() + ": " + dr["DATA_TYPE"]);
Console.WriteLine(column.DataType);
colSelections.Add(column);
}
Console.ReadLine();
}
return(colSelections);
}
//TODO: Add direct query support
/* Complete feature list, to be noted when complete support added
* - Database
* - Direct Query
* - Datasources (Done)
* - Tables (Done)
* - Translation of table (Done)
* - Attributes (Done)
* - Translation of Attribute (Done)
* - Hierarchies (Done)
* - Translation of Hierarchies (Done)
* - Levels (Done)
* - Translation of Levels (Done)
* - Measures (Done)
* - Translation of Measures (Done)
* - KPI's (Done)
* - Perspectives (Done)
* - Roles (Done)
* - Row Level Security (Done)
* - Members (Done)
* - Relationships (Done)
*/
/// <summary>
/// Given a 1200 or 1400 model, produces an equivelant 1103 model. Incompatible features are not added.
/// </summary>
/// <param name="TOMDatabase"></param>
/// <returns></returns>
public static AMO.Database ToAMODatabase(TOM.Database TOMDatabase)
{
/* The Database representing a Tabular model rarely has a 1 to 1 mapping between
* a TOM object and an AMO object.
*
* Some objects which DO have 1:1 mappings (at least, in a logical sense):
* - DataSources
* - Hierarchies
* - Levels
* - Perspectives
* - Partitions
* - Translations (however, they are structured differently)
* - Annotations (These can also accept XML nodes, not just strings)
*
* The following do NOT:
* - Tables
* - Columns
* - Measures
* - Relationships
* - Roles
*
* The following is, surprisingly, supported:
* - Tabular Actions
* - HideMemberIf
* - Display Folders
* - Custom Format strings
* - Translations
* - Similar to 1200, more than one column with the same source column
*
* Please note this does NOT produce a file able to be opened by Visual Studio.
* Unlike 1200 models, Visual Studio cannot open all valid 1103 models.
* It relies heavily on custom annotations, understood mainly through trial and error.
*
* In addition to these custom annotations, some features have no alternative (custom format strings), and
* are completely unsupported by both Visual Studio and BIDS.
*
* Display Folders, Tabular Actions, and Translations are all supported using BIDS.
*
* These drastically increase the database/file size, and as such are optionally added in a seperate function.
*/
TOM.Model TOMModel = TOMDatabase.Model;
//Create Database
AMO.Database AMODatabase = new AMO.Database(TOMDatabase.Name, TOMDatabase.Name);
//Initialise with default values.
AMODatabase.StorageEngineUsed = AMO.StorageEngineUsed.InMemory;
AMODatabase.CompatibilityLevel = SQL2012SP1;
//DataSource has 1:1 mapping with AMO object
#region DataSources
foreach (TOM.ProviderDataSource TOMDataSource in TOMModel.DataSources)
{
AMO.DataSource AMODataSource = new AMO.RelationalDataSource(TOMDataSource.Name, TOMDataSource.Name);
AMODataSource.Description = TOMDataSource.Description;
AMODataSource.ConnectionString = TOMDataSource.ConnectionString;
AMODataSource.ImpersonationInfo = new AMO.ImpersonationInfo();
switch (TOMDataSource.ImpersonationMode)
{
case TOM.ImpersonationMode.Default:
AMODataSource.ImpersonationInfo.ImpersonationMode = AMO.ImpersonationMode.Default;
break;
case TOM.ImpersonationMode.ImpersonateAccount:
AMODataSource.ImpersonationInfo.ImpersonationMode = AMO.ImpersonationMode.ImpersonateAccount;
break;
case TOM.ImpersonationMode.ImpersonateAnonymous:
AMODataSource.ImpersonationInfo.ImpersonationMode = AMO.ImpersonationMode.ImpersonateAnonymous;
break;
case TOM.ImpersonationMode.ImpersonateCurrentUser:
AMODataSource.ImpersonationInfo.ImpersonationMode = AMO.ImpersonationMode.ImpersonateCurrentUser;
break;
case TOM.ImpersonationMode.ImpersonateServiceAccount:
AMODataSource.ImpersonationInfo.ImpersonationMode = AMO.ImpersonationMode.ImpersonateServiceAccount;
break;
case TOM.ImpersonationMode.ImpersonateUnattendedAccount:
AMODataSource.ImpersonationInfo.ImpersonationMode = AMO.ImpersonationMode.ImpersonateUnattendedAccount;
break;
}
AMODataSource.ImpersonationInfo.Account = TOMDataSource.Account;
AMODataSource.ImpersonationInfo.Password = TOMDataSource.Password;
switch (TOMDataSource.Isolation)
{
case TOM.DatasourceIsolation.ReadCommitted:
AMODataSource.Isolation = AMO.DataSourceIsolation.ReadCommitted;
break;
case TOM.DatasourceIsolation.Snapshot:
AMODataSource.Isolation = AMO.DataSourceIsolation.Snapshot;
break;
}
//1 "tick" = 100 nanoseconds = 1*10^-7 seconds.
AMODataSource.Timeout = new TimeSpan(TOMDataSource.Timeout * 10000000);
AMODatabase.DataSources.Add(AMODataSource);
}
#endregion
/* The DSV is surprisingly simple.
* For each physical table (but NOT partition), a DataTable needs to be added to the DSV DataSet,
* with the same name as the table.
*
* Similarly, for each distinct source column, a DataColumn needs to be added to the corresponding DataTable
*/
#region DataSourceView
using (AMO.DataSourceView dsv = new AMO.DataSourceView(AMODatabase.DataSources[0].Name))
{
System.Data.DataSet Schema = new System.Data.DataSet(AMODatabase.DataSources[0].Name);
dsv.Schema = Schema;
dsv.DataSourceID = AMODatabase.DataSources[0].Name;
AMODatabase.DataSourceViews.Add(dsv);
}
#endregion
#region Create Cube
AMO.Cube Cube = new AMO.Cube(TOMModel.Name, TOMModel.Name);
Cube.Source = new AMO.DataSourceViewBinding(AMODatabase.DataSourceViews[0].ID);
Cube.StorageMode = AMO.StorageMode.InMemory;
//Create the MdxScript for holding DAX commands.
using (AMO.MdxScript Script = Cube.MdxScripts.Add(MDXScriptName, MDXScriptName))
{
//You MUST have a "default" MDX measure for some reason.
//We make sure to hide it - it serves no real purpose, besides its own existence
System.Text.StringBuilder InitialisationCommand = new System.Text.StringBuilder();
InitialisationCommand.AppendLine("CALCULATE;");
InitialisationCommand.AppendLine("CREATE MEMBER CURRENTCUBE.Measures.[_No measures defined] AS 1, VISIBLE = 0;");
InitialisationCommand.AppendLine("ALTER CUBE CURRENTCUBE UPDATE DIMENSION Measures, Default_Member = [_No measures defined];");
Script.Commands.Add(new AMO.Command(InitialisationCommand.ToString()));
}
AMODatabase.Cubes.Add(Cube);
#endregion
#region Add Tables
foreach (TOM.Table TOMTable in TOMModel.Tables)
{
//Three "parts" to a Table:
//1. A System.Data.DataTable, with the same Name as the TOMTable
//2. A dimension with the same name as the TOMTable
//3. A measure group with the same name as the TOMTable,
System.Data.DataTable SchemaTable = new System.Data.DataTable(TOMTable.Name);
AMODatabase.DataSourceViews[0].Schema.Tables.Add(SchemaTable);
string RowNumberColumnName = string.Format(System.Globalization.CultureInfo.InvariantCulture, "RowNumber_{0}", System.Guid.NewGuid());
#region Add Table Dimension
try
{
using (AMO.Dimension Dimension = AMODatabase.Dimensions.Add(TOMTable.Name, TOMTable.Name))
{
Dimension.Source = new AMO.DataSourceViewBinding(AMODatabase.DataSourceViews[0].ID);
Dimension.StorageMode = AMO.DimensionStorageMode.InMemory;
Dimension.UnknownMember = AMO.UnknownMemberBehavior.AutomaticNull;
Dimension.UnknownMemberName = "Unknown";
using (Dimension.ErrorConfiguration = new AMO.ErrorConfiguration())
{
Dimension.ErrorConfiguration.KeyNotFound = AMO.ErrorOption.IgnoreError;
Dimension.ErrorConfiguration.KeyDuplicate = AMO.ErrorOption.ReportAndStop;
Dimension.ErrorConfiguration.NullKeyNotAllowed = AMO.ErrorOption.ReportAndStop;
}
Dimension.ProactiveCaching = new AMO.ProactiveCaching();
System.TimeSpan DefaultProactiveCachingTimeSpan = new System.TimeSpan(0, 0, -1);
Dimension.ProactiveCaching.SilenceInterval = DefaultProactiveCachingTimeSpan;
Dimension.ProactiveCaching.Latency = DefaultProactiveCachingTimeSpan;
Dimension.ProactiveCaching.SilenceOverrideInterval = DefaultProactiveCachingTimeSpan;
Dimension.ProactiveCaching.ForceRebuildInterval = DefaultProactiveCachingTimeSpan;
Dimension.ProactiveCaching.Source = new AMO.ProactiveCachingInheritedBinding();
Dimension.Description = TOMTable.Description;
// Define RowNumber
using (AMO.DimensionAttribute RowNumberDimAttribute = Dimension.Attributes.Add(RowNumberColumnName, RowNumberColumnName))
{
RowNumberDimAttribute.Type = AMO.AttributeType.RowNumber;
RowNumberDimAttribute.KeyUniquenessGuarantee = true;
RowNumberDimAttribute.Usage = AMO.AttributeUsage.Key;
RowNumberDimAttribute.KeyColumns.Add(new AMO.DataItem());
RowNumberDimAttribute.KeyColumns[0].DataType = System.Data.OleDb.OleDbType.Integer;
RowNumberDimAttribute.KeyColumns[0].DataSize = 4;
RowNumberDimAttribute.KeyColumns[0].NullProcessing = AMO.NullProcessing.Error;
RowNumberDimAttribute.KeyColumns[0].Source = new AMO.RowNumberBinding();
RowNumberDimAttribute.NameColumn = new AMO.DataItem();
RowNumberDimAttribute.NameColumn.DataType = System.Data.OleDb.OleDbType.WChar;
RowNumberDimAttribute.NameColumn.DataSize = 4;
RowNumberDimAttribute.NameColumn.NullProcessing = AMO.NullProcessing.ZeroOrBlank;
RowNumberDimAttribute.NameColumn.Source = new AMO.RowNumberBinding();
RowNumberDimAttribute.OrderBy = AMO.OrderBy.Key;
RowNumberDimAttribute.AttributeHierarchyVisible = false;
}
// Add Translations
foreach (TOM.Culture TOMCulture in TOMModel.Cultures)
{
AMO.Translation AMOTranslation = TranslationHelper.GetTranslation(TOMCulture, TOMTable);
if (AMOTranslation != null)
{
Dimension.Translations.Add(AMOTranslation);
}
}
}
}
catch (Exception e)
{
throw new Exception(string.Format("The following error occurred creating the DImension for table {0}: {1}", TOMTable.Name, e.Message), e);
}
#endregion
#region Add Table MeasureGroup
try
{
using (AMO.MeasureGroup TableMeasureGroup = Cube.MeasureGroups.Add(TOMTable.Name, TOMTable.Name))
{
TableMeasureGroup.StorageMode = AMO.StorageMode.InMemory;
TableMeasureGroup.ProcessingMode = AMO.ProcessingMode.Regular;
// Add Default Measure
string DefaultMeasureID = string.Concat("_Count", TOMTable.Name);
using (AMO.Measure DefaultMeasure = TableMeasureGroup.Measures.Add(DefaultMeasureID, DefaultMeasureID))
using (AMO.RowBinding DefaultMeasureRowBinding = new AMO.RowBinding(TOMTable.Name))
using (AMO.DataItem DefaultMeasureSource = new AMO.DataItem(DefaultMeasureRowBinding))
{
DefaultMeasure.AggregateFunction = AMO.AggregationFunction.Count;
DefaultMeasure.DataType = AMO.MeasureDataType.BigInt;
DefaultMeasure.Visible = false;
DefaultMeasureSource.DataType = System.Data.OleDb.OleDbType.BigInt;
DefaultMeasure.Source = DefaultMeasureSource;
}
// Add Dimension to Measure Group
using (AMO.DegenerateMeasureGroupDimension DefaultMeasureGroupDimension = new AMO.DegenerateMeasureGroupDimension(TOMTable.Name))
using (AMO.MeasureGroupAttribute MeasureGroupAttribute = new AMO.MeasureGroupAttribute(RowNumberColumnName))
using (AMO.ColumnBinding RowNumberColumnBinding = new AMO.ColumnBinding(TOMTable.Name, RowNumberColumnName))
using (AMO.DataItem RowNumberKeyColumn = new AMO.DataItem(RowNumberColumnBinding))
{
DefaultMeasureGroupDimension.ShareDimensionStorage = AMO.StorageSharingMode.Shared;
DefaultMeasureGroupDimension.CubeDimensionID = TOMTable.Name;
MeasureGroupAttribute.Type = AMO.MeasureGroupAttributeType.Granularity;
RowNumberKeyColumn.DataType = System.Data.OleDb.OleDbType.Integer;
MeasureGroupAttribute.KeyColumns.Add(RowNumberKeyColumn);
DefaultMeasureGroupDimension.Attributes.Add(MeasureGroupAttribute);
TableMeasureGroup.Dimensions.Add(DefaultMeasureGroupDimension);
}
//Partitions have a 1:1 mapping
#region Partitions
foreach (TOM.Partition TOMPartition in TOMTable.Partitions)
{
using (AMO.Partition AMOPartition = new AMO.Partition(TOMPartition.Name, TOMPartition.Name))
{
AMOPartition.StorageMode = AMO.StorageMode.InMemory;
AMOPartition.ProcessingMode = AMO.ProcessingMode.Regular;
AMOPartition.Source = new AMO.QueryBinding(
((TOM.QueryPartitionSource)TOMPartition.Source).DataSource.Name,
((TOM.QueryPartitionSource)TOMPartition.Source).Query
);
AMOPartition.Type = AMO.PartitionType.Data;
TableMeasureGroup.Partitions.Add(AMOPartition);
}
}
#endregion
}
}
catch (Exception e)
{
throw new Exception(string.Format("The following error occurred creating the MeasureGroup for table {0}: {1}", TOMTable.Name, e.Message), e);
}
#endregion
//Add the dimension to the cube
Cube.Dimensions.Add(TOMTable.Name, TOMTable.Name, TOMTable.Name);
Cube.Dimensions[TOMTable.Name].Visible = !TOMTable.IsHidden;
/* No 1:1 mapping exists for columns. Each column consists of the following:
* - A DataColumn with the same name as the column,
* attached to the DataTable correlating to the Table, located in the DSV
* - An attribute with the same name as the column, added to the Dimension
* - An AttributeRelationship with the RowNumber column
*/
#region Add Columns
foreach (TOM.Column TOMColumn in TOMTable.Columns)
{
switch (TOMColumn.Type)
{
case TOM.ColumnType.Data:
//Add the DataColumn corresponding to the SourceColumn, if it does not already exist
TOM.DataColumn TOMDataColumn = (TOM.DataColumn)TOMColumn;
if (!SchemaTable.Columns.Contains(TOMDataColumn.SourceColumn))
{
SchemaTable.Columns.Add(new System.Data.DataColumn(((TOM.DataColumn)TOMColumn).SourceColumn));
}
System.Data.OleDb.OleDbType ColumnDataType = DataTypeHelper.ToOleDbType(TOMDataColumn.DataType);
AMO.DimensionAttribute NormalAttribute = AMODatabase.Dimensions[TOMTable.Name].Attributes.Add(TOMDataColumn.Name, TOMDataColumn.Name);
NormalAttribute.Usage = AMO.AttributeUsage.Regular;
NormalAttribute.KeyUniquenessGuarantee = false;
NormalAttribute.KeyColumns.Add(new AMO.DataItem(SchemaTable.TableName, SchemaTable.Columns[TOMDataColumn.SourceColumn].ColumnName, ColumnDataType));
NormalAttribute.KeyColumns[0].Source = new AMO.ColumnBinding(SchemaTable.TableName, SchemaTable.Columns[TOMDataColumn.SourceColumn].ColumnName);
NormalAttribute.KeyColumns[0].NullProcessing = AMO.NullProcessing.Preserve;
NormalAttribute.NameColumn = new AMO.DataItem(SchemaTable.TableName, SchemaTable.Columns[TOMDataColumn.SourceColumn].ColumnName, System.Data.OleDb.OleDbType.WChar);
NormalAttribute.NameColumn.Source = new AMO.ColumnBinding(SchemaTable.TableName, SchemaTable.Columns[TOMDataColumn.SourceColumn].ColumnName);
NormalAttribute.NameColumn.NullProcessing = AMO.NullProcessing.ZeroOrBlank;
NormalAttribute.OrderBy = AMO.OrderBy.Key;
AMO.AttributeRelationship NormalAttributeRelationship = AMODatabase.Dimensions[TOMTable.Name].Attributes[RowNumberColumnName].AttributeRelationships.Add(NormalAttribute.ID);
NormalAttribute.AttributeHierarchyVisible = !TOMDataColumn.IsHidden;
NormalAttribute.Description = TOMDataColumn.Description;
NormalAttribute.AttributeHierarchyDisplayFolder = TOMDataColumn.DisplayFolder;
//Add Translations to the CalculatedAttribute
foreach (TOM.Culture TOMCulture in TOMModel.Cultures)
{
using (AMO.Translation AMOTranslation = TranslationHelper.GetTranslation(TOMCulture, TOMColumn))
if (AMOTranslation != null)
{
NormalAttribute.Translations.Add(new AMO.AttributeTranslation {
Caption = AMOTranslation.Caption, Description = AMOTranslation.Description, DisplayFolder = AMOTranslation.DisplayFolder
});
}
}
NormalAttributeRelationship.Cardinality = AMO.Cardinality.Many;
NormalAttributeRelationship.OverrideBehavior = AMO.OverrideBehavior.None;
break;
case TOM.ColumnType.Calculated:
TOM.CalculatedColumn TOMCalculatedColumn = (TOM.CalculatedColumn)TOMColumn;
System.Data.OleDb.OleDbType CalculatedColumnDataType = DataTypeHelper.ToOleDbType(TOMColumn.DataType);
//Add Attribute to the Dimension
AMO.Dimension dim = AMODatabase.Dimensions[TOMTable.Name];
AMO.DimensionAttribute CalculatedAttribute = dim.Attributes.Add(TOMCalculatedColumn.Name, TOMCalculatedColumn.Name);
CalculatedAttribute.Usage = AMO.AttributeUsage.Regular;
CalculatedAttribute.KeyUniquenessGuarantee = false;
CalculatedAttribute.KeyColumns.Add(new AMO.DataItem(TOMTable.Name, TOMCalculatedColumn.Name, CalculatedColumnDataType));
CalculatedAttribute.KeyColumns[0].Source = new AMO.ExpressionBinding(TOMCalculatedColumn.Expression);
CalculatedAttribute.KeyColumns[0].NullProcessing = AMO.NullProcessing.Preserve;
CalculatedAttribute.NameColumn = new AMO.DataItem(TOMTable.Name, TOMCalculatedColumn.Name, System.Data.OleDb.OleDbType.WChar);
CalculatedAttribute.NameColumn.Source = new AMO.ExpressionBinding(TOMCalculatedColumn.Expression);
CalculatedAttribute.NameColumn.NullProcessing = AMO.NullProcessing.ZeroOrBlank;
CalculatedAttribute.OrderBy = AMO.OrderBy.Key;
AMO.AttributeRelationship currentAttributeRelationship = dim.Attributes[RowNumberColumnName].AttributeRelationships.Add(CalculatedAttribute.ID);
CalculatedAttribute.AttributeHierarchyVisible = !TOMCalculatedColumn.IsHidden;
CalculatedAttribute.Description = TOMCalculatedColumn.Description;
CalculatedAttribute.AttributeHierarchyDisplayFolder = TOMCalculatedColumn.DisplayFolder;
//Add Translations to the CalculatedAttribute
//Loop through each culture, and add the translation associated with that culture.
foreach (TOM.Culture TOMCulture in TOMModel.Cultures)
{
using (AMO.Translation AMOTranslation = TranslationHelper.GetTranslation(TOMCulture, TOMColumn))
if (AMOTranslation != null)
{
CalculatedAttribute.Translations.Add(new AMO.AttributeTranslation {
Caption = AMOTranslation.Caption, Description = AMOTranslation.Description, DisplayFolder = AMOTranslation.DisplayFolder
});
}
}
currentAttributeRelationship.Cardinality = AMO.Cardinality.Many;
currentAttributeRelationship.OverrideBehavior = AMO.OverrideBehavior.None;
//Add CalculatedColumn as attribute to the MeasureGroup
AMO.MeasureGroup mg = Cube.MeasureGroups[TOMTable.Name];
AMO.DegenerateMeasureGroupDimension currentMGDim = (AMO.DegenerateMeasureGroupDimension)mg.Dimensions[TOMTable.Name];
AMO.MeasureGroupAttribute mga = new AMO.MeasureGroupAttribute(TOMCalculatedColumn.Name);
mga.KeyColumns.Add(new AMO.DataItem(TOMTable.Name, TOMCalculatedColumn.Name, System.Data.OleDb.OleDbType.Empty));
mga.KeyColumns[0].Source = new AMO.ExpressionBinding(TOMCalculatedColumn.Expression);
currentMGDim.Attributes.Add(mga);
break;
default:
throw new System.NotImplementedException(string.Format("Cannot deploy Column of type {0}", TOMColumn.Type.ToString()));
}
}
#endregion
//Add sort by columns
foreach (TOM.Column TOMColumn in TOMTable.Columns)
{
if (TOMColumn.SortByColumn != null)
{
AMODatabase.Dimensions[TOMTable.Name].Attributes[TOMColumn.Name].OrderByAttributeID = TOMColumn.SortByColumn.Name;
}
}
#region Add Hierarchies
foreach (TOM.Hierarchy TOMHierarchy in TOMTable.Hierarchies)
{
//Create the Hierarchy, and add it
AMO.Hierarchy AMOHierarchy = AMODatabase.Dimensions[TOMTable.Name].Hierarchies.Add(TOMHierarchy.Name, TOMHierarchy.Name);
AMOHierarchy.Description = TOMHierarchy.Description;
AMOHierarchy.DisplayFolder = TOMHierarchy.DisplayFolder;
AMOHierarchy.AllMemberName = "All";
foreach (TOM.Level TOMLevel in TOMHierarchy.Levels)
{
AMO.Level AMOLevel = AMOHierarchy.Levels.Add(TOMLevel.Name);
AMOLevel.SourceAttribute = AMODatabase.Dimensions[TOMTable.Name].Attributes[TOMLevel.Column.Name];
AMOLevel.Description = TOMLevel.Description;
//Add Translations to the CalculatedAttribute
//Loop through each culture, and add the translation associated with that culture.
foreach (TOM.Culture TOMCulture in TOMModel.Cultures)
{
using (AMO.Translation AMOTranslation = TranslationHelper.GetTranslation(TOMCulture, TOMLevel))
if (AMOTranslation != null)
{
AMOLevel.Translations.Add(AMOTranslation);
}
}
}
}
#endregion
}
#endregion
#region Add Measures
using (AMO.MdxScript MdxScript = Cube.DefaultMdxScript)
{
//Create a "default" measure, required for the cube to function.
MdxScript.Commands.Add(new AMO.Command("CALCULATE;"
+ "CREATE MEMBER CURRENTCUBE.Measures.[_No measures defined] AS 1, VISIBLE = 0;"
+ "ALTER CUBE CURRENTCUBE UPDATE DIMENSION Measures, Default_Member = [_No measures defined];"));
foreach (TOM.Table TOMTable in TOMModel.Tables)
{
foreach (TOM.Measure TOMMeasure in TOMTable.Measures)
{
//Create the Command, which contains the definition of the Measure
AMO.Command AMOCommand = new AMO.Command(string.Format("CREATE MEASURE '{0}'[{1}]={2};", TOMTable.Name, TOMMeasure.Name.Replace("]", "]]"), TOMMeasure.Expression));
if (TOMMeasure.KPI != null)
{
#region Add KPI
//Start building the final command - we add to it as we go along
string FinalKPICreate = string.Format(
"CREATE KPI CURRENTCUBE.[{1}] AS Measures.[{1}], ASSOCIATED_MEASURE_GROUP = '{0}'",
TOMTable.Name,
TOMMeasure.Name
);
//Goal/Target
if (!string.IsNullOrWhiteSpace(TOMMeasure.KPI.TargetExpression))
{
AMOCommand.Text += Environment.NewLine + string.Format(string.Format("CREATE MEASURE '{0}'[_{1} Goal]={2};", TOMTable.Name, TOMMeasure.Name.Replace("]", "]]"), TOMMeasure.KPI.TargetExpression));
FinalKPICreate += string.Format(", GOAL = Measures.[_{0} Goal]", TOMMeasure.Name.Replace("]", "]]"));
}
//Status
if (!string.IsNullOrWhiteSpace(TOMMeasure.KPI.StatusExpression))
{
AMOCommand.Text += Environment.NewLine + string.Format(string.Format("CREATE MEASURE '{0}'[_{1} Status]={2};", TOMTable.Name, TOMMeasure.Name.Replace("]", "]]"), TOMMeasure.KPI.StatusExpression));
FinalKPICreate += string.Format(", STATUS = Measures.[_{0} Status]", TOMMeasure.Name.Replace("]", "]]"));
if (!string.IsNullOrWhiteSpace(TOMMeasure.KPI.StatusGraphic))
{
FinalKPICreate += string.Format(", STATUS_GRAPHIC = '{0}'", TOMMeasure.KPI.StatusGraphic);
}
}
//Trend
if (!string.IsNullOrWhiteSpace(TOMMeasure.KPI.TrendExpression))
{
AMOCommand.Text += Environment.NewLine + string.Format(string.Format("CREATE MEASURE '{0}'[_{1} Trend]={2};", TOMTable.Name, TOMMeasure.Name.Replace("]", "]]"), TOMMeasure.KPI.TrendExpression));
FinalKPICreate += string.Format(", TREND = Measures.[_{0} Trend]", TOMMeasure.Name.Replace("]", "]]"));
if (!string.IsNullOrWhiteSpace(TOMMeasure.KPI.TrendGraphic))
{
FinalKPICreate += string.Format(", TREND_GRAPHIC = '{0}'", TOMMeasure.KPI.TrendGraphic);
}
}
AMOCommand.Text += Environment.NewLine + FinalKPICreate + ";";
//Create calculation properties, hiding the "fake" measures (if they exist)
//Target
if (!string.IsNullOrWhiteSpace(TOMMeasure.KPI.TargetExpression))
{
AMO.CalculationProperty TargetCalculationProperty = new AMO.CalculationProperty(string.Format("[_{0} Goal]", TOMMeasure.Name.Replace("]", "]]")), AMO.CalculationType.Member);
TargetCalculationProperty.Description = TOMMeasure.KPI.TargetDescription;
TargetCalculationProperty.CalculationType = AMO.CalculationType.Member;
TargetCalculationProperty.Visible = false;
if (!string.IsNullOrWhiteSpace(TOMMeasure.KPI.TargetFormatString))
{
TargetCalculationProperty.FormatString = "'" + TOMMeasure.KPI.TargetFormatString + "'";
}
MdxScript.CalculationProperties.Add(TargetCalculationProperty);
}
//Status
if (!string.IsNullOrWhiteSpace(TOMMeasure.KPI.StatusExpression))
{
AMO.CalculationProperty StatusCalculationProperty = new AMO.CalculationProperty(string.Format("[_{0} Status]", TOMMeasure.Name.Replace("]", "]]")), AMO.CalculationType.Member);
StatusCalculationProperty.Description = TOMMeasure.KPI.StatusDescription;
StatusCalculationProperty.CalculationType = AMO.CalculationType.Member;
StatusCalculationProperty.Visible = false;
MdxScript.CalculationProperties.Add(StatusCalculationProperty);
}
//Trend
if (!string.IsNullOrWhiteSpace(TOMMeasure.KPI.StatusExpression))
{
AMO.CalculationProperty TrendCalculationProperty = new AMO.CalculationProperty(string.Format("[_{0} Trend]", TOMMeasure.Name.Replace("]", "]]")), AMO.CalculationType.Member);
TrendCalculationProperty.Description = TOMMeasure.KPI.TrendDescription;
TrendCalculationProperty.CalculationType = AMO.CalculationType.Member;
TrendCalculationProperty.Visible = false;
MdxScript.CalculationProperties.Add(TrendCalculationProperty);
}
//Create the KPI calculation property
AMO.CalculationProperty KPICalculationProperty = new AMO.CalculationProperty(string.Format("KPIs.[{0}]", TOMMeasure.Name.Replace("]", "]]")), AMO.CalculationType.Member);
KPICalculationProperty.Description = TOMMeasure.KPI.Description;
KPICalculationProperty.CalculationType = AMO.CalculationType.Member;
MdxScript.CalculationProperties.Add(KPICalculationProperty);
#endregion
}
//Add the Command to the MdxScript
MdxScript.Commands.Add(AMOCommand);
//Create the Calculation Property, which contains the various properties of the Measure
AMO.CalculationProperty CalculationProperty = new AMO.CalculationProperty(string.Format("[{0}]", TOMMeasure.Name.Replace("]", "]]")), AMO.CalculationType.Member);
CalculationProperty.Description = TOMMeasure.Description;
CalculationProperty.DisplayFolder = TOMMeasure.DisplayFolder;
CalculationProperty.CalculationType = AMO.CalculationType.Member;
CalculationProperty.Visible = !TOMMeasure.IsHidden;
if (!string.IsNullOrWhiteSpace(TOMMeasure.FormatString))
{
CalculationProperty.FormatString = "'" + TOMMeasure.FormatString + "'";
}
//Add Translations to the Calculation property
foreach (TOM.Culture TOMCulture in TOMModel.Cultures)
{
using (AMO.Translation AMOTranslation = TranslationHelper.GetTranslation(TOMCulture, TOMMeasure))
if (AMOTranslation != null)
{
CalculationProperty.Translations.Add(AMOTranslation);
}
}
//Finally, add the CalculationProperty to the MDX script
MdxScript.CalculationProperties.Add(CalculationProperty);
}
}
}
#endregion
#region Add Relationships
//Relationships are just TOO awful. They are placed in their own helper function as a result.
foreach (TOM.SingleColumnRelationship TOMRelationship in TOMModel.Relationships)
{
RelationshipHelper.CreateRelationship(TOMRelationship, AMODatabase);
}
#endregion
#region Add Perspectives
foreach (TOM.Perspective TOMPerspective in TOMModel.Perspectives)
{
AMO.Perspective AMOPerspective = new AMO.Perspective(TOMPerspective.Name);
foreach (TOM.PerspectiveTable TOMTable in TOMPerspective.PerspectiveTables)
{
//Add Perspective Dimension
AMO.PerspectiveDimension PerspectiveDimension = new AMO.PerspectiveDimension(TOMTable.Name);
//Perspective Columns
foreach (TOM.PerspectiveColumn TOMPerspectiveColumn in TOMTable.PerspectiveColumns)
{
PerspectiveDimension.Attributes.Add(TOMPerspectiveColumn.Name);
}
//Perspective Hierarchies
foreach (TOM.PerspectiveHierarchy TOMPerspectiveHierarchy in TOMTable.PerspectiveHierarchies)
{
PerspectiveDimension.Hierarchies.Add(TOMPerspectiveHierarchy.Name);
}
//Add Perspective MeasureGroup
AMO.PerspectiveMeasureGroup PerspectiveMeasureGroup = new AMO.PerspectiveMeasureGroup(TOMTable.Name);
//Perspective Measures
//In this case, ']' is NOT "double quoted", unlike the calculation references, s
foreach (TOM.PerspectiveMeasure TOMPerspectiveMeasure in TOMTable.PerspectiveMeasures)
{
AMOPerspective.Calculations.Add('[' + TOMPerspectiveMeasure.Name + ']');
}
//VS does not add KPI's to Perspectives, so I have no idea how to do this...
//TODO: Add KPIs to perspectives
}
AMODatabase.Cubes[0].Perspectives.Add(AMOPerspective);
}
#endregion
#region Roles
foreach (TOM.ModelRole TOMRole in TOMDatabase.Model.Roles)
{
//Ceate database role
AMO.Role DatabaseRole = AMODatabase.Roles.Add(TOMRole.Name);
DatabaseRole.Description = TOMRole.Description;
//Add Members to Database role
foreach (TOM.ModelRoleMember TOMMember in TOMRole.Members)
{
DatabaseRole.Members.Add(new AMO.RoleMember
{
Name = TOMMember.MemberName,
Sid = TOMMember.MemberID
});
}
//Add DatabasePermission
AMO.DatabasePermission DatabasePermission = AMODatabase.DatabasePermissions.Add(TOMRole.Name, TOMRole.Name, TOMRole.Name);
//Add CubePermission
AMO.CubePermission CubePermission = AMODatabase.Cubes[0].CubePermissions.Add(TOMRole.Name, TOMRole.Name, TOMRole.Name);
switch (TOMRole.ModelPermission)
{
case TOM.ModelPermission.Administrator:
//Add ReadDefinition, Read, and Administer to DatabasePermission
DatabasePermission.ReadDefinition = AMO.ReadDefinitionAccess.Allowed;
DatabasePermission.Read = AMO.ReadAccess.Allowed;
DatabasePermission.Administer = true;
//Nothing extra to add to CubePermission, that is not added after this
// switch statement
break;
case TOM.ModelPermission.None:
//Add no permissions to DatabasePermission.
break;
case TOM.ModelPermission.Read:
//Add only Read access to Database Permission
DatabasePermission.Read = AMO.ReadAccess.Allowed;
//CubePermission
CubePermission.Read = AMO.ReadAccess.Allowed;
break;
case TOM.ModelPermission.ReadRefresh:
DatabasePermission.Read = AMO.ReadAccess.Allowed;
DatabasePermission.Process = true;
//CubePermission
CubePermission.Read = AMO.ReadAccess.Allowed;
CubePermission.Process = true;
break;
case TOM.ModelPermission.Refresh:
DatabasePermission.Process = true;
//CubePermission
CubePermission.Process = true;
break;
}
CubePermission.ReadSourceData = AMO.ReadSourceDataAccess.None;
//Finally, add the row filters
foreach (TOM.TablePermission TablePermission in TOMRole.TablePermissions)
{
AMO.DimensionPermission DimensionPermission = new AMO.DimensionPermission(TOMRole.Name, TOMRole.Name, TOMRole.Name);
DimensionPermission.AllowedRowsExpression = TablePermission.FilterExpression;
AMODatabase.Dimensions.GetByName(TablePermission.Table.Name).DimensionPermissions.Add(DimensionPermission);
}
}
#endregion
return(AMODatabase);
}
/* 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);
}