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
- User should have admin access to the BPC MS web server
- Knowledge of C#.net
- Knowledge of T-SQL
- Category dimension with property CURRTIME
- 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
4 Comments
