Use Analysis service SSAS to solve the ratio, year-on-year and chain issues _BI

This article will introduce the use of Analysis Services SSAS how to solve the ratio, year-on-year and chain three indicators.

First, understand the basic concepts of these three indicators:

Jambi refers to the ratio of the current member's value to that of the member's parent, meaning the proportion of the total, such as a fruit store total sales of 14, of which Apple's sales are 2, accounting for 14.3%.

Year-on-year is the value in a period compared to the value of a previous period, expressed as a percentage of the previous period value, such as comparing this year's data with last year's data or comparing this August's data with last August's data.

The chain refers to the ratio of the current member's value to the previous member's value, such as comparing the second half of this year's data with the first-half data or comparing this year's August data with the July data.

The ratio is generally not used in the time dimension, and Jambi and year-on-year are used in the time dimension.

So how to solve these three metrics in the analysis service? In general, each dimension in a cube has a hierarchy, such as a hierarchy of time dimensions, year-half-year-quarter-month-day or year-week-day, where the year-on-year and the chain are compared in this hierarchy. The analysis service utilizes calculated members to easily resolve the above three metrics, and the analysis service provides the appropriate templates.

First, accounted for.

The MDX statement is defined as follows:

CREATE member Currentcube. [Measures]. [Ratio of sales quantity to parent sales unit]
As 
case
//Test to avoid division by zero.
When IsEmpty
     ( 
        [measures].[ Sell Num]
     ) 
Then Null
//Test for current coordinate being on the (all).
When [Dim unit]. [Province-City-district-unit]. Currentmember.level is
     [Dim unit].[ Province-City-district-unit]. [(All)]
Then 1
Else ([Dim unit].[ Province-City-district-unit]. CurrentMember,
       [measures].[ Sell Num]) 
     /
     ([Dim unit].[ Province-City-district-unit]. Currentmember.parent,
       [measures].[ Sell Num]) end
,
format_string = "Percent";

Second, Year-on-year.

The MDX statement is defined as follows:

CREATE member Currentcube. [Measures].
[Growth rate during sales volume]
As Case//Test-for-coordinate being on (all). When [Dim Date]. [year-half-year-quarter-month-day]. Currentmember.level is [Dim Date]. [year-half-year-quarter-month-day].
[(All)]
Then "NA"//Test to avoid division by zero. When IsEmpty ([measures].[ Sell num],parallelperiod ([Dim date].[ Year-half-year-quarter-month-day]. [Yearly SK], 1, [Dim Date]. [year-half-year-quarter-month-day]. CurrentMember)) Then Null Else (([Dim date].[ Year-half-year-quarter-month-day]. Currentmember,[measures]. 
	   [Sell Num]) -([Measures]. [Sell num],parallelperiod ([Dim date].[ Year-half-year-quarter-month-day]. [Yearly SK], 1, [Dim Date]. [year-half-year-quarter-month-day]. CurrentMember))/([measures].[ Sell num],parallelperiod ([Dim date].[ Year-half-year-quarter-month-day]. [Yearly SK], 1, [Dim Date]. [year-half-year-quarter-month-day]. CurrentMember))) End//This expression evaluates the difference between the value of the numeric//expression in the P
Revious period and that's the current period, as a//percentage of the previous period ' s value. format_string = "perCent "; 

Third, the chain.

The MDX statement is defined as follows:

CREATE member Currentcube. [Measures]. [Number of sales chain]
As 
case
//Test-for-coordinate being on (all).
When [Dim Date]. [year-half-year-quarter-month-day]. Currentmember.level is
     [Dim date].[ Year-half-year-quarter-month-day]. [(All)]
Then "NA"
//Test to avoid division by zero.
When IsEmpty (
   [measures].[ Sell Num],
   [Dim date].[ Year-half-year-quarter-month-day]. Currentmember.prevmember
    )
) 
Then Null
Else (( 
       [Dim date].[ Year-half-year-quarter-month-day]. Currentmember,[measures]. [Sell Num]) 
    -(
  [Measures]. [Sell Num],
   [Dim date].[ Year-half-year-quarter-month-day]. Currentmember.prevmember)
   )
  /
     (
   [measures].[ Sell Num],
   [Dim date].[ Year-half-year-quarter-month-day]. Currentmember.prevmember)) End
,
format_string = "Percent";