/// <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();
}
/// <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();
}
}
private void CreateDataSourceView()
{
try
{
_CbDataSourceView = new DataSourceView();
//Add Data Source View to the Database.
_CbDataSourceView = _CbDatabase.DataSourceViews.Add(_CbDatabase.DataSourceViews.GetNewName(CbDataSourceViewName));
_CbDataSourceView.DataSourceID = _CbDataSource.ID;
_CbDataSourceView.Schema = _CbDataSet;
_CbDataSourceView.Update();
}
catch (Exception ex)
{
AppendLogLine("Error in Creating a DataSourceView - CreateDataSourceView. Error Message -> " + ex.Message);
throw;
}
}
/// <summary>
/// Creating a DataSourceView.
/// </summary>
/// <param name="objDatabase">DB instance.</param>
/// <param name="objDataSource">DataSource instance.</param>
/// <param name="objDataSet">DataSet instance.</param>
/// <param name="strCubeDataSourceViewName">Cube DataSourceView Name.</param>
/// <returns>DataSourceView instance.</returns>
private static object CreateDataSourceView(Database objDatabase, RelationalDataSource objDataSource, DataSet objDataSet, string strCubeDataSourceViewName)
{
try
{
DataSourceView objDataSourceView = new DataSourceView();
//Add Data Source View to the Database.
objDataSourceView = objDatabase.DataSourceViews.Add(objDatabase.DataSourceViews.GetNewName(strCubeDataSourceViewName));
objDataSourceView.DataSourceID = objDataSource.ID;
objDataSourceView.Schema = objDataSet;
objDataSourceView.Update();
return(objDataSourceView);
}
catch (Exception ex)
{
Console.WriteLine("Error in Creating a DataSourceView - CreateDataSourceView. Error Message -> " + ex.Message);
return(null);
}
}
//例子
static DataSourceView CreateDataSourceView(Database db, string strDataSourceName)
{
// Create the data source view
DataSourceView dsv = db.DataSourceViews.FindByName(strDataSourceName);
if (dsv != null)
{
dsv.Drop();
}
dsv = db.DataSourceViews.Add(strDataSourceName, strDataSourceName);
dsv.DataSourceID = strDataSourceName;
dsv.Schema = new DataSet();
dsv.Schema.Locale = CultureInfo.CurrentCulture;
// Open a connection to the data source
OleDbConnection connection = new OleDbConnection(dsv.DataSource.ConnectionString);
connection.Open();
#region Create tables
// Add the DimTime table
AddTable(dsv, connection, "DimTime");
AddComputedColumn(dsv, connection, "DimTime", "SimpleDate", "DATENAME(mm, FullDateAlternateKey) + ' ' + DATENAME(dd, FullDateAlternateKey) + ',' + ' ' + DATENAME(yy, FullDateAlternateKey)");
AddComputedColumn(dsv, connection, "DimTime", "CalendarYearDesc", "'CY' + ' ' + CalendarYear");
AddComputedColumn(dsv, connection, "DimTime", "CalendarSemesterDesc", "CASE WHEN CalendarSemester = 1 THEN 'H1'+' '+ 'CY' +' '+ CONVERT(CHAR (4), CalendarYear) ELSE 'H2'+' '+ 'CY' +' '+ CONVERT(CHAR (4), CalendarYear) END");
AddComputedColumn(dsv, connection, "DimTime", "CalendarQuarterDesc", "'Q' + CONVERT(CHAR (1), CalendarQuarter) +' '+ 'CY' +' '+ CONVERT(CHAR (4), CalendarYear)");
AddComputedColumn(dsv, connection, "DimTime", "MonthName", "EnglishMonthName+' '+ CONVERT(CHAR (4), CalendarYear)");
AddComputedColumn(dsv, connection, "DimTime", "FiscalYearDesc", "'FY' + ' ' + FiscalYear");
AddComputedColumn(dsv, connection, "DimTime", "FiscalSemesterDesc", "CASE WHEN FiscalSemester = 1 THEN 'H1'+' '+ 'FY' +' '+ CONVERT(CHAR (4), FiscalYear) ELSE 'H2'+' '+ 'FY' +' '+ CONVERT(CHAR (4), FiscalYear) END");
AddComputedColumn(dsv, connection, "DimTime", "FiscalQuarterDesc", "'Q' + CONVERT(CHAR (1), FiscalQuarter) +' '+ 'FY' +' '+ CONVERT(CHAR (4), FiscalYear)");
AddComputedColumn(dsv, connection, "DimTime", "FiscalMonthNumberOfYear", "CASE WHEN MonthNumberOfYear = '1' THEN CONVERT(int,'7') WHEN MonthNumberOfYear = '2' THEN CONVERT(int,'8') WHEN MonthNumberOfYear = '3' THEN CONVERT(int,'9') WHEN MonthNumberOfYear = '4' THEN CONVERT(int,'10') WHEN MonthNumberOfYear = '5' THEN CONVERT(int,'11') WHEN MonthNumberOfYear = '6' THEN CONVERT(int,'12') WHEN MonthNumberOfYear = '7' THEN CONVERT(int,'1') WHEN MonthNumberOfYear = '8' THEN CONVERT(int,'2') WHEN MonthNumberOfYear = '9' THEN CONVERT(int,'3') WHEN MonthNumberOfYear = '10' THEN CONVERT(int,'4') WHEN MonthNumberOfYear = '11' THEN CONVERT(int,'5') WHEN MonthNumberOfYear = '12' THEN CONVERT(int,'6') END");
dsv.Update();
// Add the DimGeography table
AddTable(dsv, connection, "DimGeography");
// Add the DimProductCategory table
AddTable(dsv, connection, "DimProductCategory");
// Add the DimProductSubcategory table
AddTable(dsv, connection, "DimProductSubcategory");
AddRelation(dsv, "DimProductSubcategory", "ProductCategoryKey", "DimProductCategory", "ProductCategoryKey");
// Add the DimProduct table
AddTable(dsv, connection, "DimProduct");
AddComputedColumn(dsv, connection, "DimProduct", "ProductLineName", "CASE ProductLine WHEN 'M' THEN 'Mountain' WHEN 'R' THEN 'Road' WHEN 'S' THEN 'Accessory' WHEN 'T' THEN 'Touring' ELSE 'Components' END");
AddRelation(dsv, "DimProduct", "ProductSubcategoryKey", "DimProductSubcategory", "ProductSubcategoryKey");
dsv.Update();
// Add the DimCustomer table
AddTable(dsv, connection, "DimCustomer");
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");
AddRelation(dsv, "DimCustomer", "GeographyKey", "DimGeography", "GeographyKey");
// Add the DimReseller table
AddTable(dsv, connection, "DimReseller");
AddComputedColumn(dsv, connection, "DimReseller", "OrderFrequencyDesc", "CASE WHEN OrderFrequency = 'A' THEN 'Annual' WHEN OrderFrequency = 'S' THEN 'Bi-Annual' ELSE 'Quarterly' END");
AddComputedColumn(dsv, connection, "DimReseller", "OrderMonthDesc", "CASE WHEN OrderMonth = '1' THEN 'January' WHEN OrderMonth = '2' THEN 'February' WHEN OrderMonth = '3' THEN 'March' WHEN OrderMonth = '4' THEN 'April' WHEN OrderMonth = '5' THEN 'May' WHEN OrderMonth = '6' THEN 'June' WHEN OrderMonth = '7' THEN 'July' WHEN OrderMonth = '8' THEN 'August' WHEN OrderMonth = '9' THEN 'September' WHEN OrderMonth = '10' THEN 'October' WHEN OrderMonth = '11' THEN 'November' WHEN OrderMonth = '12' THEN 'December' ELSE 'Never Ordered' END");
// Add the DimCurrency table
AddTable(dsv, connection, "DimCurrency");
dsv.Update();
// Add the DimSalesReason table
AddTable(dsv, connection, "DimSalesReason");
// Add the FactInternetSales table
AddTable(dsv, connection, "FactInternetSales");
AddRelation(dsv, "FactInternetSales", "ProductKey", "DimProduct", "ProductKey");
AddRelation(dsv, "FactInternetSales", "CustomerKey", "DimCustomer", "CustomerKey");
AddRelation(dsv, "FactInternetSales", "OrderDateKey", "DimTime", "TimeKey");
AddRelation(dsv, "FactInternetSales", "ShipDateKey", "DimTime", "TimeKey");
AddRelation(dsv, "FactInternetSales", "DueDateKey", "DimTime", "TimeKey");
AddRelation(dsv, "FactInternetSales", "CurrencyKey", "DimCurrency", "CurrencyKey");
dsv.Update();
// Add the FactResellerSales table
AddTable(dsv, connection, "FactResellerSales");
AddRelation(dsv, "FactResellerSales", "ProductKey", "DimProduct", "ProductKey");
AddRelation(dsv, "FactResellerSales", "ResellerKey", "DimReseller", "ResellerKey");
AddRelation(dsv, "FactResellerSales", "OrderDateKey", "DimTime", "TimeKey");
AddRelation(dsv, "FactResellerSales", "ShipDateKey", "DimTime", "TimeKey");
AddRelation(dsv, "FactResellerSales", "DueDateKey", "DimTime", "TimeKey");
AddRelation(dsv, "FactResellerSales", "CurrencyKey", "DimCurrency", "CurrencyKey");
// Add the FactInternetSalesReason table
AddTable(dsv, connection, "FactInternetSalesReason");
AddCompositeRelation(dsv, "FactInternetSalesReason", "FactInternetSales", "SalesOrderNumber", "SalesOrderLineNumber");
dsv.Update();
// Add the FactCurrencyRate table
AddTable(dsv, connection, "FactCurrencyRate");
AddRelation(dsv, "FactCurrencyRate", "CurrencyKey", "DimCurrency", "CurrencyKey");
AddRelation(dsv, "FactCurrencyRate", "TimeKey", "DimTime", "TimeKey");
#endregion
// Send the data source view definition to the server
dsv.Update();
return(dsv);
}
/// <summary>
/// Creating a DataSourceView.
/// </summary>
/// <param name="objDatabase">Analysis Service Database intance</param>
/// <param name="objDataSource">Analysis Service DataSource instance</param>
/// <param name="objDataSet">Dataset</param>
/// <param name="strMiningDataSourceViewName">Mining DataSourceView Name.</param>
/// <returns>DataSourceView instance.</returns>
public static object CreateDataSourceView(Database objDatabase, RelationalDataSource objDataSource, DataSet objDataSet, string strMiningDataSourceViewName)
{
try
{
DataSourceView objDataSourceView = new DataSourceView();
//Add Data Source View to the Database.
objDataSourceView = objDatabase.DataSourceViews.Add(objDatabase.DataSourceViews.GetNewName(strMiningDataSourceViewName));
objDataSourceView.DataSourceID = objDataSource.ID;
objDataSourceView.Schema = objDataSet;
objDataSourceView.Update();
return objDataSourceView;
}
catch (Exception ex)
{
throw new Exception("Error in Creating a DataSourceView - CreateDataSourceView. Error Message -> " + ex.Message);
return null;
}
}