Sähkön hinta ja riittävyys ovat olleet syksyn kuumia puheenaiheita. Nyt onkin oiva hetki miettiä myös kodin valaistusta. Viekö se paljon energiaa? Onko meillä turhaan valoja päällä? Jos on niin missä?

Miten ylipäätään valaisimia kotona käytetään?

Jotta mitään analyysia voidaan tehdä, tarvitaan dataa. Kotimme valaistus on hoidettu Philipsin Hue -älyvaloilla, joten tarvittavan tiedon kerääminen on mahdollista.

Tällä kertaa teemmekin ratkaisun, joka

  • hakee säännöllisesti kaikkien valaisimien tilatiedot sekä sähkön tuntikohtaisen pörssihinnan
  • tallentaa ne jonnekin
  • muodostaa syntyneestä tiedosta käppyröitä

Kaikki tämä tietenkin Power Platform -työkaluilla.

Valaisinten tilatietojen hakeminen

Huen API:n käyttö on yksinkertaista, mikäli sitä kutsutaan Hue-tukiaseman kanssa samassa verkossa olevalta laitteelta. Mikäli API:a kutsutaan muualta, homma on hieman monimutkaisempaa. Toki tehtävissä.

Mutta miksi nähdä vaivaa, kun joku on sen jo nähnyt puolestamme? Mainio Tomasz Poszytek on nimittäin julkaissut oman yhdistimen, jolla voi käyttää Hue:n API:a flow’lla!

Yhteyden luominen

Luodaan flow ja lisätään siihen Philips HUE -yhdistin. Mutta mistä saan tarvittavan avaimen (HUE application key)?

Avataan puhelimen Hue App ja sieltä asetukset – > Oma Hue-järjestelmä -osioista tukiaseman ip-osoite.

Tämän jälkeen avataan (koneella joka on samassa verkossa Hue-tukiaseman kanssa) selaimella osoite http://tukiasemanip/debug/clip.html.

Avautuu API debugger.

Uusi avain luodaan lähettämällä (POST) sanoma tukiasemalle. Sanoma sisältää devicetype:n, jonka arvo on käyttäjän nimi.

{"devicetype":"Youruser/device name here"}

Emme kuitenkaan saa vastauksena avainta, ellemme ennen viestin lähettämistä paina Hue-tukiaseman painiketta.

Painikkeen painamisen jälkeen kutsu palauttaa tarvitsemamme avaimen (username).

Tämä avain annetaan yhdistimille.

Jonka jälkeen vielä tunnistaudutaan Hue-tunnuksella. Eli sillä samalla, millä olet kirjautunut Hue-sovellukseen.

Tämän jälkeen saamme Get Lights -toiminnolla haettua kaikki kodin valot ja niiden tilatiedot (onko päällä vai ei, millä kirkkaudella, mikä värisävy on asetettuna jne jne).

Voimme aloittaa varsinaisen ratkaisun rakentamisen!

Tietomalli

Ratkaisussamme on kahdentyyppistä tietoa

  • Pysyvää tietoa (lamput, huoneet, tuotteet)
  • Jatkuvasti kertyvää tilatietoa valaisimista ja sähkön hinnasta

Pysyvä tieto on kätevä tallentaa Dataverseen. Jatkuvasti kertyvää informaatiota taas ei kannata tallentaa Dataverseen. Katsotaan sille toinen koti.

Pysyvät tiedot (Dataverse)

Luodaan seuraavat taulut

  • Tuote (Hue Product) – Sisältää tiedon esim tietyn Hue-valaisimen energian kulutuksesta
  • Huone (Room) – Hue-valaisimet voidaan jakaa loogisesti eri huoneisiin. Taulu pitää sisällään näiden huoneiden nimet.
  • Valaisin (Hue Lamp) – Hue-järjestelmään liitetyt valaisimet. Kukin valaisin liittyy huoneeseen ja tuotteeseen.

Tehdään samalla mallipohjainen sovellus, jolla tietoja voi ylläpitää.

Lisätään kotoa löytyvät Hue-tuotteet käsin tuote-tauluun. Philipsin sivuilta löytyy kunkin valaisimen max virrankulutus (Power, w).

Huoneet ja valaisimet lisäämme luonnollisesti flow’lla.

Valaisimien ja huoneiden hakeminen flow’lla

Luodaan flow, joka hakee ensin kaikki valaisimet (Get lights). Käydään tulosjoukko läpi (Apply to each) ja lisätään jokainen valaisin Dataverseen. Lisäys tehdään upserttina, jolloin valaisimella on Dataverseessä sama tunniste kuin Hue:ssa. Näin valaisimen päivittäminen voidaan tehdä ajamalla sama flow uudelleen.

Tämän jälkeen haemme kaikki huoneet (Get Rooms) ja lisäämme ne Dataverseen. Jälleen upserttia hyödyntäen.

Kunkin huoneen kohdalla päivitämme kaikki huoneen valaisimet liittymään ko huoneeseen.

Flow’n suorittamisen jälkeen joudumme vielä käsin linkittämään kunkin valaisimen oikeaan Hue-tuotteeseen. Mutta sen jälkeen olemmekin valmiit.

Valaisimien tilatiedot (SQL)

Haluamme tallentaa 10 minuutin välein tiedon valaisimien tilasta (onko päällä, mikä on kirkkaus). Dataa kertyy paljon, joten emme halua tallentaa sitä Dataverseen.

Mutta minne se sitten tallennettaisiin?

Järkevintä varmaan olisi tallentaa tiedot JSON-muodossa Azuren blobiin. Niistä voisi halutessaan Data Factoryllä muodostaa vaikka päiväkohtaisia summatiedostoja.

Tallennetaan tiedot kuitenkin suoraan SQL-kantaan, sillä haluan näyttää yhden tempun josta voi olla hyötyä.

Luodaan valaisimen tilatiedoille taulu SQL-kantaan (HueLampUsage). Sarakkeet ovat

  • Id – Rivin tunniste
  • Brightness – Valon kirkkaus (0-100%)
  • Time – Tarkka aika milloin tilatiedot on haettu
  • Hour – Tunti jolloin tilatiedot on haettu
  • LampGUID – lampun tunniste
  • LightState – Oliko valaisin päällä vai ei

Perinteinen tapa tallentaa tiedot flow’lla tietokantaan on seuraava.

  • Haetaan kaikki valaisintiedot
  • Tallennetaan yksi kerrallaan kunkin valaisimen tiedot kantaan

Hidasta ja kuluttaa vuorokaudessa merkittävän määrän Power Patform -pyyntöjä.

Tietojen tallennus tallennetulla proceduurila (Stored procedure)

Voimme kuitenkin luoda uudet rivit flow’n sijasta SQL-palvelussa (kiitos vinkistä Lasse!).

Luodaan tietokantaan tallennettu proceduuri, joka saa parametrina valaisintiedot JSON-muodossa. Proceduuri käy JSON:in sisällön läpi ja luo sen pohjalta rivit tietokantaan.

CREATE PROCEDURE AddHueLampStatuses
(
    @json NVARCHAR(max)
)
AS
BEGIN
INSERT INTO dbo.HueLampUsage([LampGUID],[Brightness],[Time],[Hour],[LightState])
    SELECT
        LampGUID,Brightness,Time,Hour,LightState
    FROM OPENJSON(@json)
    WITH (
		LampGUID uniqueidentifier '$.LampGUID',
		Brightness float '$.Brightness',
		Time datetimeoffset(7) '$.Time',
		Hour datetimeoffset(7) '$.Hour',
		LightState bit '$.LightState'
    ) AS jsonValues
END
GO

Muutetaan flow’ta siten että muodostetaan (Select-toiminnolla) oikean sisältöinen JSON ja suoritetaan tallennettu proceduuri.

Supernopeaa ja tallennus vie ainoastaan yhden Power Platform -pyynnön.

Sähkön pörssihinnan hakeminen ja tallennus

Haluamme tarkastella valaisimien tuntikohtaista kulutusta sekä kyseisen tunnin sähkön markkinahintaa. Luodaan sähkön spot-hinnalle oma taulu (EnergySpotPrice), johon tallennetaan joka ilta seuraavan päivän sähkön pörssihinnat.

Seuraavan päivän hinnat saa noudettua helposti osoitteesta
https://api.spot-hinta.fi/DayForward. Haetaan ne flow’lla ja tallennetaan valaisintietojen tapaan tallennetulla proceduurilla.

Tarvittava aineisto alkaa olla kasassa.

Power BI -raportti

Ladataan tiedot Power BI:hin ja muodostetaan niiden pohjalta tietomalli.

Lisätään HueLampUsage -tauluun sarake, joka kertoo lampun virrankulutuksen kyseisellä hetkellä. Valaisimen ollessa pois päältä se on Hue-lampun lepotilan maksimikulutus (0.5w). Mikäli valaisin on päällä, kerrotaan sen maksimi virrankulutus kirkkaudella (0-100%). Eihän se mikään totuus ole, mutta joku arvio sentään.

Current Power = Max( IF(HueLampUsage[LightState] = TRUE(),(HueLampUsage[Power] * HueLampUsage[Brightness])/100,0.5),0.5)

Valaisimet sisältävät myös ohjattavat pistorasiat. Niitä emme halua mukaan, joten lisäämme tietomalliin uuden sarakkeen (ignore), jolla suodatamme pistorasiat pois Power BI:n tietomallista.

Lyhyen nykertämisen jälkeen ensimmäinen raportti on valmis. Näemme siitä esimerkiksi

  • Miten moni valaisin on ollut päällä tunnin aikana (Count of lights (turned on))?
  • Paljonko minäkin ajanhetkenä valaistukseen kuluu energiaa (Energy consumption, w)?
  • Paljonko minäkin ajanhetkenä on ollut sähkön pörssihinta?
  • Paljonko valaisimia eri huoneissa on päällä valitulla hetkellä?
  • Paljonko valitun huoneen/huoneiden valaisimien energiankulutus on tiettynä hetkenä?

Yhteenveto

Tämä oli mielenkiintoinen harjoitus. Jos lähtisin toteuttamaan tätä oikeasti, tekisin asiat hieman toisin. Idea oli kuitenkin saada vain haettua dataa ja katsottua, voiko sen pohjalta tuottaa mitään mielekästä raportointia.

Kyllä voi.

Opinko mitään? No sen, ettei kodin led-valaistuksella ole valtavaa merkitystä sähkölämmitteisen asunnon kokonaiskulutuksessa. Käppyrät näyttivät myös miten iso osa iltaisesta valaisimen viemästä sähköstä menee ulkovaloihin. Ehkä pärjäämme hieman kevyemmällä pihavalaistuksella tänä talvena.

Päivisin meillä on myös turhan paljon valoja päällä. Sitäkin tulen hieman viilaamaan.