private DataItem CreateDataItem(DataSourceView parDsv, string parTableName, string parColumnName)
{
DataTable dataTable = ((DataSourceView)parDsv).Schema.Tables[parTableName];
DataColumn dataColumn = dataTable.Columns[parColumnName];
return(new DataItem(parTableName, parColumnName, OleDbTypeConverter.GetRestrictedOleDbType(dataColumn.DataType)));
}
public override void Exec()
{
try
{
UIHierarchy solExplorer = this.ApplicationObject.ToolWindows.SolutionExplorer;
UIHierarchyItem hierItem = (UIHierarchyItem)((System.Array)solExplorer.SelectedItems).GetValue(0);
ProjectItem projItem = (ProjectItem)hierItem.Object;
DataSourceView dsv = (DataSourceView)projItem.Object;
IterateDsvColumns(dsv);
ReportViewerForm frm = new ReportViewerForm();
frm.ReportBindingSource.DataSource = usedColumns;
frm.Report = "SSAS.UsedColumns.rdlc";
Microsoft.Reporting.WinForms.ReportDataSource reportDataSource1 = new Microsoft.Reporting.WinForms.ReportDataSource();
reportDataSource1.Name = "BIDSHelper_UsedColumn";
reportDataSource1.Value = frm.ReportBindingSource;
frm.ReportViewerControl.LocalReport.DataSources.Add(reportDataSource1);
frm.ReportViewerControl.LocalReport.ReportEmbeddedResource = "SSAS.UsedColumns.rdlc";
frm.Caption = "Used Columns Report";
frm.WindowState = FormWindowState.Maximized;
frm.Show();
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void SaveAction(object obj)
{
var results = DataSourceView.Cast <Mahasiswa>();
Model = results.ToList();
WindowClose();
}
protected override void OnDataBound(EventArgs e)
{
base.OnDataBound(e);
if (!this.DesignMode)
{
ViewState["TotalRowCount"] = 0;
if (this.PageCount > 1)
{
if (this.SelectArguments.TotalRowCount > -1)
{
ViewState["TotalRowCount"] = this.SelectArguments.TotalRowCount;
}
}
else
{
ViewState["TotalRowCount"] = this.Rows.Count;
}
if (this.TotalRowCount < 1)
{
IDataSource IDS = this.GetDataSource();
DataSourceView dsv = IDS.GetView(this.DataMember);
dsv.Select(DataSourceSelectArguments.Empty, this.SetRowCount);
}
}
}
/// <summary>
/// 创建关系
/// </summary>
/// <param name="parDsv">数据源视图</param>
/// <param name="parFkTableName">外键表名</param>
/// <param name="parFkColumnName">外键列名</param>
/// <param name="parPkTableName">主键表名</param>
/// <param name="parPkColumnName">主键列名</param>
public void AddRelation(DataSourceView parDsv, string parFkTableName, string parFkColumnName, string parPkTableName, string parPkColumnName)
{
DataColumn fkColumn = parDsv.Schema.Tables[parFkTableName].Columns[parFkColumnName];
DataColumn pkColumn = parDsv.Schema.Tables[parPkTableName].Columns[parPkColumnName];
parDsv.Schema.Relations.Add("FK_" + parFkTableName + "_" + parPkColumnName, pkColumn, fkColumn);
}
/// <summary>
/// 创建数据源视图
/// </summary>
/// <param name="parDatabase">数据库</param>
/// <param name="parDataSourceName">数据源名</param>
/// <param name="parDataSourceViewName">数据源视图名</param>
public void CreateDataSourceView(Database parDatabase, string parDataSourceName, string parDataSourceViewName)
{
DataSourceView dsv = parDatabase.DataSourceViews.Add(parDataSourceViewName);
dsv.DataSourceID = parDataSourceName;
dsv.Schema = new DataSet();
//Open a connection to the data source
OleDbConnection connection = new OleDbConnection(dsv.DataSource.ConnectionString);
connection.Open();
//增加 DimGeography表
AddTable(dsv, connection, "DimGeography");
//AddComputedColumn(dsv, connection, "DimCustomer", "FullName", "CASE WHEN MiddleName IS NULL THEN FirstName + ' ' + LastName ELSE FirstName + ' ' + MiddleName + ' ' + LastName END");
//AddComputedColumn(dsv, connection, "DimCustomer", "GenderDesc", "CASE WHEN Gender = 'M' THEN 'Male' ELSE 'Female' END");
//AddComputedColumn(dsv, connection, "DimCustomer", "MaritalStatusDesc", "CASE WHEN MaritalStatus = 'S' THEN 'Single' ELSE 'Married' END");
//增加 DimCustomer表
AddTable(dsv, connection, "DimCustomer");
AddRelation(dsv, "DimCustomer", "GeographyKey", "DimGeography", "GeographyKey");
//增加 FactInternetSales表
AddTable(dsv, connection, "FactInternetSales");
AddRelation(dsv, "FactInternetSales", "CustomerKey", "DimCustomer", "CustomerKey");
dsv.Update();
}
protected override void OnDataBinding(EventArgs e)
{
base.OnDataBinding(e);
DataSourceView view = GetData();
// view could be null when a user implements his own GetData().
if (null == view)
{
throw new InvalidOperationException(SR.GetString(SR.DataControl_ViewNotFound, ID));
}
// DevDiv 1036362: enable async model binding for ListControl
bool useAsyncSelect = false;
if (AppSettings.EnableAsyncModelBinding)
{
var modelDataView = view as ModelDataSourceView;
useAsyncSelect = modelDataView != null && modelDataView.IsSelectMethodAsync;
}
if (useAsyncSelect)
{
_asyncSelectPending = true; // disable post data binding action until the callback is invoked
view.Select(SelectArguments, OnDataSourceViewSelectCallback);
}
else
{
IEnumerable data = view.ExecuteSelect(DataSourceSelectArguments.Empty);
PerformDataBinding(data);
}
}
/*
* Fact Col1 - Granularity Attribute
* Col2 - Source Table name
*/
private static void getFactMeasureGroupAttributeUsage(List <DimensionUsage> dimUsage, MeasureGroup mg, DegenerateMeasureGroupDimension factMGdim)
{
DimensionUsage usage = null;
usage = new DimensionUsage("Fact", mg, factMGdim.CubeDimension, factMGdim.Dimension);
usage.Column1Name = "Granularity Attribute";
usage.Column2Name = "Source Table";
foreach (MeasureGroupAttribute mga in factMGdim.Attributes)
{
//mga.
if (mga.Type == MeasureGroupAttributeType.Granularity)
{
usage.Column1Value = mga.Attribute.Name;
foreach (DataItem di in mga.KeyColumns)
{
if (di.Source is ColumnBinding)
{
usage.Column2Value = ((ColumnBinding)di.Source).TableID;
DataSourceView dsv = mga.ParentCube.DataSourceView;
if (dsv.Schema.Tables.Contains(usage.Column2Value))
{
DataTable oTable = dsv.Schema.Tables[dsv.Schema.Tables.IndexOf(usage.Column2Value)];
if (oTable.ExtendedProperties.ContainsKey("FriendlyName"))
{
usage.Column2Value = oTable.ExtendedProperties["FriendlyName"].ToString();
}
}
}
}
}
}
dimUsage.Add(usage);
}
protected virtual IEnumerable GetData()
{
if (DataSource != null && IsBoundUsingDataSourceID)
{
throw new HttpException("Control bound using both DataSourceID and DataSource properties.");
}
if (DataSource != null)
{
return(DataSourceResolver.ResolveDataSource(DataSource, DataMember));
}
if (!IsBoundUsingDataSourceID)
{
return(null);
}
IEnumerable result = null;
DataSourceView boundDataSourceView = ConnectToDataSource();
boundDataSourceView.Select(DataSourceSelectArguments.Empty, delegate(IEnumerable data) { result = data; });
return(result);
}
static void AddRelation(DataSourceView dsv, String fkTableName, String fkColumnName, String pkTableName, String pkColumnName)
{
DataColumn fkColumn = dsv.Schema.Tables[fkTableName].Columns[fkColumnName];
DataColumn pkColumn = dsv.Schema.Tables[pkTableName].Columns[pkColumnName];
dsv.Schema.Relations.Add("FK_" + fkTableName + "_" + fkColumnName, pkColumn, fkColumn, true);
}
/// <summary>
/// Creating the Dimension, Attribute, Hierarchy, and MemberProperty Objects.
/// </summary>
/// <param name="objDatabase">DB instance.</param>
/// <param name="objDataSourceView">DataSource instance.</param>
/// <param name="strTableNamesAndKeys">Array of Table names and keys.</param>
/// <param name="intDimensionTableCount">Dimension table count.</param>
/// <returns>Dimension Array.</returns>
private static object[] CreateDimension(Database objDatabase, DataSourceView objDataSourceView, string[,] strTableNamesAndKeys, int intDimensionTableCount)
{
try
{
Console.WriteLine("Creating the Dimension, Attribute, Hierarchy, and MemberProperty Objects ...");
Dimension[] objDimensions = new Dimension[intDimensionTableCount];
for (int i = 0; i < intDimensionTableCount; i++)
{
objDimensions[i] = (Dimension)GenerateDimension(objDatabase, objDataSourceView, strTableNamesAndKeys[i, 0], strTableNamesAndKeys[i, 1]);
}
////Add Hierarchy and Level
//Hierarchy objHierarchy = objDimension.Hierarchies.Add("ProductByCategory");
//objHierarchy.Levels.Add("Category").SourceAttributeID = objCatKeyAttribute.ID;
//objHierarchy.Levels.Add("Product").SourceAttributeID = objProdKeyAttribute.ID;
////Add Member Property
////objProdKeyAttribute.AttributeRelationships.Add(objProdDescAttribute.ID);
//objDimension.Update();
return(objDimensions);
}
catch (Exception ex)
{
Console.WriteLine("Error in Creating the Dimension, Attribute, Hierarchy, and MemberProperty Objects. Error Message -> " + ex.Message);
return(null);
}
}
/// <devdoc>
/// Returns an IEnumerable that is the DataSource, which either came
/// from the DataSource property or from the control bound via the
/// DataSourceID property.
/// </devdoc>
protected virtual IEnumerable GetData()
{
DataSourceView view = ConnectToDataSourceView();
Debug.Assert(_currentViewValid);
if (view != null)
{
// DevDiv 1070535: enable async model binding for Repeater
bool useAsyncSelect = false;
if (AppSettings.EnableAsyncModelBinding)
{
var modelDataView = view as ModelDataSourceView;
useAsyncSelect = modelDataView != null && modelDataView.IsSelectMethodAsync;
}
if (useAsyncSelect)
{
_asyncSelectPending = true; // disable post data binding action until the callback is invoked
view.Select(SelectArguments, OnDataSourceViewSelectCallback);
}
else
{
return(view.ExecuteSelect(SelectArguments));
}
}
return(null);
}
public static DataSourceView GetTypedDatSourceView(YDataSourceType dataSourceType, DataSourceView dataSourceView = null)
{
DataSourceView ds = dataSourceType switch
{
YDataSourceType.AzureBlobStorage => new DataSourceViewAzureBlobStorage(),
YDataSourceType.AzureBlobFS => new DataSourceViewAzureBlobFS(),
YDataSourceType.AzureSqlDatabase => new DataSourceViewAzureSqlDatabase(),
YDataSourceType.AzureSqlDW => new DataSourceViewAzureSqlDatabase(),
YDataSourceType.AzureDatabricks => new DataSourceViewAzureDatabricks(),
YDataSourceType.CosmosDb => new DataSourceViewCosmosDb(),
_ => new DataSourceViewUnknown()
};
// clone
if (dataSourceView != null)
{
ds.DataSource.AdditionalData = dataSourceView.DataSource.AdditionalData;
ds.DataSource.Description = dataSourceView.DataSource.Description;
ds.DataSource.Name = dataSourceView.DataSource.Name;
ds.DataSource.Type = dataSourceView.DataSource.Type;
ds.EngineId = dataSourceView.EngineId;
ds.IsNew = dataSourceView.IsNew;
if (ds.DataSource.AdditionalData?["properties"] is JObject props)
{
ds.DataSource.OnDeserialized(props);
}
}
return(ds);
}
}
/// <summary>
/// Generate single dimension.
/// </summary>
/// <param name="objDatabase">DB instance.</param>
/// <param name="objDataSourceView">DataSourceView instance.</param>
/// <param name="strTableName">Table name.</param>
/// <param name="strTableKeyName">Table key.</param>
/// <returns>Dimension instance.</returns>
private static object GenerateDimension(Database objDatabase, DataSourceView objDataSourceView, string strTableName, string strTableKeyName)
{
try
{
Dimension objDimension = new Dimension();
//Add Dimension to the Database
objDimension = objDatabase.Dimensions.Add(strTableName);
objDimension.Source = new DataSourceViewBinding(objDataSourceView.ID);
DimensionAttributeCollection objDimensionAttributesColl = objDimension.Attributes;
//Add Dimension Attributes
DimensionAttribute objAttribute = objDimensionAttributesColl.Add(strTableKeyName);
//Set Attribute usage and source
objAttribute.Usage = AttributeUsage.Key;
objAttribute.KeyColumns.Add(strTableName, strTableKeyName, OleDbType.Integer);
objDimension.Update();
return(objDimension);
}
catch (Exception ex)
{
Console.WriteLine("Error in Creating the Dimension, Attribute, Hierarchy, and MemberProperty Objects - GenerateDimension. Error Message -> " + ex.Message);
return(null);
}
}
/// <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>
/// 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>
internal static void ADD_TABLE_TO_CUBE_DSV(IDbConnection oledb,
DataSourceView dsv,
String dsvSchemaName,
String dsvQueryText,
String dbTableName,
String tableType,
String db_SchemaName,
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 as System.Data.OleDb.OleDbConnection;
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);
}
private DataSourceView ConnectToDataSourceView()
{
if (!this._currentViewValid || base.DesignMode)
{
if ((this._currentView != null) && this._currentViewIsFromDataSourceID)
{
this._currentView.DataSourceViewChanged -= new EventHandler(this.OnDataSourceViewChanged);
}
this._currentDataSource = this.GetDataSource();
string dataMember = this.DataMember;
if (this._currentDataSource == null)
{
this._currentDataSource = new ReadOnlyDataSource(this.DataSource, dataMember);
}
else if (this.DataSource != null)
{
throw new InvalidOperationException(System.Web.SR.GetString("DataControl_MultipleDataSources", new object[] { this.ID }));
}
this._currentDataSourceValid = true;
DataSourceView view = this._currentDataSource.GetView(dataMember);
if (view == null)
{
throw new InvalidOperationException(System.Web.SR.GetString("DataControl_ViewNotFound", new object[] { this.ID }));
}
this._currentViewIsFromDataSourceID = base.IsBoundUsingDataSourceID;
this._currentView = view;
if ((this._currentView != null) && this._currentViewIsFromDataSourceID)
{
this._currentView.DataSourceViewChanged += new EventHandler(this.OnDataSourceViewChanged);
}
this._currentViewValid = true;
}
return(this._currentView);
}
/// <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);
}
IEnumerable GetData()
{
IEnumerable result;
#if NET_2_0
if (IsBoundUsingDataSourceID)
{
if (DataSourceID.Length == 0)
{
return(null);
}
if (boundDataSource == null)
{
return(null);
}
DataSourceView dsv = boundDataSource.GetView(String.Empty);
dsv.Select(SelectArguments, new DataSourceViewSelectCallback(SelectCallback));
result = data;
data = null;
}
else
#endif
result = DataSourceResolver.ResolveDataSource(DataSource, DataMember);
return(result);
}
public void InsertRow(int rowIndex)
{
IDataSource ds = GetDataSource();
DataSourceView view = ds.GetView(this.DataMember);
GridViewRow row = this.Rows[rowIndex];
OrderedDictionary values = new OrderedDictionary();
for (int i = 0; i < this.Columns.Count; ++i)
{
this.Columns[i].ExtractValuesFromCell(values, (DataControlFieldCell)row.Cells[i], DataControlRowState.Insert, false);
}
GridViewInsertingEventArgs insertingArgs = new GridViewInsertingEventArgs(values, rowIndex);
OnRowInserting(insertingArgs);
if (insertingArgs.Cancel)
{
return;
}
view.Insert(values, delegate(int affectedRecords, Exception ex)
{
GridViewInsertedEventArgs insertedArgs = new GridViewInsertedEventArgs(affectedRecords, ex, values);
OnRowInserted(insertedArgs);
if (!insertedArgs.KeepInInsertMode)
{
this.InsertRowsCount = 0;
}
return(insertedArgs.ExceptionHandled);
});
}
public ActionDataSourceView(ActionDataSource source, string viewName, DataSourceView underlyingDataSourceView)
: base(source, viewName)
{
UnderlyingView = underlyingDataSourceView;
ActionDS = source;
}
static void AddComputedColumn(DataSourceView dsv, OleDbConnection connection, String tableName, String computedColumnName, String expression)
{
DataSet tmpDataSet = new DataSet();
tmpDataSet.Locale = CultureInfo.CurrentCulture;
OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT ("
+ expression + ") AS [" + computedColumnName + "] FROM [dbo].["
+ tableName + "] WHERE 1=0", connection);
DataTable[] dataTables = adapter.FillSchema(tmpDataSet, SchemaType.Mapped, tableName);
DataTable dataTable = dataTables[0];
DataColumn dataColumn = dataTable.Columns[computedColumnName];
dataTable.Constraints.Clear();
dataTable.Columns.Remove(dataColumn);
dataColumn.ExtendedProperties.Add("DbColumnName", computedColumnName);
dataColumn.ExtendedProperties.Add("ComputedColumnExpression", expression);
dataColumn.ExtendedProperties.Add("IsLogical", "True");
dsv.Schema.Tables[tableName].Columns.Add(dataColumn);
dataColumn = null;
dataTable = null;
dataTables = null;
adapter = null;
tmpDataSet = null;
}
public async Task <IActionResult> OnGetAsync(Guid engineId, string dataSourceName)
{
if (string.IsNullOrEmpty(dataSourceName))
{
return(new NotFoundResult());
}
try
{
var response = await this.client.ProcessRequestApiAsync <YDataSourceUnknown>(
$"api/Datafactories/{engineId}/links/{dataSourceName}").ConfigureAwait(false);
if (response.StatusCode == System.Net.HttpStatusCode.NotFound)
{
return(new NotFoundResult());
}
var dataSource = response.Value;
this.DataSourceView = dataSource.ToTypedDataSourceView();
this.DataSourceView.IsNew = false;
this.DataSourceView.EngineId = engineId;
}
catch (Exception)
{
return(NotFound());
}
return(Page());
}
private void GetSelectedText()
{
if (this.DesignMode)
{
return;
}
if (this.SelectedID == "")
{
this.SelectedText = "";
return;
}
if (this.SelectedText != "" && !this.Page.IsCallback)
{
return;
}
try
{
try
{
// call the event
GetSeletedItemTextEventArgs e = new GetSeletedItemTextEventArgs();
e.SelectedID = this.SelectedID;
GetSeletedItemText(this, e);
this.SelectedText = e.TextValue;
}
catch
{
if (this.DataMember != "")
{
IDataSource IDS = this.GetDataSource();
DataSourceView dsv = IDS.GetView(this.DataMember);
dsv.Select(DataSourceSelectArguments.Empty, this.GetTextFromView);
}
else
{
string typeName = this.DataSource.GetType().Name;
switch (typeName)
{
case "DataSet":
SetSelectedTextValue(((DataSet)this.DataSource).Tables[0].DefaultView);
break;
case "DataTable":
SetSelectedTextValue(((DataTable)this.DataSource).DefaultView);
break;
default:
SetSelectedTextValue((DataView)this.DataSource);
break;
}
}
}
}
catch
{}
}
private static void BuildCube()
{
try
{
Console.WriteLine("Cube creation process started.");
Console.WriteLine("");
string strDBServerName = "LocalHost"; //имя сервер
string strProviderName = "msolap"; //имя провайдера
string strFactTableName = "OrderDetails"; //имя таблицы фактов
string strDBName = "northwind"; //имя бд
string strCubeDBName = "OLAPDB"; //имя куба
string strCubeDataSourceName = "OLAPDS"; //имя источника данных
string strCubeDataSourceViewName = "OLAPDSView"; //имя представления
int intDimensionTableCount = 2; //число измерений
string[,] strTableNamesAndKeys = { { "Products", "ProductID", "OrderDetails", "ProductID" },
{ "Orders", "OrderID", "OrderDetails", "OrderID" },
//{ "DimProduct", "ProductKey", "FactResellerSales", "ProductKey" },
// { "DimPromotion", "PromotionKey", "FactResellerSales", "PromotionKey" },
// { "DimReseller", "ResellerKey", "FactResellerSales", "ResellerKey" },
//{ "DimSalesTerritory", "SalesTerritoryKey", "FactResellerSales", "SalesTerritoryKey" },
};
Server objServer = new Server(); // сервер
Database objDatabase = new Database(); // бд
RelationalDataSource objDataSource = new RelationalDataSource(); //источник
DataSourceView objDataSourceView = new DataSourceView(); // представление
DataSet objDataSet = new DataSet(); // набор данных
Dimension[] objDimensions = new Dimension[intDimensionTableCount]; //массив измерений
//Connecting to the Analysis Services. Подключаемся к АС
objServer = (Server)ConnectAnalysisServices(strDBServerName, strProviderName);
//Creating a Database. Создаем БД
objDatabase = (Database)CreateDatabase(objServer, strCubeDBName);
//Creating a DataSource. Создаем источник
objDataSource = (RelationalDataSource)CreateDataSource(objServer, objDatabase, strCubeDataSourceName, strDBServerName, strDBName);
//Creating a DataSourceView. Создаем представление
objDataSet = (DataSet)GenerateDWSchema(strDBServerName, strDBName, strFactTableName, strTableNamesAndKeys, intDimensionTableCount);
objDataSourceView = (DataSourceView)CreateDataSourceView(objDatabase, objDataSource, objDataSet, strCubeDataSourceViewName);
//Creating the Dimension, Attribute, Hierarchy, and MemberProperty Objects. Создаем измерения и прочее
objDimensions = (Dimension[])CreateDimension(objDatabase, objDataSourceView, strTableNamesAndKeys, intDimensionTableCount);
//Creating the Cube, MeasureGroup, Measure, and Partition Objects. Создаем куб
CreateCube(objDatabase, objDataSourceView, objDataSource, objDimensions, strFactTableName, strTableNamesAndKeys, intDimensionTableCount);
objDatabase.Process(ProcessType.ProcessFull);
Console.WriteLine("Cube created successfully.");
}
catch (Exception ex)
{
Console.WriteLine("Error -> " + ex.Message);
}
Console.WriteLine("");
Console.WriteLine("Press any key to exit.");
Console.ReadLine();
}
public SessionDataSourceView(SqlDataSource owner, string name, HttpContext context, DataSourceView originalView, HttpSessionState session)
: base(owner, name, context)
{
this.owner = owner as SessionDataSource;
this.originalView = originalView;
this.session = session;
this.context = context;
}
private DataItem CreateDataItem(DataSourceView dsv, string tableName, string columnName)
{
DataTable dataTable = ((DataSourceView)dsv).Schema.Tables[tableName];
DataColumn dataColumn = dataTable.Columns[columnName];
return(new DataItem(tableName, columnName,
OleDbTypeConverter.GetRestrictedOleDbType(dataColumn.DataType)));
}
public UsedColumn(DataItem di, ColumnBinding column, DataSourceView dsv, string usageType, string usageObjectName)
{
m_dataItem = di;
m_column = column;
m_dsv = dsv;
m_usageType = usageType;
m_usageObjectName = usageObjectName;
}
protected override DataSourceView GetData()
{
dataBindTrace.Append("[Start GetData]");
DataSourceView d = base.GetData();
dataBindTrace.Append("[End GetData]");
return(d);
}
/// <devdoc>
/// Gets the DataSourceView of the IDataSource that this control is
/// bound to, if any.
/// </devdoc>
protected virtual DataSourceView GetData()
{
DataSourceView view = ConnectToDataSourceView();
Debug.Assert(_currentViewValid);
return(view);
}
//--------------------
public bool CreateDimensionAttributeForLevel(Dimension dimension, DataSourceView dataSourceView,
String levelId, String levelName, String tableName, String keyColumn, String nameColumn,
AttributeUsage attributeUsage, OrderBy orderBy, AttributeType attribType, bool visible)
{
if (dimension.Attributes == null)
{
return false;
}
DimensionAttribute dimAttrib;
if (!dimension.Attributes.Contains(levelId))
{
dimAttrib = dimension.Attributes.Add(levelName, levelId);
dimAttrib.Usage = attributeUsage;
dimAttrib.OrderBy = orderBy;
dimAttrib.Type = attribType;
dimAttrib.AttributeHierarchyVisible = true;// visible;
dimAttrib.AttributeHierarchyOptimizedState = OptimizationType.NotOptimized;
dimAttrib.AttributeHierarchyOrdered = true;
dimAttrib.IsAggregatable = true;
//设置MembersWithDataCaption会导致当前粒度在子粒度中存在，形如"XXXX(All)"，因此注释掉
//if (attributeUsage == AttributeUsage.Parent)
//{
// dimAttrib.MembersWithDataCaption = "*(All)";
//}
dimAttrib.MembersWithData = MembersWithData.NonLeafDataHidden;
DataItem diKey = CreateDataItem(dataSourceView, tableName, keyColumn);
dimAttrib.KeyColumns.Add(diKey);
if (!String.IsNullOrEmpty(nameColumn))
{
DataItem diName = CreateDataItem(dataSourceView, tableName, nameColumn);
dimAttrib.NameColumn = diName;
DataItem diValue = CreateDataItem(dataSourceView, tableName, nameColumn);
dimAttrib.ValueColumn = diValue;
}
return true;
}
return true;
}
static void AddCompositeRelation(DataSourceView dsv, String fkTableName, String pkTableName, String columnName1, String columnName2)
{
DataColumn[] fkColumns = new DataColumn[2];
fkColumns[0] = dsv.Schema.Tables[fkTableName].Columns[columnName1];
fkColumns[1] = dsv.Schema.Tables[fkTableName].Columns[columnName2];
DataColumn[] pkColumns = new DataColumn[2];
pkColumns[0] = dsv.Schema.Tables[pkTableName].Columns[columnName1];
pkColumns[1] = dsv.Schema.Tables[pkTableName].Columns[columnName2];
dsv.Schema.Relations.Add("FK_" + fkTableName + "_" + columnName1 + "_" + columnName2, pkColumns, fkColumns, true);
}
/*
* Demo code!
*/
private void AddTable(DataSourceView dsv, OleDbConnection connection, string tableName, string dataSourceID)
{
OleDbDataAdapter adapter = new OleDbDataAdapter(
"SELECT * FROM [dbo].[" + tableName + "] WHERE 1=0",
connection);
DataTable[] dataTables = adapter.FillSchema(dsv.Schema,
SchemaType.Mapped, tableName);
DataTable dataTable = dataTables[0];
dataTable.ExtendedProperties.Add("TableType", "Table");
dataTable.ExtendedProperties.Add("DbSchemaName", "dbo");
dataTable.ExtendedProperties.Add("DbTableName", tableName);
dataTable.ExtendedProperties.Add("FriendlyName", tableName);
dataTable.ExtendedProperties.Add("DataSourceID", dataSourceID);
}
private static string GetQueryToValidateUniqueness(DataSourceView dsv, DataItem[] child, DataItem[] parent)
{
//need to do GetColumnBindingForDataItem
StringBuilder select = new StringBuilder();
StringBuilder outerSelect = new StringBuilder();
StringBuilder groupBy = new StringBuilder();
StringBuilder join = new StringBuilder();
StringBuilder topLevelColumns = new StringBuilder();
Dictionary<DataTable, JoinedTable> tables = new Dictionary<DataTable, JoinedTable>();
List<string> previousColumns = new List<string>();
foreach (DataItem di in child)
{
ColumnBinding col = GetColumnBindingForDataItem(di);
if (!previousColumns.Contains("[" + col.TableID + "].[" + col.ColumnID + "]"))
{
string colAlias = System.Guid.NewGuid().ToString();
DataColumn dc = dsv.Schema.Tables[col.TableID].Columns[col.ColumnID];
groupBy.Append((select.Length == 0 ? "group by " : ","));
outerSelect.Append((select.Length == 0 ? "select " : ","));
select.Append((select.Length == 0 ? "select distinct " : ","));
string sIsNull = "";
//select.Append(" /*" + dc.DataType.FullName + "*/ "); //for troubleshooting data types
if (dc.DataType == typeof(string))
{
if (di.NullProcessing == NullProcessing.Preserve)
sIsNull = "'__BIDS_HELPER_DIMENSION_HEALTH_CHECK_UNIQUE_STRING__'"; //a unique value that shouldn't ever occur in the real data
else
sIsNull = "''";
}
else if (dc.DataType == typeof(DateTime))
{
if (di.NullProcessing == NullProcessing.Preserve)
sIsNull = "'1/1/1899 01:02:03 AM'"; //a unique value that shouldn't ever occur in the real data
else
sIsNull = "'12/30/1899'"; //think this is what SSAS converts null dates to
}
else //numeric
{
if (di.NullProcessing == NullProcessing.Preserve)
sIsNull = "-987654321.123456789"; //a unique value that shouldn't ever occur in the real data
else
sIsNull = "0";
}
join.Append((join.Length == 0 ? "on " : "and ")).Append("coalesce(y.[").Append(colAlias).Append("],").Append(sIsNull).Append(") = coalesce(z.[").Append(colAlias).Append("],").Append(sIsNull).AppendLine(")");
groupBy.Append("[").Append(colAlias).AppendLine("]");
outerSelect.Append("[").Append(colAlias).AppendLine("]");
if (topLevelColumns.Length > 0) topLevelColumns.Append(",");
topLevelColumns.Append("y.[").Append(colAlias).Append("] as [").Append(dc.ColumnName).AppendLine("]");
if (!dc.ExtendedProperties.ContainsKey("ComputedColumnExpression"))
{
select.Append("[").Append(colAlias).Append("] = [").Append(dsv.Schema.Tables[col.TableID].ExtendedProperties["FriendlyName"].ToString()).Append("].[").Append((dc.ExtendedProperties["DbColumnName"] ?? dc.ColumnName).ToString()).AppendLine("]");
}
else
{
select.Append("[").Append(colAlias).Append("]");
select.Append(" = ").AppendLine(dc.ExtendedProperties["ComputedColumnExpression"].ToString());
}
if (!tables.ContainsKey(dsv.Schema.Tables[col.TableID]))
{
tables.Add(dsv.Schema.Tables[col.TableID], new JoinedTable(dsv.Schema.Tables[col.TableID]));
}
previousColumns.Add("[" + col.TableID + "].[" + col.ColumnID + "]");
}
}
foreach (DataItem di in parent)
{
ColumnBinding col = GetColumnBindingForDataItem(di);
if (!previousColumns.Contains("[" + col.TableID + "].[" + col.ColumnID + "]"))
{
string colAlias = System.Guid.NewGuid().ToString();
DataColumn dc = dsv.Schema.Tables[col.TableID].Columns[col.ColumnID];
select.Append(",");
//use the __PARENT__ prefix in case there's a column with the same name but different table
if (!dc.ExtendedProperties.ContainsKey("ComputedColumnExpression"))
{
select.Append("[").Append(colAlias).Append("] = [").Append(dsv.Schema.Tables[col.TableID].ExtendedProperties["FriendlyName"].ToString()).Append("].[").Append((dc.ExtendedProperties["DbColumnName"] ?? dc.ColumnName).ToString()).AppendLine("]");
}
else
{
select.Append("[").Append(colAlias).Append("]");
select.Append(" = ").AppendLine(dc.ExtendedProperties["ComputedColumnExpression"].ToString());
}
if (topLevelColumns.Length > 0) topLevelColumns.Append(",");
topLevelColumns.Append("y.[").Append(colAlias).Append("] as [").Append(dc.ColumnName).AppendLine("]");
if (!tables.ContainsKey(dsv.Schema.Tables[col.TableID]))
{
tables.Add(dsv.Schema.Tables[col.TableID], new JoinedTable(dsv.Schema.Tables[col.TableID]));
}
previousColumns.Add("[" + col.TableID + "].[" + col.ColumnID + "]");
}
}
int iLastTableCount = 0;
while (iLastTableCount != tables.Values.Count)
{
iLastTableCount = tables.Values.Count;
JoinedTable[] arrJt = new JoinedTable[iLastTableCount];
tables.Values.CopyTo(arrJt, 0); //because you can't iterate the dictionary keys while they are changing
foreach (JoinedTable jt in arrJt)
{
TraverseParentRelationshipsAndAddNewTables(tables, jt.table);
}
}
//check that all but one table have a valid join path to them
DataTable baseTable = null;
foreach (JoinedTable t in tables.Values)
{
if (!t.Joined)
{
if (baseTable == null)
{
baseTable = t.table;
}
else
{
throw new Exception("Cannot find join path for table " + t.table.TableName + " or " + baseTable.TableName + ". Only one table can be the starting table for the joins.");
}
}
}
if (baseTable == null)
{
//there are joins to and from the base table, so guess at the base table based on the child parameter to this function
ColumnBinding col = GetColumnBindingForDataItem(child[0]);
baseTable = dsv.Schema.Tables[col.TableID];
}
//by now, all tables needed for joins will be in the dictionary
select.Append("\r\nfrom ").AppendLine(GetFromClauseForTable(baseTable));
select.Append(TraverseParentRelationshipsAndGetFromClause(tables, baseTable));
outerSelect.AppendLine("\r\nfrom (").Append(select).AppendLine(") x").Append(groupBy).AppendLine("\r\nhaving count(*)>1");
string invalidValuesInner = outerSelect.ToString();
outerSelect = new StringBuilder();
outerSelect.Append("select ").AppendLine(topLevelColumns.ToString()).AppendLine(" from (").Append(select).AppendLine(") as y");
outerSelect.AppendLine("join (").AppendLine(invalidValuesInner).AppendLine(") z").AppendLine(join.ToString());
return outerSelect.ToString();
}
/// <devdoc>
/// Connects this data bound control to the appropriate DataSourceView
/// and hooks up the appropriate event listener for the
/// DataSourceViewChanged event. The return value is the new view (if
/// any) that was connected to. An exception is thrown if there is
/// a problem finding the requested view or data source.
/// </devdoc>
private DataSourceView ConnectToDataSourceView() {
if (_currentViewValid && !DesignMode) {
// If the current view is correct, there is no need to reconnect
return _currentView;
}
// Disconnect from old view, if necessary
if ((_currentView != null) && (_currentViewIsFromDataSourceID)) {
// We only care about this event if we are bound through the DataSourceID property
_currentView.DataSourceViewChanged -= new EventHandler(OnDataSourceViewChanged);
}
EnsureSingleDataSource();
// Connect to new view
_currentDataSource = GetDataSource();
string dataMember = DataMember;
if (_currentDataSource == null) {
// DataSource control was not found, construct a temporary data source to wrap the data
_currentDataSource = new ReadOnlyDataSource(DataSource, dataMember);
}
_currentDataSourceValid = true;
// IDataSource was found, extract the appropriate view and return it
DataSourceView newView = _currentDataSource.GetView(dataMember);
if (newView == null) {
throw new InvalidOperationException(SR.GetString(SR.DataControl_ViewNotFound, ID));
}
_currentViewIsFromDataSourceID = IsDataBindingAutomatic;
_currentView = newView;
if ((_currentView != null) && (_currentViewIsFromDataSourceID)) {
// We only care about this event if we are bound through the DataSourceID property
_currentView.DataSourceViewChanged += new EventHandler(OnDataSourceViewChanged);
}
_currentViewValid = true;
return _currentView;
}
public UnusedColumn(DataColumn column, DataSourceView dsv)
{
m_column = column;
m_dsv = dsv;
}
/// <summary>
/// 创建表
/// </summary>
/// <param name="parDsv">数据源视图</param>
/// <param name="parConn">连接到数据仓库</param>
/// <param name="parTable">表名</param>
public void AddTable(DataSourceView dsv, OleDbConnection connection, String tableName)
{
OleDbDataAdapter adapter = new OleDbDataAdapter(
"SELECT * FROM [dbo].[" + tableName + "] WHERE 1=0",
connection);
DataTable[] dataTables = adapter.FillSchema(dsv.Schema, //将表的结构映射到数据源视图
SchemaType.Mapped, tableName);
DataTable dataTable = dataTables[0];
dataTable.ExtendedProperties.Add("TableType", "Table"); //为表增加扩展属性
dataTable.ExtendedProperties.Add("DbSchemaName", "dbo");
dataTable.ExtendedProperties.Add("DbTableName", tableName);
dataTable.ExtendedProperties.Add("FriendlyName", tableName);
}
/// <summary>
/// 创建关系
/// </summary>
/// <param name="parDsv">数据源视图</param>
/// <param name="parFkTableName">外键表名</param>
/// <param name="parFkColumnName">外键列名</param>
/// <param name="parPkTableName">主键表名</param>
/// <param name="parPkColumnName">主键列名</param>
public void AddRelation(DataSourceView parDsv, string parFkTableName, string parFkColumnName, string parPkTableName, string parPkColumnName)
{
DataColumn fkColumn = parDsv.Schema.Tables[parFkTableName].Columns[parFkColumnName];
DataColumn pkColumn = parDsv.Schema.Tables[parPkTableName].Columns[parPkColumnName];
parDsv.Schema.Relations.Add("FK_" + parFkTableName + "_" + parPkColumnName, pkColumn, fkColumn);
}
/*
* Create new database objects; for future use
*/
void AddNewDataAccessObjects(Database db)
{
// Create connection to datasource cto extract schema to a dataset
DataSet dset = new DataSet();
SqlConnection cn = new SqlConnection("Data Source=localhost; Initial Catalog=Chapter 16; Integrated Security=true");
// Create the Customers data adapter with the calculated appended
SqlDataAdapter daCustomers = new SqlDataAdapter(
"SELECT *, " +
"(CASE WHEN (Age < 30) THEN ’GenY’ " +
" WHEN (Age >= 30 AND Age < 40) THEN ’GenX’ " +
"ELSE ’Baby Boomer’ END) AS Generation " +
"FROM Customers", cn);
daCustomers.FillSchema(dset, SchemaType.Mapped, "Customers");
// Add Extended properties to the Generation column indicating to
// Analysis Services that it is a calculated column
DataColumn genColumn = dset.Tables["Customers"].Columns
["Generation"];
genColumn.ExtendedProperties.Add("DbColumnName", "Generation");
genColumn.ExtendedProperties.Add("Description",
"Customer generation");
genColumn.ExtendedProperties.Add("IsLogical", "true");
genColumn.ExtendedProperties.Add("ComputedColumnExpression",
"CASE WHEN (Age < 30) THEN ’GenY’ " +
"WHEN (Age >= 30 AND Age < 40) THEN ’GenX’ " +
"ELSE ’Baby Boomer’ END");
// Create a ’Pay Channels’ data adapter with a customer query
// for our named query
SqlDataAdapter daPayChannels = new SqlDataAdapter(
"SELECT * FROM Channels " +
"WHERE Channel IN (’Cinemax’, ’Encore’, ’HBO’, ’Showtime’, " +
"’STARZ!’, ’The Movie Channel’)", cn);
daPayChannels.FillSchema(dset, SchemaType.Mapped, "PayChannels");
// Add Extended properties to the PayChannels table indicating to
// Analysis Services that it is a named query
DataTable pcTable = dset.Tables["PayChannels"];
pcTable.ExtendedProperties.Add("IsLogical", "true");
pcTable.ExtendedProperties.Add("Description",
"Channels requiring an additional fee");
pcTable.ExtendedProperties.Add("TableType", "View");
pcTable.ExtendedProperties.Add("QueryDefinition",
"SELECT * FROM Channels " +
"WHERE Channel IN (’Cinemax’, ’Encore’, ’HBO’, ’Showtime’, " +
"’STARZ!’, ’The Movie Channel’)");
// Add relationship between Customers and PayChannels
DataRelation drCustomerPayChannels = new DataRelation(
"CustomerPayChannels",
dset.Tables["Customers"].Columns["SurveyTakenID"],
dset.Tables["PayChannels"].Columns["SurveyTakenID"]);
dset.Relations.Add(drCustomerPayChannels);
// Access the data source and the DSV created previously
// by specifying the ID
DataSourceView dsv = new DataSourceView("MovieClick", "MovieClick");
dsv.DataSourceID = "MovieClick";
dsv.Schema = dset.Clone();
db.DataSourceViews.Add(dsv);
// Update the database to create the objects on the server
db.Update(UpdateOptions.ExpandFull);
}
private void CreateCube(Database database, DataSource dataSource, DataSourceView dataSourceView, CubeEntity cube)
{
Cube ssasCube = database.Cubes.Find(cube.ID);
if (ssasCube != null)
ssasCube.Drop();
ssasCube = database.Cubes.Add(cube.Name, cube.ID);
ssasCube.Source = new DataSourceViewBinding(dataSourceView.ID);
ssasCube.StorageMode = StorageMode.Molap;
ssasCube.Visible = true;
foreach (var item in cube.Dimensions)
{
ssasCube.Dimensions.Add(item.ID);
}
var defaultGroup = ssasCube.MeasureGroups.Add("Default");
defaultGroup.StorageMode = StorageMode.Molap;
defaultGroup.ProcessingMode = ProcessingMode.LazyAggregations;
foreach (var outerMeasure in cube.Measures)
{
var ssasMeasure = defaultGroup.Measures.Add(outerMeasure.Name, outerMeasure.ID);
ssasMeasure.Source = this.CreateDataItem(dataSourceView, cube.TableName, outerMeasure.ColumnName);
ssasMeasure.AggregateFunction = this.GetAggregator(outerMeasure.Aggregator);
//measure.FormatString = format;// "000,000.##";
//measure.Description = description;
//measure.DisplayFolder = folderName;
ssasMeasure.Visible = outerMeasure.Visable;
}
foreach (var dim in cube.Dimensions)
{
var cubeDim = ssasCube.Dimensions.Find(dim.ID);
var groupDim = defaultGroup.Dimensions.Add(dim.ID);
string levelId = "";
for (int i = 0; i < cubeDim.Attributes.Count; i++)
{
if (cubeDim.Dimension.Attributes[i].Usage == AttributeUsage.Key)
{
levelId = cubeDim.Dimension.Attributes[i].ID;
break;
}
}
var measureGroupAttribute = groupDim.Attributes.Add(levelId);
measureGroupAttribute.Type = MeasureGroupAttributeType.Granularity;
measureGroupAttribute.KeyColumns.Add(CreateDataItem(dataSourceView, cube.TableName, dim.FKColumn));
}
CreatePartition(defaultGroup, dataSource, cube.TableName, DateTime.Now);
ssasCube.Update(UpdateOptions.ExpandFull);
ssasCube.Process();
}
private void CreateDim(Database database, DataSourceView dataSourceView, DimensionEntity dim)
{
Dimension ssasDimension;
if (!database.Dimensions.Contains(dim.ID))
{
ssasDimension = database.Dimensions.Add(dim.Name, dim.ID);
ssasDimension.UnknownMember = UnknownMemberBehavior.Hidden;
ssasDimension.AttributeAllMemberName = "all";
ssasDimension.StorageMode = DimensionStorageMode.Molap;
ssasDimension.Source = new DataSourceViewBinding(dataSourceView.ID);
ssasDimension.Type = DimensionType.Regular;
}
else
{
ssasDimension = database.Dimensions[dim.ID];
}
if ((dim.Hierarchies == null || dim.Hierarchies.Count == 0) && (dim.Levels != null && dim.Levels.Count != 0))
{
if (!ssasDimension.Hierarchies.Contains(DefaultHierarchy))
{
Hierarchy ssasHierarchy = ssasDimension.Hierarchies.Add(DefaultHierarchy, DefaultHierarchy);
ssasHierarchy.AllMemberName = "all";
CreateLevels(ssasDimension, ssasHierarchy, dataSourceView, dim.Levels);
}
}
else
{
foreach (var outerHierarchy in dim.Hierarchies)
{
Hierarchy ssasHierarchy;
if (!ssasDimension.Hierarchies.Contains(outerHierarchy.ID))
{
ssasHierarchy = ssasDimension.Hierarchies.Add(outerHierarchy.Name, outerHierarchy.ID);
ssasHierarchy.AllMemberName = "all";
}
else
{
ssasHierarchy = ssasDimension.Hierarchies[outerHierarchy.ID];
}
if (outerHierarchy.Levels != null && outerHierarchy.Levels.Count > 0)
{
CreateLevels(ssasDimension, ssasHierarchy, dataSourceView, outerHierarchy.Levels);
}
}
}
ssasDimension.Update();
}
public void CreateViewRelation(DataSourceView dataSourceView, String parFkTableName, String parFkColumnName, String parPkTableName, String parPkColumnName)
{
DataColumn fkColumn = dataSourceView.Schema.Tables[parFkTableName].Columns[parFkColumnName];
DataColumn pkColumn = dataSourceView.Schema.Tables[parPkTableName].Columns[parPkColumnName];
string fkName = "FK_" + parFkTableName + "_" + parPkTableName;
fkName = GetFKName(dataSourceView, fkName, 0);
dataSourceView.Schema.Relations.Add(fkName, pkColumn, fkColumn);
}
protected void IterateDsvColumns(DataSourceView dsv)
{
m_dsv = dsv;
//add all DSV columns to a list
unusedColumns.Clear();
usedColumns.Clear();
foreach (DataTable t in dsv.Schema.Tables)
{
foreach (DataColumn c in t.Columns)
{
unusedColumns.Add("[" + t.TableName + "].[" + c.ColumnName + "]", new UnusedColumn(c, dsv));
}
}
//remove columns that are used in dimensions
foreach (Dimension dim in dsv.Parent.Dimensions)
{
if (dim.DataSourceView != null && dim.DataSourceView.ID == dsv.ID)
{
foreach (DimensionAttribute attr in dim.Attributes)
{
foreach (DataItem di in attr.KeyColumns)
{
ProcessDataItemInLists(di, "Dimension Attribute Key");
}
ProcessDataItemInLists(attr.NameColumn, "Dimension Attribute Name");
ProcessDataItemInLists(attr.ValueColumn, "Dimension Attribute Value");
ProcessDataItemInLists(attr.UnaryOperatorColumn, "Dimension Attribute Unary Operator");
ProcessDataItemInLists(attr.SkippedLevelsColumn, "Dimension Attribute Skipped Levels");
ProcessDataItemInLists(attr.CustomRollupColumn, "Dimension Attribute Custom Rollup");
ProcessDataItemInLists(attr.CustomRollupPropertiesColumn, "Dimension Attribute Custom Rollup Properties");
foreach (AttributeTranslation tran in attr.Translations)
{
ProcessDataItemInLists(tran.CaptionColumn, "Dimension Attribute Translation");
}
}
}
}
foreach (Cube cube in dsv.Parent.Cubes)
{
if (cube.DataSourceView != null && cube.DataSourceView.ID == dsv.ID)
{
foreach (MeasureGroup mg in cube.MeasureGroups)
{
//remove columns that are used in measures
foreach (Measure m in mg.Measures)
{
ProcessDataItemInLists(m.Source, "Measure");
}
//remove columns that are used in dimension relationships
foreach (MeasureGroupDimension mgdim in mg.Dimensions)
{
if (mgdim is ManyToManyMeasureGroupDimension)
{
//no columns to remove
}
else if (mgdim is DataMiningMeasureGroupDimension)
{
//no columns to remove
}
else if (mgdim is RegularMeasureGroupDimension)
{
//Degenerate dimensions and Reference dimensions
RegularMeasureGroupDimension regMDdim = (RegularMeasureGroupDimension)mgdim;
foreach (MeasureGroupAttribute mga in regMDdim.Attributes)
{
if (mga.Type == MeasureGroupAttributeType.Granularity)
{
foreach (DataItem di3 in mga.KeyColumns)
{
ProcessDataItemInLists(di3, "Fact Table Dimension Key");
}
}
}
}
}
}
}
}
//remove mining structure columns
foreach (MiningStructure structure in dsv.Parent.MiningStructures)
{
if (structure.DataSourceView != null && structure.DataSourceView.ID == dsv.ID)
RecurseMiningStructureColumnsAndProcessDataItemInLists(structure.Columns);
}
}
/// <summary>
/// 创建项
/// </summary>
/// <param name="parDsv">数据源视图</param>
/// <param name="parTableName">表名</param>
/// <param name="parColumnName">列名</param>
/// <returns></returns>
public DataItem CreateDataItem(DataSourceView parDsv, string parTableName, string parColumnName)
{
DataTable dataTable = ((DataSourceView)parDsv).Schema.Tables[parTableName];
DataColumn dataColumn = dataTable.Columns[parColumnName];
return new DataItem(parTableName, parColumnName, OleDbTypeConverter.GetRestrictedOleDbType(dataColumn.DataType));
}
public UsedColumn(DataItem di, ColumnBinding column, DataSourceView dsv, string usageType, string usageObjectName)
{
m_dataItem = di;
m_column = column;
m_dsv = dsv;
m_usageType = usageType;
m_usageObjectName = usageObjectName;
}
private DataItem CreateDataItem(DataSourceView dsv, string tableName, string columnName)
{
DataTable dataTable = ((DataSourceView)dsv).Schema.Tables[tableName];
DataColumn dataColumn = dataTable.Columns[columnName];
return new DataItem(tableName, columnName,
OleDbTypeConverter.GetRestrictedOleDbType(dataColumn.DataType));
}
public void RaiseUnsupportedCapabilitiesError(DataSourceView view) {}
static void AddTable(DataSourceView dsv, OleDbConnection connection, String tableName)
{
string strSelectText = "SELECT * FROM [dbo].[" + tableName + "] WHERE 1=0";
OleDbDataAdapter adapter = new OleDbDataAdapter(strSelectText, connection);
DataTable[] dataTables = adapter.FillSchema(dsv.Schema, SchemaType.Mapped, tableName);
DataTable dataTable = dataTables[0];
dataTable.ExtendedProperties.Add("TableType", "Table");
dataTable.ExtendedProperties.Add("DbSchemaName", "dbo");
dataTable.ExtendedProperties.Add("DbTableName", tableName);
dataTable.ExtendedProperties.Add("FriendlyName", tableName);
dataTable = null;
dataTables = null;
adapter = null;
}
/*
* Create mining structure for selected database
*/
private MiningStructure CreateMiningStructure(Database objDatabase)
{
// drop the existing structures with the same name
DropExistingStructures(objDatabase, sStructureName);
// Initialize a new mining structure
MiningStructure currentMiningStruct = new MiningStructure(sStructureName, sStructureName);
currentMiningStruct.Source = new DataSourceViewBinding("Adventure Works DW");
DataSourceView currentDataSource = new DataSourceView("Adventure Works DW");
// Create the columns of the mining structure
// setting the type, content and data binding
// User Id column
ScalarMiningStructureColumn StructKey = new ScalarMiningStructureColumn("StructKey", "StructKey");
StructKey.Type = MiningStructureColumnTypes.Long;
StructKey.Content = MiningStructureColumnContents.Key;
StructKey.IsKey = true;
// Add data binding to the column
StructKey.KeyColumns.Add("dbo_DimCustomer", "CustomerKey", System.Data.OleDb.OleDbType.Integer);
// Add the column to the mining structure
currentMiningStruct.Columns.Add(StructKey);
// Generation column
ScalarMiningStructureColumn Gender = new ScalarMiningStructureColumn("Gender", "Gender");
Gender.Type = MiningStructureColumnTypes.Text;
Gender.Content = MiningStructureColumnContents.Discrete;
// Add data binding to the column
Gender.KeyColumns.Add("dbo_DimCustomer", "Gender", System.Data.OleDb.OleDbType.WChar);
// Add the column to the mining structure
currentMiningStruct.Columns.Add(Gender);
// Add Nested table by creating a table column and adding
// a key column to the nested table
/*
TableMiningStructureColumn PayChannels = new TableMiningStructureColumn("PayChannels", "PayChannels");
PayChannels.ForeignKeyColumns.Add("dbo_Customer", "TotalChildren", System.Data.OleDb.OleDbType.Integer);
ScalarMiningStructureColumn Channel = new ScalarMiningStructureColumn("Channel", "Channel");
Channel.Type = MiningStructureColumnTypes.Text;
Channel.Content = MiningStructureColumnContents.Key;
Channel.IsKey = true;
// Add data binding to the column
Channel.KeyColumns.Add("dbo_Customer", "FirstName", System.Data.OleDb.OleDbType.WChar);
PayChannels.Columns.Add(Channel);
currentMiningStruct.Columns.Add(PayChannels);
* */
// Add the mining structure to the database
objDatabase.MiningStructures.Add(currentMiningStruct);
currentMiningStruct.Update();
return currentMiningStruct;
}
void UpdateViewData ()
{
if (currentView != null)
currentView.DataSourceViewChanged -= new EventHandler (OnDataSourceViewChanged);
DataSourceView view = InternalGetData ();
if (view != currentView)
currentView = view;
if (currentView != null)
currentView.DataSourceViewChanged += new EventHandler (OnDataSourceViewChanged);
}
/*
* Create new database objects; for future use
*/
private void CreateDataAccessObjects(Database objDB)
{
// Create a relational data source
// by specifying the name and the id
RelationalDataSource ds = new RelationalDataSource("MovieClick", Utils.GetSyntacticallyValidID("MovieClick", typeof(Database)));
ds.ConnectionString = "Provider=SQLNCLI10.1;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Chapter 16";
objDB.DataSources.Add(ds);
// Create connection to datasource to extract schema to a dataset
DataSet dset = new DataSet();
SqlConnection cn = new SqlConnection("Data Source=localhost; Initial Catalog=Chapter 16; Integrated Security=true");
// Create data adapters from database tables and load schemas
SqlDataAdapter daCustomers = new SqlDataAdapter("SELECT * FROM Customers", cn);
daCustomers.FillSchema(dset, SchemaType.Mapped, "Customers");
SqlDataAdapter daChannels = new SqlDataAdapter("SELECT * FROM Channels", cn);
daChannels.FillSchema(dset, SchemaType.Mapped, "Channels");
// Add relationship between Customers and Channels
DataRelation drCustomerChannels = new DataRelation("Customer_Channels", dset.Tables["Customers"].Columns["SurveyTakenID"], dset.Tables["Channels"].Columns["SurveyTakenID"]);
dset.Relations.Add(drCustomerChannels);
// Create the DSV, ad the dataset and add to the database
DataSourceView dsv = new DataSourceView("SimpleMovieClick", "SimpleMovieClick");
dsv.DataSourceID = "MovieClick";
dsv.Schema = dset.Clone();
objDB.DataSourceViews.Add(dsv);
// Update the database to create the objects on the server
objDB.Update(UpdateOptions.ExpandFull);
}
private string GetFKName(DataSourceView dataSourceView, string name, int idx)
{
string temp = name;
foreach (DataRelation dr in dataSourceView.Schema.Relations)
{
if (dr.RelationName == name)
{
idx++;
temp += idx.ToString();
return GetFKName(dataSourceView, temp, idx);
}
}
return name;
}
protected override void OnDataPropertyChanged ()
{
base.OnDataPropertyChanged ();
currentView = null;
}
private static string GetSnowflakeQuery(DataSourceView dsv, ColumnBinding[] child, ColumnBinding[] parent, ColumnBinding prefer)
{
StringBuilder select = new StringBuilder();
Dictionary<DataTable, JoinedTable> tables = new Dictionary<DataTable, JoinedTable>();
List<string> previousColumns = new List<string>();
foreach (ColumnBinding col in child)
{
if (!previousColumns.Contains("[" + col.TableID + "].[" + col.ColumnID + "]"))
{
DataColumn dc = dsv.Schema.Tables[col.TableID].Columns[col.ColumnID];
select.Append((select.Length == 0 ? "select " : ","));
if (!dc.ExtendedProperties.ContainsKey("ComputedColumnExpression"))
{
select.Append("[").Append(dsv.Schema.Tables[col.TableID].ExtendedProperties["FriendlyName"].ToString()).Append("].[").Append(GetColumnName(dc)).AppendLine("]");
}
else
{
select.Append("[").Append(GetColumnName(dc)).Append("] = ");
select.AppendLine(dc.ExtendedProperties["ComputedColumnExpression"].ToString());
}
if (!tables.ContainsKey(dsv.Schema.Tables[col.TableID]))
{
tables.Add(dsv.Schema.Tables[col.TableID], new JoinedTable(dsv.Schema.Tables[col.TableID]));
}
previousColumns.Add("[" + col.TableID + "].[" + col.ColumnID + "]");
}
}
foreach (ColumnBinding col in parent)
{
if (!previousColumns.Contains("[" + col.TableID + "].[" + col.ColumnID + "]"))
{
DataColumn dc = dsv.Schema.Tables[col.TableID].Columns[col.ColumnID];
select.Append(",");
if (!dc.ExtendedProperties.ContainsKey("ComputedColumnExpression"))
{
select.Append("[").Append(dsv.Schema.Tables[col.TableID].ExtendedProperties["FriendlyName"].ToString()).Append("].[").Append(GetColumnName(dc)).AppendLine("]");
}
else
{
select.Append("[").Append(GetColumnName(dc)).Append("] = ");
select.AppendLine(dc.ExtendedProperties["ComputedColumnExpression"].ToString());
}
if (!tables.ContainsKey(dsv.Schema.Tables[col.TableID]))
{
tables.Add(dsv.Schema.Tables[col.TableID], new JoinedTable(dsv.Schema.Tables[col.TableID]));
}
previousColumns.Add("[" + col.TableID + "].[" + col.ColumnID + "]");
}
}
int iLastTableCount = 0;
while (iLastTableCount != tables.Values.Count)
{
iLastTableCount = tables.Values.Count;
JoinedTable[] arrJt = new JoinedTable[iLastTableCount];
tables.Values.CopyTo(arrJt, 0); //because you can't iterate the dictionary keys while they are changing
foreach (JoinedTable jt in arrJt)
{
TraverseParentRelationshipsAndAddNewTables(tables, jt.table);
}
}
//check that all but one table have a valid join path to them
DataTable baseTable = null;
foreach (JoinedTable t in tables.Values)
{
if (!t.Joined)
{
if (baseTable == null)
{
baseTable = t.table;
}
else
{
throw new Exception("Cannot find join path for table " + t.table.TableName + " or " + baseTable.TableName + ". Only one table can be the starting table for the joins.");
}
}
}
//by now, all tables needed for joins will be in the dictionary
select.Append("\r\nfrom ").AppendLine(GetFromClauseForTable(baseTable));
select.Append(TraverseParentRelationshipsAndGetFromClause(tables, baseTable, prefer));
return select.ToString();
}
static void AddRelation(DataSourceView dsv, String fkTableName, String fkColumnName, String pkTableName, String pkColumnName)
{
DataColumn fkColumn = dsv.Schema.Tables[fkTableName].Columns[fkColumnName];
DataColumn pkColumn = dsv.Schema.Tables[pkTableName].Columns[pkColumnName];
dsv.Schema.Relations.Add("FK_" + fkTableName + "_" + fkColumnName, pkColumn, fkColumn, true);
}
private void CreateLevels(Dimension ssasDimension, Hierarchy ssasHierarchy, DataSourceView dataSourceView, List<LevelEntity> outerLevels)
{
var miniOuterLevel = outerLevels[outerLevels.Count - 1];
this.CreateDimensionAttributeForLevel(
ssasDimension
, dataSourceView
, miniOuterLevel.ID
, miniOuterLevel.Name
, miniOuterLevel.SourceTable
, miniOuterLevel.KeyColumn
, miniOuterLevel.NameColumn
, AttributeUsage.Key
, OrderBy.Key
, AttributeType.Regular
, true);
foreach (var outerLevel in outerLevels)
{
Level ssasLevel;
if (!ssasHierarchy.Levels.Contains(outerLevel.ID))
{
this.CreateDimensionAttributeForLevel(
ssasDimension
, dataSourceView
, outerLevel.ID
, outerLevel.Name
, outerLevel.SourceTable
, outerLevel.KeyColumn
, outerLevel.NameColumn
, AttributeUsage.Regular
, OrderBy.Key
, AttributeType.Regular
, true);
ssasLevel = ssasHierarchy.Levels.Add(outerLevel.Name);
ssasLevel.SourceAttributeID = ssasDimension.Attributes.GetByName(outerLevel.Name).ID;
}
else
{
ssasLevel = ssasHierarchy.Levels[outerLevel.ID];
}
}
}
DataSourceView ConnectToDataSource ()
{
if (_boundDataSourceView != null)
return _boundDataSourceView;
/* verify that the data source exists and is an IDataSource */
object ctrl = null;
Page page = Page;
if (page != null)
ctrl = page.FindControl (DataSourceID);
if (ctrl == null || !(ctrl is IDataSource)) {
string format;
if (ctrl == null)
format = "DataSourceID of '{0}' must be the ID of a control of type IDataSource. A control with ID '{1}' could not be found.";
else
format = "DataSourceID of '{0}' must be the ID of a control of type IDataSource. '{1}' is not an IDataSource.";
throw new HttpException (String.Format (format, ID, DataSourceID));
}
_boundDataSourceView = ((IDataSource)ctrl).GetView (String.Empty);
_boundDataSourceView.DataSourceViewChanged += OnDataSourceViewChanged;
return _boundDataSourceView;
}
/// <summary>
/// Build mining database
/// </summary>
/// <param name="MainServerConnectionString">Main server connection string</param>
/// <param name="AnalysisServerConnectionString">Analysis services connection string</param>
/// <param name="TempServerConnectionString">Temp server connection string (contain views)</param>
/// <param name="strPrefix">Prefix</param>
/// <param name="dt_parametters">Decision Tree Parametters</param>
public static void BuildMiningDatabase(string MainServerConnectionString, string AnalysisServerConnectionString, string TempServerConnectionString, string strPrefix = "PF", DecisionTreeAlgorithmParameters dt_parametter = null)
{
if (dt_parametter == null)
dt_parametter = new DecisionTreeAlgorithmParameters();
string strMiningDBName = "Job Zoom Mining"; //Mining database name (Analysis Service)
string strMiningDataSourceName = "Data Source"; //Mining datasource name (Analysis Service)
string strMiningDataSourceViewName = "Data Source View"; //Mining datasource view name (Analysis Service)
string[] strFactTableNames = getAllMiningTableNames(TempServerConnectionString, strPrefix); //tables in datasource view to mining
string[,] strTableNamesAndKeys = { { "PivotProfile", "ProfileBasicId", "PivotProfile", "ProfileBasicId" }, };
int intDimensionTableCount = 0;
Server objServer = new Server();
Database objDatabase = new Database();
RelationalDataSource objDataSource = new RelationalDataSource();
DataSourceView objDataSourceView = new DataSourceView();
DataSet objDataSet = new DataSet();
Dimension[] objDimensions = new Dimension[intDimensionTableCount];
MiningStructure[] objMiningStructures = new MiningStructure[strFactTableNames.Length];
//Console.WriteLine("Mining creation process started.");
//Console.WriteLine("");
//Console.WriteLine("Step 1. Connecting to the Analysis Services.");
//Console.WriteLine("Step 1. Started!");
objServer = (Server)ConnectAnalysisServices(AnalysisServerConnectionString);
//Console.WriteLine("Step 1. Finished!");
//Console.WriteLine("");
//Console.WriteLine("Step 2. Creating a Database.");
//Console.WriteLine("Step 2. Started!");
objDatabase = (Database)CreateDatabase(objServer, strMiningDBName);
strMiningDBName = objDatabase.Name;
//Console.WriteLine("Step 2. Finished!");
//Console.WriteLine("");
//Console.WriteLine("Step 3. Creating a DataSource.");
//Console.WriteLine("Step 3. Started!");
objDataSource = (RelationalDataSource)CreateDataSource(objServer, objDatabase, strMiningDataSourceName, TempServerConnectionString);
//Console.WriteLine("Step 3. Finished!");
//Console.WriteLine("");
//Console.WriteLine("Step 4. Creating a DataSourceView.");
//Console.WriteLine("Step 4. Started!");
//objDataSet = (DataSet)GenerateDWSchema(strDBServerName, strDBName, strFactTableName, strTableNamesAndKeys, intDimensionTableCount);
objDataSet = (DataSet)GenerateDWSchema(TempServerConnectionString, strPrefix); //Get all mining views
objDataSourceView = (DataSourceView)CreateDataSourceView(objDatabase, objDataSource, objDataSet, strMiningDataSourceViewName);
//Console.WriteLine("Step 4. Finished!");
//Console.WriteLine("");
//Console.WriteLine("Step 5. Createing Mining Structures [with Decision Tree Algorithms]");
//Console.WriteLine("Step 5. Started!");
objMiningStructures = (MiningStructure[])CreateMiningStructures(objDatabase, objDataSourceView, strFactTableNames, new DecisionTreeAlgorithmParameters());
//objDatabase.Process(ProcessType.ProcessFull);
//Console.WriteLine("Step 5. Finished!");
//Console.WriteLine("");
//Console.WriteLine("Step 6. Export mining data to JobZoom Database (Database Engine)");
//Console.WriteLine("Step 6. Started!");
//Console.WriteLine("Preparing... Put website to maintenance mode");
//EXEC WEB SITE MAINTENANCE SERVICE METHOD
//Console.WriteLine("Preparing... Cleaning DecisionTreeNode and DecisionTreeNodeDistribution");
//Console.WriteLine("\nStep 6. Finished!");
//Console.WriteLine("");
exportMiningDataToDB(MainServerConnectionString, AnalysisServerConnectionString, strFactTableNames, strPrefix);
//Console.WriteLine("Export completed! Release website to continuing for using");
//WEBSITE CAN CONTINUE FOR USING
//Console.WriteLine("Process Full...");
//objDatabase.Process(ProcessType.ProcessFull);
//Console.WriteLine("Analysis Service Database created successfully.");
//Console.WriteLine("Step 7. Removing Analysis Database");
//Console.WriteLine("Step 7. Started!");
//Console.WriteLine(deleteDatabase(objServer, objDatabase.Name));
deleteDatabase(objServer, objDatabase.Name);
//Console.WriteLine("Removing Analysis Database completely ...");
//Console.WriteLine("\nStep 7. Finished!");
//Console.WriteLine("Press any key to exit.");
//Console.ReadLine();
}
public void AddComputedColumn(DataSourceView dsv, OleDbConnection connection, String tableName, String computedColumnName, String expression)
{
DataSet tmpDataSet = new DataSet();
tmpDataSet.Locale = CultureInfo.CurrentCulture;
OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT ("
+ expression + ") AS [" + computedColumnName + "] FROM [dbo].["
+ tableName + "] WHERE 1=0", connection);
DataTable[] dataTables = adapter.FillSchema(tmpDataSet,
SchemaType.Mapped, tableName);
DataTable dataTable = dataTables[0];
DataColumn dataColumn = dataTable.Columns[computedColumnName];
dataTable.Constraints.Clear();
dataTable.Columns.Remove(dataColumn);
dataColumn.ExtendedProperties.Add("DbColumnName", computedColumnName);
dataColumn.ExtendedProperties.Add("ComputedColumnExpression",
expression);
dataColumn.ExtendedProperties.Add("IsLogical", "True");
dsv.Schema.Tables[tableName].Columns.Add(dataColumn);
}
/// <summary>
/// Generate a mining structure and full process it
/// </summary>
/// <param name="objDatabase">Analysis Service Database instance</param>
/// <param name="objDataSourceView">Analysis Service DataSourceView instance</param>
/// <param name="strCaseTableName">Mining table name</param>
/// <returns>Mining structure</returns>
public static object GenerateMiningStructure(Database objDatabase, DataSourceView objDataSourceView, string strCaseTableName, DecisionTreeAlgorithmParameters dtParams)
{
try
{
MiningStructure objMiningStructure = new MiningStructure();
objMiningStructure = objDatabase.MiningStructures.Add(objDatabase.MiningStructures.GetNewName(StringEncode(strCaseTableName)));
objMiningStructure.HoldoutMaxPercent = dtParams.HoldoutMaxPercent; // Percent for testing
objMiningStructure.Source = new DataSourceViewBinding(objDataSourceView.ID);
objMiningStructure.CaseTableName = strCaseTableName;
foreach (string name in getAllColumnName(objDataSourceView, strCaseTableName))
{
string colName = StringEncode(name);
ScalarMiningStructureColumn column = new ScalarMiningStructureColumn(colName, colName);
switch (colName)
{
case "ID":
// ProfileBasicId column
column.Type = MiningStructureColumnTypes.Long;
column.Content = MiningStructureColumnContents.Key;
column.IsKey = true;
// Add data binding to the column
column.KeyColumns.Add(strCaseTableName, name);
// Add the column to the mining structure
objMiningStructure.Columns.Add(column);
break;
case "ProfileBasicId":
case "JobPostingId":
case "UserId":
case "JobTitle":
case "JobName":
case "CompanyId":
case "CompanyName":
//column.Type = MiningStructureColumnTypes.Text;
//column.Content = MiningStructureColumnContents.Discrete;
break;
case "IsApproved":
default:
column.Type = MiningStructureColumnTypes.Boolean;
column.Content = MiningStructureColumnContents.Discrete;
// Add data binding to the column
column.KeyColumns.Add(strCaseTableName, name);
// Add the column to the mining structure
objMiningStructure.Columns.Add(column);
break;
}
}
MiningModel objMiningModel = objMiningStructure.CreateMiningModel(true, StringEncode(strCaseTableName));
//MiningModel objMiningModel = objMiningStructure.MiningModels.Add(objMiningStructure.MiningModels.GetNewName(strMiningStructureName));
objMiningModel.Algorithm = MiningModelAlgorithms.MicrosoftDecisionTrees;
objMiningModel.AllowDrillThrough = true;
objMiningModel.AlgorithmParameters.Add("SCORE_METHOD", dtParams.SCORE_METHOD);
objMiningModel.AlgorithmParameters.Add("COMPLEXITY_PENALTY", dtParams.COMPLEXITY_PENALTY);
objMiningModel.AlgorithmParameters.Add("SPLIT_METHOD", dtParams.SPLIT_METHOD);
objMiningModel.AlgorithmParameters.Add("MAXIMUM_INPUT_ATTRIBUTES", dtParams.MAXIMUM_INPUT_ATTRIBUTES);
objMiningModel.AlgorithmParameters.Add("MAXIMUM_OUTPUT_ATTRIBUTES", dtParams.MAXIMUM_OUTPUT_ATTRIBUTES);
objMiningModel.AlgorithmParameters.Add("MINIMUM_SUPPORT", dtParams.MINIMUM_SUPPORT);
int i = 0;
foreach(MiningModelColumn col in objMiningModel.Columns)
{
switch (col.Name)
{
case "IsApproved":
objMiningModel.Columns[i].Usage = "Predict";
break;
case "ID":
objMiningModel.Columns[i].Usage = "Key";
break;
default:
objMiningModel.Columns[i].Usage = "Input";
break;
}
++i;
}
//objMiningModel.Update(UpdateOptions.ExpandFull);
objMiningStructure.Update(UpdateOptions.ExpandFull);
Console.WriteLine("Processing mining model " + objMiningStructure.Name + "...");
objMiningModel.Process(ProcessType.ProcessFull);
Console.WriteLine("Process " + objMiningStructure.Name + " finished!");
return objMiningStructure;
}
catch (Exception ex)
{
throw new Exception("Error in Creating a Mining structure - GenerateMiningStructure. Error Message -> " + ex.Message);
}
}
