Example: Easy Integration

This topic details the principles of how the most easy integration can be configured using pure SQL. In reality, this is not a best practice way of building integration, but it indicates the how easy it can be to link Bizview to other databases and data sources.

The objective of the example code is to take the chart of account data in an ERP-system and make that available within Bizview for reporting and for the base of budgeting and forecasting.

In the setup of Bizview, the business super-user has created a client (company) called "DEMO10". Within this client, the super-user has defined an object/dimension called "ACCOUNT" hold the chart of accounts.

Code example for integrating the chart of accounts:

---1. Delete any existing chart of account data in stg.object_values for this company

deletefromstg.object_valueswhereclient_id='DEMO10'

---2. Get the chart of accounts from the ERP-System and insert to the the staging-table

insertinto[stg].[object_values]

          ([client_id]

          ,[obj_id]

          ,[objval_id]

          ,[descr]

          --,[valid_from_isodate]

          --,[valid_to_isodate]

          --,[active]

          --,[created]

            )

select

'DEMO10'asclient_id,

'ACCOUNT'asobj_id,

AccountIDasobjval_id,

AccountDescasdescr

--,

--1 as active

from[172.18.0.20\SQL2008DEV].TheERPSystem.dbo.Account

whereActive= 1

---3. Execute the standard procedure that loads data from the staging table to the real Bizview table

EXEC        [stg].[p_update_object_values]

         @client_id='DEMO10',

         @job_id=NULL,

         @step_no=NULL,

         @detail_log=NULL

First, the target staging-table must be cleaned from any previous load of data. Bizview never deletes any data from any staging table. It is the responsibility of the data integrator to make sure that the target staging table is empty before data is loaded into the staging table.

Caution: In the example above, there is a 'delete' with a where clause for the specific client we are loading data for. This is very important in a multi-client scenario where the loading of data for multiple clients can take place simultaneous. If instead this would be a 'one client' installation, a faster way to clean up data from the staging table would be to execute a truncate table command like this:

---Truncate and delete all data in the staging table

truncatetablestg.object_values

And then execute the insert to statement.

-------

The second step after the clean-up of the staging table is to load the chart of account from the source system to the staging table. This is in this example executed by a normal insert-select statement where the data is picked up from the Account-table in the ERP-System via a SQL-server linked server. Please note that the not logically mandatory fields have been left empty and are populated automatically by the loading procedure in the next step.

The third step is to execute the procedure to load data from the staging table into the final table (or tables) within Bizview. This procedure is a standard procedure with standard logic independent of from what source system the data has its origin. What happens when this procedure is executed? In principle, this is what happens:

• The data in the staging table is validated. I.e. the procedure checks the data and reports any error to the related stgerr table, in this example stgerr. object_values.

• The error free records are moved to a temporary table from where the insert and update of the target Bizview table will take place.

• The target Bizview table (in this example the object_value table) is updated with descriptions, valid dates etc (update)

• Any new account not previously loaded into Bizview is inserted into the target object_values table.

The final result is shown inside Bizview. As you can see, the first account (1040) was updated during the load 25/1 2014, and the DIUSER is indicating that the record has been updated by an automatic load of data via the staging table (not updated manually by a human user).

But let us go back to the execution of the procedure for loading the data:

---3. Execute the standard procedure that loads data from the staging table to the real Bizview table

EXEC        [stg].[p_update_object_values]

         @client_id='DEMO10',

         @job_id=NULL,

         @step_no=NULL,

         @detail_log=NULL

In the above example, there are three parameters that we did not populate: job_id, step_no, and detail_log. Below is a description of how they can be used.

@detail_log = 1 would tell the procedure to run with detailed logging to the dis.job_detail_log table. This would result in a very detailed log of what steps has been executed within the procedure and at what time. This can be used to analyse any performance issues during loading of data. If the procedure is executing dynamic SQL you can also in the executed SQL saved in the exec_sql field.

The @job_id and the @step_no parameters are normally used when this procedure is executed within the scope of a Bizview processing services job with job steps. These parameters are sent to from processing services down to the procedure to enable the logging of the job and its steps related to a job.

It's also possible to create a job outside of processing services and execute multiple load steps within the scope of a job.