PowerBI-raportteja¶

Kouvolan kaupunginkirjaston PowerBI-tilastoissa käytettyjä raportteja.

SQL-raportteja Kohaan¶

Taustaraportit¶

Näillä raporteilla haetaan taustatietoja PowerBI-tilastoihin. Tyypillisesti ne avaavat muussa datassa olevia lyhenteitä, esim. kirjastoja, hyllypaikkoja ja kokoelmia.

Kirjastotiedot¶

select branchcode as 'Kirjaston tunnus', branchname as 'Kirjaston nimi' from branches

Hyllypaikkatiedot¶

select authorised_value as 'Hyllypaikan lyhenne', lib as 'Hyllypaikan kuvaus' from authorised_values where category='LOC'

Asiakastyyppitiedot¶

select categorycode as 'Asiakastyypin tunnus', description as 'Asiakastyypin kuvaus' from categories

Aineistotyyppitiedot¶

select authorised_value as 'Aineistotyypin lyhenne', lib as 'Aineistotyypin kuvaus' from authorised_values where category='MTYPE'

Kokoelmatiedot¶

select authorised_value as 'Kokoelman lyhenne', lib as 'Kokoelma' from authorised_values where category='CCODE'

notforloan-tiedot¶

SELECT authorised_value as 'Ei lainattavissa -arvo', lib as 'Ei lainattavissa -tila'
FROM authorised_values 
WHERE category='NOT_LOAN'

damaged-tiedot¶

SELECT authorised_value as 'Vaurioitunut -arvo', lib as 'Vaurioitunut -tila'
FROM authorised_values 
WHERE category='DAMAGED'

lost-tiedot¶

SELECT authorised_value as 'Kadonnut-arvo', lib as 'Kadonnut-tila'
FROM authorised_values 
WHERE category='LOST'

Toimittajatiedot¶

SELECT id,name AS 'Toimittaja'
FROM aqbooksellers

Lainausdata¶

SELECT s.type AS 'Tapahtumatyyppi', IFNULL(bi.biblionumber, IFNULL(bi2.biblionumber, dbi.biblionumber)) AS 'Tietuenumero', s.itemnumber AS 'Nidenumero', 
IFNULL(bi.author, IFNULL(bi2.author, dbi.author)) as 'Tekijä', 
CONCAT_WS(' ', IFNULL(bi.title, IFNULL (bi2.title, dbi.title)), IFNULL(bi.subtitle, IFNULL (bi2.subtitle, dbi.subtitle)), IFNULL(bi.part_number, IFNULL(bi2.part_number,dbi.part_number)), IFNULL(bi.part_name, IFNULL (bi2.part_name, dbi.part_name))) as Nimeke,
s.branch AS 'Lainauskirjasto', s.borrowernumber AS 'Asiakas-id',
IFNULL(b.categorycode, db.categorycode) AS 'Asiakastyyppi', 
IFNULL(LEFT(b.dateofbirth, 4), (LEFT(db.dateofbirth, 4))) AS 'Svuosi', 
IFNULL(CAST(b.zipcode AS CHAR(5)), CAST(db.zipcode AS CHAR(5))) AS 'Postinumero', 
LEFT(s.datetime, 10) AS 'Tapahtuma-aika_pvm', 
SUBSTR(s.datetime, 12,2) AS 'Tapahtumatunti', 
IFNULL(bibi1.itemtype, IFNULL(bibi2.itemtype, dbibi.itemtype)) AS 'Aineistotyyppi',
IFNULL(i.permanent_location, d.permanent_location) AS 'Hyllypaikka', 
s.ccode AS 'Kokoelma',
IFNULL(i.cn_sort, d.cn_sort) AS 'Luokka ja pääsana', 
2021-IFNULL(LEFT(b.dateofbirth, 4), (LEFT(db.dateofbirth, 4))) AS 'Ikä',
IFNULL (bdei.primary_language, bde.primary_language) AS 'Kieli',
SUBSTR(
    IFNULL(i.cn_sort, d.cn_sort), 
    1,
    INSTR(IFNULL(i.cn_sort, d.cn_sort), ' ') - 1
) AS 'Luokka'
FROM statistics s
LEFT JOIN deleteditems d ON s.itemnumber = d.itemnumber
LEFT JOIN items i ON s.itemnumber = i.itemnumber
LEFT JOIN deletedbiblio dbi ON d.biblionumber=dbi.biblionumber
LEFT JOIN biblio bi ON i.biblionumber=bi.biblionumber
LEFT JOIN biblio bi2 ON d.biblionumber = bi2.biblionumber
LEFT JOIN deletedbiblioitems dbibi ON i.biblioitemnumber = dbibi.biblioitemnumber
LEFT JOIN biblioitems bibi1 ON i.biblioitemnumber = bibi1.biblioitemnumber
LEFT JOIN biblioitems bibi2 ON d.biblioitemnumber = bibi2.biblioitemnumber
LEFT JOIN deletedborrowers db ON s.borrowernumber = db.borrowernumber
LEFT JOIN borrowers b ON s.borrowernumber = b.borrowernumber
LEFT JOIN biblio_data_elements bdei ON i.biblioitemnumber=bdei.biblioitemnumber
LEFT JOIN koha_plugin_fi_kohasuomi_okmstats_biblio_data_elements bde ON d.biblioitemnumber = bde.biblioitemnumber
WHERE date(datetime) BETWEEN <<Aikaväli alkaen|date>> AND <<Päättyen|date>>
AND s.type in ('issue', 'renew')
AND convert(s.branch using 'utf8') LIKE (@Kunta:= <<Kunta tai kirjasto esim. KOU% tai KOU_PK>>)
AND b.categorycode != 'EITILASTO'

Nidedata¶

SELECT i.biblionumber AS 'Tietuenumero', i.itemnumber AS 'Nidenumero', i.homebranch AS 'Kotikirjasto', i.permanent_location AS 'Hyllypaikka', i.ccode AS 'Kokoelma', i.cn_sort AS 'Luokka ja pääsana', i.dateaccessioned AS 'Saapumispäivä', LEFT(i.dateaccessioned, 4) AS 'Saapumispäivän vuosi', i.datelastborrowed AS 'Viimeksi lainattu', i.datelastseen AS 'Viimeksi havaittu', i.notforloan AS 'Ei lainattavissa -tila', i.damaged AS 'Ei varattavissa', i.itemlost AS 'Kadonnut', i.issues AS 'Lainoja', i.renewals AS 'Uusintoja', (IFNULL(i.issues, 0)+IFNULL(i.renewals, 0)) AS 'Lainoja_yht.', 
bi.itemtype AS 'Aineistotyyppi', 
b.author AS 'Tekijä', b.title AS 'Nimeke', b.subtitle AS 'Alanimeke', b.part_name AS 'Osan nimi', b.part_number AS 'Osan numero', 
CONCAT_WS(' ', b.title, b.subtitle, b.part_number, b.part_name) as Nimeke2,
SUBSTR(ExtractValue(bm.metadata,'//controlfield[@tag="008"]'),8,4) AS '008/8 vuosi', SUBSTR(ExtractValue(bm.metadata,'//controlfield[@tag="008"]'),36,3) AS 'Kielikoodi',
2023-SUBSTR(ExtractValue(bm.metadata,'//controlfield[@tag="008"]'),8,4) AS 'Aineiston ikä',
2023-LEFT(i.dateaccessioned, 4) AS 'Aineiston ikä (saapumisesta)',
2023-LEFT(i.datelastborrowed, 4) AS 'Vuosia viim. lainauksesta',
ExtractValue(bm.metadata,'//datafield[@tag="599"]/subfield[@code="a"]') AS 'Daisy',
SUBSTR(
    i.cn_sort, 
    1,
    INSTR(i.cn_sort, ' ') - 1
) AS 'Luokka'
FROM items i
LEFT JOIN biblioitems bi ON bi.biblioitemnumber=i.biblioitemnumber
LEFT JOIN biblio b ON i.biblionumber=b.biblionumber
LEFT JOIN biblio_metadata bm ON b.biblionumber=bm.biblionumber
WHERE convert(i.homebranch using 'utf8') LIKE (@Kunta:= <<Kunta tai kirjasto esim. KOU% tai KOU_PK>>)

Hankintadata¶

SELECT b.biblionumber, i.itemnumber, i.homebranch AS 'kirjasto', i.permanent_location AS 'hyllypaikka', i.ccode, bi.itemtype, i.cn_sort, b.author, 
b.title, b.subtitle AS 'Alanimeke', b.part_name AS 'Osan nimi', b.part_number AS 'numero', 
CONCAT_WS(' ', b.title, b.subtitle, b.part_number, b.part_name) as Nimeke2,
aq.entrydate AS 'Tilattu (aqorders.entrydate)', 
aq.datereceived AS 'Saapunut (aqorders.datereceived)', 
format(i.price, 2,'fi_FI') AS 'Hinta niteissä', 
format(aq.ecost, 2,'fi_FI') AS 'Tilattujen hinta (ecost)', 
i.booksellerid AS 'items.booksellerid', 
i.notforloan, 
SUBSTR(ExtractValue(bm.metadata,'//controlfield[@tag="008"]'),8,4) AS '008/8 vuosi', 
SUBSTR(ExtractValue(bm.metadata,'//controlfield[@tag="008"]'),36,3) AS 'Kieli_008', 
i.issues AS lainoja, i.renewals AS uusintoja, (IFNULL(i.issues, 0)+IFNULL(i.renewals, 0)) AS 'Lainoja_yht.', 
SUBSTR(
    i.cn_sort, 
    1,
    INSTR(i.cn_sort, ' ') - 1
) AS 'Luokka'
FROM items i
JOIN aqorders_items aqi ON (aqi.itemnumber=i.itemnumber)
JOIN aqorders aq ON (aqi.ordernumber=aq.ordernumber)
JOIN biblio b ON (i.biblionumber=b.biblionumber)
JOIN biblioitems bi ON (b.biblionumber=bi.biblionumber)
JOIN biblio_metadata bm ON (b.biblionumber=bm.biblionumber)
WHERE aq.datereceived BETWEEN <<AloitusPvm|date>> AND <<LopetusPvm|date>> AND convert(i.homebranch using 'utf8') LIKE (@Kunta:= <<Kuntaosio ja prosenttimerkki>>)

Poistodata¶

SELECT di.biblionumber, di.itemnumber, di.homebranch AS 'kotikirjasto', di.permanent_location AS 'hyllypaikka', di.ccode AS 'kokoelma', di.cn_sort AS 'signum', di.dateaccessioned AS 'saapumispäivä', LEFT(di.dateaccessioned, 4) AS 'saapumispäivän vuosi', di.datelastborrowed AS 'viimeksi lainattu', di.datelastseen AS 'viimeksi havaittu', di.notforloan AS 'ei lainattavissa -tila', di.damaged AS 'ei varattavissa', di.itemlost AS 'kadonnut', di.issues AS 'lainoja', di.renewals AS 'uusintoja', (IFNULL(di.issues, 0)+IFNULL(di.renewals, 0)) AS 'Lainoja_yht.', 
di.timestamp AS 'poistoaika', 
LEFT(di.timestamp, 4) AS 'poistoajan vuosi',
IFNULL(bi.itemtype, dbi.itemtype) AS 'aineistotyyppi', 
IFNULL(b.author, db.author) AS 'tekijä', IFNULL(b.title,db.title) AS nimeke, IFNULL(b.subtitle,db.subtitle) AS alanimeke, IFNULL(b.part_number, db.part_number) AS 'osan numero', 
CONCAT_WS(' ', IFNULL(b.title,db.title), IFNULL(b.subtitle,db.subtitle), IFNULL(b.part_number, db.part_number), IFNULL(b.part_name, db.part_name)) as 'Nimeke2',
SUBSTR(ExtractValue(bm.metadata,'//controlfield[@tag="008"]'),8,4) AS '008/8 vuosi', 
bde.primary_language AS 'Kielikoodi',
2022-SUBSTR(ExtractValue(bm.metadata,'//controlfield[@tag="008"]'),8,4) AS 'aineiston ikä',
2022-LEFT(di.dateaccessioned, 4) AS 'aineiston ikä (saapumisesta)',
ExtractValue(bm.metadata,'//datafield[@tag="599"]/subfield[@code="a"]') AS 'Daisy', 
SUBSTR(di.cn_sort, 
    1,
    INSTR(di.cn_sort, ' ') - 1
) AS 'Luokka'
FROM deleteditems di
LEFT JOIN biblioitems bi ON bi.biblioitemnumber=di.biblioitemnumber
LEFT JOIN biblio b ON di.biblionumber=b.biblionumber
LEFT JOIN biblio_metadata bm ON b.biblionumber=bm.biblionumber
LEFT JOIN deletedbiblioitems dbi ON di.biblioitemnumber = dbi.biblioitemnumber
LEFT JOIN deletedbiblio db ON di.biblionumber=db.biblionumber
LEFT JOIN biblio_data_elements bde ON di.biblioitemnumber=bde.biblioitemnumber
WHERE convert(di.homebranch using 'utf8') LIKE (@Kunta:= <<Kunta tai kirjasto esim. KOU% tai KOU_PK>>) AND di.timestamp BETWEEN (@AloitusPvm:= <<AloitusPvm |date>>) AND (@LopetusPvm:= <<LopetusPvm |date>>)

Taustatietoja muista lähteistä¶

Halutun alueen asukasmäärät (esim. kunnittain, postinumeroalueitttain, ikäryhmittäin)
Postinumeroalueet (postinumerot, postinumeroalueen nimi, kunta)
aikatietotaulu, jossa tarkasteltavan ajan päivämäärät, viikonpäivät, kuukaudet, vuodet omina sarakkeinaan
luokitustiedot
kielikoodit
aineistotyyppiryhmittelyt
ikäryhmät, joilla haluaa asiakkaita tarkastella

Näistä luokitustiedot ja kielikoodit ovat melko suoraan käytettävissä. Muita voi joutua muokkaamaan halutunlaisiksi.

DAX-kielisiä lausekkeita¶

Käytetään PowerBIn sisällä mittareita luotaessa.

Lainausdataan liittyviä lausekkeita¶

Lainojen määrä = count('Lainausdata'[Tapahtumatyyppi])

Ensilainojen määrä = CALCULATE('Mittarit'[Lainojen määrä],'Lainausdata'[Tapahtumatyyppi] IN {"issue"})

Lainaajien määrä = DISTINCTCOUNT('Lainausdata'[asiakas-id])

Lainaajien keski-ikä = AVERAGEX(SUMMARIZE(Lainausdata, Lainausdata[Asiakas-id], Lainausdata[Ikä]), Lainausdata[Ikä])

Nidedataan liittyviä lausekkeita¶

Aineiston keski-ikä = AVERAGE('Nidedata'[aineiston ikä])

Niteiden määrä = count('Nidedata'[Nidenumero])

5 vuotta vanha aineisto = CALCULATE([Niteiden määrä], 'Nidedata'[aineiston ikä] IN {0,1,2,3,4})

5 vuotta vanhan aineiston osuus = DIVIDE('Mittarit'[5 vuotta vanha aineisto],'Mittarit'[Niteiden määrä])

Lainat / nide = DIVIDE(SUM('Nidedata'[Lainoja_yht.]), [Niteiden määrä])

Jotta saadaan laskettua aineiston, jota ei ole lainattu viiteen vuoteen (=1825 päivää) ja joka on saapunut yli 2 vuotta (=730 päivää) sitten, määrä ja osuus tarvitaan seuraavat mittarit:

Viimeksi lainattu -mittari = SELECTEDVALUE('Niteet'[Viimeksi lainattu])
Päiviä viime lainauksesta = DATEDIFF('Mittarit'[Viimeksi lainattu -mittari];TODAY();DAY)
Saapumispäivä_mittari = SELECTEDVALUE('Niteet'[Saapumispäivä])
Päiviä saapumisesta = DATEDIFF('Mittarit'[Saapumispäivä_mittari];TODAY();DAY)
5 vuoteen ei lainattu = CALCULATE('Mittarit'[Niteiden määrä]; FILTER('Niteet';'Mittarit'[Päiviä viime lainauksesta]>1825); FILTER ('Niteet';'Mittarit'[Päiviä saapumisesta]>730))
5 vuoteen lainaamattomien osuus = DIVIDE([5 vuoteen ei lainattu]; [Niteiden määrä])

Hankintoihin liittyviä lausekkeita¶

Hankitut niteet = COUNT('Hankintadata'[itemnumber])

Ei-lainatut hankinnat = CALCULATE('Mittarit'[Hankitut niteet], 'Hankintadata'[Lainoja_yht.]=0)

Ei-lainattujen hankintojen osuus = DIVIDE([Ei-lainatut hankinnat], [Hankitut niteet])

Tiedostot (6)

aikatietotaulu.xlsx (74,2 KB) aikatietotaulu.xlsx	aikatietotaulu	Tuomas Kunttu, 10. Tammikuuta 2023 14:01
Aineistoryhmät.xlsx (9,29 KB) Aineistoryhmät.xlsx	aineistotyyppiryhmittelyt	Tuomas Kunttu, 10. Tammikuuta 2023 14:04
kielikoodit.csv (4,36 KB) kielikoodit.csv	kielikoodit	Tuomas Kunttu, 10. Tammikuuta 2023 14:04
luokat_muotoluokat.xlsx (167 KB) luokat_muotoluokat.xlsx	luokutustiedot	Tuomas Kunttu, 10. Tammikuuta 2023 14:04
postinumerot.csv (268 KB) postinumerot.csv	postinumerot	Tuomas Kunttu, 10. Tammikuuta 2023 14:05
ikäryhmät.xlsx (12,3 KB) ikäryhmät.xlsx	ikäryhmät	Tuomas Kunttu, 10. Tammikuuta 2023 14:49

Projekti

Yleinen

Profile

Koha-Suomen dokumentaatio

Wiki