Daten aus Microsoft Excel als Analysewert verwenden

Liebe Datenanalysten,

wer in Daten nach neuen Erkenntnissen sucht, muss manchmal neue Daten zurate ziehen: Daten, die über das Angebot im Data Warehouse hinausgehen und womöglich nur zu einem speziellen Zweck erhoben werden. Das könnten zum Beispiel Sonderauswer­tungen aus anderen Systemen sein, die man mit den regelmäßig bereitgestellten Daten verknüpfen möchte. Oder es sollen externe Daten hinzugespielt werden, etwa Rohstoffpreise, Wetterdaten, Marktanalysen oder Statistiken aus dem Internet. Oder: In Anwen­dungen mit reinen Ist-Zahlen möchte man wenigstens die aggre­gierten Plan- oder Budget-Werte ergänzen, zum Beispiel für die Geschäftseinheiten, Sparten oder Regionen. Solche Zusatzdaten liegen meist in Form von Excel-Dateien vor. In dieser Form lassen sie sich sehr einfach in bestehende DeltaMaster-Anwendungen übernehmen. Das macht den Weg von neuen Daten zu neuen Er­kenntnissen angenehm kurz! Was dabei zu beachten ist, erläutern wir auf den folgenden Seiten.

Herzliche Grüße
Ihr Team von Bissantz & Company

Es gibt mehrere Wege, Daten aus Microsoft Excel in DeltaMaster-An­wendungen zu integrieren:

1. Im Rahmen einer automatisierten Datenversorgung können Da­ten aus einer oder mehreren Excel-Dateien automatisiert ins Data Warehouse übernommen und dort mit Daten aus an­deren Systemen verknüpft werden. Der Import aus Excel ist dann Teil des ETL-Prozesses (Ex­traktion, Transformation, Laden) und ver­hält sich wie Übernahmen aus anderen Vorsystemen. Die Ver­arbeitung einzurichten, setzt technische Kenntnisse und Be­rechtigungen voraus, ge­schieht außerhalb von DeltaMaster und ist häufig eine Aufgabe für die IT oder IT-nahe Kolle­gen im Fachbereich.
2. Bei Selfservice-Anwendungen ist eine Excel-Datei die primäre Datenquelle. DeltaMaster greift also nicht auf ein großes Data Warehouse zu, sondern lediglich auf die Daten aus der Excel-Datei, die zu diesem Zweck automatisch in eine sogenannte lokale Cube-Datei transformiert werden. Solche Anwendungen können Fachanwender mit DeltaMaster leicht selbst erstellen, aktualisieren und verteilen. Sie sind jedoch nur eingeschränkt mehrbenutzerfähig und nicht für Massendaten gedacht – eben „Selfservice“.
3. Zusätzlich zu den Daten im Data Warehouse können Werte aus Excel in die DeltaMaster-An­wendung geladen werden. Die primäre Datenquelle ist das Data Warehouse. Darüber hin­aus liest DeltaMaster Werte aus einer oder mehreren Excel-Dateien und macht sie in der An­wen­dung als Analysewert verfügbar. Die Einrichtung ist einfach und im Fachbereich möglich. Ver­änderungen am Data Warehouse sind nicht erforderlich. Die Anwendung bleibt mehrbe­nut­zerfähig.
4. Bei Planungsanwendungen können Werte aus Excel in Eingabemasken kopiert oder sogar zei­lenweise aus einer Excel-Datei importiert werden. Auf diese Weise lassen sich Planwerte, die in anderen Systemen ermittelt wurden, in die zentralen Planungsprozesse von Delta­Master übernehmen. Den Import können die Planer im Fachbereich selbst ausführen, sogar über das Web. Das Einsammeln und Prüfen von Tabellenblättern im Rahmen der Pla­nung ist damit keine zentrale Aufgabe mehr, sondern kann an die Planer delegiert werden. Lediglich die Importprozeduren sind zentral einzurichten.
5. gewidmet: der Übernahme von Werten aus Excel-Dateien in eine bestehende Anwendung. Eine solche Kombination von Datenquellen wird auch Mash-up genannt.

Anwendungsbeispiel: Vertriebscontrolling und CRM

Das Vorgehen erläutern wir an einem Beispiel aus unserem Referenzmodell „Chair“. Es soll um eine Auswertung ergänzt werden, wie oft der Vertriebsinnendienst die Kunden angerufen hat.

Die Informationen über Telefonate kommen in dem am Rechnungswesen orientierten Vertriebs­con­trolling eigentlich nicht vor. Dankenswerterweise hat jedoch die IT für einen kleinen Zeitraum die Daten aus dem CRM-System für uns extrahiert und in Form einer Excel-Liste bereitgestellt, damit wir sie mit dem Vertriebscontrolling abgleichen können. In der obigen Abbildung ist zu erkennen, wie sich diese Liste im Ergebnis in DeltaMaster darstellt – und wie sie in Excel aussah. Der ausgewiesene Umsatz stammt aus der bekannten OLAP-Anwendung, die Anzahl der Anrufe haben wir aus Excel beigesteuert.

Dieses Beispiel entspricht der ersten der in der Einleitung beschriebenen Anwendungssituationen: Es handelt sich um eine Sonderauswertung aus einem anderen System. Dafür, wie auch für die anderen beiden genannten Fälle (Plan-Werte, externe Quellen), gilt: Prinzipiell wäre die Einbindung auch auf Datenbankebene möglich. Daten aus unterschiedlichen Quellen zu verbinden, ist ja eine große Stärke des Data-Warehouse-Konzepts, und Plan-, Budget- oder Simulationswerte können Anwender auch direkt mit DeltaMaster eingeben und ins Data Warehouse speichern. Die dafür nötigen Strukturen in der Datenbank zu pflegen, bedarf jedoch spezieller Kenntnisse und bedeutet einen gewissen Auf­wand – den man nicht immer auf sich nehmen möchte, insbesondere, wenn sich die hinzuzu­kom­bi­nierenden Daten nur selten oder unregelmäßig ändern und wenn es nicht allzu viele sind.

Analysewert für externe Werte anlegen und definieren

Mit DeltaMaster 6 ist es möglich, eigene Kennzahlen (Analysewerte, Measures) zu definieren, die ihre Werte aus einer Excel-Datei beziehen anstatt aus einer Datenbankabfrage. Voraussetzung dafür ist, dass die Anwendung auf MDX basiert, also zum Beispiel auf Microsoft Analysis Services oder SAP BW. Die Anzeige und Verwendung solcher Analysewerte ist auch in DeltaMaster 5 möglich, zum An­legen, Bearbeiten und Aktualisieren benötigt man jedoch DeltaMaster 6.

Um einen neuen Analyse­wert anzu­le­gen, wechseln Sie in den Bearbei­tungs­modus. Beim Modellieren ge­langen Sie über die drei Punkte in der Analysewertleiste in den Analyse­wert-Browser. Dort können Sie im Menü Ich möchte einen neuen Analy­se­wert an­legen.

Als Analysewerttyp wählen Sie die externen Werte aus.

Zur Definition des Analysewerts wer­den lediglich zwei Angaben benötigt: welche Datei verwendet werden soll (Microsoft-Excel-Arbeitsmappe) und welches Arbeitsblatt daraus. Delta­Master unterstützt alle verbreiteten Dateiformate, sowohl das alte Format XLS (vor Excel 2007) als auch die neu­eren Formate XLSX, XLSM und XLSB. Wie die Tabelle inhaltlich aufgebaut sein muss, ist weiter unten beschrieben. Sobald Sie eine Datei ausgewählt haben, liest DeltaMaster sie ein und bietet ihre Ar­beitsblätter in der Auswahlliste an, einschließlich der versteckten. Der Analysewert ba­siert auf genau einem Arbeitsblatt, das Sie aus der Liste auswählen. Das war’s auch schon! Geben Sie dem Analyse­wert einen Namen und optional eine Beschreibung mit, schon steht die neue Kennzahl in der aktu­ellen Anwendung zur Verfügung.

Im Analysewert-Browser ist der Analysewerttyp als „External­Data“ gekennzeichnet.

Übernahme durch Kopieren, ohne Sperren

Beim Anlegen des Analysewerts (und beim Aktualisieren, siehe unten) liest DeltaMaster das ausge­wählte Excel-Arbeitsblatt ein und kopiert die Werte in die Anwendung bzw. Analysesitzung. Dadurch kann man auch dann mit den Werten arbeiten, falls die Excel-Datei in der Zukunft nicht mehr zur Verfügung steht. Das liegt an der Art und Weise, wie DeltaMaster die Daten aufnimmt: Sie werden allesamt einmalig aus der Datei eingelesen und in die Anwendung kopiert (anstatt eine Verbindung zu der Datei zu öffnen und Abfragen auszuführen).

Übrigens: Die leidigen Probleme mit gesperrten Dateien bleiben Ihnen erspart! Eine Datei darf durchaus in Excel (bzw. in einem anderen Programm oder von einem anderen Benutzer) geöffnet sein, während Sie den Analysewert in DeltaMaster definieren, aktualisieren oder verwenden.

Externe Werte darstellen und verwenden

Der Analysewert verhält sich wie jeder andere auch: Er kann in Grafischen Tabellen dargestellt wer­den, auch in Kombination mit anderen Analysewerten, einschließlich solcher, die ihrerseits aus einer Excel-Datei stammen, derselben oder einer anderen; er kann in Berechnungen verwendet werden, zum Beispiel in benutzerdefinierten Analysewerten oder in Filterwerten; Browsen, Zoomen und Navi­gieren funktionieren; und, und, und. Auch in allen anderen Berichtstypen sind die Analysewerte aus externen Daten verwendbar: in der Geo-Analyse, der Portfolioanalyse, der Zeitreihenanalyse und den Analyseverfahren der Methodenbibliothek. Die Dateneingabe ist nicht möglich, auch das wie bei anderen benutzerdefinierten Analysewerten.

Die nebenstehende Abbildung zeigt ein Beispiel: Die Anzahl der Anrufe aus der Excel-Datei ist neben dem Umsatz aus Analysis Services angegeben. Der rechne­rische Umsatz pro Anruf wurde als Quoti­entenwert in DeltaMaster definiert. Spark­lines veranschaulichen die Entwicklung der Anrufzahlen. Für den ersten Kunden hat man durch Navigieren aufgeschlüsselt, auf welche Vertriebsteams Anrufe und Umsatz zurückgehen.

Das Anzeigen von und Rechnen mit externen Werten benötigt mehr Rechenzeit als Datenbank­abfra­gen. Schon deshalb eignet sich das Verfahren nicht für große Datenmengen. Einige Tausend Zeilen sind aber meist kein Problem.

Werte aktualisieren: Umschalt+F9 oder Analysewerteigenschaften

Besonders chic ist: Der Analysewert kann mit frischen Daten versorgt werden – die in die Anwendung übernommenen Werte lassen sich aktualisieren! Dazu öffnet DeltaMaster die angegebene Excel-Da­tei erneut und liest erneut die Werte ein. Das Aktualisieren ist auf zweierlei Weise möglich:

• Im Präsentationsmodus sowie beim Editieren starten Sie die Aktualisierung mit der Tasten­kom­bination Umschalt+F9. Die Umschalt-Taste verstärkt gewissermaßen die Funktion der Taste F9. Allein löst diese bekanntlich eine Neuberechnung des Berichts mit den aktuel­len Werten aus der Datenbank aus; zusammen mit der Umschalt-Taste werden zusätzlich die Werte der Analysewerte aus Excel, die im Bericht vorkommen, aktualisiert. Die Aktualisierung in einem Bericht wirkt sich auch auf alle anderen Berichte aus, in denen dieser Analysewert verwendet wird.
• Im Bearbeitungsmodus beim Modellieren können Sie die Werte über die Analysewerteigenschaften auf den neusten Stand bringen, unabhängig von Be­richten: Auf der Registerkarte Definition gibt es ei­nen Link, mit dem Sie die Daten aktualisieren.

Beim Aktualisieren gelten die oben erwähnten Annehmlichkeiten: Es ist nicht erforderlich, die Excel-Datei vorher zu schließen – Sie können Ihre Werte quasi parallel in Excel bearbeiten und in Delta­Master aktualisieren.

Für Anwendungen im Repository ist zu beachten: Die Aktualisierung mit Umschalt+F9 ist nur im Windows-Client von DeltaMaster 6 möglich, nicht im Web-Client, in der App oder im Office-Add-in. In diesen Fällen muss die Anwendung zuerst im Repository aktualisiert werden, um neue Excel-Daten in der Anwendung verfügbar zu machen. Falls diese Aufgabe öfter ansteht, kann es sich lohnen, für das Aktualisieren im Repository einen Job im Publisher (Berichtsserver) anzulegen.

In DeltaMaster 5 können die Analysewerte mit externen Werten verwendet werden, jedoch lassen sie sich in DeltaMaster 5 nicht aktualisieren. Deshalb müssen Anwendungen im Repository bzw. Analy­sesitzungen (DAS-Dateien) mit DeltaMaster 6 auf den neusten Stand gebracht werden, bevor sie in DeltaMaster 5 die aktuellen Werte widerspiegeln.

Aufbau der Excel-Tabelle

Damit DeltaMaster die Excel-Tabelle verarbeiten kann, muss sie einem bestimmten Aufbau genügen – einem ganz gängi­gen Aufbau, wie an dem folgenden Beispiel zu erkennen ist. Die nebenstehende Abbildung zeigt einen Ausschnitt aus den Daten. In den Zeilen ist jeweils die Periode, der Kunde, das Vertriebsteam sowie die Anzahl der Anrufe angegeben, die dieses Vertriebsteam in diesem Monat mit diesem Kunden geführt hat.

Allgemein lässt sich der Tabellenaufbau mit den folgenden Eigenschaften beschreiben.

1. Listenform Die Tabelle muss in Listenform angelegt sein. Das heißt: In jeder Zeile steht genau ein Datensatz. Kreuz­tabellen werden nicht unterstützt.
2. Spaltenüberschriften In der ersten Zeile stehen Spaltenüberschriften. Diese müssen den Namen von Dimensions­ebenen im Analysemodell entsprechen. Maßgeblich sind die im Dimensionsbrowser ange­zeigten Namen, ggf. unter Berücksichtigung des aktuellen Alias-Sets (nicht etwa interne Ids oder MDX-Namen mit eckigen Klammern und Ähnlichem).
3. Links eine oder mehrere Spalten mit Elementen In den ersten Spalten stehen Dimensionselemente. Diese müssen zu der Ebene gehören, die in der Spaltenüberschrift angegeben ist, und ebenfalls dem aktuellen Alias-Set entsprechen, sofern ein Alias-Set aktiv ist.
4. Eindeutige Elementkombinationen Die Kombination aller Elemente in einer Zeile beschreibt, welcher Zelle im OLAP-Würfel der numerische Wert zugeordnet werden soll. Somit ist die Elementkombination ein zusammen­gesetzter Schlüssel. Dieser muss eindeutig und vollständig sein: Es darf nicht mehrere Zeilen mit identischen Elementkombinationen geben und in jeder Zeile müssen alle Felder belegt sein.
5. Rechts eine Spalte mit den Zahlen In der letzten gefüllten Spalte stehen die numerischen Werte. Die Spaltenüberschrift ist uner­heblich, da der Name des Analysewerts in DeltaMaster festgelegt wird.

Diese Kriterien sind schnell zu erfüllen – die meisten Austauschdateien werden ohnehin in diesem Format erzeugt. Eine Feinheit leitet sich aus Punkt 4 ab: Falls sich mehrere Vorgänge auf die gleichen Elemente beziehen, müssen diese bereits in der Excel-Datei aggregiert sein. In unserem Beispiel gibt es daher genau eine Zeile pro Kunde und Monat, in der jeweils die Gesamtzahl der Anrufe angege­ben ist – und nicht etwa eine Zeile pro Anruf (mit einem Zählwert von 1).

Datensätze, die im Daten­modell nicht zugeordnet werden können, übernimmt DeltaMaster ebenfalls; falls sie nicht benötigt werden, sollte man sie vorher löschen.

Zusammenspiel mit Anwendung und Analysemodell

Beim Einlesen werden die Werte in jeder referenzierten Dimension genau einem Element zugeordnet; die Elemente gehören zur selben Ebene. Un­terhalb dieser Ebene liefert der Analysewert keine Werte zurück, oberhalb davon werden die Werte automatisch als Summe aggregiert.

Damit die Spaltenüberschriften den gewünschten Ebenen zugeordnet werden können, müssen die Ebenen im Analysemodell eindeutig be­nannt sein. Das ist nicht immer gegeben. Vor allem bei Parent-Child-Dimensio­nen ist manchmal zu beobachten, dass die Ebenen nur durchnummeriert wurden und Namen wie „Level 02“ sowohl etwa in der Kunden- als auch in der Artikeldimension auftauchen. In diesen Fällen können Sie die Ebe­nen in DeltaMaster umbenennen (Modellieren, in der Filterleiste die be­tref­fende Dimension anklicken, zur Registerkarte Ebenen wechseln, dort im Kontextmenü der Ebene: Ebene umbenennen). Damit stabilisieren Sie nicht nur die Integration externer Werte, sondern verbes­sern die Anwendung insgesamt, denn die Ebenennamen werden an verschiedenen Stellen in Berich­ten und an der Benutzeroberfläche angezeigt. Es lohnt sich also, auf eine inhaltlich sinnvolle und eindeutige Benennung zu achten.

Bei der Zuordnung gemäß Ebenen- und Elementnamen gilt das jeweils aktuelle Alias-Set, wie er­wähnt. Das erleichtert es den Anwendern, die externen Daten vorzubereiten: Sie müssen in Excel so aussehen wie in DeltaMaster.

Externe Werte vs. DeltaMaster-Modeler und Selfservice-BI

So verführerisch es erscheinen mag: Das hier beschriebene Verfahren ist nicht als Ersatz für automa­tisierte Datenübernahmeprozesse oder für Eigenentwicklungen mit einer Excel-Datei als Datenquelle gedacht. Das liegt vor allem daran, dass sich mit den externen Werten keine Strukturen aufbauen lassen. Zwar können sie mühelos an bestehende Strukturen „andocken“, an einer bestimmten Ebene in jeder referenzierten Dimension. Auch die Aggregation von dieser Ebene aus nach oben ist möglich – aber die Hierarchie muss bereits gegeben sein. Zudem werden die Excel-Daten stets als Analyse­wert integriert; man kann damit also keine zusätzlichen Elemente in eine Dimension einfügen. Und auch in dem Fall, dass große Datenmengen zu integrieren sind, sollte man zu anderen Lösungen greifen. Zum Glück können Sie auch dabei auf DeltaMaster zählen: Mit dem Modeler und den Self­service-Funktionen stehen Ihnen professionelle Werkzeuge zur Verfügung, um kleine wie große Applikationen von Grund auf zu erarbeiten.