關聯性表示法 (表格式)
關聯性是指兩個資料表之間的連接。 關聯性會建立兩個資料表中的資料相互關聯的方式。
如需有關如何建立和操作關聯性表示法的詳細說明，請參閱＜關聯性表示法 (表格式)＞。
關聯性表示法
在表格式模型中，可以在兩個資料表之間定義多個關聯性。 定義兩個資料表之間的多個關聯性時，只有一個關聯性可以定義為模型的預設關聯性，而且它會稱為作用中的關聯性；所有其他關聯性則稱為非作用中的關聯性。
AMO 中的關聯性
就 AMO 物件而言，所有非作用中的關聯性與 Relationship 之間都有一對一對應關聯性表示法，而且不需要其他主要 AMO 物件；如果是作用中的關聯性，則會有其他需求，而且也需要對應至 ReferenceMeasureGroupDimension。
下列程式碼片段說明如何在表格式模型內建立關聯性、如何啟用關聯性，以及如何在資料表內定義主索引鍵 (非 "RowNumber")。 若要建立作用中的關聯性，必須在關聯性的主索引鍵資料表 PKTableName (關聯性的一端) 內定義主索引鍵，這裡顯示的範例會在 PKColumnName 上建立主索引鍵 (如果此資料行中未定義任何主索引鍵)。 您可以建立作用中的關聯性，而不需要在主索引鍵資料行上擁有主索引鍵。
private Boolean createRelationship(string PKTableName, string PKColumnName, string MVTableName, string MVColumnName, AMO.Database tabularDb, string cubeName, Boolean forceActive)
{
//verify input parameters
if( string.IsNullOrEmpty(PKTableName) || string.IsNullOrWhiteSpace(PKTableName)
|| string.IsNullOrEmpty(PKColumnName) || string.IsNullOrWhiteSpace(PKColumnName)
|| string.IsNullOrEmpty(MVTableName) || string.IsNullOrWhiteSpace(MVTableName)
|| string.IsNullOrEmpty(MVColumnName) || string.IsNullOrWhiteSpace(MVColumnName)
|| (tabularDb == null)
) return false;
if(!tabularDb.Dimensions.ContainsName(PKTableName) || !tabularDb.Dimensions.ContainsName(MVTableName)) return false;
if(!tabularDb.Dimensions[PKTableName].Attributes.ContainsName(PKColumnName) || !tabularDb.Dimensions[MVTableName].Attributes.ContainsName(MVColumnName)) return false;
//Verify underlying cube structure
if (!tabularDb.Cubes[cubeName].Dimensions.ContainsName(PKTableName) || !tabularDb.Cubes[cubeName].Dimensions.ContainsName(MVTableName)) return false; //Should return an exception!!
if (!tabularDb.Cubes[cubeName].MeasureGroups.ContainsName(PKTableName) || !tabularDb.Cubes[cubeName].MeasureGroups.ContainsName(MVTableName)) return false; //Should return an exception!!
//Make sure PKTableName.PKColumnName is set as PK ==> <attribute>.usage == AMO.AttributeUsage.Key
if (tabularDb.Dimensions[PKTableName].Attributes[PKColumnName].Usage != AMO.AttributeUsage.Key)
{
//... here we are 'fixing', if there is an issue with PKTableName.PKColumnName not beeing the PK of the table
setPKColumn(tabularDb, PKTableName, PKColumnName);
}
//Terminology note:
// - the many side of the relationship is named the From end in AMO
// - the PK side of the relationship in named the To end in AMO
//
//It seems relationships flow FROM the many side of the relationship in TO the primary key side of the relationship in AMO
//
//Verify the relationship we are creating does not exist, regardless of name.
//if it exists, return true (no need to recreate it)
//if it doesn't exists it will be created after this validation
//
foreach (AMO.Relationship currentRelationship in tabularDb.Dimensions[MVTableName].Relationships)
{
if ((currentRelationship.FromRelationshipEnd.Attributes[0].AttributeID == MVColumnName)
&& (currentRelationship.ToRelationshipEnd.DimensionID == PKTableName)
&& (currentRelationship.ToRelationshipEnd.Attributes[0].AttributeID == PKColumnName))
{
if (forceActive)
{
//Activate the relationship
setActiveRelationship(tabularDb.Cubes[cubeName], MVTableName, MVColumnName, PKTableName, currentRelationship.ID);
//Update server with changes made here
tabularDb.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.CreateOrReplace);
tabularDb.Cubes[cubeName].MeasureGroups[MVTableName].Process(AMO.ProcessType.ProcessFull);
}
return true;
}
}
//A relationship like the one to be created does not exist; ergo, let's create it:
//First, create the INACTIVE relationship definitions in the MultipleValues end of the relationship
#region define unique name for relationship
string newRelationshipID = string.Format("Relationship _{0}_{1}_ to _{2}_{3}_", MVTableName, MVColumnName, PKTableName, PKColumnName);
int rootLen = newRelationshipID.Length;
for (int i = 0; tabularDb.Dimensions[MVTableName].Relationships.Contains(newRelationshipID); )
{
newRelationshipID = string.Format("{0}_{1,8:0}", newRelationshipID.Substring(0, rootLen), i);
}
#endregion
AMO.Relationship newRelationship = tabularDb.Dimensions[MVTableName].Relationships.Add(newRelationshipID);
newRelationship.FromRelationshipEnd.DimensionID = MVTableName;
newRelationship.FromRelationshipEnd.Attributes.Add(MVColumnName);
newRelationship.FromRelationshipEnd.Multiplicity = AMO.Multiplicity.Many;
newRelationship.FromRelationshipEnd.Role = string.Empty;
newRelationship.ToRelationshipEnd.DimensionID = PKTableName;
newRelationship.ToRelationshipEnd.Attributes.Add(PKColumnName);
newRelationship.ToRelationshipEnd.Multiplicity = AMO.Multiplicity.One;
newRelationship.ToRelationshipEnd.Role = string.Empty;
//Update server to create relationship
tabularDb.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate);
tabularDb.Dimensions[MVTableName].Process(AMO.ProcessType.ProcessDefault);
tabularDb.Dimensions[PKTableName].Process(AMO.ProcessType.ProcessDefault);
//Second, activate the relationship if relationship is to be set as the active relationship: 'forceActive==true'
//... an inactive relationship needs only to be created on the dimensions object
if (forceActive)
{
//Activate the relationship
setActiveRelationship(tabularDb.Cubes[cubeName], MVTableName, MVColumnName, PKTableName, newRelationshipID);
}
return true;
}
private void setActiveRelationship(AMO.Cube currentCube, string MVTableName, string MVColumnName, string PKTableName, string relationshipID)
{
if (!currentCube.MeasureGroups.Contains(MVTableName))
{
throw new AMO.AmoException(string.Format("Cube [{0}] does not contain Measure Group [{1}]\nError activating relationship [{2}]: [{4}] <--- [{1}].[{3}]"
, currentCube.Name, MVTableName, relationshipID, MVColumnName, PKTableName));
}
AMO.MeasureGroup currentMG = currentCube.MeasureGroups[MVTableName];
if (!currentMG.Dimensions.Contains(PKTableName))
{
AMO.ReferenceMeasureGroupDimension newReferenceMGDim = new AMO.ReferenceMeasureGroupDimension();
newReferenceMGDim.CubeDimensionID = PKTableName;
newReferenceMGDim.IntermediateCubeDimensionID = MVTableName;
newReferenceMGDim.IntermediateGranularityAttributeID = MVColumnName;
newReferenceMGDim.Materialization = AMO.ReferenceDimensionMaterialization.Regular;
newReferenceMGDim.RelationshipID = relationshipID;
foreach (AMO.CubeAttribute PKAttribute in currentCube.Dimensions[PKTableName].Attributes)
{
AMO.MeasureGroupAttribute PKMGAttribute = newReferenceMGDim.Attributes.Add(PKAttribute.AttributeID);
OleDbType PKMGAttributeType = PKAttribute.Attribute.KeyColumns[0].DataType;
PKMGAttribute.KeyColumns.Add(new AMO.DataItem(PKTableName, PKAttribute.AttributeID, PKMGAttributeType));
PKMGAttribute.KeyColumns[0].Source = new AMO.ColumnBinding(PKTableName, PKAttribute.AttributeID);
}
currentMG.Dimensions.Add(newReferenceMGDim);
AMO.ValidationErrorCollection errors = new AMO.ValidationErrorCollection();
newReferenceMGDim.Validate(errors, true);
if (errors.Count > 0)
{
StringBuilder errorMessages = new StringBuilder();
foreach (AMO.ValidationError err in errors)
{
errorMessages.AppendLine(string.Format("At {2}: # {0} : {1}", err.ErrorCode, err.FullErrorText, err.Source));
}
throw new AMO.AmoException(errorMessages.ToString());
}
//Update changes in the server
currentMG.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.CreateOrReplace);
}
else
{
AMO.ReferenceMeasureGroupDimension currentReferenceMGDim = (AMO.ReferenceMeasureGroupDimension)currentMG.Dimensions[PKTableName];
currentReferenceMGDim.RelationshipID = relationshipID;
currentReferenceMGDim.IntermediateGranularityAttributeID = MVColumnName;
//Update changes in the server
currentMG.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate);
}
//process MG to activate relationship
currentMG.Process(AMO.ProcessType.ProcessFull);
}
private void setPKColumn(AMO.Database tabularDb, string PKTableName, string PKColumnName)
{
//Find all 'unwanted' Key attributes, remove their Key definitions and include the attributes in the ["RowNumber"].AttributeRelationships
foreach (AMO.DimensionAttribute currentDimAttribute in tabularDb.Dimensions[PKTableName].Attributes)
{
if ((currentDimAttribute.Usage == AMO.AttributeUsage.Key) && (currentDimAttribute.ID != PKColumnName))
{
currentDimAttribute.Usage = AMO.AttributeUsage.Regular;
if (currentDimAttribute.ID != "RowNumber")
{
currentDimAttribute.KeyColumns[0].NullProcessing = AMO.NullProcessing.Preserve;
currentDimAttribute.AttributeRelationships.Clear();
if (!tabularDb.Dimensions[PKTableName].Attributes["RowNumber"].AttributeRelationships.ContainsName(currentDimAttribute.ID))
{
AMO.DimensionAttribute currentAttribute = tabularDb.Dimensions[PKTableName].Attributes[currentDimAttribute.ID];
AMO.AttributeRelationship currentAttributeRelationship = tabularDb.Dimensions[PKTableName].Attributes["RowNumber"].AttributeRelationships.Add(currentAttribute.ID);
currentAttributeRelationship.OverrideBehavior = AMO.OverrideBehavior.None;
}
tabularDb.Dimensions[PKTableName].Attributes["RowNumber"].AttributeRelationships[currentDimAttribute.ID].Cardinality = AMO.Cardinality.Many;
}
}
}
//Remove PKColumnName from ["RowNumber"].AttributeRelationships
int PKAtribRelationshipPosition = tabularDb.Dimensions[PKTableName].Attributes["RowNumber"].AttributeRelationships.IndexOf(PKColumnName);
if (PKAtribRelationshipPosition != -1) tabularDb.Dimensions[PKTableName].Attributes["RowNumber"].AttributeRelationships.RemoveAt(PKAtribRelationshipPosition, true);
//Define PKColumnName as Key and add ["RowNumber"] to PKColumnName.AttributeRelationships with cardinality of One
tabularDb.Dimensions[PKTableName].Attributes[PKColumnName].Usage = AMO.AttributeUsage.Key;
tabularDb.Dimensions[PKTableName].Attributes[PKColumnName].KeyColumns[0].NullProcessing = AMO.NullProcessing.Error;
if (!tabularDb.Dimensions[PKTableName].Attributes[PKColumnName].AttributeRelationships.ContainsName("RowNumber"))
{
AMO.DimensionAttribute currentAttribute = tabularDb.Dimensions[PKTableName].Attributes["RowNumber"];
AMO.AttributeRelationship currentAttributeRelationship = tabularDb.Dimensions[PKTableName].Attributes[PKColumnName].AttributeRelationships.Add(currentAttribute.ID);
currentAttributeRelationship.OverrideBehavior = AMO.OverrideBehavior.None;
}
tabularDb.Dimensions[PKTableName].Attributes[PKColumnName].AttributeRelationships["RowNumber"].Cardinality = AMO.Cardinality.One;
//Update Table before going creating the relationship
tabularDb.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate);
tabularDb.Dimensions[PKTableName].Process(AMO.ProcessType.ProcessDefault);
}
AMO2Tabular 範例
不過，若要了解如何使用 AMO 建立及操作關聯性表示法，請參閱 AMO 對表格式範例的原始程式碼。 您可以在 Codeplex 上取得此範例。 有關此程式碼的重要注意事項：此程式碼的提供目的只是為了支援這裡所說明的邏輯概念，不應該用於實際執行環境，也不應該用於教學以外的其他用途。