Need idea on how to measure timestamp duration

[tm1beginner]

Hi all, i'm new on the forum, and on tm1 world, so please be patience with my question.
My question is , how i can have a measure that rappresent the amount of time that a human resource as spent on a project?
On my relational database, the fact table rappresent row by row (day level granularity), how many minutes the resource has spent, for example
id date Name ProjectCode Department HOUROFWORK ExtraTime
1 01/01/20010 Paul Smith MHC1REVAMPING MHC1TEAM 480 240

This will mean that Paul Smith has work 8:00 hour on the project MHC1REVAMPING .... so my problem is to transform on TM1 the measure HOUROFWORK in way that i can answer on question such
How many hour for the MHC1REVAMPING
How many hour for the Department and so on.....

Please any idea or methodology is welcome..
Bye
Andrew

[Wim Gielis]

Hello

You can just load HOUROFWORK into your cube (with correct dimensions / level of detail you need).
Minutes can be added, so use a CellGetN() and CellPutN combination to load the data cumulatively.
To calculate hours, you have (at least) 3 options:

- within the data load process, divide the HOUROFWORK by 60 and load it onto a second measure
- write a rule that divides HOUROFWORK by 60, including a feeder from HOUROFWORK to your new measure that holds the calculation
( - more a theoretic possibility rather than a practical solution: add HOUROFWORK as an n-type element to a consolidation, and change the weight to 1/60 = 0.016666)

Wim

[Gregor Koch]

Hi,

In the end I would do the transformation into hours at load time unless you will also be planning and forecasting on a minute level, which is unlikely. If it is a pure reporting cube definitely at load time.

If you are an absolute beginner and also have no experience with other ETL tools you might want to start by creating the dimensions and a cube manually to get a feeling for how the data will look like in TM1.
With the data/information you have given the dimensions for your cube could be

- Version
in case you also want to use this cube to forecast the spent hours in the future, at least create an element called 'Actual'

- Dates
you can create the elements exactly as you see them in your source '01/01/20010' and create and alias that shows it the way you need to see in the report, though some might argue that you should rather create the element like so '01/01/2010' in the first place. Obviously consolidations of dates into months, years etc. are needed in this case.
You could also create several dimensions like Calendar Year, Period, Days to be able to represent all dates but this really depends how and what you want to report and what kind of calculations you need.

- Employee
(or Staff or whatever) here it would be of advantage if you had employee ids (and use aliases for the names) as there could be several Paul Smith and you need to identify which one it is

- Project
use the project codes an also create a 'All Projects' consolidation

- Department
use the department codes an also create a 'All Departments' consolidation

- cubename Measure
in here you can have Hours, Extra Hours and maybe later Minutes, Cost etc.

Obviously this is not a extensive list.
Something you should consider in the future is the order of the dimensions, as the above is just a unsorted list. You'll find quite a few posts here on the order of dimensions.
Once that is done just hack in (spread, copy and paste, DBSW... there a several options) a few numbers and slice and dice.

Ideally though you'd be using Turbo Integrator ('TI') to load the data into this cube.
In your example and at least for a start you might even be ok with using the Wizard and then take it from there.
Without going into all the details of setting up an odbc connection to your source and how to use the Wizard, have a look at the TI guide.

Cheers