private void Bt_BuildCube_Click(object sender, EventArgs e)
{
try
{
CONFIGURATION_HELPER.BASIC_CONFIGURATION_FOLDER = @"E:\2.自己文档\BigData\SSASAutomation\SSASAutomation\MDXHelper.SSASAutomation";
String sqlConnectionString = @"Data Source=.\sql2012;Initial Catalog=CLB_Hospital_DW;Integrated Security=SSPI";
DB_SQLHELPER_SQLServer sqlHelper = new DB_SQLHELPER_SQLServer(sqlConnectionString);
AS_METADATA_SQLServer asMeta = new AS_METADATA_SQLServer();
IDbConnection db_connection = sqlHelper.GET_DATABASE_CONNECTION();
String oleDBConnectionString = sqlConnectionString;
IDbConnection oledb_connection = sqlHelper.GET_OLEDB_CONNECTION(oleDBConnectionString);
String cube_server_name = @".\sql2012";
String cubeDBName = "SSASCubeDB";
String cubeName = "SSASCube";
String DWConnectionString = @"Provider=SQLNCLI11.1;Data Source=.\sql2012;Integrated Security=SSPI;Initial Catalog=CLB_Hospital_DW";
Server cubeServer = AS_API_HELPER.CREATE_OLAP_CONNECTION(cube_server_name, null);
AS_API_CUBE_CREATOR cubeCreator = new AS_API_CUBE_CREATOR();
cubeCreator.CREATE_SSAS_BASE_CUBE(sqlHelper, cubeServer, cubeDBName, cubeName, DWConnectionString);
cubeServer.Disconnect();
cubeServer = AS_API_HELPER.CREATE_OLAP_CONNECTION(cube_server_name, null);
IDbConnection a = new System.Data.OleDb.OleDbConnection();
cubeCreator.CREATE_CUBE_COMMON(sqlHelper, oledb_connection, asMeta, cubeServer, cubeDBName, cubeName, 0);
cubeServer.Disconnect();
}
catch (Exception ex)
{
throw (ex);
}
}
public IEnumerable <BuildSSASCube> BuildSSASCubeWithParameters(
String clrOlapServer = "",
String clrOlapCubeDB = "",
String clrOlapCube = "",
String clrDbServer = "",
String clrDWName = "",
String clrDWConnectionString = ""
)
{
try
{
clrDWConnectionString = @"Data Source=.\SQL2012;Initial Catalog=CLB_Hospital_DW;Integrated Security=SSPI";
clrOlapServer = @".\SQL2012";
clrOlapCubeDB = "SSASCubeDB";
clrOlapCube = "SSASCube";
//clrDWConnectionString = CONFIGURATION_HELPER.GET_METADATA_PROPERTY("dw_connection_string");// @"Data Source=.\SQL2012;Initial Catalog=CLB_Hospital_DW;Integrated Security=SSPI";
//clrOlapServer = CONFIGURATION_HELPER.GET_METADATA_PROPERTY("clr.olap.server");// @".\SQL2012";
//clrOlapCubeDB = CONFIGURATION_HELPER.GET_METADATA_PROPERTY("clr.olap.cubedb");
//clrOlapCube = CONFIGURATION_HELPER.GET_METADATA_PROPERTY("clr.olap.cube");
clrDbServer = @".\SQL2012";
clrDWName = "CLB_Hospital_DW";
String sqlConnectionString = clrDWConnectionString;
// @"Data Source=.\SQL2012;Initial Catalog=CLB_Hospital_DW;Integrated Security=SSPI";
DB_SQLHELPER_SQLServer sqlHelper = new DB_SQLHELPER_SQLServer(sqlConnectionString);
AS_METADATA_SQLServer asMeta = new AS_METADATA_SQLServer();
IDbConnection db_connection = sqlHelper.GET_DATABASE_CONNECTION();
String oleDBConnectionString = sqlConnectionString;
IDbConnection oledb_connection = sqlHelper.GET_OLEDB_CONNECTION(oleDBConnectionString);
String cube_server_name = clrOlapServer;// @".\SQL2012";
String cubeDBName = clrOlapCubeDB;
String cubeName = clrOlapCube;
String dwServer = clrDbServer;
String dwName = clrDWName;
String DWConnectionString = @"Provider=SQLNCLI11.1;Data Source=" + dwServer + ";Integrated Security=SSPI;Initial Catalog=" + clrDWName;
//@"Provider=SQLNCLI11.1;Data Source=.\SQL2012;Integrated Security=SSPI;Initial Catalog=CLB_Hospital_DW";
Server cubeServer = AS_API_HELPER.CREATE_OLAP_CONNECTION(cube_server_name, null);
AS_API_CUBE_CREATOR cubeCreator = new AS_API_CUBE_CREATOR();
cubeCreator.CREATE_SSAS_BASE_CUBE(sqlHelper, cubeServer, cubeDBName, cubeName, DWConnectionString);
cubeServer.Disconnect();
cubeServer = AS_API_HELPER.CREATE_OLAP_CONNECTION(cube_server_name, null);
IDbConnection a = new System.Data.OleDb.OleDbConnection();
cubeCreator.CREATE_CUBE_COMMON(sqlHelper, oledb_connection, asMeta, cubeServer, cubeDBName, cubeName, 0);
cubeServer.Disconnect();
}
catch (Exception ex)
{
buildCube = new BuildSSASCube[]
{
new BuildSSASCube {
BuildSSASCubeMessage = "failed to build ssas cube, message:" + ex.Message.ToString(), Status = 0
}
};
}
return(buildCube);
}
//POST: /api/UpdateCubeMDXWithConfiguration
public IEnumerable <MessageStatus> UpdateCubeMDXWithConfiguration([FromBody] UpdateMDXCalculation Parameters)
{
try
{
String mdxCaculationScript = Parameters.mdxCaculationScript;
String clrOlapServer = Parameters.clrOlapServer;
String clrOlapCubeDB = Parameters.clrOlapCubeDB;
String clrOlapCube = Parameters.clrOlapCube;
String clrSSASConfigurationConnectionString = Parameters.clrSSASConfigurationConnectionString;
IEnumerator <MessageStatus> mdxIE = (new CheckMDXSyntaxController()).CheckMDXSyntax(new CheckMDXSyntax(mdxCaculationScript)).GetEnumerator();
while (mdxIE.MoveNext())
{
if (mdxIE.Current.Status == 1)
{
// @"Data Source=.\SQL2012;Initial Catalog=CLB_Hospital_DW;Integrated Security=SSPI";
DB_SQLHELPER_SQLServer sqlHelper = new DB_SQLHELPER_SQLServer(clrSSASConfigurationConnectionString);
String SSASConfigurationPath = AppDomain.CurrentDomain.BaseDirectory + @"App_Data";
CONFIGURATION_HELPER.BASIC_CONFIGURATION_FOLDER = SSASConfigurationPath; // @"E:\2.自己文档\BigData\SSASAutomation\SSASAutomation\MDXHelper.SSASAutomation";
String cube_server_name = clrOlapServer; // @".\SQL2012";
String cubeDBName = clrOlapCubeDB;
String cubeName = clrOlapCube;
Server cubeServer = AS_API_HELPER.CREATE_OLAP_CONNECTION(cube_server_name, null);
AS_API_CUBE_CREATOR cubeCreator = new AS_API_CUBE_CREATOR();
Database CubeDB = cubeServer.Databases.FindByName(cubeDBName);
Cube CubeObg = CubeDB.Cubes.FindByName(cubeName);
cubeCreator.UPDATE_CUBE_COMMAND(sqlHelper, cubeServer, CubeDB, CubeObg, mdxCaculationScript);
CubeDB.Update(Microsoft.AnalysisServices.UpdateOptions.ExpandFull);
}
else
{
buildCube = new MessageStatus[]
{
new MessageStatus {
Message = "MDX Syntax is invalid.", Status = 0
}
};
}
break;
}
}
catch (Exception ex)
{
buildCube = new MessageStatus[]
{
new MessageStatus {
Message = "failed to update cube mdx, message:" + ex.Message.ToString(), Status = 0
}
};
}
return(buildCube);
}
//POST: /api/BuildSSASCubeWithConfiguration
public IEnumerable <MessageStatus> BuildSSASCubeWithConfiguration([FromBody] BuildSSASCubeWithConfiguration Pameters)
{
try
{
String SSASConfigurationPath = AppDomain.CurrentDomain.BaseDirectory + @"App_Data";
CONFIGURATION_HELPER.BASIC_CONFIGURATION_FOLDER = SSASConfigurationPath;// @"E:\2.自己文档\BigData\SSASAutomation\SSASAutomation\MDXHelper.SSASAutomation";
String clrOlapServer = Pameters.clrOlapServer;
String clrOlapCubeDB = Pameters.clrOlapCubeDB;
String clrOlapCube = Pameters.clrOlapCube;
String clrDWServer = Pameters.clrOlapServer;
String clrDWName = Pameters.clrDWName;
String clrDWConnectionString = Pameters.clrDWConnectionString;
String clrSSASConfigurationConnectionString = Pameters.clrSSASConfigurationConnectionString;
// @"Data Source=.\SQL2012;Initial Catalog=CLB_Hospital_DW;Integrated Security=SSPI";
DB_SQLHELPER_SQLServer sqlHelper = new DB_SQLHELPER_SQLServer(clrSSASConfigurationConnectionString);
AS_METADATA_SQLServer asMeta = new AS_METADATA_SQLServer();
IDbConnection db_connection = sqlHelper.GET_DATABASE_CONNECTION();
String oleDBConnectionString = clrSSASConfigurationConnectionString;
IDbConnection oledb_connection = sqlHelper.GET_OLEDB_CONNECTION(clrDWConnectionString);
String cube_server_name = clrOlapServer;
String cubeDBName = clrOlapCubeDB;
String cubeName = clrOlapCube;
String dwServer = clrDWServer;
String dwName = clrDWName;
// DW链接字符串和SSASAutomation的数据库连接字符串都提供如下格式的链接字符串
//@"Provider=SQLNCLI11.1;Data Source=" + dwServer + ";Integrated Security=SSPI;Initial Catalog=" + clrDWName;
//@"Data Source=.\SQL2012;Integrated Security=SSPI;Initial Catalog=CLB_Hospital_DW";
//@"Data Source=.\SQL2012;Password=**********;User ID=sa;Initial Catalog=CLB_Hospital_DW"
//Oledb会在DWConnectionString前面加上Provider=SQLNCLI11.1;
//Oledb会在SSAS的DataSource里设置，以及在DataSourceView里使用
Server cubeServer = AS_API_HELPER.CREATE_OLAP_CONNECTION(cube_server_name, null);
AS_API_CUBE_CREATOR cubeCreator = new AS_API_CUBE_CREATOR();
cubeCreator.CREATE_SSAS_BASE_CUBE(sqlHelper, cubeServer, cubeDBName, cubeName, sqlHelper.GET_OLEDB_CONNECTION_STRING(clrDWConnectionString));
cubeServer.Disconnect();
cubeServer = AS_API_HELPER.CREATE_OLAP_CONNECTION(cube_server_name, null);
IDbConnection a = new System.Data.OleDb.OleDbConnection();
cubeCreator.CREATE_CUBE_COMMON(sqlHelper, oledb_connection, asMeta, cubeServer, cubeDBName, cubeName, 0);
cubeServer.Disconnect();
}
catch (Exception ex)
{
buildCube = new MessageStatus[]
{
new MessageStatus {
Message = "failed to build ssas cube, message:" + ex.Message.ToString(), Status = 0
}
};
}
return(buildCube);
}
/// <summary>
///
/// </summary>
/// <param name="mdxCaculationScript"></param>
/// <param name="clrOlapServer"></param>
/// <param name="clrOlapCubeDB"></param>
/// <param name="clrOlapCube"></param>
/// <param name="clrDWConnectionString"></param>
/// <returns></returns>
public IEnumerable <BuildSSASCube> UpdateCubeMDX(
String mdxCaculationScript = "",
String clrOlapServer = "",
String clrOlapCubeDB = "",
String clrOlapCube = "",
String clrDWConnectionString = "")
{
try
{
clrDWConnectionString = @"Data Source=.\SQL2012;Initial Catalog=SSASAutomationDB;Integrated Security=SSPI";
clrOlapServer = @".\SQL2012";
clrOlapCubeDB = "SSASCubeDB";
clrOlapCube = "SSASCube";
mdxCaculationScript = @"
Calculate;
Create Member CurrentCube.[Measures].[Total Sales] as
[Measures].[门诊人次]*2+ [Measures].[门急诊人次];";
IEnumerator <BuildSSASCube> mdxIE = CheckMDXSyntax(mdxCaculationScript).GetEnumerator();
while (mdxIE.MoveNext())
{
if (mdxIE.Current.Status == 1)
{
String sqlConnectionString = @"Data Source=.\SQL2012;Initial Catalog=SSASAutomationDB;Integrated Security=SSPI";
DB_SQLHELPER_SQLServer sqlHelper = new DB_SQLHELPER_SQLServer(sqlConnectionString);
String cube_server_name = clrOlapServer;// @".\SQL2012";
String cubeDBName = clrOlapCubeDB;
String cubeName = clrOlapCube;
Server cubeServer = AS_API_HELPER.CREATE_OLAP_CONNECTION(cube_server_name, null);
AS_API_CUBE_CREATOR cubeCreator = new AS_API_CUBE_CREATOR();
Database CubeDB = cubeServer.Databases.FindByName(cubeDBName);
Cube CubeObg = CubeDB.Cubes.FindByName(cubeName);
cubeCreator.UPDATE_CUBE_COMMAND(sqlHelper, cubeServer, CubeDB, CubeObg, mdxCaculationScript);
CubeDB.Update(Microsoft.AnalysisServices.UpdateOptions.ExpandFull);
}
break;
}
}
catch (Exception ex)
{
buildCube = new BuildSSASCube[]
{
new BuildSSASCube {
BuildSSASCubeMessage = "failed to update cube mdx, message:" + ex.Message.ToString(), Status = 0
}
};
}
return(buildCube);
}
public IEnumerable <BuildSSASCube> BuildSSASCube()
{
try
{
String SSASConfigurationPath = AppDomain.CurrentDomain.BaseDirectory + @"App_Data";
CONFIGURATION_HELPER.BASIC_CONFIGURATION_FOLDER = SSASConfigurationPath; // @"E:\2.自己文档\BigData\SSASAutomation\SSASAutomation\MDXHelper.SSASAutomation";
String sqlConnectionString = CONFIGURATION_HELPER.GET_METADATA_PROPERTY("dw_connection_string"); // @"Data Source=.\SQL2012;Initial Catalog=CLB_Hospital_DW;Integrated Security=SSPI";
String cube_server_name = CONFIGURATION_HELPER.GET_METADATA_PROPERTY("clr.olap.server"); // @".\SQL2012";
String cubeDBName = CONFIGURATION_HELPER.GET_METADATA_PROPERTY("clr.olap.cubedb");
String cubeName = CONFIGURATION_HELPER.GET_METADATA_PROPERTY("clr.olap.cube");
String dwServer = CONFIGURATION_HELPER.GET_METADATA_PROPERTY("clr.db.server");
String dwDB = CONFIGURATION_HELPER.GET_METADATA_PROPERTY("clr.db.dw");
DB_SQLHELPER_SQLServer sqlHelper = new DB_SQLHELPER_SQLServer(sqlConnectionString);
AS_METADATA_SQLServer asMeta = new AS_METADATA_SQLServer();
IDbConnection db_connection = sqlHelper.GET_DATABASE_CONNECTION();
String oleDBConnectionString = sqlConnectionString;
IDbConnection oledb_connection = sqlHelper.GET_OLEDB_CONNECTION(oleDBConnectionString);
String DWConnectionString = @"Provider=SQLNCLI11.1;Data Source=" + dwServer + ";Integrated Security=SSPI;Initial Catalog=" + dwDB;
//@"Provider=SQLNCLI11.1;Data Source=.\SQL2012;Integrated Security=SSPI;Initial Catalog=CLB_Hospital_DW";
Server cubeServer = AS_API_HELPER.CREATE_OLAP_CONNECTION(cube_server_name, null);
AS_API_CUBE_CREATOR cubeCreator = new AS_API_CUBE_CREATOR();
cubeCreator.CREATE_SSAS_BASE_CUBE(sqlHelper, cubeServer, cubeDBName, cubeName, DWConnectionString);
cubeServer.Disconnect();
cubeServer = AS_API_HELPER.CREATE_OLAP_CONNECTION(cube_server_name, null);
IDbConnection a = new System.Data.OleDb.OleDbConnection();
cubeCreator.CREATE_CUBE_COMMON(sqlHelper, oledb_connection, asMeta, cubeServer, cubeDBName, cubeName, 0);
cubeServer.Disconnect();
}
catch (Exception ex)
{
buildCube = new BuildSSASCube[]
{
new BuildSSASCube {
BuildSSASCubeMessage = "failed to build ssas cube, message:" + ex.Message.ToString(), Status = 0
}
};
}
return(buildCube);
}