Skip to content
EC Data MIning Handbok
Lektions-PDFs
Schema
Hemuppgifter
Microsoft
Data
Algoritmer
Azure Data studio
Visual Studio
Sammankopplande
Resurser
Hemside-input
Queries
Inlämningen
More
Share
Explore
MDX Queries

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:
⁠
bild.png
⁠
⁠

According to our analysis requirements, the results should be as follows:
⁠
inventory results
⁠
⁠
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.