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

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?

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 ?

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

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 !!! :)

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.

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

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

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"

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ć

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...