public static string[] HierarchiesEnumerate(AMO.Database tabularDatabase, string tableName)
{
#region Validate input arguments and other initial preparations
// Validate required input arguments
if (tabularDatabase == null)
{
throw new ArgumentNullException(TabularDatabaseStringName);
}
if (tableName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(TableStringName);
}
if (!IsDatabaseCompatibilityLevelCorrect(tabularDatabase))
{
throw new InvalidOperationException(Resources.InvalidCompatibilityLevelOperationException);
}
// Other initial preparations
// - Cleaning and preparing name variables
tableName = tableName.Trim();
// - Obtain table name in DSV
string datasourceTableName = tabularDatabase.Dimensions.GetByName(tableName).ID;
#endregion
return((from hierarchy in tabularDatabase.Dimensions[datasourceTableName].Hierarchies.Cast <AMO.Hierarchy>()
select hierarchy.Name).ToArray());
}
/// <summary>
/// Add dimension into cube (instead of cube db)
/// </summary>
/// <param name="sqlHelper"></param>
/// <param name="asMeta"></param>
/// <param name="cubeDB"></param>
/// <param name="cube"></param>
public void ADD_DIMENSION_TO_CUBE(DB_SQLHELPER_BASE sqlHelper
, AS_METADATA asMeta
, Microsoft.AnalysisServices.Database cubeDB
, Cube cube
)
{
try
{
DataTable DimensionSet = asMeta.GET_SSAS_DIMENSION_SET(sqlHelper);
foreach (DataRow row in DimensionSet.Rows)
{
String DimensionID = row["dimension_id"].ToString();
String DimensionName = row["dimension_name"].ToString();
String DimensionType = row["dimension_type"].ToString();
AS_API.ADD_CUBE_DIMENSION(
sqlHelper,
cubeDB,
cube,
DimensionID,
DimensionType);
sqlHelper.ADD_MESSAGE_LOG(
String.Format("Addedd Dimension {0} into Cube ", DimensionID)
, MESSAGE_TYPE.ADD_CUBE_DIMENSION
, MESSAGE_RESULT_TYPE.Normal);
}
cube.Update(Microsoft.AnalysisServices.UpdateOptions.ExpandFull);
}
catch (Exception ex)
{
sqlHelper.ADD_MESSAGE_LOG(ex.Message.ToString(), MESSAGE_TYPE.DIMENSION, MESSAGE_RESULT_TYPE.Error);
throw (ex);
}
}
public static string[] KpisEnumerate(AMO.Database tabularDatabase,
string tableName)
{
#region Validate input arguments and other initial preparations
// Validate required input arguments
if (tabularDatabase == null)
{
throw new ArgumentNullException(TabularDatabaseStringName);
}
if (tableName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(TableStringName);
}
// Validate required initial conditions
if (!IsDatabaseCompatibilityLevelCorrect(tabularDatabase))
{
throw new InvalidOperationException(Resources.InvalidCompatibilityLevelOperationException);
}
// Other initial preparations
// - Cleaning and preparing name variables
tableName = tableName.Trim();
#endregion
return((from measureName in MeasuresEnumerate(tabularDatabase, tableName)
where KpiExist(tabularDatabase, tableName, measureName)
select measureName).ToArray());
}
public static void ExportVPAX(string serverName, string databaseName, string path, bool includeTomModel, string applicationName, string applicationVersion, bool readStatisticsFromData, string modelName)
{
//
// Get Dax.Model object from the SSAS engine
//
Dax.Metadata.Model model = Dax.Metadata.Extractor.TomExtractor.GetDaxModel(serverName, databaseName, applicationName, applicationVersion,
readStatisticsFromData: readStatisticsFromData,
sampleRows: 0);
//
// Get TOM model from the SSAS engine
//
Microsoft.AnalysisServices.Database database = includeTomModel ? Dax.Metadata.Extractor.TomExtractor.GetDatabase(serverName, databaseName): null;
//
// Create VertiPaq Analyzer views
//
Dax.ViewVpaExport.Model viewVpa = new Dax.ViewVpaExport.Model(model);
model.ModelName = new Dax.Metadata.DaxName(modelName);
//
// Save VPAX file
//
// TODO: export of database should be optional
Dax.Vpax.Tools.VpaxTools.ExportVpax(path, model, viewVpa, database);
}
public bool VerifySelections()
{
try
{
if (VerifyServer())
{
if (srv.Databases.ContainsName(cmbDatabase.Text))
db = srv.Databases.GetByName(cmbDatabase.Text);
else
return false;
if (db == null || !db.Dimensions.ContainsName(cmbDimension.Text))
return false;
dim = db.Dimensions.GetByName(cmbDimension.Text);
for (int i = 0; i < PCAttributesToInclude.Count; i++)
if (!dim.Attributes.ContainsName(PCAttributesToInclude[i]))
{
PCAttributesToInclude.RemoveAt(i);
i--;
}
for (int i = 0; i < NonPCHierarchiesToInclude.Count; i++)
if (!dim.Attributes.ContainsName(NonPCHierarchiesToInclude[i]))
{
NonPCHierarchiesToInclude.RemoveAt(i);
i--;
}
return true;
}
return false;
}
catch (Exception)
{
return false;
}
}
private static void MeasureDrop_2012SP1(AMO.Database tabularDatabase,
string tableName,
string measureName)
{
// Note: Because of the way KPIs are implemented in tabular models 2012, as a promotion of the measure;
// Then, when you want to delete a measure, you also have to delete the dependent KPI (if exists)
// Verifying and deleting KPI if it exists
if (KpiExist(tabularDatabase, tableName, measureName))
{
KpiDrop(tabularDatabase, tableName, measureName, false);
}
using (AMO.MdxScript modelMdxScript = tabularDatabase.Cubes[0].MdxScripts[MdxScriptStringName])
{
modelMdxScript.CalculationProperties.Remove(measureName);
Regex commandTypeExpression = new Regex(MeasurePattern, RegexOptions.CultureInvariant | RegexOptions.IgnoreCase | RegexOptions.Multiline);
foreach (AMO.Command modelCommand in modelMdxScript.Commands)
{
if (commandTypeExpression.IsMatch(modelCommand.Text) && (string.Compare(measureName, commandTypeExpression.Match(modelCommand.Text).Groups[MeasureStringName].Value, StringComparison.InvariantCultureIgnoreCase) == 0))
{
modelMdxScript.Commands.Remove(modelCommand);
break;
}
}
}
}
public static string[] CalculatedColumnsEnumerate(AMO.Database tabularDatabase, string tableName)
{
#region Validate input arguments and other initial preparations
// Validate required input arguments
if (tabularDatabase == null)
{
throw new ArgumentNullException(TabularDatabaseStringName);
}
if (tableName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(TableStringName);
}
if (!IsDatabaseCompatibilityLevelCorrect(tabularDatabase))
{
throw new InvalidOperationException(Resources.InvalidCompatibilityLevelOperationException);
}
// Other initial preparations
// - Cleaning and preparing name variables
tableName = tableName.Trim();
#endregion
return((from calculatedColumn in tabularDatabase.Dimensions.GetByName(tableName).Attributes.Cast <AMO.DimensionAttribute>()
where (calculatedColumn.NameColumn.Source is AMO.ExpressionBinding)
select calculatedColumn.Name).ToArray());
}
private void cboDatabaseName_SelectedIndexChanged(object sender, EventArgs e)
{
cboCubeName.Items.Clear();
cboCubeName.SelectedItem = null;
//OLAPDatabase = OLAPServer.Databases[cboDatabaseName.SelectedItem.ToString()];
OLAPDatabase = OLAPServer.Databases[(cboDatabaseName.SelectedItem as ComboboxItem).Value.ToString()];
TOMDb = TOMServer.Databases[(cboDatabaseName.SelectedItem as ComboboxItem).Value.ToString()];
TabularCompatibilityLevel = TOMDb.CompatibilityLevel;
if (TabularCompatibilityLevel < 1200)
{
cboCubeName.Enabled = true;
foreach (AMO.Cube OLAPCube in OLAPDatabase.Cubes)
{
cboCubeName.Items.Add(OLAPCube.Name);
}
if (cboCubeName.Items.Count == 1)
{
cboCubeName.SelectedItem = cboCubeName.Items[0];
}
}
else
{
cboCubeName.Enabled = false;
txtFileName.Text = cboDatabaseName.SelectedItem.ToString();
}
}
private void cmbDimension_Enter(object sender, EventArgs e)
{
if (cmbDatabase.Text.Trim() != "" || txtServer.Text.Trim() != "")
{
try
{
if (VerifyServer())
{
db = srv.Databases.GetByName(cmbDatabase.Text);
cmbDimension.Items.Clear();
for (int i = 0; i < db.Dimensions.Count; i++)
{
if (db.Dimensions[i].IsParentChild)
{
cmbDimension.Items.Add(db.Dimensions[i].Name);
}
}
cmbDimension.Sorted = true;
}
}
catch (Exception)
{
MessageBox.Show("Error accessing database server.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
public RelationshipGraph(AMO.Database tabularDatabase)
{
foreach (TableInfo tableInfo in AMO2Tabular.TablesEnumerateFull(tabularDatabase))
{
using (AMO.MeasureGroup currentMeasureGroup = tabularDatabase.Cubes[0].MeasureGroups[tableInfo.DataSourceName])
{
foreach (AMO.MeasureGroupDimension measureGroupDimension in currentMeasureGroup.Dimensions)
{
if (measureGroupDimension is AMO.ReferenceMeasureGroupDimension)
{
AMO.ReferenceMeasureGroupDimension referencedTable = measureGroupDimension as AMO.ReferenceMeasureGroupDimension;
string foreignTableId = referencedTable.IntermediateCubeDimensionID;
string foreignColumnId = referencedTable.IntermediateGranularityAttributeID;
string primaryKeyTableId = referencedTable.CubeDimensionID;
string primaryKeyColumnId = string.Empty;
foreach (AMO.MeasureGroupAttribute attribute in referencedTable.Attributes)
{
if (attribute.Type == AMO.MeasureGroupAttributeType.Granularity)
{
primaryKeyColumnId = attribute.AttributeID;
break;
}
}
FullName foreignKeyEnd = new FullName(foreignTableId, foreignColumnId);
FullName primaryKeyEnd = new FullName(primaryKeyTableId, primaryKeyColumnId);
RelationshipPair relationship = new RelationshipPair(primaryKeyEnd, foreignKeyEnd);
AddForeignKeyUpPair(primaryKeyTableId, relationship);
AddPrimaryKeyDownPair(foreignTableId, relationship);
}
}
}
}
}
public static string[] MeasuresEnumerate(AMO.Database tabularDatabase, string tableName)
{
#region Validate input arguments and other initial preparations
// Validate required input arguments
if (tabularDatabase == null)
{
throw new ArgumentNullException(TabularDatabaseStringName);
}
if (tableName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(TableStringName);
}
if (!IsDatabaseCompatibilityLevelCorrect(tabularDatabase))
{
throw new InvalidOperationException(Resources.InvalidCompatibilityLevelOperationException);
}
// Other initial preparations
// - Cleaning and preparing name variables
tableName = tableName.Trim();
#endregion
switch ((CompatibilityLevel)tabularDatabase.CompatibilityLevel)
{
case CompatibilityLevel.SQL2012RTM:
return(MeasuresEnumerate_2012RTM(tabularDatabase, tableName));
case CompatibilityLevel.SQL2012SP1:
return(MeasuresEnumerate_2012SP1(tabularDatabase, tableName));
default:
throw new NotSupportedException(Resources.InvalidCompatibilityLevelOperationException);
}
}
private static bool IsDatabaseCompatibilityLevelCorrect(AMO.Database db)
{
return((db != null) &&
IsServerCompatibilityLevelCorrect(db.Parent) &&
(db.Parent.DefaultCompatibilityLevel >= db.CompatibilityLevel) &&
IsCompatibilityLevelCorrect(db.CompatibilityLevel));
}
public static bool PerspectiveContainsMeasure(AMO.Database tabularDatabase,
string perspectiveName,
string measureName)
{
#region Validate input arguments and other initial preparations
// Validate required input arguments
if (tabularDatabase == null)
{
throw new ArgumentNullException(TabularDatabaseStringName);
}
if (perspectiveName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(PerspectiveStringName);
}
if (measureName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(MeasureStringName);
}
if (!IsDatabaseCompatibilityLevelCorrect(tabularDatabase))
{
throw new InvalidOperationException(Resources.InvalidCompatibilityLevelOperationException);
}
// Other initial preparations
// - Cleaning and preparing name variables
perspectiveName = perspectiveName.Trim();
measureName = measureName.Trim();
#endregion
return(tabularDatabase.Cubes[0].Perspectives.GetByName(perspectiveName).Calculations.Contains(measureName));
}
private static void MeasureAlterFormat_2012SP1(AMO.Database tabularDatabase,
string tableName,
string measureName,
string format)
{
tabularDatabase.Cubes[0].MdxScripts[MdxScriptStringName].CalculationProperties[measureName].FormatString = format;
}
private static string[] MeasuresEnumerate_2012SP1(AMO.Database tabularDatabase, string tableName)
{
Regex commandTypeExpression = new Regex(MeasurePattern, RegexOptions.CultureInvariant | RegexOptions.IgnoreCase | RegexOptions.Multiline);
return((from measure in tabularDatabase.Cubes[0].MdxScripts[MdxScriptStringName].Commands.Cast <AMO.Command>()
where string.Compare(commandTypeExpression.Match(measure.Text).Groups[TableStringName].Value.Replace("'", string.Empty), tableName, StringComparison.OrdinalIgnoreCase) == 0
select commandTypeExpression.Match(measure.Text).Groups[MeasureStringName].Value).ToArray());
}
public static void RlsAdd(AMO.Database tabularDatabase,
string roleName,
string tableName,
string daxFilterExpression,
bool updateInstance = true)
{
// Major steps in adding a Row Level Security (RLS)
//
// - Validate required input arguments and other initial preparations
// - Add RLS to Table (as dimension) and enable ReadAccess
//
// Note: In AMO, strings as indexers refer to the ID of the object, not the name
//
#region Validate input arguments and other initial preparations
// Validate required input arguments
if (tabularDatabase == null)
{
throw new ArgumentNullException(TabularDatabaseStringName);
}
if (roleName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(RoleStringName);
}
if (tableName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(TableStringName);
}
if (daxFilterExpression.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(DaxFilterExpressionStringName);
}
if (!IsDatabaseCompatibilityLevelCorrect(tabularDatabase))
{
throw new InvalidOperationException(Resources.InvalidCompatibilityLevelOperationException);
}
// Other initial preparations
// - Cleaning and preparing name variables
roleName = roleName.Trim();
tableName = tableName.Trim();
daxFilterExpression = daxFilterExpression.Trim();
#endregion
string dimensionPermissionName = string.Format(CultureInfo.InvariantCulture, "DimensionPermision_for_{0}", roleName);
string roleId = tabularDatabase.Roles.GetByName(roleName).ID;
using (AMO.DimensionPermission dimensionPermission = tabularDatabase.Dimensions.GetByName(tableName).DimensionPermissions.Add(roleId, dimensionPermissionName))
{
dimensionPermission.Read = AMO.ReadAccess.Allowed;
dimensionPermission.AllowedRowsExpression = daxFilterExpression;
}
// Update server instance
if (updateInstance)
{
tabularDatabase.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate);
}
}
public static void MeasureDrop(AMO.Database tabularDatabase,
string tableName,
string measureName,
bool updateInstance = true)
{
#region Validate input arguments and other initial preparations
// Validate required input arguments
if (tabularDatabase == null)
{
throw new ArgumentNullException(TabularDatabaseStringName);
}
if (tableName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(TableStringName);
}
if (measureName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(MeasureStringName);
}
// Validate required initial conditions
if (!IsDatabaseCompatibilityLevelCorrect(tabularDatabase))
{
throw new InvalidOperationException(Resources.InvalidCompatibilityLevelOperationException);
}
if (!MeasureExists(tabularDatabase, tableName, measureName))
{
throw new InvalidOperationException(Resources.MeasureDoesntExistsInvalidOperationException);
}
// Other initial preparations
// - Cleaning and preparing name variables
tableName = tableName.Trim();
measureName = measureName.Trim();
#endregion
switch ((CompatibilityLevel)tabularDatabase.CompatibilityLevel)
{
case CompatibilityLevel.SQL2012RTM:
MeasureDrop_2012RTM(tabularDatabase, tableName, measureName);
break;
case CompatibilityLevel.SQL2012SP1:
MeasureDrop_2012SP1(tabularDatabase, tableName, measureName);
break;
default:
throw new NotSupportedException(Resources.InvalidCompatibilityLevelOperationException);
}
// Update server instance
if (updateInstance)
{
tabularDatabase.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate);
}
}
private static void SetPKColumn(AMO.Database Database, AMO.DimensionAttribute PKAttribute)
{
//Get RowNumber Attribute
AMO.DimensionAttribute RowNumber = null;
//Find all 'unwanted' Key attributes, remove their Key definitions and include the attributes in the ["RowNumber"].AttributeRelationships
foreach (AMO.DimensionAttribute CurrentDimAttribute in PKAttribute.Parent.Attributes)
{
foreach (AMO.DimensionAttribute Attribute in PKAttribute.Parent.Attributes)
{
if (Attribute.Type == AMO.AttributeType.RowNumber)
{
RowNumber = Attribute;
}
}
if (RowNumber == null)
{
throw new System.Exception("Unable to find rownumber in Dimension " + PKAttribute.Parent.Name);
}
if ((CurrentDimAttribute.Usage == AMO.AttributeUsage.Key) && (CurrentDimAttribute.ID != PKAttribute.ID))
{
CurrentDimAttribute.Usage = AMO.AttributeUsage.Regular;
if (CurrentDimAttribute.Type != AMO.AttributeType.RowNumber)
{
CurrentDimAttribute.KeyColumns[0].NullProcessing = AMO.NullProcessing.Preserve;
CurrentDimAttribute.AttributeRelationships.Clear();
if (!RowNumber.AttributeRelationships.ContainsName(CurrentDimAttribute.ID))
{
AMO.AttributeRelationship currentAttributeRelationship = CurrentDimAttribute.Parent.Attributes["RowNumber"].AttributeRelationships.Add(CurrentDimAttribute.ID);
currentAttributeRelationship.OverrideBehavior = AMO.OverrideBehavior.None;
}
RowNumber.AttributeRelationships[CurrentDimAttribute.ID].Cardinality = AMO.Cardinality.Many;
}
}
}
//Remove PKColumnName from ["RowNumber"].AttributeRelationships
int PKAtribRelationshipPosition = RowNumber.AttributeRelationships.IndexOf(PKAttribute.Name);
if (PKAtribRelationshipPosition != -1)
{
RowNumber.AttributeRelationships.RemoveAt(PKAtribRelationshipPosition, true);
}
//Define PKColumnName as Key and add ["RowNumber"] to PKColumnName.AttributeRelationships with cardinality of One
PKAttribute.Usage = AMO.AttributeUsage.Key;
PKAttribute.KeyColumns[0].NullProcessing = AMO.NullProcessing.Error;
if (!PKAttribute.AttributeRelationships.ContainsName("RowNumber"))
{
AMO.DimensionAttribute currentAttribute = RowNumber;
AMO.AttributeRelationship currentAttributeRelationship = PKAttribute.AttributeRelationships.Add(currentAttribute.ID);
currentAttributeRelationship.OverrideBehavior = AMO.OverrideBehavior.None;
}
PKAttribute.AttributeRelationships["RowNumber"].Cardinality = AMO.Cardinality.One;
}
/// <summary>
/// Verifies if there exists a relationship between the given columns in the model
/// </summary>
/// <param name="tabularDatabase"></param>
/// <param name="pkTableName"></param>
/// <param name="pkColumnName"></param>
/// <param name="foreignTableName"></param>
/// <param name="foreignColumnName"></param>
/// <returns></returns>
public static bool RelationshipExists(AMO.Database tabularDatabase,
string pkTableName,
string pkColumnName,
string foreignTableName,
string foreignColumnName)
{
// This is a shortcut wrapper to verify if a relationship exists
// whenever you don't need the relationshipId
return(!RelationshipTryGetRelationshipId(tabularDatabase, pkTableName, pkColumnName, foreignTableName, foreignColumnName).IsNullOrEmptyOrWhitespace());
}
public void TableCount1103()
{
using (Amo.Server server = new Amo.Server())
{
server.Connect("localhost\\tb");
Amo.Database db = server.Databases.FindByName("Test1103_Target");
Assert.IsNotNull(db);
Assert.AreEqual(3, db.Cubes[0].Dimensions.Count);
server.Disconnect();
}
}
public static string[] TablesEnumerate(AMO.Database tabularDatabase)
{
#region Validate input arguments and other initial preparations
// Validate required input arguments
if (tabularDatabase == null)
{
throw new ArgumentNullException(TabularDatabaseStringName);
}
#endregion
return((from dimension in tabularDatabase.Dimensions.Cast <AMO.Dimension>()
select dimension.Name).ToArray());
}
public static void RlsDrop(AMO.Database tabularDatabase,
string roleName,
string tableName,
bool updateInstance = true)
{
// Major steps in deleting/droping Row Level Security (RLS)
//
// - Validate required input arguments and other initial preparations
// - Remove DimensionPermissions in table (as dimension)
//
// Note: In AMO, strings as indexers refer to the ID of the object, not the name
//
#region Validate input arguments and other initial preparations
// Validate required input arguments
if (tabularDatabase == null)
{
throw new ArgumentNullException(TabularDatabaseStringName);
}
if (roleName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(RoleStringName);
}
if (tableName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(TableStringName);
}
if (!IsDatabaseCompatibilityLevelCorrect(tabularDatabase))
{
throw new InvalidOperationException(Resources.InvalidCompatibilityLevelOperationException);
}
// Other initial preparations
// - Cleaning and preparing name variables
roleName = roleName.Trim();
tableName = tableName.Trim();
#endregion
// [Codeplex issue # 3]
// [JPJofre, 2012-10-18]
// [Description: tabularDatabase.Roles.GetByName(roleName).ID, returns the database role id not the DimensionsPermissions Id]
// [Suggested fix: TBD]
string roleId = tabularDatabase.Roles.GetByName(roleName).ID;
tabularDatabase.Dimensions.GetByName(tableName).DimensionPermissions.Remove(roleId, true);
// Update server instance
if (updateInstance)
{
tabularDatabase.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate);
}
}
public void RestoreCube(string connectionString, string abfFilePath)
{
if (string.IsNullOrEmpty(abfFilePath))
{
FailedToRestore($"abfFilePath is null or empty.");
//throw new ArgumentNullException($"abfFilePath is null or empty.");
}
if (string.IsNullOrEmpty(connectionString))
{
FailedToRestore($"connectionString is null or empty.");
//throw new ArgumentNullException($"connectionString is null or empty.");
}
try
{
if (ConnectSSASServer(connectionString))
{
string SSASDBName = Path.GetFileNameWithoutExtension(abfFilePath);
ssasServer.Restore(abfFilePath, SSASDBName, true);
ssasServer.Restore(abfFilePath, SSASDBName, true);
ssasServer.Refresh();
AMO.Database ssasDataBase = ssasServer.Databases[SSASDBName];
ssasDataBase.Cubes[0].Update();
ssasDataBase.Cubes[0].Refresh();
RestoredSuccessfully();
}
else
{
FailedToRestore($"Failed to connect to the server.");
}
}
catch (NullReferenceException nex)
{
FailedToRestore(nex.Message);
//throw new Exception(nex.Message);
}
catch (Exception ex)
{
FailedToRestore(ex.Message);
//throw new Exception(ex.Message);
}
finally
{
DisConnectSSASServer();
}
}
public static string GetSerialisedXMLString(AMO.Database AMODatabase)
{
using (var StringWriter = new System.IO.StringWriter())
{
using (var XMLWriter = System.Xml.XmlWriter.Create(StringWriter, new System.Xml.XmlWriterSettings {
Indent = true,
IndentChars = "\t",
NewLineOnAttributes = true
}))
{
AMO.Utils.Serialize(XMLWriter, AMODatabase, false);
}
return(StringWriter.ToString());
}
}
public bool ConnectToAnalysisServer()
{
_CbServer = new Server();
_CbDatabase = new Database();
_CbDataSource = new RelationalDataSource();
_CbDataSourceView = new DataSourceView();
_CbDataSet = new DataSet();
//Connecting to the Analysis Services.
_CbServer = (Server)ConnectAnalysisServices(CbServerName, CbProviderName);
if (_CbServer != null)
{
LoadFromDsDwMap();
}
return(_CbServer != null);
}
public static void TableAlterSetDateTable(AMO.Database tabularDatabase,
string tableName,
string columnName,
bool updateInstance = true)
{
// Major steps in setting the Date table in the database
//
// - Validate required input arguments
// - Set Date Column to Primary Key
// - Set table dimension type to Time
//
// 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.
//
// Validate required input arguments
if (tabularDatabase == null)
{
throw new ArgumentNullException(TabularDatabaseStringName);
}
if (tableName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(TableStringName);
}
if (columnName.IsNullOrEmptyOrWhitespace())
{
throw new ArgumentNullException(ColumnStringName);
}
//
// Set Date Column to Primary Key, but do not update server instance until comming back and procesing what the user requests in 'updateInstance'
// Note: As a best practice every time the object model is altered a database update needs to be issued; however, in this case
// to avoid multiple database updates while creating one major object (ie, Table) we are invoking ColumnAlterSetPrimaryKey with NO updateInstance
ColumnAlterSetPrimaryKey(tabularDatabase, tableName, columnName, false);
// Set table dimension type to Time
tabularDatabase.Dimensions[tabularDatabase.Dimensions.GetByName(tableName).ID].Type = AMO.DimensionType.Time;
// Update server instance
if (updateInstance)
{
tabularDatabase.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate);
}
}
private static void RelationshipAddReferenceMeasureGroupDimension(AMO.Database tabularDatabase,
string currentMeasureGroupId,
RelationshipPair relationshipPair,
string relationshipId = null)
{
string foreignTableId = relationshipPair.ForeignKeyEnd.TableId;
string foreignColumnId = relationshipPair.ForeignKeyEnd.ColumnId;
string pkTableId = relationshipPair.PrimaryKeyEnd.TableId;
string pkColumnId = relationshipPair.PrimaryKeyEnd.ColumnId;
// Creating the ReferenceMeasureGroupDimension that defines the 'Activeness' of a relationship
using (AMO.Cube modelCube = tabularDatabase.Cubes[0])
using (AMO.MeasureGroup currentMG = modelCube.MeasureGroups[currentMeasureGroupId])
using (AMO.ReferenceMeasureGroupDimension newReferenceMGDim = new AMO.ReferenceMeasureGroupDimension())
{
newReferenceMGDim.CubeDimensionID = pkTableId;
newReferenceMGDim.IntermediateCubeDimensionID = foreignTableId;
newReferenceMGDim.IntermediateGranularityAttributeID = foreignColumnId;
// Replicating attributes (columns) from dimension
foreach (AMO.CubeAttribute PKAttribute in modelCube.Dimensions[pkTableId].Attributes)
{
using (AMO.MeasureGroupAttribute PKMGAttribute = newReferenceMGDim.Attributes.Add(PKAttribute.AttributeID))
using (AMO.DataItem dataItem = new AMO.DataItem(pkTableId, PKAttribute.AttributeID, PKAttribute.Attribute.KeyColumns[0].DataType))
using (AMO.ColumnBinding columnBinding = new AMO.ColumnBinding(pkTableId, PKAttribute.AttributeID))
{
PKMGAttribute.KeyColumns.Add(dataItem);
PKMGAttribute.KeyColumns[0].Source = columnBinding;
}
}
newReferenceMGDim.Attributes[pkColumnId].Type = AMO.MeasureGroupAttributeType.Granularity;
// If relationship is not null or empty then this is a direct relationship and
// has to have Materialization.Regular
if (!relationshipId.IsNullOrEmptyOrWhitespace())
{
newReferenceMGDim.Materialization = AMO.ReferenceDimensionMaterialization.Regular;
newReferenceMGDim.RelationshipID = relationshipId;
}
else
{
newReferenceMGDim.Materialization = AMO.ReferenceDimensionMaterialization.Indirect;
}
// Adding the ReferenceMeasureGroupDimension to the measure group
currentMG.Dimensions.Add(newReferenceMGDim);
}
}
private static bool KpiExist_2012RTM(AMO.Database tabularDatabase,
string tableName,
string kpiName)
{
Regex kpiTypeExpression = new Regex(KpiPattern, RegexOptions.CultureInvariant | RegexOptions.IgnoreCase | RegexOptions.Multiline);
foreach (Match match in kpiTypeExpression.Matches(tabularDatabase.Cubes[0].MdxScripts[MdxScriptStringName].Commands[1].Text))
{
string matchKpiName = match.Groups["kpiName"].Value.Replace("[", string.Empty);
matchKpiName = matchKpiName.Replace("]", string.Empty);
if (string.Compare(kpiName, matchKpiName, StringComparison.InvariantCultureIgnoreCase) == 0)
{
return(true);
}
}
return(false);
}
private static void MeasureDrop_2012RTM(AMO.Database tabularDatabase,
string tableName,
string measureName)
{
// Note: Because of the way KPIs are implemented in tabular models 2012, as a promotion of the measure;
// Then, when you want to delete a measure, you also have to delete the dependent KPI (if exists)
// Verifying and deleting KPI if it exists
if (KpiExist(tabularDatabase, tableName, measureName))
{
KpiDrop(tabularDatabase, tableName, measureName, false);
}
using (AMO.MdxScript modelMdxScript = tabularDatabase.Cubes[0].MdxScripts[MdxScriptStringName])
{
modelMdxScript.CalculationProperties.Remove(measureName);
// To be sure that changing the command expression doesn't affect the search...
// The search is performed over the AMO command text --> modelCommand.Text
// But the removal of text is done over the copy --> measureRemovedCommands
AMO.Command modelCommand = modelMdxScript.Commands[1];
Regex commandTypeExpression = new Regex(MeasurePattern, RegexOptions.CultureInvariant | RegexOptions.IgnoreCase | RegexOptions.Multiline);
bool found = false;
string measureRemovedCommands = null;
foreach (Match match in commandTypeExpression.Matches(modelCommand.Text))
{
if (string.Compare(measureName, match.Groups[MeasureStringName].Value, StringComparison.InvariantCultureIgnoreCase) == 0)
{
measureRemovedCommands = modelCommand.Text.Replace(match.Groups[0].Value, string.Empty);
found = true;
break;
}
}
if (found)
{
modelCommand.Text = measureRemovedCommands;
}
else
{
throw new InvalidOperationException(Resources.MeasureDoesntExistsInvalidOperationException);
}
}
}
private static bool MeasureExists_2012SP1(AMO.Database tabularDatabase,
string tableName,
string measureName)
{
using (AMO.MdxScript mdxScript = tabularDatabase.Cubes[0].MdxScripts[MdxScriptStringName])
{
Regex measureTypeExpression = new Regex(MeasurePattern, RegexOptions.CultureInvariant | RegexOptions.IgnoreCase | RegexOptions.Multiline);
foreach (AMO.Command modelCommand in mdxScript.Commands)
{
if (measureTypeExpression.IsMatch(modelCommand.Text) && (string.Compare(measureName, measureTypeExpression.Match(modelCommand.Text).Groups[MeasureStringName].Value, StringComparison.InvariantCultureIgnoreCase) == 0))
{
return(true);
}
}
}
return(false);
}
private static bool MeasureExists_2012RTM(AMO.Database tabularDatabase,
string tableName,
string measureName)
{
Regex commandTypeExpression = new Regex(MeasurePattern, RegexOptions.CultureInvariant | RegexOptions.IgnoreCase | RegexOptions.Multiline);
if (tabularDatabase.Cubes[0].MdxScripts[MdxScriptStringName].Commands.Count > 1)
{
foreach (Match match in commandTypeExpression.Matches(tabularDatabase.Cubes[0].MdxScripts[MdxScriptStringName].Commands[1].Text))
{
if (string.Compare(measureName, match.Groups[MeasureStringName].Value, StringComparison.InvariantCultureIgnoreCase) == 0)
{
return(true);
}
}
}
return(false);
}
private void cmbDimension_Enter(object sender, EventArgs e)
{
if (cmbDatabase.Text.Trim() != "" || txtServer.Text.Trim() != "")
{
try
{
if (VerifyServer())
{
db = srv.Databases.GetByName(cmbDatabase.Text);
cmbDimension.Items.Clear();
for (int i = 0; i < db.Dimensions.Count; i++)
if (db.Dimensions[i].IsParentChild) cmbDimension.Items.Add(db.Dimensions[i].Name);
cmbDimension.Sorted = true;
}
}
catch (Exception)
{
MessageBox.Show("Error accessing database server.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
