Variance Analysis Cube (including commentary)

[LanceTylor]

Hello All,

I wanted to get you opinions on how best to incorporate commentary for variance analysis in a cube.

I am thinking of two different approaches (1) String variables included in a measures dimension alongside numeric values (2) separate cube which is identical to the one containing values but contain commentary (string variables). I know I can report this Perspectives & Tm1 web but I was hoping to use BI reporting (Reporter in Cognos Express 9.5). i haven't been in the game for 1.5years (Tm1 9.4 days) and I know things have evolved.

My questions are:
1. Does anyone have a preference between approach 1 or 2? and why?
2. Has anyone captured numeric values (monthly variances) alongside string variables (commentary) in a BI Report? I know crosstab reports don't like text but maybe structuring it with a crosstab (numbers) & list (commentary)????
3. does anyone have a better approach? or standard they follow?

As always, thanks for any suggestions

Regards
Lance
TM1-green belt

[lotsaram]

(In a pre-Contributor world) I have always preferred comments in a separate cube that has the same dimensionality as the modelling cube plus one additional dimension with one string element "Comment" in the dimension. This allows the main cube to be properly optimized for dimension order and you can easily have a generic drill-through process to drill through to comments and in an Excel interface you can likewise have generic VBA code to fetch comments from the comment cube and write them to Excel cell comments and to go the other way as well.

Of course it all depends on the requirements and the interface. For a websheet I would stick with the separate cube approach but pull in comments in a separate column or columns. For cube viewer or contributor I don't think there's much option to including string elements in your measure dimension.

[Andy Key]

Last time I tested this BI (I think 10.1.1, and connecting to TM1 9.5.2) ignored string measures completely. It's not that it didn't display the data, it wouldn't even recognise that the measures existed.

[LanceTylor]

Thanks for the feedback. Thanks for the approach lotsaram.

[mgibson]

Hi Lance,

Which approach did you decide on and adopt? I am considering the best approach for a similar scenario, using TM1 to replace a current custom, web-based solution for capturing month-end variance comments.

Regards
Mick

[LanceTylor]

HI Mick,

I am capturing commentary within data entry cubes (where users enter their budget) in the measures dimension. Ex: Payroll Cube containing staff Name, salary, benefits etc.... For this Payroll Cube in particular I am using List Reports in BI which meets the clients expectations and is able to display Employee Salary by Dept.

As for the P&L Summary cubes including variances I pushed that part of the project out a couple months (when we tackle the Actuals and forecast piece).

According to IBM, Cognos Express 10 should be able to handle text in crosstabs (I still need to test this to confirm but my sales guy says it was an enhancement). If this is the the case then I will definitely include commentary (as a measure) in the P&L Cube.

If this isnt the case I might try the other approach? I still have a bit of time to figure this out.

I would be interested in the approach you take.
All the best Lance

P.S. I believe Cognos Express 10 was released today???