Loading invoice details into TM1 cube for allocation analys

[harrytm1]

Hi all,

We just received a new requirement to use TM1 for allocation. Users plan to analyse profitability right down to the invoice level. here are the perceived dimensions and sizes.

Company : 20
Period: 12 months
Year: to store 3 years of monthly data
Invoice: still not decided if this is to be a dim, sounds extravagant. but the sizing is 6000 invoices per customer per month... HUGE
Customer: est. 3000 customers
account: est. 100 GL accounts
Business Type: 5
Pdoruct Type: 25
Cost Centre: 30

as you can see, this cube will be pretty big. I hope to gather some opinions whether there will be peformance issues is this cube is just used to store raw data. We plan to propose a minimum of 64GB RAM, but are not sure if this is even sufficient to allow users to view profitability right down to invoice level.

FYi, we don't even plan to apply the allocation rules real-time on this cube for all years. We will calculate the allocation in another cube for the latest month and then TI the calculated data back into the main cube.

Last question, can you share with me the largest number of elements in ONE dimension that you have worked with? given the sizing above, my invoice dimension will be 6000 * 15 * 12 * 3 which is 3.2 million. Is this even possible, let alone running efficiently?
thanks in advance!

[LoadzaGrunt]

my invoice dimension will be 6000 * 15 * 12 * 3 which is 3.2 million. Is this even possible, let alone running efficiently?

Yes, and that wouldn't be the largest dimension out there either. However, very large dimensions such as these don't 'play well' with functionality like Subset Editor, Cube Viewer and (don't even go there) attributes.

Are users really going to want to analyse at that level of granularity all the time ?

[lotsaram]

Although TM1 will cope with this data volume, true transactional cubes are not really recommended.

A better design for performance (and usability) in TM1 would be to have a "reusable" "invoice line" dimension with 6000 elements (e.g. 0001 to 5999) and a flat hierarchy representing the maximum possible number of invoices per customer per month.

This makes the cube much easier to browse for users and performance issues from very large dimensions are negated. In reports it is easy to cross-reference to the actual invoice number by using a lookup cube (with dimensions customer, year-month, "invoice line" and a string measure holding the actual OLTP invoice number).

There are 2 ways to determine what "invoice line" to allocate each invoice number to.
1/ maintain an ever growing invoice number dimension (not actually used in any cube) and have attributes for year-month and "invoice line"
2/ maintain the logic for which invoice line to allocate a particular invoice number to in the DW or some kind of SQL staging table
(my preference would be option 2, use relation tables for what they're good at and TM1 for what it's good at.)

[LoadzaGrunt]

Or perhaps if you can categorise the invoices so you get different points of aggregation (by customer, by payment terms, etc) in the invoice dimension then you can chop off the detailed level in the cube and allow access to the invoice detail using a relational drill-through.

[harrytm1]

thank you so much! it's really helpful!