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);
}
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>
/// 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 Create Cube DSV
/// </summary>
/// <param name="sqlHelper"></param>
/// <param name="asMeta"></param>
/// <param name="oleDB"></param>
/// <param name="DSV"></param>
/// <param name="dsvSchema"></param>
public void CREATE_CUBE_DSV(DB_SQLHELPER_BASE sqlHelper
, AS_METADATA asMeta
, IDbConnection oleDB
, Microsoft.AnalysisServices.DataSourceView DSV
, String dsvSchema)
{
try
{
sqlHelper.ADD_MESSAGE_LOG("[Create cube dsv] Starting add cube dsv", MESSAGE_TYPE.DSV, MESSAGE_RESULT_TYPE.Normal);
DataTable dsvSet = asMeta.GET_SSAS_DSV_SET(sqlHelper);
foreach (DataRow row in dsvSet.Rows)
{
String dsv_schema_name = row["dsv_schema_name"].ToString();
String db_table_name = row["db_table_name"].ToString();
String table_type = row["table_type"].ToString();
String is_name_query = row["is_named_query"].ToString();
String dsv_query_text = String.Format("SELECT * FROM {0} WHERE 1=0", db_table_name);
sqlHelper.ADD_MESSAGE_LOG(String.Format("[Create cube dsv->{0}] Adding to add cube dsv", db_table_name), MESSAGE_TYPE.DSV, MESSAGE_RESULT_TYPE.Succeed);
AS_API.ADD_TABLE_TO_CUBE_DSV(oleDB
, DSV
, db_table_name
, dsv_query_text
, db_table_name
, "View"
, CONFIGURATION_HELPER.GET_METADATA_PROPERTY("db_table_schema_name"));
}
DSV.Update();
sqlHelper.ADD_MESSAGE_LOG("[Create cube dsv] Succeed to add cube dsv", MESSAGE_TYPE.DSV, MESSAGE_RESULT_TYPE.Succeed);
}
catch (Exception ex)
{
sqlHelper.ADD_MESSAGE_LOG("[Create cube dsv] " + ex.Message.ToString(), MESSAGE_TYPE.DSV, MESSAGE_RESULT_TYPE.Error);
throw(ex);
}
}
//-----------------------------
//---------Query---------------
//-----------------------------
public int EXECUTE_SQL_QUERY(DB_SQLHELPER_BASE sqlHelper, String SqlString)
{
int return_value = 0;
try
{
IDbCommand iCom = sqlHelper.GET_DATABASE_CONNECTION().CreateCommand();
iCom.CommandText = SqlString;
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>
/// CREATE_CUBE_PARTITION_FOR_MEASURE_GROUP
/// </summary>
/// <param name="sqlHelper"></param>
/// <param name="asMeta"></param>
/// <param name="cube"></param>
/// <param name="is_rolap_cube"></param>
/// <param name="mg"></param>
/// <param name="aggregation_design"></param>
/// <param name="is_rolap_mg"></param>
/// <param name="depended_fact_table"></param>
/// <param name="DSVSchemaName"></param>
public void CREATE_CUBE_PARTITION_FOR_MEASURE_GROUP(
DB_SQLHELPER_BASE sqlHelper
, AS_METADATA asMeta
, Cube cube
, int is_rolap_cube
, MeasureGroup mg
, AggregationDesign aggregation_design
, int is_rolap_mg
, String depended_fact_table
, String DSVSchemaName)
{
DataTable partition_date_filter = asMeta.GET_SSAS_PARTITION_SET(sqlHelper, mg.ID);
String aggregation_design_id = null;
if (aggregation_design != null)
{
aggregation_design_id = aggregation_design.ID.ToString();
}
if (partition_date_filter != null && partition_date_filter.Rows != null && partition_date_filter.Rows.Count > 0)
{
String factFKDimColumnName = partition_date_filter.Rows[0]["fact_fk_dim_column_name"].ToString();
int month_volumn_per_partition = Convert.ToInt32(CONFIGURATION_HELPER.GET_METADATA_PROPERTY("month_volumn_per_partition"));
int year_volumn_per_cube = Convert.ToInt32(CONFIGURATION_HELPER.GET_METADATA_PROPERTY("year_volumn_per_cube"));
int partitionCount = 0;
partitionCount = year_volumn_per_cube * 12 / month_volumn_per_partition;
for (int i = 1; i <= partitionCount + 1; i++)
{
String partitionSelectQuery = "";// String.Format("SELECT * FROM {0} WHERE 1=1 ", DSVSchemaName);
//if rolap cube, and current mg is molap, then add where 1=2 filter, select * from tb_name where 1=1 and 1=2
if (is_rolap_cube == 1 && is_rolap_mg == 0)
{
partitionSelectQuery = partitionSelectQuery + " AND 1=2";
}
//if rolap cube, then no need additional date column filter, if molap , then need date column filter,
//select * from tb_name where 1=1 and dateid>=20100101 and dateid<20100201
if (is_rolap_cube == 0)
{
partitionSelectQuery = partitionSelectQuery + " " + asMeta.GET_SSAS_PARTITION_FILTER(factFKDimColumnName, i, month_volumn_per_partition);
}
AS_API.CREATE_MOLAP_PARTITION(
sqlHelper,
mg,
"DW_DataSource",
mg.ID.ToString() + "_" + i.ToString(),
DSVSchemaName,
partitionSelectQuery,
aggregation_design_id,
is_rolap_mg,
depended_fact_table
);
//if rolap cube, then only need one partition
if (is_rolap_cube == 1)
{
break;
}
}
}
else
{
sqlHelper.ADD_MESSAGE_LOG(String.Format("Create cube partition-> No partition date column been detected for mg {0}", mg.ID), MESSAGE_TYPE.PARTITION, MESSAGE_RESULT_TYPE.Warning);
AS_API.CREATE_MOLAP_PARTITION(
sqlHelper,
mg,
"DW_DataSource",
mg.ID.ToString() + "_1",
DSVSchemaName,
"",
aggregation_design_id,
is_rolap_mg,
depended_fact_table
);
}
}
/// <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();
}
}
