Thursday, April 11, 2013

Transactional Reporting in TM1 – Some Guidelines

Can TM1 report transactional data?
Yes but further questions need to be asked on business process, volumes and dimensionality

What Volume limitations are there?
You will always be limited by the amount of physical RAM within the box but a key consideration will always be the dimensionality required. Does a table field become a measure or a dimension will need to be carefully thought through. Does that field become a prompt in a report or a filter? Does it have multi-level hierarchies on it?

How can I reduce Volume?
Think about only storing a certain time period of transactional data e.g. a rolling 12 months or maybe just the current financial year.
Does the transactional data need to be physically stored in TM1. Can you instead do drill through to transactions from balance. Always question the business process and whether they can do exception inquiries vs data dumps
Transactional cubes should hopefully not contain any rules.

What Dimensionality limitations are there?
You can build a 256 dimension cube in TM1 but why would you if you can never find the data you want.
Cubes within TM1 should always be built to a business purpose. Mega cubes are never an answer. Think about the prompts and filters that users require.
If the cubes requires multiple date dimensions (date created, date sold, date invoiced etc) then your sparsity in the cube will naturally increase. In memory cubes will struggle to compete with ROLAP cubes in this regard.

How can I reduce dimensionality?
Can a dimension become a measure.
Can you merge 2 or 3 dimension into 1 by concatenation e.g. CostCentre-Project-Employee

What is the appropriate tool to display the results in?
TM1 Active Forms – Easiest and most flexible since you can report from multiple cubes, pass MDX queries etc. Issue is acceptance in BI deployment
Report Studio – Possible but long time to get right. Need to remember cross tabs don’t do strings only lists. Querying multiple cubes will require prompt macros
Analysis Studio – Will struggle with  multiple dimensions on rows and wont display strings
Don’t forget strings will only appear typically at the lowest level of the cube i.e. all N levels