TM1 Forum

I have recently built a capex cube to hold forecast capex data. I have set it up so that each new project creates a new element within a capex projects dimension. I am now wanting to improve this cube and allow users to directly enter text regarding projects. Is there a better way in building this cube that avoids creating a new element for each capital project?

Has anyone done this before - any tips would be useful.

I think there are only really a couple of ways to appraoch this;

1 - have a dimension called capex project that contains a finite list of elements (eg Project 1, Project 2, Project 3 etc etc) - You could then (eg) use attributes of the elements to describe their different or actual uses once the forecast takes shape.

2 - have a growing list of projects that are added to as the forecast takes shape as required.

Although option 1 may seem simpler, I've rarely used it in practice as it's quite inflexible.
You mention the need for users to add text - just remember that you'll need to include an element in the last dimension of your cube that is of type string - that way users can add comments data directly to TM1 (though you'll need to carefully design your reports and input forms such that the comments can be easily found / retreived )

We use a variation of Martin's option 1. I included our department dimension in the cube and created a "CapEx_SequenceNumber" dimension with fifty elements. No single department ever has more than fifty CapEx projects in a year (and only one even comes close). I also broke the data into two cubes, one for $s and one for project metadata-- this makes it harder to use the cube viewer, but template-building is much simpler:

CapEx
- Scenario
- Department
- Sequence
- Category (Hardware, Software, Internal Labor, etc)
- Status (Requested, Approved, Committed, Accrued, Actual)
- Period
- Year
- Currency

CapEx_ProjectData
- Scenario
- Department
- Sequence
- Measures (project name, comments, annual OpEx, Bugzilla ticket, etc)

A change I made later was to include a year in the sequence number(e.g. 2010-001). This signifies the project start year and allows projects to span multiple years. This does mean an ever-growing list of sequence numbers, but this cube is so small it's not a performance issue, and it's simple to just add the fifty new base elements and a new consol once a year. Projects are uniquely identified administratively by dept+sequence.

User security is based on the dept dimension. In the "Status" dimension, nearly all users also only have access to the "Requested" element; our central CapEx analyst has write access to the "Approved" element. Committed, Accrued, and Actual come in via ODBC from Oracle.

Matt

Matt - that sounds like a nice solution

Thanks for this Matt. I think I will include the currency translation in the same cube under an extra dimension, and will need upto 100 capex project numbers per BU, but after that will build something very simular to what you have below.