internal void ExecuteSyncDescriptions(Microsoft.AnalysisServices.BackEnd.DataModelingSandbox sandbox, IServiceProvider provider, string tableName)
{
try
{
Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.AMOCode code = delegate
{
int iDescriptionsSet;
Microsoft.AnalysisServices.BackEnd.SandboxTransactionProperties properties = new Microsoft.AnalysisServices.BackEnd.SandboxTransactionProperties();
properties.RecalcBehavior = Microsoft.AnalysisServices.BackEnd.TransactionRecalcBehavior.Default;
using (Microsoft.AnalysisServices.BackEnd.SandboxTransaction tran = sandbox.CreateTransaction(properties))
{
if (!TabularHelpers.EnsureDataSourceCredentials(sandbox))
{
MessageBox.Show("Cancelling Sync Descriptions because data source credentials were not entered.", "BIDS Helper Tabular Sync Descriptions - Cancelled!");
tran.RollbackAndContinue();
return;
}
Dimension d = sandbox.Database.Dimensions.GetByName(tableName);
iDescriptionsSet = SyncDescriptionsPlugin.SyncDescriptions(d, true, provider, true);
sandbox.Database.Update(UpdateOptions.ExpandFull);
tran.Commit();
}
MessageBox.Show("Set " + iDescriptionsSet + " descriptions successfully.", "BIDS Helper - Sync Descriptions");
};
sandbox.ExecuteAMOCode(Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.OperationType.Update, Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.OperationCancellability.AlwaysExecute, code, true);
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message + "\r\n" + ex.StackTrace, "BIDS Helper - Error");
}
}
internal void SetHideMemberIf(Microsoft.AnalysisServices.BackEnd.DataModelingSandbox sandbox, IEnumerable <Tuple <string, string, string> > hierarchyLevels, List <HideIfValue> newValues)
{
try
{
Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.AMOCode code = delegate
{
Microsoft.AnalysisServices.BackEnd.SandboxTransactionProperties properties = new Microsoft.AnalysisServices.BackEnd.SandboxTransactionProperties();
properties.RecalcBehavior = Microsoft.AnalysisServices.BackEnd.TransactionRecalcBehavior.AlwaysRecalc;
List <Dimension> dims = new List <Dimension>();
using (Microsoft.AnalysisServices.BackEnd.SandboxTransaction tran = sandbox.CreateTransaction(properties))
{
if (!TabularHelpers.EnsureDataSourceCredentials(sandbox))
{
MessageBox.Show("Cancelling apply of HideMemberIf because data source credentials were not entered.", "BIDS Helper Tabular HideMemberIf - Cancelled!");
tran.RollbackAndContinue();
return;
}
SSAS.TabularHideMemberIfAnnotation annotation = GetAnnotation(sandbox);
foreach (Tuple <string, string, string> tuple in hierarchyLevels)
{
Dimension d = sandbox.Database.Dimensions.GetByName(tuple.Item1);
if (!dims.Contains(d))
{
dims.Add(d);
}
Hierarchy h = d.Hierarchies.GetByName(tuple.Item2);
Level l = h.Levels.GetByName(tuple.Item3);
l.HideMemberIf = newValues[0];
newValues.RemoveAt(0);
annotation.Set(l);
}
TabularHelpers.SaveXmlAnnotation(sandbox.Database, HIDEMEMBERIF_ANNOTATION, annotation);
sandbox.Database.Update(UpdateOptions.ExpandFull);
//bug in AS2012 (still not working in RTM CU1) requires ProcessFull to successfully switch from HideMemberIf=Never to NoName
foreach (Dimension d in dims)
{
d.Process(ProcessType.ProcessFull);
}
tran.Commit();
}
};
sandbox.ExecuteAMOCode(Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.OperationType.Update, Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.OperationCancellability.AlwaysExecute, code, true);
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message + "\r\n" + ex.StackTrace, "BIDS Helper - Error");
}
}
private void ExecSandbox(Microsoft.AnalysisServices.BackEnd.DataModelingSandbox sandboxParam)
{
try
{
sandbox = sandboxParam;
if (sandbox == null)
{
throw new Exception("Can't get Sandbox!");
}
cube = sandbox.Cube;
if (cube == null)
{
throw new Exception("The workspace database cube doesn't exist.");
}
SSAS.TabularActionsEditorForm form = new SSAS.TabularActionsEditorForm(cube, sandbox.AdomdConnection);
if (form.ShowDialog() == DialogResult.OK)
{
Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.AMOCode code = delegate
{
using (Microsoft.AnalysisServices.BackEnd.SandboxTransaction tran = sandbox.CreateTransaction())
{
foreach (Perspective p in cube.Perspectives)
{
p.Actions.Clear();
}
cube.Actions.Clear();
foreach (Microsoft.AnalysisServices.Action action in form.Actions())
{
cube.Actions.Add(action);
foreach (Perspective p in cube.Perspectives)
{
if (form.ActionInPerspective(action.ID, p.ID))
{
p.Actions.Add(action.ID);
}
}
}
TabularHelpers.SaveXmlAnnotation(cube, SSAS.TabularActionsEditorForm.ACTION_ANNOTATION, form.Annotation);
cube.Update(UpdateOptions.ExpandFull);
tran.Commit();
}
};
sandbox.ExecuteAMOCode(Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.OperationType.Update, Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.OperationCancellability.AlwaysExecute, code, true);
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message + "\r\n" + ex.StackTrace, "BIDS Helper - Error");
}
}
private void AlterDatabase(List <SSAS.TabularTranslatedItem> translatedItems)
{
Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.AMOCode code = delegate
{
using (Microsoft.AnalysisServices.BackEnd.SandboxTransaction tran = sandbox.CreateTransaction())
{
SSAS.TabularTranslationsAnnotation annotation = new SSAS.TabularTranslationsAnnotation();
List <SSAS.TabularTranslationObjectAnnotation> annotationsList = new List <SSAS.TabularTranslationObjectAnnotation>();
foreach (SSAS.TabularTranslatedItem item in translatedItems)
{
item.Save(annotationsList);
}
annotation.TabularTranslations = annotationsList.ToArray();
TabularHelpers.SaveXmlAnnotation(cube.Parent, TRANSLATIONS_ANNOTATION, annotation);
TabularHelpers.EnsureDataSourceCredentials(sandbox);
cube.Parent.Update(UpdateOptions.ExpandFull);
tran.Commit();
}
};
sandbox.ExecuteAMOCode(Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.OperationType.Update, Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.OperationCancellability.AlwaysExecute, code, true);
}
internal void ExecuteSyncDescriptions(Microsoft.AnalysisServices.BackEnd.DataModelingSandbox sandbox, IServiceProvider provider, string tableName)
{
try
{
#if DENALI || SQL2014
var db = sandbox.Database;
Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.AMOCode code;
#else
Database db = null;
if (!sandbox.IsTabularMetadata)
{
db = ((Microsoft.AnalysisServices.BackEnd.DataModelingSandboxAmo)sandbox.Impl).Database;
}
else
{
db = null;
}
Microsoft.AnalysisServices.BackEnd.AMOCode code;
#endif
code = delegate
{
int iDescriptionsSet;
Microsoft.AnalysisServices.BackEnd.SandboxTransactionProperties properties = new Microsoft.AnalysisServices.BackEnd.SandboxTransactionProperties();
properties.RecalcBehavior = Microsoft.AnalysisServices.BackEnd.TransactionRecalcBehavior.Default;
using (Microsoft.AnalysisServices.BackEnd.SandboxTransaction tran = sandbox.CreateTransaction(properties))
{
if (!TabularHelpers.EnsureDataSourceCredentials(sandbox))
{
MessageBox.Show("Cancelling Sync Descriptions because data source credentials were not entered.", "BIDS Helper Tabular Sync Descriptions - Cancelled!");
tran.RollbackAndContinue();
return;
}
#if !(DENALI || SQL2014)
Microsoft.AnalysisServices.BackEnd.DataModelingTable table = sandbox.Tables[tableName];
if (table.IsStructuredDataSource)
{
MessageBox.Show("BI Developer Extensions does not yet support modern (Power Query) data sources.", "BI Developer Extensions");
return;
}
iDescriptionsSet = SyncDescriptionsPlugin.SyncDescriptions(table, true);
if (iDescriptionsSet > 0)
{
table.UpdateNowOrLater();
}
#else
Dimension d = db.Dimensions.GetByName(tableName);
iDescriptionsSet = SyncDescriptionsPlugin.SyncDescriptions(d, true, provider, true);
if (iDescriptionsSet > 0)
{
db.Update(UpdateOptions.ExpandFull);
}
#endif
tran.GetType().InvokeMember("Commit", System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.InvokeMethod | System.Reflection.BindingFlags.Public, null, tran, null); //The .Commit() function used to return a list of strings, but in the latest set of code it is a void method which leads to "method not found" errors
//tran.Commit();
}
MessageBox.Show("Set " + iDescriptionsSet + " descriptions successfully.", "BIDS Helper - Sync Descriptions");
};
#if DENALI || SQL2014
sandbox.ExecuteAMOCode(Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.OperationType.Update, Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.OperationCancellability.AlwaysExecute, code, true);
#else
sandbox.ExecuteEngineCode(Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.OperationType.Update, Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.OperationCancellability.AlwaysExecute, code, true);
#endif
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message + "\r\n" + ex.StackTrace, "BIDS Helper - Error");
}
}
private void ExecSandbox(Microsoft.AnalysisServices.BackEnd.DataModelingSandbox sandboxParam)
{
try
{
sandbox = sandboxParam;
if (sandbox == null) throw new Exception("Can't get Sandbox!");
cube = sandbox.Cube;
if (cube == null) throw new Exception("The workspace database cube doesn't exist.");
bool bRestoreDisplayFolders = false;
SSAS.TabularDisplayFoldersAnnotation annotationSaved = GetAnnotation(sandbox);
if (GetPreBuildWarning(sandbox) != null)
{
if (MessageBox.Show("Some display folders have been blanked out by other editing operations. Restoring display folders may be possible except when a measures or columns have been renamed.\r\n\r\nWould you like BIDS Helper to attempt restore the display folders now?", "BIDS Helper Tabular Display Folders", MessageBoxButtons.YesNo) == System.Windows.Forms.DialogResult.Yes)
{
bRestoreDisplayFolders = true;
}
}
SSAS.TabularDisplayFolderWindow form = new SSAS.TabularDisplayFolderWindow();
List<BIDSHelper.SSAS.TabularDisplayFolder> displayFolders = new List<BIDSHelper.SSAS.TabularDisplayFolder>();
Microsoft.AnalysisServices.AdomdClient.AdomdRestrictionCollection restrictions = new Microsoft.AnalysisServices.AdomdClient.AdomdRestrictionCollection();
restrictions.Add(new Microsoft.AnalysisServices.AdomdClient.AdomdRestriction("CUBE_NAME", cube.Name));
DataSet datasetMeasures = sandbox.AdomdConnection.GetSchemaDataSet("MDSCHEMA_MEASURES", restrictions);
Database db = cube.Parent;
foreach (Dimension d in db.Dimensions)
{
foreach (DimensionAttribute da in d.Attributes)
{
if (da.Type != AttributeType.RowNumber && da.AttributeHierarchyVisible)
{
if (bRestoreDisplayFolders && string.IsNullOrEmpty(da.AttributeHierarchyDisplayFolder))
{
SSAS.TabularDisplayFolderAnnotation a = annotationSaved.Find(da);
if (a != null) da.AttributeHierarchyDisplayFolder = a.DisplayFolder;
}
displayFolders.Add(new BIDSHelper.SSAS.TabularDisplayFolder(d.Name, da));
}
}
foreach (Hierarchy h in d.Hierarchies)
{
if (bRestoreDisplayFolders && string.IsNullOrEmpty(h.DisplayFolder))
{
SSAS.TabularDisplayFolderAnnotation a = annotationSaved.Find(h);
if (a != null) h.DisplayFolder = a.DisplayFolder;
}
displayFolders.Add(new BIDSHelper.SSAS.TabularDisplayFolder(d.Name, h));
}
}
foreach (Cube c in db.Cubes)
{
foreach (MdxScript mdx in c.MdxScripts)
{
foreach (CalculationProperty calc in mdx.CalculationProperties)
{
if (calc.Visible && !calc.CalculationReference.StartsWith("KPIs.")) //TODO: display folder for KPIs have to be set inline in MDX script... do this in the future
{
string sTableName = null;
foreach (DataRow r in datasetMeasures.Tables[0].Rows)
{
if (Convert.ToString(r["MEASURE_UNIQUE_NAME"]) == "[Measures]." + calc.CalculationReference)
{
sTableName = Convert.ToString(r["MEASUREGROUP_NAME"]);
break;
}
}
if (bRestoreDisplayFolders && string.IsNullOrEmpty(calc.DisplayFolder))
{
SSAS.TabularDisplayFolderAnnotation a = annotationSaved.Find(calc);
if (a != null) calc.DisplayFolder = a.DisplayFolder;
}
displayFolders.Add(new BIDSHelper.SSAS.TabularDisplayFolder(sTableName, calc));
}
}
}
}
displayFolders.Sort();
form.dataGrid1.ItemsSource = displayFolders;
if (form.ShowDialog() == true)
{
//count dirty changes and create annotation
SSAS.TabularDisplayFoldersAnnotation annotation = new SSAS.TabularDisplayFoldersAnnotation();
List<SSAS.TabularDisplayFolderAnnotation> folderAnnotationsList = new List<SSAS.TabularDisplayFolderAnnotation>();
int iNumberOfChanges = 0;
foreach (BIDSHelper.SSAS.TabularDisplayFolder folder in displayFolders)
{
if (folder.Dirty)
{
iNumberOfChanges++;
}
if (!string.IsNullOrEmpty(folder.DisplayFolder))
{
SSAS.TabularDisplayFolderAnnotation folderAnnotation = new SSAS.TabularDisplayFolderAnnotation();
folderAnnotation.TableID = cube.Parent.Dimensions.GetByName(folder.Table).ID;
folderAnnotation.ObjectID = folder.ObjectID;
folderAnnotation.ObjectType = folder.ObjectType;
folderAnnotation.DisplayFolder = folder.DisplayFolder;
folderAnnotationsList.Add(folderAnnotation);
}
}
annotation.TabularDisplayFolders = folderAnnotationsList.ToArray();
if (iNumberOfChanges > 0 || bRestoreDisplayFolders)
{
Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.AMOCode code = delegate
{
using (Microsoft.AnalysisServices.BackEnd.SandboxTransaction tran = sandbox.CreateTransaction())
{
foreach (BIDSHelper.SSAS.TabularDisplayFolder folder in displayFolders)
{
folder.SaveDisplayFolder();
}
TabularHelpers.SaveXmlAnnotation(cube.Parent, DISPLAY_FOLDER_ANNOTATION, annotation);
TabularHelpers.EnsureDataSourceCredentials(sandbox);
cube.Parent.Update(UpdateOptions.ExpandFull);
tran.Commit();
}
};
sandbox.ExecuteAMOCode(Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.OperationType.Update, Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.OperationCancellability.AlwaysExecute, code, true);
}
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message + "\r\n" + ex.StackTrace, "BIDS Helper - Error");
}
}
private void ExecSandbox(Microsoft.AnalysisServices.BackEnd.DataModelingSandbox sandboxParam)
{
try
{
sandbox = sandboxParam;
if (sandbox == null) throw new Exception("Can't get Sandbox!");
cube = sandbox.Cube;
if (cube == null) throw new Exception("The workspace database cube doesn't exist.");
SSAS.TabularActionsEditorForm form = new SSAS.TabularActionsEditorForm(cube, sandbox.AdomdConnection);
if (form.ShowDialog() == DialogResult.OK)
{
Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.AMOCode code = delegate
{
using (Microsoft.AnalysisServices.BackEnd.SandboxTransaction tran = sandbox.CreateTransaction())
{
foreach (Perspective p in cube.Perspectives)
{
p.Actions.Clear();
}
cube.Actions.Clear();
foreach (Microsoft.AnalysisServices.Action action in form.Actions())
{
cube.Actions.Add(action);
foreach (Perspective p in cube.Perspectives)
{
if (form.ActionInPerspective(action.ID, p.ID))
{
p.Actions.Add(action.ID);
}
}
}
TabularHelpers.SaveXmlAnnotation(cube, SSAS.TabularActionsEditorForm.ACTION_ANNOTATION, form.Annotation);
cube.Update(UpdateOptions.ExpandFull);
tran.Commit();
}
};
sandbox.ExecuteAMOCode(Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.OperationType.Update, Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.OperationCancellability.AlwaysExecute, code, true);
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message + "\r\n" + ex.StackTrace, "BIDS Helper - Error");
}
}
private void ExecSandbox(Microsoft.AnalysisServices.BackEnd.DataModelingSandbox sandboxParam)
{
try
{
sandbox = sandboxParam;
if (sandbox == null)
{
throw new Exception("Can't get Sandbox!");
}
cube = sandbox.Cube;
if (cube == null)
{
throw new Exception("The workspace database cube doesn't exist.");
}
bool bRestoreDisplayFolders = false;
SSAS.TabularDisplayFoldersAnnotation annotationSaved = GetAnnotation(sandbox);
if (GetPreBuildWarning(sandbox) != null)
{
if (MessageBox.Show("Some display folders have been blanked out by other editing operations. Restoring display folders may be possible except when a measures or columns have been renamed.\r\n\r\nWould you like BIDS Helper to attempt restore the display folders now?", "BIDS Helper Tabular Display Folders", MessageBoxButtons.YesNo) == System.Windows.Forms.DialogResult.Yes)
{
bRestoreDisplayFolders = true;
}
}
SSAS.TabularDisplayFolderWindow form = new SSAS.TabularDisplayFolderWindow();
List <BIDSHelper.SSAS.TabularDisplayFolder> displayFolders = new List <BIDSHelper.SSAS.TabularDisplayFolder>();
Microsoft.AnalysisServices.AdomdClient.AdomdRestrictionCollection restrictions = new Microsoft.AnalysisServices.AdomdClient.AdomdRestrictionCollection();
restrictions.Add(new Microsoft.AnalysisServices.AdomdClient.AdomdRestriction("CUBE_NAME", cube.Name));
DataSet datasetMeasures = sandbox.AdomdConnection.GetSchemaDataSet("MDSCHEMA_MEASURES", restrictions);
Database db = cube.Parent;
foreach (Dimension d in db.Dimensions)
{
foreach (DimensionAttribute da in d.Attributes)
{
if (da.Type != AttributeType.RowNumber && da.AttributeHierarchyVisible)
{
if (bRestoreDisplayFolders && string.IsNullOrEmpty(da.AttributeHierarchyDisplayFolder))
{
SSAS.TabularDisplayFolderAnnotation a = annotationSaved.Find(da);
if (a != null)
{
da.AttributeHierarchyDisplayFolder = a.DisplayFolder;
}
}
displayFolders.Add(new BIDSHelper.SSAS.TabularDisplayFolder(d.Name, da));
}
}
foreach (Hierarchy h in d.Hierarchies)
{
if (bRestoreDisplayFolders && string.IsNullOrEmpty(h.DisplayFolder))
{
SSAS.TabularDisplayFolderAnnotation a = annotationSaved.Find(h);
if (a != null)
{
h.DisplayFolder = a.DisplayFolder;
}
}
displayFolders.Add(new BIDSHelper.SSAS.TabularDisplayFolder(d.Name, h));
}
}
foreach (Cube c in db.Cubes)
{
foreach (MdxScript mdx in c.MdxScripts)
{
foreach (CalculationProperty calc in mdx.CalculationProperties)
{
if (calc.Visible && !calc.CalculationReference.StartsWith("KPIs.")) //TODO: display folder for KPIs have to be set inline in MDX script... do this in the future
{
string sTableName = null;
foreach (DataRow r in datasetMeasures.Tables[0].Rows)
{
if (Convert.ToString(r["MEASURE_UNIQUE_NAME"]) == "[Measures]." + calc.CalculationReference)
{
sTableName = Convert.ToString(r["MEASUREGROUP_NAME"]);
break;
}
}
if (bRestoreDisplayFolders && string.IsNullOrEmpty(calc.DisplayFolder))
{
SSAS.TabularDisplayFolderAnnotation a = annotationSaved.Find(calc);
if (a != null)
{
calc.DisplayFolder = a.DisplayFolder;
}
}
displayFolders.Add(new BIDSHelper.SSAS.TabularDisplayFolder(sTableName, calc));
}
}
}
}
displayFolders.Sort();
form.dataGrid1.ItemsSource = displayFolders;
if (form.ShowDialog() == true)
{
//count dirty changes and create annotation
SSAS.TabularDisplayFoldersAnnotation annotation = new SSAS.TabularDisplayFoldersAnnotation();
List <SSAS.TabularDisplayFolderAnnotation> folderAnnotationsList = new List <SSAS.TabularDisplayFolderAnnotation>();
int iNumberOfChanges = 0;
foreach (BIDSHelper.SSAS.TabularDisplayFolder folder in displayFolders)
{
if (folder.Dirty)
{
iNumberOfChanges++;
}
if (!string.IsNullOrEmpty(folder.DisplayFolder))
{
SSAS.TabularDisplayFolderAnnotation folderAnnotation = new SSAS.TabularDisplayFolderAnnotation();
folderAnnotation.TableID = cube.Parent.Dimensions.GetByName(folder.Table).ID;
folderAnnotation.ObjectID = folder.ObjectID;
folderAnnotation.ObjectType = folder.ObjectType;
folderAnnotation.DisplayFolder = folder.DisplayFolder;
folderAnnotationsList.Add(folderAnnotation);
}
}
annotation.TabularDisplayFolders = folderAnnotationsList.ToArray();
if (iNumberOfChanges > 0 || bRestoreDisplayFolders)
{
Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.AMOCode code = delegate
{
using (Microsoft.AnalysisServices.BackEnd.SandboxTransaction tran = sandbox.CreateTransaction())
{
foreach (BIDSHelper.SSAS.TabularDisplayFolder folder in displayFolders)
{
folder.SaveDisplayFolder();
}
TabularHelpers.SaveXmlAnnotation(cube.Parent, DISPLAY_FOLDER_ANNOTATION, annotation);
TabularHelpers.EnsureDataSourceCredentials(sandbox);
cube.Parent.Update(UpdateOptions.ExpandFull);
tran.Commit();
}
};
sandbox.ExecuteAMOCode(Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.OperationType.Update, Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.OperationCancellability.AlwaysExecute, code, true);
}
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message + "\r\n" + ex.StackTrace, "BIDS Helper - Error");
}
}