Let's use inventory analysis as an example. Inventory analysis is frequently conducted in the manufacturing, retail, and logistics industries.
Among these, the inventory is a semi-additive measurement, meaning it does not have the cumulative property in the time dimension, but has the accumulative property in other dimensions.
Suppose that the inventory record is as reflected in Figure 3 and the total inventory of all products at the beginning (the first day of the month) and the end (the last day of the month) of all products needs to be obtained each month:
According to our analysis requirements, the results should be as follows:
If you use SQL, the query expression is as follows:
SELECT `year`, `month`,
SUM (case when `day of month` = 1 then inventory else 0 end) as "Inventory on first day of the month",
SUM (case when day (last_day(`year` || '-' || `month` || '-' || `day of month`) = `day of month` then inventory else 0 end) as "Inventory on last day of the month"
FROM inventory group by `year`, `month`
If you use MDX, you need to first define the calculation metric (including the basic metric [Measures].[inventory]=SUM(inventory)), as follows:
SELECT {[Measures].[opening inventory], [Measures].[End-of-period inventory]} ON Columns,
[Time].[Month].members ON Rows
FROM [inventory]
As you can see above, MDX is easier to implement than SQL in inventory analysis scenarios. Similar scenarios include the common account balance analysis in the banking industry and the initial and final value analysis common in the securities industry. In addition, MDX can also support multi-analysis scenarios, which are not supported by SQL.
Want to print your doc? This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (