Skip to content

Latest commit

 

History

History
626 lines (477 loc) · 31.8 KB

README.md

File metadata and controls

626 lines (477 loc) · 31.8 KB
Raw

Formål

Formålet med dette projekt er at lave en generisk ETL-service (extract, transform og load) til up- og download af data til og fra Randers Kommunes KMD Insight løsning.

Løsningen skal udvikles i løbet af efteråret 2023.

Komponenter

ETL-servicen bygger på en række komponenter:

  • Eksterne datakilder som enten er håndholdte eller tilgængelige via et API.
  • En Node-RED-datafordeler hosted på Randers Kommunes Kubernets-platform.
  • En SFTP-server som en del af KMD Insight Custom Data.
  • SAP BusinessObjects (SAP BO).

I tillæg til ETL-servicen, er det også muligt at manuelt at tilføje og hente data. Hvordan denne mulighed skal administreres skal afdækkes i BI-teamet.

Upload

Uploadflowet er illustreret i nedenstående figur:

%%{
  init: {
    'theme': 'base',
    'themeVariables': {
      'primaryColor': '#3c3c3c',
      'primaryTextColor': '#fff',
      'primaryBorderColor': '#3c3c3c',
      'lineColor': '#F8B229',
      'secondaryColor': '#616161',
      'tertiaryColor': '#616161',
      'tertiaryTextColor': '#fff'
    }
  }
}%%

%% Upload af data %%
flowchart LR
    subgraph SG1["Interne og\n eksterne"]
        A[("Datakilde(r)")] 
    end
    subgraph SG2["Randers Kommune"]
        subgraph SG20 [ ]
            style SG20 stroke-dasharray: 0 1  
            subgraph SG21["Kubernetes-platformen"]
            
                B1["
                Automatisk transformering
                via Node-RED til 
                prædefineret syntaks
                "]
            end
        end
        B2["
            Manuel transformering til 
            prædefineret syntaks
        "]
    end
    subgraph SG3["KMD Insight" ]
        C[("SFTP-server")]
        D["
            Transformation til 
            SAP-univers
        "]
        E("
            Adgang til data via
            SAP BusinessObjects
        ")
    end
    A-->B1
    A-->B2
    B1-->C
    B2-->C
    C-->D
    D-->E
Loading

Transformation

For data kan transformeres til et SAP univers, skal det afleveres i en bestemt struktur på SFTP-serveren. Her kan i udgangspunktet vælges mellem to muligheder:

  1. Metadata og data samlet i en fil
  2. Opdelt metadata og data i to separate filer

Fordelen ved mulighed er, at metadata kan genbruges. I begge tilfælde skal data afleveres som UTF8-kodede csv-filer på SFTP-serveren. Der henvises til /docs for yderligere dokumentation.

Download

Download af data forudsætter en Webi-rapport opsat på en tidsplan, som afleverer data via en tidsplan til SFTP-serveren.

Downloadflowet er illustreret i nedenstående figur:

%%{
  init: {
    'theme': 'base',
    'themeVariables': {
      'primaryColor': '#3c3c3c',
      'primaryTextColor': '#fff',
      'primaryBorderColor': '#3c3c3c',
      'lineColor': '#F8B229',
      'secondaryColor': '#616161',
      'tertiaryColor': '#616161',
      'tertiaryTextColor': '#fff'
    }
  }
}%%

%% Upload af data %%
flowchart LR
    subgraph "Randers Kommune" 
        subgraph subgraph_padding_1 [ ]
            style subgraph_padding_1 stroke-dasharray: 0 1     
            subgraph "Kubernetes-platformen"
                B1["
                    Viderebehandling i Node-RED
                    og andre services på
                    Kubernetes-platformen
                "]
            end
        end
        B2["
            Manuel download 
        "]
    end
    subgraph "KMD Insight" 
        C[("SFTP-server")]
        D["
            Datastræk via SAP BO/
            Webi-rapport
            opsat på tidsplan
        "]
        E[("
            SAP univers        
        ")]
    end;
    E-->D
    D-->C
    C-->B1
    C-->B2
Loading

Fil organisering

Der er 3 typer filer med præfikserne Aut_, Data_ og Meta_:

  • Aut_ er autorisationsfiler, csv fil med én kolonne med navnet "Brugernavn" og rækker med DQ-numre på de brugere der skal have adgang.
  • Data_ er datafiler, csv fil hvor første række er kolonnenavne, behøver ikke matche metafil, men datatypen er defineret deri. Resten af rækkerne indeholder data.
  • Meta_ er er metafiler, csv fil med 3 kolonner; "Kolonnenummer", "TekniskNavn" og "EgetNavn". Definere datatyper for datafiler. Detaljer om filtyperne findes i KMD dokumentation her

Derudover er filerne organiseret efter organisatoriske enheder, som er sat som et præfix men efter de ovenfornævnte. Fx. Data_ITresten_af_navnet.csv

Organisatoriske enheder:

  • BS - Børn og Skole
  • HR - Personale og HR
  • IT - IT og Digitalisering
  • SA - Social og Arbejdsmarked
  • SKO - Sundhed, Kultur og Omsorg
  • UMT - Udvikling, Miljø og Teknik
  • ØK - Økonomi

Adgang i web-ut'et

Der er defineret roller i keycloack for hver organisatorisk enhed, samt en admin rolle. Hver rolle begrænser brugeren således at de kun kan oprette, se, redigere og slette filer med præfiks tilsvarende rollen. Admin rollen giver adgang til alle filer samt adgang til filer eksporteret fra SAP BI.

REST API Documentation

Meta and data files

GET /in (get list of files)

Custom headers (optional)

name type description
auth comma seperated string list of org. units (ØK,IT,SKO etc.) allowed to be returned, if it contains 'admin' all files are returned.
Parameters

None

Responses

http code content-type response
200 application/json {"success":true,"files":{"failed":[<filenames>],"imported":[<filenames>],"waiting":[<filenames>]}}
400/401/500 application/json {"success":false,"message":"<error message>"}
GET /in/{filename} (get file)

Custom headers (optional)

name type description
auth comma seperated string list of org. units (ØK,IT,SKO etc.) allowed to be returned, if it contains 'admin' all files are allowed.

Parameters

name type data type description
filename required string must match an existing filename exacly e.g. Data_my_file.csv

Responses

http code content-type response
200 text/csv CSV string
400/401/500 application/json {"success":false,"message":"<error message>"}
POST /in (create with JSON)

Notes

The generated files will be named in the forms: Data_<group>_<name>.csv and Meta_<group>.csv
If the key "name" is omitted, both the data and meta file will have the same name. E.g. "Meta_myGroup.csv" and "Data_myGroup.csv"

Custom headers (optional)

name type description
new-meta string "true" - force to generate new meta file
overwrite string "true" - overwrites files if they already exitsts
auth comma seperated string list of org. units (ØK,IT,SKO etc.) - only allow group with org. unit prefix. If admin then any group value is allowed

Parameters

name type data type description
body required JSON object N/A
Example JSON object
{
    "group":"myGroup",
    "name": "myName", (optional)
    "data":[
        {
            "myColNum": 1,
            "myColText":"some text"
        },
        {
            "myColNum": 2,
            "myColText":"some more text"
        }
    ]
}

Responses

http code content-type response
200 application/json {"success":true,"message":"<N> fil(er) uploaded", "files":[<filenames>]}
400/401/500 application/json {"success":false,"message":"<error message>"}
POST /in (create with formData - one or more CSV files)

Notes

Important! filenames must be percent-encoded (URL encoded) to handle special characters such as æ,ø,å. Javascipt function

Custom headers (optional)

name type description
overwrite string "true" - overwrites files if they already exitsts
new-meta string true/false - force to generate new meta file
auth comma seperated string list of org. units (ØK,IT,SKO etc.) - only allow group with org. unit prefix. If admin then any group value is allowed

Parameters

name type data type description
body required formData formData with one or more field(s) containing CSV files , field names ignored

Responses

http code content-type response
200 application/json {"success":true,"message":"<N> fil(er) uploaded", "files":[<filenames>]}
400/401/500 application/json {"success":false,"message":"<error message>"}
POST /in/{filename} (create with CSV file)

Notes

Filenames must follow the form <Group><name>.csv or <Group>.csv
The generated files will be: Data_<group><name>.csv (or Data_<group>.csv if original filename did not have any underscores) and Meta_<group>.csv

Custom headers (optional)

name type description
overwrite string "true" - overwrites files if they already exitsts
new-meta string true/false - force to generate new meta file
auth comma seperated string list of org. units (ØK,IT,SKO etc.) - only allow group with org. unit prefix. If admin then any group value is allowed

Parameters

name type data type description
filename required string the filename e.g. My_file.csv
body required text/csv a CSV string with data in the form described in KMD Insight documentation

Responses

http code content-type response
200 application/json {"success":true,"message":"<N> fil(er) uploaded", "files":[<filenames>]}
400/401/500 application/json {"success":false,"message":"<error message>"}
PUT /in (create or overwite files)

Custom headers (optional)

name type description
new-meta string true/false - force to generate new meta file
auth comma seperated string list of org. units (ØK,IT,SKO etc.) - only allow group with org. unit prefix. If admin then any group value is allowed

Parameters

name type data type description
body required JSON object N/A
Example JSON object
{
    "group":"myGroup",
    "name": "myName", (optional)
    "data":[
        {
            "myColNum": 1,
            "myColText":"some text"
        },
        {
            "myColNum": 2,
            "myColText":"some more text"
        }
    ]
}

Responses

http code content-type response
200 application/json {"success":true,"message":"<N> fil(er) uploaded", "files":[<filenames>]}
400/401/500 application/json {"success":false,"message":"<error message>"}
PUT /in/{filename} (create or overwite files)

Custom headers (optional)

name type description
new-meta string true/false - force to generate new meta file
auth comma seperated string list of org. units (ØK,IT,SKO etc.) - only allow group with org. unit prefix. If admin then any group value is allowed

Parameters

name type data type description
filename required string must start with 'Meta_' or 'Data_', e.g. Meta_my_file.csv
body required text/csv a CSV string with data in the form described in KMD Insight documentation

Responses

http code content-type response
200 application/json {"success":true,"message":"<N> fil(er) uploaded", "files":[<filenames>]}
400/401/500 application/json {"success":false,"message":"<error message>"}
DELETE /in/{filename} (delete a file)

Notes

When deleting a file, the file will not be deleted from the SFTP server, but it will be overwritten with an empty file.
But files which failed to be imported into SAP BI will really be deleted from the SFTP server.\

Custom headers (optional)

name type description
auth comma seperated string list of org. units (ØK,IT,SKO etc.) - only allow group with org. unit prefix. If admin then any group value is allowed

Parameters

name type data type description
filename required string exact filename, e.g. Meta_my_file.csv

Responses

http code content-type response
200 application/json {"success":true,"message":"<filename> slettet"}
400/401/500 application/json {"success":false,"message":"<error message>"}

Aut files

POST /in/aut (create authentication file with JSON)

Custom headers (optional)

name type description
overwrite string "true" - overwrites files if they already exitsts
auth comma seperated string list of org. units (ØK,IT,SKO etc.) - only allow group with org. unit prefix. If admin then any group value is allowed

Parameters

name type data type description
body required JSON object N/A
Example JSON object
{
    "data_file":"Data_my_file.csv",
    "users":["username1", "username2"]
}

Responses

http code content-type response
200 application/json {"success":true,"message":"<N> fil(er) uploaded", "files":[<filenames>]}
400/401/500 application/json {"success":false,"message":"<error message>"}
POST /in/aut (create authentication file(s) with formData)

Notes

Important! filenames must be percent-encoded (URL encoded) to handle special characters such as æ,ø,å. Javascipt function Filenames must match existing data files.
E.g. if data file Data_my_file.csv exist authentication file Aut_my_file.csv can be uploaded.

Custom headers (optional)

name type description
overwrite string "true" - overwrites files if they already exitsts
auth comma seperated string list of org. units (ØK,IT,SKO etc.) - only allow group with org. unit prefix. If admin then any group value is allowed

Parameters

name type data type description
body required formData formData with one or more field(s) containing CSV files , field names ignored

Responses

http code content-type response
200 application/json {"success":true,"message":"<N> fil(er) uploaded", "files":[<filenames>]}
400/401/500 application/json {"success":false,"message":"<error message>"}
POST /in/aut/{filename} (create authentication file with CSV file))

Notes

Filenames must match existing data files.
E.g. if data file Data_my_file.csv exist authentication file Aut_my_file.csv can be uploaded.

Custom headers (optional)

name type description
overwrite string "true" - overwrites files if they already exitsts
auth comma seperated string list of org. units (ØK,IT,SKO etc.) - only allow group with org. unit prefix. If admin then any group value is allowed

Parameters

name type data type description
filename required string the filename e.g. My_file.csv
body required text/csv a CSV string with data in the form described in KMD Insight documentation

Responses

http code content-type response
200 application/json {"success":true,"message":"<N> fil(er) uploaded", "files":[<filenames>]}
400/401/500 application/json {"success":false,"message":"<error message>"}
PUT /in/aut/{filename} (create/overwrite authentication file)

Custom headers (optional)

name type description
auth comma seperated string list of org. units (ØK,IT,SKO etc.) - only allow group with org. unit prefix. If admin then any group value is allowed

Parameters

name type data type description
filename required string must start with 'Aut_' , e.g. Aut_my_file.csv
body required text/csv a CSV string with data in the form described in KMD Insight documentation

Responses

http code content-type response
200 application/json {"success":true,"message":"<N> fil(er) uploaded", "files":[<filenames>]}
400/401/500 application/json {"success":false,"message":"<error message>"}
DELETE /in/aut/{filename} (delete an Aut file)

Notes

Filename must start with Aut_ When deleting a file, the file will not be deleted from the SFTP server, but it will be overwritten with an empty file.\

Custom headers (optional)

name type description
auth comma seperated string list of org. units (ØK,IT,SKO etc.) - only allow group with org. unit prefix. If admin then any group value is allowed

Parameters

name type data type description
filename required string exact filename, e.g. Aut_my_file.csv

Responses

http code content-type response
200 application/json {"success":true,"message":"<filename> slettet"}
400/401/500 application/json {"success":false,"message":"<error message>"}

Files exported from KMD insight

GET /out (get list of files)

Custom headers (optional)

name type description
auth comma seperated string only allowed if contains admin.
Parameters

None

Responses

http code content-type response
200 application/json {"success":true,"files":[<filenames>]}
400/401/500 application/json {"success":false,"message":"<error message>"}
GET /out/{filename} (get file)

Custom headers (optional)

name type description
auth comma seperated string only allowed if contains admin.

Parameters

name type data type description
filename required string must match an existing filename exacly e.g. my_out_file.xlsx

Responses

http code content-type response
200 binary binary file
400/401/500 application/json {"success":false,"message":"<error message>"}
DELETE /out/{filename} (delete file)

Notes

Unlike the other DELETE endpoints, output files will really be deleted from the SFTP server.\

Custom headers (optional)

name type description
auth comma seperated string only allowed if contains admin.

Parameters

name type data type description
filename required string must match an existing filename exacly e.g. my_out_file.xlsx

Responses

http code content-type response
200 application/json {"success":true,"message":"<filename> slettet"}
400/401/500 application/json {"success":false,"message":"<error message>"}