Planning Analytics

Recent posts on feeding and going feederless have sparked some interest. Today's article follows on from my series looking at MDX and calculated members to analyse what is being fed using the }StatsByCube performance cubes.

We will use Planning Analytics Workspace to render an MDX view on the }StatsByCube cube but you should be able to use Apliqo UX or Arc too.

You will need to be an admin or data admin to access the }StatsByCube cube on your model. As always, try this in a development environment, not in production.

Create a new workbench or book to allow us to navigate to the database and cubes.

Ensure that you have a }StatsByCube cube by navigating to your database then expand Control Objects and search for }StatsByCube:

If you for some reason do not have }StatsByCube, you may need to start Performance Monitor to create this cube.

Assuming you have the cube, use the kebab menu to show the options then select "Add new view" to add the default via to your workbench or book.

My default view show something like the below but this is not important as we will overwrite with our MDX:

Click on the MDX icon on the cube viewer widget toolbar to open and show the current MDX used to generate the view. Select all the MDX code and delete it leaving the window empty.

Paste in the below code for our analysis:

WITH 
MEMBER [}PerfCubes].[}PerfCubes].[Cupcake] as "Raisin and mint chocolate chip cupcakes for all!", SOLVE_ORDER=1
MEMBER [}PerfCubes].[}PerfCubes].[Result] as "Feeding Analysis"
MEMBER [}StatsStatsByCube].[}StatsStatsByCube].[Flour]      as Right(Left(MemberToString([}PerfCubes].[}PerfCubes].[Cupcake]),8) ,1)
MEMBER [}StatsStatsByCube].[}StatsStatsByCube].[Oil]        as Right(Left(MemberToString([}PerfCubes].[}PerfCubes].[Cupcake]),30),1)
MEMBER [}StatsStatsByCube].[}StatsStatsByCube].[Water]      as Right(Left(MemberToString([}PerfCubes].[}PerfCubes].[Cupcake]),1) ,1)
MEMBER [}StatsStatsByCube].[}StatsStatsByCube].[Raisins]    as Right(Left(MemberToString([}PerfCubes].[}PerfCubes].[Cupcake]),3) ,1)
MEMBER [}StatsStatsByCube].[}StatsStatsByCube].[Choc Chips] as Right(Left(MemberToString([}PerfCubes].[}PerfCubes].[Cupcake]),46),1)
MEMBER [}StatsStatsByCube].[}StatsStatsByCube].[Salt]       as Right(Left(MemberToString([}PerfCubes].[}PerfCubes].[Cupcake]),7) ,1)
MEMBER [}StatsStatsByCube].[}StatsStatsByCube].[Sugar]      as Right(Left(MemberToString([}PerfCubes].[}PerfCubes].[Cupcake]),41),1)
MEMBER [}StatsStatsByCube].[}StatsStatsByCube].[Sprinkles]  as Right(Left(MemberToString([}PerfCubes].[}PerfCubes].[Cupcake]),19),1)
MEMBER [}StatsStatsByCube].[}StatsStatsByCube].[Icing]      as Right(Left(MemberToString([}PerfCubes].[}PerfCubes].[Cupcake]),21),1)
MEMBER [}StatsStatsByCube].[}StatsStatsByCube].[Vanilla]    as Right(Left(MemberToString([}PerfCubes].[}PerfCubes].[Cupcake]),22),1)
MEMBER [}StatsStatsByCube].[}StatsStatsByCube].[Mint]       as Right(Left(MemberToString([}PerfCubes].[}PerfCubes].[Cupcake]),48),1)
SELECT {
 [}StatsStatsByCube].[}StatsStatsByCube].[Flour],
 [}StatsStatsByCube].[}StatsStatsByCube].[Oil],
 [}StatsStatsByCube].[}StatsStatsByCube].[Water],
 [}StatsStatsByCube].[}StatsStatsByCube].[Raisins],
 [}StatsStatsByCube].[}StatsStatsByCube].[Choc Chips],
 [}StatsStatsByCube].[}StatsStatsByCube].[Salt],
 [}StatsStatsByCube].[}StatsStatsByCube].[Sugar],
 [}StatsStatsByCube].[}StatsStatsByCube].[Sprinkles],
 [}StatsStatsByCube].[}StatsStatsByCube].[Icing],
 [}StatsStatsByCube].[}StatsStatsByCube].[Vanilla],
 [}StatsStatsByCube].[}StatsStatsByCube].[Mint]
 } ON 1, 
{[}PerfCubes].[}PerfCubes].[Result]} ON 0 FROM [}StatsByCube] 
WHERE ([}TimeIntervals].[}TimeIntervals].[LATEST])

Once pasted, click on OK to accept the code and generate the new view:

Once rendered, you should now have an updated view showing you the following analysis:

From the above view you can clearly see the components of what you are about to feed on.

So what have we learned from the above MDX?

• You can use MDX to generate calculated members on both the columns and rows.
• These members can be completely unrelated to the data or cube and could be used to insert spacing, additional text like guidance or possibly validation feedback.
• You can return member names and convert these to string to use in text manipulations
• MDX functions like Left() and Right() allow you to chop text which may be useful when you want to show an account without a description or possibly the description without an account and chop the first n characters from the string.

Hope you had some fun with this!

2024-04-05 UPDATE:

For those that missed it, this was an April Fool's prank and the file not found screenshot was intentional to avoid giving away the result per the below image:

If you have not tried the code yourself, you can still do so.

Also, in V12, the }Stats cubes are deprecated but the concept above would apply to any cube. I chose to use the }StatsByCube to make the link to Feeding somewhat more believable.

Please see my other related articles for additional insights.

Learning MDX view in Planning Analytics-Part 1

Part 2 - Using Calculated Members in MDX

Part 3 - Aggregate Calculated Members in MDX

#IBMChampion