PowerBI
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
päivitetty 13.11.2023
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',
COALESCE(bibi1.itemtype, bibi2.itemtype, dbibi.itemtype, dbibi2.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',
2023-IFNULL(LEFT(b.dateofbirth, 4), (LEFT(db.dateofbirth, 4))) AS 'Ikä',
IFNULL (bde1.primary_language, bde2.primary_language) AS 'Kieli',
SUBSTR(
IFNULL(i.cn_sort, d.cn_sort),
1,
INSTR(IFNULL(i.cn_sort, d.cn_sort), ' ') - 1
) AS 'Luokka',
IFNULL(i.dateaccessioned, d.dateaccessioned) AS 'Saapumispäivä',
IFNULL(LEFT(i.dateaccessioned, 4), (LEFT(d.dateaccessioned, 4))) AS 'Saapumispäivän vuosi'
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 deletedbiblioitems dbibi2 ON d.biblioitemnumber = dbibi2.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 koha_plugin_fi_kohasuomi_okmstats_biblio_data_elements bde1 ON i.biblionumber = bde1.biblionumber
LEFT JOIN koha_plugin_fi_kohasuomi_okmstats_biblio_data_elements bde2 ON d.biblionumber = bde2.biblionumber
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 tai kirjasto esim. KOU% tai KOU_PK>>
AND b.categorycode != 'EITILASTO'
Nidedata
päivitetty 13.11.2023
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ä',
CONCAT_WS(' ', b.title, b.subtitle, b.part_number, b.part_name, i.enumchron) as 'Nimeke',
bde.publication_year AS 'julk. vuosi',
bde.primary_language AS 'Kielikoodi',
bde.celia AS 'Daisy',
i.barcode AS 'viivakoodi',
SUBSTR(
i.cn_sort,
1,
INSTR(i.cn_sort, ' ') - 1
) AS 'Luokka',
SUBSTRING_INDEX(SUBSTRING_INDEX(i.cn_sort, " ",2), " " ,-1) AS 'pääsana'
FROM items i
LEFT JOIN biblioitems bi ON bi.biblioitemnumber=i.biblioitemnumber
LEFT JOIN biblio b ON i.biblionumber=b.biblionumber
LEFT JOIN koha_plugin_fi_kohasuomi_okmstats_biblio_data_elements bde ON i.biblionumber=bde.biblionumber
WHERE convert(i.homebranch using 'utf8') LIKE <<Kunta tai kirjasto esim. KOU% tai KOU_PK>>
Hankintadata
päivitetty 13.11.2023
SELECT aqi.itemnumber,
IFNULL(i.biblionumber, d.biblionumber) AS 'Biblionumber',
IFNULL(i.homebranch, d.homebranch) AS 'Kirjasto',
IFNULL(i.permanent_location, d.permanent_location) AS 'Hyllypaikka',
IFNULL(i.ccode, d.ccode) AS 'Kokoelma',
IFNULL(bibi1.itemtype, IFNULL(bibi2.itemtype, dbibi.itemtype)) AS 'Aineistotyyppi',
SUBSTR(
IFNULL(i.cn_sort, d.cn_sort),
1,
INSTR(IFNULL(i.cn_sort, d.cn_sort), ' ') - 1
) AS 'Luokka',
aq.entrydate AS 'Tilattu',
aq.datereceived AS 'Saapunut (tilaustaulu)',
IFNULL(format(i.price, 2,'fi_FI'), format(d.price, 2,'fi_FI')) AS 'Hinta niteissä',
IFNULL(i.booksellerid, d.booksellerid) AS 'Toimittaja',
IFNULL(i.notforloan, d.notforloan) AS 'Notforloan',
IFNULL (bde1.primary_language, bde2.primary_language) AS 'Kieli',
IFNULL (bde1.publication_year, bde2.publication_year) AS 'Julk. vuosi',
IFNULL (bde1.celia, bde2.celia) AS 'Daisy'
FROM aqorders_items aqi
LEFT JOIN items i ON (i.itemnumber=aqi.itemnumber)
LEFT JOIN aqorders aq ON (aq.ordernumber=aqi.ordernumber)
LEFT JOIN deleteditems d ON (d.itemnumber=aqi.itemnumber)
LEFT JOIN biblio b ON (b.biblionumber=i.biblionumber)
LEFT JOIN deletedbiblio dbi ON (dbi.biblionumber=d.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 koha_plugin_fi_kohasuomi_okmstats_biblio_data_elements bde1 ON (i.biblionumber = bde1.biblionumber)
LEFT JOIN koha_plugin_fi_kohasuomi_okmstats_biblio_data_elements bde2 ON (d.biblionumber = bde2.biblionumber)
WHERE aq.datereceived BETWEEN <<AloitusPvm|date>> AND <<LopetusPvm|date>> AND IFNULL(i.homebranch, d.homebranch) LIKE <<Kuntaosio ja prosenttimerkki>>
Poistodata
päivitetty 13.11.2023
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ä',
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 'Nimeke',
bde.publication_year AS 'julkaisuvuosi',
bde.primary_language AS 'Kielikoodi',
2023-SUBSTR(ExtractValue(bm.metadata,'//controlfield[@tag="008"]'),8,4) AS 'aineiston ikä',
2023-LEFT(di.dateaccessioned, 4) AS 'aineiston ikä (saapumisesta)',
bde.celia 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 tai kirjasto esim. KOU% tai KOU_PK>> AND di.timestamp BETWEEN <<AloitusPvm |date>> AND LopetusPvm:= <<LopetusPvm |date>>
Taustatietoja muista lähteistä
- Halutun alueen asukasmäärät (esim. kunnittain, postinumeroalueitttain, ikäryhmittäin)
- Postinumeroalueet: postinumerot.xlsx (postinumerot, postinumeroalueen nimi, kunta)
- Aikatietotaulu: aikatietotaulu.xlsx, jossa tarkasteltavan ajan päivämäärät, viikonpäivät, kuukaudet, vuodet omina sarakkeinaan
- Luokitustiedot: luokat_muotoluokat.xlsx
- Kielikoodit: kielikoodit.xlsx
- Aineistotyyppiryhmittelyt: Aineistoryhmät.xlsx
- Ikäryhmät: ikäryhmät.xlsx, 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])