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 SetHideMemberIf(Microsoft.AnalysisServices.BackEnd.DataModelingSandbox sandboxParam, IEnumerable <Tuple <string, string, string> > hierarchyLevels, List <HideIfValue> newValues) { try { #if DENALI || SQL2014 Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.AMOCode code; var sandbox = sandboxParam; #else Microsoft.AnalysisServices.BackEnd.AMOCode code; var sandbox = (Microsoft.AnalysisServices.BackEnd.DataModelingSandboxAmo)sandboxParam.Impl; #endif 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 = sandboxParam.CreateTransaction(properties)) { if (!TabularHelpers.EnsureDataSourceCredentials(sandboxParam)) { 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(sandboxParam); 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.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(); } }; #if DENALI || SQL2014 sandbox.ExecuteAMOCode(Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.OperationType.Update, Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.OperationCancellability.AlwaysExecute, code, true); #else sandboxParam.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 { #if DENALI || SQL2014 var sb = sandboxParam; var conn = sandboxParam.AdomdConnection; #elif SQL2017 var sb = (Microsoft.AnalysisServices.BackEnd.DataModelingSandboxAmo)sandboxParam.Impl; var localConn = sandboxParam.AdomdConnection; var conn = new localAdomdClient.Microsoft.AnalysisServices.AdomdClient.AdomdConnection(localConn.ConnectionString); #else var sb = (Microsoft.AnalysisServices.BackEnd.DataModelingSandboxAmo)sandboxParam.Impl; var localConn = sandboxParam.AdomdConnection; var conn = new Microsoft.AnalysisServices.AdomdClient.AdomdConnection(localConn.ConnectionString); #endif if (sb == null) { throw new Exception("Can't get Sandbox!"); } cube = sb.Cube; if (cube == null) { throw new Exception("The workspace database cube doesn't exist."); } SSAS.TabularActionsEditorForm form = new SSAS.TabularActionsEditorForm(cube, conn); if (form.ShowDialog() == DialogResult.OK) { #if DENALI || SQL2014 Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.AMOCode code; #else Microsoft.AnalysisServices.BackEnd.AMOCode code; #endif 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.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(); } }; #if DENALI || SQL2014 sandbox.ExecuteAMOCode(Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.OperationType.Update, Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.OperationCancellability.AlwaysExecute, code, true); #else sandboxParam.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"); } }