Custom Validation of Dimension Properties in BPC MS 10
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>
- 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
Create a C# class library
Add reference to C:\PC_MS\Websrvr\bin\BPCServerExtensionSDK.dll
Add the following code
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();
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);
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows == true)
ret.messages.Add(“The following members were wrong. Please correct to process dimension:”);
ret.messages.Add(reader.ToString() + “-” + reader.ToString());
ret.Success = false;
ret.Success = true;
catch (Exception ex)
ret.Success = false;
ret.SetExcetpion = ex;
Build the DLL in visual studio
Copy the DLL of the project to <appserver>\Webfolders\AdminTemplates\ServerExtension. Create the folder if it is not available
Create the following procedure
Create Procedure [dbo].[USP_DIM_VALIDATION]
IF (@ENV_ID=‘EnvironmentShell’ AND @DIMENSION =‘CATEGORY’)
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))
ELSE IF (@ENV_ID=‘EnvironmentShell’ AND @DIMENSION =‘Entity’)
SELECT ID,‘Error: Invalid entry in ENTITY_TYPE property for ‘ EXCEPTION FROM EnvironmentShell.DBO.mbrEntity WITH (NOLOCK)
WHERE ENTITY_TYPE NOT IN (‘MFG’,‘SVR’,”)
Restart IIS in the web services using the following command
Process dimension to check the validation process using BPC MS Admin Console
SAP Help Document
http://help.sap.com/bopacms100 >> Server Extension User’s Guide (link)
Very useful article!!!
Thanks a lot!
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
Excellent article and video.I really liked it and thanks for taking time and effort in making this tip.
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?
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.