Themabewertung:
  • 0 Bewertung(en) - 0 im Durchschnitt
  • 1
  • 2
  • 3
  • 4
  • 5
externe my-Gekko Dashboards
#1
Lightbulb 
Hallo zusammen,

um mal wieder etwas in die private Entwicklung einzusteigen, habe ich mich mal einem neuen Projekt gewidmet. Aber was liegt da näher, als irgendwas rund um den Punkt Hausautomatisierung und mygekko zu machen?

Aus diesem Grund habe ich mich entschieden, eine optimierte Dashboard Übersicht zu erstellen. Am Gekko hat man ständig das Problem, dass man immer nur einen kleinen Teil von Auswertungen/Charts/Tabellen oder ähnliches sieht. Man muss ständig in einzelne Punkte springen, um sich Trends anzeigen zu lassen. Nochmal zwei weitere Klicks, um sich bspw. Tabellarische Sichten anzeigen zu lassen. Das finde ich etwas nervig auf Dauer.

Die beiden Screenshots habe ich erstmal als Exemplar entworfen, ob die so für mich bleiben, muss ich noch schauen.
   
Dies zeigt aktuell die Werte des Lastabwurfs und die Wetterinformationen als Graphen Visualisierung.

   
In diesem Bild werden die aktuellen Jahresverbräuche dargestellt. Für Strom/Gas habe ich mich für eine Art Manometer Darstellung entschieden, mit entsprechenden Schwellwerten.
Zusätzlich gibt es noch eine Jahresübersicht der Stromkosten und Verbräuche, ab 2015 aufsteigend.

Natürlich ist alles abbildbar, man muss nur Phantasien dafür haben :-D ... und natürlich die Zeit.

Der vielleicht spannendste Punkte: Wie habe ich das realisiert?
- Die Trend- und Listendaten liegen bei mir im Netzwerk auf einer NAS. Das ist eigentlich der Hauptpunkt, um schnell und immer an die Daten zu kommen.
- Zudem hat mir Santos vor kurzen das Tool TrendyTranslator zur Verfügung gestellt, da die Zip-Datei direkt von der Gekko-Homepage nicht funktionierte.

Realisiert habe ich das ganze in der Azure Cloud auf einer VM. Das aber auch nur, weil es mir an Virtualisierungsmöglichkeiten im heimischen Netzwerk mangelt. Die NAS gibt es nicht her und auf einem Raspi war die Umsetzung nach meinen Vorstellungen leider nicht möglich.
Basis ist eine Microsoft SQL Server Datenbank 2017. Kleinere Versionen funktionieren im Moment nicht, da ich Funktionen verwende, die erst aber dieser Version zur Verfügung stehen.
Vorteil: Es gibt eine Express Edition, die sich jeder installieren kann und völlig ausreichend ist.

Wie aber kommen die Daten vom Gekko in die SQL-Datenbank?
Kurz gesagt mittels Microsoft SQL Server Integration Service. Vorteil: Die erzeugten DTSX-Packages können exportiert und auf jedem anderen Server installiert bzw. ausgeführt werden (glaube bei Express kann man die nur mittels Kommandozeile ausführen).

Wie werden die Daten zur Zeit visualisiert?
Ich habe mich für Grafana entschieden. Diese Software ist frei und es gibt relativ viele Plugins, um weitere Auswertungen von anderen Komponenten aus dem Heimischen Netzwerk zu machen. Da dies aber bei mir aktuell nur in der Cloud läuft, ist das für mich nur ein Nice To Have, sobald ich das Projekt zu mir holen kann.
Man kann auch andere Software verwenden, sofern eine Schnittstelle zum SQL-Server gibt.
Leider gibt es m.W. keine Möglichkeit, auch Aktionen wie bei Node-Red auszuführen, so könnte man ggf. auch Lichter einschalten oder Aktionen steuern. Oder kennt da jemand Tools, die beides können?

Warum Microsoft SQL Server und SSIS als Applikation und nicht eine WebApplikation um portabel zu bleiben?
Weil SQL mein täglich Brot ist und ich mich darin heimisch fühle. Der SQL-Server bietet einfach so viele Möglichkeiten.

Ich mache hier jetzt erstmal Schluss. Falls Details über die eigentliche Umsetzung gewünscht sind, lasst es mich wissen. Ich gebe gerne Auskunft. Dürft mich auch gerne mit anderen Fragen - sofern welche da sind - löchern.

Falls Bedarf besteht, gebe ich gerne in den nächsten Tagen eine erste Version frei. Ich muss noch ein wenig an den Package-Parametern arbeiten und auch noch einen FTP-Download integrieren, damit ich die Daten aus dem heimischen Netz in die VM bekomme.

Viele Grüße
Frank
#2
Hallo Frank,

hiermit melde Interesse für die erste Version an.
Sehr interessantes Thema, stark!
Viele Grüße

Francisco Santos

myGekko im Einfamilienhaus  Tongue
#3
Frank

Booooombe

das ganze für die PV anlage und über den Raspy am HDMI auszugeben ...
Und ich hätte eine Fertige Anzeige unserer einspeissung für den Monitor im Eingang .....
Evtl noch die Wetterdaten mit einbeziehn und Spass haben

LG
Benny

P.S.
Hab leider gerade wenig zeit um Aktive mitzuhelfen ....
Bin aber gerne in Auszugen dabei
#4
i see lot of datalagger InfluxDb+Grafana on NAS or Raspberry (with db moved on usb), i have one up an running too (dietpi on Pi3b+ with homeassistant)
so i'm very curious about "Kleinere Versionen funktionieren im Moment nicht, da ich Funktionen verwende, die erst aber dieser Version zur Verfügung stehen" .
may you post few code/function ? tnx
All my post are from Google Translator. Hope it can be understand.  Angel
Greetings from Veneto
MyGekko-nodered-HomeAssistant

#5
Hallo santos, hallo Benny,
ich denke ich brauche noch eine Woche für den ersten Prototyp. Ich habe gerade Performance Probleme beim Übertragen der Daten in die Zieltabellen.
Mein aktuelles vorhaben, die Daten abzugleichen muss ich wohl leider verwerfen, da dies einfach viel zu lange dauert. Ich werde diesen Step umbauen, so dass ich die Inhalte immer komplett neu reinschreibe. Birgt aber die Gefahr, wenn etwas schief geht, dass nicht einmal alte Daten zur Verfügung stehen.
Das werde ich aber nochmal genau erklären (wie der ganze Aufbau funktioniert), wenn die erste Version fertig ist.
Dafür habe ich aber die Parametrisierung angepasst und auch einen ftpDownload (aber nur im Zusammenhang mit WinScp) implementiert. Zudem sind einige Bugfixes durchgeführt worden.

Ich halte euch auf dem laufenden ... ;-)

@Marco:
"Kleinere Versionen funktionieren im Moment nicht, da ich Funktionen verwende, die erst aber dieser Version zur Verfügung stehen"
=> Das bezieht sich nur auf den SQL Server. Ich nutze Standard Funktionen wie STRING_SPLIT, diese stehen erst ab SQL Server 2016 zur Verfügung. Daher läuft mein Code vorerst nicht mit kleineren SQL-Server Versionen.
#6
Hallo Frank,

Hinweis:
die Trends und Listen können auch über die API abgerufen werden, Infos findest Du dazu im Handbuch ab der Seite 10.

Query API Handbuch

Euer myGEKKO Team
Rupert Jud
#7
Hallo Rupert,

Danke für deinen Hinweis. Am Anfang hatte ich schon mit dem Gedanken gespielt, die Daten via API abzufragen und abzuspeichern.
Ich wollte aber die Möglichkeit haben, das System auch extern, bspw in der Cloud betreiben zu können.
Mir ist bewusst, dass ich auch extern das Gekko abfragen kann, wollte aber keine verpflichtende Remote Anbindung des Gekkos voraussetzen.
Außerdem bin ich der Meinung, das ich bei dem jetzt gewählten Weg Recht flexibel bin, falls neue Felder oder neue Werte hinzukommen.

Man soll aber nichts ausschließen, vielleicht findet sich ja noch ein Anwendungszweig für die Remote APi Listen/Trends Abfrage :-)

Viele Grüße
Frank
#8
Hallo zusammen,

Ich denke ich bin soweit und kann den ersten Prototyp bereitstellen. Anbei findet ihr eine .ispac Datei (bzw. als zip, diese Endung entfernen), welche Ihr auf dem SQL-Server installieren müsst. Nachfolgend eine Anleitung von Microsoft zur Installation von Packages. klick mich

Im Endeffekt ist es nichts anderes, als die Datei zu laden und doppelklicken auf dem SQL-Server, dann kommt schon der Wizard. Ich hoffe ihr kommt damit klar, sonst lasst es mich wissen, dann muss ich es nochmal bebildert schildern.

Wie geht es weiter? Anschließend solltet ihr im "Integration Service Catalog" einen weiteren Zweig haben, der die Packages vom myGekko-Projekt beinhaltet:

.png   SSIS.PNG (Größe: 8,68 KB / Downloads: 51)

Generell gibt es zwei Parameter, die immer verlangt werden:
ConnectionString: Data Source=SQLVM;Initial Catalog=myGekko;Integrated Security=True;

MasterConnectionString: Data Source=SQLVM;Initial Catalog=master;Integrated Security=True;Application Name=SSIS-myGekko-{F1196109-BE2A-4E35-AA36-ADB7D32258AE}SQLVM.master;

Diese Parameter müssen natürlich auf eure Maschine angepasst werden. Initial Catalog bedeutet "Datenbankname". Data Source ist der Name des Server bzw. der Instanz.

Jetzt habt ihr eine Liste von Paketen - hier kurz zur Erklärung (ich stelle die Pakete der nötigen Abarbeitungsreihenfolge vor):
1) CreateDatabase.dtsx
Damit erzeugt ihr die entsprechende Datenbank auf dem SQL-Server. Die nötigen Schemas und Prozeduren werden automatisch installiert. 
Anbei die Parameter:
pDatabaseName -> myGekko -> sollte später mit dem ConnectionString übereinstimmen
pSchemaNameRAW -> RAW -> in dieses Schema werden die Daten bulk aus den Dateien geladen
pSchemaNameStaging -> STG -> dieses Schema wird benutzt, um die Daten zwischen zu stagen und für die Endlage vorzubereiten

2) FtpDownload.dtsx
Dieses Paket lädt Daten über einen FTP-Server herunter. Da die interne Komponente nicht funktioniert, lade ich die Daten mittels WinSCP-Kommando herunter. Ihr müsst diesen Step nicht nutzen, könnt auch euren eigenen Download implementieren oder nutzt es eh im lokalen Netzwerk, dann ist es egal.
Parameter:
FtpConnectionString -> ftpUrl
FtpServerPassword -> ftpPasswort
FtpServerUserName -> ftpBenutzer
LocalFolder -> C:\download\gekko\ -> wo soll der Download abgelegt werden? 
RemoteFolder -> / -> Ordner auf dem FTP-Server, im Notfall einfach nur ein Slash
WinScpCommand -> C:\Program Files (x86)\WinSCP\winscp.com -> hier wird die WinSCP-Komponente aufgerufen. Der Rest des Kommandos wird aus den Parametern zusammengesetzt. Es erfolgt momentan nur ein Download, kein entfernen der Daten vom Server.

3) TrendyTranslator.dtsx
Ihr müsst auf eurer Maschine den TrendyTranslator installiert haben. Dieser dient dazu, die Trend-Informationen aus dem eigenen Speicherformat in das CSV-Format umzustellen, ansonsten können wir die Daten nicht einlesen.
Parameter:
pDestinationPath -> C:\download\gekko\trend_out\ -> wo sollen die CSV-Dateien abgelegt werden?
pSourcePath -> C:\download\gekko\trend\ -> wo liegen die Trend-Daten aus dem myGekko
pTrendyPath -> C:\Program Files (x86)\myGEKKO\myGEKKO.TrendyTranslator 1.01\MyGekkoTrendyTranslatorBatch.exe

4) CreateCSVSchemaTrend.dtsx
Jetzt kommt die eigentliche Magie. Mit diesem Paket werden die Trend-Dateien analysiert und die zugehörigen RAW-Tabellen werden automatisch angelegt.
Parameter:
pDestinationPath -> C:\download\gekko\trend_out\ -> das ist in der Regel das pDestinationPath-Verzeichnis vom TrendyTranslator Vorgang. Das sind unsere Quelldaten.
pSchemaError -> C:\download\gekko\trend_out\SchemaError\ -> Wo sollen fehlerhafte Dateien abgelegt werden?
pSchemaNameRAW -> RAW -> in welchem Schema sollen die Tabellen angelegt werden?
pSchemaOK -> C:\download\gekko\trend_out\SchemaOK\ -> hier werden die Dateien verschoben, wenn diese erfolgreich als Tabelle angelegt wurden

5) CreateCSVSchemaList.dtsx
Hier gilt das selbe wie für die Trend Informationen, dieses mal für die Listen. Auch hier werden die Dateien analysiert und die entsprechenden RAW-Tabellen angelegt.
Parameter:
pListOK -> C:\download\gekko\list\OK\ -> an diesen Ort werden die erfolgreichen Dateien verschoben
pListPath -> C:\download\gekko\list\ -> Wo liegen die Daten die eingelesen werden sollen?
pSchemaNameRAW -> RAW -> in welchem Schema sollen die Daten angelegt werden? 

6) BulkInsertTrend.dtsx
Hier werden nun die eigentlichen Dateien eingelesen. Der Vorgang für die Analyse und Tabellenanlage und Import ist absichtlich getrennt. Grund dafür sind Performanceaspekte. Ich würde sonst jede Zeile einzeln übertragen müssen, was natürlich recht lange bei der Anzahl an Dateien dauern würde. Daher habe ich es getrennt. Hier wird mittels BULK-Kommando die Datei komplett in die zuvor angelegte Tabelle eingelesen. Gilt natürlich für alle Trend-Dateien.
Parameter:
pErrorPath -> C:\download\gekko\trend_out\ERROR\ -> dort werden die fehlerhaften Dateien abgelegt
pOkPath -> C:\download\gekko\trend_out\OK\ -> hier werden die erfolgreich importierten Dateien abgelegt
pSchemaNameRAW -> RAW -> in welches Schema werden die Daten abglegt? Sollte mit der vorherigen Anlage übereinstimmen
pSchemaOK -> C:\download\gekko\trend_out\SchemaOK\ -> Aus diesem Ordner werden die Daten gelesen und importiert. Sollte mit dem vorherigen Schema-Import (Output-Pfad) übereinstimmen.

7) BulkInsertList.dtsx
Hier gilt das selbe wie bei 6. Auch hier werden die Listeninformationen BULK eingelesen.
Parameter:
pListBulkError -> C:\download\gekko\list\BulkError\ -> dort werden die fehlerhaften Dateien abgelegt
pListBulktImported -> C:\download\gekko\list\BulkImported\ -> hier werden die erfolgreich importierten Dateien abgelegt
pListOK -> C:\download\gekko\list\OK\ -> Quellordner für den Import der Dateien
pSchemaNameRAW -> RAW -> in welches Schema werden die Daten abgelegt? Sollte mit der vorherigen Anlage übereinstimmen

8) RawToStaging.dtsx
Jetzt werden die Daten aus den Tabellen des Schemas (hier: RAW) in die neue Tabellenstruktur (hier: STG) übertragen. Dabei finden Datenkonvertierungen und Korrekturen statt. Auch Duplikate werden rausgerechnet/zusammengefasst. Zuvor sind alle Daten in den RAW-Tabellen in Feldern vom Datentyp "varchar" eingelesen worden. Jetzt werden die Daten in entsprechende Datentypen gewandelt. Dieser Prozess kann einige Zeit in Anspruch nehmen, da vor allem die Duplikatensuche etwas aufwendiger ist.
Parameter: 
pSchemaNameRAW -> RAW -> aus welchem Schema kommen die Daten?
pSchemaNameStaging -> STG -> in welches Schema sollen die Daten abgelegt werden?

9) ToFinalStage.dtsx
Ab hier werden die Daten in die Endfassung gebracht. Ursprünglich war vorgesehen, dass immer nur die fehlenden Daten (also das Delta) hinzugefügt wird. Das war aber leider nicht möglich, da die Dauer einfach ins unermessliche stieg. Daher werden die Tabellen komplett geleert und anschließend wieder neu befüllt. 
Parameter:
pSchemaNameFinalStage -> dbo -> in welchem Schema sollen die finalen Daten vorliegen?
pSchemaNameStaging -> STG -> Quellschema des Stagings

10) CleanUpTables.dtsx
Mit diesem Paket wird am Ende die Datenbank etwas aufgeräumt. Hintergrund ist, wir haben eine Menge an Dateien. Für jede CSV-Datei besitzen wir am Schluss drei Tabellen. Eine davon enthält aber nur die richtigen Daten, so dass wir die Tabellen aus dem Schema RAW und STG entfernen können. Bei mir wird dieser Step zweimal aufgerufen, so dass ich den Parameter "pSchemaNameRAW" einmal mit "RAW" und einmal mit "STG" aufrufe. Wichtig ist auch, dass die Aufräumaktion vor jedem Importdurchgang für beide Schema aufgerufen werden muss, da es sonst zum Abbruch kommt. Irgendwann kann man ggf. nochmal eine Prüfung und ein DROP einbauen, um sich diesen Step zu schenken.
Parameter:
pDatabaseName -> myGekko -> in welcher Datenbank soll aufgeräumt werden?
pSchemaNameRAW -> RAW -> Tabellen welchen Schemas sollen entfernt werden?

Anmerkung: Sollte my-Gekko die Strukturen der CSV-Dateien mal mit einem Update anpassen, so sollte der Import mindestens bis Step 8 (RawToStaging) ohne Fehler weiterlaufen, da alle Tabellen bis dato immer wieder entfernt und neu angelegt werden. Für das FinalStage findet sich dann eine Lösung.

Auf meiner sehr schmal bemessenen Cloud-Lösung läuft ein Durchgang, von Anlage der Datenbank bis zum CleanUp in 8-9 Minuten durch. Dies liegt wie gesagt an der SQL-Server Dimension und vor allem auch an der Anzahl an Dateien. Mein Gekko läuft seit 2015. Hat jemand das Gekko länger am laufen, besitzt er auch mehr Dateien.
Natürlich macht es keinen Sinn, jedes mal die Dateien von 2015 - 2018 komplett einzulesen. Diese werden sich nicht mehr ändern. In diesem Fall sollte man natürlich dem Importer immer nur die Dateien zur Verfügung stellen, die sich geändert haben und importiert werden sollen. Das reduziert den Importvorgang natürlich um einiges. Ausprobiert habe ich es aber immer mit dem kompletten Datenbestand.

Jede CSV-Datei entspricht einer Tabelle im SQL-Server. D.h. für Auswertungen über Grafana müsst ihr euch ggf. Views bauen. Die Tabellen haben soweit möglich alle einen Primärschlüssel, meist das Datum. 
Um eine Trendinformation aus meinem ersten Post des Energiemonitors zu erhalten, legt ihr euch am besten einen View wie den folgenden an:
Code:
drop view [dbo].[EMAX0_ENERGY_v]
go
CREATE VIEW [dbo].[EMAX0_ENERGY_v]
WITH SCHEMABINDING
AS
SELECT [Datum]
     ,[Haus]
     ,[Netz+]
     ,[Netz-]
     ,[Pv]
     ,[Batt+]
     ,[Batt-]
      , NULL as ProgH
      , NULL as ProgHIcon
      , NULL as ProgM
      , NULL as ProgMIcon
 FROM [dbo].[2015 EMAX0_ENERGY]
 union
 SELECT [Datum]
     ,[Haus]
     ,[Netz+]
     ,[Netz-]
     ,[Pv]
     ,[Batt+]
     ,[Batt-]
      , NULL as ProgH
      , NULL as ProgHIcon
      , NULL as ProgM
      , NULL as ProgMIcon
 FROM [dbo].[2016 EMAX0_ENERGY]
 union
 SELECT [Datum]
     ,[Haus]
     ,[Netz+]
     ,[Netz-]
     ,[Pv]
     ,[Batt+]
     ,[Batt-]
      , NULL as ProgH
      , NULL as ProgHIcon
      , NULL as ProgM
      , NULL as ProgMIcon
 FROM [dbo].[2017 EMAX0_ENERGY]
 union
 SELECT [Datum]
     ,[Haus]
     ,[Netz+]
     ,[Netz-]
     ,[Pv]
     ,[Batt+]
     ,[Batt-]
      ,[ProgH]
      ,[ProgHIcon]
      ,[ProgM]
      ,[ProgMIcon]
 FROM [dbo].[2018 EMAX0_ENERGY]
 union
 SELECT [Datum]
     ,[Haus]
     ,[Netz+]
     ,[Netz-]
     ,[Pv]
     ,[Batt+]
     ,[Batt-]
      ,[ProgH]
      ,[ProgHIcon]
      ,[ProgM]
      ,[ProgMIcon]
 FROM [dbo].[2019 EMAX0_ENERGY]

 -- select * from [dbo].[EMAX0_ENERGY_v]
Diesen könnt ihr später in Grafana oder einfach im Management Studio mittels "select * from [dbo].[EMAX0_ENERGY_v]" abrufen und habt die Daten von 2015 bis zum aktuellen Datum in einer Abfrage.
Jetzt kommt es darauf an, was ihr euch auswerten wollt und wie ihr was im Gekko konfiguriert habt. Dementsprechend habt ihr CSV-Dateien/Tabellen mit der entsprechenden Benennung. Die ist glaube ich teilweise unterschiedlich, das weis ich aber nicht 100%ig.

Je nachdem wie gut ihr euch im SQL-Server auskennt, empfehle ich noch zwei Dinge:
1) legt euch einen SQL-Agent Job an, der alle Pakete nacheinander ausführt
2) Im Sql Server Integration Service kann man sich sogenannte Environments anlegen. Parameter aus den Paketen kann man an die Environments binden und mit Werten vorbelegen. Diese Vorbelegung lässt sich später mit einem Klick bei der Agent-Definition (oder dem manuellen Aufruf) direkt zuweisen und spart Konfigurationsaufwand und mindert das Fehlerrisiko, wenn man bspw. Pfade mehrmals tippen müsste.
Kann dazu gerne auch nochmal eine Anleitung posten oder ihr sucht ganz kurz bei Google, da wird man bzgl. diesem Thema auch recht schnell fündig.

So, jetzt erstmal genug von mir. Lasst mich wissen wie ihr klar kommt oder wenn ihr Probleme habt.

Feedback ist auch gerne erwünscht :-D

Viele Grüße
Frank


Angehängte Dateien
.zip   myGekkoV1.0.0.ispac.zip (Größe: 34,91 KB / Downloads: 1)


Gehe zu:


Benutzer, die gerade dieses Thema anschauen:
1 Gast/Gäste