Manage Dimensions in Planning
By Joyce Zhou - Oct 24, 2014
Business users may have the needs to manage dimension members in the planning process. Instead of having them go through Modeler, we can provide SQL rule solutions so that they can manage dimensions in Planning.
In this article, we will create an Add New Product SKU rule together. This will allow advanced contributor to add new SKUs through Kepion explorer panel. Update or remove member rules can be developed in the similar way, with some code changes in the stored procedure.
If you haven’t created any SQL rules in Kepion before, please refer to How to Setup a SQL Rule for prerequisite information.
Step 1. Create a SQL rule in the model that you would like the rule to reside in.
Step 2. Go to the PARAMETER tab in the rule editor to define the parameters that you need to collect from the Explorer Panel.
For add a member rule, you will need to collect the dimension member name, together with any necessary attribute values.
Step 3. Go 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 new member to the dimension, we select the type as ‘Process Add’.
Step 4. Go to the Definition tab in the rule editor and enter the SQL query to read filter selection values if there is any, and call the stored procedure.
Step 5. In SQL Server Management Studio, create the stored procedure that you called in Step 3. A sample stored procedure is shown below.
In most case, the stored procedure you created will be similar to this one. You will need to
- Verify user input is valid,
- Insert into D_ table,
- Insert into H_ table, and
- Insert into DO_ and HO_ tables (optional).
Step 6. Link the rule with the form in Administrator, then advanced contributors will be able to see and use it in Planning.