Bartłomiej Głowacki

Bartłomiej Głowacki BI Support Manager

Temat: MDX, calculated measure, pytanie dla specjalistów w...

Pytanie w tematyce MDX, OLAP

Pracuję nad stworzeniem tzw. "calculated measure", która ma być obliczana na podstawie wartości jednej z istniejących miar (measures) i jednego z atrybutów.

Konkretniej, chodzi o sprzedaż produktów, które mogą być sprzedawane jako opakowania i jako indywidualne sztuki. Atrybutem produktu jest jego [Product split code], dwa produkty z tym samym identyfikatorem mają się sumować. Np. puszka coli jest produktem X, natomiast zgrzewka produktem Y, ale ponieważ posiadają ten sam [Product Split Code] więc mając 3 puszki i 5 zgrzewek, można powiedzieć, że mamy 5,25 zgrzewki. To jaki jest ekwiwalent produktów dla opakowania zbiorczego mamy w "propertisie" o nazwie [Product Case Equivalent Amount]

Sprzedaż zawiera miary, w tym przypadku interesuje nas sprzedana ilość [Measures].[Invoice Qty]

Do tego dochodzi sytuacja, że w wymiarze produktów jest hierarchia kat.nadrzędna/kategoria/grupa/produkt. Produkt ze sprzedażą połączony jest poprzez [Product_ID]

Okej, teraz problem.

Chciałbym sobie wyliczyć prostą (z pozoru) rzecz, zapytanie od którego wychodzę wygląda tak:

with

-- to wyświetla mi ekwiwalent dla konkretnego produktu
member [Measures].[Case Equivalent Amount] as
CoalesceEmpty([Product].[Product].CurrentMember.Properties("Product Case Equivalent Amount"),0)

-- to wyświetla mi ilość w opakowaniach dla danego produktu
member [Measures].[Case Equivalent Qty] as
[Measures].[Invoice Qty] * [Measures].[Case Equivalent Amount]

SELECT
{
[Measures].[Invoice Qty],
[Measures].[Case Equivalent Qty]
}
ON COLUMNS,
{
[Product].[Product].Members
}
ON ROWS
FROM [MWP DWH Cube]
where
{
[Product].[Product Split Code].&[1-19sdfs]
}

Niestety wynik jest mało satysfakcjonujący:

All 132 0.00
Prod single 54 9.18
Prod 6pack 78 78.00

Oczywiście brakuje mi w All wartości 87.18! Co mogę zrobić, żeby tam się znalazła ? ! ? !

Próbuję wszelkich kombinacji funkcji takich jak:
SUM
Descendants

Ale albo nie wyświetla mi nic, albo zgłasza błąd, nie wiem jak się do tego dobrać, jakieś rady?

Dzięki!!!

PS. Uprzedzam pytania typu, żeby zrobić to inaczej, w tabelce/widoku itd. źródłowym zrobiłbym to w 4 godziny z testowaniem i wdrożeniem, trzeba byłoby jednak przebudować kostkę, na co niektórzy "decydenci" bardzo się krzywią, więc od 2 dni siedzę i dłubię w MDX, który niestety jest dla mnie cały czas nowością.Bartłomiej Głowacki edytował(a) ten post dnia 20.02.12 o godzinie 23:50

konto usunięte

Temat: MDX, calculated measure, pytanie dla specjalistów w...

Problem zdaje się polega na tym, że w wymiarze produkt na poziomie ALL jako properties("Product Case Equivalent Amount) nie masz nic, dlatego wpisywane jest 0, no i przy mnożeniu przez ilość z faktury zwracana jest wartość 0 (total nie pokazuje zsumowanych wartości cząstkowych).
Teraz tylko pytanie, co z tym faktem zrobić. :)
Bartłomiej Głowacki

Bartłomiej Głowacki BI Support Manager

Temat: MDX, calculated measure, pytanie dla specjalistów w...

pytanie pomocnicze, czy poziom ALL odpowiada TOTAL, np. kiedy przeglądamy kostke w browserze w SSMS?

konto usunięte

Temat: MDX, calculated measure, pytanie dla specjalistów w...

Moim zdaniem tak. Dorzuć do swojego zapytania coś takiego:

member [Measures].[test] as ([Measures].[Invoice Qty]+1)

dodaj w select [Measures].[test] i wtedy zobaczysz, jak to się liczy (albo jeszcze dorzuć [Measures].[Case Equivalent Amount], żeby już było wszystko).
Bartłomiej Głowacki

Bartłomiej Głowacki BI Support Manager

Temat: MDX, calculated measure, pytanie dla specjalistów w...

widzę jak się liczy, dodaje wszędzie 1, nie sumuje w All wartości dodanych w podrzędnych elementach

po wielu eksperymentach użyłem takiej kombinacji :)

member [Measures].[Case Equivalent] as
CoalesceEmpty([Product].[Product Case Equivalent Amount].MemberValue,0)

member [Measures].[Number of Cases] as
SUM({descendants([Product].[Product Categorisation].CurrentMember,
[Product].[Product Categorisation].[Product])},
[Measures].[Case Equivalent] ) * [Measures].[Invoice Qty]

i w ALL (total) pojawia się wartość, niestety jest to wartość dużo za duża, czyżby mój skrypt szedł do kategorii nadrzędnej, i sumował wszystkie produkty, mimo filtra na konkretny kod w sekcji WHERE ?

konto usunięte

Temat: MDX, calculated measure, pytanie dla specjalistów w...

Obawiam się, że pokazuje iloczyn sumy ilości z faktury i sumy ekwiwalentu. Ja bym jednak zmodyfikowała kostkę. Chyba że ktoś inny ma jeszcze jakiś pomysł.
Bartłomiej Głowacki

Bartłomiej Głowacki BI Support Manager

Temat: MDX, calculated measure, pytanie dla specjalistów w...

muszę chyba poeksperymentować z funkcjami FILTER i EXISTING

PS. zastanawiałem się nad pytaniem na interview kiedy będę szukał developera ze znajomością MDX we Wrocławiu, ciekawe czy ktoś będzie w stanie rozwiązać ten problem :D

konto usunięte

Temat: MDX, calculated measure, pytanie dla specjalistów w...

Podszedłbym do tematu inaczej.
Jeśli kalkulację [Measures].[Case Equivalent Qty] wrzucimy do MDX Script, to następnie można będzie położyć na niej SCOPE zmieniający sposób obliczania w koordynacie All wymiaru Product, bo tam jest potrzebna agregacja podrzędnych (czyli AGGREGATE) a nie mnożenie. To klasyczny problem wartość = cena * ilość na poziomach wyższych niż liście.

Piszę tak trochę z pamięci, bo nie mam przed sobą SSAS.

pozdrawiamGrzegorz Stolecki edytował(a) ten post dnia 22.02.12 o godzinie 16:03
Bartłomiej Głowacki

Bartłomiej Głowacki BI Support Manager

Temat: MDX, calculated measure, pytanie dla specjalistów w...

zaczęło działać po dodaniu w środku mojej SUMy

{EXISTING [Product].[Product Categorisation].Members}

pozdrawiam i dzięki za pomoc !!! :)
Bartłomiej Głowacki

Bartłomiej Głowacki BI Support Manager

Temat: MDX, calculated measure, pytanie dla specjalistów w...

no dobra, nie do końca :( działa dobrze tylko jeśli w filtrach (tzn. where) zastosuję ograniczenia na poziomie hierarchii "produkt"

Patrzę na zapytania, które generuje Excel, on ogranicza wyniki w klauzuli select, do whera whodzi wszystko, wydaje mi się że to powoduje, że wyniki wychodzą dużo za duże.

konto usunięte

Temat: MDX, calculated measure, pytanie dla specjalistów w...

Eeee, nie przejmuj się za bardzo treścią MDX generowaną przez Excel'a. Pewnie jeszcze tworzy jakieś set'y w ramach sesji, potem je usuwa. Kiedyś było znacznie gorzej...
Zapytanie może nie jest optymalne, ale wartości są prawidłowe.

To co napisałeś o działaniu formuły tylko na pewnym poziomie potwierdza, że dobry może być SCOPE w skrypcie kalkulacyjnym kostki.

pozdrawiam
Bartłomiej Głowacki

Bartłomiej Głowacki BI Support Manager

Temat: MDX, calculated measure, pytanie dla specjalistów w...

To co napisałeś o działaniu formuły tylko na pewnym poziomie potwierdza, że dobry może być SCOPE w skrypcie kalkulacyjnym kostki.

No i powiem Ci w tajemnicy, że działa to dobrze (prawie), co prawda dopiero teraz znalazłem trochę czasu na powrót do tego problemu i rozkmninianie niuansów funkcji SCOPE (np. to że nie działa ona z poziomu SSMS).

Dzięki zastosowaniu osobnego SCOPE dla każdego z poziomów hierarchii jestem w stanie otrzymać poprawne sumy dla każdego poziomu (masz za to piwo ;))

Teraz jednak znowu wracamy do problemu WHERE/filtrowania. Jeśli zaznaczę sobie kilka konkretnych produktów i dodam do filtra, w dalszym ciągu na wyższych poziomach pokazują się (poprawne) sumy ale niestety dla całej podkategorii !!!

2 measures na początek:

[Box Equivalent Amount] =
IIF([Product].[Product].CurrentMember.Properties("Is Split")="N",1,
CoalesceEmpty([Product].[Product].CurrentMember.Properties("Box Equivalent"),0))
----
[Box Equivalent Quantity] = 0

I Scope:
SCOPE ([Product].[Product Hierarchy].Members,[Measures].[Box Equivalent Quantity]);
this =
Sum(descendants([Product].[Product Hierarchy].CurrentMember,
[Product].[Product Hierarchy].[Product])
,([Measures].[Box Equivalent Amount] * [Measures].[Sales Quantity])
);
END SCOPEBartłomiej Głowacki edytował(a) ten post dnia 09.03.12 o godzinie 13:08
Bartłomiej Głowacki

Bartłomiej Głowacki BI Support Manager

Temat: MDX, calculated measure, pytanie dla specjalistów w...

jestem zawiedziony, myślałem że ktoś będzie w stanie chociaż powiedzieć: "to se ne da" :D
Mariusz Buraczewski

Mariusz Buraczewski Starszy programista,
Asseco Business
Solutions S.A.

Temat: MDX, calculated measure, pytanie dla specjalistów w...

To może potrzebna jest nowa hierarchia z [Product split code] i produktem na końcu.

Puszki coli
- puszka
- zgrzewkaMariusz Buraczewski edytował(a) ten post dnia 12.03.12 o godzinie 12:31
Bartłomiej Głowacki

Bartłomiej Głowacki BI Support Manager

Temat: MDX, calculated measure, pytanie dla specjalistów w...

hierarchia to drugorzędna sprawa, moim zdaniem produkt nie powinien być najniższym ziarnem, niższy od niego powinien być "product package", wtedy byśmy mieli napoje-> cola -> puszka coli / zgrzewka coli

ale to już logika, mnie pokonała "technika" i gość, który przekonał mnie żeby robić to w MDXie a nie w bazie, mówiąc "don't overcomplicate things :D"

konto usunięte

Temat: MDX, calculated measure, pytanie dla specjalistów w...

Bałbym się dokładania nowej hierarchii, bo przybędzie kolejna koordynata do obsługi.
Ten scope to jest jedyny jaki masz zdefiniowany? Bo coś mi się zdaję, że gra tu coś innego.

Polecałbym przepuszczenie zapytania w MDX Studio. Tam dokładnie da się zobaczyć jakie koordynaty są aktualne dla każdej hierarchii atrybutowej w kostce.

A z tym niższym ziarnem to może być dobry pomysł. Ja wyznaję zasadę, że wszystko należy liczyć najwcześniej jak się da w kolejności źródło, ETL, Data Source View, skrypt kalkulacyjny, zapytania.

BTW: przepraszam za opóźnienia, ale ostatnio mam masę pracy...

pozdrawiamGrzegorz Stolecki edytował(a) ten post dnia 16.03.12 o godzinie 10:30
Bartłomiej Głowacki

Bartłomiej Głowacki BI Support Manager

Temat: MDX, calculated measure, pytanie dla specjalistów w...

hurtownie w SQL Serverze to dla mnie trochę nowa dziedzina, więc ugiąłem się mimo że wiedziałem, że najlepszy sposób to robić wszystko w hurtowni. Skoro "spece" mówili że to takie proste, posłuchałem, potem żaden z nich nie potrafił mi pomóc. Jednak czasem warto posłuchać wewnętrznego głosu, wyrobionego na Teradacie i Oraclu :)

jedynie z ciekawości chciałbym wiedzieć, czy to co chciałem zrobić jest w ogóle wykonalne, na posta na "stackoverflow" nie dostałem odpowiedzi:
http://stackoverflow.com/questions/9633732/mdx-ssas-ca...

nie lubię kostek i MDX :) są takie ... mało uniwersalne. SQLem zrobisz wszystko w locie, tutaj trzeba się męczyć

konto usunięte

Temat: MDX, calculated measure, pytanie dla specjalistów w...

Bartłomiej Głowacki:
nie lubię kostek i MDX :) są takie ... mało uniwersalne. SQLem zrobisz wszystko w locie, tutaj trzeba się męczyć

czas wykonania SQL-a cie ogranicza
Bartłomiej Głowacki

Bartłomiej Głowacki BI Support Manager

Temat: MDX, calculated measure, pytanie dla specjalistów w...

offtopic ;-)

Ja to widzę tak. Klient zgłasza się do mnie, mówi że mu się sumy nie zgadzają na raporcie. Siedzę nad tym X czasu i widzę, że kalkulacja została zaprojektowana z myślą o nieco innym raporcie, a klient wstawił daną miarę nie tam gdzie początkowo miała ona być. Developer wcześniej mógł to zrobić bardziej uniwersalnie, ale ponieważ gonił go czas i miał konkretne wymagania, to zrobił to w najłatwiejszy możliwy sposób. Często okazuje się, że zmian należałoby zrobić tyle, że cała zabawa nie jest warta świeczki (budowa od nowa wymiaru)
Czas trwania od zgłoszenia problemu do implementacji rozwiązania jest dramatycznie długi w porównaniu z Bazą + narzędziem raportującym (tupu BO), dla mnie to jest kluczowe.

Teraz czas wykonywania SQL. Miałem do czynienia z tabelką CDR (rejestr rozmów telefonicznych), gdzie 1 dzień to było 7GB danych, tabelka przechowywała 3 miesiące. Przy odpowiednich indeksach i mocy Teradaty, rozmowy jednego abonenta można zwrócić w ciągu sekund/ułamków sekund.
Zapytania wyższego ziarna można wykonywać na tzw. summary tables (dzienne, miesięczne itd.), które są pewnego rodzaju odpowiednikiem kostek. Jeśli masz podsumowanie rozmów abonenta to nie musisz ich agregować za każdym razem, dopiero przy "drill down", jeśli chcesz zobaczyć gdzie dzwonił danego dnia, wykonuje się nowe zapytanie, które jedzie po indeksach (data, nr abonenta) i zwraca wyniki bardzo szybko.

OLAP został wymyślony trochę aby zasypać dziurę w możliwościach technicznych serwerów i SQL SERVER zaadaptował to bardzo skutecznie. Bardziej efektywne będzie zawsze znalezienie drogi do istniejącej wartości w strukturze danych niż obliczenie tego w locie. Problemem jest to, że musisz znać wszystkie pytania zanim zostaną zadane, żeby mieć pod ręką wszystkie możliwe odpowiedzi...

konto usunięte

Temat: MDX, calculated measure, pytanie dla specjalistów w...

a widziałaś odbiorcę drilującego w SQL-u? Ja tam wole do tego celu Excel-a i tabelę przestawną wpiętą w odpowiednio spreparowany raport, sam sobie wszystko zrobi

agregaty danych fajna sprawa, ale spłaszcza dane np.dane miesięczne z callsów i struktura która uległa modyfikacji w trakcie miesiąca i mamy albo szybko albo dobrze

nie każdy ma teradatę

Następna dyskusja:

OLAP Calculated Measure - i...




Wyślij zaproszenie do