Träning

MDX Implementation of Complex Analysis Scenarios

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:

[Measures].[Beginning Inventory] = ([Time].[Month].currentMember.firstChild, [Measures].[Inventory]) [Measures].[End-of-period inventory] = ([Time].[Month].currentMember.lastChild, [Measures].[Inventory]) 

The MDX query expression is: 

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 (

CtrlP

) instead.