/// <summary>
/// Generate single dimension.
/// </summary>
/// <param name="objDatabase">DB instance.</param>
/// <param name="objDataSourceView">DataSourceView instance.</param>
/// <param name="strTableName">Table name.</param>
/// <param name="strTableKeyName">Table key.</param>
/// <returns>Dimension instance.</returns>
private static object GenerateDimension(Database objDatabase, DataSourceView objDataSourceView, string strTableName, string strTableKeyName)
{
try
{
Dimension objDimension = new Dimension();
//Add Dimension to the Database
objDimension = objDatabase.Dimensions.Add(strTableName);
objDimension.Source = new DataSourceViewBinding(objDataSourceView.ID);
DimensionAttributeCollection objDimensionAttributesColl = objDimension.Attributes;
//Add Dimension Attributes
DimensionAttribute objAttribute = objDimensionAttributesColl.Add(strTableKeyName);
//Set Attribute usage and source
objAttribute.Usage = AttributeUsage.Key;
objAttribute.KeyColumns.Add(strTableName, strTableKeyName, OleDbType.Integer);
objDimension.Update();
return(objDimension);
}
catch (Exception ex)
{
Console.WriteLine("Error in Creating the Dimension, Attribute, Hierarchy, and MemberProperty Objects - GenerateDimension. Error Message -> " + ex.Message);
return(null);
}
}
/// <summary>
/// Add relation ship to attribute
/// </summary>
/// <param name="dim"></param>
/// <param name="basedAttributeID"></param>
/// <param name="relatedAttributeID"></param>
/// <param name="attributeRelationShipType"></param>
/// <param name="relationShipName"></param>
internal static void ADD_ATTRIBUTE_RELATIONSHIP(
DB_SQLHELPER_BASE sqlHelper,
Dimension dim,
String basedAttributeID,
String relatedAttributeID,
RelationshipType attributeRelationShipType = RelationshipType.Flexible,
String relationShipName = null)
{
DimensionAttribute attr = dim.Attributes.Find(basedAttributeID);
DimensionAttribute relatedAttr = dim.Attributes.Find(relatedAttributeID);
if (relationShipName == null)
{
relationShipName = relatedAttr.Name;
}
AttributeRelationship relationship = new AttributeRelationship();
relationship.Attribute = attr;
relationship.Name = relationShipName;
relationship.AttributeID = relatedAttributeID;
if (attributeRelationShipType != null)
{
relationship.RelationshipType = attributeRelationShipType;
}
else
{
sqlHelper.ADD_MESSAGE_LOG(
String.Format("A None RelationShipType is passed between [{0} and [{1}]", basedAttributeID, relatedAttributeID),
MESSAGE_TYPE.ATTRIBUTE_RELATIONSHIP, MESSAGE_RESULT_TYPE.Warning);
}
if (!attr.AttributeRelationships.Contains(relatedAttributeID))
{
attr.AttributeRelationships.Add(relationship);
}
}
private static bool ContainsSubsetOfKeys(DimensionAttribute a1, DimensionAttribute a2)
{
//check that every a2.KeyColumns can be found in a1.KeyColumns
if (a2.KeyColumns.Count == 0)
{
return(false);
}
foreach (DataItem di2 in a2.KeyColumns)
{
bool bFoundKey = false;
foreach (DataItem di1 in a1.KeyColumns)
{
if (CompareDataItems(di1, di2))
{
bFoundKey = true;
break;
}
}
if (!bFoundKey)
{
return(false);
}
}
return(true);
}
public static void CheckDimension(Resources d365Client)
{
DimensionAttribute objdim = new DimensionAttribute();
objdim.DimensionName = "ksc_Campus";
objdim.DimensionValueMask = "Boys Campus";
}
/// <summary>
/// Add Attribute To Dimension
/// </summary>
/// <param name="cubeDSV"></param>
/// <param name="dim"></param>
/// <param name="tableID"></param>
/// <param name="colName"></param>
/// <param name="attribID"></param>
/// <param name="attribName"></param>
/// <param name="type"></param>
/// <param name="usage"></param>
/// <param name="nameColumn"></param>
/// <param name="visible"></param>
/// <param name="AttHierEnabled"></param>
/// <param name="orderby"></param>
/// <param name="attDisplayFolder"></param>
/// <param name="orderByAttName"></param>
/// <param name="attType"></param>
/// <param name="valueColumn"></param>
/// <param name="valueColtype"></param>
internal static void ADD_ATTRIBUTE_TO_DIMENSION(
DB_SQLHELPER_BASE sqlHelper,
DataSourceView cubeDSV,
Dimension dim,
String tableID,
String colName,
String attribID,
String attribName,
System.Data.OleDb.OleDbType type,
AttributeUsage usage,
String nameColumn,
bool visible = true,
bool AttHierEnabled = true,
OrderBy orderby = OrderBy.Name,
String attDisplayFolder = "",
String orderByAttName = null,
String attType = "Regular",
String valueColumn = null,
System.Data.OleDb.OleDbType valueColtype = System.Data.OleDb.OleDbType.Integer)
{
DimensionAttribute attr = dim.Attributes.FindByName(attribName);
if (attr == null)
{
attr = dim.Attributes.Add(attribID);
attr.Name = attribName;
attr.Usage = usage;
attr.Type = AttributeType.Regular;
attr.AttributeHierarchyEnabled = AttHierEnabled;
DataItem dataItem = CREATE_COLUMN_BINDING_DATA_ITEM(sqlHelper, cubeDSV, tableID, colName, type);
attr.KeyColumns.Add(dataItem);
attr.KeyColumns[0].DataType = type;
attr.AttributeHierarchyVisible = visible;
attr.OrderBy = orderby;
if (nameColumn != colName && nameColumn != "")
{
DataItem nameColDataItem = CREATE_COLUMN_BINDING_DATA_ITEM(sqlHelper, cubeDSV, tableID, nameColumn, System.Data.OleDb.OleDbType.WChar);
attr.NameColumn = nameColDataItem;
}
if (attDisplayFolder != null && attDisplayFolder != "")
{
attr.AttributeHierarchyDisplayFolder = attDisplayFolder;
}
if (orderByAttName != null && orderByAttName != "")
{
attr.OrderByAttributeID = orderByAttName;
}
if (valueColumn != null && valueColumn != "")
{
DataItem valueColDataItem = CREATE_COLUMN_BINDING_DATA_ITEM(sqlHelper, cubeDSV, tableID, valueColumn, valueColtype);
attr.ValueColumn = valueColDataItem;
}
sqlHelper.ADD_MESSAGE_LOG(
String.Format("Added attribute [{0}] to dimension [{1}]", attribName, dim.Name),
MESSAGE_TYPE.ATTRIBUTE, MESSAGE_RESULT_TYPE.Succeed);
}
}
private static string GetQueryToValidateKeyUniqueness(DimensionAttribute da)
{
if (da.KeyColumns.Count == 0 && (da.NameColumn == null || da.ValueColumn == null))
{
return(null); // no need
}
if (da.NameColumn == null && da.ValueColumn == null)
{
return(null); // no need
}
if (da.KeyColumns.Count == 1 && CompareDataItems(da.KeyColumns[0], da.NameColumn) && da.ValueColumn == null)
{
return(null); // no need
}
if (da.KeyColumns.Count == 1 && CompareDataItems(da.KeyColumns[0], da.ValueColumn) && da.NameColumn == null)
{
return(null); // no need
}
DataSourceView oDSV = da.Parent.DataSourceView;
DataItem[] keyCols;
DataItem[] nameAndValueCols;
if (da.KeyColumns.Count == 0)
{
keyCols = new DataItem[] { da.NameColumn };
nameAndValueCols = new DataItem[] { da.ValueColumn }; //value column will be there because of previous check
}
else if (da.NameColumn == null)
{
if (da.KeyColumns.Count > 1)
{
throw new Exception("If no name column is defined, you may not have more than one key column.");
}
keyCols = new DataItem[] { da.KeyColumns[0] };
//use value as "name" column when checking uniqueness
nameAndValueCols = new DataItem[] { da.ValueColumn }; //value column will be there because of previous check
}
else
{
keyCols = new DataItem[da.KeyColumns.Count];
for (int i = 0; i < da.KeyColumns.Count; i++)
{
keyCols[i] = da.KeyColumns[i];
}
if (da.ValueColumn == null)
{
nameAndValueCols = new DataItem[] { da.NameColumn };
}
else
{
nameAndValueCols = new DataItem[] { da.NameColumn, da.ValueColumn };
}
}
return(GetQueryToValidateUniqueness(oDSV, keyCols, nameAndValueCols));
}
public override void OnGUI(Rect position, SerializedProperty property, GUIContent label)
{
DimensionAttribute rangeAttribute = (DimensionAttribute)base.attribute;
if (property.propertyType == SerializedPropertyType.Integer)
{
label.text = property.name + ": " + property.intValue * 16;
EditorGUI.IntSlider(position, property, 1, 20, label);
}
}
void Expander_Click(object sender, HtmlElementEventArgs e)
{
try
{
HtmlElement el = (HtmlElement)sender;
if (!string.IsNullOrEmpty(el.GetAttribute("ErrorCnt")))
{
HtmlElement error = el.Document.GetElementById("error" + el.GetAttribute("ErrorCnt"));
if (error != null)
{
if (error.Style.ToLower().StartsWith("display") ||
error.Style.ToLower().Contains("display: none") ||
error.Style.ToLower().Contains("display:none"))
{
error.Style = "font-family:Arial;font-size:10pt";
}
else
{
error.Style = "display:none;font-family:Arial;font-size:10pt";
}
}
}
else
{
string sAttributeID = el.GetAttribute("Attribute");
string sRelatedAttributeID = el.GetAttribute("RelatedAttribute");
DimensionAttribute parent = this.oLastDimension.Attributes[sAttributeID];
DimensionAttribute child = this.oLastDimension.Attributes[sRelatedAttributeID];
if (MessageBox.Show("Are you sure you want to make [" + child.Name + "] related to [" + parent.Name + "]?", "BIDS Helper - Fix Obvious Attribute Relationship Oversight?", MessageBoxButtons.YesNo) != DialogResult.Yes)
{
return;
}
foreach (DimensionAttribute da in this.oLastDimension.Attributes)
{
if (da.AttributeRelationships.Contains(child.ID))
{
da.AttributeRelationships.Remove(child.ID);
}
}
parent.AttributeRelationships.Add(child.ID);
//mark dimension as dirty
this.changesvc.OnComponentChanging(this.oLastDimension, null);
this.changesvc.OnComponentChanged(this.oLastDimension, null, null, null);
MessageBox.Show("Finished making [" + child.Name + "] related to [" + parent.Name + "].\r\n\r\nPlease rerun Dimension Health Check to see if any other warnings were resolved.", "BIDS Helper - Fix Obvious Attribute Relationship Oversight");
}
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex.Message);
}
}
private static bool AggContainsChild(AggregationDimension aggDim, DimensionAttribute attr)
{
foreach (AggregationAttribute aggAttr in aggDim.Attributes)
{
if (IsParentOf(attr, aggAttr.Attribute))
{
return(true);
}
}
return(false);
}
private List<string> AnyAttsOrHiersDependOnAtt(DimensionAttribute attr)
{
List<string> dependants = new List<string>();
foreach (Hierarchy hier in Program.ASFlattener.dim.Hierarchies)
foreach (Level lvl in hier.Levels)
if (lvl.SourceAttribute.Name == attr.Name)
dependants.Add(hier.Name);
for (int i = 0; i < lbAttributes.Items.Count; i++)
if (ASPCDimNaturalizer.IsAttributeRelated((Program.ASFlattener.dim.Attributes.FindByName((string)lbAttributes.Items[i])), attr))
dependants.Add((string)lbAttributes.Items[i]);
return dependants;
}
private static bool AggContainsParent(AggregationDimension aggDim, DimensionAttribute attr)
{
foreach (AttributeRelationship rel in attr.AttributeRelationships)
{
if (aggDim.Attributes.Find(rel.AttributeID) != null)
{
return(true);
}
else if (AggContainsParent(aggDim, rel.Attribute))
{
return(true);
}
}
return(false);
}
private bool IsParentOf(DimensionAttribute parent, DimensionAttribute child)
{
foreach (AttributeRelationship rel in child.AttributeRelationships)
{
if (rel.AttributeID == parent.ID)
{
return(true);
}
else if (IsParentOf(parent, rel.Attribute))
{
return(true);
}
}
return(false);
}
public static bool IsAtOrAboveGranularity(DimensionAttribute attribute, MeasureGroupDimension mgDim)
{
if (mgDim is RegularMeasureGroupDimension)
{
MeasureGroupAttribute granularity = GetGranularityAttribute((RegularMeasureGroupDimension)mgDim);
if (granularity.AttributeID == attribute.ID)
{
return(true);
}
return(IsParentOf(attribute, granularity.Attribute));
}
else if (mgDim is ManyToManyMeasureGroupDimension)
{
//this depends on the granularity of the m2m dimension as it appears on the intermediate measure group
ManyToManyMeasureGroupDimension m2mDim = (ManyToManyMeasureGroupDimension)mgDim;
return(IsAtOrAboveGranularity(attribute, m2mDim.MeasureGroup.Dimensions.Find(mgDim.CubeDimensionID)));
}
else
{
return(true);
}
}
private List <string> AnyAttsOrHiersDependOnAtt(DimensionAttribute attr)
{
List <string> dependants = new List <string>();
foreach (Hierarchy hier in Program.ASFlattener.dim.Hierarchies)
{
foreach (Level lvl in hier.Levels)
{
if (lvl.SourceAttribute.Name == attr.Name)
{
dependants.Add(hier.Name);
}
}
}
for (int i = 0; i < lbAttributes.Items.Count; i++)
{
if (ASPCDimNaturalizer.IsAttributeRelated((Program.ASFlattener.dim.Attributes.FindByName((string)lbAttributes.Items[i])), attr))
{
dependants.Add((string)lbAttributes.Items[i]);
}
}
return(dependants);
}
private void GenerateDimension(CbDimension_M aCbDimension)
{
try
{
aCbDimension.DimensionObj = new Dimension();
//Add Dimension to the Database
aCbDimension.DimensionObj = _CbDatabase.Dimensions.Add(aCbDimension.Name);
aCbDimension.DimensionObj.Source = new DataSourceViewBinding(_CbDataSourceView.ID);
aCbDimension.DimensionObj.UnknownMember = UnknownMemberBehavior.Visible;
aCbDimension.DimensionObj.StorageMode = DimensionStorageMode.Molap;
DimensionAttributeCollection objDimensionAttributesColl = aCbDimension.DimensionObj.Attributes;
//Add attributes to dimension
foreach (CbDimensionAttribute_M cbDimensionAttributeM in aCbDimension.AttributeList.Where(da => da.Include))
{
DimensionAttribute objAttribute = objDimensionAttributesColl.Add(cbDimensionAttributeM.Name);
objAttribute.Usage = cbDimensionAttributeM.Usage;
objAttribute.AttributeHierarchyVisible = cbDimensionAttributeM.AttributeHierarchyVisible;
objAttribute.KeyColumns.Add(
CreateDataItem(aCbDimension.ReferenceToDsDwTableMap.DwTableName,
cbDimensionAttributeM.ReferenceToDsDwColumnMap.DwColumn.Name,
NullProcessing.UnknownMember,
cbDimensionAttributeM.ReferenceToDsDwColumnMap.DwColumn.DataType
));
}
//Save dimension to database
aCbDimension.DimensionObj.Update();
}
catch (Exception ex)
{
AppendLogLine("Error in creating a dimension. Error Message -> " + ex.Message);
throw;
}
}
/// <summary>
/// Populate this ParameterCollection with parameters matching the
/// properties of a source object.
/// </summary>
/// <param name="source">The object from which to extract the parameters</param>
/// <param name="namePrefix">A prefix to be prepended to the start of the generated property names</param>
public void GenerateFromProperties(object source, string namePrefix = "")
{
var type = source.GetType();
var props = type.GetProperties();
foreach (var prop in props)
{
var pType = typeof(Parameter <>);
var gType = pType.MakeGenericType(new Type[] { prop.PropertyType });
Parameter parameter = (Parameter)Activator.CreateInstance(
gType,
new object[] { namePrefix + prop.Name });
object value = prop.GetValue(source);
parameter.SetValue(value);
var dimAtt = DimensionAttribute.ExtractFrom(prop);
if (dimAtt != null)
{
parameter.Units = dimAtt.Type.SIUnit();
}
Add(parameter);
}
}
public void Save(List <SSAS.TabularTranslationObjectAnnotation> annotationsList)
{
if (_Property != TabularTranslatedItemProperty.Caption)
{
return; //every object will have a caption, so only perform the save on the caption
}
Dictionary <int, string> DisplayFolderLanguages = new Dictionary <int, string>();
Dictionary <int, string> DescriptionLanguages = new Dictionary <int, string>();
bool bDependentPropertyDirty = false;
foreach (TabularTranslatedItem dependent in DependentProperties)
{
bDependentPropertyDirty = bDependentPropertyDirty || dependent.Dirty;
if (dependent.Property == TabularTranslatedItemProperty.DisplayFolder)
{
DisplayFolderLanguages = dependent.Languages;
}
else if (dependent.Property == TabularTranslatedItemProperty.Description)
{
DescriptionLanguages = dependent.Languages;
}
}
//if (!Dirty && !bDependentPropertyDirty) return; //would be nice if we could short-circuit here, but we need to build the annotation
SSAS.TabularTranslationObjectAnnotation annotation = new TabularTranslationObjectAnnotation();
annotation.ObjectType = _ObjectType;
if (_object is DimensionAttribute)
{
DimensionAttribute da = (DimensionAttribute)_object;
annotation.ObjectID = da.ID;
annotation.TableID = da.Parent.ID;
SaveInternal(da.Translations, DisplayFolderLanguages, DescriptionLanguages, annotation);
}
else if (_object is Hierarchy)
{
Hierarchy h = (Hierarchy)_object;
annotation.ObjectID = h.ID;
annotation.TableID = h.Parent.ID;
SaveInternal(h.Translations, DisplayFolderLanguages, DescriptionLanguages, annotation);
}
else if (_object is Level)
{
Level l = (Level)_object;
annotation.ObjectID = l.ID;
annotation.HierarchyID = l.Parent.ID;
annotation.TableID = l.ParentDimension.ID;
SaveInternal(l.Translations, DisplayFolderLanguages, DescriptionLanguages, annotation);
}
else if (_object is CalculationProperty)
{
CalculationProperty calc = (CalculationProperty)_object;
annotation.ObjectID = _ObjectName;
//no need to save table
SaveInternal(calc.Translations, DisplayFolderLanguages, DescriptionLanguages, annotation);
}
else if (_object is Database)
{
Database db = (Database)_object;
annotation.ObjectID = db.ID;
SaveInternal(db.Translations, DisplayFolderLanguages, DescriptionLanguages, annotation);
}
else if (_object is Cube)
{
Cube cube = (Cube)_object;
annotation.ObjectID = cube.ID;
SaveInternal(cube.Translations, DisplayFolderLanguages, DescriptionLanguages, annotation);
}
else if (_object is Perspective)
{
Perspective p = (Perspective)_object;
annotation.ObjectID = p.ID;
SaveInternal(p.Translations, DisplayFolderLanguages, DescriptionLanguages, annotation);
}
else if (_object is Dimension)
{
Dimension dim = (Dimension)_object;
annotation.ObjectID = dim.ID;
SaveInternal(dim.Translations, DisplayFolderLanguages, DescriptionLanguages, annotation);
foreach (Cube cube in dim.Parent.Cubes)
{
foreach (CubeDimension cd in cube.Dimensions)
{
if (cd.DimensionID == dim.ID)
{
SaveInternal(cd.Translations, DisplayFolderLanguages, DescriptionLanguages, null);
}
}
foreach (MeasureGroup mg in cube.MeasureGroups)
{
if (mg.ID == dim.ID)
{
SaveInternal(mg.Translations, DisplayFolderLanguages, DescriptionLanguages, null);
}
}
}
}
else if (_object is Microsoft.AnalysisServices.Action)
{
Microsoft.AnalysisServices.Action actionMaster = (Microsoft.AnalysisServices.Action)_object;
foreach (Microsoft.AnalysisServices.Action action in actionMaster.Parent.Actions)
{
if (action.ID.StartsWith(actionMaster.ID))
{
annotation.ObjectID = action.ID;
SaveInternal(action.Translations, DisplayFolderLanguages, DescriptionLanguages, annotation);
}
}
}
else
{
throw new Exception("Unexpected object type: " + _object.GetType().Name);
}
if (annotation.TabularTranslations.Length > 0)
{
annotationsList.Add(annotation);
}
}
public TabularTranslatedItem(string table, IModelComponent obj, TabularTranslatedItemProperty property, TabularTranslatedItem caption, SSAS.TabularTranslationsAnnotation annotations)
{
_Table = table;
_object = obj;
_Property = property;
TranslationCollection translations;
string sCaption;
string sDescription;
string sDisplayFolder = null;
if (obj is DimensionAttribute)
{
_ObjectType = TabularTranslatedItemType.Column;
DimensionAttribute typedobj = (DimensionAttribute)obj;
translations = typedobj.Translations;
sDisplayFolder = typedobj.AttributeHierarchyDisplayFolder;
sCaption = typedobj.Name;
sDescription = typedobj.Description;
}
else if (obj is Hierarchy)
{
_ObjectType = TabularTranslatedItemType.Hierarchy;
Hierarchy typedobj = (Hierarchy)obj;
translations = typedobj.Translations;
sDisplayFolder = typedobj.DisplayFolder;
sCaption = typedobj.Name;
sDescription = typedobj.Description;
}
else if (obj is Level)
{
_ObjectType = TabularTranslatedItemType.Level;
Level typedobj = (Level)obj;
translations = typedobj.Translations;
sCaption = typedobj.Name;
sDescription = typedobj.Description;
}
else if (obj is CalculationProperty)
{
_ObjectType = TabularTranslatedItemType.Measure;
CalculationProperty typedobj = (CalculationProperty)obj;
translations = typedobj.Translations;
sDisplayFolder = typedobj.DisplayFolder;
sCaption = typedobj.CalculationReference;
sDescription = typedobj.Description;
if (sCaption.StartsWith("[") && sCaption.EndsWith("]"))
{
sCaption = sCaption.Substring(1, sCaption.Length - 2);
}
}
else if (obj is Database)
{
_ObjectType = TabularTranslatedItemType.Database;
Database typedobj = (Database)obj;
translations = typedobj.Translations;
sCaption = typedobj.Name;
sDescription = typedobj.Description;
}
else if (obj is Cube)
{
_ObjectType = TabularTranslatedItemType.Cube;
Cube typedobj = (Cube)obj;
translations = typedobj.Translations;
sCaption = typedobj.Name;
sDescription = typedobj.Description;
}
else if (obj is Perspective)
{
_ObjectType = TabularTranslatedItemType.Perspective;
Perspective typedobj = (Perspective)obj;
translations = typedobj.Translations;
sCaption = typedobj.Name;
sDescription = typedobj.Description;
}
else if (obj is Dimension)
{
_ObjectType = TabularTranslatedItemType.Table;
Dimension typedobj = (Dimension)obj;
translations = typedobj.Translations;
sCaption = typedobj.Name;
sDescription = typedobj.Description;
}
else if (obj is Microsoft.AnalysisServices.Action)
{
_ObjectType = TabularTranslatedItemType.Action;
Microsoft.AnalysisServices.Action typedobj = (Microsoft.AnalysisServices.Action)obj;
translations = typedobj.Translations;
sCaption = typedobj.Name;
sDescription = typedobj.Description;
}
else
{
throw new Exception("Unexpected object type: " + obj.GetType().Name);
}
_ObjectName = sCaption;
if (property == TabularTranslatedItemProperty.Caption)
{
_DefaultLanguage = sCaption;
SSAS.TabularTranslationObjectAnnotation annotation = annotations.Find(obj);
if (annotation != null && translations.Count == 0)
{
foreach (SSAS.TabularTranslationAnnotation tranAnnotation in annotation.TabularTranslations)
{
Translation t = new Translation(tranAnnotation.Language);
if (obj is DimensionAttribute)
{
t = new AttributeTranslation(tranAnnotation.Language);
}
t.Caption = tranAnnotation.Caption;
t.Description = tranAnnotation.Description;
t.DisplayFolder = tranAnnotation.DisplayFolder;
translations.Add(t);
}
_restoredTranslations = true;
}
}
else if (property == TabularTranslatedItemProperty.Description)
{
_DefaultLanguage = sDescription;
}
else if (property == TabularTranslatedItemProperty.DisplayFolder)
{
_DefaultLanguage = sDisplayFolder;
}
Languages = new Core.DirtyMonitoredDictionary <int, string>();
foreach (Translation t in translations)
{
if (property == TabularTranslatedItemProperty.Caption)
{
Languages.Add(t.Language, t.Caption);
}
else if (property == TabularTranslatedItemProperty.Description)
{
Languages.Add(t.Language, t.Description);
}
else if (property == TabularTranslatedItemProperty.DisplayFolder)
{
Languages.Add(t.Language, t.DisplayFolder);
}
}
if (caption != null)
{
caption.DependentProperties.Add(this);
}
}
private static bool ContainsSubsetOfKeys(DimensionAttribute a1, DimensionAttribute a2)
{
//check that every a2.KeyColumns can be found in a1.KeyColumns
if (a2.KeyColumns.Count == 0) return false;
foreach (DataItem di2 in a2.KeyColumns)
{
bool bFoundKey = false;
foreach (DataItem di1 in a1.KeyColumns)
{
if (CompareDataItems(di1, di2))
{
bFoundKey = true;
break;
}
}
if (!bFoundKey) return false;
}
return true;
}
private string GetPartialQueryForSingleAttributeColumn(DimensionAttribute Attr, DataItem Col, string ColType, int iLevel)
{
string qryView = "";
string AttribName = "";
string LevelName = GetNaturalizedLevelName(iLevel);
EnhancedColumnBinding cb = (EnhancedColumnBinding)Col.Source;
if (Attr.Usage != AttributeUsage.Key)
AttribName = "_" + Attr.Name;
if (cb.NamedCalculationExpression != "")
qryView += cb.NamedCalculationExpression + " [" + LevelName + AttribName + "_" + ColType + "]";
else
qryView += cb.ColumnName + " [" + LevelName + AttribName + "_" + ColType + "]";
return qryView;
}
private string GetPartialQueryForAttributeColumns(DimensionAttribute Attr, DataItemCollection Cols, string ColType, int iLevel)
{
try
{
string qryView = "";
if (Cols.Count > 1)
{
EnhancedColumnBinding cb = null;
for (int k = 0; k < Cols.Count; k++)
{
cb = (EnhancedColumnBinding)Cols[k].Source;
if (cb.NamedCalculationExpression != "")
qryView += "CONVERT(VARCHAR(MAX), " + cb.NamedCalculationExpression + ") + ";
else
qryView += "CONVERT(VARCHAR(MAX), " + cb.ColumnName + ") + ";
}
qryView = qryView.Trim().Trim('+');
qryView += " [" + GetNaturalizedLevelName(iLevel) + "_" + Attr.Name + "_" + ColType + "]";
}
else
qryView += GetPartialQueryForSingleAttributeColumn(Attr, Cols[0], ColType, iLevel);
return qryView;
}
catch (Exception e)
{
throw e;
}
}
public static bool IsAttributeRelated(DimensionAttribute attr, DimensionAttribute key)
{
foreach (AttributeRelationship atrel in key.AttributeRelationships)
if (key.AttributeRelationships.Contains(attr.ID) || IsAttributeRelated(attr, atrel.Attribute))
return true;
return false;
}
private string SetColumnFormatAndVisibility(DimensionAttribute attribute)
{
string columnDef = "";
if (attribute != null && attribute.Annotations != null && attribute.Annotations.Contains("Format") && attribute.Annotations["Format"].Value.Attributes["Format"] != null)
{
switch (attribute.Annotations["Format"].Value.Attributes["Format"].Value)
{
case "General":
columnDef += "Data Format: General";
break;
case "NumberDecimal":
columnDef += "Data Format: Decimal Number" +
(attribute.Annotations["Format"].Value.Attributes["Accuracy"] != null ? ", Decimal Places: " + attribute.Annotations["Format"].Value.Attributes["Accuracy"].Value : "") +
(attribute.Annotations["Format"].Value.Attributes["ThousandSeparator"] != null ? ", Show Thousand Separator: " + attribute.Annotations["Format"].Value.Attributes["ThousandSeparator"].Value : "");
break;
case "NumberWhole":
columnDef += "Data Format: Whole Number" +
(attribute.Annotations["Format"].Value.Attributes["ThousandSeparator"] != null ? ", Show Thousand Separator: " + attribute.Annotations["Format"].Value.Attributes["ThousandSeparator"].Value : "");
break;
case "Percentage":
columnDef += "Data Format: Percentage" +
(attribute.Annotations["Format"].Value.Attributes["Accuracy"] != null ? ", Decimal Places: " + attribute.Annotations["Format"].Value.Attributes["Accuracy"].Value : "") +
(attribute.Annotations["Format"].Value.Attributes["ThousandSeparator"] != null ? ", Show Thousand Separator: " + attribute.Annotations["Format"].Value.Attributes["ThousandSeparator"].Value : "");
break;
case "Scientific":
columnDef += "Data Format: Scientific" +
(attribute.Annotations["Format"].Value.Attributes["Accuracy"] != null ? ", Decimal Places: " + attribute.Annotations["Format"].Value.Attributes["Accuracy"].Value : "");
break;
case "Currency":
columnDef += "Data Format: Currency" +
(attribute.Annotations["Format"].Value.Attributes["Accuracy"] != null ? ", Decimal Places: " + attribute.Annotations["Format"].Value.Attributes["Accuracy"].Value : "") +
(attribute.Annotations["Format"].Value.HasChildNodes &&
attribute.Annotations["Format"].Value.ChildNodes[0].Attributes["DisplayName"] != null
? ", Currency Symbol: " + attribute.Annotations["Format"].Value.ChildNodes[0].Attributes["DisplayName"].Value : "");
break;
case "DateTimeCustom":
columnDef += "Data Format: Date" +
(attribute.Annotations["Format"].Value.HasChildNodes &&
attribute.Annotations["Format"].Value.ChildNodes[0].HasChildNodes &&
attribute.Annotations["Format"].Value.ChildNodes[0].ChildNodes[0].Attributes["FormatString"] != null
? ", Date Format: " + attribute.Annotations["Format"].Value.ChildNodes[0].ChildNodes[0].Attributes["FormatString"].Value : "");
break;
case "DateTimeGeneral":
columnDef += "Data Format: General";
break;
case "Text":
columnDef += "Data Format: Text";
break;
case "Boolean":
columnDef += "Data Format: TRUE/FALSE";
break;
default:
break;
}
}
else
{
// Sometimes annotations are not populated, so just show the default formats (which are text/general)
switch (attribute.KeyColumns[0].DataType)
{
case System.Data.OleDb.OleDbType.WChar:
columnDef += "Data Format: Text";
break;
case System.Data.OleDb.OleDbType.BigInt:
case System.Data.OleDb.OleDbType.Integer:
case System.Data.OleDb.OleDbType.SmallInt:
case System.Data.OleDb.OleDbType.Double:
case System.Data.OleDb.OleDbType.Currency:
case System.Data.OleDb.OleDbType.Date:
case System.Data.OleDb.OleDbType.Binary:
columnDef += "Data Format: General";
break;
case System.Data.OleDb.OleDbType.Boolean:
columnDef += "Data Format: TRUE/FALSE";
break;
default:
break;
}
}
columnDef += ", Hidden: " + (!attribute.AttributeHierarchyVisible).ToString();
//if (_parentTabularModel.ComparisonInfo.OptionsInfo.OptionDisplayFolders)
//{
// columnDef += ", Display Folder: ";
// if (attribute.AttributeHierarchyDisplayFolder != null)
// {
// columnDef += attribute.AttributeHierarchyDisplayFolder;
// }
//}
//if (_parentTabularModel.ComparisonInfo.OptionsInfo.OptionTranslations)
//{
// columnDef += ", Column Translations: ";
// if (attribute.Translations.Count > 0)
// {
// columnDef += "[";
// foreach (AttributeTranslation attributeTranslation in attribute.Translations)
// {
// columnDef += CultureInfo.GetCultureInfo(attributeTranslation.Language).DisplayName + ": " + attributeTranslation.Caption + ", ";
// }
// columnDef = columnDef.Substring(0, columnDef.Length - 2) + "]";
// }
// if (_parentTabularModel.ComparisonInfo.OptionsInfo.OptionDisplayFolders)
// {
// columnDef += ", Display Folder Translations: ";
// if (attribute.Translations.Count > 0)
// {
// columnDef += "[";
// foreach (AttributeTranslation attributeTranslation in attribute.Translations)
// {
// columnDef += CultureInfo.GetCultureInfo(attributeTranslation.Language).DisplayName + ": " + attributeTranslation.DisplayFolder + ", ";
// }
// columnDef = columnDef.Substring(0, columnDef.Length - 2) + "]";
// }
// }
//}
return(columnDef);
}
private static bool AggContainsParent(AggregationDimension aggDim, DimensionAttribute attr)
{
foreach (AttributeRelationship rel in attr.AttributeRelationships)
{
if (aggDim.Attributes.Find(rel.AttributeID) != null)
return true;
else if (AggContainsParent(aggDim, rel.Attribute))
return true;
}
return false;
}
public static List<DimensionAttribute> GetAttrRelOwnerChainToKey(DimensionAttribute Attr)
{
List<DimensionAttribute> atList = new List<DimensionAttribute>();
atList.Add(Attr);
foreach (DimensionAttribute atRelOwner in Program.ASFlattener.dim.Attributes)
if (atRelOwner.AttributeRelationships.ContainsName(Attr.Name))
{
if (atRelOwner.Usage == AttributeUsage.Regular)
atList.AddRange(GetAttrRelOwnerChainToKey(atRelOwner));
atList.Add(atRelOwner);
return atList;
}
return atList;
}
private DimensionAttribute MaterializeAttributeColumns(DimensionAttribute attrOrig, DataTable tblNew, string MemberName)
{
DimensionAttribute attr = null;
attr = CloneAttributeSkeleton(attrOrig, MemberName);
attr.KeyColumns.Add(
new DataItem(txtNewView, MemberName + "_KeyColumn", OleDbTypeConverter.GetRestrictedOleDbType(tblNew.Columns[MemberName + "_KeyColumn"].DataType), tblNew.Columns[MemberName + "_KeyColumn"].MaxLength));
if (attrOrig.NameColumn != null)
if (attrOrig.KeyColumns.Count > 1 || ColNameFromDataItem(attrOrig.KeyColumns[0]) != ColNameFromDataItem(attrOrig.NameColumn))
attr.NameColumn = new DataItem(txtNewView, MemberName + "_NameColumn", OleDbTypeConverter.GetRestrictedOleDbType(tblNew.Columns[MemberName + "_NameColumn"].DataType), tblNew.Columns[MemberName + "_NameColumn"].MaxLength);
else
attr.NameColumn = new DataItem(txtNewView, MemberName + "_KeyColumn", OleDbType.WChar, tblNew.Columns[MemberName + "_KeyColumn"].MaxLength); // Name column must always be string type. In at least one case, data used an integer column, but name column still had to be string or error occurs...
if (attrOrig.ValueColumn != null)
if (attrOrig.KeyColumns.Count > 1 || ColNameFromDataItem(attrOrig.KeyColumns[0]) != ColNameFromDataItem(attrOrig.ValueColumn) && ColNameFromDataItem(attrOrig.NameColumn) != ColNameFromDataItem(attrOrig.ValueColumn))
attr.ValueColumn = new DataItem(txtNewView, MemberName + "_ValueColumn", OleDbTypeConverter.GetRestrictedOleDbType(tblNew.Columns[MemberName + "_ValueColumn"].DataType), tblNew.Columns[MemberName + "_ValueColumn"].MaxLength);
else
attr.ValueColumn = new DataItem(txtNewView, MemberName + "_KeyColumn", OleDbTypeConverter.GetRestrictedOleDbType(tblNew.Columns[MemberName + "_KeyColumn"].DataType), tblNew.Columns[MemberName + "_KeyColumn"].MaxLength);
if (MemberName != dim.KeyAttribute.Name)
{
if (attrOrig.UnaryOperatorColumn != null || (attrOrig.Usage == AttributeUsage.Key && PCParentAttribute().UnaryOperatorColumn != null))
attr.UnaryOperatorColumn = new DataItem(txtNewView, MemberName + "_UnaryOperatorColumn", OleDbTypeConverter.GetRestrictedOleDbType(tblNew.Columns[MemberName + "_UnaryOperatorColumn"].DataType), tblNew.Columns[MemberName + "_UnaryOperatorColumn"].MaxLength);
if (attrOrig.CustomRollupColumn != null || (attrOrig.Usage == AttributeUsage.Key && PCParentAttribute().CustomRollupColumn != null))
attr.CustomRollupColumn = new DataItem(txtNewView, MemberName + "_CustomRollupColumn", OleDbTypeConverter.GetRestrictedOleDbType(tblNew.Columns[MemberName + "_CustomRollupColumn"].DataType), tblNew.Columns[MemberName + "_CustomRollupColumn"].MaxLength);
if (attrOrig.OrderByAttribute != null)
attr.OrderByAttributeID = MemberName + "_" + attrOrig.OrderByAttribute.Name;
}
return attr;
}
private static bool HasRelationship(DimensionAttribute from, DimensionAttribute to, System.Collections.Hashtable visitedAttributes)
{
if (from != null)
{
if (from == to)
{
return true;
}
if (visitedAttributes.Contains(from))
{
return false;
}
visitedAttributes.Add(from, null);
foreach (AttributeRelationship relationship in from.AttributeRelationships)
{
if (HasRelationship(relationship.Attribute, to, visitedAttributes))
{
return true;
}
}
}
return false;
}
public static bool IsAtOrAboveGranularity(DimensionAttribute attribute, MeasureGroupDimension mgDim)
{
if (mgDim is RegularMeasureGroupDimension)
{
MeasureGroupAttribute granularity = GetGranularityAttribute((RegularMeasureGroupDimension)mgDim);
if (granularity.AttributeID == attribute.ID)
return true;
return IsParentOf(attribute, granularity.Attribute);
}
else if (mgDim is ManyToManyMeasureGroupDimension)
{
//this depends on the granularity of the m2m dimension as it appears on the intermediate measure group
ManyToManyMeasureGroupDimension m2mDim = (ManyToManyMeasureGroupDimension)mgDim;
return IsAtOrAboveGranularity(attribute, m2mDim.MeasureGroup.Dimensions.Find(mgDim.CubeDimensionID));
}
else
{
return true;
}
}
private static string GetQueryToValidateKeyUniqueness(DimensionAttribute da)
{
if (da.KeyColumns.Count == 0 && (da.NameColumn == null || da.ValueColumn == null)) return null; // no need
if (da.NameColumn == null && da.ValueColumn == null) return null; // no need
if (da.KeyColumns.Count == 1 && CompareDataItems(da.KeyColumns[0], da.NameColumn) && da.ValueColumn == null) return null; // no need
if (da.KeyColumns.Count == 1 && CompareDataItems(da.KeyColumns[0], da.ValueColumn) && da.NameColumn == null) return null; // no need
DataSourceView oDSV = da.Parent.DataSourceView;
DataItem[] keyCols;
DataItem[] nameAndValueCols;
if (da.KeyColumns.Count == 0)
{
keyCols = new DataItem[] { da.NameColumn };
nameAndValueCols = new DataItem[] { da.ValueColumn }; //value column will be there because of previous check
}
else if (da.NameColumn == null)
{
if (da.KeyColumns.Count > 1)
{
throw new Exception("If no name column is defined, you may not have more than one key column.");
}
keyCols = new DataItem[] { da.KeyColumns[0] };
//use value as "name" column when checking uniqueness
nameAndValueCols = new DataItem[] { da.ValueColumn }; //value column will be there because of previous check
}
else
{
keyCols = new DataItem[da.KeyColumns.Count];
for (int i = 0; i < da.KeyColumns.Count; i++)
{
keyCols[i] = da.KeyColumns[i];
}
if (da.ValueColumn == null)
{
nameAndValueCols = new DataItem[] { da.NameColumn };
}
else
{
nameAndValueCols = new DataItem[] { da.NameColumn, da.ValueColumn };
}
}
return GetQueryToValidateUniqueness(oDSV, keyCols, nameAndValueCols);
}
static void PartitionInfo()
{
string srv_name = @"SCRBMSBDK000660";
string db_name = "FBR_FYPnL_DPRD";
string cube_name = "FYPnL Cube";
string mg_name = "USD";
//TextWriter tw = new StreamWriter("date.txt", true);
Microsoft.AnalysisServices.Server srv;
Microsoft.AnalysisServices.Database db;
srv = new Microsoft.AnalysisServices.Server();
try
{
srv.Connect(srv_name);
logMessageFmt("Databases on [{0}]: {1}", srv_name, srv.Databases.Count);
db = srv.Databases.FindByName(db_name);
Cube cube = db.Cubes.FindByName(cube_name);
CubeDimension cubedim = cube.Dimensions[0];
Dimension dbdim = cubedim.Dimension;
DimensionAttribute dbattr = dbdim.Attributes[0];
//var Source = dbattr.Source;
System.Data.DataSet ds = dbdim.DataSourceView.Schema;
string dsid = db.DataSourceViews[0].DataSourceID;
DataTable dt = db.DataSourceViews[0].Schema.Tables[0];
//db.DataSources[0].
//DataTable dt = ds.Tables["SHARED_DimBrand"];
//ep = dt.ExtendedProperties.
MeasureGroup mg = cube.MeasureGroups.FindByName(mg_name);
MeasureGroupDimension mgd = mg.Dimensions[0];
List <MeasureGroupAttribute> alist = new List <MeasureGroupAttribute>();
if (mgd is RegularMeasureGroupDimension)
{
RegularMeasureGroupDimension rmgd = (RegularMeasureGroupDimension)mgd;
foreach (MeasureGroupAttribute mgattr in rmgd.Attributes)
{
if (mgattr.Type == MeasureGroupAttributeType.Granularity)
{
alist.Add(mgattr);
}
}
//MeasureGroupAttribute mgattr = rmgd.Attributes.f["Key"];
}
Type t = alist[0].KeyColumns[0].Source.GetType();
Measure msr = mg.Measures[0];
foreach (Partition part in mg.Partitions)
{
string src;
TabularBinding tb = part.Source;
if (tb is QueryBinding)
{
src = String.Format("QUERY: {0}", ((QueryBinding)tb).QueryDefinition);
}
else if (tb is TableBinding)
{
src = String.Format("TABLE: {0}.{1}", ((TableBinding)tb).DbSchemaName, ((TableBinding)tb).DbTableName);
}
else if (tb is DsvTableBinding)
{
src = String.Format("DSV: {0}.{1}", ((DsvTableBinding)tb).DataSourceViewID, ((DsvTableBinding)tb).TableID);
}
else
{
src = String.Empty;
}
logMessageFmt("Partition [{0}]: {1}", part.Name, src /*part.EstimatedRows*/);
//part.Process()
}
//Partition part = mg.Partitions[0]; //.FindByName(part_name);
logMessage("Done.");
Console.ReadKey();
}
finally
{
if (srv.Connected == true)
{
srv.Disconnect();
}
}
}
/// <summary>
/// Create a relationship for the Table object.
/// </summary>
/// <param name="amoRelationshipSource"></param>
/// <param name="parentDimSource"></param>
/// <param name="relationshipName"></param>
/// <param name="warningMessage"></param>
/// <param name="active"></param>
/// <returns></returns>
public bool CreateRelationship(Microsoft.AnalysisServices.Relationship amoRelationshipSource, Dimension parentDimSource, string relationshipName, ref string warningMessage, bool active)
{
// Do the required tables exist?
// Child table is guaranteed to exist - we are in an instance of it. Just need to ensure the FromRelationshipEnd.DimensionID is correct
amoRelationshipSource.FromRelationshipEnd.DimensionID = _amoDimension.ID;
// Parent table need to check ...
if (_parentTabularModel.Tables.ContainsName(parentDimSource.Name))
{
//plug in the substitute id for the parent and move on with my life (I have a life you know)
amoRelationshipSource.ToRelationshipEnd.DimensionID = _parentTabularModel.Tables.FindByName(parentDimSource.Name).Id;
}
else
{
warningMessage = "Unable to create Relationship " + relationshipName + " because (considering changes) parent table not found in target model.";
return(false);
}
Dimension childDimSource = (Dimension)amoRelationshipSource.Parent;
Dimension childDimTarget = _amoDimension;
//Dimension parentDimSource = ... //had to pass in as parameter
Dimension parentDimTarget = _parentTabularModel.Tables.FindById(amoRelationshipSource.ToRelationshipEnd.DimensionID).AmoDimension;
// do the required columns exist?
Microsoft.AnalysisServices.Relationship amoRelationshipSourceTemp = null; // can't modify attribute values while in dim collection, so (might) need a temporary holder
foreach (RelationshipEndAttribute childDimAttributeSource in amoRelationshipSource.FromRelationshipEnd.Attributes)
{
DimensionAttribute childDimAttributeTarget = childDimTarget.Attributes.FindByName(childDimSource.Attributes[childDimAttributeSource.AttributeID].Name);
if (childDimAttributeTarget == null)
{
warningMessage = "Unable to create Relationship " + relationshipName + " because (considering changes) child column not found in target model.";
return(false);
}
// just in case the attribute ids are not the same between source/target (though obviously the names are the same), then set the correct id (below) in the source relationship and everything will just work
if (childDimAttributeSource.AttributeID != childDimAttributeTarget.ID)
{
amoRelationshipSourceTemp = amoRelationshipSource.Clone();
RelationshipEndAttribute childDimAttributeSourceTemp = childDimAttributeSource.Clone();
childDimAttributeSourceTemp.AttributeID = childDimAttributeTarget.ID;
amoRelationshipSourceTemp.FromRelationshipEnd.Attributes.Remove(childDimAttributeSource.AttributeID);
amoRelationshipSourceTemp.FromRelationshipEnd.Attributes.Add(childDimAttributeSourceTemp);
}
}
// now check parent columns
foreach (RelationshipEndAttribute parentDimAttributeSource in amoRelationshipSource.ToRelationshipEnd.Attributes)
{
if (!parentDimSource.Attributes.Contains(parentDimAttributeSource.AttributeID))
{
warningMessage = "Unable to create Relationship " + relationshipName + " because (considering changes) parent column not found in target model.";
return(false);
}
DimensionAttribute parentDimAttributeTarget = parentDimTarget.Attributes.FindByName(parentDimSource.Attributes[parentDimAttributeSource.AttributeID].Name);
if (parentDimAttributeTarget == null)
{
warningMessage = "Unable to create Relationship " + relationshipName + " because (considering changes) parent column not found in target model.";
return(false);
}
////does the parent column allow non-unique values? (if so, won't work as a parent in the relationship)
//if (!( parentDimTarget.Attributes.Contains("RowNumber") &&
// parentDimTarget.Attributes["RowNumber"].AttributeRelationships.Contains(parentDimAttributeTarget.ID) &&
// parentDimTarget.Attributes["RowNumber"].AttributeRelationships[parentDimAttributeTarget.ID].Cardinality == Cardinality.One ))
//{
// warningMessage = "Unable to create Relationship " + relationshipName + " because (considering changes) parent column allows non-unique values.";
// return false;
//}
// just in case the attribute ids are not the same between source/target (though obviously the names are the same), then set the correct id (below) in the source relationship and everything will just work
if (parentDimAttributeSource.AttributeID != parentDimAttributeTarget.ID)
{
if (amoRelationshipSourceTemp == null)
{
amoRelationshipSourceTemp = amoRelationshipSource.Clone();
}
RelationshipEndAttribute parentDimAttributeSourceTemp = parentDimAttributeSource.Clone();
parentDimAttributeSourceTemp.AttributeID = parentDimAttributeTarget.ID;
amoRelationshipSourceTemp.ToRelationshipEnd.Attributes.Remove(parentDimAttributeSource.AttributeID);
amoRelationshipSourceTemp.ToRelationshipEnd.Attributes.Add(parentDimAttributeSourceTemp);
}
}
if (amoRelationshipSourceTemp != null) //i.e. we had to replace at least one attribute id
{
childDimSource.Relationships.Remove(amoRelationshipSource.ID);
childDimSource.Relationships.Add(amoRelationshipSourceTemp);
amoRelationshipSource = amoRelationshipSourceTemp;
}
// is there already a relationship with the same tables/columns?
bool foundMatch = false;
foreach (Relationship relationshipTarget in _relationships)
{
// has same parent table?
if (relationshipTarget.AmoRelationship.ToRelationshipEnd.DimensionID == amoRelationshipSource.ToRelationshipEnd.DimensionID)
{
// check columns
bool columnsMatch = true;
foreach (RelationshipEndAttribute attribute in amoRelationshipSource.FromRelationshipEnd.Attributes)
{
if (!relationshipTarget.AmoRelationship.FromRelationshipEnd.Attributes.Contains(attribute.AttributeID))
{
columnsMatch = false;
}
}
foreach (RelationshipEndAttribute attribute in amoRelationshipSource.ToRelationshipEnd.Attributes)
{
if (!relationshipTarget.AmoRelationship.ToRelationshipEnd.Attributes.Contains(attribute.AttributeID))
{
columnsMatch = false;
}
}
if (columnsMatch)
{
foundMatch = true;
}
}
}
if (foundMatch)
{
warningMessage = "Unable to create Relationship " + relationshipName + " because (considering changes) relationship already exists in target model.";
return(false);
}
// at this point we know we will add the relationship, but need to check that parent column only allows unique values. If not, change it.
foreach (RelationshipEndAttribute parentDimAttributeSource in amoRelationshipSource.ToRelationshipEnd.Attributes)
{
DimensionAttribute parentDimAttributeTarget = parentDimTarget.Attributes.FindByName(parentDimSource.Attributes[parentDimAttributeSource.AttributeID].Name);
//(already checked for existence of parentDimAttributeTarget above)
if (parentDimTarget.Attributes.Contains("RowNumber") &&
parentDimTarget.Attributes["RowNumber"].AttributeRelationships.Contains(parentDimAttributeTarget.ID) &&
parentDimTarget.Attributes["RowNumber"].AttributeRelationships[parentDimAttributeTarget.ID].Cardinality != Cardinality.One)
{
parentDimTarget.Attributes["RowNumber"].AttributeRelationships[parentDimAttributeTarget.ID].Cardinality = Cardinality.One;
foreach (DataItem di in parentDimAttributeTarget.KeyColumns)
{
di.NullProcessing = NullProcessing.Error;
}
if (_parentTabularModel.AmoDatabase.Cubes.Count > 0)
{
foreach (MeasureGroup mg in _parentTabularModel.AmoDatabase.Cubes[0].MeasureGroups)
{
if (mg.ID == parentDimTarget.ID)
{
foreach (MeasureGroupDimension mgd in mg.Dimensions)
{
if (mgd.CubeDimensionID == parentDimTarget.ID && mgd is DegenerateMeasureGroupDimension)
{
foreach (MeasureGroupAttribute mga in ((DegenerateMeasureGroupDimension)mgd).Attributes)
{
if (mga.AttributeID == parentDimAttributeTarget.ID)
{
mga.KeyColumns[0].NullProcessing = NullProcessing.Error;
}
}
}
}
}
}
}
}
}
// at this point we know we will add the relationship
Microsoft.AnalysisServices.Relationship relationshipClone = amoRelationshipSource.Clone();
// but first check if there is an existing relationship with same id
if (_parentTabularModel.ContainsRelationship(relationshipClone.ID))
{
//Id already exists, but still need to add because different definition - this is due to clever clog users changing table names that were originially in both source and target
string oldRelationshipId = relationshipClone.ID;
relationshipClone.ID = Convert.ToString(Guid.NewGuid());
}
if (active && !_parentTabularModel.ActiveRelationshipIds.Contains(relationshipClone.ID))
{
_parentTabularModel.ActiveRelationshipIds.Add(relationshipClone.ID);
}
_amoDimension.Relationships.Add(relationshipClone);
_relationships.Add(new Relationship(this, relationshipClone, copiedFromSource: true));
return(true);
}
private void RemoveInvalidCustomAttributes()
{
bool update = false;
Database db = this.DatabaseManager.AdomdDatabase;
// get the Store dimension
Dimension dim = db.Dimensions.FindByName(this.DimensionName);
if (dim == null)
{
throw new Exception("Dimension not found: " + this.DimensionName);
}
// get existing custom columns
DataTable dataColumns = GetCustomDataSourceColumns();
// check and remove
for (int i = dim.Attributes.Count - 1; i >= 0; i--)
{
DimensionAttribute attr = dim.Attributes[i];
// custom attribute consists of 1 column
if (attr.KeyColumns.Count != 1)
{
continue;
}
// get key column info
DataItem di = attr.KeyColumns[0];
string tableId = ((ColumnBinding)di.Source).TableID;
string colName = ((ColumnBinding)di.Source).ColumnID;
// check if it's not custom
if (!colName.StartsWith("GRP@#@"))
{
continue;
}
// check if valid
if (dataColumns.Rows.Find(colName) != null)
{
continue;
}
// remove datasourceview column
if (db.DataSourceViews[0].Schema.Tables[tableId].Columns.Contains(colName))
{
db.DataSourceViews[0].Schema.Tables[tableId].Columns.Remove(colName);
update = true;
}
// remove hierarchies that contain this attribute
for (int j = dim.Hierarchies.Count - 1; j >= 0; j--)
{
foreach (Level lev in dim.Hierarchies[j].Levels)
{
if (lev.SourceAttribute == attr)
{
dim.Hierarchies.RemoveAt(j);
update = true;
break; // level loop
}
}
}
// remove attribute
if (dim.Attributes.Contains(attr))
{
dim.Attributes.RemoveAt(i);
update = true;
}
}
if (update)
{
// update datasource
db.DataSourceViews[0].Update(UpdateOptions.ExpandFull | UpdateOptions.AlterDependents);
// update dimension
dim.Update(UpdateOptions.ExpandFull | UpdateOptions.AlterDependents);
}
}
private static List <DimensionUsage> RecurseTabularRelationships(Dimension dMG, MeasureGroup mgOuter, bool bIsBusMatrix)
{
List <DimensionUsage> list = new List <DimensionUsage>();
foreach (Microsoft.AnalysisServices.Relationship relOuter in dMG.Relationships)
{
bool bFound = false;
MeasureGroup mgFrom = dMG.Parent.Cubes[0].MeasureGroups[relOuter.FromRelationshipEnd.DimensionID];
DimensionAttribute daFrom = dMG.Attributes[relOuter.FromRelationshipEnd.Attributes[0].AttributeID];
Dimension dTo = dMG.Parent.Dimensions[relOuter.ToRelationshipEnd.DimensionID];
DimensionAttribute daTo = dTo.Attributes[relOuter.ToRelationshipEnd.Attributes[0].AttributeID];
CubeDimension dToCube = dMG.Parent.Cubes[0].Dimensions[relOuter.ToRelationshipEnd.DimensionID];
foreach (MeasureGroupDimension mgdOuter in mgFrom.Dimensions)
{
ReferenceMeasureGroupDimension rmgdOuter = mgdOuter as ReferenceMeasureGroupDimension;
if (rmgdOuter != null && rmgdOuter.Materialization == ReferenceDimensionMaterialization.Regular && rmgdOuter.RelationshipID == relOuter.ID)
{
//active relationships have a materialized reference relationship
bFound = true;
break;
}
}
string sActiveFlag = "Active";
if (!bFound)
{
sActiveFlag = "Inactive";
if (bIsBusMatrix)
{
continue; //don't show inactive relationships in bus matrix view
}
}
DimensionUsage usage = new DimensionUsage(sActiveFlag, mgOuter, dToCube, dTo);
usage.Column1Name = "Foreign Key Column";
usage.Column1Value = daFrom.Name;
usage.Column2Name = "Primary Key Column";
usage.Column2Value = daTo.Name;
bool bFoundVisibleAttribute = false;
foreach (DimensionAttribute da in dTo.Attributes)
{
if (da.AttributeHierarchyVisible)
{
bFoundVisibleAttribute = true;
break;
}
}
if (bFoundVisibleAttribute) //only if the To end has visible attributes should we show it as a dimension
{
list.Add(usage);
}
if (bIsBusMatrix)
{
//recurse if it's the bus matrix view
list.AddRange(RecurseTabularRelationships(dTo, mgOuter, bIsBusMatrix));
}
}
return(list);
}
private void CreateCustomAttribute(string colName)
{
bool update = false;
Database db = this.DatabaseManager.AdomdDatabase;
// get captions
string caption = BuildCaptionFromColumnName(colName);
if (caption == null || caption.Length == 0)
{
return;
}
string attrCaption = caption + " Attr";
string hierName = caption;
// get the dimension
Dimension dim = db.Dimensions.FindByName(this.DimensionName);
if (dim == null)
{
throw new Exception("Dimension not found: " + this.DimensionName);
}
// get key column info
DataItem di = dim.KeyAttribute.KeyColumns[0];
string tableId = ((ColumnBinding)di.Source).TableID;
// change datasource
if (!db.DataSourceViews[0].Schema.Tables[tableId].Columns.Contains(colName))
{
db.DataSourceViews[0].Schema.Tables[tableId].Columns.Add(colName, typeof(string));
db.DataSourceViews[0].Update(UpdateOptions.ExpandFull | UpdateOptions.AlterDependents);
}
// find or create attribute
DimensionAttribute attr = dim.Attributes.FindByName(attrCaption);
if (attr == null)
{
attr = dim.Attributes.Add(attrCaption);
attr.Usage = AttributeUsage.Regular;
attr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], tableId, colName));
attr.NameColumn = CreateDataItem(db.DataSourceViews[0], tableId, colName);
attr.AttributeHierarchyVisible = false;
attr.MemberNamesUnique = false;
update = true;
}
// find or create hiearachy
Hierarchy hier = dim.Hierarchies.FindByName(hierName);
if (hier == null)
{
hier = dim.Hierarchies.Add(hierName);
hier.MemberKeysUnique = MemberKeysUnique.NotUnique;
hier.Levels.Add(attrCaption.Replace(" Attr", "")).SourceAttributeID = attr.ID;
// level to add
if (!string.IsNullOrEmpty(this.LevelToAdd) && this.LevelToAdd != dim.KeyAttribute.Name.Replace(" Attr", ""))
{
DimensionAttribute levelAttr = dim.Attributes.FindByName(this.LevelToAdd + " Attr");
hier.Levels.Add(this.LevelToAdd).SourceAttributeID = levelAttr.ID;
}
hier.Levels.Add(dim.KeyAttribute.Name.Replace(" Attr", "")).SourceAttributeID = dim.KeyAttribute.ID;
update = true;
}
// update
if (update)
{
dim.Update(UpdateOptions.ExpandFull | UpdateOptions.AlterDependents);
}
}
private DimensionAttribute CloneAttributeSkeleton(DimensionAttribute OriginalAttribute, string NewIDAndName)
{
DimensionAttribute attr = OriginalAttribute.Clone();
attr.Name = NewIDAndName;
attr.ID = NewIDAndName;
attr.AttributeRelationships.Clear();
attr.KeyColumns.Clear();
attr.CustomRollupColumn = null;
attr.UnaryOperatorColumn = null;
attr.NameColumn = null;
attr.ValueColumn = null;
attr.OrderBy = OrderBy.Key;
attr.OrderByAttributeID = null;
// Do not know why but when this was copied from PC settings it caused failure in new attributes, possibly because of breakdown into levels?
attr.DiscretizationMethod = DiscretizationMethod.None;
return attr;
}
private void CreateCustomAttribute(string colName)
{
bool update = false;
Database db = this.DatabaseManager.AdomdDatabase;
// get column caption
if (colName == null || colName == string.Empty)
{
throw new ArgumentException("Empty columnName");
}
string attrCaption = (colName.StartsWith("GRP@#@") ? colName.Substring(6) : colName) + " Attr";
string hierName = (colName.StartsWith("GRP@#@") ? colName.Substring(6) : colName);
// get the Store dimension
Dimension dim = db.Dimensions.FindByName(this.DimensionName);
if (dim == null)
{
throw new Exception("Dimension not found: " + this.DimensionName);
}
// get key column info
DataItem di = dim.KeyAttribute.KeyColumns[0];
string tableId = ((ColumnBinding)di.Source).TableID;
// change datasource
if (!db.DataSourceViews[0].Schema.Tables[tableId].Columns.Contains(colName))
{
db.DataSourceViews[0].Schema.Tables[tableId].Columns.Add(colName, typeof(string));
db.DataSourceViews[0].Update(UpdateOptions.ExpandFull | UpdateOptions.AlterDependents);
}
// find or create attribute
DimensionAttribute attr = dim.Attributes.FindByName(attrCaption);
if (attr == null)
{
attr = dim.Attributes.Add(attrCaption);
attr.Usage = AttributeUsage.Regular;
attr.KeyColumns.Add(CreateDataItem(db.DataSourceViews[0], tableId, colName));
attr.NameColumn = CreateDataItem(db.DataSourceViews[0], tableId, colName);
attr.AttributeHierarchyVisible = false;
attr.MemberNamesUnique = false;
update = true;
}
// find or create hiearachy
Hierarchy hier = dim.Hierarchies.FindByName(hierName);
if (hier == null)
{
hier = dim.Hierarchies.Add(hierName);
hier.MemberKeysUnique = MemberKeysUnique.NotUnique;
hier.Levels.Add(attrCaption.Replace(" Attr", "")).SourceAttributeID = attr.ID;
hier.Levels.Add(dim.KeyAttribute.Name.Replace(" Attr", "")).SourceAttributeID = dim.KeyAttribute.ID;
update = true;
}
// update
if (update)
{
dim.Update(UpdateOptions.ExpandFull | UpdateOptions.AlterDependents);
}
}
private string GetPartialQueryForAttributeColumns(DimensionAttribute Attr, DataItem Col, string ColType, int iLevel)
{
return GetPartialQueryForSingleAttributeColumn(Attr, Col, ColType, iLevel); ;
}
/*
* cavaets:
* doesn't handle varchar(max) DSV columns as the DSV doesn't report the length. The length needs to be set manually on the KeyColumn or NameColumn
*/
private void CheckDataTypeDiscrepancies(DataItem di, ColumnType ColumnType)
{
if (di == null)
{
return;
}
ColumnBinding cb = di.Source as ColumnBinding;
if (cb == null)
{
return;
}
IModelComponent parent = di.Parent;
while (parent != null && !(parent is DimensionAttribute))
{
parent = parent.Parent;
}
DimensionAttribute da = (DimensionAttribute)parent;
if (!da.Parent.DataSourceView.Schema.Tables.Contains(cb.TableID))
{
return;
}
if (!da.Parent.DataSourceView.Schema.Tables[cb.TableID].Columns.Contains(cb.ColumnID))
{
return;
}
DataColumn col = da.Parent.DataSourceView.Schema.Tables[cb.TableID].Columns[cb.ColumnID];
if (ColumnType == ColumnType.NameColumn)
{
if (col.MaxLength <= 0)
{
return;
}
if (col.DataType != typeof(string))
{
return;
}
if (di.DataType != OleDbType.WChar)
{
return;
}
if (col.MaxLength != di.DataSize)
{
DataTypeDiscrepancy discrepancy = new DataTypeDiscrepancy();
discrepancy.AnalysisServicesColumnType = ColumnType;
discrepancy.AnalysisServicesColumn = di;
discrepancy.DimensionAttribute = da;
discrepancy.DSVColumn = col;
listDiscrepancies.Add(discrepancy);
}
}
else //KeyColumn
{
bool bDiscrepancy = false;
if (Microsoft.AnalysisServices.OleDbTypeConverter.Convert(di.DataType) != col.DataType && Microsoft.AnalysisServices.OleDbTypeConverter.GetRestrictedOleDbType(col.DataType) != di.DataType)
{
bDiscrepancy = true;
}
if (di.DataSize >= 0 && col.MaxLength >= 0 && di.DataSize != col.MaxLength)
{
bDiscrepancy = true;
}
if (bDiscrepancy)
{
DataTypeDiscrepancy discrepancy = new DataTypeDiscrepancy();
discrepancy.AnalysisServicesColumnType = ColumnType;
discrepancy.AnalysisServicesColumn = di;
discrepancy.DimensionAttribute = da;
discrepancy.DSVColumn = col;
listDiscrepancies.Add(discrepancy);
}
}
}
private string GetSingleNonPCUserHierarchyColumnNamesFromOriginalDimension(DimensionAttribute attr)
{
string strSubselect = "";
bool NameColumnCoveredInKey = false;
bool ValueColumnCoveredInKey = false;
foreach (DataItem di in attr.KeyColumns)
{
strSubselect += GetNonPCColNameOrExpressionFromDSVColumn(di) + ", -- End of column definition\r\n";
if (attr.NameColumn != null && ColNameFromDataItem(di) == ColNameFromDataItem(attr.NameColumn))
NameColumnCoveredInKey = true;
if (attr.ValueColumn != null && ColNameFromDataItem(di) == ColNameFromDataItem(attr.ValueColumn))
ValueColumnCoveredInKey = true;
}
if (!NameColumnCoveredInKey && attr.NameColumn != null)
strSubselect += GetNonPCColNameOrExpressionFromDSVColumn(attr.NameColumn) + ", -- End of column definition\r\n";
if (!ValueColumnCoveredInKey && attr.ValueColumn != null)
strSubselect += GetNonPCColNameOrExpressionFromDSVColumn(attr.ValueColumn) + ", -- End of column definition\r\n";
return strSubselect;
}
private static bool AggContainsChild(AggregationDimension aggDim, DimensionAttribute attr)
{
foreach (AggregationAttribute aggAttr in aggDim.Attributes)
{
if (IsParentOf(attr, aggAttr.Attribute))
return true;
}
return false;
}
private Dimension MaterializeSingleNonPCUserHierarchyAttribute(Dimension Dim, DataTable tblNew, DimensionAttribute attr)
{
Dimension dimNew = Dim;
if (attr.Usage == AttributeUsage.Regular)
{
DimensionAttribute attrNew = CloneAttributeSkeleton(attr, attr.Name);
for (int k = 0; k < attr.KeyColumns.Count; k++)
{
string ColName = "CurrentMember_" + ((EnhancedColumnBinding)attr.KeyColumns[k].Source).ColumnName.Trim('[').Trim(']');
attrNew.KeyColumns.Add(new DataItem(txtNewView, ColName,
OleDbTypeConverter.GetRestrictedOleDbType(tblNew.Columns[ColName].DataType),
tblNew.Columns[ColName].MaxLength));
}
if (attr.NameColumn != null)
{
string ColName = "CurrentMember_" + ((EnhancedColumnBinding)attr.NameColumn.Source).ColumnName.Trim('[').Trim(']');
attrNew.NameColumn = new DataItem(txtNewView, ColName,
OleDbType.WChar, // Name must always be string even though source column has in some customer cases not been...
tblNew.Columns[ColName].MaxLength);
}
if (attr.ValueColumn != null)
{
string ColName = "CurrentMember_" + ((EnhancedColumnBinding)attr.ValueColumn.Source).ColumnName.Trim('[').Trim(']');
attrNew.ValueColumn = new DataItem(txtNewView, ColName,
OleDbTypeConverter.GetRestrictedOleDbType(tblNew.Columns[ColName].DataType),
tblNew.Columns[ColName].MaxLength);
}
dimNew.Attributes.Add(attrNew);
}
return dimNew;
}
internal static string GetQueryDefinition(Database d, NamedComponent nc, Microsoft.AnalysisServices.Binding b, List <DataItem> columnsNeeded)
{
StringBuilder sQuery = new StringBuilder();
if (b is DsvTableBinding)
{
DsvTableBinding oDsvTableBinding = (DsvTableBinding)b;
DataSourceView oDSV = d.DataSourceViews[oDsvTableBinding.DataSourceViewID];
DataTable oTable = oDSV.Schema.Tables[oDsvTableBinding.TableID];
if (oTable == null)
{
throw new Exception("DSV table " + oDsvTableBinding.TableID + " not found");
}
else if (!oTable.ExtendedProperties.ContainsKey("QueryDefinition") && oTable.ExtendedProperties.ContainsKey("DbTableName"))
{
foreach (DataColumn oColumn in oTable.Columns)
{
bool bFoundColumn = false;
if (columnsNeeded == null)
{
bFoundColumn = true;
}
else
{
foreach (DataItem di in columnsNeeded)
{
if (GetColumnBindingForDataItem(di).TableID == oTable.TableName && GetColumnBindingForDataItem(di).ColumnID == oColumn.ColumnName)
{
bFoundColumn = true;
}
}
}
if (bFoundColumn)
{
if (sQuery.Length == 0)
{
sQuery.Append("select ");
}
else
{
sQuery.Append(",");
}
if (!oColumn.ExtendedProperties.ContainsKey("ComputedColumnExpression"))
{
sQuery.Append(sq).Append((oColumn.ExtendedProperties["DbColumnName"] ?? oColumn.ColumnName).ToString()).AppendLine(fq);
}
else
{
sQuery.Append(oColumn.ExtendedProperties["ComputedColumnExpression"].ToString()).Append(" as ").Append(sq).Append((oColumn.ExtendedProperties["DbColumnName"] ?? oColumn.ColumnName).ToString()).AppendLine(fq);
}
}
}
if (sQuery.Length == 0)
{
throw new Exception("There was a problem constructing the query.");
}
sQuery.Append("from ");
if (oTable.ExtendedProperties.ContainsKey("DbSchemaName"))
{
sQuery.Append(sq).Append(oTable.ExtendedProperties["DbSchemaName"].ToString()).Append(fq).Append(".");
}
sQuery.Append(sq).Append(oTable.ExtendedProperties["DbTableName"].ToString());
sQuery.Append(fq).Append(" ").Append(sq).Append(oTable.ExtendedProperties["FriendlyName"].ToString()).AppendLine(fq);
}
else if (oTable.ExtendedProperties.ContainsKey("QueryDefinition"))
{
sQuery.AppendLine("select *");
sQuery.AppendLine("from (");
sQuery.AppendLine(oTable.ExtendedProperties["QueryDefinition"].ToString());
sQuery.AppendLine(") x");
}
else
{
throw new Exception("Current the code does not support this type of query.");
}
}
else if (b is QueryBinding)
{
QueryBinding oQueryBinding = (QueryBinding)b;
sQuery.Append(oQueryBinding.QueryDefinition);
}
else if (b is ColumnBinding)
{
ColumnBinding cb = (ColumnBinding)b;
object parent = cb.Parent;
DataTable dt = d.DataSourceViews[0].Schema.Tables[cb.TableID];
if (nc is DimensionAttribute)
{
DimensionAttribute da = (DimensionAttribute)nc;
if (da.Parent.KeyAttribute.KeyColumns.Count != 1)
{
throw new Exception("Attribute " + da.Parent.KeyAttribute.Name + " has a composite key. This is not supported for a key attribute of a dimension.");
}
string sDsvID = ((DimensionAttribute)nc).Parent.DataSourceView.ID;
columnsNeeded.Add(new DataItem(cb.Clone()));
columnsNeeded.Add(da.Parent.KeyAttribute.KeyColumns[0]);
return(GetQueryDefinition(d, nc, new DsvTableBinding(sDsvID, cb.TableID), columnsNeeded));
}
else
{
throw new Exception("GetQueryDefinition does not currently support a ColumnBinding on a object of type " + nc.GetType().Name);
}
}
else
{
throw new Exception("Not a supported query binding type: " + b.GetType().FullName);
}
return(sQuery.ToString());
}
private bool IsParentOf(DimensionAttribute parent, DimensionAttribute child)
{
foreach (AttributeRelationship rel in child.AttributeRelationships)
{
if (rel.AttributeID == parent.ID)
return true;
else if (IsParentOf(parent, rel.Attribute))
return true;
}
return false;
}
private static bool CanReachAttributeFromChildInAgg(AggregationAttribute attr, DimensionAttribute current, bool bChildIsInAgg)
{
bChildIsInAgg = bChildIsInAgg || attr.Parent.Attributes.Contains(current.ID);
foreach (AttributeRelationship r in current.AttributeRelationships)
{
if (r.AttributeID == attr.AttributeID && bChildIsInAgg)
{
return true;
}
else
{
if (CanReachAttributeFromChildInAgg(attr, r.Attribute, bChildIsInAgg)) return true;
}
}
return false;
}