/// <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);
            }
        }
        /// <summary>
        /// Create measure group
        /// </summary>
        /// <param name="dsv"></param>
        /// <param name="sqlHelper"></param>
        /// <param name="asMeta"></param>
        /// <param name="cube"></param>
        /// <param name="is_rolap"></param>
        /// <param name="measure_group_id"></param>
        public void CREATE_MEASURE_GROUP(DataSourceView dsv
                                         , DB_SQLHELPER_BASE sqlHelper
                                         , AS_METADATA asMeta
                                         , Cube cube
                                         , int is_rolap_cube
                                         , String measure_group_id = null)
        {
            try
            {
                DataTable MGSet         = asMeta.GET_SSAS_MEASURE_GROUPS_SET(sqlHelper, is_rolap_cube);
                String    DSVSchemaName = "";
                foreach (DataRow row in MGSet.Rows)
                {
                    String       measureGroupID     = row["measure_group_id"].ToString();
                    String       measureGroupName   = row["measure_group_name"].ToString();
                    String       DependedFactTable  = row["depended_fact_table"].ToString();
                    String       KeyNotFound_Action = row["key_not_found_action"].ToString();
                    int          is_rolap_mg        = Convert.ToInt16(row["is_rolap_mg"].ToString());
                    MeasureGroup newMG          = AS_API.ADD_MEASURE_GROUP(sqlHelper, cube, measureGroupName, measureGroupID, is_rolap_mg, KeyNotFound_Action);
                    DataTable    dimUsageSet    = asMeta.GET_SSAS_DIM_USAGE_SET(sqlHelper, measureGroupID);
                    DataTable    CoreMeasureSet = asMeta.GET_SSAS_CORE_MEASURES_SET(sqlHelper, measureGroupID);
                    foreach (DataRow measure in CoreMeasureSet.Rows)
                    {
                        measureGroupID   = measure["measure_group_id"].ToString();
                        measureGroupName = measure["measure_group_name"].ToString();
                        String MeasureId       = measure["measure_id"].ToString();
                        String MeasureName     = measure["measure_name"].ToString();
                        String MeasureDataType = measure["measure_data_type"].ToString();
                        String DBColumn        = measure["db_column"].ToString();
                        DSVSchemaName = measure["dsv_schema_name"].ToString();
                        String AggregationFunction = measure["aggregation_function"].ToString();
                        String DisplayFolder       = measure["display_folder"].ToString();
                        String FormatString        = measure["format_string"].ToString();
                        AS_API.ADD_MEASURE_TO_MEASURE_GROUP(
                            sqlHelper,
                            newMG,
                            DSVSchemaName,
                            DBColumn,
                            MeasureName,
                            MeasureId,
                            DisplayFolder,
                            FormatString,
                            AggregationFunction,
                            true,
                            MeasureDataType,
                            MeasureDataType);
                    }
                    foreach (DataRow dimUsage in dimUsageSet.Rows)
                    {
                        String DimUsageType      = dimUsage["dim_usage_type"].ToString();
                        String InternalDimID     = dimUsage["internal_dim_id"].ToString();
                        String InternalDimAttrID = dimUsage["internal_dim_attrid"].ToString();
                        DSVSchemaName = dimUsage["dsv_schema_name"].ToString();
                        String factFKDimColumnName    = dimUsage["fact_fk_dim_column_name"].ToString();
                        String DataType               = dimUsage["fact_fk_dim_column_data_type"].ToString();
                        String DimensionID            = dimUsage["dimension_id"].ToString();
                        String AttributeID            = dimUsage["attribute_id"].ToString();
                        String InternalMeasureGroupID = dimUsage["internal_measure_group_id"].ToString();
                        switch (DimUsageType.ToLower())
                        {
                        case "regular":
                            DataItem factDataItem = AS_API.CREATE_DATA_ITEM(
                                sqlHelper,
                                dsv,
                                DSVSchemaName,
                                factFKDimColumnName,
                                AS_API_HELPER.GET_SSAS_OLEDB_TYPE_BY_NAME(DataType));
                            AS_API.ADD_DIM_USAGE_REGULAR_RELATIONSHIP(
                                sqlHelper,
                                cube,
                                newMG,
                                factDataItem,
                                DimensionID,
                                AttributeID);
                            break;

                        case "reference":
                            AS_API.ADD_DIM_USAGE_REFERENCE_RELATIONSHIP(newMG,
                                                                        DimensionID,
                                                                        AttributeID,
                                                                        InternalDimID,
                                                                        InternalDimAttrID);
                            break;

                        case "manytomany":
                            AS_API.ADD_DIM_USAGE_MANY_RELATIONSHIP(newMG, InternalMeasureGroupID, DimensionID);
                            break;

                        case "fact":
                            AS_API.ADD_DIM_USAGE_FACT_RELATIONSHIP(newMG, InternalDimAttrID, DimensionID);
                            break;

                        default:
                            break;
                        }
                    }
                    AggregationDesign agg_design = CREATE_AGGREGATION_DESIGN(newMG, sqlHelper, asMeta);
                    CREATE_CUBE_PARTITION_FOR_MEASURE_GROUP(sqlHelper, asMeta, cube, is_rolap_cube, newMG, agg_design, is_rolap_mg, DependedFactTable, DSVSchemaName);
                    newMG.Update();
                }
            }
            catch (Exception ex)
            {
                sqlHelper.ADD_MESSAGE_LOG(ex.Message.ToString(), MESSAGE_TYPE.MEASURE_GROUP, MESSAGE_RESULT_TYPE.Error);
                throw (ex);
            }
        }
示例#3
0
        /// <summary>
        /// Add measure into a measure group
        /// </summary>
        /// <param name="measureGroup"></param>
        /// <param name="tableID"></param>
        /// <param name="columnID"></param>
        /// <param name="measureName"></param>
        /// <param name="measureID"></param>
        /// <param name="displayFolder"></param>
        /// <param name="formatStr"></param>
        /// <param name="aggregationFunction"></param>
        /// <param name="visible"></param>
        /// <param name="sourceColDataType"></param>
        /// <param name="measureDataType"></param>
        internal static void ADD_MEASURE_TO_MEASURE_GROUP(
            DB_SQLHELPER_BASE sqlHelper
            , MeasureGroup measureGroup
            , String tableID
            , String columnID
            , String measureName
            , String measureID
            , String displayFolder
            , String formatStr
            , String aggregationFunction
            , bool visible             = true
            , String sourceColDataType = "double"
            , String measureDataType   = "double")
        {
            Microsoft.AnalysisServices.DataItem source = new Microsoft.AnalysisServices.DataItem();
            source.NullProcessing = NullProcessing.Preserve;
            Measure measure = new Measure(measureName, measureID);
            String  aggType = aggregationFunction.ToLower();

            measure.DataType = AS_API_HELPER.GET_SSAS_MEASURE_DATA_TYPE_BY_NAME(measureDataType);
            if (aggType == "count*")
            {
                RowBinding rowBind = new RowBinding();
                rowBind.TableID           = tableID;
                measure.AggregateFunction = AggregationFunction.Count;
                source.Source             = rowBind;
                measure.Source            = source;
                measure.DataType          = MeasureDataType.Integer;
                //source.DataType = AS_API_HELPER.GET_SSAS_OLEDB_TYPE_BY_NAME(sourceColDataType);
            }
            else
            {
                ColumnBinding colBind = new ColumnBinding();
                colBind.TableID           = tableID;
                colBind.ColumnID          = columnID;
                source.DataType           = AS_API_HELPER.GET_SSAS_OLEDB_TYPE_BY_NAME(sourceColDataType);
                source.Source             = colBind;
                measure.AggregateFunction = AS_API_HELPER.GET_SSAS_AGGREGATION_FUNCTION_BY_NAME(aggType.ToLower());
                if (aggType.ToLower() == "distinctcount")
                {
                    source.NullProcessing = NullProcessing.Automatic;
                    source.DataType       = AS_API_HELPER.GET_SSAS_OLEDB_TYPE_BY_NAME("integer");
                    measure.DataType      = MeasureDataType.Integer;
                }
                measure.Source = source;
            }
            String dataType = sourceColDataType.ToLower();

            measure.DisplayFolder = displayFolder;
            //measure.FormatString = formatStr
            measure.Visible = visible;
            Measure measureEx = measureGroup.Measures.Find(measureID);

            if (measureEx != null)
            {
                sqlHelper.ADD_MESSAGE_LOG(

                    String.Format("measure {0} exists", measureName),
                    MESSAGE_TYPE.MEASURES, MESSAGE_RESULT_TYPE.Warning);
                measureEx.Name = measure.Name;
                measureEx.AggregateFunction = measure.AggregateFunction;
                measureEx.DataType          = AS_API_HELPER.GET_SSAS_MEASURE_DATA_TYPE_BY_NAME(measureDataType);
                measureEx.DisplayFolder     = measure.DisplayFolder;
                measureEx.Visible           = measure.Visible;
                measureEx.FormatString      = measure.FormatString;
                measureEx.Source            = source.Clone();
            }
            else
            {
                sqlHelper.ADD_MESSAGE_LOG(
                    String.Format("Added measure {0} into measure group {1}", measureName, measureGroup.Name),
                    MESSAGE_TYPE.MEASURES, MESSAGE_RESULT_TYPE.Succeed);
                measureGroup.Measures.Add(measure);
            }
        }