public string GetPreBuildWarning(Microsoft.AnalysisServices.BackEnd.DataModelingSandbox sandbox)
{
string strWarning = "Click OK for BIDS Helper to restore display folders. The Tabular Display Folders form will open. Then click Yes then OK.";
SSAS.TabularDisplayFoldersAnnotation annotation = GetAnnotation(sandbox);
Database db = sandbox.Database;
return(GetPreBuildWarningInternal(strWarning, annotation, db));
}
public static SSAS.TabularDisplayFoldersAnnotation GetAnnotation(Microsoft.AnalysisServices.BackEnd.DataModelingSandbox sandbox)
{
SSAS.TabularDisplayFoldersAnnotation annotation = new SSAS.TabularDisplayFoldersAnnotation();
if (sandbox.Database.Annotations.Contains(DISPLAY_FOLDER_ANNOTATION))
{
string xml = TabularHelpers.GetAnnotationXml(sandbox.Database, DISPLAY_FOLDER_ANNOTATION);
System.Xml.Serialization.XmlSerializer serializer = new System.Xml.Serialization.XmlSerializer(typeof(SSAS.TabularDisplayFoldersAnnotation));
annotation = (SSAS.TabularDisplayFoldersAnnotation)serializer.Deserialize(new System.IO.StringReader(xml));
}
return(annotation);
}
public static List <DimensionUsage> GetTabularDimensionUsage(Microsoft.AnalysisServices.BackEnd.DataModelingSandbox sandbox, bool bIsBusMatrix)
{
Cube c = sandbox.Cube;
List <CubeDimension> listCubeDimensions = new List <CubeDimension>();
List <DimensionUsage> dimUsage = new List <DimensionUsage>();
foreach (CubeDimension cd in c.Dimensions)
{
bool bFoundVisibleAttribute = false;
foreach (DimensionAttribute da in cd.Dimension.Attributes)
{
if (da.AttributeHierarchyVisible)
{
bFoundVisibleAttribute = true;
break;
}
}
if (bFoundVisibleAttribute)
{
listCubeDimensions.Add(cd);
}
bool bFoundVisibleMeasure = false;
foreach (Microsoft.AnalysisServices.BackEnd.DataModelingMeasure m in sandbox.Measures)
{
if (m.Table == cd.Dimension.Name && !m.IsPrivate)
{
bFoundVisibleMeasure = true;
break;
}
}
if (!bFoundVisibleMeasure && bIsBusMatrix)
{
continue;
}
MeasureGroup mg = c.MeasureGroups[cd.DimensionID];
List <DimensionUsage> tmp = RecurseTabularRelationships(cd.Dimension, mg, bIsBusMatrix);
dimUsage.AddRange(tmp);
if (bFoundVisibleAttribute && bFoundVisibleMeasure) //if this table had a measure but no dimension relationships (except to itself)
{
DimensionUsage du = new DimensionUsage("Fact", mg, cd, cd.Dimension);
dimUsage.Add(du);
}
else if (tmp.Count == 0 && bIsBusMatrix && bFoundVisibleMeasure) //if this table with a measure had no dimension relationships, add it as such...
{
DimensionUsage du = new DimensionUsage(string.Empty, mg, null, null);
dimUsage.Add(du);
}
}
//remove dimensions in relationships
foreach (DimensionUsage du in dimUsage)
{
for (int i = 0; i < listCubeDimensions.Count; i++)
{
if (listCubeDimensions[i].Name == du.DimensionName)
{
listCubeDimensions.RemoveAt(i);
i--;
}
}
}
//add any cube dimensions which aren't related to any measure groups
foreach (CubeDimension cd in listCubeDimensions)
{
DimensionUsage du = new DimensionUsage(string.Empty, null, cd, cd.Dimension);
dimUsage.Add(du);
}
return(dimUsage);
}
private void ExecSandbox(Microsoft.AnalysisServices.BackEnd.DataModelingSandbox sandboxParam)
{
var wrap = new DataModelingSandboxWrapper(sandboxParam);
ExecSandbox(wrap);
}
public void FixPreBuildWarning(Microsoft.AnalysisServices.BackEnd.DataModelingSandbox sandbox)
{
//TODO - fix this method
//open the actions form and let it fix actions
//ExecSandbox(sandbox);
}
private void ExecSandbox(DataModelingSandboxWrapper sandboxParam)
{
try
{
//#if DENALI || SQL2014
sandbox = sandboxParam.GetSandbox();
//#else
// sandbox = sandboxParam.GetSandboxAmo();
//#endif
if (sandbox == null)
{
throw new Exception("Can't get Sandbox!");
}
cube = sandboxParam.GetSandboxAmo().Cube;
if (cube == null)
{
throw new Exception("The workspace database cube doesn't exist.");
}
bool bRestoreDisplayFolders = false;
SSAS.TabularDisplayFoldersAnnotation annotationSaved = GetAnnotation(sandboxParam.GetSandbox());
if (GetPreBuildWarning(sandboxParam.GetSandbox()) != 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>();
Dictionary <string, string> restrictions = new Dictionary <string, string>();
restrictions.Add("CUBE_NAME", cube.Name);
DataSet datasetMeasures = sandboxParam.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)
{
#if DENALI || SQL2014
Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.AMOCode code;
#else
Microsoft.AnalysisServices.BackEnd.AMOCode code;
#endif
code = delegate
{
using (Microsoft.AnalysisServices.BackEnd.SandboxTransaction tran = sandboxParam.GetSandbox().CreateTransaction())
{
foreach (BIDSHelper.SSAS.TabularDisplayFolder folder in displayFolders)
{
folder.SaveDisplayFolder();
}
TabularHelpers.SaveXmlAnnotation(cube.Parent, DISPLAY_FOLDER_ANNOTATION, annotation);
TabularHelpers.EnsureDataSourceCredentials(sandboxParam.GetSandbox());
cube.Parent.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.GetSandbox().ExecuteEngineCode(DataModelingSandbox.OperationType.Update, DataModelingSandbox.OperationCancellability.AlwaysExecute, code, true);
#endif
}
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message + "\r\n" + ex.StackTrace, "BIDS Helper - Error");
}
}
