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

/wp-content/uploads/2013/11/image001_318077.png

/wp-content/uploads/2013/11/image002_318078.png

Reference Link

SAP Help Document

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

Video

To report this post you need to login first.

4 Comments

You must be Logged on to comment or reply to a post.

  1. Marcella Zucca

    Do you knows if is possible also modify dimension (for example create news member or new value for existing attribute) in BPC 10 MS by API extension?

    Thanks in advance

    Marcella

    (0) 
  2. Scorpio b

    Excellent article and video.I really liked it and thanks for taking time and effort in making this tip.

    Few questions

    1) Why do we have to override the PostDimensionProcess. Can we do that in PreDimensionProcess so that processing time is avoided for better response time

    2)The SDK states it is applicable to BPC MS 10. But in the video you are using the admin console might be from 7.X version, bit curious to know if these server extension SDK is available for 7.5 as well?

    (0) 
    1. Badrish Shriniwas Post author

      Thanks for your comment, keeps us going…

      1) Certainly using PreDimensionProcess would be wiser but unfortunately I wanted to use the mbr<dimension> table to validate. I don’t think PreDimensionProcess will have that table populated (not sure if I tested this fact). Great observation though..

      2) I am certain that this is only applicable to BPC MS 10. Admin console is not much different in BPC MS 10 from that of 7.x version.

      Badrish Shriniwas

      (0) 

Leave a Reply