Dobry przykład MDX vs SQL dla zapytań analitycznych

Czy ktoś może mi pokazać dobry przykład przewagi MDX nad zwykłym SQL podczas wykonywania zapytań analitycznych? Chciałbym porównać zapytanie MDX z zapytaniem SQL, które daje podobne wyniki.

Wikipedia mówi :

Chociaż niektóre z nich można przetłumaczyć na tradycyjny SQL, często wymagałoby to syntezy niezgrabnych wyrażeń SQL, nawet w przypadku bardzo prostych wyrażeń MDX.

Ale nie ma ani cytatu ani przykładu. Jestem w pełni świadomy, że podstawowe dane muszą być zorganizowane inaczej, a OLAP będzie wymagał więcej przetwarzania i przechowywania na wkładkę. (Moja propozycja to przejście z Oracle RDBMS do Apache Kylin + Hadoop )

Kontekst: Próbuję przekonać moją firmę, że powinniśmy sprawdzać bazę danych OLAP zamiast bazy danych OLTP. Większość zapytań SIEM korzysta z grupowania według, sortowania i agregacji. Oprócz zwiększenia wydajności, myślę, że zapytania OLAP (MDX) byłyby bardziej zwięzłe i łatwiejsze do odczytu / zapisu niż równoważne SQL OLTP. Konkretny przykład doprowadziłby do sedna sprawy, ale nie jestem ekspertem od SQL, a tym bardziej MDX ...

Jeśli to pomaga, oto przykładowe zapytanie SQL związane z SIEM dotyczące zdarzeń zapory, które miały miejsce w ostatnim tygodniu:

SELECT   'Seoul Average' AS term, 
         Substr(To_char(idate, 'HH24:MI'), 0, 4) 
                  || '0'        AS event_time , 
         Round(Avg(tot_accept)) AS cnt 
FROM     ( 
                SELECT                     * 
                FROM   st_event_100_#yyyymm-1m# 
                WHERE  idate BETWEEN trunc(sysdate, 'iw')-7 AND trunc(sysdate, 'iw')-3 #stat_monitor_group_query#
                UNION ALL 
                SELECT * 
                FROM   st_event_100_#yyyymm# 
                WHERE  idate BETWEEN trunc(sysdate, 'iw')-7 AND trunc(sysdate, 'iw')-3 #stat_monitor_group_query# ) pm
GROUP BY substr(to_char(idate, 'HH24:MI'), 0, 4) 
                  || '0' 
UNION ALL 
SELECT   'today' AS term , 
         substr(to_char(idate, 'HH24:MI'), 0, 4) 
                  || '0'        AS event_time , 
         round(avg(tot_accept)) AS cnt 
FROM     st_event_100_#yyyymm# cm 
WHERE    idate >= trunc(sysdate) #stat_monitor_group_query# 
GROUP BY substr(to_char(idate, 'HH24:MI'), 0, 4) 
                  || '0' 
ORDER BY term DESC, 
         event_time ASC

MDXi SQLnie są w żaden sposób taki sam, a często nawet nie są porównywalne, ponieważ są one zapytań multidimensionali relational databasesodpowiednio. Za pomocą MDX nie można przeszukiwać istniejącej relacyjnej bazy danych.

Główną zaletą korzystania z modelu wielowymiarowego i korzystania z MDX do zapytań jest to, że odpytujesz wstępnie zagregowane dane i że MDX jest zoptymalizowany do zapytania w sposób statystyczny, a nie relacyjny. Już nie wyszukujesz wierszy i tabel w celu uzyskania płaskiego zestawu wyników, ale używasz krotek i zestawów do wycinania i agregowania wielowymiarowej kostki.

Pomyśl o tym w ten sposób: jeśli użyjesz zapytania SQL, aby uzyskać całkowitą kwotę sprzedaży dla określonej grupy towarów, musisz napisać zapytanie podsumowujące wszystkie wiersze faktury dla wszystkich towarów w grupie towarów. Jeśli używasz kostki i masz agregacje na poziomie grupy produktów, wynik jest obliczany podczas przetwarzania, a agregacje są przechowywane dla każdej grupy towarów, dzięki czemu zapytania są natychmiastowe.

Multidimensional i MDX to zupełnie inna koncepcja niż relacyjny SQL oparty na zestawie.

Twój przykład może stać się o wiele prostszy, ponieważ wykonujesz transformacje, takie jak parsowanie dat podczas procesu ładowania danych, a porównanie z ostatniego miesiąca może być calculated measure. Twoja średnia w Seulu i dzisiaj może byćcalculated members

Jeśli twoje kostki są dobrze zaprojektowane dla twoich wymagań, uważam, że możesz kroić i kroić zestaw danych z twojego przykładu bez potrzeby pisania zapytań, ale rób to w przestawnym lub innym narzędziu analitycznym.

Z drugiej strony nie ma „przepisywania SQL w MDX”. To wymaga sporej wiedzy, aby zrobić to dobrze i innego sposobu myślenia. Pomyśl o diagramach Venna zamiast zestawów wyników.

Aby podać przykład z wykorzystaniem bazy danych Adventureworks, wyobraź sobie wymóg wyliczenia liczby zamówień sprzedaży według klienta w kategorii rowery.

Jeśli zrobiłeś to za pomocą SQL, musiałbyś napisać zapytanie, które zlicza liczbę zamówień sprzedaży zawierających wiersz z produktem, który przypadkowo należy do kategorii rowerów i dołączyć to do tabeli klientów, aby stało się to dość złożonym zapytaniem .

-- need distinct count, we're counting orders, not order lines
SELECT count(DISTINCT soh.salesorderid)
    ,pers.FirstName + ' ' + pers.LastName
FROM sales.SalesOrderDetail sod
-- we need product details to get to the category
INNER JOIN Production.Product p ON sod.ProductID = p.ProductID
-- but we need to pass via subcategories
INNER JOIN Production.ProductSubcategory psc ON p.ProductSubcategoryID = psc.ProductSubcategoryID
-- we finally get to the category
INNER JOIN Production.ProductCategory pc ON psc.ProductCategoryID = pc.ProductCategoryID
-- we also need the headers because that's where the customer is stored
INNER JOIN sales.SalesOrderHeader soh ON sod.SalesOrderID = soh.SalesOrderID
-- finally the customer, but we don't have his name here
INNER JOIN sales.Customer c ON soh.CustomerID = c.CustomerID
-- customers
INNER JOIN Person.Person pers ON c.PersonID = pers.BusinessEntityID
-- filter on bikes
WHERE pc.Name = 'bikes'
-- but the customers table doesn't contain the concatenated name
GROUP BY pers.FirstName + ' ' + pers.LastName;

W MDX (pod warunkiem, że twoja kostka jest dobrze zaprojektowana do tego wymagania) możesz po prostu pisać, ponieważ logika i złożoność przeniosły się gdzie indziej:

SELECT [Measures].[Internet Order Count] ON COLUMNS,
[Customer].[Customer].Members ON ROWS
FROM [Adventure Works]
WHERE [Product].[Product Categories].[Category].[Bikes]

Kostki / bazy danych OLAP mają następujące cechy:

• Uzyskaj już zagregowane informacje zgodnie z potrzebami użytkownika.
• Łatwy i szybki dostęp
• Możliwość manipulowania danymi agregowanymi w różnych wymiarach
• Sześcian używa klasycznych funkcji agregacyjnych min, max, count, sum, avg, ale może także używać określonych funkcji agregujących.

MDX a SQL:

MDX służy do nawigacji po wielowymiarowych bazach danych i definiowania zapytań na wszystkich ich obiektach (wymiarach, hierarchiach, poziomach, elementach i komórkach) w celu uzyskania (po prostu) reprezentacji tabel przestawnych.

MDX wykorzystuje wiele identyczny jak SQL słów kluczowych, takich jak SELECT, FROM, WHERE. Różnica polega na tym, że SQL tworzy widoki relacyjne, podczas gdy MDX tworzy wielowymiarowe widoki danych .

Różnicę widać także w ogólnej strukturze dwóch języków:

Zapytanie SQL: SELECT column1, column2, ..., column FROM table
zapytanie MDX:SELECT axis1 ON COLUMNS, axis2 ON ROWS FROM cube

FROMokreśla źródło danych:
W SQL: jedna lub więcej tabel
W MDX: kostka

SELECT wskazuje wyniki pożądane do odzyskania przez zapytanie:

W SQL:

• Zobacz dane w dwóch wymiarach (wiersze i kolumny)
• Rzędy mają tę samą strukturę zdefiniowaną przez kolumny

W MDX:

• Dowolna liczba wymiarów tworzących wyniki zapytania.
• Termin oś używany w celu uniknięcia pomyłki z wymiarami kostki.
• Nie ma specjalnego znaczenia dla wierszy i kolumn, ale musisz zdefiniować każdą oś: oś 1 określa oś poziomą, a oś 2 określa oś pionową.

Przykład zapytania MDX:

Miary : Cena jednostkowa, Ilość, Rabat, Kwota sprzedaży, Fracht
Wymiar :
Hierarchia czasu : Rok> Kwartał> Miesiąc> z członkami:

• Rok: 2010, 2011, 2012, 2013, 2014

• Kwartał: Q1, Q2, Q3, Q4

• Miesiąc: styczeń, luty, marzec,…

Wymiar :
Hierarchia klientów : Kontynent> Kraj> Stan> Miasto z członkami:

• Miasto: Paryż, Lyon, Berlin, Köln, Marsylia, Nantes…

• Państwo: Loire atlantique, Bouches du Rhône, Bas Rhin, Torino…

• Kraj: Austria, Belgia, Dania, Francja, ...

• Poziom kontynentu: Europa, Ameryka Północna, Ameryka Południowa, Azja

Wymiar :
Hierarchia produktów : Kategoria> Podkategoria> Produkt z elementami:

• Kategoria: Jedzenie, napoje…
• Kategoria żywności: Baked_food…
• …

aktualizacja : ten przykład jest lepszy:

Cel zapytania: Uzyskaj kwotę sprzedaży i liczbę jednostek (w kolumnach) wszystkich rodzin produktów (w wierszach) sprzedanych w Kalifornii w pierwszym kwartale 2010 r.

MDX

SELECT  {[Measures].[Unit Sales], [Measures].[Store Sales]} ON COLUMNS,
      {[Products].children} ON ROWS
FROM  [Sales]
WHERE ([Time].[2010].[Q1], [Customers].[USA].[CA])

SQL

SELECT SUM(unit_sales) unit_sales_sum, SUM(store_sales) store_sales_sum
FROM sales
  LEFT JOIN products ON sales.product_id = products.id
  LEFT JOIN product_classes ON products.product_class_id = product_classes.id
  LEFT JOIN time ON sales.time_id = time.id
  LEFT JOIN customers ON sales.customer_id = customers.id
WHERE time.the_year = 2010 AND time.quarter = 'Q1'
  AND customers.country = 'USA' AND customers.state_province = 'CA'
GROUP BY product_classes.product_family
ORDER BY product_classes.product_family

źródło: Uwagi dotyczące użytkowania Modriana (które tłumaczy zapytania MDX do stosowania w relacyjnych bazach danych)

Znalazłem dobry przykład, chociaż SQL nie jest o wiele bardziej złożony (w porównaniu do SaasBase zamiast MDX):

źródło: „OLAP” w czasie rzeczywistym dla Big Data (+ przypadki użycia) - bigdata.ro 2013