/// <summary>
/// Populate ssas cube data source view defination
/// </summary>
/// <param name="oledb">oledb connection obj</param>
/// <param name="dsv">ssas cube data source view obj</param>
/// <param name="dsvSchemaName">dsv schema name: eg.. OLAP_FACT_SALES</param>
/// <param name="dsvQueryText">query text of dsv</param>
/// <param name="dbTableName">depended table name</param>
/// <param name="tableType"></param>
/// <param name="db_SchemaName"></param>
/// <param name="isNamedQuery"></param>
public static void ADD_TABLE_TO_CUBE_DSV(System.Data.OleDb.OleDbConnection oledb,
Microsoft.AnalysisServices.DataSourceView dsv,
String dsvSchemaName,
String dsvQueryText,
String dbTableName,
String tableType = "View",
String db_SchemaName = "dbo",
bool isNamedQuery = false)
{
System.Data.DataSet schema = dsv.Schema;
System.Data.OleDb.OleDbCommand select_cmd = new System.Data.OleDb.OleDbCommand();
select_cmd.CommandText = dsvQueryText;
select_cmd.Connection = oledb;
System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter();
adapter.SelectCommand = select_cmd;
DataTable[] data_tables = adapter.FillSchema(schema, SchemaType.Mapped, dsvSchemaName);
DataTable data_table = data_tables[0];
data_table.ExtendedProperties.Remove("TableType");
data_table.ExtendedProperties.Add("TableType", tableType);
if (isNamedQuery)
{
data_table.ExtendedProperties.Remove("QueryDefinition");
data_table.ExtendedProperties.Add("QueryDefinition", dsvQueryText);
}
else
{
data_table.ExtendedProperties.Remove("DbSchemaName");
data_table.ExtendedProperties.Add("DbSchemaName", db_SchemaName);
}
data_table.ExtendedProperties.Remove("DbTableName");
data_table.ExtendedProperties.Add("DbTableName", dbTableName);
data_table.ExtendedProperties.Remove("FriendlyName");
data_table.ExtendedProperties.Add("FriendlyName", dbTableName);
}
/// <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>
public static Microsoft.AnalysisServices.DataItem CREATE_COLUMN_BINDING_DATA_ITEM(
Microsoft.AnalysisServices.DataSourceView dsv,
String tableName,
String columnName,
System.Data.OleDb.OleDbType dataType)
{
Microsoft.AnalysisServices.DataItem dataItem = null;
DataTable data_table = dsv.Schema.Tables[tableName];
if (data_table == null)
{
//module_helper.helper.print_message_to_client( "Table "+tableName+" is not existed in current DSV","warning");
}
DataColumn dataColumn = data_table.Columns[columnName];
if (dataColumn == null)
{
//module_helper.helper.print_message_to_client( "Column "+columnName+" is not existed in table "+tableName,"warning");
}
dataItem = new Microsoft.AnalysisServices.DataItem(tableName, dataColumn.ColumnName);
if (dataType != null)
{
dataItem.DataType = dataType;
}
else
{
dataItem.DataType = Microsoft.AnalysisServices.OleDbTypeConverter.GetRestrictedOleDbType(dataColumn.DataType);
}
return(dataItem);
}
public static void ADD_ATTRIBUTE_TO_DIMENSION(
Microsoft.AnalysisServices.DataSourceView cubeDSV,
Microsoft.AnalysisServices.Dimension dim,
String tableID,
String colName,
String attribID,
String attribName,
System.Data.OleDb.OleDbType type,
Microsoft.AnalysisServices.AttributeUsage usage,
String nameColumn,
bool visible = true,
bool AttHierEnabled = true,
Microsoft.AnalysisServices.OrderBy orderby = Microsoft.AnalysisServices.OrderBy.Name,
String attDisplayFolder = "",
String orderByAttName = null,
String attType = "Regular",
String valueColumn = null,
System.Data.OleDb.OleDbType valueColtype = System.Data.OleDb.OleDbType.Integer)
{
Microsoft.AnalysisServices.DimensionAttribute attr = dim.Attributes.FindByName(attribName);
if (attr == null)
{
attr = dim.Attributes.Add(attribID);
attr.Name = attribName;
attr.Usage = usage;
attr.Type = Microsoft.AnalysisServices.AttributeType.Regular;
attr.AttributeHierarchyEnabled = AttHierEnabled;
Microsoft.AnalysisServices.DataItem dataItem = CREATE_COLUMN_BINDING_DATA_ITEM(cubeDSV, tableID, colName, type);
attr.KeyColumns.Add(dataItem);
attr.KeyColumns[0].DataType = type;
attr.AttributeHierarchyVisible = visible;
attr.OrderBy = orderby;
if (nameColumn != colName && nameColumn != "")
{
Microsoft.AnalysisServices.DataItem nameColDataItem = CREATE_COLUMN_BINDING_DATA_ITEM(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 != "")
{
Microsoft.AnalysisServices.DataItem valueColDataItem = CREATE_COLUMN_BINDING_DATA_ITEM(cubeDSV, tableID, valueColumn, valueColtype);
attr.ValueColumn = valueColDataItem;
}
//module_helper.helper.print_message_to_client( "Added attribute ["+attribName+"] to dimension ["+dim.Name+"]")
}
}