Planning Data
Bizview has a standardized interface (staging table stg.data_cells) for integration of planning data.
This topic contains the following sections:
stg.data_cells
This is the table where planning data should be inserted from the pre-system.
A detailed description of the table and its columns is not specified here (see separate document for detailed description of stg.data_cells).
Inserting Data into Bizview Planning Data Tables
Bizview has a standard procedure which handles import of planning data, from the staging table to Bizview planning data tables. Tables that are updated in Bizview are dat.data_cells and dat.data_dimensions. The procedure that handles import of planning data is stg.p_update_data_cells. Functionality in this procedure is specified below.
Parameters to stg.p_update_data_cells
@client_id: Client_Id (mandatory)
@job_id: Automatically filled in by calling step in Bizview processing services
@step_no: Automatically filled in by calling step in Bizview processing services
@detail_log: 0 = No detail logging, 1 = Details will be logged to log table in Bizview
@delete_method: Should be 0, 1, 2, 3, or 9. (See detailed explanations below)
@del_obj_id_1: See section below.
@del_objval_id_1: See section below.
@del_obj_id_2: See section below.
@del_objval_id_2: See section below.
@del_obj_id_3: See section below.
@del_objval_id_3: See section below.
Parameters to stg.p_update_data_cells
@client_id: Client_Id (mandatory)
@job_id: Automatically filled in by calling step in Bizview processing services
@step_no: Automatically filled in by calling step in Bizview processing services
@detail_log: 0 = No detail logging, 1 = Details will be logged to log table in Bizview
@delete_method: Should be 0, 1, 2, 3, or 9. (See detailed explanations below)
@del_obj_id_1: See section below.
@del_objval_id_1: See section below.
@del_obj_id_2: See section below.
@del_objval_id_2: See section below.
@del_obj_id_3: See section below.
@del_objval_id_3: See section below.
Delete method 0 (version and value type)
Delete will be done by version and value types. Only the values that exists in the stg.data_cells will be deleted.
Example (pseudo code):
delete from <planning data table>
where version = <exists in staging table> and
valuetype = <exists in staging table>
Delete Method 1 (version and value type + time units min/max values)
This is the same as method 0 but with the extension that time values within the stage data will also be added to the filter for deletion.
Example (pseudo code):
delete from <planning data table>
where version = <exists in staging table> and
valuetype = <exists in staging table> and
timeunit = staging table.time_unit and
timevalue >= min(staging table.timevalue) and
timevalue <= max(staging table.timevalue)
Delete Method 2 (version and value type + object filters)
This is the same as method 0, but with the extension that object values will also be added to the filter for deletion.
Example (pseudo code):
@del_obj_id_1 DEPARTMENT
@del_objval_id_1 1*
delete from <planning data table>
where version = <exists in staging table> and
valuetype = <exists in staging table> and
object = DEPARTMENT and
objectvalue like '1%'
Note: You can use the full SQL Smart Syntax for object value filters, e.g. 1*, 100..200, 100#200#300 and so on.
Delete method 3 (version and value type + object filters)
Delete method 3 is the combination of delete methods 0, 1 and 2.
Example (pseudo code):
delete from <planning data table>
where /* method 0 */
version = <exists in staging table> and
valuetype = <exists in staging table> and
/* method 1 */
timeunit = staging table.time_unit and
timevalue >= min(staging table.timevalue) and
timevalue <= max(staging table.timevalue) and
/* method 2 */
object = DEPARTMENT and
objectvalue in('100', '200', '300')
Delete method 9 (no automatic delete filters)
Delete method 9 will disable deletion of existing planning data.
You can use this method in special cases when you want to control the deletion of existing data through a custom procedure.
Example: Integrate Planning Data
Integration of planning data can either be done by a simple TSQL script, preferably when this is a one-time load of planning data into the Bizview database.
Or, if loading planning data from external sources into Bizview is a recurring activity, then this would be best suited by setting up a Bizview Processing Service job.
Example: TSQL script (one-time load)
/* load data to stage */
DELETE from [stg].[data_cells] where client_id = 'BIZCOMPANY'
INSERT INTO [stg].[data_cells]
([client_id],[version_id],[valtype_id],[currcode_id]
,[timeunit_id],[timeval_id]
,[cellvalue]
,[comment]
,[obj_id_1],[objval_id_1]
,[obj_id_2],[objval_id_2]
,[obj_id_3],[objval_id_3]
,[obj_id_4],[objval_id_4]
,[obj_id_5],[objval_id_5]
,[obj_id_6],[objval_id_6]
,[obj_id_7],[objval_id_7]
,[obj_id_8],[objval_id_8]
,[obj_id_9],[objval_id_9]
,[obj_id_10],[objval_id_10]
,[created],[job_id]
,[dim_cluster_id],[attribgroup])
SELECT
'BIZCOMPANY', 'BUD2014', 'AMOUNT', 'SEK',
'ISOMONTH', period,
budgetvalue,
text,
'ACCOUNT', account,
'DEPT', department,
null, null,
null, null,
null, null,
null, null,
null, null,
null, null,
null, null,
null, null,
getdate(), null,
null, null
from erpsystem.dbo.Budget
where period between '201401' and '201412'
/* update data to Bizview planning tables */
exec [stg].[p_update_data_cells]
@client_id = 'BIZCOMPANY',
@delete_method = 1,
@del_obj_id_1 = null,
@del_objval_id_1 = null,
@del_obj_id_2 = null,
@del_objval_id_2 = null,
@del_obj_id_3 = null,
@del_objval_id_3 = null
Next step:Example: Easy Integration