TM1 Working with Input Data that is at Different Level

[andy_chien]

Hi All:

My office and I are new the TM1 we are halfway through the design process, I have basic understanding about the structure TM1 uses and the basic capabilities the software offers.

The question I have is with inputting data in a conslidation level. While understanding that it is not possible to input the data directly at the consolidation level for TM1, we do face the tough decision that two other offices that feed us the data are working on different level of disaggregation. For example office A give us data at the building level where as office B give us data at not only the building level but the room level as well.

There is a motion to move toward room based reporting in the future so the design has deemed it necessary to retain information from both the building and the room. The only difficulty is to allow the input of data at the consolidated building level.

The best possible solution we have so far is to use a data entry cube at the consolidated building level so they can be ruled into the consolidation later. Unfortunately, we have several situations that are similar to this plus we can already forsee the nightmare of manually maintaining 2 sets of dimensions (one with building as elements, and the other with building as consolidation of rooms) for each of this situation encountered.

Could people see a better alternative to this and point me to a direction? Thank you very much in advance.


Cheers,

Andy

[rkaif]

You can use Data Spreads to enter data in the consolidation level.

If you are using TM1 9.5 then you can also use TM1 Contributor to enter data at the consolidation level.

[andy_chien]

Hi rkaif:

Thank you for the response.

I am aware that you can input the data one by one by selecting to spread the total, this maybe ok for inputting cell by cell but we have many cells to enter and it would be nice to have the data we read in from the database or pasted from excel (using the copy and paste method between Excel and TM1 view) to automatically spread down porportionally without manual intervention. Right now it does not matter what spread method is used but more getting the consolidated total to be that number we read/pasted.

Hope this helps clearifies it a bit.

Thank you for your quick response rkaif.


Andy

[Steve Rowe]

Hi Andy,
Not sure spreading is going to help you too much since you'll need something to spread against, since all your data for a given office is at the building level you won't necessarily have data at the room level to drive the spreading.

What you can do is just nominate a single room that is a child of a particular building to store the data. Say Building A is a consolidation of rooms 1 to 10, you can nominate room 1 to hold the data.
If you give the Building dimension a string attribute "Data Entry Reference" and populate the attribute for Building A with "Room 1" then when ever you encounter Building A in your data feed you can reference the attribute Data Entry Reference in order to give the correct N level entry point.

In a TI Process you would use Attrs ('BuildingDimName', BuidlingVar, 'Data Entry Reference') in an Excel worksheet you can use DBRA("server:BuildingDimName", RefToBuidlingElement, "DataEntryReference") combined with a DBS formula.

If you populate the attribute with the element name for the N levels of the dimension then you should be able to standardise your TI or DBS sheet so that you can always reference the attribute irrespective of the source being at the room or building level. (i.e make the DataEntryReference attribute of Room 10 = "Room 10").

I hope all this makes sense!

I'm not aware of anyway this will help you to copy and paste values directly from Excel into the cube viewer, I'd encourage the use of a DBS(W) formula in this area anyway as this is a reusable auditable piece of functionality where as a copy and paste action relies very much on your users understanding what they are doing and not making any mistakes.

Cheers,

Steve

[rkaif]

Steve's approach could be a way to do it.

I think if you create DUMMY 0-level elements then you can load the quirky data in those Dummy elements. While the clean data could go to their respective elements.

Hope it helps

[jydell]

Hi

A Excel option for creating a DBS upload template. This will enable the users to have excel data ready at either level 1 or level 0 elements, and excel always uploads at the N element level or the N element directly below the C element.

1: Use excel function dtype to determine if the element they are entering into is a C or N element. This helps identify if the users are entering at C or N elements
DTYPE(dimension, element) will return "C" or "N"
alternatively use Elev function to determine if its a level 1 or 0. To identify problem that they may try to enter data at say level 5.
ELLEV(dimension, element)

2: If its a C element use excel function Elcomp to return the first child under the element.
ELCOMP(dimension, element, index) index 1 will return the first n element under the C element

3: Point DBS formula to upload either the to the N elment direct or to the derived N element in step 2 if their original data is at C element level

Note this solution may fill your gap until they all enter at N level. Potential problems include
1: The above assumes that all C elements they wish to enter at are level 1. (ie N elements only below them)
2: It will always force data to the first n element below the c element. This may not be what you are after.
3: Manual error from your users may result in them enterring data at both C and N element level. producing errors.

[Gregor Koch]

Hi

Tthe first thing I have seen reading parts of the answers was DBS!
So without further discussing the above...if you go down that path of using a DBS, use DBSW as much as possible rather than DBS!!!

Cheers

[andy_chien]

Hi All:

Thank you Steve Rowe, rkaif, jyudell, and George Koch for your responses.

I think that 0-level catch all element is a great idea. And I think the DBS or DBSW logistic you have provided sounds like a great way to fill these numbers in very quickly by-passing the dimensional structure of TM1. I will get working on testing it and getting it to work. Thank you all for the advices and suggestions guys.

Being new to TM1 for both my office and I, I have to say this warm experience on this forum community certainly has given us more confidence about choosing it. Thanks guys!


Cheers,

Andy