The data warehousing theory is out of scope here, but we could introduce some very basic concepts.
Most of the times we need to prepare data for a reporting/analysis tool without accessing directly the production databases:
- We don’t want to load the server interfering with every day user’s activities
- Normally source data is quite normalized (not ready for efficient reporting queries)
- We may need to do some cleansing and checks
- Many times we have to combine data from different sources (ERP, CRM…) and enrich them
MKST will enable you to transfer data from different sources (local or linked databases) to a data warehouse.
The MKST data warehouse flow consists of a process organized in three phases.
- Upload: transfer data from multiple sources to MKST staging
- Validation: check the uploaded data
- Import: transfer data from the staging area to a DWH
After configuring the staging the dbo.full_process stored procedure will do the job and the dbo.last_process_log will provide you the results.
MKST comes with four sql schemas.
- dbo: basic tables and procedures you should not modify
- ext: tables and procedure you may need to extend or add
- stg: tables that will serve as a destination for the original data
- dwh: views that will serve as a source for the DWH tables
You can download the script and the manual MKST.zip