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));
}
