Vielleicht erhalten Sie auch regelmässig grössere Datenmengen, welche Sie auf Vollständigkeit überprüfen müssen. Wenn die Anzahl Datensätze gleichbleibt – beispielsweise der tägliche Zählerstand von Messgeräten – können Sie Abweichungen problemlos feststellen und der Ursache auf den Grund gehen. Bei einer unbestimmten Anzahl an Datensätzen wird es etwas komplizierter. Hier könnte die KI gute Dienste leisten. Doch was tun, wenn intern keine Tools vorhanden sind und Cloudlösungen nicht verwendet werden dürfen? Eine Möglichkeit ist das kartesische Produkt.
Die Ausgangslage
Wir erhalten über eine Schnittstelle täglich 40’000 bis 50’000 Datensätze mit Verkaufstransaktionen von 87 Filialen. Sollten Daten einer oder mehrerer Filialen fehlen, liegt ein Server-, Netzwerk- oder Kassenproblem vor. Je nach Problem kann es mehrere Tage dauern, bis die fehlenden Daten bei uns eintreffen. Die werden in unserem ERP-System aufbereitet, verbucht und teilweise weiterverrechnet. Aufgrund der Relevanz müssen wir den Eingang überwachen und die nötigen Schritte einleiten.
Das Problem
Wir können die Daten in einer Pivot-Tabelle aufliste mit den Filialen als Zeilen, den Kalendertagen als Spalten und der Anzahl Transaktionen als Werte.
| 01.03. | 02.03. | 03.03. | 05.03. | 06.03. | 07.03. | |
| FI193 | 370 | 287 | 329 | 341 | 319 | 313 |
| FI194 | 1275 | 1365 | 1236 | 1201 | 1244 | 1288 |
| FI195 | 27 | 28 | 15 | 31 | 28 | 27 |
| FI197 | 715 | 689 | 677 | 706 | 742 | 714 |
Auf den ersten Blick sieht alles gut aus. Nur wo ist die Spalte für den 04.03. und wo ist die Zeile für die Filiale 196? Anscheinen gab es technische Probleme. Weil die Daten nicht existieren, kann die Pivot-Tabelle sie auch nicht anzeigen. Wir benötigen also ein Gerüst mit allen Filialen und Kalendertagen, dem wir die Transaktionsdaten gegenüberstellen können.
Die Lösung: das Kartesische Produkt
Das Kartesische Produkt ist eine neue Menge, die aus vorhandenen Mengen gebildet wird. Auf unseren Fall bezogen bedeutet dies, dass wir aus der Filialliste und einem Kalender eine neue Liste erstellen, bei der jede Filiale mit jedem Datum kombiniert ist (und dementsprechend auch jedes Datum mit jeder Filiale). Somit hat unsere neue Filialen-Datums-Liste für das Jahr 2024 total 31’842 Einträge (87 Filialen x 366 Tage). Diese Liste verbinden wir mit der Filiale und dem Datum mit den Transaktionen und können so die fehlenden Transaktionen erkennen.
| 01.03. | 02.03. | 03.03. | 04.03. | 05.03. | 06.03. | 07.03. | |
| FI193 | 370 | 287 | 329 | null | 341 | 319 | 313 |
| FI194 | 1275 | 1365 | 1236 | null | 1201 | 1244 | 1288 |
| FI195 | 27 | 28 | 15 | null | 31 | 28 | 27 |
| FI196 | null | null | null | null | null | null | null |
| FI197 | 715 | 689 | 677 | null | 706 | 742 | 714 |
Ein Kartesisches Produkt erstellen
Power Query
Seit Excel 2016 gehört Power Query zu den Bordmitteln. Es bietet unzählige Möglichkeiten, Daten zu transformieren. Um mit Power Query unsere Daten kontrollieren zu können, benötigen wir eine Excelliste unserer Filialen sowie ein Kalender für das Jahr 2024. Diese importieren wir über das Menü Daten/Daten abrufen/Aus Datei/Aus Excel-Arbeitsmappe.
Excel zeigt uns nun den Inhalt der Arbeitsmappe. Wir wählen unsere Liste und klicken auf die Schaltfläche „Daten transformieren“.
Mit der Kalendertabelle und der Transaktionstabelle verfahren wir gleich. Schlussendlich haben wir die drei Tabellen im Power Query. Hier können wir die Filialen mit der Kalendertabelle kombinieren. Dazu fügen wir eine neue benutzerdefinierte Spalte ein, benennen diese „Datum“ und geben als Formel nur den Namen der Kalendertabelle ein.
Anschliessen expandieren wir die Spalte Datum mit dem Doppelpfeil und bestätigen mit OK.
Als Ergebnis erhalten wir eine Tabelle, in der alle Filialen mit allen Daten kombiniert sind. Um nun die Transaktionen mit dem kartesischen Produkt zu verknüpfen, wählen wir das Menü Start/Kombinieren/Abfragen zusammenführen/Abfragen als neue Abfrage zusammenführen.
Wir wählen als erste Tabelle die Filialen aus und als zweite die Transaktionsdaten. Mit Control und Mausklick verbinden wir die zusammengehörenden Spalten und wählen „Linker äusserer Join“ als Join-Typ.
Nachdem wir die neue Spalte expandiert haben, entsteht unsere Wunschtabelle mit allen Kalenderdaten 2024 pro Filiale und den zugehörigen Transaktionen. Mit der Schaltfläche „Speichern & laden“ wird die Tabelle in ein Excel-Arbeitsblatt übernommen, wo mit ihr eine Pivot-Tabelle erstellt werden kann. Mit bedingter Formatierung lassen sich fehlende Daten farblich hervorheben. Somit sehen wir auf den ersten Blick, dass die Daten der Filiale 128 am 6. November 2024 fehlen.
DAX
Um in DAX ein kartesisches Produkt zu erstellen, verwenden Sie die Funktion CROSSJOIN(Filialen, Kalender). Grundsätzlich werden in den Klammern alle Tabellen für das kartesische Produkt angegeben.
Microsoft SQL
In Microsoft SQL können Sie mit diesem Statement ein kartesisches Produkt erstellen:
SELECT *
FROM Filialen
CROSS JOIN Kalender
