Zastępowanie pustych wartości w raportach analitycznych MS Office PerformancePoint Server 2007

Autor: Radosław Łebkowski

Opublikowano: 24 lipca 2008

Zawartość strony

	1. Zastępowanie pustych wartości w komponentach Scorecard
	2. Zastępowanie pustych wartości w komponentach Analytic Grid

Kilka dni temu natknąłem się na ciekawy wątek na forum Microsoft Technet poświęconym obszarowi monitorowania i analiz w Microsoft Office PerformancePoint Server 2007. Wątek ten dotyczył problemu zastępowania pustych wartości w raportach analitycznych umieszczanych jako elementy pulpitów menadżerskich.

Bardzo często użytkownicy systemów raportowania chcą otrzymywać informację o tym, że dane komórki raportów nie zawierają danych. W wielu przypadkach chcą również, aby puste wartości na raportach były zastępowane inną wartością – czy to numeryczną, czy też tekstową.

Niniejszy artykuł prezentuje sposób w jaki można rozwiązać ten problem w raportach analitycznych opartych na platformie MS Office PerformancePoint Server 2007 – z wykorzystaniem aplikacji Dashbard Designer.

W szczególności zostaną zaprezentowane rozwiązania oparte o zapytania MDX do modeli wielowymiarowych, z których najczęściej zasilane są tego typu systemy raportowania.

1. Zastępowanie pustych wartości w komponentach Scorecard

Aplikacja Dashboard Designer (element MS Office PerformancePoint Server 2007) udostępnia dedykowaną funkcję pozwalającą użytkownikowi skonfigurować zastępowanie pustych wartości w raporcie (karcie wyników) dowolnym ciągiem znaków.

Przykładowo, na Rysunku 1 przedstawiono kartę wyników wykonaną w aplikacji Dashboard Designer. Źródłem danych dla karty jest kostka wielowymiarowa oparta na MS SQL Server 2005 Analysis Services (SSAS). Jak można zauważyć, na przecięciu niektórych elementów wymiarów pojawiają się puste komórki (kolumny Bikes i Components). Przyczyną tego jest brak wartości w kostce OLAP dla przecięcia elementów tych wymiarów (kolumn i wierszy).

Aby zastąpić puste elementy na karcie wyników wyrażeniem „Empty”, lub dowolnym innym, wybranym przez użytkownika, należy:

  •  w aplikacji Dashboard Designer otworzyć obiekt Scorecard do edycji

  •  przejść na zakładkę Editor w środkowym panelu okna

  •  z menu głównego aplikacji wybrać zakładkę Edit, a następnie opcję View Options (patrz Rysunek 1) - opcja ta jest aktywna w momencie, gdy kursor edycji umieszczony jest na jednej z komórek komponentu Scorecard

Rysunek 1: Przykładowy Scorecard z pustymi polami, lokalizacja View Options.

Po wybraniu opcji View Options prezentowane jest okno przedstawione na Rysunku 2 .

W oknie dostępne są podstawowe opcje konfiguracyjne związane z prezentacją obiektu Scorecard na publikowanym pulpicie.

Opcje w sekcji Messages umożliwiają zdefiniowanie komunikatów wyświetlanych w przypadku braku wartości, lub błędów związanych z ich publikowaniem (np. problemem z aktualizacją wartości ze źródła danych lub błędną formułą wskaźnika KPI).

Sekcja View zawiera dodatkowe opcje konfiguracji widoku, w tym m.in. włączenie opcji dodawania przez użytkowników komentarzy do wskaźników KPI (Allow Comments), włączenie opcji zwijania/rozwijania hierarchii elementów KPI, włączenie opcji podpowiedzi (wprowadzonych w polu Description wskaźnika KPI) oraz zawijania długich opisów wierszy.

Aby zastapić puste wartości w obiekcie Scorecard należy zaznaczyć opcję Indicate empty cells , a następnie w polu Message in grid wprowadzić wartość, która będzie prezentowana w pustych komórkach Scorecard-u (zobacz Rysunek 2).

Rysunek 2: View options w aplikacji Dashboard Designer.

Po zatwierdzeniu zmian wszystkie puste wartości zostaną zastąpione wprowadzonym przez użytkownika tekstem – tak, jak na Rysunku 3.

Rysunek 3: Scorecard z nowymi wartościami w pustych polach.

Jak widać aplikacja Dashboard Designer udostępnia dedykowaną funkcję pozwalającą zastąpić puste wartości w obiekcie Scorecard. Niestety taka funkcja nie istnieje w przypadku obiektów Analytic Grid.

Zatem w jaki sposób osiągnąć podobną funkcjonalność?

Poniżej zostanie zaprezentowanych kilka rozwiązań tego problemu.

 Do początku strony

2. Zastępowanie pustych wartości w komponentach Analytic Grid

Niestety aplikacja Dashboard Designer w MS Office PerformancePointServer 2007 ver.1 udostępnia opcję zastępowania pustych wartości tylko dla komponentów typu Scorecard. Aby osiągnąć podobną funkcjonalności w przypadku często stosowanych komponentów analitycznych Analytic Grid możemy wykorzystać m.in. następujące metody:

  •  implementacja dodatkowych miar kalkulowanych (calculated measures) w kostkach OLAP będących źródłem danych dla obiektu Analytic Grid

  •  implementacja zapytań MDX stosowanych bezpośrednio w obiektach Analytic Grid

*    a.  Przygotowanie miar kalkulowanych w kostkach OLAP (SSAS)*

W naszym przykładzie załóżmy, że chcemy zamienić wszystkie puste wartości miary [Measures].[Order Count]. Zapytanie MDX, które wykorzystujemy w obiekcie Analytic Grid ma postać następującą:

SELECT { [Product].[Product Categories].[All Products].CHILDREN } ON COLUMNS,

{ [Date].[Calendar].[Month].&[2004]&[7].CHILDREN } ON ROWS  

FROM [Adventure Works] 

WHERE ( [Measures].[Order Count])

CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL

Wynik wykonania zapytania w komponencie Analytic Grid przedstawiony jest na Rysunku 4.

Rysunek 4: Raport Analytic Grid z pustymi wartościami.

Jak można zauważyć dwie kolumny w raporcie (Bikes and Componets) zawierają puste wartości. Naszym celem będzie zastąpienie ich wartością “0”.

Możemy to osiągnąć implementując nową miarę kalkulowaną bezpośrednio w definicji kostki OLAP – będącej źródłem danych dla naszego raportu.

Poniżej przedstawiono kod MDX tworzący nową miarę kalkulowaną [Measures].[Order Count 2] opartą na mierze pierwotnej [Measures].[Order Count] .

Przedstawiony fragment skryptu należy umieścić w skrypcie kalkulacji (w zakładce Calculations projektu kostki OLAP w Business Intelligence Development Studio (BIDS)).

CREATE MEMBER CURRENTCUBE.[Measures].[Order Count 2]

 AS IIF(ISEMPTY([Measures].[Order Count]), 

       0,

      [Measures].[Order Count]

    )

Teraz musimy zmodyfikować nasze pierwotne zapytanie MDX wprowadzając nową miarę do zapytania SELECT.

SELECT { [Product].[Product Categories].[All Products].CHILDREN } ON COLUMNS,

{ [Date].[Calendar].[Month].&[2004]&[7].CHILDREN } ON ROWS  

FROM [Adventure Works] 

WHERE ( [Measures].[Order Count 2])

CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL

Na Rysunku 5 przedstawiono końcowy raport, na którym puste komórki zastąpione zostały wartością ‘0’.

Rysunek 5: Analytic Grid z zastąpionymi pustymi wartościami.

*    b.  Modyfikacja zapytań MDX i wykorzystanie ich w Analytic Grid*

Taki sam efekt, jak powyżej, możemy uzyskać przygotowując odpowiednie zapytanie MDX umieszczając w nim dodatkowe miary kalkulowane, w których zastępujemy puste wartości. W ten sposób nie musimy modyfikować struktury kostki OLAP (tak jak w poprzednim przypadku).

Poniżej przedstawiono zapytanie MDX wykorzystujące dodatkową miarę [Order Count 2].

WITH 

MEMBER [Measures].[Order Count 2] as 

IIF(ISEMPTY([Measures].[Order Count]), 0, [Measures].[Order Count])  

SELECT { [Product].[Product Categories].[All Products].CHILDREN } ON COLUMNS ,

{ [Date].[Calendar].[Month].&[2004]&[7].CHILDREN } ON ROWS  

FROM [Sales Summary] 

WHERE ( [Measures].[Order Count 2] )

CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL

Wynik powyższego zapytania będzie taki sam, jak ten, przedstawiony na Rysunku 5.

Do zastąpienia pustych wartości wykorzystana została dedykowana funkcja ISEMPTY.

*    c.  Zastępowanie pustych wartości ciągami tekstowymi w zapytaniach MDX*

W poprzednich przykładach zastępowaliśmy puste wartości innymi wartościami numerycznymi. Jednak w wielu przypadkach koniecznym okazuje się zastąpienie pustej wartości wartością tekstową, np. “Empty value”. Jak zatem osiągnąć to w zapytaniach MDX?

Nie jest to zbyt skomplikowane. Często miary w zapytaniach MDX i strukturach wielowymiarowych są kojarzone tylko i wyłącznie z wartościami numerycznymi. Okazuje się jednak, że odpowiednio przygotowana miara kalkulowana pozwala nam zwracać również wartości tekstowe. W tym przykładzie wykorzystamy tę możliwość.

Modyfikujemy nasze poprzednie zapytanie MDX wprowadzając nową miarę o charakterze technicznym.

Przykładowy kod zapytania MDX przedstawiono poniżej.

WITH 

MEMBER [Measures].[Empty value] AS 1

MEMBER [Measures].[Order Count 3] 

AS IIF(isempty([Measures].[Order Count]), [Measures].[Empty value].Name, 

[Measures].[Order Count])  

SELECT { [Product].[Product Categories].[All Products].CHILDREN } ON COLUMNS ,

{ [Date].[Calendar].[Month].&[2004]&[7].CHILDREN } ON ROWS  

FROM [Adventure Works] 

WHERE ([Measures].[Order Count 3] )

CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL

Jak łatwo zauważyć zapytanie zostało uzupełnione o miarę [Measures].[Empty value] zawierającą dowolną wartość (w tym przypadku 1). W naszym zapytaniu wykorzystamy tylko nazwę tej miary, dlatego jej wartość może być dowolna. Nazwa miary będzie wykorzystana jako tekst, który zastąpi puste wartości w naszym raporcie Analytic Grid.

Kolejną miarą jest [Measures].[Order Count 3]. Miara ta ma charakter analogiczny do tych przedstawionych w poprzednich przykładach (funkcje IIF oraz ISEMPTY wykorzystujemy to zastąpienia pustej wartości miary pierwotnej [Measures].[Order Count] wyrażeniem [Measures].[Empty value].Name, które zwraca nazwę obiektu [Measures].[Empty value]).

Wynik zastosowania powyższego zapytania w komponencie Analytic Grid został przedstawiony na Rysunku 6.

Rysunek 6: Puste wartości zastąpione wartością tekstową.

Jak można zauważyć wszystkie puste wartości zostały zastąpione ciągiem znaków „ Empty value ”.

Chcąc wprowadzić inny ciąg tekstowy, wystarczy zmienić nazwę miary [Measures].[Empty value] i zmodyfikować odwołania do tej miary w zapytaniu MDX.

Zatem dzięki odpowiedniemu przygotowaniu zapytania MDX puste wartości miar możemy zastąpić nie tylko wartościami numerycznymi, ale również tekstowymi.

Radosław Łebkowski (MCTS, MCITP DBA, MCITP DB Dev, MCITP BI Dev) Technology Solution Professional – Data Platform Absolwent Wydziału Elektroniki i Technik Informacyjnych Politechniki Warszawskiej. Od początku w pracy zawodowej zajmował się administracją i optymalizacją systemów baz danych opartych na platformie Microsoft SQL Server, m.in. u jednego z polskich operatorów telekomunikacyjnych. Od kilku lat zajmuje się wdrażaniem hurtowni danych oraz systemów Business Intelligence, a jego pasją jest zgłębianie tajników rozwiązań z obszaru Data Mining oraz Corporate Performance Management. Doświadczenie zdobywał m.in. w firmie Bonair S.A., gdzie brał udział w wielu wdrożeniach dużych systemów BI i CPM dla firm oraz instytucji administracji publicznej. Obecnie pracuje w firmie Microsoft na stanowisku Technology Solution Professional w dziale Enterprise and Parter Group (EPG), gdzie jest odpowiedzialny za wsparcie klientów sektora Enterprise w obszarze rozwiązań platformy bazodanowej i Business Intelligence. Posiada certyfikaty MCTS i MCITP z obszaru SQL Server 2005 i 2008 (MCITP Business Intelligence Developer, MCITP Database Administrator, MCITP Database Developer) oraz PerformancePoint Server 2007. Jest również aktywnym członkiem Polish SQL Server User Group (PLSSUG) w Warszawie oraz portalu WSS.pl.

Do początku strony