Question on TM1 how to Achive this

[AshokReddy]

In source file they have two time stamped references & they want to connect both with Time Dimension.

Let me give you some examples of why we have this business requirement:

Case 1:
In our SAP-bookings we consider two time stamps: date of transaction and date of the booking itself. It can happen that something has been sold in June, but booked in July. So some reports would show the selling by month – in this case this transaction will be reported for June. Other reports consider the turnover by month - in this case we cannot report this transaction in June, because by that time it was not yet in the system and will show the booking for July, even if the report is recreated in August…
Case 2:
We have a complicated chart of cost accounts. Any costs of the IT provider firstly get booked against certain cost accounts and then the costs get transferred / recharged against other internal cost accounts. So most SAP booking contain two accounts (of the same chart of cost account): the account where the costs get transferred from and an account, where the costs get transferred to. A kind of AB relationship…

In both cases above we have a single record of a fact table with multiple references to the same dimension (in different roles). Theoretically we can clone the time or chart-of-accounts dimensions for every role. But then, take for example a chart of accounts, we would need to maintain the same chart of accounts in two dimensions simultaneously. This will not only increase the complexity and the risk of inconsistency, but also make it difficult for the client to navigate the TM1 data, since he will then see multiple clones of the same chart of account and get confused….

So we are looking for a solution to create multiple references from a measure dimension to master data dimensions without being forced to clone the dimensions…. In other words we would need an n:m relationship between the measure and time master data dimension…. Is there a way to set up a kind of alias for the names elements of the master data dimension? Something like timestampA and timestampB would be aliases for the timestampID element of the time dimension. Can you think otherwise of any other “bridge solution”? I guess our constellation is not really unique and there shall be standard solutions for it around….

Basically we have two different timestamps in a source file, which is a basis for our measure dimension. Hence we need two connections between the measure dimension and the time dimension.

If we had only one timestamp, I had named the timestamp variable timeID and connected it with the timeID element of the time dimension. Since we have two timestamps I cannot just name both timeID. On the other hand, if I call the variables timestampA and timestampB I am not able to connect them to the time dimension…

Regards
Ashok

[tomok]

Why not just introduce a new dimension to the model, call it whatever you want, and it has two elements in it:

Trans_Date
Booking_Date

Wouldn't this allow you to capture data in the cube based on these two scenarios????

[lotsaram]

What you are talking about is 2 relatively common requirements - although I wouldn't normally expect to seem them both in the same cube. Usually the sale date vs. revenue recognition date is restricted to a sales analysis type cube that then by its nature only has a much smaller section of relevant accounts and profit centers then you would get in a full GL cube. Tomok has given one good and workable approach for that, a 2nd simple dimension to "toggle" between sale data and booking date. Note this approach means you are duplicating data as you need to load the same key figure to 2 places in the cube. I have also seen the other approach with 2 (or sometimes more) time dimensions. Apart from only needing to load data once this approach is more flexible and allows for more useful analysis. You would probably be surprised that users will probably cope fine with 2 time dimensions. But the best way to know is to do a quick PoC and demonstrate the different approaches.

In terms of the scenario you are talking about with cost centers it sounds like you are talking about pre-allocation vs. post allocation which would be more commonly handled using some kind o version dimension and single cost center and chart of account dimensions.