Example #1
0
 public bool VerifySelections()
 {
     try
     {
         if (VerifyServer())
         {
             if (srv.Databases.ContainsName(cmbDatabase.Text))
                 db = srv.Databases.GetByName(cmbDatabase.Text);
             else 
                 return false;
             if (db == null || !db.Dimensions.ContainsName(cmbDimension.Text))
                 return false;
             dim = db.Dimensions.GetByName(cmbDimension.Text);
             for (int i = 0; i < PCAttributesToInclude.Count; i++)
                 if (!dim.Attributes.ContainsName(PCAttributesToInclude[i]))
                 {
                     PCAttributesToInclude.RemoveAt(i);
                     i--;
                 }
             for (int i = 0; i < NonPCHierarchiesToInclude.Count; i++)
                 if (!dim.Attributes.ContainsName(NonPCHierarchiesToInclude[i]))
                 {
                     NonPCHierarchiesToInclude.RemoveAt(i);
                     i--;
                 }
             return true;
         }
         return false;
     }
     catch (Exception)
     {
         return false;
     }
 }
        /// <summary>
        /// Extracts column names of the specified table from database object
        /// </summary>
        /// <param name="tableName">Table name that owns columns function should return</param>
        /// <returns>Returns list of column names</returns>
        /// <exception cref="System.ArgumentException">Thrown when table name is not found in database.</exception>
        /// <exception cref="System.InvalidOperationException">Thrown when database object is null.</exception>
        public List <String> GetColumnNames(string tableName)
        {
            if (null == this._database)
            {
                throw new InvalidOperationException("Current database is NULL. GetColumnNames function cannot be called when database object is NULL");
            }

            List <string> columnNames = new List <string>();

            Microsoft.AnalysisServices.Dimension dim = this._database.Dimensions.FindByName(tableName);
            if (null == dim)
            {
                throw new ArgumentException(string.Format("Table '{0}' was not found in database {1}", tableName, this._database.Name), "tableName");
            }

            foreach (DimensionAttribute dimAttr in dim.Attributes)
            {
                if (_rowNumberColumnNames.Any(i => { return(string.Equals(i, dimAttr.Name, StringComparison.CurrentCultureIgnoreCase)); }))
                {
                    continue; // Hide the RowNumber column
                }
                columnNames.Add(dimAttr.Name);
            }

            return(columnNames);
        }
        internal static bool CreateRelationship(TOM.SingleColumnRelationship TOMRelationship, AMO.Database Database)
        {
            AMO.Dimension          ToDimension   = Database.Dimensions[TOMRelationship.ToTable.Name];
            AMO.DimensionAttribute ToAttribute   = ToDimension.Attributes[TOMRelationship.ToColumn.Name];
            AMO.Dimension          FromDimension = Database.Dimensions[TOMRelationship.FromTable.Name];
            if (ToDimension.Attributes[TOMRelationship.ToColumn.Name].Usage != AMO.AttributeUsage.Key)
            {
                SetPKColumn(Database, ToAttribute);
            }

            string RelationshipID = System.Guid.NewGuid().ToString();

            AMO.Relationship AMORelationship = Database.Dimensions[TOMRelationship.FromTable.Name].Relationships.Add(RelationshipID);

            AMORelationship.FromRelationshipEnd.DimensionID = TOMRelationship.FromTable.Name;
            AMORelationship.FromRelationshipEnd.Attributes.Add(TOMRelationship.FromColumn.Name);
            AMORelationship.FromRelationshipEnd.Multiplicity = AMO.Multiplicity.Many;
            AMORelationship.FromRelationshipEnd.Role         = string.Empty;
            AMORelationship.ToRelationshipEnd.DimensionID    = TOMRelationship.ToTable.Name;
            AMORelationship.ToRelationshipEnd.Attributes.Add(TOMRelationship.ToColumn.Name);
            AMORelationship.ToRelationshipEnd.Multiplicity = AMO.Multiplicity.One;
            AMORelationship.ToRelationshipEnd.Role         = string.Empty;

            if (TOMRelationship.IsActive)
            {
                setActiveRelationship(Database.Cubes[0], TOMRelationship.FromTable.Name, TOMRelationship.FromColumn.Name, TOMRelationship.ToTable.Name, RelationshipID);
            }

            return(true);
        }
Example #4
0
 public bool VerifySelections()
 {
     try
     {
         if (VerifyServer())
         {
             if (srv.Databases.ContainsName(cmbDatabase.Text))
             {
                 db = srv.Databases.GetByName(cmbDatabase.Text);
             }
             else
             {
                 return(false);
             }
             if (db == null || !db.Dimensions.ContainsName(cmbDimension.Text))
             {
                 return(false);
             }
             dim = db.Dimensions.GetByName(cmbDimension.Text);
             for (int i = 0; i < PCAttributesToInclude.Count; i++)
             {
                 if (!dim.Attributes.ContainsName(PCAttributesToInclude[i]))
                 {
                     PCAttributesToInclude.RemoveAt(i);
                     i--;
                 }
             }
             for (int i = 0; i < NonPCHierarchiesToInclude.Count; i++)
             {
                 if (!dim.Attributes.ContainsName(NonPCHierarchiesToInclude[i]))
                 {
                     NonPCHierarchiesToInclude.RemoveAt(i);
                     i--;
                 }
             }
             return(true);
         }
         return(false);
     }
     catch (Exception)
     {
         return(false);
     }
 }
Example #5
0
        public static bool PerspectiveContainsHierarchy(AMO.Database tabularDatabase,
                                                        string perspectiveName,
                                                        string tableName,
                                                        string hierarchyName)
        {
            #region Validate input arguments and other initial preparations
            //  Validate required input arguments
            if (tabularDatabase == null)
            {
                throw new ArgumentNullException(TabularDatabaseStringName);
            }
            if (perspectiveName.IsNullOrEmptyOrWhitespace())
            {
                throw new ArgumentNullException(PerspectiveStringName);
            }
            if (tableName.IsNullOrEmptyOrWhitespace())
            {
                throw new ArgumentNullException(TableStringName);
            }
            if (hierarchyName.IsNullOrEmptyOrWhitespace())
            {
                throw new ArgumentNullException(HierarchyStringName);
            }
            if (!IsDatabaseCompatibilityLevelCorrect(tabularDatabase))
            {
                throw new InvalidOperationException(Resources.InvalidCompatibilityLevelOperationException);
            }

            //  Other initial preparations
            //  -   Cleaning and preparing name variables
            perspectiveName = perspectiveName.Trim();
            tableName       = tableName.Trim();
            hierarchyName   = hierarchyName.Trim();
            #endregion

            using (AMO.Dimension tableDimension = tabularDatabase.Dimensions.GetByName(tableName))
            {
                string tableId     = tableDimension.ID;
                string hierarchyId = tableDimension.Hierarchies.GetByName(hierarchyName).ID;

                return(tabularDatabase.Cubes[0].Perspectives.GetByName(perspectiveName).Dimensions[tableId].Hierarchies.Contains(hierarchyId));
            }
        }
        /// <summary>
        /// Create cube dimension
        /// </summary>
        /// <param name="sqlHelper"></param>
        /// <param name="asMeta"></param>
        /// <param name="cubeDB"></param>
        /// <param name="DSV"></param>
        public void CREATE_CUBE_DIMENSION(DB_SQLHELPER_BASE sqlHelper
                                          , AS_METADATA asMeta
                                          , Microsoft.AnalysisServices.Database cubeDB
                                          , Microsoft.AnalysisServices.DataSourceView DSV
                                          )
        {
            try
            {
                sqlHelper.ADD_MESSAGE_LOG("[Create dimension] Starting create dimension"
                                          , MESSAGE_TYPE.DIMENSION
                                          , MESSAGE_RESULT_TYPE.Normal);
                DataTable DimensionSet = asMeta.GET_SSAS_DIMENSION_SET(sqlHelper);
                foreach (DataRow dimension_row in DimensionSet.Rows)
                {
                    String DimensionID    = dimension_row["dimension_id"].ToString();
                    String DimensionName  = dimension_row["dimension_name"].ToString();
                    String DimensionType  = dimension_row["dimension_type"].ToString();
                    String DataSourceName = dimension_row["dsv_schema_name"].ToString();
                    String dsvName        = DSV.Name;
                    Microsoft.AnalysisServices.Dimension dim = AS_API.ADD_DIMENSION(sqlHelper, cubeDB, dsvName, DimensionID, DimensionName, DimensionType);

                    DataTable AttributeSet = asMeta.GET_SSAS_ATTRIBUTES_SET(sqlHelper, DimensionID);
                    if (AttributeSet == null || AttributeSet.Rows == null || AttributeSet.Rows.Count == 0)
                    {
                        sqlHelper.ADD_MESSAGE_LOG(
                            String.Format("[Create dimension] Dimension {0} has not any attributes, is it expected?", DimensionID)
                            , MESSAGE_TYPE.DIMENSION
                            , MESSAGE_RESULT_TYPE.Warning);
                    }
                    else
                    {
                        sqlHelper.ADD_MESSAGE_LOG(
                            String.Format("[Create dimension] Adding {0} attributeds for dimension {1}", AttributeSet.Rows.Count.ToString(), DimensionID)
                            , MESSAGE_TYPE.DIMENSION
                            , MESSAGE_RESULT_TYPE.Normal);
                    }
                    foreach (DataRow attribute_row in AttributeSet.Rows)
                    {
                        String AttributeID    = attribute_row["attribute_id"].ToString();
                        String AttributeName  = attribute_row["attribbute_name"].ToString();
                        String DSVSchemaName  = attribute_row["dsv_schema_name"].ToString();
                        String DBColumn       = attribute_row["key_column_db_column"].ToString();
                        String OleDbType      = attribute_row["key_column_oledb_type"].ToString();
                        String AttributeUsage = attribute_row["attribute_usage"].ToString();
                        String NameColumn     = attribute_row["name_column"].ToString();
                        String Visible        = attribute_row["visible"].ToString();
                        String AttHierEnabled = attribute_row["atthier_enabled"].ToString();
                        String OrderBy        = attribute_row["order_by"].ToString();
                        Microsoft.AnalysisServices.OrderBy attribute_order_by = Microsoft.AnalysisServices.OrderBy.Name;
                        if (OrderBy.ToLower() == "key")
                        {
                            attribute_order_by = Microsoft.AnalysisServices.OrderBy.Key;
                        }
                        AS_API.ADD_ATTRIBUTE_TO_DIMENSION(
                            sqlHelper,
                            DSV,
                            dim,
                            DataSourceName,
                            DBColumn,
                            AttributeID,
                            AttributeName,
                            AS_API_HELPER.GET_SSAS_OLEDB_TYPE_BY_NAME(OleDbType),
                            AS_API_HELPER.GET_SSAS_ATTRIBUTE_USAGE_BY_NAME(AttributeUsage),
                            NameColumn, Convert.ToBoolean(Visible),
                            Convert.ToBoolean(AttHierEnabled),
                            attribute_order_by
                            );
                    }

                    DataTable AttributeRelationShipSet = asMeta.GET_SSAS_ATTRIBUTE_RELATION_SHIPS_SET(sqlHelper, DimensionID);

                    sqlHelper.ADD_MESSAGE_LOG(String.Format("[Create dimension] Adding {0} attribute relationships for dimension {1}", AttributeRelationShipSet.Rows.Count.ToString(), DimensionID)
                                              , MESSAGE_TYPE.ATTRIBUTE_RELATIONSHIP
                                              , MESSAGE_RESULT_TYPE.Normal);

                    foreach (DataRow row in AttributeRelationShipSet.Rows)
                    {
                        String BasedAttributeID   = row["based_attribute_id"].ToString();
                        String RelatedAttributeID = row["related_attribute_id"].ToString();
                        String RelationShipType   = row["relationship_type"].ToString();
                        Microsoft.AnalysisServices.RelationshipType AttributeRelationShipType = AS_API_HELPER.GET_SSAS_ATTRIBUTE_RELATION_SHIP_TYPE_BY_NAME(RelationShipType);

                        AS_API.ADD_ATTRIBUTE_RELATIONSHIP(
                            sqlHelper,
                            dim,
                            BasedAttributeID,
                            RelatedAttributeID,
                            AttributeRelationShipType);
                    }


                    DataTable HierarchiesSet = asMeta.GET_SSAS_HIERARCHIES_SET(sqlHelper, DimensionID);

                    sqlHelper.ADD_MESSAGE_LOG(
                        String.Format("[Create dimension] Adding {0} hierarchy levels for dimension {1}", HierarchiesSet.Rows.Count.ToString(), DimensionID)
                        , MESSAGE_TYPE.HIERARCHIES
                        , MESSAGE_RESULT_TYPE.Normal);
                    foreach (DataRow row in HierarchiesSet.Rows)
                    {
                        String HierarchyName     = row["hierarchy_name"].ToString();
                        String LevelName         = row["level_name"].ToString();
                        String LevelID           = row["level_id"].ToString();
                        String SourceAttributeID = row["source_attribute_id"].ToString();
                        AS_API.ADD_ATTRIBUTE_HIERACHIES(
                            sqlHelper,
                            dim,
                            HierarchyName,
                            LevelName,
                            SourceAttributeID);
                        sqlHelper.ADD_MESSAGE_LOG(
                            "[Create dimension->Hierarchy] |" + new string('_', Convert.ToInt16(LevelID)) + LevelName
                            , MESSAGE_TYPE.HIERARCHIES
                            , MESSAGE_RESULT_TYPE.Normal);
                    }
                    sqlHelper.ADD_MESSAGE_LOG(
                        "[Create dimension] Updating changes of dimension objects.."
                        , MESSAGE_TYPE.DIMENSION
                        , MESSAGE_RESULT_TYPE.Normal);
                    dim.Update();
                    sqlHelper.ADD_MESSAGE_LOG(
                        "[Create dimension] Succeed to add changes to dimension objects.."
                        , MESSAGE_TYPE.DIMENSION
                        , MESSAGE_RESULT_TYPE.Normal);
                }
            }
            catch (Exception ex)
            {
                sqlHelper.ADD_MESSAGE_LOG("[Create dimension] " + ex.Message.ToString(), MESSAGE_TYPE.ADD_DIMENSION, MESSAGE_RESULT_TYPE.Error);
                throw (ex);
            }
        }
Example #7
0
        public static void PerspectiveAlterHierarchyAdd(AMO.Database tabularDatabase,
                                                        string perspectiveName,
                                                        string tableName,
                                                        string hierarchyName,
                                                        bool updateInstance = true)
        {
            //  Major steps in adding a Hierarchy to a Perspective in the database
            //
            //  - Validate required input arguments
            //  - Other Initial preparations
            //  - Adding hierarchy of table to perspective
            //
            //  Note: There are no validations for duplicated names, invalid names or
            //  similar scenarios. It is expected the server will take care of them and
            //  throw exceptions on any invalid situation.
            //
            //  Note:   In AMO, strings as indexers refer to the ID of the object, not the Name of the object
            //
            //  Note:   Only one DataSourceView is used in Tabular Models
            //          ==> tabularDatabase.DataSourceViews[0] represents the DSV of the model
            //
            //  Note:   Only one Cube is used in Tabular Models
            //          ==> tabularDatabase.Cubes[0] represents the cube in the model
            //
            //  Note:   Microsoft design tools use the following pattern to keep track of the
            //          datasource matching elements:
            //          DataSourceView->TableName <---> Dimension.ID, MeasureGroup.ID
            //          DataSourceView->ColumnName <---> Dimension->ColumnID, MeasureGroup.DegeneratedDimension->CoumnID
            //          So far, this sample follows the same pattern.
            //
            //          WARNING:    Breaking the above pattern when creating your
            //                      own AMO to Tabular functions might lead to
            //                      unpredictable behavior when using Microsoft
            //                      Design tools in your models.

            #region Validate input arguments and other initial preparations
            //  Validate required input arguments
            if (tabularDatabase == null)
            {
                throw new ArgumentNullException(TabularDatabaseStringName);
            }
            if (perspectiveName.IsNullOrEmptyOrWhitespace())
            {
                throw new ArgumentNullException(PerspectiveStringName);
            }
            if (tableName.IsNullOrEmptyOrWhitespace())
            {
                throw new ArgumentNullException(TableStringName);
            }
            if (hierarchyName.IsNullOrEmptyOrWhitespace())
            {
                throw new ArgumentNullException(HierarchyStringName);
            }
            if (!IsDatabaseCompatibilityLevelCorrect(tabularDatabase))
            {
                throw new InvalidOperationException(Resources.InvalidCompatibilityLevelOperationException);
            }

            //  Other initial preparations
            //  -   Cleaning and preparing name variables
            perspectiveName = perspectiveName.Trim();
            tableName       = tableName.Trim();
            hierarchyName   = hierarchyName.Trim();
            #endregion

            using (AMO.Dimension tableDimension = tabularDatabase.Dimensions.GetByName(tableName))
            {
                string tableId     = tableDimension.ID;
                string hierarchyId = tableDimension.Hierarchies.GetByName(hierarchyName).ID;

                using (AMO.Perspective perspective = tabularDatabase.Cubes[0].Perspectives.GetByName(perspectiveName))
                    using (AMO.PerspectiveDimension perspectiveTable = perspective.Dimensions[tableId])
                    {
                        perspectiveTable.Hierarchies.Add(hierarchyId);
                    }
            }


            //  Update server instance
            if (updateInstance)
            {
                tabularDatabase.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate);
            }
        }
Example #8
0
        public static void CalculatedColumnAdd(AMO.Database tabularDatabase,
                                               string tableName,
                                               string columnName,
                                               string daxExpression,
                                               bool updateInstance               = true,
                                               ColumnInfo?columnProperties       = null,
                                               ReportingInfo?reportingProperties = null,
                                               AMO.ProcessType?processType       = null)
        {
            //  Major steps in adding a calculated calculatedColumn to a table in the database
            //
            //  - Validate required input arguments
            //  - Other Initial preparations
            //  - Adding calculatedColumn as attribute to dimension
            //  - Adding calculatedColumn as attribute to degenerated dimension in Measure Group
            //  - Set calculatedColumn properties according to optional parameters
            //  - Set reporting properties according to optional parameters
            //  - Process table/database according to optional parameters

            //
            //  Note: There are no validations for duplicated names, invalid names or
            //  similar scenarios. It is expected the server will take care of them and
            //  throw exceptions on any invalid situation.
            //
            //  Note:   In AMO, strings as indexers refer to the ID of the object, not the Name of the object
            //
            //  Note:   Only one DataSourceView is used in Tabular Models
            //          ==> tabularDatabase.DataSourceViews[0] represents the DSV of the model
            //
            //  Note:   Only one Cube is used in Tabular Models
            //          ==> tabularDatabase.Cubes[0] represents the cube in the model
            //
            //  Note:   Microsoft design tools use the following pattern to keep track of the
            //          datasource matching elements:
            //          DataSourceView->TableName <---> Dimension.ID, MeasureGroup.ID
            //          DataSourceView->ColumnName <---> Dimension->ColumnID, MeasureGroup.DegeneratedDimension->CoumnID
            //          So far, this sample follows the same pattern.
            //
            //          WARNING:    Breaking the above pattern when creating your
            //                      own AMO to Tabular functions might lead to
            //                      unpredictable behavior when using Microsoft
            //                      Design tools in your models.
            //
            //  Note:   There are no validations on the ProcessType requested and whatever value is passed it's used
            //
            //  Note:   For Calculated Columns, in tabular models, the following ProcessType values are 'valid' or have sense:
            //          -   ProcessDefault  ==> (issued at table level) verifies if a data (at partition level) or recalc is
            //                                  required and issues coresponding internal process tasks
            //          -   ProcessFull     ==> (issued at table level) forces data upload (on all partitions) and recalc,
            //                                  regardless of table status
            //          -   ProcessRecalc   ==> (issued at Database level) forces a recalc of internal structures (measures,
            //                                  calculated columns, hierarchies, etc.) at database level; doesn't load
            //                                  new data.
            //
            //  Note:   Issuing a process request (setting parameter processType != null) forces a database update

            #region Validate input arguments and other initial preparations
            //  Validate required input arguments
            if (tabularDatabase == null)
            {
                throw new ArgumentNullException(TabularDatabaseStringName);
            }
            if (tableName.IsNullOrEmptyOrWhitespace())
            {
                throw new ArgumentNullException(TableStringName);
            }
            if (columnName.IsNullOrEmptyOrWhitespace())
            {
                throw new ArgumentNullException(ColumnStringName);
            }
            if (daxExpression.IsNullOrEmptyOrWhitespace())
            {
                throw new ArgumentNullException(DaxExpressionStringName);
            }
            if (!IsDatabaseCompatibilityLevelCorrect(tabularDatabase))
            {
                throw new  InvalidOperationException(Resources.InvalidCompatibilityLevelOperationException);
            }

            //  Other initial preparations
            //  -   Cleaning and preparing name variables
            tableName     = tableName.Trim();
            columnName    = columnName.Trim();
            daxExpression = daxExpression.Trim();

            //  -   Obtain table name in DSV
            string datasourceTableName = tabularDatabase.Dimensions.GetByName(tableName).ID;

            //  -   Obtain "RowNumber" column id
            string rowNumberColumnId = string.Empty;
            foreach (AMO.DimensionAttribute da in tabularDatabase.Dimensions[datasourceTableName].Attributes)
            {
                if (da.Type == AMO.AttributeType.RowNumber)
                {
                    rowNumberColumnId = da.ID;
                    break;
                }
            }

            #endregion

            // Add calculated calculatedColumn as attribute to the Dimension
            //      Note: datasourceTableName == tableId; because parity with DS object names needs to be kept
            using (AMO.Dimension tableDimension = tabularDatabase.Dimensions[datasourceTableName])
                using (AMO.DimensionAttribute calculatedColumnDimensionAttribute = tableDimension.Attributes.Add(columnName, columnName))
                    using (AMO.DataItem dataItemEmptyType = new AMO.DataItem(datasourceTableName, columnName, System.Data.OleDb.OleDbType.Empty))
                        using (AMO.ExpressionBinding expressionBinding = new AMO.ExpressionBinding(daxExpression))
                            using (AMO.DataItem dataItemWCharType = new AMO.DataItem(datasourceTableName, columnName, System.Data.OleDb.OleDbType.WChar))
                            {
                                calculatedColumnDimensionAttribute.Usage = AMO.AttributeUsage.Regular;
                                calculatedColumnDimensionAttribute.KeyUniquenessGuarantee = false;
                                calculatedColumnDimensionAttribute.KeyColumns.Add(dataItemEmptyType);
                                calculatedColumnDimensionAttribute.KeyColumns[0].Source         = expressionBinding.Clone();
                                calculatedColumnDimensionAttribute.KeyColumns[0].NullProcessing = AMO.NullProcessing.Preserve;
                                calculatedColumnDimensionAttribute.NameColumn                = dataItemWCharType;
                                calculatedColumnDimensionAttribute.NameColumn.Source         = expressionBinding;
                                calculatedColumnDimensionAttribute.NameColumn.NullProcessing = AMO.NullProcessing.ZeroOrBlank;
                                calculatedColumnDimensionAttribute.OrderBy = AMO.OrderBy.Key;
                                using (AMO.AttributeRelationship calculatedColumnDimensionAttributeRelationship = tableDimension.Attributes[rowNumberColumnId].AttributeRelationships.Add(calculatedColumnDimensionAttribute.ID))
                                {
                                    calculatedColumnDimensionAttributeRelationship.Cardinality      = AMO.Cardinality.Many;
                                    calculatedColumnDimensionAttributeRelationship.OverrideBehavior = AMO.OverrideBehavior.None;
                                }
                            }
            //  Add calculatedColumn as attribute to the MG, in the DegeneratedMeasureGroupDimension
            using (AMO.MeasureGroup tableMeasureGroup = tabularDatabase.Cubes[0].MeasureGroups[datasourceTableName])
                using (AMO.DegenerateMeasureGroupDimension tableMGDimension = (AMO.DegenerateMeasureGroupDimension)tableMeasureGroup.Dimensions[datasourceTableName])
                    using (AMO.MeasureGroupAttribute calculatedColumnMGAttribute = new AMO.MeasureGroupAttribute(columnName))
                        using (AMO.DataItem dataItemEmptyType = new AMO.DataItem(datasourceTableName, columnName, System.Data.OleDb.OleDbType.Empty))
                            using (AMO.ExpressionBinding expressionBinding = new AMO.ExpressionBinding(daxExpression))
                            {
                                calculatedColumnMGAttribute.KeyColumns.Add(dataItemEmptyType);
                                calculatedColumnMGAttribute.KeyColumns[0].Source = expressionBinding;
                                tableMGDimension.Attributes.Add(calculatedColumnMGAttribute);
                            }

            //  Set/update optional calculatedColumn properties
            if (columnProperties != null)
            {
                if (!columnProperties.Value.DataFormat.IsNullOrEmptyOrWhitespace())
                {
                    ColumnAlterFormat(tabularDatabase, tableName, columnName, columnProperties.Value.DataFormat);
                }

                if (columnProperties.Value.DataType != DataType.Default && columnProperties.Value.DataType != DataType.Unsupported)
                {
                    ColumnAlterDataType(tabularDatabase, tableName, columnName, columnProperties.Value.DataType);
                }

                if (columnProperties.Value.Visible != null)
                {
                    ColumnAlterVisibility(tabularDatabase, tableName, columnName, columnProperties.Value.Visible.Value);
                }

                if (!columnProperties.Value.SortByColumn.IsNullOrEmptyOrWhitespace())
                {
                    ColumnAlterSortByColumnName(tabularDatabase, tableName, columnName, columnProperties.Value.SortByColumn);
                }
            }

            //  ToDo: Set/update optional reporting properties
            //if (reportingProperties != null)
            //{
            //}

            //  Update server instance
            if (updateInstance)
            {
                tabularDatabase.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate);
            }


            if (processType != null)
            {
                //  Throw exception if server instance is outdated and user requests process
                if (!updateInstance)
                {
                    throw new InvalidOperationException(Resources.ProcessRequestedForOutdatedModelInvalidOperationException);
                }


                //  Now the table, that contains the Calculated Column, can be processed according to the user request
                TableProcess(tabularDatabase, tableName, (AMO.ProcessType)processType);

                //  Calculated columns require a database level process recalc
                tabularDatabase.Process(AMO.ProcessType.ProcessRecalc);
            }
        }
Example #9
0
        //TODO: Add direct query support

        /* Complete feature list, to be noted when complete support added
         *  - Database
         *      - Direct Query
         *      - Datasources (Done)
         *      - Tables (Done)
         *          - Translation of table (Done)
         *          - Attributes (Done)
         *              - Translation of Attribute (Done)
         *          - Hierarchies (Done)
         *              - Translation of Hierarchies (Done)
         *              - Levels (Done)
         *                  - Translation of Levels (Done)
         *      - Measures (Done)
         *          - Translation of Measures (Done)
         *          - KPI's (Done)
         *      - Perspectives (Done)
         *      - Roles (Done)
         *          - Row Level Security (Done)
         *          - Members (Done)
         *      - Relationships (Done)
         */
        /// <summary>
        /// Given a 1200 or 1400 model, produces an equivelant 1103 model. Incompatible features are not added.
        /// </summary>
        /// <param name="TOMDatabase"></param>
        /// <returns></returns>
        public static AMO.Database ToAMODatabase(TOM.Database TOMDatabase)
        {
            /* The Database representing a Tabular model rarely has a 1 to 1 mapping between
             * a TOM object and an AMO object.
             *
             * Some objects which DO have 1:1 mappings (at least, in a logical sense):
             *  - DataSources
             *  - Hierarchies
             *  - Levels
             *  - Perspectives
             *  - Partitions
             *  - Translations (however, they are structured differently)
             *  - Annotations (These can also accept XML nodes, not just strings)
             *
             * The following do NOT:
             *  - Tables
             *  - Columns
             *  - Measures
             *  - Relationships
             *  - Roles
             *
             *  The following is, surprisingly, supported:
             *  - Tabular Actions
             *  - HideMemberIf
             *  - Display Folders
             *  - Custom Format strings
             *  - Translations
             *  - Similar to 1200, more than one column with the same source column
             *
             * Please note this does NOT produce a file able to be opened by Visual Studio.
             * Unlike 1200 models, Visual Studio cannot open all valid 1103 models.
             * It relies heavily on custom annotations, understood mainly through trial and error.
             *
             * In addition to these custom annotations, some features have no alternative (custom format strings), and
             * are completely unsupported by both Visual Studio and BIDS.
             *
             * Display Folders, Tabular Actions, and Translations are all supported using BIDS.
             *
             * These drastically increase the database/file size, and as such are optionally added in a seperate function.
             */

            TOM.Model TOMModel = TOMDatabase.Model;

            //Create Database
            AMO.Database AMODatabase = new AMO.Database(TOMDatabase.Name, TOMDatabase.Name);
            //Initialise with default values.
            AMODatabase.StorageEngineUsed  = AMO.StorageEngineUsed.InMemory;
            AMODatabase.CompatibilityLevel = SQL2012SP1;

            //DataSource has 1:1 mapping with AMO object
            #region DataSources
            foreach (TOM.ProviderDataSource TOMDataSource in TOMModel.DataSources)
            {
                AMO.DataSource AMODataSource = new AMO.RelationalDataSource(TOMDataSource.Name, TOMDataSource.Name);
                AMODataSource.Description       = TOMDataSource.Description;
                AMODataSource.ConnectionString  = TOMDataSource.ConnectionString;
                AMODataSource.ImpersonationInfo = new AMO.ImpersonationInfo();
                switch (TOMDataSource.ImpersonationMode)
                {
                case TOM.ImpersonationMode.Default:
                    AMODataSource.ImpersonationInfo.ImpersonationMode = AMO.ImpersonationMode.Default;
                    break;

                case TOM.ImpersonationMode.ImpersonateAccount:
                    AMODataSource.ImpersonationInfo.ImpersonationMode = AMO.ImpersonationMode.ImpersonateAccount;
                    break;

                case TOM.ImpersonationMode.ImpersonateAnonymous:
                    AMODataSource.ImpersonationInfo.ImpersonationMode = AMO.ImpersonationMode.ImpersonateAnonymous;
                    break;

                case TOM.ImpersonationMode.ImpersonateCurrentUser:
                    AMODataSource.ImpersonationInfo.ImpersonationMode = AMO.ImpersonationMode.ImpersonateCurrentUser;
                    break;

                case TOM.ImpersonationMode.ImpersonateServiceAccount:
                    AMODataSource.ImpersonationInfo.ImpersonationMode = AMO.ImpersonationMode.ImpersonateServiceAccount;
                    break;

                case TOM.ImpersonationMode.ImpersonateUnattendedAccount:
                    AMODataSource.ImpersonationInfo.ImpersonationMode = AMO.ImpersonationMode.ImpersonateUnattendedAccount;
                    break;
                }
                AMODataSource.ImpersonationInfo.Account  = TOMDataSource.Account;
                AMODataSource.ImpersonationInfo.Password = TOMDataSource.Password;
                switch (TOMDataSource.Isolation)
                {
                case TOM.DatasourceIsolation.ReadCommitted:
                    AMODataSource.Isolation = AMO.DataSourceIsolation.ReadCommitted;
                    break;

                case TOM.DatasourceIsolation.Snapshot:
                    AMODataSource.Isolation = AMO.DataSourceIsolation.Snapshot;
                    break;
                }
                //1 "tick" = 100 nanoseconds = 1*10^-7 seconds.
                AMODataSource.Timeout = new TimeSpan(TOMDataSource.Timeout * 10000000);
                AMODatabase.DataSources.Add(AMODataSource);
            }
            #endregion

            /* The DSV is surprisingly simple.
             * For each physical table (but NOT partition), a DataTable needs to be added to the DSV DataSet,
             * with the same name as the table.
             *
             * Similarly, for each distinct source column, a DataColumn needs to be added to the corresponding DataTable
             */
            #region DataSourceView
            using (AMO.DataSourceView dsv = new AMO.DataSourceView(AMODatabase.DataSources[0].Name))
            {
                System.Data.DataSet Schema = new System.Data.DataSet(AMODatabase.DataSources[0].Name);
                dsv.Schema       = Schema;
                dsv.DataSourceID = AMODatabase.DataSources[0].Name;
                AMODatabase.DataSourceViews.Add(dsv);
            }
            #endregion

            #region Create Cube
            AMO.Cube Cube = new AMO.Cube(TOMModel.Name, TOMModel.Name);

            Cube.Source      = new AMO.DataSourceViewBinding(AMODatabase.DataSourceViews[0].ID);
            Cube.StorageMode = AMO.StorageMode.InMemory;

            //Create the MdxScript for holding DAX commands.
            using (AMO.MdxScript Script = Cube.MdxScripts.Add(MDXScriptName, MDXScriptName))
            {
                //You MUST have a "default" MDX measure for some reason.
                //We make sure to hide it - it serves no real purpose, besides its own existence
                System.Text.StringBuilder InitialisationCommand = new System.Text.StringBuilder();
                InitialisationCommand.AppendLine("CALCULATE;");
                InitialisationCommand.AppendLine("CREATE MEMBER CURRENTCUBE.Measures.[_No measures defined] AS 1, VISIBLE = 0;");
                InitialisationCommand.AppendLine("ALTER CUBE CURRENTCUBE UPDATE DIMENSION Measures, Default_Member = [_No measures defined];");
                Script.Commands.Add(new AMO.Command(InitialisationCommand.ToString()));
            }
            AMODatabase.Cubes.Add(Cube);
            #endregion
            #region Add Tables
            foreach (TOM.Table TOMTable in TOMModel.Tables)
            {
                //Three "parts" to a Table:
                //1. A System.Data.DataTable, with the same Name as the TOMTable
                //2. A dimension with the same name as the TOMTable
                //3. A measure group with the same name as the TOMTable,
                System.Data.DataTable SchemaTable = new System.Data.DataTable(TOMTable.Name);
                AMODatabase.DataSourceViews[0].Schema.Tables.Add(SchemaTable);

                string RowNumberColumnName = string.Format(System.Globalization.CultureInfo.InvariantCulture, "RowNumber_{0}", System.Guid.NewGuid());

                #region Add Table Dimension
                try
                {
                    using (AMO.Dimension Dimension = AMODatabase.Dimensions.Add(TOMTable.Name, TOMTable.Name))
                    {
                        Dimension.Source            = new AMO.DataSourceViewBinding(AMODatabase.DataSourceViews[0].ID);
                        Dimension.StorageMode       = AMO.DimensionStorageMode.InMemory;
                        Dimension.UnknownMember     = AMO.UnknownMemberBehavior.AutomaticNull;
                        Dimension.UnknownMemberName = "Unknown";
                        using (Dimension.ErrorConfiguration = new AMO.ErrorConfiguration())
                        {
                            Dimension.ErrorConfiguration.KeyNotFound       = AMO.ErrorOption.IgnoreError;
                            Dimension.ErrorConfiguration.KeyDuplicate      = AMO.ErrorOption.ReportAndStop;
                            Dimension.ErrorConfiguration.NullKeyNotAllowed = AMO.ErrorOption.ReportAndStop;
                        }
                        Dimension.ProactiveCaching = new AMO.ProactiveCaching();
                        System.TimeSpan DefaultProactiveCachingTimeSpan = new System.TimeSpan(0, 0, -1);
                        Dimension.ProactiveCaching.SilenceInterval         = DefaultProactiveCachingTimeSpan;
                        Dimension.ProactiveCaching.Latency                 = DefaultProactiveCachingTimeSpan;
                        Dimension.ProactiveCaching.SilenceOverrideInterval = DefaultProactiveCachingTimeSpan;
                        Dimension.ProactiveCaching.ForceRebuildInterval    = DefaultProactiveCachingTimeSpan;
                        Dimension.ProactiveCaching.Source = new AMO.ProactiveCachingInheritedBinding();

                        Dimension.Description = TOMTable.Description;

                        // Define RowNumber
                        using (AMO.DimensionAttribute RowNumberDimAttribute = Dimension.Attributes.Add(RowNumberColumnName, RowNumberColumnName))
                        {
                            RowNumberDimAttribute.Type = AMO.AttributeType.RowNumber;
                            RowNumberDimAttribute.KeyUniquenessGuarantee = true;
                            RowNumberDimAttribute.Usage = AMO.AttributeUsage.Key;
                            RowNumberDimAttribute.KeyColumns.Add(new AMO.DataItem());
                            RowNumberDimAttribute.KeyColumns[0].DataType       = System.Data.OleDb.OleDbType.Integer;
                            RowNumberDimAttribute.KeyColumns[0].DataSize       = 4;
                            RowNumberDimAttribute.KeyColumns[0].NullProcessing = AMO.NullProcessing.Error;
                            RowNumberDimAttribute.KeyColumns[0].Source         = new AMO.RowNumberBinding();
                            RowNumberDimAttribute.NameColumn                = new AMO.DataItem();
                            RowNumberDimAttribute.NameColumn.DataType       = System.Data.OleDb.OleDbType.WChar;
                            RowNumberDimAttribute.NameColumn.DataSize       = 4;
                            RowNumberDimAttribute.NameColumn.NullProcessing = AMO.NullProcessing.ZeroOrBlank;
                            RowNumberDimAttribute.NameColumn.Source         = new AMO.RowNumberBinding();
                            RowNumberDimAttribute.OrderBy = AMO.OrderBy.Key;
                            RowNumberDimAttribute.AttributeHierarchyVisible = false;
                        }

                        // Add Translations
                        foreach (TOM.Culture TOMCulture in TOMModel.Cultures)
                        {
                            AMO.Translation AMOTranslation = TranslationHelper.GetTranslation(TOMCulture, TOMTable);
                            if (AMOTranslation != null)
                            {
                                Dimension.Translations.Add(AMOTranslation);
                            }
                        }
                    }
                }
                catch (Exception e)
                {
                    throw new Exception(string.Format("The following error occurred creating the DImension for table {0}: {1}", TOMTable.Name, e.Message), e);
                }
                #endregion
                #region Add Table MeasureGroup
                try
                {
                    using (AMO.MeasureGroup TableMeasureGroup = Cube.MeasureGroups.Add(TOMTable.Name, TOMTable.Name))
                    {
                        TableMeasureGroup.StorageMode    = AMO.StorageMode.InMemory;
                        TableMeasureGroup.ProcessingMode = AMO.ProcessingMode.Regular;

                        // Add Default Measure
                        string DefaultMeasureID = string.Concat("_Count", TOMTable.Name);
                        using (AMO.Measure DefaultMeasure = TableMeasureGroup.Measures.Add(DefaultMeasureID, DefaultMeasureID))
                            using (AMO.RowBinding DefaultMeasureRowBinding = new AMO.RowBinding(TOMTable.Name))
                                using (AMO.DataItem DefaultMeasureSource = new AMO.DataItem(DefaultMeasureRowBinding))
                                {
                                    DefaultMeasure.AggregateFunction = AMO.AggregationFunction.Count;
                                    DefaultMeasure.DataType          = AMO.MeasureDataType.BigInt;
                                    DefaultMeasure.Visible           = false;
                                    DefaultMeasureSource.DataType    = System.Data.OleDb.OleDbType.BigInt;
                                    DefaultMeasure.Source            = DefaultMeasureSource;
                                }

                        // Add Dimension to Measure Group
                        using (AMO.DegenerateMeasureGroupDimension DefaultMeasureGroupDimension = new AMO.DegenerateMeasureGroupDimension(TOMTable.Name))
                            using (AMO.MeasureGroupAttribute MeasureGroupAttribute = new AMO.MeasureGroupAttribute(RowNumberColumnName))
                                using (AMO.ColumnBinding RowNumberColumnBinding = new AMO.ColumnBinding(TOMTable.Name, RowNumberColumnName))
                                    using (AMO.DataItem RowNumberKeyColumn = new AMO.DataItem(RowNumberColumnBinding))
                                    {
                                        DefaultMeasureGroupDimension.ShareDimensionStorage = AMO.StorageSharingMode.Shared;
                                        DefaultMeasureGroupDimension.CubeDimensionID       = TOMTable.Name;
                                        MeasureGroupAttribute.Type  = AMO.MeasureGroupAttributeType.Granularity;
                                        RowNumberKeyColumn.DataType = System.Data.OleDb.OleDbType.Integer;
                                        MeasureGroupAttribute.KeyColumns.Add(RowNumberKeyColumn);
                                        DefaultMeasureGroupDimension.Attributes.Add(MeasureGroupAttribute);
                                        TableMeasureGroup.Dimensions.Add(DefaultMeasureGroupDimension);
                                    }

                        //Partitions have a 1:1 mapping
                        #region Partitions
                        foreach (TOM.Partition TOMPartition in TOMTable.Partitions)
                        {
                            using (AMO.Partition AMOPartition = new AMO.Partition(TOMPartition.Name, TOMPartition.Name))
                            {
                                AMOPartition.StorageMode    = AMO.StorageMode.InMemory;
                                AMOPartition.ProcessingMode = AMO.ProcessingMode.Regular;
                                AMOPartition.Source         = new AMO.QueryBinding(
                                    ((TOM.QueryPartitionSource)TOMPartition.Source).DataSource.Name,
                                    ((TOM.QueryPartitionSource)TOMPartition.Source).Query
                                    );
                                AMOPartition.Type = AMO.PartitionType.Data;
                                TableMeasureGroup.Partitions.Add(AMOPartition);
                            }
                        }
                        #endregion
                    }
                }
                catch (Exception e)
                {
                    throw new Exception(string.Format("The following error occurred creating the MeasureGroup for table {0}: {1}", TOMTable.Name, e.Message), e);
                }
                #endregion

                //Add the dimension to the cube
                Cube.Dimensions.Add(TOMTable.Name, TOMTable.Name, TOMTable.Name);
                Cube.Dimensions[TOMTable.Name].Visible = !TOMTable.IsHidden;

                /* No 1:1 mapping exists for columns. Each column consists of the following:
                 *  - A DataColumn with the same name as the column,
                 *      attached to the DataTable correlating to the Table, located in the DSV
                 *  - An attribute with the same name as the column, added to the Dimension
                 *  - An AttributeRelationship with the RowNumber column
                 */

                #region Add Columns
                foreach (TOM.Column TOMColumn in TOMTable.Columns)
                {
                    switch (TOMColumn.Type)
                    {
                    case TOM.ColumnType.Data:
                        //Add the DataColumn corresponding to the SourceColumn, if it does not already exist
                        TOM.DataColumn TOMDataColumn = (TOM.DataColumn)TOMColumn;
                        if (!SchemaTable.Columns.Contains(TOMDataColumn.SourceColumn))
                        {
                            SchemaTable.Columns.Add(new System.Data.DataColumn(((TOM.DataColumn)TOMColumn).SourceColumn));
                        }

                        System.Data.OleDb.OleDbType ColumnDataType  = DataTypeHelper.ToOleDbType(TOMDataColumn.DataType);
                        AMO.DimensionAttribute      NormalAttribute = AMODatabase.Dimensions[TOMTable.Name].Attributes.Add(TOMDataColumn.Name, TOMDataColumn.Name);
                        NormalAttribute.Usage = AMO.AttributeUsage.Regular;
                        NormalAttribute.KeyUniquenessGuarantee = false;
                        NormalAttribute.KeyColumns.Add(new AMO.DataItem(SchemaTable.TableName, SchemaTable.Columns[TOMDataColumn.SourceColumn].ColumnName, ColumnDataType));
                        NormalAttribute.KeyColumns[0].Source         = new AMO.ColumnBinding(SchemaTable.TableName, SchemaTable.Columns[TOMDataColumn.SourceColumn].ColumnName);
                        NormalAttribute.KeyColumns[0].NullProcessing = AMO.NullProcessing.Preserve;
                        NormalAttribute.NameColumn                = new AMO.DataItem(SchemaTable.TableName, SchemaTable.Columns[TOMDataColumn.SourceColumn].ColumnName, System.Data.OleDb.OleDbType.WChar);
                        NormalAttribute.NameColumn.Source         = new AMO.ColumnBinding(SchemaTable.TableName, SchemaTable.Columns[TOMDataColumn.SourceColumn].ColumnName);
                        NormalAttribute.NameColumn.NullProcessing = AMO.NullProcessing.ZeroOrBlank;
                        NormalAttribute.OrderBy = AMO.OrderBy.Key;
                        AMO.AttributeRelationship NormalAttributeRelationship = AMODatabase.Dimensions[TOMTable.Name].Attributes[RowNumberColumnName].AttributeRelationships.Add(NormalAttribute.ID);

                        NormalAttribute.AttributeHierarchyVisible = !TOMDataColumn.IsHidden;
                        NormalAttribute.Description = TOMDataColumn.Description;
                        NormalAttribute.AttributeHierarchyDisplayFolder = TOMDataColumn.DisplayFolder;
                        //Add Translations to the CalculatedAttribute
                        foreach (TOM.Culture TOMCulture in TOMModel.Cultures)
                        {
                            using (AMO.Translation AMOTranslation = TranslationHelper.GetTranslation(TOMCulture, TOMColumn))
                                if (AMOTranslation != null)
                                {
                                    NormalAttribute.Translations.Add(new AMO.AttributeTranslation {
                                        Caption = AMOTranslation.Caption, Description = AMOTranslation.Description, DisplayFolder = AMOTranslation.DisplayFolder
                                    });
                                }
                        }

                        NormalAttributeRelationship.Cardinality      = AMO.Cardinality.Many;
                        NormalAttributeRelationship.OverrideBehavior = AMO.OverrideBehavior.None;
                        break;

                    case TOM.ColumnType.Calculated:
                        TOM.CalculatedColumn        TOMCalculatedColumn      = (TOM.CalculatedColumn)TOMColumn;
                        System.Data.OleDb.OleDbType CalculatedColumnDataType = DataTypeHelper.ToOleDbType(TOMColumn.DataType);

                        //Add Attribute to the Dimension
                        AMO.Dimension          dim = AMODatabase.Dimensions[TOMTable.Name];
                        AMO.DimensionAttribute CalculatedAttribute = dim.Attributes.Add(TOMCalculatedColumn.Name, TOMCalculatedColumn.Name);
                        CalculatedAttribute.Usage = AMO.AttributeUsage.Regular;
                        CalculatedAttribute.KeyUniquenessGuarantee = false;

                        CalculatedAttribute.KeyColumns.Add(new AMO.DataItem(TOMTable.Name, TOMCalculatedColumn.Name, CalculatedColumnDataType));
                        CalculatedAttribute.KeyColumns[0].Source         = new AMO.ExpressionBinding(TOMCalculatedColumn.Expression);
                        CalculatedAttribute.KeyColumns[0].NullProcessing = AMO.NullProcessing.Preserve;
                        CalculatedAttribute.NameColumn                = new AMO.DataItem(TOMTable.Name, TOMCalculatedColumn.Name, System.Data.OleDb.OleDbType.WChar);
                        CalculatedAttribute.NameColumn.Source         = new AMO.ExpressionBinding(TOMCalculatedColumn.Expression);
                        CalculatedAttribute.NameColumn.NullProcessing = AMO.NullProcessing.ZeroOrBlank;

                        CalculatedAttribute.OrderBy = AMO.OrderBy.Key;
                        AMO.AttributeRelationship currentAttributeRelationship = dim.Attributes[RowNumberColumnName].AttributeRelationships.Add(CalculatedAttribute.ID);

                        CalculatedAttribute.AttributeHierarchyVisible = !TOMCalculatedColumn.IsHidden;
                        CalculatedAttribute.Description = TOMCalculatedColumn.Description;
                        CalculatedAttribute.AttributeHierarchyDisplayFolder = TOMCalculatedColumn.DisplayFolder;
                        //Add Translations to the CalculatedAttribute
                        //Loop through each culture, and add the translation associated with that culture.
                        foreach (TOM.Culture TOMCulture in TOMModel.Cultures)
                        {
                            using (AMO.Translation AMOTranslation = TranslationHelper.GetTranslation(TOMCulture, TOMColumn))
                                if (AMOTranslation != null)
                                {
                                    CalculatedAttribute.Translations.Add(new AMO.AttributeTranslation {
                                        Caption = AMOTranslation.Caption, Description = AMOTranslation.Description, DisplayFolder = AMOTranslation.DisplayFolder
                                    });
                                }
                        }

                        currentAttributeRelationship.Cardinality      = AMO.Cardinality.Many;
                        currentAttributeRelationship.OverrideBehavior = AMO.OverrideBehavior.None;

                        //Add CalculatedColumn as attribute to the MeasureGroup
                        AMO.MeasureGroup mg = Cube.MeasureGroups[TOMTable.Name];
                        AMO.DegenerateMeasureGroupDimension currentMGDim = (AMO.DegenerateMeasureGroupDimension)mg.Dimensions[TOMTable.Name];
                        AMO.MeasureGroupAttribute           mga          = new AMO.MeasureGroupAttribute(TOMCalculatedColumn.Name);

                        mga.KeyColumns.Add(new AMO.DataItem(TOMTable.Name, TOMCalculatedColumn.Name, System.Data.OleDb.OleDbType.Empty));
                        mga.KeyColumns[0].Source = new AMO.ExpressionBinding(TOMCalculatedColumn.Expression);
                        currentMGDim.Attributes.Add(mga);
                        break;

                    default:
                        throw new System.NotImplementedException(string.Format("Cannot deploy Column of type {0}", TOMColumn.Type.ToString()));
                    }
                }
                #endregion

                //Add sort by columns
                foreach (TOM.Column TOMColumn in TOMTable.Columns)
                {
                    if (TOMColumn.SortByColumn != null)
                    {
                        AMODatabase.Dimensions[TOMTable.Name].Attributes[TOMColumn.Name].OrderByAttributeID = TOMColumn.SortByColumn.Name;
                    }
                }

                #region Add Hierarchies
                foreach (TOM.Hierarchy TOMHierarchy in TOMTable.Hierarchies)
                {
                    //Create the Hierarchy, and add it
                    AMO.Hierarchy AMOHierarchy = AMODatabase.Dimensions[TOMTable.Name].Hierarchies.Add(TOMHierarchy.Name, TOMHierarchy.Name);
                    AMOHierarchy.Description   = TOMHierarchy.Description;
                    AMOHierarchy.DisplayFolder = TOMHierarchy.DisplayFolder;

                    AMOHierarchy.AllMemberName = "All";
                    foreach (TOM.Level TOMLevel in TOMHierarchy.Levels)
                    {
                        AMO.Level AMOLevel = AMOHierarchy.Levels.Add(TOMLevel.Name);
                        AMOLevel.SourceAttribute = AMODatabase.Dimensions[TOMTable.Name].Attributes[TOMLevel.Column.Name];
                        AMOLevel.Description     = TOMLevel.Description;
                        //Add Translations to the CalculatedAttribute
                        //Loop through each culture, and add the translation associated with that culture.
                        foreach (TOM.Culture TOMCulture in TOMModel.Cultures)
                        {
                            using (AMO.Translation AMOTranslation = TranslationHelper.GetTranslation(TOMCulture, TOMLevel))
                                if (AMOTranslation != null)
                                {
                                    AMOLevel.Translations.Add(AMOTranslation);
                                }
                        }
                    }
                }
                #endregion
            }
            #endregion

            #region Add Measures
            using (AMO.MdxScript MdxScript = Cube.DefaultMdxScript)
            {
                //Create a "default" measure, required for the cube to function.
                MdxScript.Commands.Add(new AMO.Command("CALCULATE;"
                                                       + "CREATE MEMBER CURRENTCUBE.Measures.[_No measures defined] AS 1, VISIBLE = 0;"
                                                       + "ALTER CUBE CURRENTCUBE UPDATE DIMENSION Measures, Default_Member = [_No measures defined];"));

                foreach (TOM.Table TOMTable in TOMModel.Tables)
                {
                    foreach (TOM.Measure TOMMeasure in TOMTable.Measures)
                    {
                        //Create the Command, which contains the definition of the Measure
                        AMO.Command AMOCommand = new AMO.Command(string.Format("CREATE MEASURE '{0}'[{1}]={2};", TOMTable.Name, TOMMeasure.Name.Replace("]", "]]"), TOMMeasure.Expression));
                        if (TOMMeasure.KPI != null)
                        {
                            #region Add KPI
                            //Start building the final command - we add to it as we go along
                            string FinalKPICreate = string.Format(
                                "CREATE KPI CURRENTCUBE.[{1}] AS Measures.[{1}], ASSOCIATED_MEASURE_GROUP = '{0}'",
                                TOMTable.Name,
                                TOMMeasure.Name
                                );
                            //Goal/Target
                            if (!string.IsNullOrWhiteSpace(TOMMeasure.KPI.TargetExpression))
                            {
                                AMOCommand.Text += Environment.NewLine + string.Format(string.Format("CREATE MEASURE '{0}'[_{1} Goal]={2};", TOMTable.Name, TOMMeasure.Name.Replace("]", "]]"), TOMMeasure.KPI.TargetExpression));
                                FinalKPICreate  += string.Format(", GOAL = Measures.[_{0} Goal]", TOMMeasure.Name.Replace("]", "]]"));
                            }

                            //Status
                            if (!string.IsNullOrWhiteSpace(TOMMeasure.KPI.StatusExpression))
                            {
                                AMOCommand.Text += Environment.NewLine + string.Format(string.Format("CREATE MEASURE '{0}'[_{1} Status]={2};", TOMTable.Name, TOMMeasure.Name.Replace("]", "]]"), TOMMeasure.KPI.StatusExpression));
                                FinalKPICreate  += string.Format(", STATUS = Measures.[_{0} Status]", TOMMeasure.Name.Replace("]", "]]"));
                                if (!string.IsNullOrWhiteSpace(TOMMeasure.KPI.StatusGraphic))
                                {
                                    FinalKPICreate += string.Format(", STATUS_GRAPHIC = '{0}'", TOMMeasure.KPI.StatusGraphic);
                                }
                            }

                            //Trend
                            if (!string.IsNullOrWhiteSpace(TOMMeasure.KPI.TrendExpression))
                            {
                                AMOCommand.Text += Environment.NewLine + string.Format(string.Format("CREATE MEASURE '{0}'[_{1} Trend]={2};", TOMTable.Name, TOMMeasure.Name.Replace("]", "]]"), TOMMeasure.KPI.TrendExpression));
                                FinalKPICreate  += string.Format(", TREND = Measures.[_{0} Trend]", TOMMeasure.Name.Replace("]", "]]"));
                                if (!string.IsNullOrWhiteSpace(TOMMeasure.KPI.TrendGraphic))
                                {
                                    FinalKPICreate += string.Format(", TREND_GRAPHIC = '{0}'", TOMMeasure.KPI.TrendGraphic);
                                }
                            }

                            AMOCommand.Text += Environment.NewLine + FinalKPICreate + ";";

                            //Create calculation properties, hiding the "fake" measures (if they exist)
                            //Target
                            if (!string.IsNullOrWhiteSpace(TOMMeasure.KPI.TargetExpression))
                            {
                                AMO.CalculationProperty TargetCalculationProperty = new AMO.CalculationProperty(string.Format("[_{0} Goal]", TOMMeasure.Name.Replace("]", "]]")), AMO.CalculationType.Member);
                                TargetCalculationProperty.Description     = TOMMeasure.KPI.TargetDescription;
                                TargetCalculationProperty.CalculationType = AMO.CalculationType.Member;
                                TargetCalculationProperty.Visible         = false;
                                if (!string.IsNullOrWhiteSpace(TOMMeasure.KPI.TargetFormatString))
                                {
                                    TargetCalculationProperty.FormatString = "'" + TOMMeasure.KPI.TargetFormatString + "'";
                                }
                                MdxScript.CalculationProperties.Add(TargetCalculationProperty);
                            }

                            //Status
                            if (!string.IsNullOrWhiteSpace(TOMMeasure.KPI.StatusExpression))
                            {
                                AMO.CalculationProperty StatusCalculationProperty = new AMO.CalculationProperty(string.Format("[_{0} Status]", TOMMeasure.Name.Replace("]", "]]")), AMO.CalculationType.Member);
                                StatusCalculationProperty.Description     = TOMMeasure.KPI.StatusDescription;
                                StatusCalculationProperty.CalculationType = AMO.CalculationType.Member;
                                StatusCalculationProperty.Visible         = false;
                                MdxScript.CalculationProperties.Add(StatusCalculationProperty);
                            }

                            //Trend
                            if (!string.IsNullOrWhiteSpace(TOMMeasure.KPI.StatusExpression))
                            {
                                AMO.CalculationProperty TrendCalculationProperty = new AMO.CalculationProperty(string.Format("[_{0} Trend]", TOMMeasure.Name.Replace("]", "]]")), AMO.CalculationType.Member);
                                TrendCalculationProperty.Description     = TOMMeasure.KPI.TrendDescription;
                                TrendCalculationProperty.CalculationType = AMO.CalculationType.Member;
                                TrendCalculationProperty.Visible         = false;
                                MdxScript.CalculationProperties.Add(TrendCalculationProperty);
                            }

                            //Create the KPI calculation property
                            AMO.CalculationProperty KPICalculationProperty = new AMO.CalculationProperty(string.Format("KPIs.[{0}]", TOMMeasure.Name.Replace("]", "]]")), AMO.CalculationType.Member);
                            KPICalculationProperty.Description     = TOMMeasure.KPI.Description;
                            KPICalculationProperty.CalculationType = AMO.CalculationType.Member;
                            MdxScript.CalculationProperties.Add(KPICalculationProperty);
                            #endregion
                        }

                        //Add the Command to the MdxScript
                        MdxScript.Commands.Add(AMOCommand);

                        //Create the Calculation Property, which contains the various properties of the Measure
                        AMO.CalculationProperty CalculationProperty = new AMO.CalculationProperty(string.Format("[{0}]", TOMMeasure.Name.Replace("]", "]]")), AMO.CalculationType.Member);
                        CalculationProperty.Description     = TOMMeasure.Description;
                        CalculationProperty.DisplayFolder   = TOMMeasure.DisplayFolder;
                        CalculationProperty.CalculationType = AMO.CalculationType.Member;
                        CalculationProperty.Visible         = !TOMMeasure.IsHidden;
                        if (!string.IsNullOrWhiteSpace(TOMMeasure.FormatString))
                        {
                            CalculationProperty.FormatString = "'" + TOMMeasure.FormatString + "'";
                        }

                        //Add Translations to the Calculation property
                        foreach (TOM.Culture TOMCulture in TOMModel.Cultures)
                        {
                            using (AMO.Translation AMOTranslation = TranslationHelper.GetTranslation(TOMCulture, TOMMeasure))
                                if (AMOTranslation != null)
                                {
                                    CalculationProperty.Translations.Add(AMOTranslation);
                                }
                        }

                        //Finally, add the CalculationProperty to the MDX script
                        MdxScript.CalculationProperties.Add(CalculationProperty);
                    }
                }
            }
            #endregion

            #region Add Relationships
            //Relationships are just TOO awful. They are placed in their own helper function as a result.
            foreach (TOM.SingleColumnRelationship TOMRelationship in TOMModel.Relationships)
            {
                RelationshipHelper.CreateRelationship(TOMRelationship, AMODatabase);
            }
            #endregion

            #region Add Perspectives
            foreach (TOM.Perspective TOMPerspective in TOMModel.Perspectives)
            {
                AMO.Perspective AMOPerspective = new AMO.Perspective(TOMPerspective.Name);
                foreach (TOM.PerspectiveTable TOMTable in TOMPerspective.PerspectiveTables)
                {
                    //Add Perspective Dimension
                    AMO.PerspectiveDimension PerspectiveDimension = new AMO.PerspectiveDimension(TOMTable.Name);

                    //Perspective Columns
                    foreach (TOM.PerspectiveColumn TOMPerspectiveColumn in TOMTable.PerspectiveColumns)
                    {
                        PerspectiveDimension.Attributes.Add(TOMPerspectiveColumn.Name);
                    }
                    //Perspective Hierarchies
                    foreach (TOM.PerspectiveHierarchy TOMPerspectiveHierarchy in TOMTable.PerspectiveHierarchies)
                    {
                        PerspectiveDimension.Hierarchies.Add(TOMPerspectiveHierarchy.Name);
                    }

                    //Add Perspective MeasureGroup
                    AMO.PerspectiveMeasureGroup PerspectiveMeasureGroup = new AMO.PerspectiveMeasureGroup(TOMTable.Name);

                    //Perspective Measures
                    //In this case, ']' is NOT "double quoted", unlike the calculation references, s
                    foreach (TOM.PerspectiveMeasure TOMPerspectiveMeasure in TOMTable.PerspectiveMeasures)
                    {
                        AMOPerspective.Calculations.Add('[' + TOMPerspectiveMeasure.Name + ']');
                    }

                    //VS does not add KPI's to Perspectives, so I have no idea how to do this...
                    //TODO: Add KPIs to perspectives
                }
                AMODatabase.Cubes[0].Perspectives.Add(AMOPerspective);
            }
            #endregion

            #region Roles
            foreach (TOM.ModelRole TOMRole in TOMDatabase.Model.Roles)
            {
                //Ceate database role
                AMO.Role DatabaseRole = AMODatabase.Roles.Add(TOMRole.Name);
                DatabaseRole.Description = TOMRole.Description;

                //Add Members to Database role
                foreach (TOM.ModelRoleMember TOMMember in TOMRole.Members)
                {
                    DatabaseRole.Members.Add(new AMO.RoleMember
                    {
                        Name = TOMMember.MemberName,
                        Sid  = TOMMember.MemberID
                    });
                }

                //Add DatabasePermission
                AMO.DatabasePermission DatabasePermission = AMODatabase.DatabasePermissions.Add(TOMRole.Name, TOMRole.Name, TOMRole.Name);
                //Add CubePermission
                AMO.CubePermission CubePermission = AMODatabase.Cubes[0].CubePermissions.Add(TOMRole.Name, TOMRole.Name, TOMRole.Name);
                switch (TOMRole.ModelPermission)
                {
                case TOM.ModelPermission.Administrator:
                    //Add ReadDefinition, Read, and Administer to DatabasePermission
                    DatabasePermission.ReadDefinition = AMO.ReadDefinitionAccess.Allowed;
                    DatabasePermission.Read           = AMO.ReadAccess.Allowed;
                    DatabasePermission.Administer     = true;

                    //Nothing extra to add to CubePermission, that is not added after this
                    // switch statement
                    break;

                case TOM.ModelPermission.None:
                    //Add no permissions to DatabasePermission.
                    break;

                case TOM.ModelPermission.Read:
                    //Add only Read access to Database Permission
                    DatabasePermission.Read = AMO.ReadAccess.Allowed;

                    //CubePermission
                    CubePermission.Read = AMO.ReadAccess.Allowed;
                    break;

                case TOM.ModelPermission.ReadRefresh:
                    DatabasePermission.Read    = AMO.ReadAccess.Allowed;
                    DatabasePermission.Process = true;

                    //CubePermission
                    CubePermission.Read    = AMO.ReadAccess.Allowed;
                    CubePermission.Process = true;
                    break;

                case TOM.ModelPermission.Refresh:
                    DatabasePermission.Process = true;

                    //CubePermission
                    CubePermission.Process = true;
                    break;
                }
                CubePermission.ReadSourceData = AMO.ReadSourceDataAccess.None;

                //Finally, add the row filters
                foreach (TOM.TablePermission TablePermission in TOMRole.TablePermissions)
                {
                    AMO.DimensionPermission DimensionPermission = new AMO.DimensionPermission(TOMRole.Name, TOMRole.Name, TOMRole.Name);
                    DimensionPermission.AllowedRowsExpression = TablePermission.FilterExpression;
                    AMODatabase.Dimensions.GetByName(TablePermission.Table.Name).DimensionPermissions.Add(DimensionPermission);
                }
            }
            #endregion
            return(AMODatabase);
        }
        public static void HierarchyAlterLevelsAdd(AMO.Database tabularDatabase,
                                                   string tableName,
                                                   string hierarchyName,
                                                   bool updateInstance = true,
                                                   params LevelInfo[] levelInfo)
        {
            //  Major steps in adding Levels to a Hierarchy in a table
            //
            //  - Validate required input arguments
            //  - Other Initial preparations
            //  - Adding Levels to Hierarchy

            //
            //  Note: There are no validations for duplicated names, invalid names or
            //  similar scenarios. It is expected the server will take care of them and
            //  throw exceptions on any invalid situation.
            //
            //  Note:   In AMO, strings as indexers refer to the ID of the object, not the Name of the object
            //
            //  Note:   Only one DataSourceView is used in Tabular Models
            //          ==> tabularDatabase.DataSourceViews[0] represents the DSV of the model
            //
            //  Note:   Only one Cube is used in Tabular Models
            //          ==> tabularDatabase.Cubes[0] represents the cube in the model
            //
            //  Note:   Microsoft design tools use the following pattern to keep track of the
            //          datasource matching elements:
            //          DataSourceView->TableName <---> Dimension.ID, MeasureGroup.ID
            //          DataSourceView->ColumnName <---> Dimension->ColumnID, MeasureGroup.DegeneratedDimension->CoumnID
            //          So far, this sample follows the same pattern.
            //
            //          WARNING:    Breaking the above pattern when creating your
            //                      own AMO to Tabular functions might lead to
            //                      unpredictable behavior when using Microsoft
            //                      Design tools in your models.

            #region Validate input arguments and other initial preparations
            //  Validate required input arguments
            if (tabularDatabase == null)
            {
                throw new ArgumentNullException(TabularDatabaseStringName);
            }
            if (tableName.IsNullOrEmptyOrWhitespace())
            {
                throw new ArgumentNullException(TableStringName);
            }
            if (hierarchyName.IsNullOrEmptyOrWhitespace())
            {
                throw new ArgumentNullException(HierarchyStringName);
            }
            if (levelInfo == null || levelInfo.Length == 0)
            {
                throw new ArgumentNullException(LevelInfo);
            }
            if (!IsDatabaseCompatibilityLevelCorrect(tabularDatabase))
            {
                throw new InvalidOperationException(Resources.InvalidCompatibilityLevelOperationException);
            }

            //  Other initial preparations
            //  -   Cleaning and preparing name variables
            tableName     = tableName.Trim();
            hierarchyName = hierarchyName.Trim();
            #endregion

            //  [Codeplex issue # 8]
            //  [JPJofre, 2012-10-18]
            //  [Description: using (AMO.Hierarchy currentHierarchy = tableDimension.Hierarchies[hierarchyName]);
            //  ...   it should be using (AMO.Hierarchy currentHierarchy = tableDimension.Hierarchies.GetByName(hierarchyName))]
            //  [Suggested fix: replace with tableDimension.Hierarchies.GetByName(hierarchyName)]


            //  Add levels
            using (AMO.Dimension tableDimension = tabularDatabase.Dimensions.GetByName(tableName))
                using (AMO.Hierarchy currentHierarchy = tableDimension.Hierarchies.GetByName(hierarchyName))
                {
                    //  Add levels to the hierarchy
                    foreach (LevelInfo level in levelInfo)
                    {
                        string attributeId = tableDimension.Attributes.GetByName(level.DefiningLevelColumnName).ID;
                        currentHierarchy.Levels.Add(level.LevelName).SourceAttributeID = attributeId;
                    }
                }

            //  Update server instance
            if (updateInstance)
            {
                tabularDatabase.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate);
            }
        }
Example #11
0
        //this method is where any future code changes will need to be made
        //the decisions about what extra properties are show are configured here
        void ConfigureProjectItemExtraProperties(ProjectItem pi)
        {
            if (pi == null)
            {
                return;
            }
            if (pi.Object is Dimension)
            {
                Microsoft.AnalysisServices.Dimension dim = (Microsoft.AnalysisServices.Dimension)pi.Object;
                System.ComponentModel.TypeDescriptor.Refresh(dim);

                SetAttribute(typeof(Dimension), "Annotations", new System.ComponentModel.BrowsableAttribute(bInEffect), true);
                SetAttribute(dim, "Annotations", new System.ComponentModel.EditorAttribute(typeof(AttributeCollectionEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
                SetAttribute(typeof(Dimension), "Description", new System.ComponentModel.EditorAttribute(typeof(MultilineStringEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
                SetAttribute(dim, "Description", new System.ComponentModel.EditorAttribute(typeof(MultilineStringEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);

                SetAttribute(typeof(Hierarchy), "Annotations", new System.ComponentModel.BrowsableAttribute(bInEffect), true);
                SetAttribute(typeof(Hierarchy), "Annotations", new System.ComponentModel.EditorAttribute(typeof(AttributeCollectionEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
                SetAttribute(typeof(Hierarchy), "Description", new System.ComponentModel.EditorAttribute(typeof(MultilineStringEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);

                SetAttribute(typeof(DimensionAttribute), "Annotations", new System.ComponentModel.BrowsableAttribute(bInEffect), true);
                SetAttribute(typeof(DimensionAttribute), "Annotations", new System.ComponentModel.EditorAttribute(typeof(AttributeCollectionEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
                SetAttribute(typeof(DimensionAttribute), "Description", new System.ComponentModel.EditorAttribute(typeof(MultilineStringEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);

                SetAttribute(typeof(Translation), "Annotations", new System.ComponentModel.BrowsableAttribute(bInEffect), true);
                SetAttribute(typeof(Translation), "Annotations", new System.ComponentModel.EditorAttribute(typeof(AttributeCollectionEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
            }
            else if (pi.Object is Cube)
            {
                Microsoft.AnalysisServices.Cube cube = (Microsoft.AnalysisServices.Cube)pi.Object;
                System.ComponentModel.TypeDescriptor.Refresh(cube);
                SetAttribute(typeof(Cube), "Annotations", new System.ComponentModel.BrowsableAttribute(bInEffect), true);
                SetAttribute(cube, "Annotations", new System.ComponentModel.BrowsableAttribute(bInEffect), true);
                SetAttribute(cube, "Annotations", new System.ComponentModel.EditorAttribute(typeof(AttributeCollectionEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
                SetAttribute(typeof(Cube), "Description", new System.ComponentModel.EditorAttribute(typeof(MultilineStringEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);

                SetAttribute(typeof(MeasureGroup), "Annotations", new System.ComponentModel.BrowsableAttribute(bInEffect), true);
                SetAttribute(typeof(MeasureGroup), "Annotations", new System.ComponentModel.EditorAttribute(typeof(AttributeCollectionEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
                SetAttribute(typeof(MeasureGroup), "Annotations", new System.ComponentModel.ReadOnlyAttribute(false), true);
                SetAttribute(typeof(MeasureGroup), "Description", new System.ComponentModel.EditorAttribute(typeof(MultilineStringEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);

                SetAttribute(typeof(Measure), "Annotations", new System.ComponentModel.BrowsableAttribute(bInEffect), true);
                SetAttribute(typeof(Measure), "Annotations", new System.ComponentModel.EditorAttribute(typeof(AttributeCollectionEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
                SetAttribute(typeof(Measure), "Annotations", new System.ComponentModel.ReadOnlyAttribute(false), true);
                SetAttribute(typeof(Measure), "Description", new System.ComponentModel.EditorAttribute(typeof(MultilineStringEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);

                foreach (MeasureGroup mg in cube.MeasureGroups)
                {
                    if (mg.IsLinked)
                    {
                        SetAttribute(mg, "Annotations", new System.ComponentModel.BrowsableAttribute(bInEffect), true);
                        SetAttribute(mg, "Annotations", new System.ComponentModel.EditorAttribute(typeof(AttributeCollectionEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
                        SetAttribute(mg, "Description", new System.ComponentModel.ReadOnlyAttribute(!bInEffect), true);
                        SetAttribute(mg, "Description", new System.ComponentModel.EditorAttribute(typeof(MultilineStringEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);

                        foreach (Measure m in mg.Measures)
                        {
                            SetAttribute(m, "Description", new System.ComponentModel.ReadOnlyAttribute(!bInEffect), true);
                            SetAttribute(m, "Description", new System.ComponentModel.EditorAttribute(typeof(MultilineStringEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
                            SetAttribute(m, "Annotations", new System.ComponentModel.EditorAttribute(typeof(AttributeCollectionEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
                            SetAttribute(m, "Annotations", new System.ComponentModel.BrowsableAttribute(bInEffect), true);
                            SetAttribute(m, "FormatString", new System.ComponentModel.BrowsableAttribute(bInEffect), true);
                        }
                    }

                    foreach (Partition p in mg.Partitions)
                    {
                        SetAttribute(p, "Annotations", new System.ComponentModel.EditorAttribute(typeof(AttributeCollectionEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
                        SetAttribute(p, "Annotations", new System.ComponentModel.BrowsableAttribute(bInEffect), true);
                    }
                }

                SetAttribute(typeof(Perspective), "Annotations", new System.ComponentModel.BrowsableAttribute(bInEffect), true);
                SetAttribute(typeof(Perspective), "Annotations", new System.ComponentModel.EditorAttribute(typeof(AttributeCollectionEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
                SetAttribute(typeof(Perspective), "Description", new System.ComponentModel.EditorAttribute(typeof(MultilineStringEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);

                SetAttribute(typeof(Translation), "Annotations", new System.ComponentModel.BrowsableAttribute(bInEffect), true);
                SetAttribute(typeof(Translation), "Annotations", new System.ComponentModel.EditorAttribute(typeof(AttributeCollectionEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
                SetAttribute(typeof(Translation), "Description", new System.ComponentModel.EditorAttribute(typeof(MultilineStringEditor), typeof(System.Drawing.Design.UITypeEditor)), bInEffect);
            }
        }
Example #12
0
        /* Complete feature list, to be noted when complete support added
         *  - Database
         *      - Direct Query
         *      - Datasources (Done)
         *      - Tables (Done)
         *          - Translation of table (Done)
         *          - Columns (Done)
         *              - Translation of Column (Done)
         *          - Hierarchies (Done)
         *              - Translation of Hierarchies (Done)
         *              - Levels (Done)
         *                  - Translation of Levels (Done)
         *      - Measures (Done)
         *          - Translation of Measures (Done)
         *          - KPI's
         *      - Perspectives (Done)
         *      - Roles (Done)
         *          - Row Level Security (Done)
         *          - Members (Done)
         *      - Relationships (Done)
         */
        /// <summary>
        /// Generates a 1200 Tabular Database, based on the provided 1103 Database.
        /// </summary>
        /// <param name="AMODatabase">The 1103 Database to create 1200 model from</param>
        /// <param name="AddTabularEditorAnnotation">Whether to add an annotation to allow compatibility with TabularEditor</param>
        /// <returns></returns>
        public static TOM.Database Database(AMO.Database AMODatabase, bool AddTabularEditorAnnotation = true)
        {
            //Create the database
            TOM.Database TOMDatabase = new TOM.Database(AMODatabase.Name);

            //Create the model
            TOMDatabase.Model      = new TOM.Model();
            TOMDatabase.Model.Name = AMODatabase.Cubes[0].Name;

            #region DataSources
            foreach (AMO.DataSource AMODataSource in AMODatabase.DataSources)
            {
                //Create the data source. We use the ProviderDataSource specifically
                TOM.ProviderDataSource TOMDataSource = new TOM.ProviderDataSource
                {
                    Description      = AMODataSource.Description,
                    ConnectionString = AMODataSource.ConnectionString,
                    MaxConnections   = AMODataSource.MaxActiveConnections,
                    Name             = AMODataSource.Name,
                    Provider         = AMODataSource.ManagedProvider,
                    Timeout          = (int)AMODataSource.Timeout.TotalSeconds
                };

                //Convert AMO ImpersonationMode enum to TOM ImpersonationMode enum
                switch (AMODataSource.ImpersonationInfo.ImpersonationMode)
                {
                case AMO.ImpersonationMode.Default:
                    TOMDataSource.ImpersonationMode = TOM.ImpersonationMode.Default;
                    break;

                case AMO.ImpersonationMode.ImpersonateAccount:
                    TOMDataSource.ImpersonationMode = TOM.ImpersonationMode.ImpersonateAccount;
                    break;

                case AMO.ImpersonationMode.ImpersonateAnonymous:
                    TOMDataSource.ImpersonationMode = TOM.ImpersonationMode.ImpersonateAnonymous;
                    break;

                case AMO.ImpersonationMode.ImpersonateCurrentUser:
                    TOMDataSource.ImpersonationMode = TOM.ImpersonationMode.ImpersonateCurrentUser;
                    break;

                case AMO.ImpersonationMode.ImpersonateServiceAccount:
                    TOMDataSource.ImpersonationMode = TOM.ImpersonationMode.ImpersonateServiceAccount;
                    break;

                case AMO.ImpersonationMode.ImpersonateUnattendedAccount:
                    TOMDataSource.ImpersonationMode = TOM.ImpersonationMode.ImpersonateUnattendedAccount;
                    break;
                }

                //Convert AMO Isolation enum to TOM Isolation enum
                switch (AMODataSource.Isolation)
                {
                case AMO.DataSourceIsolation.ReadCommitted:
                    TOMDataSource.Isolation = TOM.DatasourceIsolation.ReadCommitted;
                    break;

                case AMO.DataSourceIsolation.Snapshot:
                    TOMDataSource.Isolation = TOM.DatasourceIsolation.Snapshot;
                    break;
                }

                //Add the DataSource
                TOMDatabase.Model.DataSources.Add(TOMDataSource);
            }
            #endregion

            foreach (AMO.Dimension Dimension in AMODatabase.Dimensions)
            {
                //Create the table
                TOM.Table TOMTable = new TOM.Table();
                TOMTable.Description = Dimension.Description;
                TOMTable.Name        = Dimension.Name;
                TOMTable.IsHidden    = !AMODatabase.Cubes[0].Dimensions.FindByName(Dimension.Name).Visible;

                //Add Translations
                foreach (AMO.Translation AMOTranslation in Dimension.Translations)
                {
                    TranslationHelper.AddTOMTranslation(TOMDatabase, TOMTable, AMOTranslation);
                }

                #region Columns
                foreach (AMO.DimensionAttribute Attribute in Dimension.Attributes)
                {
                    if (Attribute.Type != AMO.AttributeType.RowNumber)
                    {
                        //Declare "generic" TOM Column, to be assigned to the "specific" column for reuse in
                        // assigning common properties
                        TOM.Column TOMColumn;
                        if (Attribute.NameColumn.Source is AMO.ExpressionBinding)
                        {
                            //Create the Calculated Column, and set specific properties
                            TOM.CalculatedColumn CalculatedColumn = new TOM.CalculatedColumn();
                            CalculatedColumn.Expression = ((AMO.ExpressionBinding)Attribute.NameColumn.Source).Expression;

                            //Set as TOMColumn so generic properties can be applied later
                            TOMColumn = CalculatedColumn;
                        }
                        else
                        {
                            //Create the Data Column, and set specific properties
                            TOM.DataColumn DataColumn = new TOM.DataColumn();
                            DataColumn.SourceColumn = ((AMO.ColumnBinding)Attribute.NameColumn.Source).ColumnID;
                            DataColumn.IsKey        = Attribute.Usage == AMO.AttributeUsage.Key;
                            DataColumn.IsNullable   = Attribute.KeyColumns[0].NullProcessing != AMO.NullProcessing.Error;

                            //Set as TOMColumn so generic properties can be applied later
                            TOMColumn = DataColumn;
                        }

                        //Generic Properties, shared between both Data Columns and Calculated Columns
                        TOMColumn.Name          = Attribute.Name;
                        TOMColumn.Description   = Attribute.Description;
                        TOMColumn.DisplayFolder = Attribute.AttributeHierarchyDisplayFolder;
                        TOMColumn.FormatString  = Attribute.FormatString;
                        TOMColumn.IsHidden      = !Attribute.AttributeHierarchyVisible;
                        TOMColumn.DataType      = DataTypeHelper.ToTOMDataType(Attribute.KeyColumns[0].DataType);

                        //Add translations
                        foreach (AMO.Translation AMOTranslation in Attribute.Translations)
                        {
                            TranslationHelper.AddTOMTranslation(TOMDatabase, TOMColumn, AMOTranslation);
                        }

                        //Finally, add the Column to the Table
                        TOMTable.Columns.Add(TOMColumn);
                    }
                }
                //Add sort by columns last
                //This is because we cannot add sort by columns referring to columns which do not exist yet
                foreach (AMO.DimensionAttribute Attribute in Dimension.Attributes)
                {
                    if (Attribute.Type != AMO.AttributeType.RowNumber && Attribute.OrderByAttribute != null)
                    {
                        TOMTable.Columns[Attribute.Name].SortByColumn = TOMTable.Columns[Attribute.OrderByAttribute.Name];
                    }
                }
                #endregion

                //Add the Table
                TOMDatabase.Model.Tables.Add(TOMTable);

                #region Hierarchies
                foreach (AMO.Hierarchy AMOHierarchy in Dimension.Hierarchies)
                {
                    //Create the hierarchy
                    TOM.Hierarchy TOMHierarchy = new TOM.Hierarchy();
                    TOMHierarchy.Name          = AMOHierarchy.Name;
                    TOMHierarchy.Description   = AMOHierarchy.Description;
                    TOMHierarchy.DisplayFolder = AMOHierarchy.DisplayFolder;

                    //AMO Hierarchies are always visible, from what I can tell
                    TOMHierarchy.IsHidden = false;

                    //Add translations
                    foreach (AMO.Translation AMOTranslation in AMOHierarchy.Translations)
                    {
                        TranslationHelper.AddTOMTranslation(TOMDatabase, TOMHierarchy, AMOTranslation);
                    }

                    foreach (AMO.Level AMOLevel in AMOHierarchy.Levels)
                    {
                        //Create the level
                        TOM.Level TOMLevel = new TOM.Level();
                        TOMLevel.Name        = AMOLevel.Name;
                        TOMLevel.Description = AMOLevel.Description;
                        TOMLevel.Column      = TOMTable.Columns[AMOLevel.SourceAttribute.Name];

                        //Add translations
                        foreach (AMO.Translation AMOTranslation in AMOLevel.Translations)
                        {
                            TranslationHelper.AddTOMTranslation(TOMDatabase, TOMLevel, AMOTranslation);
                        }

                        //Add the Level
                        TOMHierarchy.Levels.Add(TOMLevel);
                    }
                    //Add the Hierarchy
                    TOMTable.Hierarchies.Add(TOMHierarchy);
                }
                #endregion
                #region Partitions
                foreach (AMO.Partition AMOPartition in AMODatabase.Cubes[0].MeasureGroups.GetByName(TOMTable.Name).Partitions)
                {
                    //Create the partition
                    TOM.Partition TOMPartition = new TOM.Partition
                    {
                        Description = AMOPartition.Description,

                        //Add the query
                        Source = new TOM.QueryPartitionSource
                        {
                            DataSource = TOMDatabase.Model.DataSources[AMOPartition.DataSource.Name],
                            Query      = ((AMO.QueryBinding)AMOPartition.Source).QueryDefinition
                        }
                    };

                    //Add the Partition
                    TOMTable.Partitions.Add(TOMPartition);
                }
                #endregion
            }

            #region Measures
            foreach (AMO.Command Command in AMODatabase.Cubes[0].MdxScripts[0].Commands)
            {
                List <MDXCommand> Commands = MDXCommandParser.GetCommands(Command.Text);
                if (Commands.Count > 0)
                {
                    MDXCommand MainCommand = Commands[0];
                    if (MainCommand.Type == CommandType.CreateMeasure)
                    {
                        List <MDXString> Strings = MDXCommandParser.GetStrings(MainCommand.LHS);
                        //Throw exception if we do not have a valid CREATE MEASURE command.

                        /* We do not care if a command is claid, only if we can parse it.
                         * We need two strings:
                         * - A single quoted string, representing the table name
                         * - A square bracketed string, representing the measure name
                         * As long as they are present, we consider it valid.
                         */
                        if (Strings.Count < 2 || Strings[0].Type != StringType.SingleQuote || Strings[1].Type != StringType.SquareBracket)
                        {
                            throw new System.Exception("A CREATE MEASURE statement must at least have two delimited elements (one table, one measure name)");
                        }

                        //First, single quoted string, is table name
                        string TableName = Strings[0].Text;
                        //Then, square-bracket delimited string is the measure name.
                        string MeasureName = Strings[1].Text;

                        //Find the existing CalculationProperty, containing the relevant properties of the Measure, if it exists
                        AMO.CalculationProperty CalculationProperty = AMODatabase.Cubes[0].MdxScripts[0].CalculationProperties.Find("[" + MeasureName.Replace("]", "]]") + "]");

                        //Create the Measure
                        TOM.Measure TOMMeasure = new TOM.Measure
                        {
                            Name          = MeasureName,
                            Expression    = MainCommand.RHS,
                            Description   = CalculationProperty?.Description,
                            DisplayFolder = CalculationProperty?.DisplayFolder,
                            //AMO format string is wrapped in single quotes, so we need to get rid of them here
                            FormatString = CalculationProperty?.FormatString.Substring(1, CalculationProperty.FormatString.Length - 2),
                            IsHidden     = CalculationProperty == null ? true : !CalculationProperty.Visible
                        };

                        //Add Translations
                        if (CalculationProperty != null)
                        {
                            foreach (AMO.Translation AMOTranslation in CalculationProperty.Translations)
                            {
                                TranslationHelper.AddTOMTranslation(TOMDatabase, TOMMeasure, AMOTranslation);
                            }
                        }

                        //Add the Measure
                        TOMDatabase.Model.Tables[TableName].Measures.Add(TOMMeasure);
                    }
                }
            }
            #endregion

            #region Perspectives
            foreach (AMO.Perspective AMOPerspective in AMODatabase.Cubes[0].Perspectives)
            {
                //Create the Perspective
                TOM.Perspective TOMPerspective = new TOM.Perspective
                {
                    Name        = AMOPerspective.Name,
                    Description = AMOPerspective.Description
                };
                foreach (AMO.PerspectiveDimension AMOPerspectiveDimension in AMOPerspective.Dimensions)
                {
                    //Find the TOM Table equivelant to the AMO Perspective Dimension
                    TOM.Table TOMTable = TOMDatabase.Model.Tables[AMOPerspectiveDimension.Dimension.Name];

                    //Create the Perspective Table
                    TOM.PerspectiveTable TOMPerspectiveTable = new TOM.PerspectiveTable {
                        Table = TOMTable
                    };

                    //Add Columns
                    foreach (AMO.PerspectiveAttribute PerspectiveAttribute in AMOPerspectiveDimension.Attributes)
                    {
                        TOMPerspectiveTable.PerspectiveColumns.Add(new TOM.PerspectiveColumn
                        {
                            Column = TOMTable.Columns[PerspectiveAttribute.Attribute.Name]
                        });
                    }

                    //Add Hierarchies
                    foreach (AMO.PerspectiveHierarchy PerspectiveHierarchy in AMOPerspectiveDimension.Hierarchies)
                    {
                        TOMPerspectiveTable.PerspectiveHierarchies.Add(new TOM.PerspectiveHierarchy
                        {
                            Hierarchy = TOMTable.Hierarchies[PerspectiveHierarchy.Hierarchy.Name]
                        });
                    }

                    //Add Measures
                    foreach (AMO.PerspectiveCalculation PerspectiveCalculation in AMOPerspective.Calculations)
                    {
                        foreach (TOM.Measure TOMMeasure in TOMTable.Measures)
                        {
                            if (
                                PerspectiveCalculation.Type == AMO.PerspectiveCalculationType.Member &&
                                PerspectiveCalculation.Name == "[Measures].[" + TOMMeasure.Name + "]"
                                )
                            {
                                TOMPerspectiveTable.PerspectiveMeasures.Add(new TOM.PerspectiveMeasure {
                                    Measure = TOMMeasure
                                });
                            }
                        }
                    }

                    //Add the Perspective Table
                    TOMPerspective.PerspectiveTables.Add(TOMPerspectiveTable);
                }
                //Add the Perspective
                TOMDatabase.Model.Perspectives.Add(TOMPerspective);
            }
            #endregion

            #region Roles
            foreach (AMO.Role AMORole in AMODatabase.Roles)
            {
                //Create the Role
                TOM.ModelRole TOMRole = new TOM.ModelRole
                {
                    Name        = AMORole.Name,
                    Description = AMORole.Description
                };

                //Determine the ModelPermission from the equivelant DatabasePermission
                foreach (AMO.DatabasePermission Permission in AMODatabase.DatabasePermissions)
                {
                    if (Permission.Role.Name == AMORole.Name)
                    {
                        if (Permission.Administer)
                        {
                            TOMRole.ModelPermission = TOM.ModelPermission.Administrator;
                        }
                        else if (Permission.Read == AMO.ReadAccess.Allowed && Permission.Process)
                        {
                            TOMRole.ModelPermission = TOM.ModelPermission.ReadRefresh;
                        }
                        else if (Permission.Read == AMO.ReadAccess.Allowed)
                        {
                            TOMRole.ModelPermission = TOM.ModelPermission.Read;
                        }
                        else if (Permission.Process)
                        {
                            TOMRole.ModelPermission = TOM.ModelPermission.Refresh;
                        }
                        else
                        {
                            TOMRole.ModelPermission = TOM.ModelPermission.None;
                        }
                    }
                }

                //Add the Row Level Security
                foreach (AMO.Dimension Dimension in AMODatabase.Dimensions)
                {
                    foreach (AMO.DimensionPermission DimensionPermission in Dimension.DimensionPermissions)
                    {
                        if (DimensionPermission.Role.Name == TOMRole.Name)
                        {
                            //Create the Table Permission
                            TOM.TablePermission TablePermission = new TOM.TablePermission
                            {
                                Table            = TOMDatabase.Model.Tables[Dimension.Name],
                                FilterExpression = DimensionPermission.AllowedRowsExpression
                            };

                            //Add the Table Permission to the Role
                            TOMRole.TablePermissions.Add(TablePermission);
                        }
                    }
                }

                //Add Role Members
                foreach (AMO.RoleMember AMOMember in AMORole.Members)
                {
                    //Create the Role Member
                    TOM.ModelRoleMember TOMMember = new TOM.WindowsModelRoleMember
                    {
                        MemberID   = AMOMember.Sid,
                        MemberName = AMOMember.Name
                    };

                    //Add the Member to the Role
                    TOMRole.Members.Add(TOMMember);
                }

                //Add the Role to the Database
                TOMDatabase.Model.Roles.Add(TOMRole);
            }
            #endregion

            #region Relationships
            foreach (AMO.Dimension Dimension in AMODatabase.Dimensions)
            {
                foreach (AMO.Relationship AMORelationship in Dimension.Relationships)
                {
                    //Get To and From columns
                    AMO.Dimension FromDimension = AMODatabase.Dimensions[AMORelationship.FromRelationshipEnd.DimensionID];
                    AMO.Dimension ToDimension   = AMODatabase.Dimensions[AMORelationship.ToRelationshipEnd.DimensionID];
                    TOM.Table     FromTable     = TOMDatabase.Model.Tables[FromDimension.Name];
                    TOM.Column    FromColumn    = FromTable.Columns[FromDimension.Attributes[AMORelationship.FromRelationshipEnd.Attributes[0].AttributeID].Name];
                    TOM.Table     ToTable       = TOMDatabase.Model.Tables[ToDimension.Name];
                    TOM.Column    ToColumn      = ToTable.Columns[ToDimension.Attributes[AMORelationship.ToRelationshipEnd.Attributes[0].AttributeID].Name];

                    //Create the Relationship
                    TOM.SingleColumnRelationship TOMRelationship = new TOM.SingleColumnRelationship
                    {
                        FromColumn = FromColumn,
                        ToColumn   = ToColumn,
                        //Set IsActive to false, and update later
                        IsActive = false
                    };

                    //Check if Relationship is active
                    foreach (AMO.MeasureGroupDimension MeasureGroupDimension in AMODatabase.Cubes[0].MeasureGroups.GetByName(FromTable.Name).Dimensions)
                    {
                        if (MeasureGroupDimension is AMO.ReferenceMeasureGroupDimension)
                        {
                            if (((AMO.ReferenceMeasureGroupDimension)MeasureGroupDimension).RelationshipID == AMORelationship.ID)
                            {
                                TOMRelationship.IsActive = true;
                            }
                        }
                    }

                    //Add the Relationship to the Database
                    TOMDatabase.Model.Relationships.Add(TOMRelationship);
                }
            }
            #endregion
            //Add TabularEditor compatibility annotation if necessary
            if (AddTabularEditorAnnotation)
            {
                TOMDatabase.Model.Annotations.Add(new TOM.Annotation {
                    Name  = "TabularEditor_CompatibilityVersion",
                    Value = AMODatabase.CompatibilityLevel.ToString()
                });
            }
            //TODO: Handle KPIs
            return(TOMDatabase);
        }
Example #13
0
        /// <summary>
        /// Adds an 'empty' table to the tabular model; no calculatedColumn informationis added
        /// </summary>
        /// <param name="tabularDatabase">A reference to an AMO database object</param>
        /// <param name="datasourceTableName">A string with the name of the table from where the data will come to populate the destination table</param>
        /// <param name="tableName">A string with the name of the tabular table added</param>
        public static void TableAddEmptyTable(AMO.Database tabularDatabase,
                                              string datasourceTableName,
                                              string tableName,
                                              string defaultPartitionFilterClause = null,
                                              bool updateInstance = true
                                              )
        {
            //  Table creation strategy:
            //  Because a table is a combination of a Dimension and a MeasureGroup
            //  there are different ways to create the table, here we have two
            //  possibilities
            //
            //  A   Create the entire Dimension object with attributes, add the
            //      reference to the dimension in the cube, create the entire
            //      MeasureGroup (associated to the dimension and attributes),
            //      add the default partition to the MeasureGroup, update Primary
            //      Key attribute from source table and, finally, process
            //      partition (if user wants it).
            //      This sequence of steps implies adding all table columns to
            //      the dimension and later iterate again over the same columns
            //      to add them to the 'degenerated' dimension of the MeasureGroup
            //
            //  B   Create an 'empty' Dimension object (with only the rownumber
            //      attribute), add the reference to the dimension in the cube,
            //      create the MeasureGroup object (based on the 'empty'dimension),
            //      add the default partition, add all columns in the source
            //      table to both dimension and degenerated dimension in
            //      MeasureGroup, update Primary Key and, finally, process
            //      partition (if user wants it).
            //      This sequence of steps implies creating the skeleton
            //      infrastructure of a table and later add all columns using
            //      ColumnAdd function.
            //      This is the approach used to create a table in this sample
            //
            //  Major steps in creating a table in the database
            //
            //  - Validate required input arguments
            //      - Verify there is 1 and only 1 cube in the database; as part of the initial conditions validations
            //
            //  - Create empty local copy of Dimension object
            //  - Add Dimension reference to cube
            //  - Add empty MeasureGroup to cube
            //  - Adding default Measure to MeasureGroup
            //  - Add 'Dimension' to MeasureGroup
            //  - Add partition to MeasureGroup
            //
            //  Note: There are no validations for duplicated names, invalid names or
            //  similar scenarios. It is expected the server will take care of them and
            //  throw exceptions on any invalid situation.
            //
            //  Note:   Only one DataSourceView is used in Tabular Models
            //          ==> tabularDatabase.DataSourceViews[0] represents the DSV of the model
            //
            //  Note:   Only one Cube is used in Tabular Models
            //          ==> tabularDatabase.Cubes[0] represents the cube in the model
            //
            //  Note:   Microsoft design tools use the following pattern to keep track of the
            //          datasource matching elements:
            //          DataSourceView->TableName <---> Dimension.ID, MeasureGroup.ID
            //          DataSourceView->ColumnName <---> Dimension->ColumnID, MeasureGroup.DegeneratedDimension->CoumnID
            //          So far, this sample follows the same pattern.
            //
            //          WARNING:    Breaking the above pattern when creating your
            //                      own AMO to Tabular functions might lead to
            //                      unpredictable behavior when using Microsoft
            //                      Design tools in your models.



            //  Validate required input arguments
            if (tabularDatabase == null)
            {
                throw new ArgumentNullException(TabularDatabaseStringName);
            }
            if (datasourceTableName.IsNullOrEmptyOrWhitespace())
            {
                throw new ArgumentNullException("datasourceTableName");
            }
            if (tableName.IsNullOrEmptyOrWhitespace())
            {
                throw new ArgumentNullException(TableStringName);
            }


            //  Validate other intitial conditions: Verify there is only one cube in the database
            if (tabularDatabase.Cubes.Count != 1)
            {
                throw new InvalidOperationException(string.Format(CultureInfo.InvariantCulture, Resources.InvalidNumberOfCubesInvalidOperationException, tabularDatabase.Cubes.Count));
            }


            //  Create empty local copy of Dimension object in two (2) steps:
            //  -   Define Dimension general properties
            //  -   Manualy add "RowNumber" calculatedColumn
            //
            //  Define Dimension general properties
            string rowNumberColumnName = string.Format(CultureInfo.InvariantCulture, "RowNumber_{0}", Guid.NewGuid()); //  Making sure the RowNumber calculatedColumn has a unique name

            using (AMO.Dimension tableDimension = tabularDatabase.Dimensions.Add(tableName, datasourceTableName))
            {
                tableDimension.Source                               = new AMO.DataSourceViewBinding(tabularDatabase.DataSourceViews[0].ID);
                tableDimension.StorageMode                          = AMO.DimensionStorageMode.InMemory;
                tableDimension.UnknownMember                        = AMO.UnknownMemberBehavior.AutomaticNull;
                tableDimension.UnknownMemberName                    = "Unknown";
                tableDimension.ErrorConfiguration                   = new AMO.ErrorConfiguration();
                tableDimension.ErrorConfiguration.KeyNotFound       = AMO.ErrorOption.IgnoreError;
                tableDimension.ErrorConfiguration.KeyDuplicate      = AMO.ErrorOption.ReportAndStop;
                tableDimension.ErrorConfiguration.NullKeyNotAllowed = AMO.ErrorOption.ReportAndStop;
                tableDimension.ProactiveCaching                     = new AMO.ProactiveCaching();
                TimeSpan defaultProactiveChachingTimeSpan = new TimeSpan(0, 0, -1);
                tableDimension.ProactiveCaching.SilenceInterval         = defaultProactiveChachingTimeSpan;
                tableDimension.ProactiveCaching.Latency                 = defaultProactiveChachingTimeSpan;
                tableDimension.ProactiveCaching.SilenceOverrideInterval = defaultProactiveChachingTimeSpan;
                tableDimension.ProactiveCaching.ForceRebuildInterval    = defaultProactiveChachingTimeSpan;
                tableDimension.ProactiveCaching.Source = new AMO.ProactiveCachingInheritedBinding();

                //  Manualy add a "RowNumber" attribute as the key attribute of the dimension
                //  "RowNumber" is a required calculatedColumn 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 calculatedColumn name and calculatedColumn ID of the RowNumber attribute to
                //  "RowNumber"; and, to "InternalRowNumber" if there is a collition with a user calculatedColumn named "RowNumber"
                //  In this sample, to avoid problems with any customer table that contains a calculatedColumn named "RowNumber" and/or "InternalRowNumber"
                //  the Name and Id value of the calculatedColumn (in the dimension object) will be renamed to
                //  'RowNumber_<NewGuid()>';
                //  For that purpose the variable rowNumberColumnId was defined above

                using (AMO.DimensionAttribute rowNumber = tableDimension.Attributes.Add(rowNumberColumnName, rowNumberColumnName))
                {
                    rowNumber.Type = AMO.AttributeType.RowNumber;
                    rowNumber.KeyUniquenessGuarantee = true;
                    rowNumber.Usage = AMO.AttributeUsage.Key;
                    rowNumber.KeyColumns.Add(new AMO.DataItem());
                    rowNumber.KeyColumns[0].DataType       = System.Data.OleDb.OleDbType.Integer;
                    rowNumber.KeyColumns[0].DataSize       = 4;
                    rowNumber.KeyColumns[0].NullProcessing = AMO.NullProcessing.Error;
                    rowNumber.KeyColumns[0].Source         = new AMO.RowNumberBinding();
                    rowNumber.NameColumn                = new AMO.DataItem();
                    rowNumber.NameColumn.DataType       = System.Data.OleDb.OleDbType.WChar;
                    rowNumber.NameColumn.DataSize       = 4;
                    rowNumber.NameColumn.NullProcessing = AMO.NullProcessing.ZeroOrBlank;
                    rowNumber.NameColumn.Source         = new AMO.RowNumberBinding();
                    rowNumber.OrderBy = AMO.OrderBy.Key;
                    rowNumber.AttributeHierarchyVisible = false;
                }
            }

            //  Add Dimension reference to cube
            tabularDatabase.Cubes[0].Dimensions.Add(datasourceTableName, tableName, datasourceTableName);

            //  Add empty MeasureGroup to cube
            using (AMO.MeasureGroup tableMeasureGroup = tabularDatabase.Cubes[0].MeasureGroups.Add(tableName, datasourceTableName))
            {
                tableMeasureGroup.StorageMode    = AMO.StorageMode.InMemory;
                tableMeasureGroup.ProcessingMode = AMO.ProcessingMode.Regular;


                //  Add default Measure to MeasureGroup
                string defaultMeasureID = string.Concat("_Count ", tableName);
                using (AMO.Measure defaultMeasure = tableMeasureGroup.Measures.Add(defaultMeasureID, defaultMeasureID))
                    using (AMO.RowBinding defaultMeasureRowBinding = new AMO.RowBinding(datasourceTableName))
                        using (AMO.DataItem defaultMeasureSource = new AMO.DataItem(defaultMeasureRowBinding))
                        {
                            defaultMeasure.AggregateFunction = AMO.AggregationFunction.Count;
                            defaultMeasure.DataType          = AMO.MeasureDataType.BigInt;
                            defaultMeasure.Visible           = false;
                            defaultMeasureSource.DataType    = System.Data.OleDb.OleDbType.BigInt;
                            defaultMeasure.Source            = defaultMeasureSource;
                        }


                //  Add 'Dimension' to MeasureGroup
                using (AMO.DegenerateMeasureGroupDimension defaultMGDim = new AMO.DegenerateMeasureGroupDimension(tableName))
                    using (AMO.MeasureGroupAttribute mga = new AMO.MeasureGroupAttribute(rowNumberColumnName))
                        using (AMO.ColumnBinding rowNumberColumnBinding = new AMO.ColumnBinding(datasourceTableName, rowNumberColumnName))
                            using (AMO.DataItem rowNumberKeyColumn = new AMO.DataItem(rowNumberColumnBinding))
                            {
                                defaultMGDim.ShareDimensionStorage = AMO.StorageSharingMode.Shared;
                                defaultMGDim.CubeDimensionID       = datasourceTableName;
                                mga.Type = AMO.MeasureGroupAttributeType.Granularity;
                                rowNumberKeyColumn.DataType = System.Data.OleDb.OleDbType.Integer;
                                mga.KeyColumns.Add(rowNumberKeyColumn);
                                defaultMGDim.Attributes.Add(mga);
                                tableMeasureGroup.Dimensions.Add(defaultMGDim);
                            }


                //  Add default partition to MeasureGroup
                StringBuilder partitionSqlStatement = new StringBuilder();
                partitionSqlStatement.Append("SELECT * FROM [");
                partitionSqlStatement.Append(datasourceTableName);
                partitionSqlStatement.Append("]");
                if (defaultPartitionFilterClause != null)
                {
                    partitionSqlStatement.Append(" WHERE ");
                    partitionSqlStatement.Append(defaultPartitionFilterClause);
                }

                //  Create partition, but do not update server instance until comming back and procesing what the user requests in 'updateInstance'
                //  Note:   As a best practice every time the object model is altered a database update needs to be issued; however, in this case
                //          to avoid multiple database updates while creating one major object (ie, Table) we are invoking PartitionAdd with NO updateInstance

                PartitionAdd(tabularDatabase, tableName, tableName, partitionSqlStatement.ToString(), false);
            }

            //  Update server instance
            if (updateInstance)
            {
                tabularDatabase.Update(AMO.UpdateOptions.ExpandFull, AMO.UpdateMode.UpdateOrCreate);
            }
        }