TM1 Forum

Hello All,
Here is the design case :

There are 11 dimensions in a cube.
1- Minor dimensions : Class, Age group, Corproate title etc
2- Major dimensions : Region (250,000 elements), Division (40,000 elements)
3- There is period dimension with 5 classification :
----A : MTD : Monthly period from Jan01 to Dec14
----B : YTD : Monthly period from Jan01 to Dec14
----C : QTD : Monthly period from Jan01 to Dec14
----D : Avg_YTD : Monthly period from Jan01 to Dec14
----E : Avg_QTD : Monthly period from Jan01 to Dec14
Hence total elements in Period dimension : 840

Totol records to be loaded for MTD : 14 Million (Coming from fact table)
Total records to be loaded for YTD : 14 million (Coming from fact table)
Total records to be loaded for QTD : 14 Million (Coming from fact table)

Avg_QTD is a calculation based on MTD data:
Examle "
Jan02_Avg_QTD = (Dec01_MTD+Jan02_MTD)/2
Feb02_Avg_QTD = (Dec01_MTD+Jan02_MTD+Mar02_MTD)/3 and so on till Dec02_Avg_YTD and calculation repeats for every year

Avg_YTD is also a similar caluclation based on MTD Data.

Hence in the above cube, we have around 42 Million records to be loaded directly from the fact table and around 28 Millions as calculated values.

Which is the best approach : To load MTD, YTD and QTD and have Average values to be calculated on the fly

OR

Any other approach?

I wanted few opionions before deciding the load strategy for this cube. This is POC stage and client wants to see how TM1 handles this volume of data.

Would really appreciate your advise on the same

Kind Regards
Gbehel

gbehel wrote:Which is the best approach : To load MTD, YTD and QTD and have Average values to be calculated on the fly

MTD, QTD, and YTD should be consolidations. For Avg_*, precalculated is fine if the data is read-only. It will require more disk storage and possibly more RAM usage, but it will probably also be faster. I recommend (especially since you're a paid consultant) building the model both ways and testing which works better for the client. It's pretty trivial to build it both ways-- literally 10 minutes (plus load and rule compile time, of course). BTW, the data volumes are not very big in this model, well within TM1's capabilities depending on rule complexity of course.

BTW:

• If "month" is your least-granular time period, what is "MTD" going to show? Or is it really just "Month"?
• You can forget about using the cube viewer with that many region and division elements. Is that granularity really necessary?
• Your "minor dimensions" look a lot like measures (in one dimension) to me.

Matt

P.S. I was in the mood for a model design, but since you're a paid consultant I would really think this is the type of question best directed to your company's TM1 Architect.

How do you know he is a paid consultant? Matt?

Thanks Matt,

This POC is not paid and since there is no much help available on TM1 where im doing this and im new to TM1, i direct these questions to forum so that I dont mess-up at any step. The response from this group has been great and with every passing day, im getting more and more comfortable with TM1. To answer your questions :

•If "month" is your least-granular time period, what is "MTD" going to show? Or is it really just "Month"?
MTD = sum of Jan01_MTD till Dec14_MTD

•You can forget about using the cube viewer with that many region and division elements. Is that granularity really necessary?
Loadind data is necessary becuase we want to test if TM1 can handle this volume of data. In the latter stages, im going to suggest having a cube on top of it to show the aggregations only, if clients dont dig upto leaf level

•Your "minor dimensions" look a lot like measures (in one dimension) to me.
The minor dimensions have individual elements in them and these are the parameters across which the data needs to be viewed.

and there are no other rules except the calculation of AvgQTD ad AvgYTD.

Its good to know that the volume of data is not that big and i shall work on designing both the approaches to showcase and see what works best.

Thanks once again
Gbehel