Can anybody show me a good example of MDX's advantages over regular SQL when doing analytical queries? I would like to compare an MDX query with an SQL query that gives similar results.
While it is possible to translate some of these into traditional SQL, it would frequently require the synthesis of clumsy SQL expressions even for very simple MDX expressions.
But there is neither a citation nor example. I am fully aware the underlying data must be organized differently, and OLAP will require more processing and storage per insert. (My proposal is to move from an Oracle RDBMS to Apache Kylin + Hadoop)
Context: I am trying to convince my company that we should be querying an OLAP database instead of an OLTP database. Most SIEM queries make heavy use of group-by, sort, and aggregation. Besides the performance boost, I think OLAP (MDX) queries would be more concise and easier to read/write than the equivalent OLTP SQL. A concrete example would drive the point home, but I am not an expert at SQL, much less MDX...
If it helps, here is a sample SIEM-related SQL query for firewall events that happened in the past week:
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