/// <summary>
/// Create Create Cube DSV
/// </summary>
/// <param name="sqlHelper"></param>
/// <param name="asMeta"></param>
/// <param name="oleDB"></param>
/// <param name="DSV"></param>
/// <param name="dsvSchema"></param>
public void CREATE_CUBE_DSV(DB_SQLHELPER_BASE sqlHelper
, AS_METADATA asMeta
, IDbConnection oleDB
, Microsoft.AnalysisServices.DataSourceView DSV
, String dsvSchema)
{
try
{
sqlHelper.ADD_MESSAGE_LOG("[Create cube dsv] Starting add cube dsv", MESSAGE_TYPE.DSV, MESSAGE_RESULT_TYPE.Normal);
DataTable dsvSet = asMeta.GET_SSAS_DSV_SET(sqlHelper);
foreach (DataRow row in dsvSet.Rows)
{
String dsv_schema_name = row["dsv_schema_name"].ToString();
String db_table_name = row["db_table_name"].ToString();
String table_type = row["table_type"].ToString();
String is_name_query = row["is_named_query"].ToString();
String dsv_query_text = String.Format("SELECT * FROM {0} WHERE 1=0", db_table_name);
sqlHelper.ADD_MESSAGE_LOG(String.Format("[Create cube dsv->{0}] Adding to add cube dsv", db_table_name), MESSAGE_TYPE.DSV, MESSAGE_RESULT_TYPE.Succeed);
AS_API.ADD_TABLE_TO_CUBE_DSV(oleDB
, DSV
, db_table_name
, dsv_query_text
, db_table_name
, "View"
, CONFIGURATION_HELPER.GET_METADATA_PROPERTY("db_table_schema_name"));
}
DSV.Update();
sqlHelper.ADD_MESSAGE_LOG("[Create cube dsv] Succeed to add cube dsv", MESSAGE_TYPE.DSV, MESSAGE_RESULT_TYPE.Succeed);
}
catch (Exception ex)
{
sqlHelper.ADD_MESSAGE_LOG("[Create cube dsv] " + ex.Message.ToString(), MESSAGE_TYPE.DSV, MESSAGE_RESULT_TYPE.Error);
throw(ex);
}
}
/// <summary>
/// Create cube dimension
/// </summary>
/// <param name="sqlHelper"></param>
/// <param name="asMeta"></param>
/// <param name="cubeDB"></param>
/// <param name="DSV"></param>
public void CREATE_CUBE_DIMENSION(DB_SQLHELPER_BASE sqlHelper
, AS_METADATA asMeta
, Microsoft.AnalysisServices.Database cubeDB
, Microsoft.AnalysisServices.DataSourceView DSV
)
{
try
{
sqlHelper.ADD_MESSAGE_LOG("[Create dimension] Starting create dimension"
, MESSAGE_TYPE.DIMENSION
, MESSAGE_RESULT_TYPE.Normal);
DataTable DimensionSet = asMeta.GET_SSAS_DIMENSION_SET(sqlHelper);
foreach (DataRow dimension_row in DimensionSet.Rows)
{
String DimensionID = dimension_row["dimension_id"].ToString();
String DimensionName = dimension_row["dimension_name"].ToString();
String DimensionType = dimension_row["dimension_type"].ToString();
String DataSourceName = dimension_row["dsv_schema_name"].ToString();
String dsvName = DSV.Name;
Microsoft.AnalysisServices.Dimension dim = AS_API.ADD_DIMENSION(sqlHelper, cubeDB, dsvName, DimensionID, DimensionName, DimensionType);
DataTable AttributeSet = asMeta.GET_SSAS_ATTRIBUTES_SET(sqlHelper, DimensionID);
if (AttributeSet == null || AttributeSet.Rows == null || AttributeSet.Rows.Count == 0)
{
sqlHelper.ADD_MESSAGE_LOG(
String.Format("[Create dimension] Dimension {0} has not any attributes, is it expected?", DimensionID)
, MESSAGE_TYPE.DIMENSION
, MESSAGE_RESULT_TYPE.Warning);
}
else
{
sqlHelper.ADD_MESSAGE_LOG(
String.Format("[Create dimension] Adding {0} attributeds for dimension {1}", AttributeSet.Rows.Count.ToString(), DimensionID)
, MESSAGE_TYPE.DIMENSION
, MESSAGE_RESULT_TYPE.Normal);
}
foreach (DataRow attribute_row in AttributeSet.Rows)
{
String AttributeID = attribute_row["attribute_id"].ToString();
String AttributeName = attribute_row["attribbute_name"].ToString();
String DSVSchemaName = attribute_row["dsv_schema_name"].ToString();
String DBColumn = attribute_row["key_column_db_column"].ToString();
String OleDbType = attribute_row["key_column_oledb_type"].ToString();
String AttributeUsage = attribute_row["attribute_usage"].ToString();
String NameColumn = attribute_row["name_column"].ToString();
String Visible = attribute_row["visible"].ToString();
String AttHierEnabled = attribute_row["atthier_enabled"].ToString();
String OrderBy = attribute_row["order_by"].ToString();
Microsoft.AnalysisServices.OrderBy attribute_order_by = Microsoft.AnalysisServices.OrderBy.Name;
if (OrderBy.ToLower() == "key")
{
attribute_order_by = Microsoft.AnalysisServices.OrderBy.Key;
}
AS_API.ADD_ATTRIBUTE_TO_DIMENSION(
sqlHelper,
DSV,
dim,
DataSourceName,
DBColumn,
AttributeID,
AttributeName,
AS_API_HELPER.GET_SSAS_OLEDB_TYPE_BY_NAME(OleDbType),
AS_API_HELPER.GET_SSAS_ATTRIBUTE_USAGE_BY_NAME(AttributeUsage),
NameColumn, Convert.ToBoolean(Visible),
Convert.ToBoolean(AttHierEnabled),
attribute_order_by
);
}
DataTable AttributeRelationShipSet = asMeta.GET_SSAS_ATTRIBUTE_RELATION_SHIPS_SET(sqlHelper, DimensionID);
sqlHelper.ADD_MESSAGE_LOG(String.Format("[Create dimension] Adding {0} attribute relationships for dimension {1}", AttributeRelationShipSet.Rows.Count.ToString(), DimensionID)
, MESSAGE_TYPE.ATTRIBUTE_RELATIONSHIP
, MESSAGE_RESULT_TYPE.Normal);
foreach (DataRow row in AttributeRelationShipSet.Rows)
{
String BasedAttributeID = row["based_attribute_id"].ToString();
String RelatedAttributeID = row["related_attribute_id"].ToString();
String RelationShipType = row["relationship_type"].ToString();
Microsoft.AnalysisServices.RelationshipType AttributeRelationShipType = AS_API_HELPER.GET_SSAS_ATTRIBUTE_RELATION_SHIP_TYPE_BY_NAME(RelationShipType);
AS_API.ADD_ATTRIBUTE_RELATIONSHIP(
sqlHelper,
dim,
BasedAttributeID,
RelatedAttributeID,
AttributeRelationShipType);
}
DataTable HierarchiesSet = asMeta.GET_SSAS_HIERARCHIES_SET(sqlHelper, DimensionID);
sqlHelper.ADD_MESSAGE_LOG(
String.Format("[Create dimension] Adding {0} hierarchy levels for dimension {1}", HierarchiesSet.Rows.Count.ToString(), DimensionID)
, MESSAGE_TYPE.HIERARCHIES
, MESSAGE_RESULT_TYPE.Normal);
foreach (DataRow row in HierarchiesSet.Rows)
{
String HierarchyName = row["hierarchy_name"].ToString();
String LevelName = row["level_name"].ToString();
String LevelID = row["level_id"].ToString();
String SourceAttributeID = row["source_attribute_id"].ToString();
AS_API.ADD_ATTRIBUTE_HIERACHIES(
sqlHelper,
dim,
HierarchyName,
LevelName,
SourceAttributeID);
sqlHelper.ADD_MESSAGE_LOG(
"[Create dimension->Hierarchy] |" + new string('_', Convert.ToInt16(LevelID)) + LevelName
, MESSAGE_TYPE.HIERARCHIES
, MESSAGE_RESULT_TYPE.Normal);
}
sqlHelper.ADD_MESSAGE_LOG(
"[Create dimension] Updating changes of dimension objects.."
, MESSAGE_TYPE.DIMENSION
, MESSAGE_RESULT_TYPE.Normal);
dim.Update();
sqlHelper.ADD_MESSAGE_LOG(
"[Create dimension] Succeed to add changes to dimension objects.."
, MESSAGE_TYPE.DIMENSION
, MESSAGE_RESULT_TYPE.Normal);
}
}
catch (Exception ex)
{
sqlHelper.ADD_MESSAGE_LOG("[Create dimension] " + ex.Message.ToString(), MESSAGE_TYPE.ADD_DIMENSION, MESSAGE_RESULT_TYPE.Error);
throw (ex);
}
}
//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);
}
/// <summary>
/// Adds a connection object (aka DataSource object in AMO) and a
/// DataSourceView, if it doesn't exist, to a the local copy of
/// the tabular database, using the given OleDb connection string.
/// </summary>
/// <param name="tabularDatabase">A reference to an AMO server object</param>
/// <param name="datasourceOledbConnectionString">A well formed OleDb connection string to the relational data source</param>
/// <param name="connectionName">A string with the name on the connection object</param>
/// <param name="userName">(optional) A string with the user name; when given, it modifies impersonation from service account to user account</param>
/// <param name="password">(optional) A string with the user password; WARNING: this string is not encrypted</param>
public static void ConnectionAddRelationalDataSource(AMO.Database tabularDatabase,
string datasourceOledbConnectionString,
string connectionName,
bool updateInstance = true,
string userName = null,
string password = null)
{
// Major steps in creating a connection object or AMO.DataSource
// - Validate required input arguments
// - Create local copy of data source object (the connection object)
// - Verify there are no other DataSourceView view object
// - Create dsv object in database
// - Get source database schema
// - Add DataSource object to database
// - Add DSV (if created) to database
//
// Note: There are no validations for duplicated names, invalid names or
// similar scenarios. It is expected the server will take care of them and
// throw exceptions on any invalid situation.
//
// Note: There are no validations on the 'datasourceOledbConnectionString'
// The user is responsible for accuracy and usability of the string.
//
// Note: In AMO only well formed OleDb connection strings are supported
// The 'Provider' key word must be the first keyword in the connection string
//
//
// Note: As of SQL Server 2012 Analysis Services, the only supported OleDb providers were:
//
// - "MICROSOFT.JET.OLEDB.4.0" <-- Microsoft OLE DB Provider for Microsoft Jet 4.0
// - "SQLOLEDB" <-- Microsoft SQL OLE DB Provider for SQL Server
// - "SNAC" <-- Microsoft SQL Native Client OLE DB Provider
// - "SQLNCLI" <-- SQL Server Native Client
// - "MSDAORA" <-- Microsoft OLE DB Provider for Oracle
// - "DB2OLEDB" <-- Microsoft OLE DB Provider for DB2
// - "TDOLEDB" <-- OLE DB Provider for Teradata
// - "IFXOLEDBC" <-- IBM Informix OLE DB Provider
// - "SYBASE ASE OLE DB PROVIDER" <-- OLE DB Provider for Sybase Adaptive Server Enterprise (ASE)
// - "ASAPROV" <-- OLE DB Provider for Sybase Adaptive Server Anywhere (ASA)
// - "SYBASE OLEDB PROVIDER" <-- OLE DB Provider for Sybase in version 12
// - "ASEOLEDB" <-- OLE DB Provider for Sybase in version 15
// - "Microsoft SQL Server MPP OLE DB Provider" <-- OLE DB Provider for SQL Server MPP OLE DB Provider
bool dsvAdded = false;
// Validate required input arguments
if (tabularDatabase == null)
{
throw new ArgumentNullException(TabularDatabaseStringName);
}
if (datasourceOledbConnectionString.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException("datasourceOledbConnectionString");
}
if (connectionName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException("connectionName");
}
if (!IsDatabaseCompatibilityLevelCorrect(tabularDatabase))
{
throw new InvalidOperationException(Resources.InvalidCompatibilityLevelOperationException);
}
// Create local copy of data source
using (AMO.DataSource connection = new AMO.RelationalDataSource(connectionName, connectionName))
using (AMO.DataSourceView dsv = new AMO.DataSourceView(connectionName, connectionName))
{
connection.ConnectionString = datasourceOledbConnectionString;
//If user name is null or blank, use default ImpersonateServiceAccount
if (userName.IsNullOrEmptyOrWhitespace())
{
connection.ImpersonationInfo = new AMO.ImpersonationInfo(AMO.ImpersonationMode.ImpersonateServiceAccount);
}
else
{// At this point, there is no verification that the given credentials will work
connection.ImpersonationInfo = new AMO.ImpersonationInfo(AMO.ImpersonationMode.ImpersonateAccount, userName.Trim(), password);
}
// Verify DSV existence and create one if needed
if (tabularDatabase.DataSourceViews.Count == 0)
{// No DSV in the database
// Note: DSV added and populated it with the entire relational database schema
dsvAdded = true;
dsv.Schema = GetDatabaseSchema(datasourceOledbConnectionString);
dsv.DataSourceID = connection.ID;
}
// Add DataSource and DataSourceView objects to database
tabularDatabase.DataSources.Add(connection);
if (dsvAdded)
{
tabularDatabase.DataSourceViews.Add(dsv);
}
}
// Update server instance
if (updateInstance)
{
tabularDatabase.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate);
}
}