Oletko nähnyt viime aikoina PowerApps demoa? Sellaista jossa tehdään hokkuspokkus parissa minuutissa liiketoimintasovellus, jonka tietovarastona on SharePoint?
Ne ovat upeita! Rakastan vauhtia, jonka PowerApps tuo ratkaisujen kehittämiseen. Näissä demoissa on kuitenkin yksi osa, joka tuntuu kaihertavan monia.
Kuka ääliö rakentaa liiketoimintasovelluksia SharePointin listojen päälle?
No esimerkiksi minä. SharePoint + PowerApps on usein aivan lyömätön yhdistelmä. Muttei välttämättä tilanteessa, jossa käsiteltävät tietomassat ovat suuria. Tämä ei kuitenkaan tee PowerApps:ista kelvotonta. Voit aivan hyvin käyttää tietotovarastona Common Data Serviceä (CDS). Tai jotain ihan oikeaa tietokantaa.
Epäilysten hälventämiseksi rakennetaan yksinkertainen PowerApps, jolla käsitellään kooltaan perusesimerkeistä poikkeavaa aineistoa. Vaikkapa miljoonaa riviä.
Esimerkki – Maan kaikkien ajoneuvojen tiedot
Ensin pitää löytää jostain mielekästä sisältöä. Tein aikanaan Power BI -esimerkin käyttäen Trafin avointa dataa. Aineistossa on mukana kaikki suomen ajoneuvot (henkilöautot, moottoripyörät, rekat, peräkärryt jne). Yhteensä 5,1 miljoonaa riviä.
Tehdään PowerApps, jolla haetaan ja ylläpidetään tämän aineiston tietoja.
Aineiston lataaminen tietokantaan
Tarvitsemme tietokannan, jossa tietoja säilytetään. Azure SQL on Microsoft:in tuotteilla pelatessa ilmeinen vaihtoehto. Luodaan sellainen.
Luodaan SQL-palveluun uusi tietokanta.
Avataan SQL Server Management Studio ja otetaan yhteys (Connect to server) juuri luomaamme tietokantaan.
Käynnistetään luodun kannan kohdalta tiedoston imurointi (Task -> Import Flat File).
Edetään kiltisti wizardia seuraamalla. Tietojen esikatselu näyttää lupaavalta.
Vinkki: Jotta imurointi onnistuu, joudut muuttamaan lähes kaikkien sarakkeiden tietotyypiksi nvarchar(sopivaluku). Laiskana käytin pääsääntöisesti tyyppiä nvarchar(MAX).
Pienen odottamisen jälkeen csv-tiedoston sisältö on imuroitu tietokantaan.
Lopputulosta pitää hieman siivota
- Tuhotaan kaikki ylimääräisiksi katsomamme sarakkeet pois (variantti, versio, alue, vaihteiden lukumäärä jne jne)
- Korjataan jäljelle jääneiden sarakkeiden tietotyypit oikeiksi
- Määritellään jarnro-sarake ensisijaiseksi avaimeksi (primary key)
- Luodaan indeksit kentille merkki ja matkamittarilukema.
Siivouksen jälkeen tietokannan tulisi näyttää suurin piirtein tältä.
Ja kyllä. Rivejä löytyy reilu 5 miljoonaa.
Rivimäärän vuoksi on tärkeää määritellä indeksit sarakkeille, joita käytetään hakuehtoina. Voit toki kokeilla miten PowerApps toimii ilman niitä. Kokemus kehittää lähinnä hermoja.
Ajoneuvorekisteri – PowerApps
Luodaan tyhjä powerapps. Käytetään tabletti-asettelua.
Tietolähteen lisääminen
Lisätään uusi tietolähde (datasource). Valitaan SQL server ja annetaan tietokantamme osoite ja nimi.
Kun yhteys on muodostettu valitaan käytettävä taulu (table). Valmista! Tai melkein. Puuttuu ainoastaan käyttöliittymä.
Gallerian lisääminen
Aloitetaan lisäämällä galleria-kontrolli (Insert -> Gallery), jossa näytetään joukko ajoneuvoja. Määritellään gallerian tietojoukoksi (items) juuri lisäämämme tietokantataulu. Esimerkissämme se on nimeltään dbo.Autot_All.
Asetataan gallerian ominaisuuksista jokaisesta rivistä näkymään seuraavat arvot:
- merkkiselvakielinen
- matkamittarilukema
Nyt meillä on galleria, joka näyttää kaikki ajoneuvot. Siihen ei haeta kaikkia 5 miljoonaa riviä kerrallaan vaan tavaraa haetaan 100 rivin erissä. Käyttäjän vierittäessä listaa alaspäin, hakee PowerApps siihen automaattisesi lisää rivejä.
Ajoneuvon hakeminen järjestysnumerolla
Tuskin kukaan on kiinnostunut scrollaamaan läpi viittä miljoonaa riviä. Toteutetaan siis pari hakua.
Lisätään hakukenttä, johon käyttäjä syöttää ajoneuvon järjestysnumeron. Suodatetaan tämän perusteella galleria näyttämään ainoastaan ajoneuvo, jolla on kyseinen järjestysnumero.
Filter('[dbo].[Autot_All]', jarnro = Value(txtInputJarjnro.Text))
Järjestysnumero on tietokannassa numeerinen kenttä. Tämän vuoksi myös tekstilaatikon arvo pitää muuntaa numeroksi (Value-funktio).
Jarnro -sarake on tietokantataulussamme avaimena ja haku on todella nopea.
Ajoneuvon hakeminen merkillä ja mittarilukemalla
Pelkkä järjestysnumerolla haku on vähän tylsää, joten lisätään hakukentät myös merkille ja matkamittarilukemalle (min ja max).
Uusilla kentillä aineistoa voi suodattaa seuraavasti.
Filter('[dbo].[Autot_All]',
matkamittarilukema > Value(txtInputmittariMin.Text),
matkamittarilukema < Value(txtInputmittariMax.Text),
merkkiSelvakielinen = txtInputMerkki.Text
)
Myös merkki ja matkamittarilukema ovat tietokannassa indekseinä. Hakutulos räpsähtää ruutuun edelleen varsin nopeasti (< 1 sek).
Käyttöliittymän siistiminen
Perusasioiden toimiessa voimme viilata hetken sovelluksen ulkoasua ja toiminnallisuuta.
- Tarjotaan käyttäjälle kaksi erilaista hakutapaa:
- Haetaan tiettyä ajoneuvoa järjestysnumerolla
- Haetaan autojoukkoa joko merkillä, mittarilukemalla tai näiden yhdistelmällä
- Muokataan gallerian filtteriä siten että se toimii vaikkei automerkkiä ole syötetty
- Lisätään hakutulokseen sopiviksi katsomamme sarakkeet, sekä niitä vastaavat otsikot
Lopputulos näyttää tältä.
Tietojen muokkaaminen
Tehdään vielä pikaisesti oma näyttö tietojen muokkaamiselle. Insert -> Forms -> Edit. Asetetaan tietolähteeksi (datasource) käyttämämme tietokantataulu ja käsiteltäväksi riviksi (item) hakutuloksesta valittu rivi (Gallery.SelectedItem).
Jälleen pienen siistimisen jälkeen lomake näyttää tältä.
Tallenna-painikkeella tallennetaan lomakkeen olevat tiedot.
SubmitForm(Form1);Notify("Muutokset tallennettu",NotificationType.Success);Navigate(SearchResults,ScreenTransition.None)
Lisätään lopuksi hakutulosriveille ikoni josta kyseistä riviä pääsee muokkaamaan.
Ikonin OnSelect -arvoksi asetetaan.
Select(Parent);Navigate(Edit,ScreenTransition.None)
Ratkaisu on valmis. 5 miljoonan rivin aineistoa voi hakea ja päivittää alle sekunnin vasteajoilla.
Sovellus on myös heti käytettävissä loppukäyttäjien mobiililaiteissa (mikäli laitteessa on PowerApps asennettuna).
Ei tämä nyt ihan surkeaa ole.
Mutta…
Isojen rivimäärien kanssa saa olla tarkkana. Ettei tee mitään hölmöä.
Haluamme suodattaa ajoneuvoja ajoneuvoluokan perusteella. Lisäämme hakulomakkeelle alasvetovalikon, joka saa arvokseen kaikki mahdolliset ajoneuvoluokat.
Sort(Distinct('[dbo].[Autot_All]',ajoneuvoluokka), Result, Ascending)
Ongelma on se, että alasvetovalikkoon ilmestyy 18 valintaa. Kun ajamme vastaavan komennon suoraan SQL Manangement Studiossa, saamme ulos 40 eri ajoneuvoluokkaa.
Syy on tähän yksinkertainen. PowerAppsin distinct-funktiota ei delegoida. Lauseemme hakee 500 ensimmäistä riviä (viidestä miljoonasta) PowerAppsiin ja etsii niiden seasta yksittäiset ajoneuvoluokat. 500 ensimmäisen rivin joukosta niitä nyt sattuuu löytymään 18.
Voimme käydä kasvattamassa ei delegoitavien kyselyjen palauttamien rivien määrää maksimiin (2000).
Jolloin dropdowniin ilmestyy 22 arvoa. Sentään yli puolet, mutta ei kaikkia.
Ratkaisu on yksinkertainen. PowerApps tukee SQL Serverin näkymiä (views). Tehdään näkymä, joka palauttaa kaikki mahdolliset ajoneuvoluokan arvot.
Näkymä pitää lisätä omaksi tietolähteekseen.
Jonka jälkeen sitä voi käyttää.
Lisätään uusi hakuehto filtteriin ja näin olemme lisänneet uuden hakukriteeerin ratkaisuumme!
Tässä kohtaa on hyvä käydä lisäämässä myös ajoneuvoluokka -sarakkeelle tietokantaan indeksi. Jos käynnistät PowerAppsin ilman sitä, huomaat kyllä miksi.
Yhteenveto
Olemme tehneet nopeasti yksinkertaisen rekisterisovelluksen, jolla voi hakea rivejä 5 miljoonan joukosta ja tehdä päivityksiä niiden tietoihin. Mitättömällä lisävaivalla voisimme lisätä valittujen rivien poistamisen ja uusien rivien lisäämisen.
Vasteajat ovat kelvollisia ja ratkaisu on helposti ja hallitusti jaeltavissa loppukäyttäjien mobiililaitteisiin.
Ei tämä nyt ihan huono ole.
Timo Pertilä 