예제 #1
0
        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);
            }
        }
예제 #2
0
        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);
        }
예제 #3
0
        //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);
        }
예제 #5
0
        /// <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);
        }
예제 #6
0
        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);
        }