/// <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; }