Elokuussa avautui mahdollisuus tehdä DAX-kyselyjä Power BI:n REST API:lla (preview).

Kiva. Entäs sitten?

Ajoittain tulee eteen tilanteita, jolloin olisi kätevää hakea valmiiksi laskettuja lukuja Power BI:n tietomallista. Meillä töissä työaikasaldot on laskettuna Power BI tietomalliin. Mutta saldo olisi hyödyllistä nähdä myös tuntikirjaus Power Appsissa.

Miten tämän voisi toteuttaa?

Power BI tiilen upottaminen Power Appsiin

Helpointa on luoda Power BI:llä raportti, jonka yhtenä elementtinä on työntekijöiden yhteenlasktut saldot.

Julkaistaan raportti Power BI -palveluun ja lisätään saldo-lukema tiileksi (tile) dashboardille.

Lopuksi lisätään Power Appsiin Power BI tile -kontrolli (1) ja määritelläään sen tietolähteeksi (Data, 2) luomamme tiili (3). Lopputuloksena tiilen sisältö ilmestyy sovellukseen.

Tiilessä näytetään kaikkien työntekijöiden yhteenlaskettu saldo. Vaihdetaan tiili käyttämään uutta Power BI API:a.

Jolloin voimme suodattaa tiilen sisältöä url-parametreilla. Sovelluksen käyttäjälle näytetään hänen oma saldonsa. Suodattimena käytetään siis työntekijän sähköpostiosoitetta.

Helppoa.

Ongelmana on kuitenkin ulkoasun sovittaminen. Tiili näyttää samalta kuin Power BI -palvelussa. Eikä se välttämättä istu sovelluksen käyttöliittymään.

Tämä ratkeaa hakemalla työntekijän saldo DAX-kyselyllä, jolloin sen voi näyttää käyttöliittymässä juuri siten kuin haluaa.

Haetaankin työntekijän saldo suoraan tietomallista DAX-kyselyllä.

DAX-kyselyn tekeminen Power Automatella

Power BI:n REST API:a voi käyttää käyttäjällä (user) tai sovelluksella (Service Principal). Käytetään jälkimmäistä vaihtoehtoa.

Service Principalin luominen

Meillä ei ole tarvittavaa rekisteröityä sovellusta (application, toiselta nimeltään Service Principal), joten käydään Azure portaalissa (Azure Active Directoryn alla) rekisteröimässä sellainen.

Annetaan sovelluksellemme nimi.

Luodaan sovellukselle salaisuus (secret) ja laitetaan se talteen.

Luodaan AAD-ryhmä Power BI Admins ja lisätään sovellus sen jäseneksi.

Power BI -palvelun asetukset

Siirrytään Power BI -palvelun hallintaportaaliin ja varmistetaan että tenantin asetuksissa (Tenant settings) on API:n käyttö sallittu service principaleille. Rajataan käyttöä AAD-ryhmien avulla ja lisätään sallittujen ryhmien joukkoon luomamme ryhmä.

Käyttämämme service principalin tulee olla jäsen työtilassa, jossa kyselyn kohteena oleva tietojoukko (dataset) sijaitsee.

Valmistelut on tehty. Sitten itse asiaan.

Saldon hakeminen flow’lla

Luodaan flow, joka käynnistyy Power Appsista. Se saa parametrina käyttäjän sähköpostiosoitteen.

Tokenin muodostaminen

Ensimmäiseksi haemme http-toiminnon avulla tokenin Service Principalille tokenin. Sitä käytetään tunnistautumisessa. Tarvitsemme

  • Tenantin id:n
  • Service Principalin id:n
  • Service Principalin salaisuuden (secret)

Kahta jälkimmäistä ei kannata ikinä tallentaa flow’n toimintoihin (kuten alla olen tehnyt). Katsotaan joskus myöhemmin, miten tämä oikeasti tehdään.

Toiminnon Body-osuutena on 

grant_type=client_credentials
&resource=https://analysis.windows.net/powerbi/api
&client_id=xxxxxxxxxxxxxxxxxxxxx
&client_secret=xxxxxxxxxxxxxxxxxxxxx
&scope=https://analysis.windows.net/powerbi/api/.default

Tämän jälkeen parsitaan paluuarvo (Body) Parse JSON -toiminnolla.

Käytettävä skeema on seuraava.

{
    "properties": {
        "access_token": {
            "type": "string"
        },
        "expires_in": {
            "type": "string"
        },
        "expires_on": {
            "type": "string"
        },
        "ext_expires_in": {
            "type": "string"
        },
        "not_before": {
            "type": "string"
        },
        "refresh_token": {
            "type": "string"
        },
        "resource": {
            "type": "string"
        },
        "scope": {
            "type": "string"
        },
        "token_type": {
            "type": "string"
        }
    },
    "type": "object"
}

DAX-kyselyn generointi Power BI Desktopilla

Seuraavaksi tarvimme suoritettavan DAX-kyselyn. Mikäli Power BI -raportilla on visualisointi, joka näyttää haluamasi arvot, on homma helppo.

  • Avataan raportti Power BI desktopilla, tehdään halutut suodatukset ja siirrytään View-välilehdelle (1)
  • Avataan Performance analyzer (2) ja sieltä edelleen avautuvasta panelista Start recording (3)
  • Päivitetään visualisointien sisällöt (4) ja kopioidan halutun visualisoinnin alta DAX-kysely Copy query (5)

Tallennetaan DAX-kysely laadi (compose) -toimintoon.Vaihdetaan samalla suodattimena käytetty sähköpostiosoite flow’n parametrina saamaan.

DAX-kyselyn suoritus

Seuraavaksi voimme suorittaa varsinaisen kyselyn. Tähän tarvitsemme tietojoukon (dataset) tunnisteen. Se löytyy Power BI -palvelusta.

Huomaa että token_typen ja access_tokenin välissä on välilyönti!

Paluuarvokin näyttää oikealta.

{
  "results": [
    {
      "tables": [
        {
          "rows": [
            {
              "[SumHours]": 36
            }
          ]
        }
      ]
    }
  ]
}

Kaivetaan vielä se varsinainen arvo tuolta esiin. Käytetään jälleen Parse JSON -toimintoa.

Skeemana toimii seuraava.

{
    "type": "object",
    "properties": {
        "results": {
            "type": "array",
            "items": {
                "type": "object",
                "properties": {
                    "tables": {
                        "type": "array",
                        "items": {
                            "type": "object",
                            "properties": {
                                "rows": {
                                    "type": "array",
                                    "items": {
                                        "type": "object",
                                        "properties": {
                                            "[SumHours]": {
                                                "type": "integer"
                                            }
                                        },
                                        "required": [
                                            "[SumHours]"
                                        ]
                                    }
                                }
                            },
                            "required": [
                                "rows"
                            ]
                        }
                    }
                },
                "required": [
                    "tables"
                ]
            }
        }
    }
}

Tiedämme että paluuarvo sisältää ainoastaan yhden rivin, jolla on yksi arvo. Eli haluamme poimia ensimmäisestä tulosjoukosta (results) ensimmäisen taulukon (tables) ja sieltä ensimmäisen rivin (rows) saldon (SumHours).

Kaavana se menee näin.

first(first(first(body('Parse_JSON_2')?['results'])['tables'])['rows'])['[SumHours]']

Tämä luku palautetaan flow’ta kutsuneelle Power Appsille.

Flow näyttää kokonaisuudessaan tältä.

Power Apps

Kutsutaan tekemäämme flow’ta Power Appsista. Asetetaan flow’n paluuarvo muuttujaan (MyWorkBalance).

Set(MyWorkBalance, GetMyHourBalance.Run(varCurrentUser).hourbalance)

Näin voimme esittää saldon haluamallamme tavalla käyttöliittymässä.