資料表表示法 (表格式)

發行項
08/01/2012

在表格式模型中，資料表是資料的基本表示法，資料會呈現在由資料列和資料行組成的矩形資料形狀中。所有資料列都具有相同的形狀或資料行數。

資料表表示法

在表格式物件模型中，資料表是一種直接明瞭的邏輯物件：相同種類的記錄集合 (稱為資料列)。每一個資料列都是由固定屬性數目所組成 (稱為資料行)，其中每一個屬性都具有簡單的資料類型。

AMO 中的資料表

當您使用 AMO 管理表格式模型資料表時，物件與 AMO 中的資料表不會有一對一的相符關係；AMO 中的資料表是由 Dimension 和 MeasureGroup 表示。但是，如果量值群組要存在，則必須定義 Cube 來裝載量值群組；此外，如果維度、量值群組和 Cube 要存在，必須定義資料來源檢視物件將繫結定義保存到資料來源。總而言之，如果所有資料表要存在，則需要通用資料來源檢視和 Cube；資料來源檢視會提供維度與量值群組中形成邏輯資料表之資料元素的繫結 (或對應)，而且需要 Cube 來裝載資料表表示法的量值群組端。

從程序觀點來看，在定義其他任何物件之前，必須先建立資料來源檢視。資料來源檢視物件包含資料來源內所有相關物件與表格式模型的對應；關聯式模型的對應會內嵌在資料來源檢視中當做 .Net 資料集物件，並儲存在 DSV 的結構描述屬性中。

下列程式碼片段假設您擁有 SQL 用戶端連接字串、Select 陳述式的字典 (這些陳述式會對應至您打算在表格式模型中表示之關聯式模型內的所有資料表)，以及包含資料來源檢視名稱的 newDataSourceViewName 變數 (通常是關聯式資料庫的名稱)。

  DataSet newDataSourceViewDataSet = new DataSet(newDataSourceViewName);

  Foreach( String tableName in listOfSqlStatements.Keys)
  {
      String sqlStmt = listOfSqlStatements[tableName];

      DataTable newTable = new DataTable(tableName);

      using (SqlConnection SqlCnx = new SqlConnection(SqlCnxStr))
      {
          SqlDataAdapter dataAdapter = new SqlDataAdapter(sqlStmt, SqlCnx);
          dataAdapter.FillSchema(newTable, SchemaType.Source);
      }
      newDataSourceViewDataSet.Tables.Add(newTable);
  }

  AMO.DataSourceView newDatasourceView = newDatabase.DataSourceViews.AddNew(newDataSourceViewName, newDataSourceViewName);
  newDatasourceView.DataSourceID = newDatasource.ID; //This is the ID of the DataSource object 
  newDatasourceView.Schema = newDataSourceViewDataSet; //Here you are storing all the relational schema in the DSV
  newDatasourceView.Update();

一旦建立及更新資料來源檢視之後，就需要建立 Cube 物件，但是要等到建立第一個資料表之後才會在伺服器中更新。建立的 Cube 物件不能是空的。下列程式碼片段示範如何建立 Cube；此程式碼片段假設您擁有非空白字串 newCubeName，其中的 Cube 名稱也已驗證是否有重複。

            modelCube = newDatabase.Cubes.Add(newCubeName, newCubeName);
            modelCube.Source = new AMO.DataSourceViewBinding(newDatasourceView.ID);
            modelCube.StorageMode = AMO.StorageMode.InMemory;
            modelCube.Language = newDatabase.Language;
            modelCube.Collation = newDatabase.Collation;
            //Create initial MdxScript
            AMO.MdxScript mdxScript = modelCube.MdxScripts.Add("MdxScript", "MdxScript");
            StringBuilder initialCommand = new StringBuilder();
            initialCommand.AppendLine("CALCULATE;");
            initialCommand.AppendLine("CREATE MEMBER CURRENTCUBE.Measures.[__No measures defined] AS 1;");
            initialCommand.AppendLine("ALTER CUBE CURRENTCUBE UPDATE DIMENSION Measures, Default_Member = [__No measures defined];");
            mdxScript.Commands.Add(new AMO.Command(initialCommand.ToString()));

一旦在本機定義 Cube 之後，就可以建立及更新資料表。以下程序概述建立資料表的必要步驟：

建立表示資料表的維度，還不要更新伺服器。
建立 RowNumber 屬性，並將它定義為維度的索引鍵屬性。
建立維度屬性，並使用與 RowNumber 之間的一對多關聯性加以標示
將維度加入至 Cube 維度
建立也表示資料表的量值群組
將維度加入至量值群組
建立量值群組的 AMO 預設量值物件。請注意，這是使用 AMO 量值物件的唯一時間；表格式模型中的導出量值會定義在 AMO MdxScripts["MdxScript"] 物件中。
建立預設資料分割
更新資料庫

下列程式碼片段示範如何建立資料表：

        private Boolean CreateTable(
                               AMO.Database db      //the AMO database object where dimension are created
                             , AMO.Cube cb          //the AMO cube where measure group is created
                             , DataTable dataTable  //the schema of the table to be created
                             )
        {
            String tableID = dataTable.TableName;

            if (db.Dimensions.Contains(tableID))
            {
                if (cb.MeasureGroups.Contains(tableID))
                {
                    cb.MeasureGroups[tableID].Measures.Clear();
                    cb.MeasureGroups[tableID].Partitions.Clear();
                    cb.MeasureGroups.Remove(tableID, true);
                }
                if (cb.Dimensions.Contains(tableID))
                {
                    cb.Dimensions.Remove(tableID, true);
                }
                db.Dimensions.Remove(tableID);
            }

            #region Create Dimension
            //Define Dimension general properties
            AMO.Dimension currentDimension = db.Dimensions.AddNew(tableID, tableID);
            currentDimension.Source = new AMO.DataSourceViewBinding(newDatasourceView.ID);
            currentDimension.StorageMode = AMO.DimensionStorageMode.InMemory;
            currentDimension.UnknownMember = AMO.UnknownMemberBehavior.AutomaticNull;
            currentDimension.UnknownMemberName = "Unknown";
            currentDimension.ErrorConfiguration = new AMO.ErrorConfiguration();
            currentDimension.ErrorConfiguration.KeyNotFound = AMO.ErrorOption.IgnoreError;
            currentDimension.ErrorConfiguration.KeyDuplicate = AMO.ErrorOption.ReportAndStop;
            currentDimension.ErrorConfiguration.NullKeyNotAllowed = AMO.ErrorOption.ReportAndStop;
            currentDimension.Language = db.Language;
            currentDimension.Collation = db.Collation;
            currentDimension.ProactiveCaching = new AMO.ProactiveCaching();
            TimeSpan defaultProactiveChachingTimeSpan = new TimeSpan(0, 0, -1);
            currentDimension.ProactiveCaching.SilenceInterval = defaultProactiveChachingTimeSpan;
            currentDimension.ProactiveCaching.Latency = defaultProactiveChachingTimeSpan;
            currentDimension.ProactiveCaching.SilenceOverrideInterval = defaultProactiveChachingTimeSpan;
            currentDimension.ProactiveCaching.ForceRebuildInterval = defaultProactiveChachingTimeSpan;
            currentDimension.ProactiveCaching.Source = new AMO.ProactiveCachingInheritedBinding();

            //Manualy add a "RowNumber" attribute as the key attribute of the dimension, until a primary key is defined
            //"RowNumber" a required column for a tabular model and has to be of type AMO.AttributeType.RowNumber and binding AMO.RowNumberBinding
            //The name of the "RowNumber" attribute can be any name, as long as type and binding are correctly set
            //By default the MS client tools set the column name and column ID of the RowNumber attribute to "RowNumber"
            //In this sample, to avoid problems, on any customer table that contains a column named 'RowNumber' 
            //the Id value of the column (in the dimension object) will be renamed to 'RowNumber_in_<TableName>' and the Name of the column will remain "RowNumber"

            AMO.DimensionAttribute currentAttribute = currentDimension.Attributes.Add("RowNumber", "RowNumber");
            currentAttribute.Type = AMO.AttributeType.RowNumber;
            currentAttribute.KeyUniquenessGuarantee = true;
            currentAttribute.Usage = AMO.AttributeUsage.Key;
            currentAttribute.KeyColumns.Add(new AMO.DataItem());
            currentAttribute.KeyColumns[0].DataType = System.Data.OleDb.OleDbType.Integer;
            currentAttribute.KeyColumns[0].DataSize = 4;
            currentAttribute.KeyColumns[0].NullProcessing = AMO.NullProcessing.Error;
            currentAttribute.KeyColumns[0].Source = new AMO.RowNumberBinding();
            currentAttribute.NameColumn = new AMO.DataItem();
            currentAttribute.NameColumn.DataType = System.Data.OleDb.OleDbType.WChar;
            currentAttribute.NameColumn.DataSize = 4;
            currentAttribute.NameColumn.NullProcessing = AMO.NullProcessing.ZeroOrBlank;
            currentAttribute.NameColumn.Source = new AMO.RowNumberBinding();
            currentAttribute.OrderBy = AMO.OrderBy.Key;
            currentAttribute.AttributeHierarchyVisible = false;
            //Deferring AttributeRelationships until after adding each other attribute
            //Add each column in the table as an attribute in the dimension
            foreach (DataColumn dataColumn in dataTable.Columns)
            {
                string attributeID, attributeName;
                if (dataColumn.ColumnName != "RowNumber")
                {
                    attributeID = dataColumn.ColumnName;
                }
                else
                {
                    attributeID = string.Format("RowNumber_in_{0}", dataTable.TableName);
                }
                attributeName = dataColumn.ColumnName;
                currentAttribute = currentDimension.Attributes.Add(attributeName, attributeID);
                currentAttribute.Usage = AMO.AttributeUsage.Regular;
                currentAttribute.KeyUniquenessGuarantee = false;
                currentAttribute.KeyColumns.Add(new AMO.DataItem(dataTable.TableName, dataColumn.ColumnName, AMO.OleDbTypeConverter.GetRestrictedOleDbType(dataColumn.DataType)));
                currentAttribute.KeyColumns[0].Source = new AMO.ColumnBinding(dataTable.TableName, dataColumn.ColumnName);
                currentAttribute.KeyColumns[0].NullProcessing = AMO.NullProcessing.Preserve;
                currentAttribute.NameColumn = new AMO.DataItem(dataTable.TableName, dataColumn.ColumnName, System.Data.OleDb.OleDbType.WChar);
                currentAttribute.NameColumn.Source = new AMO.ColumnBinding(dataTable.TableName, dataColumn.ColumnName);
                currentAttribute.NameColumn.NullProcessing = AMO.NullProcessing.ZeroOrBlank;
                currentAttribute.OrderBy = AMO.OrderBy.Key;
                AMO.AttributeRelationship currentAttributeRelationship = currentDimension.Attributes["RowNumber"].AttributeRelationships.Add(currentAttribute.ID);
                currentAttributeRelationship.Cardinality = AMO.Cardinality.Many;
                currentAttributeRelationship.OverrideBehavior = AMO.OverrideBehavior.None;
            }
            #endregion

            #region Add Dimension to Model cube
            cb.Dimensions.Add(tableID, tableID, tableID);
            #endregion

            #region Add MeasureGroup to Model cube
            AMO.MeasureGroup currentMeasureGroup = cb.MeasureGroups.Add(tableID, tableID);
            currentMeasureGroup.StorageMode = AMO.StorageMode.InMemory;
            currentMeasureGroup.ProcessingMode = AMO.ProcessingMode.Regular;

            //Adding Dimension
            AMO.DegenerateMeasureGroupDimension currentMGDim = new AMO.DegenerateMeasureGroupDimension(tableID);
            currentMeasureGroup.Dimensions.Add(currentMGDim);
            currentMGDim.ShareDimensionStorage = AMO.StorageSharingMode.Shared;
            currentMGDim.CubeDimensionID = tableID;
            foreach (AMO.CubeAttribute ca in cb.Dimensions[tableID].Attributes)
            {
                AMO.MeasureGroupAttribute mga = new AMO.MeasureGroupAttribute(ca.AttributeID);
                if (mga.AttributeID == "RowNumber")
                {
                    mga.Type = AMO.MeasureGroupAttributeType.Granularity;
                    AMO.DataItem rowNumberKeyColumn = new AMO.DataItem(new AMO.ColumnBinding(tableID, "RowNumber"));
                    rowNumberKeyColumn.DataType = System.Data.OleDb.OleDbType.Integer;
                    mga.KeyColumns.Add(rowNumberKeyColumn);
                }
                else
                {
                    foreach (AMO.DataItem di in ca.Attribute.KeyColumns)
                    {
                        AMO.DataItem keyColumn = new AMO.DataItem(new AMO.ColumnBinding(tableID, ((AMO.ColumnBinding)di.Source).ColumnID));
                        keyColumn.DataType = di.DataType;
                        keyColumn.NullProcessing = AMO.NullProcessing.Preserve;
                        keyColumn.InvalidXmlCharacters = AMO.InvalidXmlCharacters.Remove;
                        mga.KeyColumns.Add(keyColumn);
                    }
                }
                currentMGDim.Attributes.Add(mga);
            }

            //Adding default Measure
            String defaultMeasureID = string.Concat("_Count ", tableID);
            AMO.Measure currentMeasure = currentMeasureGroup.Measures.Add(defaultMeasureID, defaultMeasureID);
            currentMeasure.AggregateFunction = AMO.AggregationFunction.Count;
            currentMeasure.DataType = AMO.MeasureDataType.BigInt;
            AMO.DataItem currentMeasureSource = new AMO.DataItem(new AMO.RowBinding(tableID));
            currentMeasureSource.DataType = System.Data.OleDb.OleDbType.BigInt;
            currentMeasure.Source = currentMeasureSource;

            //Partitions
            AMO.Partition currentPartition = new AMO.Partition(tableID, tableID);
            currentPartition.StorageMode = AMO.StorageMode.InMemory;
            currentPartition.ProcessingMode = AMO.ProcessingMode.Regular;
            currentPartition.Source = new AMO.QueryBinding(newDatasource.ID, (String)dataTable.ExtendedProperties["sqlStmt"]);
            currentMeasureGroup.Partitions.Add(currentPartition);

            #endregion

            #region Update new objects in database
            db.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate);
            #endregion

            return true;
        }

注意
上述程式碼片段在失敗時並沒有錯誤檢查或清除程序。

AMO2Tabular 範例

若要了解如何使用 AMO 建立及操作資料庫表示法，請參閱 AMO 對表格式範例的原始程式碼，特別要檢查以下的原始程式檔：CreateTable.cs。您可以在 Codeplex 上取得此範例。有關此程式碼的重要注意事項：此程式碼的提供目的只是為了支援這裡所說明的邏輯概念，不應該用於實際執行環境，也不應該用於教學以外的其他用途。

共用方式為

資料表表示法 (表格式)