Add Dimension Members with SQL Rule

Author: Joyce Zhou

Introduction

Business users may need to manage Dimension Members inside APPS. Instead of having them go through MODELER, we can provide SQL Rules to help manage Dimensions on their own.

In this article, we will create an Add SKU Rule together. This will allow Advanced Contributors to add new SKUs through Dashboard Rule Components. Rules to update or remove members can be developed in a similar way.

Tip: If you haven’t created a SQL Rule before, refer to the article How to Setup a SQL Rule for more information.

Example

Step 1. Create a new SQL Rule in the target Model. Then go to the PARAMETER tab in the Rule editor to define the parameters that you need to collect from the Rule Component.

For this Rule we will need to collect the Dimension Member name, along with any necessary attribute values.

Step 2. Navigate to the PROCESS tab in the Rule editor. Add a Process item to process the Dimension and hierarchy that you need to manage. Since we are going to add a new member to the Dimension, we select the type as Process Add.

Step 3. Next, move on to the DEFINITION tab in the Rule editor and enter a SQL query to read filter selection values if there are any, and call the stored procedure.

EXEC [dbo].[USR_ADD_SKU]
    @SKUName = @SKUName,
    @Brand = @Brand;

Step 4. In SQL Server Management Studio, create the stored procedure that you called in Step 3. A sample stored procedure is shown below. It may need to be altered to fit your requirements.

CREATE PROCEDURE [dbo].[USR_ADD_SKU]
 
    -- Add the parameters for the stored procedure here
 
    @SKUName nvarchar(200),
 
    @Brand tLabelSet READONLY

AS

BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;



    DECLARE @BrandName NVARCHAR(200);

    SET @BrandName = (SELECT TOP 1 Label FROM @Brand)



    IF @SKUName NOT IN (SELECT MemberLabel FROM D_Product)


        AND @BrandName IN (SELECT Brand FROM D_Product)

    BEGIN



        INSERT INTO [dbo].[D_Product]

            ([MemberLabel],

            [MemberName],

            [Input],

            [Annotate],

            [Brand],

            [Type])

        VALUES

            (@SKUName,

            @SKUName,

            1,

            0,

            @BrandName,

            N'SKU')



        DECLARE @ProductID SMALLINT;

        SET @ProductID = (SELECT MemberID FROM D_Product WHERE MemberLabel = @SKUName)



        INSERT INTO [dbo].[H_Product_Product]

            ([MemberId],

            [ParentMemberId])

        VALUES

            (@ProductID,

            @ProductID)



    END



END

In most cases, the stored procedure you create will be similar to this one. You will need to:

1. Verify user input is valid.
2. Insert into D_ table.
3. Insert into H_ table.
4. (Optional) Insert into DO_ and HO_ tables.

Step 5. Link the Rule with the Form in ADMINISTRATOR and add a Rule Component in the Dashboard, and then Advanced Contributors will be able to use it in their APPs.