Jobindsats - Logisk datamodel
- Bjarne Hansen (Edora)
- Knud de Place (STAR)
Indholdsfortegnelse
- 1 Indholdsfortegnelse
- 2 Indledning
- 2.1 Afgrænsning
- 3 Logisk Metadatamodel
- 3.1 Beskrivelse af tabeller
- 3.1.1 Alle tabeller
- 3.1.2 Tabel: cube
- 3.1.3 Tabel: data_source_info
- 3.1.4 Tabel: cube_data_source_info
- 3.1.5 Tabel: dimension
- 3.1.6 Tabel: cube_dimension
- 3.1.7 Tabel: measure
- 3.1.8 Tabel: cube_measure
- 3.1.9 Tabel: member
- 3.1.10 Tabel: cube_member
- 3.1.11 Tabel: mgroup_rel
- 3.1.12 Tabel: mgroup
- 3.1.13 Tabel: maaling
- 3.1.14 Tabel: maaling_mgroup
- 3.1.15 Tabel: maaling_hierarchy
- 3.1.16 Tabel: hierarchy
- 3.1.17 Tabel: level
- 3.1.18 Tabel: update_frequency
- 3.2 Beskrivelse af views
- 3.2.1 View: valueset_hrchy
- 3.2.1.1 Vigtige forhold
- 3.2.2 View: valueset_period
- 3.2.3 View: cube_update
- 3.2.1 View: valueset_hrchy
- 3.1 Beskrivelse af tabeller
- 4 Detaljeret design
Indledning
Formålet med dette slutprodukt er at beskrive de logiske data entiteter i løsningens domæne sammen med deres attributer og relationer.
Dokumentet henvender sig til:
Udviklere, som har behov for at forstå designet af datamodellen og forberede detaljeret design og senere implementere løsningen.
Deltagere fra STAR, som er ansvarlige for det funktionelle design.
Afgrænsning
Dette er ikke en fysisk database model, men en logisk repræsentaton af relationer imellem forretningsobjekter. Der tages forbehold for eventuelle tilføjelser af nye entiteter.
Logisk Metadatamodel
Figur 1: JI3_Datamodel logisk metadatamodel
Beskrivelse af tabeller
Alle tabeller
Samtlige tabeller i databasen har følgende felter:
Felt | Beskrivelse | Type |
created_dttm | Datoen for oprettelse af entiteten | Datetime |
created_by | Initialer på den person, der har oprettet entiteten | Varchar(64) |
changed_dttm | Seneste dato for ændringer af entiteten | Datetime |
changed_by | Intitialer på den person, der senest har ændret entiteten | Varchar(64) |
Tabel: cube
Felt | Beskrivelse | Type |
cube_id | Primær nøgle for cube | Varchar(32) |
display_name | Visningsnavnet for cuben. Anvendes ikke i databanken | Varchar(128) |
description | Beskrivelse af cuben. Anvendes internt af STAR | Varchar(256) |
update_frequency_id | Fremmednøgle for den update_frequency som rækken er tilknyttet | Varchar(32) |
responsible | Initialer på den person, der er ansvarlig for vedligeholdelse af cuben | Varchar(32) |
update_allowed | Angiver om cuben må opdateres. Anvendes internt af STAR | Bit |
Anvendes internt af STAR | Varchar(64) |
Tabel: data_source_info
Felt | Beskrivelse | Type |
data_source_info_id | Primærnøgle for data_source_info | Varchar(32) |
data_source_info_text | Tekst for data_source_info. Anvendes i databanken til at vise kilden ved resultatvisningen (anmærkning). | Varchar(2048) |
Tabel: cube_data_source_info
Felt | Beskrivelse | Type |
cube_id | Fremmednøgle for den cube som rækken er tilknyttet | Varchar(32) |
data_source_info_id | Fremmednøgle for den data_source_info som rækken er tilknyttet | Varchar(32) |
Tabel: dimension
Felt | Beskrivelse | Type |
dimension_id | Primærnøgle for dimension | Varchar(32) |
display_name | Visningsnavnet for dimension. Anvendes ikke i databanken | Varchar(64) |
description | Beskrivelse af dimension. Anvendes internt af STAR | Varchar(256) |
responsible | Initialer på den person, der er ansvarlig for vedligeholdelse af dimensionen | Varchar(32) |
dimension_type | Typen af dimension. Anvendes internt af STAR | Varchar(64) |
dim_table | Anvendes internt af STAR | Varchar(32) |
dim_table_key_column | Anvendes internt af STAR | Varchar(16) |
Tabel: cube_dimension
Felt | Beskrivelse | Type |
cube_id | Fremmednøgle for den cube som rækken er tilknyttet | Varchar(32) |
dimension_id | Fremmednøgle for den dimension som rækken er tilknyttet | Varchar(32) |
hierarchy_id | Fremmednøgle for det hierarchy som rækken er tilknyttet | Varchar(32) |
is_helper_dimension | Angiver om hierarchy entiteter relateret til den givne cube_dimension skal vises i databanken | Bit |
Tabel: measure
Felt | Beskrivelse | Type |
measure_id | Primær nøgle for measure | Varchar(32) |
display_name | Visningsnavnet for measure. Anvendes ved resultatvisningen i databanken | Varchar(128) |
description | Beskrivelse af measure. Anvendes internt af STAR | Varchar(256) |
agg_statistic | Anvendes internt af STAR | Varchar(32) |
column | Anvendes internt af STAR | Varchar(32) |
format | Anvendes i databanken til at afgøre hvilket format data skal præsenteres i resultatvisningen | Varchar(32) |
Tabel: cube_measure
Felt | Beskrivelse | Type |
cube_id | Fremmednøgle for den cube som rækken er tilknyttet | Varchar(32) |
measure_id | Fremmednøgle for den measure som rækken er tilknyttet | Varchar(32) |
Tabel: member
Felt | Beskrivelse | Type |
member_id | Primær nøgle for member | Varchar(32) |
display_name | Visningsnavnet for member. Anvendes ved resultatvisningen i databanken | Varchar(128) |
description | Beskrivelse af member. Anvendes internt af STAR | Varchar(256) |
formula | Anvendes internt af STAR | Varchar(512) |
format | Anvendes i databanken til at afgøre hvilket format data skal præsenteres i resultatvisningen | Varchar(32) |
ji2_mdx | Anvendes internt af STAR | Varchar(512) |
Tabel: cube_member
Felt | Beskrivelse | Type |
cube_id | Fremmednøgle for den cube som rækken er tilknyttet | Varchar(32) |
member_id | Fremmednøgle for den member som rækken er tilknyttet | Varchar(32) |
Tabel: mgroup_rel
Felt | Beskrivelse | Type |
mgroup_id | Fremmednøgle for den mgroup som rækken er tilknyttet | Varchar(32) |
member_id | Fremmednøgle for den member som rækken er tilknyttet | Varchar(32) |
measure_id | Fremmednøgle for den measure som rækken er tilknyttet | Varchar(32) |
meas_memb_id | Beregnet værdi. Indeholder enten measure_id eller member_id afhængigt af hvilken af disse, der ikke er null | Varchar(32) |
display_order | Anvendes i databanken til at afgøre rækkefølgen for visningen af mgroups | Int |
Tabel: mgroup
Felt | Beskrivelse | Type |
mgroup_id | Primærnøgle for mgroup | Varchar(32) |
display_name | Visningsnavnet for mgroup. Anvendes som visningsnavne for målingsvariable i databanken | Varchar(128) |
description | Beskrivelse af mgroup. Anvendes internt af STAR | Varchar(256) |
footnote | Fodnote for mgroup. Vises i databanken som en del af resultatvisningen | Varchar(2048) |
display_order | Anvendes i databanken til at afgøre rækkefølgen for visningen af mgroups | Int |
Tabel: maaling
Felt | Beskrivelse | Type |
maaling_id | Primærnøgle for maaling | Varchar(32) |
cube_id | Fremmednøgle for den cube som rækken er tilknyttet | Varchar(32) |
display_name | Visningsnavnet for maaling. Anvendes ikke i databanken | Varchar(128) |
footnote | Fodnote for maaling. Vises i databanken som en del af resultatvisningen | Varchar(2048) |
Tabel: maaling_mgroup
Felt | Beskrivelse | Type |
maaling_id | Fremmednøgle for den måling som rækken er tilknyttet | Varchar(32) |
mgroup_id | Fremmednøgle for den mgroup som rækken er tilknyttet | Varchar(32) |
is_active | Angiver om mgroup entiteter relateret til den givne maaling_mgroup skal vises i databanken | Bit |
Tabel: maaling_hierarchy
Felt | Beskrivelse | Type |
maaling_id | Fremmednøgle for den måling som rækken er tilknyttet | Varchar(32) |
hierarchy_id | Fremmednøgle for det hierarchy som rækken er tilknyttet | Varchar(32) |
is_selectable | Angiver om hierarchy entiteter relateret til denne maaling_hierarchy skal vises og kunne vælges i databanken | Bit |
is_all_member_selectable | Angiver om “I alt”-valgmulighed skal vises for den bestemte måling. Hvis False, vises ”I alt” felter fra valueset_hrchy ikke i databanken | Bit |
is_required | Angiver om hierarchy entiteter relateret til denne maaling_hierarchy er obligatoriske og påkrævede at udfylde i databanken. Værdien er også afgørende for sorteringen af hierarchy i databanken. Rækker med True vises før rækker med False. | Bit |
is_active | Angiver om hierarchy entiteter relateret til denne maaling_hierarchy er aktive. Hvis False, vises hierarchy ikke, uanset om is_selectable er True. | Bit |
Tabel: hierarchy
Felt | Beskrivelse | Type |
hierarchy_id | Primærnøgle for hierarchy | Varchar(32) |
dimension_id | Fremmednøgle for den dimension som rækken er tilknyttet | Varchar(32) |
display_name | Visningsnavnet for hierarchy. Anvendes visningsnaven for fordelingsvariable i databanken. | Varchar(128) |
description | Beskrivelse af hierarchy. Anvendes internt af STAR | Varchar(256) |
all_member_display_name | Anvendes internt af STAR. display_name for “all member” hentes fra valueset_hrchy til databanken. | Varchar(2048) |
footnote | Fodnote for maaling. Vises i databanken som en del af resultatvisningen. | Varchar(2048) |
display_order | Bestemmer rækkefølgen af visning af fordelingsvariable i databanken efter de faste; ”Målingsvariable”, ”Område”, ”Periode” og de hierarchy hvor is_active er True. | Int |
sequence | Anvendes internt af STAR | Int |
Tabel: level
Felt | Beskrivelse | Type |
hierarchy_id | Fremmednøgle for det hierarchy som rækken er tilknyttet | Varchar(32) |
level_id | Primærnøgle for level | Varchar(32) |
hierarchy_level_sequence | Anvendes internt af STAR | Int |
display_name | Visningsnavn af level. Anvendes internt af STAR | Varchar(64) |
description | Beskrivelse af level. Anvendes internt af STAR | Varchar(256) |
level_value_sort_order | Anvendes internt af STAR | Varchar(32) |
Tabel: update_frequency
Felt | Beskrivelse | Type |
update_frequency_id | Primærnøgle for update_frequency | Varchar(32) |
display_name | Visningsnavn for update_frequency. Anvendes i databanken til at vise opdateringsfrekvensen for en måling. | Varchar(128) |
Beskrivelse af views
View: valueset_hrchy
Valueset_hrchy (udtales valueset heirarchy) indeholder de mulige værdier, som det er muligt at forespørge på, for en måling i Datamarten. Viewet viser data fra JI3_Valuesets databasen. Dette er en essentiel tabel, der indeholder de data, som præsenteres for brugeren af databanken på jobindsats.dk og alle fordelingsvariable undtagen periode (disse værdier er at finde i en valueset_periode som beskrives i afsnit View: valueset_period).
Felt | Beskrivelse | Type |
cube_id | Fremmednøgle for den cube som rækken er tilknyttet | char(16) |
dimension_id | Fremmednøgle for den dimension som rækken er tilknyttet | varchar(32) |
hierarchy_id | Fremmednøgle for det hierarchy som rækken er tilknyttet | varchar(32) |
hrchy_txt | Visningsnavn for valgmuligheden under en fordelingsvariable i databanken. Feltet vises i databanken og benyttes som parameter ved query mod Datamarten, der returnerer målingsresultatet gennem den integrerede stored procedure. | varchar(128) |
hrchy_id | ID kolonne, der muliggøre angivelse af et hierarchy datastruktur. Benyttes til angivelse af parant/child forhold og til sortering. | hierarchyid |
lev1_txt | Teksten for level 1. Benyttes ikke i databanken. NULL hvis der ikke er et level | varchar(64) |
lev2_txt | Teksten for level 2. Benyttes ikke i databanken. NULL hvis der ikke er et level | varchar(64) |
Lev3_txt | Teksten for level 3. Benyttes ikke i databanken. NULL hvis der ikke er et level | varchar(64) |
lev4_txt | Teksten for level 4. Benyttes ikke i databanken. NULL hvis der ikke er et level | varchar(64) |
lev5_txt | Teksten for level 5. Benyttes ikke i databanken. NULL hvis der ikke er et level | varchar(64) |
lev6_txt | Teksten for level 6. Benyttes ikke i databanken. NULL hvis der ikke er et level | varchar(64) |
hrchy_levels | Referencer til de levels, der benyttes i hierarkiet. Separeret med ”. ” (punktum og mellemrum). Benyttes ikke i databanken. | varchar(160) |
Vigtige forhold
Valueset_hrchy er et view af tabellen af samme navn i Datamarten.
Kolonnen hrchy_id kan indeholde huller i strukturen som det f.eks. er tilfældet for tilbud_ptv (nytteindsats) for cube_id=y11c02. Ift. præsentation af værdierne i databankens, fortolkes child-elementer uden en parent som childs til den parents-parent.
Eksempel på hvordan et manglende level (parant) håndteres.
/5/ Nytteindsats i alt
/5/10/1/ Nytteindsats, stat
/5/10/2/ Nytteindsats, region
/5/10/3/ Nytteindsats, kommune
/5/10/4/ Nytteindsats, selvej. inst.
Fortolkes som var det:
/5/ Nytteindsats i alt
/5/1/ Nytteindsats, stat
/5/2/ Nytteindsats, region
/5/3/ Nytteindsats, kommune
/5/4/ Nytteindsats, selvej. inst.
Sorting og bestemmelse af parent-child forhold fra valuesets_hrchy, bestemmes af hrchy_id, der sorteres ascending.
”X i alt” felter er specielle og vises som det første elementer med en afstand fra de andre valgmuligheder. Dette felt afgøres ud fra følgende: hrchy_id == ”/”
View: valueset_period
Valueset_period indeholder de mulige perioder, som er mulige at forespørge på, for en måling i Datamarten. Viewet viser data fra JI3_Valuesets databasen. Dette er en essentiel tabel, der indeholder de periodedata, som brugeren af databanken på jobindsats.dk præsenteres for.
Felt | Beskrivelse | Type |
cube_id | Fremmednøgle for den cube som rækken er tilknyttet | char(32) |
periode | Sammensætning af aar, periodetype2 og taller. Benyttes som parameter ved query mod Datamarten, der returnerer målingsresultatet gennem den integrerede stored procedure | char(10) |
display_name | Visningsnavn | char(48) |
aar | Året på integer form | char(4) |
periodetype2 | Bestemmer hvordan en periode skal vises i frontend, fx måned eller år | char(4) |
taller | Holder styr på periode rækkefølgen, fx er januar 01 mens december er 12 | char(2) |
View: cube_update
Et simpelt view som indeholder data om sidste og næste opdateringstidspunkt for en cube. Viewet viser data fra JI3_Valuesets databasen.
Felt | Beskrivelse | Type |
cube_id | Fremmednøgle for den cube som rækken er tilknyttet | varchar(32) |
latest_update_dttm | Sidste opdateringstidspunkt for kuben | datetime |
next_update_dttm | Næste opdateringstidspunkt for kuben | datetime |
Detaljeret design
Created og Changed felter for alle tabeller
Felterne created_dttm, created_by, changed_dttm, changed_by placeres til sidst i tabellerne og oprettes for alle tabeller.
De to create-felter; created_dttm, created_by er påkrævede og sættes hhv. af MSSQL database funktionerne getdate() og system_user.
Felterne changed_dttm, changed_by skal udfyldes vha. triggers og konfigureres af STAR.
Fodnoter
Der skelnes mellem kilder og anmærkninger. Anmærkninger relaterer sig til den cube data trækkes fra, mens fodnoter findes på tabellerne: mgroup, hierarchy og maaling.
Anmærkninger trækkes ud og skrives øverst for sig selv, fodnoter trækkes ud og sammensættes i rækkefølgen: maaling, mgroup, hierarchy.
Model for udtræk af målingsdata
Dette afsnit beskriver hvordan data trækkes ud af metadatamodellen til brug for temasiderne samt databanksiderne. Data trækkes med udgangspunkt i en måling hvilket fungerer som foreign keys til cube og relationstabellerne maaling_x findes de andre værdier der skal vises i UI.
Nedenstående illustrerer hvordan data udtrækkes. Hver boks er en tabel med tabelnavn i toppen og de felter der benyttes. Data felter fra relationstabeller er tilknyttet den tabel de er relevante for. Er en række markeret med fed betyder det at det er en tabel. Pilene indikerer fra hvilken tabel, der er muligt at komme fra og til, via foreign keys.
De relevante foreign keys er: cube_id, dimension_id, hierarchy_id
Figur 2 viser entiteter nødvendige for at vise data om en måling ifm. selve målingssiden i databasen samt resultatvisningen.
Figur 2: Logisk relation mellem data til udtræk af metadata til en databank Element side
Figur 3 viser data der skal trækkes ud for alle målinger, tilhørende en undersektion, for at vise relevant data på temasiderne.
Figur 3: Logisk relation mellem data til udtræk af metadata til en databank Tema side
Forhold der afgør om et hierarki skal vises i databanken
Følgende felter afgør om et hierarki skal vises i databanken:
cube_dimensions.is_helper_dimension (ud fra den dimension der er angivet i hierarki-tabellen)
maaling_hierarchy.is_selectable
maaling_hierarchy.is_active
For at et hierarki skal vises og være muligt at vælge fra igennem databanken, skal følgende gælde:
”cube_dimensions.Is_helper_dimension = False” AND” maaling_hierarchy.is_selectable = True” AND ”maaling_hierarchy.is_active = True”
Et hierarki kan dog godt have ”is_selectable = False” AND ”is_active = True”, dette vil medføre at hierakiet ikke bliver vist til brugeren men det sendes allivel med til stored procedure og vises i resultatviseren.
Indbyggertal
Indbyggertal fandtes i Jobindsats 2.0 som en sorteringsmulighed for område-vælgeren. Denne funktionalitet er ikke blevet prioriteret i Jobindsats 3.0 leverancen. Se historik her: udestående 104.
Data fra Stored procedure
Der bruges sp_ji3_GetDataV1_JSON i databasen JI3_Aggregates, denne stored procedure leverer data formateret som JSON. Dataværdier er formateret med det korrekte antal decimaler når det sendes op fra datamarten, dog vil alle deciamler som slutter på ”0” få fjernet denne præcision. Dette forhindres i frontenden ved at tjekke alle hver enkelt data søjle igennem og finde det tal som har højest præcision og benytte denne til visning, fx: ”1.08” vil medføre at ”1.1” bliver til ”1.10”.
Det eneste data som trækkes fra metadata til resultatvisningen er fodnoter/anmærkninger. Disse bliver trukket sammen med det objekt som bygger vælgersiden, se Figur 2.
Responseobjektet består af et headerobjekt og et dataobjekt, eksempel:
Figur 4 Eksempel på responseobjektet fra SP
Headeren består af en blanding af lister og objekter:
Liste indeholdende keys, disse korresponderer en til en med de display name der står er på næste liste. Denne liste bliver længere alt efter hvor mange dimension/measures/members der er valgt af brugeren.
Liste med displaynames som bruges til visning i tabellen.
Dimension: objekt som indeholder alle dimensioner, disse er vist med tilhørende visningsnavn samt underliggende hierarki.
Mgroup: objekt som indeholder alle mgroups med tilhørende visningsnavne. Under en mgroup er de tilhørende measures, såfremt at der kun er tale om enkelt measure/member vil dennes display name være det samme som for Mgruppen, hvis Der er flere vil de have unikke navne.
Valuesets: Primært brugt til at sortere displaynames korrekt i fronten ud fra hierarki ID.
Period: relevante periode data.
Data: liste af lister som hver korresponderer til liste 1 og 2
Når frontenden modtager responseobjektet skal det omstruktureres således at det kan benyttes af PivotTable.js. Dette gøres ved at splitte hver række i dataobjektet op så der kun er 1 talværdi pr. række, samtidigt tilføjes det korresponderende navn fra headeren, eksempel:
Figur 5 Transformeret responseobjekt
Som det ses i Figur 5 er de fleste af header navnene bevaret efter transformeringen, der er dog tilføjet ”Målingsværdier” og ”Målinger”. Dette er påkrævet for korrekt visualisering i PivotTable.js. Alle tal er også transformeret til strings som har dansk formatering (fx punktum som tusindtals seperator).
For at kunne vise brugeren de korrekte række- og søjle-displaynavne når der pivoteres indsættes de rigtige visningnavne på de lister som benyttes af PivotTable.js:
Figur 6 Mock objekt med displaynavne og interne navne
Her benyttes det faktum at der er indsat et displaynavn (fx Herkomst) før dets tilhørende interne navn/navne (fx 'oprinda_txt) således at de interne navne kan filtreres væk fra visningen når der pivoteres:
Figur 7 Pivotering UI uden interne navne
Når der så pivoteres flyttes både displayname og interne navne:
Figur 8 Mock objekt efter pivotering