Business Case

Scenario 1: Category dimension has a property called CURRTIME. This property should be a member of time id.

Scenario 2: Entity dimension has a property called ENTITY_TYPE. The value of this property should be MFG, SRV or <NULL>

Prerequisites

1. User should have admin access to the BPC MS web server
2. Knowledge of C#.net
3. Knowledge of T-SQL
4. Category dimension with property CURRTIME
5. Entity dimension with property ENTITY_TYPE

Process

Step 1

Create a C# class library

Step 2

Add reference to C:\PC_MS\Websrvr\bin\BPCServerExtensionSDK.dll

Step 3

Add the following code

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using SAP.BPC.Services.Application;

using System.Data.SqlClient;

using System.Configuration;

using System.Collections;

using System.Collections.Specialized;

using System.Diagnostics;

namespace nsValidation

{

    public class clsValidation : BPCServerExtension

    {

        public override ServerExtensionResult PostDimensionProcess(string env_name, string dimension_name,           BPCServerExtension.DimensionProcessOption process_option, BPCServerExtension.WorksheetOption worksheet_option)

        {

            ServerExtensionResult ret = new ServerExtensionResult();

            try

            {

                string ConnString = “Data Source=(local);Initial Catalog=AppServer; Integrated Security=true”;

                string QryString = “exec [dbo].[USP_DIM_VALIDATION] ‘” + env_name + “‘,'” + dimension_name + “‘;”;

                SqlConnection connection = new SqlConnection(ConnString);

                SqlCommand command = new SqlCommand(QryString, connection);

                connection.Open();

                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows == true)

                {

                     ret.messages.Add(“The following members were wrong. Please correct to process dimension:”);

                    while (reader.Read())

                    {

                          ret.messages.Add(reader[1].ToString() + “-” + reader[0].ToString());

                    }

                    reader.Close();

                    ret.Success = false;

                }

                else

                {

                    reader.Close();

                    ret.Success = true;

                }

                connection.Close();

                return ret;

            }

            catch (Exception ex)

            {

                ret.Success = false;

                ret.SetExcetpion = ex;

                return ret;

            }

        }

    }

}

Step 4

Build the DLL in visual studio

Step 5

Copy the DLL of the project to <appserver>\Webfolders\AdminTemplates\ServerExtension. Create the folder if it is not available

Step 6

Create the following procedure

USE [AppServer]

GO

Create Procedure [dbo].[USP_DIM_VALIDATION]

(

@ENV_ID VARCHAR(20),

@DIMENSION VARCHAR(20)

)

As

Begin

        IF (@ENV_ID=‘EnvironmentShell’ AND @DIMENSION =‘CATEGORY’)

        BEGIN

                SELECT ID,EXCEPTION

                FROM

                (

SELECT ID,‘Error: Invalid entry in CURRTIME property for ‘ EXCEPTION FROM EnvironmentShell.DBO.mbrCATEGORY  WITH (NOLOCK)

WHERE CURRTIME  NOT IN (SELECT ID FROM EnvironmentShell.DBO.mbrTIME WITEH (NOLOCK))

                ) TBL_EXCEPTION

        END

        ELSE IF (@ENV_ID=‘EnvironmentShell’ AND @DIMENSION =‘Entity’)

        BEGIN

                SELECT ID,EXCEPTION

                FROM

                (

SELECT ID,‘Error: Invalid entry in ENTITY_TYPE property for ‘ EXCEPTION FROM EnvironmentShell.DBO.mbrEntity  WITH (NOLOCK)

                        WHERE ENTITY_TYPE NOT IN (‘MFG’,‘SVR’,”)

                ) TBL_EXCEPTION

        END

END

Step 7

Restart IIS in the web services using the following command

IISRESET /RESTART

Step 8

Process dimension to check the validation process using BPC MS Admin Console

Reference Link

SAP Help Document

http://help.sap.com/bopacms100  >> Server Extension User’s Guide (link)

Video