Consultant BI - Automation & Processes

Project Description

 

The service is requested as part of the project Henkel NEW BI Foundation. The project has the purpose automate processes in the environment and within the NEW BI Foundation.

 

Task Description

 

The scope of services includes the following tasks, which are independently performed by the external contractor:

 

• Automation of Environment, Cloud Services and Software provisioning

• Implementation of automation scripts for built, deploy and configure, typical technology is PowerShell / Terraform or comparable

• Technical consultation in requests regarding provisioning, technology access and how to apply technology to solve project needs

• Creating database connections, configuration of windows servers and Azure services in order to enable data loads or report implementation

• Detailed documentation of Data load server and hand-over into standard maintenance

 

Used tool stack: Azure Cloud services, team foundation server (Azure Dev Ops), Azure SQL, Azure SQL DWH, SQL Server Management Studio, Microsoft SQL Server, Azure Data Factory, Azure Data Lake, Azure Analysis Service, Power BI Desktop, Windows server, GitHub, Power Shell

 

4. Work Packages

1. Dynamic Ingest: DataAsset.Processing.ADF
Establish a second fully automated ingest service: DataAsset.Processing.ADF including:

· setup, preparation and deployment existing DataLoad Servers to support this component (ADF runtime + X)

· source drivers

· system IDs / service principals required

· Setup ADF instances for each environment (DEV01/QUA/ PRD)

· create access rights auth config

· Review, test, potentially extend Meta Database for JobGroup, stored procedures and all necessary structures

· Repository and Build pipeline for ADF artifacts

· Extension of EDW2 pipeline

· creation for ADF jobs in processing DB

· deploy ADF artifacts

· Documentation and KT to maintenance team

 

2. Automated processing relation database Phases 3 and 4

· 3: deploy version from phase 2 via pipeline incl. update of processing DB and initial processing

· 3: design ADF pipeline as generic or generated

· 4: implement ADF according to design in repository with build and CI testing

· 4: deploy ADF artifacts accordingly

· 4: enhance pipeline to deploy accordingly

·  4: delta KT and retro fit of existing infosets

 

3. Dynamic Processing: Information Sets Data Flow (Power BI)

· Automated processing relation database Phases 3 and 4

· 3: deploy version from phase 2 via pipeline incl. update of processing DB and initial processing

· 3: design data flow object to be generated or generic including release process

· 4: enhance pipeline to deploy accordingly

· 4: delta KT and retro fit of existing infosets

 

4. EDW Information Layer: enhance meta data generation

· Meta data needs to be collected in the information layer of EDW in order to subscribe / request the data in your information set

· design the storage of meta data tags, e.g. as code in the comments

· specify the initial set of Meta data required view and column level

·  create build step in EDW2 pipeline, fail in case minimal requirements are not satisfied

·  backfill for existing views (retro fit)

·  create deployment step in EDW2 pipeline to deploy to meta db

·  create necessary integration steps to display this information in interim data catalog

5. EDW: Azure Synapse - separate clusters

· Design and build how EDW2 clusters will be used (private synapse feature to come)

· one cluster for dataloading

· one per application cluster:

financial

sales

supply chain

exports

· run performance tests

· separate and automated scaling of the clusters

· implement workload isolation feature