Dynamische Periodenauswahl für KPI-Wert und Wetterfarbe im DeltaMaster Portal

DeltaMaster generiert MDX-Befehle für KPI-Filter, KPI-Wert und Wetterfarbe im DeltaMaster Portal automatisch aus der Wetterzelle eines ausgewählten Berichts. Dabei wird jedoch die aktuell im Bericht eingestellte Periode in den MDX-Ausdrücken fest eingestellt. Durch eine dynamische Definition der MDX-Ausdrücke werden die Werte immer für den aktuellen Monat berechnet – wie der folgende Blogbeitrag zeigt.

Festlegung der aktuellen Periode

Im den meisten Fällen wird in der KPI-Definition eine Periode referenziert. Hier ist zunächst festzulegen, wie die aktuelle Periode bestimmt werden soll.

Hierzu sind verschiedene Varianten möglich wie z.B.

Letzte Periode mit Wert
Periode aus Systemdatum ermitteln

Dies ist eine fachliche / inhaltliche und keine technische Entscheidung und wird hier daher nicht weiter ausgeführt. Im Folgenden wird die letzte Periode mit Wert ermittelt.
Der dafür notwendige MDX-Ausdruck kann so aussehen:

tail(
  filter(
    [Period].[Period].Month.members,
    ([Measures].[LST_h],[ValueType].[ValueType].[ValueType].&[1]) > 0)
    ,
    1
).item(0)

Dieser Filterausdruck wird in den folgende MDX-Ausdrücken immer wieder verwendet.

MDX-Ausdruck für den KPI-Filter

Der MDX Ausdruck für den KPI-Filter muss das aktuelle Element zurückliefern. Daher wird der oben definierte Filterausdruck direkt in der Achsendefinition im SELECT Statement eingefügt:

select
tail(filter([Period].[Period].Month.members, ([Measures].[LST_h],
[ValueType].[ValueType].[ValueType].&[1]) > 0),1).item(0)
DIMENSION PROPERTIES [Period].[Period].[MonthBEZ] on axis(0)
from   BC_Project
WHERE (
[Project].[Project_PC].&[1], 
[ANF].[ANF_ANF_ProjectTypeID].[ANF_ProjectTypeID].&[1]
)

MDX-Ausdruck für den KPI-Wert

DeltaMaster generiert hier bei Übernahme der Wetterzelle folgendes MDX:

WITH MEMBER [Measures].[UserDef 2] AS '[Measures].[LST_h]/8', SOLVE_ORDER=100 SELECT {[Measures].[UserDef 2]} ON AXIS(0) FROM [BC_Project] WHERE ([Period].[Period].[Month].&[201701], [Project].[Project_PC].&[1], [TimeUtility].[TimeUtility].&[1])

Im ersten Schritt ist es hilfreich den MDX-Ausdruck besser zu formatieren. Kollegen aus dem Support und Kunden, die diesen Ausdruck später verstehen oder anpassen müssen, werden dankbar sein.

WITH

MEMBER [Measures].[UserDef 2] AS '[Measures].[LST_h]/8', SOLVE_ORDER=100

SELECT {[Measures].[UserDef 2]} ON AXIS(0)
FROM   [BC_Project]

WHERE   ( [Period].[Period].[Month].&[201701],
               [Project].[Project_PC].&[1],
               [TimeUtility].[TimeUtility].&[1]
             )

Im nächsten Schritt ist der gelb markierte Elementausdruck durch das dynamische Statement zu ersetzen. Auch wenn in diesem Fall die Ersetzung direkt im WHERE-Statement möglich ist, empfehle ich jedoch für die Periode ein weiteres MEMBER anzulegen. Bei komplexeren Ausdrücken kann dieses MEMBER an verschiedenen Stellen verwendet werden. Außerdem ist es dann übersichtlich in den ersten Zeilen des MDX-Ausdruck zu sehen und verschwindet nicht im nicht suchbaren Bereich der Eingabebox im KPI-Konfigurationsdialog.

WITH

MEMBER [Period].[Period].[ACT_MONTH] as
'tail(filter([Period].[Period].Month.members, ([Measures].[LST_h],[ValueType].[ValueType].[ValueType].&[1]) > 0),1).item(0)',
SOLVE_ORDER=200

MEMBER [Measures].[UserDef 2] AS '[Measures].[LST_h]/8', SOLVE_ORDER=100

SELECT {[Measures].[UserDef 2]} ON AXIS(0) --, am on axis(1)
FROM   [BC_Project]

WHERE   (      [Period].[Period].[ACT_MONTH],
               [Project].[Project_PC].&[1],
               [TimeUtility].[TimeUtility].&[1]
       )

MDX-Ausdruck für die KPI-Wetterfarbe

Analog erfolgt die Dynamisierung der Wetterfarbe: Der vom DeltaMaster generierte Ausdruck:

WITH MEMBER [Measures].[UserDef 2] AS '[Measures].[LST_h]/8', SOLVE_ORDER=100 MEMBER [Measures].[__TEMP0__] AS 'Min({[Period].[Period].[Month].&[201701].Lag(11) : [Period].[Period].[Month].&[201701]},[Measures].[UserDef 2])', SOLVE_ORDER=10000 MEMBER [Measures].[__TEMP1__] AS 'Max({[Period].[Period].[Month].&[201701].Lag(11) : [Period].[Period].[Month].&[201701]},[Measures].[UserDef 2])', SOLVE_ORDER=10000 MEMBER [Measures].[__WEATHER__] AS 'Iif([Measures].[__TEMP0__] >= 0, Iif([Measures].[UserDef 2] <= [Measures].[__TEMP0__] And [Measures].[UserDef 2] > 0, 1 * 1E-09, 1 * ([Measures].[UserDef 2] - [Measures].[__TEMP0__]) / ([Measures].[__TEMP1__] - [Measures].[__TEMP0__])),
Iif([Measures].[__TEMP1__] <= 0, Iif([Measures].[UserDef 2] >= [Measures].[__TEMP1__] And [Measures].[UserDef 2] < 0, 1 * 1E-09, 1 * ([Measures].[UserDef 2] - [Measures].[__TEMP1__]) / ([Measures].[__TEMP0__] - [Measures].[__TEMP1__])),

Iif([Measures].[UserDef 2] >= 0, 1 * ([Measures].[UserDef 2] / [Measures].[__TEMP1__]),

1 * ([Measures].[UserDef 2] / [Measures].[__TEMP0__]))))', SOLVE_ORDER=10001 SELECT {[Measures].[__WEATHER__]} ON AXIS(0) FROM [BC_Project] WHERE ([Period].[Period].[Month].&[201701], [Project].[Project_PC].&[1], [TimeUtility].[TimeUtility].&[1], [ANF].[ANF_ANF_ProjectTypeID].[ANF_ProjectTypeID].&[1])

Auch empfehlen wir zunächst eine Formatierung, die die Lesbarkeit erhöht. Hier ist erkennbar, dass die aktuelle Periode an fünf Stellen vorkommt.

WITH

MEMBER [Measures].[UserDef 2] AS '[Measures].[LST_h]/8', SOLVE_ORDER=100

MEMBER [Measures].[__TEMP0__] AS

'Min({[Period].[Period].[Month].&[201701].Lag(11) : [Period].[Period].[Month].&[201701]},[Measures].[UserDef 2])',

SOLVE_ORDER=10000

MEMBER [Measures].[__TEMP1__] AS

'Max({[Period].[Period].[Month].&[201701].Lag(11) : [Period].[Period].[Month].&[201701]},[Measures].[UserDef 2])',

SOLVE_ORDER=10000 MEMBER [Measures].[__WEATHER__] AS

'Iif([Measures].[__TEMP0__] >= 0, Iif([Measures].[UserDef 2] <= [Measures].[__TEMP0__] And [Measures].[UserDef 2] > 0, 1 * 1E-09, 1 * ([Measures].[UserDef 2] - [Measures].[__TEMP0__]) / ([Measures].[__TEMP1__] - [Measures].[__TEMP0__])),

Iif([Measures].[__TEMP1__] <= 0, Iif([Measures].[UserDef 2] >= [Measures].[__TEMP1__] And [Measures].[UserDef 2] < 0, 1 * 1E-09, 1 * ([Measures].[UserDef 2] - [Measures].[__TEMP1__]) / ([Measures].[__TEMP0__] - [Measures].[__TEMP1__])),

Iif([Measures].[UserDef 2] >= 0, 1 * ([Measures].[UserDef 2] / [Measures].[__TEMP1__]),

1 * ([Measures].[UserDef 2] / [Measures].[__TEMP0__]))))',

SOLVE_ORDER=10001

SELECT {[Measures].[__WEATHER__]} ON AXIS(0)

FROM   [BC_Project]

WHERE  (
               [Period].[Period].[Month].&[201701],

               [Project].[Project_PC].&[1],

               [TimeUtility].[TimeUtility].&[1],

               [ANF].[ANF_ANF_ProjectTypeID].[ANF_ProjectTypeID].&[1]

Für die Dynamisierung wird hier neben dem schon oben verwendeten MEMBER [ACT_MONTH] noch ein SET [LAST12MONTH] zur Definition der zu berücksichtigen Perioden eingebaut. Hierfür kann der schon für die Auswahl der Periode verwendete tail ()-Ausdruck erneut verwendet werden, jedoch ist dabei zu beachten die Länge von 1 auf 12 anzupassen.

Die Definition der Menge ist auch notwendig, weil der Intervalloperator (:) mit einem berechneten Element wie [ACT_MONTH] nicht angewendet werden kann.

WITH

MEMBER [Period].[Period].[ACT_MONTH] as
'tail(filter([Period].[Period].Month.members, ([Measures].[LST_h],[ValueType].[ValueType].[ValueType].&[1]) > 0),1).item(0)'

SET LAST12MONTH as
'tail(filter([Period].[Period].Month.members, ([Measures].[LST_h],[ValueType].[ValueType].[ValueType].&[1]) > 0),12)'

MEMBER [Measures].[UserDef 2] AS '[Measures].[LST_h]/8', SOLVE_ORDER=100

MEMBER [Measures].[__TEMP0__] AS 'Min( LAST12MONTH,  [Measures].[UserDef 2])',
SOLVE_ORDER=10000

MEMBER [Measures].[__TEMP1__] AS 'Max( LAST12MONTH,  [Measures].[UserDef 2])',
SOLVE_ORDER=10000

MEMBER [Measures].[__WEATHER__] AS
'Iif([Measures].[__TEMP0__] >= 0, Iif([Measures].[UserDef 2] <= [Measures].[__TEMP0__] And [Measures].[UserDef 2] > 0, 1 * 1E-09, 1 * ([Measures].[UserDef 2] - [Measures].[__TEMP0__]) / ([Measures].[__TEMP1__] - [Measures].[__TEMP0__])),
Iif([Measures].[__TEMP1__] <= 0, Iif([Measures].[UserDef 2] >= [Measures].[__TEMP1__] And [Measures].[UserDef 2] < 0, 1 * 1E-09, 1 * ([Measures].[UserDef 2] - [Measures].[__TEMP1__]) / ([Measures].[__TEMP0__] - [Measures].[__TEMP1__])),
Iif([Measures].[UserDef 2] >= 0, 1 * ([Measures].[UserDef 2] / [Measures].[__TEMP1__]),
1 * ([Measures].[UserDef 2] / [Measures].[__TEMP0__]))))',
SOLVE_ORDER=10001

SELECT {[Measures].[__WEATHER__]} ON AXIS(0)
FROM   [BC_Project]

WHERE  (
               [Period].[Period].[ACT_MONTH],
               [Project].[Project_PC].&[1],
               [TimeUtility].[TimeUtility].&[1],
               [ANF].[ANF_ANF_ProjectTypeID].[ANF_ProjectTypeID].&[1]
             )

Für die Übertragung aus anderen Anwendungen muss ggf. die SOLVE-Order von [ACT_MONTH] angepasst werden. Vor allem in Kombination mit der Verwendung von Periodenabweichungen.

Freitag, 6. Januar 2017

MDX

Veranstaltungs-Tipp

On-Demand-Webinar 8. Januar 2025 Webinar: Planung mit DeltaMaster

DeltaMaster ermöglicht die effektive Erfassung und Verarbeitung von Planzahlen mit unterschiedlichen Planungsansätzen, Simulationen, Planungshorizonten und Analyse-Funktionen. Sehen Sie selbst!

Anmeldung

DeltaMaster ermöglicht die effektive Erfassung und Verarbeitung von Planzahlen mit unterschiedlichen Planungsansätzen, Simulationen, Planungshorizonten und Analyse-Funktionen. Sehen Sie selbst!

On-Demand-Webinar 14. Januar 2025 Webinar: Selfservice mit DeltaMaster und Microsoft Excel

Mit Selfservice-BI lassen sich Managementinformationen ergänzen, z. B. aus Excel- oder Access-Dateien. Wie auf Basis einer Excel-Tabelle eine DeltaMaster-Anwendung entsteht, sehen Sie im Webinar [...]

Anmeldung

Mit Selfservice-BI lassen sich Managementinformationen ergänzen, z. B. aus Excel- oder Access-Dateien. Wie auf Basis einer Excel-Tabelle eine DeltaMaster-Anwendung entsteht, sehen Sie im Webinar.

On-Demand-Webinar 22. Januar 2025 Webinar: DeltaMaster ETL

Mit DeltaMaster ETL genügt ein Knopfdruck, um ein relationales Modell und die darauf aufbauende OLAP-Datenbank in Microsoft SQL Server Analysis Services zu erzeugen. Sehen Sie selbst!

Anmeldung

Mit DeltaMaster ETL genügt ein Knopfdruck, um ein relationales Modell und die darauf aufbauende OLAP-Datenbank in Microsoft SQL Server Analysis Services zu erzeugen. Sehen Sie selbst!

Über den Autor

Consulting

Die Bissantz-Consultants teilen ihr Wissen rund um Data-Warehouse-Projekte und Business-Intelligence-Lösungen – jede Woche ein neuer Beitrag. Auf die Würfel, fertig, los!

Letzte Blogartikel

Data Warehousing Arbeitstagkalender

Zur Berechnung von Kennzahlen, die von der genauen Anzahl der Arbeitstage abhängen, ist es notwendig, diese im Modell korrekt abzubilden. Die [...]

mehr erfahren

Forschung Personenverkehr mit Bussen und Bahnen in der DeltaApp

Für den Personenverkehr spielen Busse und Bahnen eine wichtige Rolle. Das Statistische Bundesamt stellt hierzu interessante Daten zur Verfügung [...]

mehr erfahren

Bissantz News The BI & Analytics Survey 25: Platz 1 für Bissantz in Selfservice, Mobile BI und zahlreichen weiteren Kriterien

Im BI & Analytics Survey 25 von BARC liegt Bissantz auf Platz eins in den Vergleichsgruppen Selfservice BI, Mobile BI und zahlreichen weiteren [...]

mehr erfahren

Forschung Reisegebiete mit Ankünften und Übernachtungen

Reisegebiete in Deutschland üben sowohl auf inländische als auch auf ausländische Touristen wieder ansteigende Anziehungskraft aus. Wie sieht [...]

mehr erfahren

Data Warehousing OLAP-Verarbeitung zwischendurch

In diesem Blogbeitrag geht es um die flexible Verarbeitung von Analysis-Server-Objekten aus TSQ-Prozeduren heraus.

mehr erfahren

Forschung Zeitreihenanalyse - neue Features zur Darstellung

In der Zeitreihenanalyse gab es mit den letzten DeltaMaster-Releases einige nützliche neue Features. Besonders erwähnenswert ist die leichtere [...]

mehr erfahren

Forschung Herausfiltern von Ergebnissen mit geringer Datenbasis

Erfolgs- oder Ablehnungsquoten, die auf geringen absoluten Anzahlen beruhen, stören häufig bei der Analyse. Wie man dann bei der Auswertung [...]

mehr erfahren

Data Warehousing DuPont-Treiberbäume – Darstellungsmöglichkeiten in DeltaMaster

Der Blog-Beitrag beschreibt anfangs, was unter einem Treiberbaum im Kontext der wertorientierten Unternehmenssteuerung zu verstehen ist. Im [...]

mehr erfahren

Bissantz News Bissantz zu Gast im Atvisio-Podcast: BI-Experten im Dialog

Peter Bluhm und Dr. Nicolas Bissantz sind Experten in der Welt von Business Intelligence und Data Analytics. In der aktuellen Ausgabe des Performance [...]

mehr erfahren

Forschung Beliebte Sportarten in Niedersachsen

Sportarten bei Olympischen Spielen konsumieren wir meistens eher passiv auf der Couch vor dem Fernseher. Wie es mit der aktiven Mitgliedschaft [...]

mehr erfahren

Vorheriger Artikel

DeltaMaster-Excel-Hybrid

Nächster Artikel

Sukzessiv integrierte Planung des Gewinns sowie der Finanzen

Name	Borlabs Cookie
Anbieter	Eigentümer dieser Website, Impressum
Zweck	Zur Speicherung der Einstellungen, die Sie in der Cookie-Leiste ausgewählt haben.
Cookie Name	borlabs-cookie
Cookie Laufzeit	1 Jahr

Name	Microsoft Azure CDN
Anbieter	Microsoft Azure
Zweck	Das Cookie dient der Lastverteilung der Anfragen (Load-Balancing) durch Seitennutzer bei der Nutzung des Dienstes.
Datenschutzerklärung	https://www.bissantz.de/datenschutz
Cookie Name	ARRAffinity,ARRAffinitySameSite
Cookie Laufzeit	Sitzung

Name	Polylang
Anbieter	Eigentümer dieser Website
Zweck	Speichert die aktuelle Sprache.
Cookie Name	pll_language
Cookie Laufzeit	1 Jahr

Name	cloud.bissantz.de
Anbieter	Bissantz
Zweck	Dieser Cookie dient der Erfassung der Position des Seitenbesuchers um eine möglichst effeziente und schnelle Zuteilung zu einem nahen Server zu ermöglichen.
Datenschutzerklärung	https://www.bissantz.de/datenschutz
Cookie Name	ASLBSA,ASLBSACORS
Cookie Laufzeit	Sitzung

Name	UserLike (verwendet den Webfont Loader und das Google-API CDN)
Anbieter	https://www.userlike.com/de/
Zweck	Notwendig zur umfassenden Information, z. B. auch für Datenschutzfragen.
Datenschutzerklärung	https://www.userlike.com/de/terms#privacy-policy
Host(s)	userlike.com
Cookie Laufzeit	365 Tage

Akzeptieren	Google Tag Manager
Name	Google Tag Manager
Anbieter	Google Ireland Limited, Gordon House, Barrow Street, Dublin 4, Ireland
Zweck	Cookie von Google zur Steuerung der erweiterten Script- und Ereignisbehandlung.
Datenschutzerklärung	https://policies.google.com/privacy?hl=de
Cookie Name	_ga,_gat,_gid
Cookie Laufzeit	2 Jahre

Akzeptieren	Facebook Pixel
Name	Facebook Pixel
Anbieter	Meta Platforms Ireland Limited, 4 Grand Canal Square, Dublin 2, Ireland
Zweck	Cookie von Facebook, das für Website-Analysen, Ad-Targeting und Anzeigenmessung verwendet wird.
Datenschutzerklärung	https://www.facebook.com/policies/cookies
Cookie Name	_fbp,act,c_user,datr,fr,m_pixel_ration,pl,presence,sb,spin,wd,xs
Cookie Laufzeit	Sitzung / 1 Jahr

Akzeptieren	Google Optimize
Name	Google Optimize
Anbieter	Google
Zweck	Das Google Website-Optimierungstool ist eine kostenlos verfügbare Software zur Durchführung von A/B- und Multivariatentests im Webdesign.
Datenschutzerklärung	https://policies.google.com/privacy?hl=de

Akzeptieren	Facebook
Name	Facebook
Anbieter	Meta Platforms Ireland Limited, 4 Grand Canal Square, Dublin 2, Ireland
Zweck	Mit der Zustimmung können z. B. unsere Beiträge direkt in Ihrem Facebook-Profil geteilt werden und Sie erlauben Facebook, Informationen über Sie zu verarbeiten, damit Ihnen personalisierter Content angezeigt werden kann.
Datenschutzerklärung	https://www.facebook.com/privacy/explanation
Host(s)	.facebook.com
Cookie Laufzeit	730 Tage

Dynamische Periodenauswahl für KPI-Wert und Wetterfarbe im DeltaMaster Portal

Festlegung der aktuellen Periode

MDX-Ausdruck für den KPI-Filter

MDX-Ausdruck für den KPI-Wert

MDX-Ausdruck für die KPI-Wetterfarbe

Veranstaltungs-Tipp

Über den Autor

Schlagwörter

Letzte Blogartikel

Mehr aus Data Warehousing.

Nicolas Bissantz

Diagramme im Management