CiAgICA8IS0tIExpbmtlZEluIC0tPgogICAgPHNjcmlwdCB0eXBlPSJ0ZXh0L2phdmFzY3JpcHQiPgogICAgICAgIF9saW5rZWRpbl9wYXJ0bmVyX2lkID0gIjEyMzUwNzMiOwogICAgICAgIHdpbmRvdy5fbGlua2VkaW5fZGF0YV9wYXJ0bmVyX2lkcyA9IHdpbmRvdy5fbGlua2VkaW5fZGF0YV9wYXJ0bmVyX2lkcyB8fCBbXTsKICAgICAgICB3aW5kb3cuX2xpbmtlZGluX2RhdGFfcGFydG5lcl9pZHMucHVzaChfbGlua2VkaW5fcGFydG5lcl9pZCk7CiAgICA8L3NjcmlwdD48c2NyaXB0IHR5cGU9InRleHQvamF2YXNjcmlwdCI+CiAgICAgICAgKGZ1bmN0aW9uKCl7dmFyIHMgPSBkb2N1bWVudC5nZXRFbGVtZW50c0J5VGFnTmFtZSgic2NyaXB0IilbMF07CiAgICAgICAgICAgIHZhciBiID0gZG9jdW1lbnQuY3JlYXRlRWxlbWVudCgic2NyaXB0Iik7CiAgICAgICAgICAgIGIudHlwZSA9ICJ0ZXh0L2phdmFzY3JpcHQiO2IuYXN5bmMgPSB0cnVlOwogICAgICAgICAgICBiLnNyYyA9ICJodHRwczovL3NuYXAubGljZG4uY29tL2xpLmxtcy1hbmFseXRpY3MvaW5zaWdodC5taW4uanMiOwogICAgICAgICAgICBzLnBhcmVudE5vZGUuaW5zZXJ0QmVmb3JlKGIsIHMpO30pKCk7CiAgICA8L3NjcmlwdD4KICAgIDxub3NjcmlwdD4KICAgICAgICA8aW1nIGhlaWdodD0iMSIgd2lkdGg9IjEiIHN0eWxlPSJkaXNwbGF5Om5vbmU7IiBhbHQ9IiIgc3JjPSJodHRwczovL3B4LmFkcy5saW5rZWRpbi5jb20vY29sbGVjdC8/cGlkPTEyMzUwNzMmZm10PWdpZiIgLz4KICAgIDwvbm9zY3JpcHQ+CiAgICA8IS0tIEVuZCBMaW5rZWRJbiAtLT4KICAgIA==
Generic filters
Exact matches only
Search in title
Search in excerpt
Search in content

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.