Add Dimension Members with SQL Rule

Author: Joyce Zhou

Introduction

Business users may need to manage Dimension Members while using an App. Instead of having them go through Modeler, we can set up SQL rules for them to 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.

Create a SQL Rule and Set Parameters

1. Create a new SQL Rule in the target Model.

2. Go to Parameter to define the parameters the Rule Component will collect.

3. Click Add to add a parameter.

4. Configure the SKU Name parameter with String as the Type: 

5. Click OK when done. 

6. Repeat steps 3-5 for the Brand parameter, but set the Type to Member Picker.

Add Process Item

1. Go to Process and select Add.

2. Configure the process item to target the Dimension and hierarchy. Since we are going to add a new member to the Dimension, we set the Type to Process Add.

3. Click OK. 

Define the Rule

1. Go to Definition 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;

2. Open SQL Server Management Studio and create the stored procedure that you called in the previous step.

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.

Link the Rule with Form

Link the Rule with the Form in Administrator and add a Rule Component to the target Dashboard page. Advanced Contributors should be able to use the new component in their App.