Muistatko sen puhelun kun asiakas soittaa ja sanoo alkuun, että “Joo, et oo ollut tekemässä tätä mutta me ei just nyt saada ketään muuta kiinni ja olis ihan pakko tietää mikä tässä on vikana.” Saat käsiisi tabulaarisen mallin joka ei ehkä ole ihan kaikkien taiteen sääntöjen mukaan tehty ja yrität selvittää mitä se oikein tekee.
Tämän päivän pähkinä liittyy siihen. Kysymys kuuluu: Minkä luvun suure palauttaa kun valitsen filteriksi “Ship”? Eli mikä luku taulukkoon ilmestyy ja mille kuukaudelle.
Kyseessä FactInternetSales kanta, tarvittavat taulut ovat:
Näiden kahden taulun välillä on kolme relaatiota, joista OrderDateKey:n kautta oleva relaatio on aktiivinen. Passiivisina ovat ShipDateKey ja DueDateKey.
DateType taulu on seuraavanlainen:
Suure on:
Ja raportti on:
Mitä siis tapahtuu ylhäällä olevalle taulukolle kun valitsen slicerista “Ship”
Pähkinä #4 oli periaatteessa helppo. Ajatuksenani oli näyttää että “sort-by-column” – toiminnallisuus ei aina toimi. Tabulaarinen malli tarkastaa vain ensimmäiset 1000 riviä oikeellisuuden varalta ja jos data ei ole eheää sen jälkeen, voi tulla kummallisuuksia. Tässä esimerkkidatassa päivämääräkentissä on DayNumberOfWeek kentässä maanantain osalta virheitä, niin että se on 4, mutta tämä virhe on vasta n. 1100’s elementti taulun alusta, jolloin sort-by-column ei löydä virhettä kun sitä asetat. Sen sijaan kuvaaja muuttuu hassuksi.
Eli ennen muutosta kuvaaja näyttää:
Ja kun order-by-column muutoksen on tehnyt, näyttää se:
Taulu visualisointi näyttää virheellisen rivin ihan numeroina, pylväskuvaaja näyttää tyhjän pylvään.
Älä siis luota sort-by-columnin oikeellisuuteen ilman omaa tarkastusta jos sortattava joukko on iso.
Neljäs pähkinä on taas DAX:ia. Aika yleinen tarve raportoinnissa on saada esimerkiksi myynti per viikonpäivä tai myynti per kuukausi – tyyppinen suure. Monet käyttäjät haluavat nähdä nimet numeroiden sijaan, joten x-akselille pitää saada vaikka viikonpäivät tyyliin “maanantai”, “tiistai”, “keskiviikko” jne… Viikonpäivät eivät kuitenkaan ole luonnollisesti aakkosjärjestyksessä ja Power BI järjestelee elementit aakkosten mukaan oletuksena. Päivän tehtävänä on muokata oheista raporttia niin, että päivät menevät järjestykseen. Koska Microsoft Forms ei mahdollista liitetiedoston palauttamista, tulee sinun palauttaa kuvaus siitä miten ratkaisit ongelmaa.
Esimerkkiraportilla olen laittanut järjestyksen menemään viikonpäivän mukaan, mutta valitettavasti järjestys on aakkoksellinen ja käyttäjät haluavat maanantain ekaksi ja sunnuntain vikaksi. Tässä tehtävässä on tärkeää että kokeilet toimintoa itse raportilla, koska saatat vaikka oppia jotain uutta!
Raportti on direct query raportti. Jos Power BI kysyy käyttäjätunnuksia datalähteeseen, ovat ne:
Pähkinä #3 oli selkeästi joko tylsä tai vaikea… Vain 4 vastausta. Hauskimmassa vastauksessa oli “Copying with pride!” kommentti.
Ongelma ei ole helppo, mutta minun käyttämäni ratkaisu perustuu kahteen Power Queryn ominaisuuteen. Voit viitata aiempiin stepeihin ihan vain nimellä missä kohdassa skriptiä tahansa ja let … in -lauserakenteita saa olla useita sisäkkäin. Tämä mahdollistaa muuttujien käytön yhden stepin sisällä.
Avain on tuossa siniseksi maalatussa kohdassa. Siinä lisätään uusi sarake, joka on taulu aikaisemmasta vaiheesta. Lisäksi siinä käytetään muuttujia CurrentYear ja CurrentMonth, jotka pystytään luomaan let-rakenteella. Näiden avulla pystytään filteroimaan soluun lisättävää taulua niin että siinä on vain myynti kyseisen kuun loppuun saakka. Loppu onkin ihan käyttöliittymällä naksuttelua.
Maalatun vaiheen jälkeen tulosjoukko näyttää tältä.
Kolmas pähkinä, sama ongelma! Lupaan, nyt alkaa olla kielet käyty, ellei sitten mennä Python/R tai ehkä ADF:n dataflow:hun? Mutta tänään siis Power Queryllä tuttu kumulatiivinen summa ongelma. Lisätään hieman vaikeutta, ratkaisun tulee olla yksi kysely. Power Queryssä ei siis saa näkyä uusia kyselyitä. Steppejä tietty voi olla vaikka kuinka monta.
Kumulatiivinen summa Power Query kielellä
Pähkinänä on siis ratkaista miten Power Queryllä saadaan vastattua kysymykseen: “Myynti kunakin vuonna kyseisen kuukauden loppuun”. Summa siis kasvaa joka kuukausi, kunnes taas tammikuussa lähtee alusta.
Lähtödata on:
Ja kun olet tehnyt taikuudet, on tulosjoukko seuraavanlainen:
Näet että summa kasvaa aina vuoden ensimmäisestä kuukaudesta eteenpäin ja on isoimmillaan joulukuussa, jonka jälkeen se alkaa alusta.
Tehtävän suorittamista varten tarvitset oheisen Power BI Desktop tiedoston. Siinä on sinulle kysely “CumulativeSum” valmiina, jota voit muokata. Jos Power BI Desktop kysyy datalähteen oikeuksia, ovat ne vanhat tutut:
Palvelin: qsql.database.windows.net
Käyttäjätunnus (sql autentikaatio): joulu
Salasana: kalenteri123!
Tietokannan nimi: AdventureWorksLT (huomaa että käyttäjällä ei ole oikeutta muihin tietokantoihin, joten sinun tulee merkitä tietokanta jo yhdistämisvaiheessa).
Palautetuissa vastauksissa oli kahta päätyyppiä. Joko väliaikaistaulu with-rakenteella ja sitten tarvittava määrä kyselyitä siihen, tai sitten ehkä se SQL Server mäisempi ikkunafunktioiden käyttö. Hauskin vastaus oli kysely, jossa kommenteissa luki “Lopputulos on oikein, mutta poiminta meni spagetin puolelle!” Vastauksia tuli 14 kpl.
Minun esimerkkivastaukseni hyväksikäyttäisi ikkunointifunktioita, koska ne on erikseen optimoitu SQL Serverissä. Voisi siis olettaa niiden olevan hieman tehokkaampi tapa suurella tietojoukolla.
select [CalendarYear],[MonthNumberOfYear], convert(decimal,SUM(SalesAmount) OVER (PARTITION BY [CalendarYear] ORDER BY [MonthNumberOfYear]ROWS UNBOUNDED PRECEDING)) as CumulativeSum from (select [CalendarYear],[MonthNumberOfYear],SUM(SalesAmount) as SalesAmount from [dbo].[FactInternetSales] as f inner join [dbo].[DimDate] as d ON f.OrderDateKey=d.DateKey group by [CalendarYear],[MonthNumberOfYear]) as t
Toinen joulupähkinä on myös kumulatiivinen summa. Nyt SQL kielellä! Tarkoitus on saada sama tulosjoukko aikaiseksi, kuin ensimmäiselläkin kerralla, mutta nyt T-SQL Selectillä. Data on sama kuin ensimmäisellä kerralla, joten tulosjoukon tulisi näyttää samalta.
Tämä blogisarja on siis 24-osainen Microsoft Data joulupähkinä -sarja. Jokaisena joulukuun 24 ensimmäisenä päivänä tällä sivustolla julkaisen joko oman, tai toivottavasti myös muutaman vierailevan henkilön pulman Microsoft Data aiheista. Toivottavasti mahdollisimman moni katsoo pulman ja hieman miettii sitä, silloin tapahtuu hyviä asioita. Jos innostut pulmasta niin, että voisit jopa jakaa vastauksen, voit lähettää sen oheisella lomakkeella. Eniten pähkinöitä palauttaneet palkitaan Microsoftin lahjoittamilla joulumuistamisilla.
Kumulatiivinen summa SQL lauseessa
Pähkinänä on siis ratkaista miten SQL-lauseella saadaan vastattua kysymykseen: “Myynti kunakin vuonna kyseisen kuukauden loppuun”. Summa siis kasvaa joka kuukausi, kunnes taas tammikuussa lähtee alusta.
Lähtökysely on:
select [CalendarYear], [MonthNumberOfYear], SUM(SalesAmount) as SumSalesAmount from [dbo].[FactInternetSales] as f inner join [dbo].[DimDate] as d ON f.OrderDateKey=d.DateKey group by [CalendarYear],[MonthNumberOfYear] order by [CalendarYear],[MonthNumberOfYear]
Tämä kysely palauttaa myynnin per kuukausi.
Tämä tulisi kuitenkin saada sellaiseen muotoon, että SalesAmountisssa olisi kumulatiivinen summa. Tavoite tulosjoukko on siis:
Näet että summa kasvaa aina vuoden ensimmäisestä kuukaudesta eteenpäin ja on isoimmillaan joulukuussa, jonka jälkeen se alkaa alusta.
Tehtävän suorittamista varten tarvitset jonkin sovelluksen, jolla saat yhteyden SQL-palvelimeen. Hyviä vaihtoehtoja ovat Azure Data Studio tai SQL Server Management Studio.
Sinulla on käytössä palvelin seuraavilla tiedoilla:
Palvelin: qsql.database.windows.net
Käyttäjätunnus (sql autentikaatio): joulu
Salasana: kalenteri123!
Tietokannan nimi: AdventureWorksLT (huomaa että käyttäjällä ei ole oikeutta muihin tietokantoihin, joten sinun tulee merkitä tietokanta jo yhdistämisvaiheessa).
Sinulla on select-oikeudet tarvittaviin tauluihin, mutta muokkaamalla tuota esimerkki-selectiä saa kyllä oikean tuloksen aikaiseksi.
Pähkinään #1 tuli 19 vastausta, mikä on minusta ihan superhienosti! Kiitos. Sivulla yli 4 minuuttia viettäneitä ihmisiä oli 350 kpl, joten aika moni oli ehkä ajatellut tehtävää joka on ihan huippua sekin!
Yleisin vastaus oli oheisen mukainen:
Tämä on varmasti kaikkein helpoin tapa tehdä YTD-summa, koska siihen on valmis funktio ja olin ollut kiltti, kun olin merkinnyt aika-dimension valmiiksi malliin. Oheinen funktio ei toimi, jos aika-dimensiota ei ole merkitty “date table”:ksi.
Minulla on ollut monella asiakkaalla vähän kaikenlaisia kumulatiivisia summia ja usein ne eivät ala kvartaalin alusta, kuukauden alusta tai vuoden alusta. Tällöin se on pakko toteuttaa itse ja muutama oli ajatellut asiaa samalla lailla, kuin miten itsekin lähestyin esimerkkivastaustani. Eli lasketaan se ikkuna, josta summa tulee saada, sen jälkeen vapautetaan aikadimensio sopivasti ja sidotaan taas siihen laskemaamme ikkunaan.
Tämä näyttää ensimmäiseksi vähän monimutkaisemmalta, mutta antaa sinulle aika paljon vapauksia kun voit ihan itse määritellä sitten mistä se summa lasketaan jos asiakkaan tarve on jokin muu kuin sisäänrakennettujen avulla onnistuu. Mutta siis konsultti haluaa päästä vähällä ja ykkösvaihtoehto on varmasti lyhin 🙂
Joulukuu on alkanut! Ja samalla Microsoft Data joulupähkinäkalenteri on täällä. Ensimmäinen luukku liittyy yksinkertaiseen DAX-ongelmaan, joka on hyvin yleinen raportoinnissa: Kumulatiivinen summa.
Tarkoituksena olisi siis laskea ns. YTD-summa myynneistä. Mitä se siis tarkoittaa? Tarkoituksena on näyttää kunkin kuukauden kohdalla luku, joka edustaa myyntiä vuoden alusta ko. kuukauden loppuun saakka. Käytännössä siis jos tammikuussa on myyntiä 3 eur ja helmikuussa 2 eur ja maaliskuussa 1 eur, niin suure antaa tammikuulle 3 eur, helmikuulle 5 eur ja maaliskuulle 6 eur. Kuvaajalle piirrettynä siitä tulee nouseva viiva.
Lataa alhaalta harjoituksen Power BI Desktop tiedosto, muuta siinä oleva suure “KumulatiivinenSumma” (Joka nyt on kuvattu: SUM([SalesAmount]) ) sellaiseksi, että se vastaa kysymykseen: “Myynti vuoden alusta valitun kuukauden loppuun”. Käytännössä tiedostossa on yksi raporttisivu, jossa on yksi viivakuvaaja, joka näyttää oheiselta.
Suuretta tulisi muuttaa niin, että se palauttaa tällaisen kuvaajan:
Saat tehtävässä muuttaa ainoastaan “KumulatiivinenSumma” -suuretta. Palauta oheisella lomakkeella suureen koodi. Muista että eniten kuukauden aikana pulmia palauttaneet saavat Microsoftilta ja tarkemmin Jussi Roineelta yllätyspalkinnon!
Esimerkkivastaus julkaistaan pulman #2 julkaisun yhteydessä.
Suklainen joulukalenteri on aika helppo ja palkitseva. Aamulla sängystä, kalenterin katselu ja jäljellä olevasta suljettujen luukkujen joukosta pienimmän kokonaisluvun etsiminen. Sit luukku auki ja suklaa suuhun!
Voisko samaa tehdä Microsoft Data jutuilla? 24 helppoa pähkinää, mukava tunne kun saa pulman ratkaistua ja sit päivän muihin juttuihin?
Julkaisen tällä sivulla https://vesa.ws/blog 24 pähkinää, joulukuun 24 ensimmäisenä päivänä. Pulman ratkaisemiseksi voit joutua asentamaan jonkun maksuttoman sovelluksen, kuten vaikka Management Studion tai Power BI Desktopin, mutta mitään rahallista investointia ei tarvitse tehdä!
Jokaisessa pähkinässä on palautusosoite, jonne voit halutessasi pähkinän palauttaa. Mitään rekisteriä ei synny, tietoja ei luovuteta kenellekään ja tiedot poistetaan joulukuun loppuun mennessä.
Voit halutessasi jättää jonkun yhteystiedon jolla sinut saa kiinni (twitter tilin nimen, emailin, puhelinnumeron, tms… ). Jätä joka kerta samat tiedot kun palautat eri päivien tehtäviä ja 25. päivä sitten eniten palautuksia tehneille (tai jos eniten palauttaneita on kärjessä liian monta tasapistein arvotaan) lähtee Microsoftin lupaama Azure SWAG palkinto! Mitä paketissa on ei oo mullekaan ihan vielä selvää, mutta jotain mitä näissä yhteisötapahtumissa on aiemminkin jaettu 🙂
Tule siis käymään tällä sivulla 1.12. alkaen. Jos haluat notifikaation pulmasta itsellesi, julkaisen sen eri sosiaalisen median alustoilla myös, joten pistä seuraten tai tykäten tai miten ikinä missäkin alustassa se pitää tehdä. Koordinaatit ovat:
