How to unleash the power of dynamic subsets to speed up budgeting process

Check list of all posts

To identify data problem in budgeting process, we can generate summary report to help. However, this approach has two issues:

1. Development team needs to build report via report studio with cube based framework manager
2. User need to look at report, then go to contributor to edit data, it is very cumbersome process

Dynamic subsets are here to rescue, which will overcome two mentioned issues above:

1. We can simple leverage current existing cube, the only thing we need to do is to create a new view for business to use.
2. From business user point of view, they don’t need to go back and forth to identify locations with problems; instead, they can directly input data within view. In addition, data set with exception will be reduced during process.

To demonstrate this idea, a simple cube has been built as below

The highlighted cells have problem, because there are sales, but no cast is associated. Practically, this is a very big data set.

To allow business users to identify this problem, we can build two dynamic sets for this issue.

1. Customers that has sales but no cast at Year level

Build a subset to show only customers that has sales but no cast at Year level

{

    filter (

           {TM1FILTERBYLEVEL( {TM1SubsetBasis()}, 0)}

         , [Cube03].([Month].[Year],[Version].[Sales]) <> 0 and  [Cube03].([Month].[Year],[Version].[Cost]) = 0

    )

}

Then apply subset to cube view. Only customer B has problem. Customer C does have cost at year level, and it won’t show in cube view.

Business user can use this cube view to directly input data if needed. Customer B will be disappeared once cost data is entered.

2. Customers that has sales but no cast at Month level

Build a subset to show only customers that has sales but no cast at Month level

{

    filter (

           {TM1FILTERBYLEVEL( {TM1SubsetBasis()}, 0)}

         ,       ([Cube03].([Month].[Jan],[Version].[Sales]) <> 0 and  [Cube03].([Month].[Jan],[Version].[Cost]) = 0)

           OR ([Cube03].([Month].[Feb],[Version].[Sales]) <> 0 and  [Cube03].([Month].[Feb],[Version].[Cost]) = 0)

           OR ([Cube03].([Month].[Mar],[Version].[Sales]) <> 0 and  [Cube03].([Month].[Mar],[Version].[Cost]) = 0)

           OR ([Cube03].([Month].[Apr],[Version].[Sales]) <> 0 and  [Cube03].([Month].[Apr],[Version].[Cost]) = 0)

           OR ([Cube03].([Month].[May],[Version].[Sales]) <> 0 and  [Cube03].([Month].[May],[Version].[Cost]) = 0)

           OR ([Cube03].([Month].[Jun],[Version].[Sales]) <> 0 and  [Cube03].([Month].[Jun],[Version].[Cost]) = 0)

           OR ([Cube03].([Month].[Jul],[Version].[Sales]) <> 0 and  [Cube03].([Month].[Jul],[Version].[Cost]) = 0)

           OR ([Cube03].([Month].[Aug],[Version].[Sales]) <> 0 and  [Cube03].([Month].[Aug],[Version].[Cost]) = 0)

           OR ([Cube03].([Month].[Sep],[Version].[Sales]) <> 0 and  [Cube03].([Month].[Sep],[Version].[Cost]) = 0)

           OR ([Cube03].([Month].[Oct],[Version].[Sales]) <> 0 and  [Cube03].([Month].[Oct],[Version].[Cost]) = 0)

           OR ([Cube03].([Month].[Nov],[Version].[Sales]) <> 0 and  [Cube03].([Month].[Nov],[Version].[Cost]) = 0)

           OR ([Cube03].([Month].[Dec],[Version].[Sales]) <> 0 and  [Cube03].([Month].[Dec],[Version].[Cost]) = 0)

          )

}

Then apply subset to cube view. both customer B and C have problem.

Note:

1. Subset is working with context filter(s) accordingly, meaning that the MDX is working within scope of context. This behavior can be leveraged to resolve many logic issues
2. However, nested dynamic sets with such MDX looks like not working. Simple nested dynamic sets are working.