Visit 'LiveWorld24.tk' for Live world..............

OLAP

Online analytical processing (OLAP) allows the user to access aggregated and organized data from business data sources, such as data warehouses, in a multidimensional structure called a cube. Microsoft provides tools and features for OLAP that user can use to design, deploy, and maintain cubes and other supporting objects.

Cubes in a data warehouse are stored in three different modes. A relational storage model is called Relational Online Analytical Processing mode or ROLAP, while a Multidimensional Online Analytical processing mode is called MOLAP. When dimensions are stored in a combination of the two modes then it is known as Hybrid Online Analytical Processing mode or HOLAP.

http://www.sql-server-performance.com/articles/biz/intro_ssas_p1.aspx

1) functions are used for computations where as procedures

can be used for performing business logic

2) functions MUST return a value, procedures need not be.

4) function parameters are always IN, no OUT is possible

2. EXEC command can't be used inside a Function where it

can be used inside an sproc.

a. A FUNCTION is always returns a value using the return

statement. A PROCEDURE may return one or more values

through parameters or may not return at all.

b. Functions are normally used for computations where as

procedures are normally used for executing business logic.

c. A Function returns 1 value only. Procedure can return

multiple values (max 1024).

d. Stored procedure returns always integer value by default

zero. Whereas function returns type could be scalar or

table or table values

e. Stored procedure is precompiled execution plan where as

functions are not.

f. A function can call directly by SQL statement like

select func_name from dual while procedure cannot.

g.Stored procedure has the security and reduces the network

traffic and also we can call stored procedure in any no. of

applications at a time.

h. A Function can be used in the SQL Queries while a

procedure cannot be used in SQL queries .that cause a major

difference b/w function and procedures.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using net.ERP.Constants;

using System.Data;

using System.Data.Sql;

using System.Data.SqlClient;

using System.Windows.Forms;

namespace net.ERP.DatabaseSchema.DBExistanceChecking

{

    class DatabaseExistanceChecking:CommonConstants

    {

        public bool mpubbIsDatabaseExists(string pstrServerName)

        {

            SqlConnection lprisqlconnSQLConnection = new SqlConnection("Server=" + pstrServerName + ";Integrated security=SSPI;database=master");

            try

            {

                lprisqlconnSQLConnection.Open();

                SqlCommand lsqlcommSQLCommand = lprisqlconnSQLConnection.CreateCommand();

                lsqlcommSQLCommand.CommandText = "SELECT COUNT(*) AS TOTAL_RECORDS FROM master.dbo.sysdatabases WHERE NAME = 'NET_ERP_REMS'";

                SqlDataReader lsqldrSqlDataReader = lsqlcommSQLCommand.ExecuteReader();

                if (lsqldrSqlDataReader.Read())

                {

                    int liTotalDatabases = (int)lsqldrSqlDataReader["TOTAL_RECORDS"];

                    lsqldrSqlDataReader.Close();

                    if (liTotalDatabases > 0)

                    {

                        return true;

                    }

                    else

                    {

                        return false;

                    }

                }

                return true;

            }

            catch (Exception pexpDBExistance)

            {

                MessageBox.Show(pexpDBExistance.ToString(), gmpubcstrUserConfirmation, MessageBoxButtons.OK, MessageBoxIcon.Information);

                return false;

            }

            finally

            {

                if (lprisqlconnSQLConnection.State == ConnectionState.Open)

                {

                    lprisqlconnSQLConnection.Close();

                }

            }

        }

    }

}

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data;

using System.Data.Sql;

using System.Data.SqlClient;

using System.Windows.Forms;

using net.ERP.Constants;

namespace net.ERP.DatabaseSchema.DatabaseCreationForServer

{

    class BlankDatabaseCreation:CommonConstants

    {

        public void mpubvCreateDatabase(string pstrServerName)

        {

            SqlConnection lprisqlconnSQLConnection = new SqlConnection("Server=" + pstrServerName + ";Integrated security=SSPI;database=master");

            try

            {

                lprisqlconnSQLConnection.Open();

                SqlCommand lsqlcommSqlCommand = new SqlCommand("CREATE DATABASE " + gmpubcstrDatabaseName, lprisqlconnSQLConnection);

                lsqlcommSqlCommand.ExecuteNonQuery();

            }

            catch (Exception pexpDBCreation)

            {

                MessageBox.Show(pexpDBCreation.ToString(), CommonConstants.gmpubcstrUserConfirmation, MessageBoxButtons.OK, MessageBoxIcon.Information);

            }

            finally

            {

                if (lprisqlconnSQLConnection.State == ConnectionState.Open)

                {

                    lprisqlconnSQLConnection.Close();

                }

            }

        }

    }

}

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using net.ERP.Constants;

using System.Windows.Forms;

using System.Data.SqlClient;

namespace net.ERP.Utility

{

    class DatabaseConnector:CommonConstants

    {

        private string mpristrDBConnectionString = null;

        public static SqlConnection gmpubstasqlconnSQLConnection = null;

        public static SqlConnection gmpubstasqlconnSQLConnectionTemp = null;

        public void mpubvConnectDatabase(string pstrServerName)

        {

            try

            {

                mpristrDBConnectionString = "Data Source=" + pstrServerName + ";Initial Catalog=" + gmpubcstrDatabaseName + ";User ID=" + gmpubcstrUserName + ";Password=" + gmpubcstrPassword;

                gmpubstasqlconnSQLConnection = new SqlConnection(mpristrDBConnectionString);

                gmpubstasqlconnSQLConnectionTemp = new SqlConnection(mpristrDBConnectionString);

            }

            catch (Exception pexpDBConnection)

            {

                MessageBox.Show(pexpDBConnection.Message);

            }

            finally

            {

                mpristrDBConnectionString = null;

            }

        }

    }

}