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.
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.
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
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:
- Metadata og data samlet i en fil
- 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 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
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
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.
GET /in (get list of files)
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.
None
http code content-type response 200application/json{"success":true,"files":{"failed":[<filenames>],"imported":[<filenames>],"waiting":[<filenames>]}}400/401/500application/json{"success":false,"message":"<error message>"}
GET /in/{filename} (get file)
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.
name type data type description filename required string must match an existing filename exacly e.g. Data_my_file.csv
http code content-type response 200text/csvCSV string 400/401/500application/json{"success":false,"message":"<error message>"}
POST /in (create with JSON)
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"
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
name type data type description body required JSON object N/A
{
"group":"myGroup",
"name": "myName", (optional)
"data":[
{
"myColNum": 1,
"myColText":"some text"
},
{
"myColNum": 2,
"myColText":"some more text"
}
]
}
http code content-type response 200application/json{"success":true,"message":"<N> fil(er) uploaded", "files":[<filenames>]}400/401/500application/json{"success":false,"message":"<error message>"}
POST /in (create with formData - one or more CSV files)
Important! filenames must be percent-encoded (URL encoded) to handle special characters such as æ,ø,å. Javascipt function
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
name type data type description body required formData formData with one or more field(s) containing CSV files , field names ignored
http code content-type response 200application/json{"success":true,"message":"<N> fil(er) uploaded", "files":[<filenames>]}400/401/500application/json{"success":false,"message":"<error message>"}
POST /in/{filename} (create with CSV file)
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
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
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
http code content-type response 200application/json{"success":true,"message":"<N> fil(er) uploaded", "files":[<filenames>]}400/401/500application/json{"success":false,"message":"<error message>"}
PUT /in (create or overwite files)
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
name type data type description body required JSON object N/A
{
"group":"myGroup",
"name": "myName", (optional)
"data":[
{
"myColNum": 1,
"myColText":"some text"
},
{
"myColNum": 2,
"myColText":"some more text"
}
]
}
http code content-type response 200application/json{"success":true,"message":"<N> fil(er) uploaded", "files":[<filenames>]}400/401/500application/json{"success":false,"message":"<error message>"}
PUT /in/{filename} (create or overwite files)
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
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
http code content-type response 200application/json{"success":true,"message":"<N> fil(er) uploaded", "files":[<filenames>]}400/401/500application/json{"success":false,"message":"<error message>"}
DELETE /in/{filename} (delete a file)
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.\
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
name type data type description filename required string exact filename, e.g. Meta_my_file.csv
http code content-type response 200application/json{"success":true,"message":"<filename> slettet"}400/401/500application/json{"success":false,"message":"<error message>"}
POST /in/aut (create authentication file with JSON)
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
name type data type description body required JSON object N/A
{
"data_file":"Data_my_file.csv",
"users":["username1", "username2"]
}
http code content-type response 200application/json{"success":true,"message":"<N> fil(er) uploaded", "files":[<filenames>]}400/401/500application/json{"success":false,"message":"<error message>"}
POST /in/aut (create authentication file(s) with formData)
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.
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
name type data type description body required formData formData with one or more field(s) containing CSV files , field names ignored
http code content-type response 200application/json{"success":true,"message":"<N> fil(er) uploaded", "files":[<filenames>]}400/401/500application/json{"success":false,"message":"<error message>"}
POST /in/aut/{filename} (create authentication file with CSV file))
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.
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
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
http code content-type response 200application/json{"success":true,"message":"<N> fil(er) uploaded", "files":[<filenames>]}400/401/500application/json{"success":false,"message":"<error message>"}
PUT /in/aut/{filename} (create/overwrite authentication file)
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
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
http code content-type response 200application/json{"success":true,"message":"<N> fil(er) uploaded", "files":[<filenames>]}400/401/500application/json{"success":false,"message":"<error message>"}
DELETE /in/aut/{filename} (delete an Aut file)
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.\
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
name type data type description filename required string exact filename, e.g. Aut_my_file.csv
http code content-type response 200application/json{"success":true,"message":"<filename> slettet"}400/401/500application/json{"success":false,"message":"<error message>"}
GET /out (get list of files)
name type description auth comma seperated string only allowed if contains admin.
None
http code content-type response 200application/json{"success":true,"files":[<filenames>]}400/401/500application/json{"success":false,"message":"<error message>"}
GET /out/{filename} (get file)
name type description auth comma seperated string only allowed if contains admin.
name type data type description filename required string must match an existing filename exacly e.g. my_out_file.xlsx
http code content-type response 200binary binary file 400/401/500application/json{"success":false,"message":"<error message>"}
DELETE /out/{filename} (delete file)
Unlike the other DELETE endpoints, output files will really be deleted from the SFTP server.\
name type description auth comma seperated string only allowed if contains admin.
name type data type description filename required string must match an existing filename exacly e.g. my_out_file.xlsx
http code content-type response 200application/json{"success":true,"message":"<filename> slettet"}400/401/500application/json{"success":false,"message":"<error message>"}