public DataTable GET_SSAS_CORE_MEASURES_SET(DB_SQLHELPER_BASE sqlHelper, String measure_group_id = null) { String QueryString = String.Format(@" SELECT DISTINCT mg.measure_group_id AS measure_group_id, mg.measure_group_name AS measure_group_name, mea.measure_id AS measure_id, mea.measure_name AS measure_name, mapp.measure_data_type AS measure_data_type, Isnull(mapp.db_column_customized, mapp.db_column_default) AS db_column, mg.dsv_schema_name AS dsv_schema_name, Isnull(mapp.aggregation_function_customized, mapp.aggregation_function_default) AS aggregation_function, mea.display_folder AS display_folder, mea.format_string AS format_string FROM ssas_measures AS mea WITH(nolock) INNER JOIN ssas_measures_mapping AS mapp WITH(nolock) ON mea.measure_id = mapp.measure_id INNER JOIN ssas_measure_group AS mg WITH(nolock) ON mg.measure_group_id = mapp.measure_group_id INNER JOIN ssas_measures_description AS descr WITH(nolock) ON descr.measure_id = mea.measure_id WHERE mea.is_enabled = 1 AND mg.measure_group_id='{0}'", measure_group_id); return(sqlHelper.EXECUTE_SQL_QUERY_RETURN_TABLE(sqlHelper, QueryString)); }
public DataTable GET_SSAS_DIM_USAGE_SET(DB_SQLHELPER_BASE sqlHelper, String measure_group_id = null) { String QueryString = String.Format(@" SELECT mg.measure_group_id AS measure_group_id, usage.dim_usage_type AS dim_usage_type, mg.dsv_schema_name AS dsv_schema_name, Isnull(usage.fact_fk_dim_column_name_customized, usage.fact_fk_dim_column_name) AS fact_fk_dim_column_name, Isnull(usage.fact_fk_dim_column_data_type_customized, usage.fact_fk_dim_column_data_type) AS fact_fk_dim_column_data_type, usage.dimension_id AS dimension_id, usage.attribute_id AS attribute_id, usage.internal_dim_id AS internal_dim_id, usage.internal_dim_attrid AS internal_dim_attrid, usage.internal_measure_group_id AS internal_measure_group_id FROM ssas_dim_usage AS usage WITH(nolock) INNER JOIN ssas_measure_group AS mg WITH(nolock) ON usage.measure_group_id = mg.measure_group_id -- INNER JOIN ssas_etl_module AS module WITH(nolock) -- ON module.module_name = Replace(mg.dsv_schema_name, 'olap_', '') -- AND module.is_enabled = 1 WHERE mg.measure_group_id = '{0}' ORDER BY CASE WHEN Lower(usage.dim_usage_type) = 'regular' THEN '1' WHEN Lower(usage.dim_usage_type) = 'reference' THEN '2' WHEN Lower(usage.dim_usage_type) = 'manytomany' THEN '3' ELSE '4' END ", measure_group_id); return(sqlHelper.EXECUTE_SQL_QUERY_RETURN_TABLE(sqlHelper, QueryString)); }
public DataTable EXECUTE_SQL_QUERY_RETURN_TABLE(DB_SQLHELPER_BASE sqlHelper, String SqlQuery) { DataTable returnTable = null; try { IDbCommand iCom = sqlHelper.GET_DATABASE_CONNECTION().CreateCommand(); iCom.CommandText = SqlQuery; iCom.CommandTimeout = Convert.ToInt32(CONFIGURATION_HELPER.GET_METADATA_PROPERTY("command_time_out"));; IDbDataAdapter iAdap = GET_DATABASE_ADAPATER_DEFAULT(); iAdap.SelectCommand = iCom; DataSet dataSet = new System.Data.DataSet(); iAdap.Fill(dataSet); iCom.Dispose(); returnTable = dataSet.Tables[0]; } catch (Exception ex) { sqlHelper.ADD_MESSAGE_LOG(ex.Message, MESSAGE_TYPE.SQLQuery, MESSAGE_RESULT_TYPE.Error); throw(ex); } finally { CLOSE_CONNECTION(); } return(returnTable); }
/// <summary> /// Add dimension into cube (instead of cube db) /// </summary> /// <param name="sqlHelper"></param> /// <param name="asMeta"></param> /// <param name="cubeDB"></param> /// <param name="cube"></param> public void ADD_DIMENSION_TO_CUBE(DB_SQLHELPER_BASE sqlHelper , AS_METADATA asMeta , Microsoft.AnalysisServices.Database cubeDB , Cube cube ) { try { DataTable DimensionSet = asMeta.GET_SSAS_DIMENSION_SET(sqlHelper); foreach (DataRow row in DimensionSet.Rows) { String DimensionID = row["dimension_id"].ToString(); String DimensionName = row["dimension_name"].ToString(); String DimensionType = row["dimension_type"].ToString(); AS_API.ADD_CUBE_DIMENSION( sqlHelper, cubeDB, cube, DimensionID, DimensionType); sqlHelper.ADD_MESSAGE_LOG( String.Format("Addedd Dimension {0} into Cube ", DimensionID) , MESSAGE_TYPE.ADD_CUBE_DIMENSION , MESSAGE_RESULT_TYPE.Normal); } cube.Update(Microsoft.AnalysisServices.UpdateOptions.ExpandFull); } catch (Exception ex) { sqlHelper.ADD_MESSAGE_LOG(ex.Message.ToString(), MESSAGE_TYPE.DIMENSION, MESSAGE_RESULT_TYPE.Error); throw (ex); } }
/// <summary> /// Cube full process /// </summary> /// <param name="cube_server"></param> /// <param name="cubeDBName"></param> /// <param name="xmla"></param> public void CUBE_PROCESS_FULL(DB_SQLHELPER_BASE sqlHelper, Server cube_server, String cubeDBName) { try { String cubeProcessXMLAPath = CONFIGURATION_HELPER.BASIC_CONFIGURATION_FOLDER + @"\SSASConfiguration\CubeProcess.xml"; String cubeProcessXMLA = System.IO.File.ReadAllText(cubeProcessXMLAPath); cubeProcessXMLA = cubeProcessXMLA.Replace("$(cubeDBName)", cubeDBName); XmlaResultCollection _result = cube_server.Execute(cubeProcessXMLA); foreach (XmlaResult _res in _result) { foreach (XmlaMessage message in _res.Messages) { sqlHelper.ADD_MESSAGE_LOG( message.ToString(), MESSAGE_TYPE.CUBE_PROCESS, MESSAGE_RESULT_TYPE.Normal); } } sqlHelper.ADD_MESSAGE_LOG( String.Format("Processed cube {0}", cubeDBName), MESSAGE_TYPE.CUBE_PROCESS, MESSAGE_RESULT_TYPE.Normal); } catch (Exception ex) { sqlHelper.ADD_MESSAGE_LOG(ex.Message.ToString(), MESSAGE_TYPE.CUBE_PROCESS, MESSAGE_RESULT_TYPE.Error); throw (ex); } }
public int EXECUTE_PROCEDURE_WITH_PARAMETERS(DB_SQLHELPER_BASE sqlHelper, String StoreProcedureName, IDataParameter[] PassedParameters) { int return_value = 0; try { IDbCommand iCom = sqlHelper.GET_DATABASE_CONNECTION().CreateCommand(); iCom.CommandType = System.Data.CommandType.StoredProcedure; iCom.CommandText = StoreProcedureName; foreach (IDataParameter parameter in PassedParameters) { iCom.Parameters.Add(parameter); } iCom.CommandTimeout = Convert.ToInt32(CONFIGURATION_HELPER.GET_METADATA_PROPERTY("command_time_out")); iCom.ExecuteNonQuery(); iCom.Dispose(); return_value = 1; } catch (Exception ex) { sqlHelper.ADD_MESSAGE_LOG(ex.Message, MESSAGE_TYPE.SQLQuery, MESSAGE_RESULT_TYPE.Error); throw (ex); } finally { CLOSE_CONNECTION(); } return(return_value); }
/// <summary> /// Add cube dimension (instead of cube db) /// </summary> /// <param name="cubedb"></param> /// <param name="cube"></param> /// <param name="dimID"></param> /// <param name="dimension_type"></param> /// <param name="cube_dimName"></param> /// <param name="visible"></param> internal static void ADD_CUBE_DIMENSION( DB_SQLHELPER_BASE sqlHelper, Database cubedb, Cube cube, String dimID, String dimension_type, String cube_dimName = "", bool visible = true) { Dimension dim = cubedb.Dimensions.Find(dimID); if (dim == null) { sqlHelper.ADD_MESSAGE_LOG( String.Format("Detected dimension name [{0}] is not existed in current cube db", cube_dimName), MESSAGE_TYPE.DIMENSION, MESSAGE_RESULT_TYPE.Warning); } CubeDimension cube_dim = cube.Dimensions.Add(dim.ID); cube_dim.Visible = visible; cube_dim.Name = dim.Name; sqlHelper.ADD_MESSAGE_LOG( String.Format("Added dimension [{0}] into cube (instead of cube db)", cube.Dimensions.FindByName(dim.Name).Name), MESSAGE_TYPE.DIMENSION, MESSAGE_RESULT_TYPE.Succeed); }
/// <summary> /// Add dimension into Cube dataBase, instead of cube /// </summary> /// <param name="cubedb">Cube dataBase</param> /// <param name="datasourcename">dataSourceName</param> /// <param name="dimensionid">dimension id</param> /// <param name="dimensionname">dimension id</param> /// <param name="dim_type">dimension type, eg..time、regular</param> /// <returns></returns> internal static Dimension ADD_DIMENSION( DB_SQLHELPER_BASE sqlHelper, Database cubedb, String datasourcename, String dimensionid, String dimensionname, String dim_type) { Dimension dim = cubedb.Dimensions.FindByName(dimensionname); try { String[] nullvalue = new String[] { "null" }; if (!nullvalue.Contains(dimensionname)) { dim = cubedb.Dimensions.Add(dimensionid); dim.Name = dimensionname; dim.Type = DimensionType.Regular; if (dim_type.ToLower() == "time") { dim.Type = DimensionType.Time; } dim.Source = new DataSourceViewBinding(datasourcename); dim.StorageMode = DimensionStorageMode.Molap; dim.ProcessingGroup = ProcessingGroup.ByAttribute; } sqlHelper.ADD_MESSAGE_LOG( String.Format("Added dimension [{0}]", dimensionname), MESSAGE_TYPE.DIMENSION, MESSAGE_RESULT_TYPE.Succeed); } finally { } return(dim); }
/// <summary> /// Add regular dim usage /// </summary> /// <param name="cube"></param> /// <param name="measureGroup"></param> /// <param name="factDataItem"></param> /// <param name="dimID"></param> /// <param name="dimAttrId"></param> /// <returns>Regular dim usage</returns> internal static RegularMeasureGroupDimension ADD_DIM_USAGE_REGULAR_RELATIONSHIP( DB_SQLHELPER_BASE sqlHelper, Cube cube, MeasureGroup measureGroup, DataItem factDataItem, String dimID, String dimAttrId) { RegularMeasureGroupDimension regMgDim = null; CubeDimension curDim = cube.Dimensions.Find(dimID); if (curDim == null) { sqlHelper.ADD_MESSAGE_LOG( String.Format("Cann't find dimension {0}", dimID), MESSAGE_TYPE.DIM_USAGE_REGULAR, MESSAGE_RESULT_TYPE.Error); } regMgDim = new RegularMeasureGroupDimension(curDim.Name); regMgDim.CubeDimensionID = dimID; measureGroup.Dimensions.Add(regMgDim); MeasureGroupAttribute mgAttr = regMgDim.Attributes.Add(dimAttrId); mgAttr.Type = MeasureGroupAttributeType.Granularity; mgAttr.KeyColumns.Add(factDataItem); return(regMgDim); }
/// <summary> /// Add relation ship to attribute /// </summary> /// <param name="dim"></param> /// <param name="basedAttributeID"></param> /// <param name="relatedAttributeID"></param> /// <param name="attributeRelationShipType"></param> /// <param name="relationShipName"></param> internal static void ADD_ATTRIBUTE_RELATIONSHIP( DB_SQLHELPER_BASE sqlHelper, Dimension dim, String basedAttributeID, String relatedAttributeID, RelationshipType attributeRelationShipType = RelationshipType.Flexible, String relationShipName = null) { DimensionAttribute attr = dim.Attributes.Find(basedAttributeID); DimensionAttribute relatedAttr = dim.Attributes.Find(relatedAttributeID); if (relationShipName == null) { relationShipName = relatedAttr.Name; } AttributeRelationship relationship = new AttributeRelationship(); relationship.Attribute = attr; relationship.Name = relationShipName; relationship.AttributeID = relatedAttributeID; if (attributeRelationShipType != null) { relationship.RelationshipType = attributeRelationShipType; } else { sqlHelper.ADD_MESSAGE_LOG( String.Format("A None RelationShipType is passed between [{0} and [{1}]", basedAttributeID, relatedAttributeID), MESSAGE_TYPE.ATTRIBUTE_RELATIONSHIP, MESSAGE_RESULT_TYPE.Warning); } if (!attr.AttributeRelationships.Contains(relatedAttributeID)) { attr.AttributeRelationships.Add(relationship); } }
/// <summary> /// Create data item /// </summary> /// <param name="dsv"></param> /// <param name="factTableName"></param> /// <param name="factFKDimColumnName"></param> /// <param name="dataType"></param> /// <returns></returns> internal static DataItem CREATE_DATA_ITEM( DB_SQLHELPER_BASE sqlHelper, DataSourceView dsv, String factTableName, String factFKDimColumnName, System.Data.OleDb.OleDbType dataType) { DataTable data_table = dsv.Schema.Tables[factTableName]; DataColumn dataColumn = data_table.Columns[factFKDimColumnName]; if (dataColumn == null) { sqlHelper.ADD_MESSAGE_LOG( String.Format("Table [{0}] doesn't have column [{0}]", factTableName, factFKDimColumnName), MESSAGE_TYPE.COLUMN_BINDING_DATA_ITEM, MESSAGE_RESULT_TYPE.Error); } DataItem dataItem = new DataItem(factTableName, dataColumn.ColumnName); if (dataType != null) { dataItem.DataType = dataType; } else { dataItem.DataType = OleDbTypeConverter.GetRestrictedOleDbType(dataColumn.DataType); } return(dataItem); }
/// <summary> /// Create Column Binding Data Item /// </summary> /// <param name="dsv"></param> /// <param name="tableName"></param> /// <param name="columnName"></param> /// <param name="dataType"></param> /// <returns></returns> internal static DataItem CREATE_COLUMN_BINDING_DATA_ITEM( DB_SQLHELPER_BASE sqlHelper, DataSourceView dsv, String tableName, String columnName, System.Data.OleDb.OleDbType dataType) { DataItem dataItem = null; DataTable data_table = dsv.Schema.Tables[tableName]; if (data_table == null) { sqlHelper.ADD_MESSAGE_LOG( String.Format("Table {0} is not existed in current DSV", tableName), MESSAGE_TYPE.COLUMN_BINDING_DATA_ITEM, MESSAGE_RESULT_TYPE.Warning); } DataColumn dataColumn = data_table.Columns[columnName]; if (dataColumn == null) { sqlHelper.ADD_MESSAGE_LOG( String.Format("Column {0} is not existed in table {1}", columnName, tableName), MESSAGE_TYPE.COLUMN_BINDING_DATA_ITEM, MESSAGE_RESULT_TYPE.Warning); } dataItem = new DataItem(tableName, dataColumn.ColumnName); if (dataType != null) { dataItem.DataType = dataType; } else { dataItem.DataType = OleDbTypeConverter.GetRestrictedOleDbType(dataColumn.DataType); } return(dataItem); }
/// <summary> /// Add Attribute To Dimension /// </summary> /// <param name="cubeDSV"></param> /// <param name="dim"></param> /// <param name="tableID"></param> /// <param name="colName"></param> /// <param name="attribID"></param> /// <param name="attribName"></param> /// <param name="type"></param> /// <param name="usage"></param> /// <param name="nameColumn"></param> /// <param name="visible"></param> /// <param name="AttHierEnabled"></param> /// <param name="orderby"></param> /// <param name="attDisplayFolder"></param> /// <param name="orderByAttName"></param> /// <param name="attType"></param> /// <param name="valueColumn"></param> /// <param name="valueColtype"></param> internal static void ADD_ATTRIBUTE_TO_DIMENSION( DB_SQLHELPER_BASE sqlHelper, DataSourceView cubeDSV, Dimension dim, String tableID, String colName, String attribID, String attribName, System.Data.OleDb.OleDbType type, AttributeUsage usage, String nameColumn, bool visible = true, bool AttHierEnabled = true, OrderBy orderby = OrderBy.Name, String attDisplayFolder = "", String orderByAttName = null, String attType = "Regular", String valueColumn = null, System.Data.OleDb.OleDbType valueColtype = System.Data.OleDb.OleDbType.Integer) { DimensionAttribute attr = dim.Attributes.FindByName(attribName); if (attr == null) { attr = dim.Attributes.Add(attribID); attr.Name = attribName; attr.Usage = usage; attr.Type = AttributeType.Regular; attr.AttributeHierarchyEnabled = AttHierEnabled; DataItem dataItem = CREATE_COLUMN_BINDING_DATA_ITEM(sqlHelper, cubeDSV, tableID, colName, type); attr.KeyColumns.Add(dataItem); attr.KeyColumns[0].DataType = type; attr.AttributeHierarchyVisible = visible; attr.OrderBy = orderby; if (nameColumn != colName && nameColumn != "") { DataItem nameColDataItem = CREATE_COLUMN_BINDING_DATA_ITEM(sqlHelper, cubeDSV, tableID, nameColumn, System.Data.OleDb.OleDbType.WChar); attr.NameColumn = nameColDataItem; } if (attDisplayFolder != null && attDisplayFolder != "") { attr.AttributeHierarchyDisplayFolder = attDisplayFolder; } if (orderByAttName != null && orderByAttName != "") { attr.OrderByAttributeID = orderByAttName; } if (valueColumn != null && valueColumn != "") { DataItem valueColDataItem = CREATE_COLUMN_BINDING_DATA_ITEM(sqlHelper, cubeDSV, tableID, valueColumn, valueColtype); attr.ValueColumn = valueColDataItem; } sqlHelper.ADD_MESSAGE_LOG( String.Format("Added attribute [{0}] to dimension [{1}]", attribName, dim.Name), MESSAGE_TYPE.ATTRIBUTE, MESSAGE_RESULT_TYPE.Succeed); } }
/// <summary> /// Create molap partition /// </summary> /// <param name="measureGroup"></param> /// <param name="datasourceName"></param> /// <param name="partitionid"></param> /// <param name="MGdsvTableName"></param> /// <param name="filter_string"></param> /// <param name="aggregation_design_id"></param> /// <param name="is_real_time"></param> /// <param name="depended_fact_table"></param> internal static void CREATE_MOLAP_PARTITION( DB_SQLHELPER_BASE sqlHelper, MeasureGroup measureGroup, String datasourceName, String partitionid, String MGdsvTableName, String filter_string, String aggregation_design_id, int is_rolap_mg, String depended_fact_table) { Partition part = measureGroup.Partitions.FindByName(partitionid); if (part != null) { sqlHelper.ADD_MESSAGE_LOG( String.Format("Drop Partition {0}", partitionid), MESSAGE_TYPE.DIMENSION, MESSAGE_RESULT_TYPE.Succeed); part.Drop(DropOptions.AlterOrDeleteDependents); } part = measureGroup.Partitions.Add(partitionid); part.ID = partitionid; part.Name = partitionid; part.Source = new QueryBinding(datasourceName, "SELECT * FROM " + MGdsvTableName + " WHERE 1=1 " + filter_string); if (is_rolap_mg.ToString() == "1") { part.StorageMode = StorageMode.Rolap; part.CurrentStorageMode = StorageMode.Rolap; ProactiveCachingTablesBinding tables_binding = new ProactiveCachingTablesBinding(); tables_binding.NotificationTechnique = NotificationTechnique.Server; TableNotification table_notification = new TableNotification(depended_fact_table, CONFIGURATION_HELPER.GET_METADATA_PROPERTY("db_table_schema_name")); tables_binding.TableNotifications.Add(table_notification); ProactiveCaching proactive_caching = new ProactiveCaching(); proactive_caching.OnlineMode = ProactiveCachingOnlineMode.Immediate; proactive_caching.AggregationStorage = ProactiveCachingAggregationStorage.MolapOnly; proactive_caching.Enabled = true; proactive_caching.Source = tables_binding; System.TimeSpan SilenceInterval_time = new System.TimeSpan(0, 0, 1); proactive_caching.SilenceInterval = SilenceInterval_time.Negate(); proactive_caching.SilenceOverrideInterval = proactive_caching.SilenceInterval; proactive_caching.ForceRebuildInterval = proactive_caching.SilenceInterval; proactive_caching.Latency = System.TimeSpan.Zero; part.ProactiveCaching = proactive_caching; } else { part.StorageMode = StorageMode.Molap; } part.ProcessingMode = ProcessingMode.Regular; if (aggregation_design_id != null) { part.AggregationDesignID = aggregation_design_id.ToString(); } }
/// <summary> /// add measure group into cube /// </summary> /// <param name="cube"> cube (instead of cube db)</param> /// <param name="measureGroupName"></param> /// <param name="measureGroupID"></param> /// <param name="isRealTime"></param> /// <param name="keyNotFoundAction"></param> /// <param name="dropIfExisted"></param> /// <returns>measure group</returns> internal static MeasureGroup ADD_MEASURE_GROUP( DB_SQLHELPER_BASE sqlHelper, Cube cube, String measureGroupName, String measureGroupID, int isRealTime, String keyNotFoundAction, bool dropIfExisted = false) { MeasureGroup measure_group = cube.MeasureGroups.Find(measureGroupID); if (measure_group != null && dropIfExisted) { measure_group.Drop(); } measure_group = cube.MeasureGroups.Add(measureGroupID); measure_group.Name = measureGroupName; if (isRealTime == '0') { measure_group.StorageMode = StorageMode.Molap; } else { measure_group.StorageMode = StorageMode.Rolap; } measure_group.Type = MeasureGroupType.Regular; if (keyNotFoundAction != "0") { ErrorConfiguration error_configuration = new ErrorConfiguration(); switch (keyNotFoundAction.ToLower()) { case "ignoreerror": error_configuration.KeyNotFound = ErrorOption.IgnoreError; break; case "reportandcontinue": error_configuration.KeyNotFound = ErrorOption.ReportAndContinue; break; case "reportandstop": error_configuration.KeyNotFound = ErrorOption.ReportAndStop; break; default: error_configuration.KeyNotFound = ErrorOption.IgnoreError; break; } measure_group.ErrorConfiguration = error_configuration; } sqlHelper.ADD_MESSAGE_LOG( String.Format("Added measure group {0} into cube", measureGroupName), MESSAGE_TYPE.MEASURE_GROUP, MESSAGE_RESULT_TYPE.Succeed); return(measure_group); }
/// <summary> /// Create common cube by configured metadata /// </summary> /// <param name="oledb"></param> /// <param name="sqlHelper"></param> /// <param name="asMeta"></param> /// <param name="cube_server"></param> /// <param name="cube_db_name"></param> /// <param name="cube_name"></param> /// <param name="cubeProcessXMLA"></param> /// <param name="is_rolap"></param> public void CREATE_CUBE_COMMON( DB_SQLHELPER_BASE sqlHelper , IDbConnection oledb , AS_METADATA asMeta , Server cube_server , String cube_db_name , String cube_name , int is_rolap) { try { Database cube_db = cube_server.Databases.FindByName(cube_db_name); DataSourceView cube_dsv = cube_db.DataSourceViews.FindByName("SSAS_DSV"); DataSet dsv_schema = cube_dsv.Schema; // 1.create dsv CREATE_CUBE_DSV(sqlHelper, asMeta, oledb, cube_dsv, dsv_schema.DataSetName); cube_dsv.Update(); // 2.create dimensions CREATE_CUBE_DIMENSION(sqlHelper, asMeta, cube_db, cube_dsv); cube_db.Update(); // 3.cube Cube cube = cube_db.Cubes.FindByName(cube_name); // 4.add dimension into cube ADD_DIMENSION_TO_CUBE(sqlHelper, asMeta, cube_db, cube); // 5.create measure groups CREATE_MEASURE_GROUP(cube_dsv, sqlHelper, asMeta, cube, is_rolap); // 6.remove unnecessary objects REMOVE_CUBE_OBJECTS(sqlHelper, cube_db, cube); // 8.create partitions // 9.create aggregations // 10.cube customized //customized_cube_changes(self,db_connection,cube_server,cube_db,cube,is_rolap); sqlHelper.ADD_MESSAGE_LOG( "[Create cube common] Saving cube changes...", MESSAGE_TYPE.CREATE_CUBE, MESSAGE_RESULT_TYPE.Normal); //11.cube save cube_db.Update(UpdateOptions.ExpandFull); sqlHelper.ADD_MESSAGE_LOG( "[Create cube common] Saved cube changes, processing cube...", MESSAGE_TYPE.CREATE_CUBE, MESSAGE_RESULT_TYPE.Normal); CUBE_PROCESS_FULL(sqlHelper, cube_server, cube_db_name); } catch (Exception ex) { sqlHelper.ADD_MESSAGE_LOG("[Create cube common] " + ex.Message.ToString(), MESSAGE_TYPE.CREATE_CUBE, MESSAGE_RESULT_TYPE.Error); throw(ex); } finally { //oledb.close_connection(); } }
public DataTable GET_SSAS_DSV_SET(DB_SQLHELPER_BASE sqlHelper) { String QueryString = @" SELECT DISTINCT dsv_schema_name, db_table_name, table_type, is_named_query FROM [dbo].[ssas_dsv] as tb with(nolock) "; return(sqlHelper.EXECUTE_SQL_QUERY_RETURN_TABLE(sqlHelper, QueryString)); }
public DataTable GET_SSAS_DIMENSION_SET(DB_SQLHELPER_BASE sqlHelper) { String QueryString = @" SELECT DISTINCT dimension_id, dimension_name, dsv_schema_name, dimension_type FROM dbo.[ssas_dimension] AS tb WITH(nolock) "; return(sqlHelper.EXECUTE_SQL_QUERY_RETURN_TABLE(sqlHelper, QueryString)); }
/// <summary> /// Remove measure group /// </summary> /// <param name="cube"></param> /// <param name="mgID"></param> internal static void REMOVE_MEASURE_GROUPS(DB_SQLHELPER_BASE sqlHelper, Cube cube, String mgID) { MeasureGroup mg = cube.MeasureGroups.Find(mgID); if (mg != null) { cube.MeasureGroups.Remove(mg); sqlHelper.ADD_MESSAGE_LOG( String.Format("Deleted [{0}] measure group", mgID), MESSAGE_TYPE.MEASURE_GROUP, MESSAGE_RESULT_TYPE.Succeed); } }
public DataTable GET_SSAS_ATTRIBUTE_RELATION_SHIPS_SET(DB_SQLHELPER_BASE sqlHelper, String dimension_id = null) { String QueryString = String.Format(@" SELECT DISTINCT dimension_id, based_attribute_id, related_attribute_id, relationship_type FROM ssas_attributes_relationship AS rs WITH(nolock) WHERE is_enabled = 1 AND dimension_id='{0}'", dimension_id); return(sqlHelper.EXECUTE_SQL_QUERY_RETURN_TABLE(sqlHelper, QueryString)); }
/// <summary> /// Update Cube Command /// </summary> /// <param name="cube_server"></param> /// <param name="cube_db"></param> /// <param name="cube"></param> /// <param name="mdx_code"></param> public void UPDATE_CUBE_COMMAND(DB_SQLHELPER_BASE sqlHelper, Server cube_server, Database cube_db, Cube cube, String mdx_code) { try { MdxScript mdx = new MdxScript(); if (cube.DefaultMdxScript == null) { sqlHelper.ADD_MESSAGE_LOG( "DefaultMdxScript is none, creating a new one", MESSAGE_TYPE.MDX, MESSAGE_RESULT_TYPE.Normal); mdx.ID = cube.MdxScripts.GetNewID(); mdx.Name = "MDXHelper"; } else { mdx = cube.DefaultMdxScript; } Command cmd = new Command(); if (cube.DefaultMdxScript == null || cube.DefaultMdxScript.Commands == null) { sqlHelper.ADD_MESSAGE_LOG( "DefaultMdxScript.Commands is none, creating a new one", MESSAGE_TYPE.MDX, MESSAGE_RESULT_TYPE.Normal); } else { cmd = cube.DefaultMdxScript.Commands[0]; } cmd.Text = mdx_code; mdx.Commands.Remove(cmd); mdx.Commands.Add(cmd); cube.MdxScripts.Remove(mdx); cube.MdxScripts.Add(mdx); sqlHelper.ADD_MESSAGE_LOG( "Refreshed cube mdx calculations.", MESSAGE_TYPE.MDX, MESSAGE_RESULT_TYPE.Normal); } catch (Exception ex) { sqlHelper.ADD_MESSAGE_LOG( "Failed to refreshed cube mdx calculations:" + ex.Message.ToString(), MESSAGE_TYPE.MDX, MESSAGE_RESULT_TYPE.Error); throw ex; } }
public DataTable GET_SSAS_MEASURE_GROUPS_SET(DB_SQLHELPER_BASE sqlHelper, int isRolap) { String isRolapFilter = (isRolap == 0) ? " AND mg.is_real_time<>1" : ""; String QueryString = String.Format(@" SELECT DISTINCT order_query.order_index AS order_index, mg.measure_group_id AS measure_group_id, mg.dsv_schema_name AS dsv_schema_name, mg.measure_group_name AS measure_group_name, Isnull(mg.key_not_found_action, '0') AS key_not_found_action, mg.is_real_time AS is_real_time, dsv.depended_fact_table AS depended_fact_table, cast(Isnull(mg.is_rolap_mg, Isnull(mg.is_real_time, 0)) as int) AS is_rolap_mg FROM ssas_measures AS mea WITH(nolock) INNER JOIN ssas_measures_mapping AS mapp WITH(nolock) ON mea.measure_id = mapp.measure_id INNER JOIN ssas_measure_group AS mg WITH(nolock) ON mg.measure_group_id = mapp.measure_group_id INNER JOIN ssas_measures_description AS descr WITH(nolock) ON descr.measure_id = mea.measure_id LEFT JOIN ssas_etl_module AS module WITH(nolock) ON module.module_name = Replace(mg.dsv_schema_name, 'olap_', '') AND module.is_enabled = 1 INNER JOIN ssas_dsv AS dsv WITH(nolock) ON mg.dsv_schema_name = dsv.dsv_schema_name LEFT JOIN (SELECT Min(order_index) AS order_index, v1.measure_group_id FROM (SELECT DISTINCT 1 AS order_index, v2.measure_group_id FROM ssas_dim_usage v1 INNER JOIN ssas_dim_usage v2 ON v1.internal_measure_group_id = v2.measure_group_id UNION ALL SELECT Max(CASE WHEN Lower(dim_usage_type) = 'regular' THEN 2 WHEN Lower(dim_usage_type) = 'reference' THEN 3 WHEN Lower(dim_usage_type) = 'manytomany' THEN 4 ELSE 4 END) AS order_index, measure_group_id FROM [dbo].ssas_dim_usage GROUP BY measure_group_id) v1 GROUP BY v1.measure_group_id)order_query ON order_query.measure_group_id = mg.measure_group_id WHERE 1 = 1 {0} ORDER BY order_query.order_index, mg.is_real_time DESC, mg.measure_group_name DESC ", isRolapFilter); return(sqlHelper.EXECUTE_SQL_QUERY_RETURN_TABLE(sqlHelper, QueryString)); }
public DataTable GET_SSAS_PARTITION_SET(DB_SQLHELPER_BASE sqlHelper, String measure_group_id = null) { String QueryString = String.Format(@" SELECT DISTINCT mg.dsv_schema_name AS dsv_schema_name, Isnull(usage.fact_fk_dim_column_name_customized, usage.fact_fk_dim_column_name) AS fact_fk_dim_column_name, mg.measure_group_id AS measure_group_id FROM ssas_dim_usage AS usage WITH(nolock) INNER JOIN ssas_measure_group AS mg WITH(nolock) ON usage.measure_group_id = mg.measure_group_id WHERE Lower(usage.dimension_id) = 'olap_dim_date' AND mg.measure_group_id ='{0}'", measure_group_id); return(sqlHelper.EXECUTE_SQL_QUERY_RETURN_TABLE(sqlHelper, QueryString));; }
/// <summary> /// Remove cube objects (FactBool) /// </summary> /// <param name="cubedb"></param> /// <param name="cube"></param> public void REMOVE_CUBE_OBJECTS(DB_SQLHELPER_BASE sqlHelper, Database cubedb, Cube cube) { try { AS_API.REMOVE_MEASURE_GROUPS(sqlHelper, cube, "FactBool"); } catch (Exception ex) { sqlHelper.ADD_MESSAGE_LOG( "Failed to refreshed cube mdx calculations:" + ex.Message.ToString(), MESSAGE_TYPE.REMOVE_CUBE_OBJECT, MESSAGE_RESULT_TYPE.Error); throw ex; } }
public DataTable GET_SSAS_HIERARCHIES_SET(DB_SQLHELPER_BASE sqlHelper, String dimension_id = null) { String QueryString = String.Format(@" SELECT DISTINCT dimension_id, hierarchy_name, level_name, level_id, source_attribute_id, hierarchies_id FROM ssas_attributes_hierarchies AS hie WITH(nolock) WHERE is_enabled = 1 AND dimension_id = '{0}' ORDER BY hierarchy_name, level_id ", dimension_id); return(sqlHelper.EXECUTE_SQL_QUERY_RETURN_TABLE(sqlHelper, QueryString)); }
public DataTable GET_SSAS_AGGREGATION_DESIGN_SET(DB_SQLHELPER_BASE sqlHelper, String measure_group_id = null) { String QueryString = String.Format(@" SELECT DISTINCT mg.measure_group_id, mg.aggregation_design_name, agg_d_att.aggregation_name, agg_d_att.dimension_id, agg_d_att.attribute_id, agg_d.description FROM ssas_measure_group AS mg WITH(nolock) INNER JOIN ssas_aggregation_design AS agg_d WITH(nolock) ON mg.aggregation_design_name = agg_d.aggregation_design_name INNER JOIN ssas_aggregation_design_attribute AS agg_d_att WITH(nolock) ON agg_d.aggregation_name = agg_d_att.aggregation_name WHERE mg.measure_group_id='{0}'", measure_group_id); return(sqlHelper.EXECUTE_SQL_QUERY_RETURN_TABLE(sqlHelper, QueryString)); }
/// <summary> /// Create aggregation design /// </summary> /// <param name="mg"></param> /// <param name="sqlHelper"></param> /// <param name="asMeta"></param> /// <returns></returns> public AggregationDesign CREATE_AGGREGATION_DESIGN(MeasureGroup mg, DB_SQLHELPER_BASE sqlHelper , AS_METADATA asMeta) { DataTable agg_design_list = null; AggregationDesign agg_design = null; try { agg_design_list = asMeta.GET_SSAS_AGGREGATION_DESIGN_SET(sqlHelper, mg.ID); foreach (DataRow measure in agg_design_list.Rows) { String AggregationDesignName = measure["aggregation_design_name"].ToString(); //agg_design=AggregationDesignName; String AggregationName = measure["aggregation_name"].ToString(); String DimensionID = measure["dimension_id"].ToString(); String AttributeID = measure["attribute_id"].ToString(); if (mg.AggregationDesigns.Find(AggregationDesignName) == null) { mg.AggregationDesigns.Add(AggregationDesignName); } agg_design = mg.AggregationDesigns[AggregationDesignName]; Aggregation agg = agg_design.Aggregations.Find(AggregationName); if (agg == null) { agg = agg_design.Aggregations.Add(AggregationName, AggregationName); } AggregationDimension agg_dim = agg.Dimensions.Find(DimensionID); if (agg_dim == null) { agg.Dimensions.Add(DimensionID); } agg.Dimensions[DimensionID].Attributes.Add(AttributeID); } } catch (Exception ex) { sqlHelper.ADD_MESSAGE_LOG(ex.Message.ToString(), MESSAGE_TYPE.AGGREGATION_DESIGN, MESSAGE_RESULT_TYPE.Error); throw (ex); } return(agg_design); }
/// <summary> /// Create ssas base cube by calling SSAS API /// </summary> /// <param name="cubeServer">Cube server</param> /// <param name="cubeDBName">Cube data base name</param> /// <param name="cubeXmla">Base cube xmla</param> public void CREATE_SSAS_BASE_CUBE( DB_SQLHELPER_BASE sqlHelper, Server cubeServer, String cubeDBName, String cubeName, String dwConnectionString ) { String SSASConfigurationPath = CONFIGURATION_HELPER.BASIC_CONFIGURATION_FOLDER + @"\SSASConfiguration\BaseCubeXMLA.xml"; String SSASConfiguration = ""; System.Security.Principal.NTAccount _Everyone_Account = new System.Security.Principal.NTAccount("Everyone"); System.Security.Principal.SecurityIdentifier _SecurityIdentifier = (System.Security.Principal.SecurityIdentifier)_Everyone_Account.Translate(typeof(System.Security.Principal.SecurityIdentifier)); String sidString = _SecurityIdentifier.ToString(); SSASConfiguration = System.IO.File.ReadAllText(SSASConfigurationPath); SSASConfiguration = SSASConfiguration .Replace("$(dwConnectionString)", dwConnectionString) .Replace("$(cubeDBName)", cubeDBName) .Replace("$(cubeName)", cubeName) .Replace("$(DBTableSchemaName)", CONFIGURATION_HELPER.GET_METADATA_PROPERTY("db_table_schema_name")) .Replace("$(sid)", sidString); sqlHelper.ADD_MESSAGE_LOG(String.Format("[Create base cube] Starting create cube database {0}", cubeDBName), MESSAGE_TYPE.CREATE_CUBE, MESSAGE_RESULT_TYPE.Normal); XmlaResultCollection resultCol = cubeServer.Execute(SSASConfiguration); foreach (XmlaResult result in resultCol) { foreach (XmlaMessage message in result.Messages) { if (message.ToString().Contains("error") || message.ToString().Contains("failed")) { sqlHelper.ADD_MESSAGE_LOG("[Create base cube]" + message.ToString(), MESSAGE_TYPE.CREATE_CUBE, MESSAGE_RESULT_TYPE.Error); return; } else { sqlHelper.ADD_MESSAGE_LOG("[Create base cube]" + message.ToString(), MESSAGE_TYPE.CREATE_CUBE, MESSAGE_RESULT_TYPE.Succeed); } } } sqlHelper.ADD_MESSAGE_LOG("[Create base cube]" + String.Format("Succeed to create cube database {0}", cubeDBName), MESSAGE_TYPE.CREATE_CUBE, MESSAGE_RESULT_TYPE.Succeed); }
/// <summary> /// Create cube core measure /// </summary> /// <param name="sqlHelper"></param> /// <param name="asMeta"></param> /// <param name="cube"></param> public void CREATE_CUBE_CORE_MEASURES(DB_SQLHELPER_BASE sqlHelper , AS_METADATA asMeta , Cube cube) { try { DataTable coreMeasureSet = asMeta.GET_SSAS_CORE_MEASURES_SET(sqlHelper); foreach (DataRow row in coreMeasureSet.Rows) { String measureGroupID = row["measure_group_id"].ToString(); String MeasureId = row["measure_id"].ToString(); String MeasureName = row["measure_name"].ToString(); String DSVSchemaName = row["dsv_schema_name"].ToString(); String DisplayFolder = row["display_folder"].ToString(); String FormatString = row["format_string"].ToString(); String MeasureDataType = row["measure_data_type"].ToString(); String DBColumn = row["db_column"].ToString(); String AggregationFunction = row["aggregation_function"].ToString(); MeasureGroup measureGroup = cube.MeasureGroups.Find(measureGroupID); AS_API.ADD_MEASURE_TO_MEASURE_GROUP( sqlHelper , measureGroup , DSVSchemaName , DBColumn , MeasureName , MeasureId , DisplayFolder , FormatString , AggregationFunction , true , MeasureDataType , MeasureDataType); measureGroup.Update(); } } catch (Exception ex) { sqlHelper.ADD_MESSAGE_LOG("Failed to create cube core measures:" + ex.Message.ToString(), MESSAGE_TYPE.MEASURES, MESSAGE_RESULT_TYPE.Error); throw (ex); } }
public DataTable GET_SSAS_ATTRIBUTES_SET(DB_SQLHELPER_BASE sqlHelper, String dimension_id = null) { String QueryString = String.Format(@" SELECT DISTINCT attr.attribute_id AS attribute_id, Isnull(attr.attribute_name_customized, attr.attribute_name) AS attribbute_name, dim.dimension_id AS dimension_id, dim.dsv_schema_name AS dsv_schema_name, Isnull(attr.key_column_db_column_customized, attr.key_column_db_column) AS key_column_db_column, Isnull(attr.key_column_oledb_type_customized, attr.key_column_oledb_type) AS key_column_oledb_type, attr.attribute_usage AS attribute_usage, Isnull(attr.name_column_customized, attr.name_column) AS name_column, attr.visible AS visible, attr.atthier_enabled AS atthier_enabled, Isnull(attr.order_by_customized, attr.order_by) AS order_by FROM [dbo].[ssas_attributes] attr WITH(nolock) INNER JOIN [dbo].[ssas_dimension] AS dim WITH(nolock) ON attr.[dimension_id] = dim.[dimension_id] WHERE dim.[dimension_id]= '{0}'", dimension_id); return(sqlHelper.EXECUTE_SQL_QUERY_RETURN_TABLE(sqlHelper, QueryString)); }