Integration Strategies
Bizview can report on data from many different data sources. A report, dashboard, or similar show data from a datawarehouse, an ERP system, or from some other datasource.
Bizview also needs to store some data within the Bizview application database. The general principle is that this data is related to the functionality of Bizview application. The Bizview application must for example know what users should be able to login, and their access rights. The system must have information about any object (dimension) that an end-user should be able to do planning or forecasting on.
Some of this data is normally configured manually within Bizview. For example, what clients (companies) that should be stored within one application database, and what reports exist in a database.
Some data is normally loaded from some other external data source into the Bizview application database. The normal data that will be retrieved on a continuous basis from other data sources are:
Dimension members (Bizview: object values), and their attributes and structures, i.e. for example products, accounts, cost centers, customer groups, and their attributes and structures
Users and their access rights
Planning data from other data sources (not implemented in 5.2)
This topic contains the following sections:
Two-Step Integration Approach
Bizview implements a two-step approach to data integration. This is a common best practice methodology used in many data warehouse scenarios. The idea is to build a loosely coupled integration with clear responsibility.
Data from the source system is extracted, transformed and finally loaded into special Bizview staging-tables within the Bizview application database. These tables are located in the .stg-schema. These tables are the exposed interface that data integrators should use to push data into Bizview. There is a very clear responsibility for the data integrator to first clean out any old data in the staging-tables, and then fill these tables with new data.
Secondly, it is Bizview and, in particular, the role of Bizview Processing Services to take the data in the staging tables, check and validate this data, report any error, and finally move this data into the appropriate Bizview tables.
The data added to the staging-tables can be added using different technologies. The most common and maybe easiest to use is to write direct database-to-database queries to get data from some other database (above 'Direct SQL' and linked server). This is normally executed using stored procedures within the Bizview database (see note below). But any other ETL-tool, such as Microsoft SQL Server Integration services (SSIS), or any other technology could be used to retrieve data from some source system and add the data to the Bizview staging tables.
There are several advantages with this integration model:
There is a clear-cut interface with clear responsibilities. Populating the staging tables with correct data in the correct format is the responsibility of the data integrator and his or her technologies. And bringing the data into the final tables in Bizview is the role of standardized functionality within the Bizview application database part of the packaged application Bizview.
The data can be checked and validated by Bizview prior to being loaded into the final tables in Bizview. Any records with errors are logged and stored in the .stgerr tables.
The business logic needed to build a specific 'adapter' another system is very limited. It is only the logic to clear and fill the staging tables that will be included in each adapter. The second stage move from the staging tables into the application database is not adapter specific.
We can more easy change the internal Bizview table structure of the final application tables without breaking the externally focused staging tables. If we for example would like to split one of the Bizview internal tables into two different tables, we could do this without changing the original staging table.
Note: Any custom object within the Bizview application database should be created within the .cust schema of the database. This is the only schema that is protected during a Bizview version upgrade. If you are not using the schema you will lose any customized object within the database.
Bizview Processing Services
The role of the Bizview Processing Services ("Bizview PS")is to orchestrate and control this updating process. The Bizview PS can act on its own to coordinate the full process of requesting data from another database (direct SQL, stored procedures, trigger SQL Agents and SSIS-packages), and storing the data in the staging tables. And from there execute the load steps from the staging tables into the final application tables.
The advantages of using Processing Services are:
The end-user can start an integration job manually within Bizview (from version 5.2), and follow the progress
A processing services job can be scheduled to be automatically executed every night, hour or minute etc. And it doesn't require any external tool like SQL Agent to be installed and running
Bizview and Other ETL-Tools
Another alternative is to use some other data integration and orchestration tool such as SSIS to coordinate the full process. This can be achieved due to the fact that the business logic to check and move data from the staging tables into the final tables are stored in standardized Bizview stored procedures within the Bizview application database. Stored procedures that can be executed by for example SSIS as the last step in the integration process.
It is also possible to do a combination where a SSIS package loads the data into the staging-tables, and then kick-off a Processing Services job that further moves the data into the final tables.
Next step:Prerequisites