TM1 - Viable Solution Approach?

[mixim]

Hello, prepare for long post : )

I recently went for an introductury course, and got to know basic capabilities of TM1. Now I am about to start some self-paced courses etc.. But this week I got a task to start working on a TM1 Proof of Concept. It was identified that TM1 is wished to be used for storing property information. Such as areas, number of parking spaces etc, and some other (what I call attributes) values..The data is gathered via emailed excel sheets from each store (and finally consolidated to one big) with a structure logic example below:

shopArea (m2)
entranceArea (m2)
restaurantAreaTot (m2)
- restaurantSubArea1 (m2)
- restaurantSubArea2 (m2)
franschiseEnabled (boolean yes/no)
parkingModes(string)

This data would like to be used for two purposes:

• Financial Followup (Main purpose) Thought here is to later also load in some financial data from Controller and get some return of investment and KPI-figures based on square meters for analysis and reporting.
• Property Register (Secondary Purpose) - To actually use TM1 as a register and get rid of the spreadsheets. This means all input directly from each store through e.g. Cognos Insight or other interface is wished.

(My POC would relate to this)

While for the Financial follow up, I already see that in such cases it would be necessary to create some more complicated aggregations (exluding some areas) and also align with the structure from Controller. For the secondary purpose i think it could be a pretty straight forward structure. There is also a wish that all totals of areas should be calculated in the tool itself and not by hand before input.

Questions:
A. Is this something that is viable to use TM1 for?
B.For me , the gut feeling says I will have to create two separate cubes for each purpose. What, do you guys think?
- b1. If yes, are there possibilities of say, automatically loading data from the "Property register" cube, into the "Financial cube" as soon as a user does an input in the first?
- b2. If yes, any ideas of which cube might be better to start of with? (im guessing the Register)
- b3. If yes, how flexible is the data loading between cubes. Do you use Turbo Integrator, can you transform from any level from source to any level in target etc?
- b4. If no, what could be another approach?

c. (Question related to POC). When it comes to the creation of a bit more complex total. E.g calculating totals, and excluding some subtotals for another definition. I would probably be necessitated to using rules right, just using hierarchies with some weights would not be sufficient?

Some other general thoughts or comments on this? If someone has time and feels its interesting to disect , please do. I think I would appreciate any kind of discussion/conversation with someone with more experience as I'm alone in this boat! =). While I will only work on a small portion of this for the POC (simplified version of secondary target), I was wondering about if TM1 is viable at all, and general approaches for situations like above !

[tomok]

You're not going to like this answer, or perhaps your company won't, but what you should really do is find an experienced TM1 consultant to help you with this project. Note I didn't say hire a consultant to build it for you but get one to help you build it. You'll get two benefits out of this 1) your model will actually be pretty good (assuming the person you hire is competent) and 2) you'll get hands on training on a live model. Whatever you spend on a consultant will be more than made up by savings on wasted development efforts and money you might spend on extended training.

[lotsaram]

Opinion seconded. Mentoring works very well with TM1. Rules in particular can be tricky to get your head around, in fact many "TM1 consultants" don't have a firm grasp of rules. The trick is to find someone competent at TM1, with a good understanding of the business problem, and a decent bedside manner.

[mixim]

You're not going to like this answer...

Opinion seconded...

Got it guys, and I had the same feeling from the get go for TM1. Ive noticed that Cubes are inherently sensitive to "small" choices in the beginning. I did actually start this with a mentor. But he left the company a week ago (sigh).

Keep in mind: Maybe I wasn't clear about this, but my purpose is only to produce a small internal POC for demonstrating some basic capabilities that would be possible with TM1. The questions i asked in the thread are mostly on a philosophical level (mostly curious for potential future). For myself I have around two weeks of time already ear-tagged to me. SO I'm not going in to this to build the entire solution.

I've had small successes, I have been able to import basic data into dimensions via TI and play around with it in different views.
So for this POC I wanted to at least for a simple POC registry which retains data about the stores (Related to the secondary goal).

My aim for the two weeks is:
- Create a working process for importing data from an excel. 200 rows, variables.
- Load this data in to some hierarchy not very complicated, something like Country->Store->Areas->Subareas
- Be able to display how the data can be browsed in e.g. Business Insight, and that users can commit to it.

Hoping to get this at least working.

Thanks for your replies, still wondering about questions on a "discussion level" =)

[Alan Kirk]

I don't really have time to take a look at this properly at the moment (will try to do so when I get a bit more time) but there are arguments both for and against separate cubes in your case which you might want to mull over for a bit and decide how you'd rather approach it.

The biggest issue as I see it is the time one. Your financials data is going to be time dependent; you will receive revenue and incur costs over different time periods, which your users would doubtless want to be able to report by period (week, month, quarter, year, etc).

You're not going to want to put your space measures into each time period (to calculate yield per square metre, say), however, because they will not frequently change. Not frequently being the key qualification there. Tenants will doubtless come and go and remodelling will occur periodically, which is likely to shift the space allocations between the various categories that you listed and others. Consequently you WILL need to have some facility to store multiple versions of your property register over time, though whether it's at the same time granularity as your financial data is another matter. If it's not, then it may be possible to map financial periods to registry periods via attributes, but that may be a little high maintenance.

I know that you said in the other post that you didn't want to think about how the data would be analysed, but I'm afraid that even with a POC (with TM1 or any other tool) that's exactly what you need to do. The way to approach this is:
(a) Find out the exact level of detail that the business users will need;
(b) Determine what data already exists and what data gaps will need to be filled;
(c) Then set about designing your cubes by working out what dimensions you think you'll need to deliver the requirements that were identified in (a). Remember that each dimension will be some kind of attribute of the value stored; the profit centre, the version, the year, etc. The key question here is "what attributes do I need?" Then the next question that follows on from that is "what measures (elements) do I need in my measures dimension?";
(d) Then knock up a couple of draft versions, maybe with a bit of dummied up data;
(e) Get some feedback from the users (on scope only, since you won't have put together all of the rules, etc at this point);
(f) Then start optimising based on the feedback and start building some actual cubes and pumping the real, live data into them.

[Alan Kirk]

c. (Question related to POC). When it comes to the creation of a bit more complex total. E.g calculating totals, and excluding some subtotals for another definition. I would probably be necessitated to using rules right, just using hierarchies with some weights would not be sufficient?

Neither of the above. You can create multiple hierarchies so you can have some consolidations which include particular sub-totals but not others, others which have everything, and so on. In some cases you can exclude some items from a total using a weighting of -1 but generally it's better to structure according to what you need to report.

[mixim]

The biggest issue as I see it is the time one. Your financials data is going to be time dependent; you will receive revenue and incur costs over different time periods, which your users would doubtless want to be able to report by period (week, month, quarter, year, etc).

Actually, It will be adjusted so that we only save the register aligned to the financial report period, meaning that the date/calendar dimension will be setup the same way. These reports at the moment are on a Yearly level and that is the requirement. I then understand it that the users will be able to put in values during the year, but its not until the end of the financial year that it will be calculated against. At that point, it will be the "latest" value. I recognised that lower level of analysis will not be possible e.g. compare against minute changes of size during the year, but controllers were fine with that because today they send out these excels once a year.

I know that you said in the other post that you didn't want to think about how the data would be analysed, but I'm afraid that even with a POC (with TM1 or any other tool) that's exactly what you need to do. The way to approach this is: a,b,c,d,e,f.

Well, for the property register, the data will truly not be "analysed", at this point only used as a storage for values (and yes, it is questionable if TM1 is the right tool here, but that is another discussion). That is also why I was pondering about keeping two cubes. To have one as a "storage" or "input" cube. Then another one, loading the data in a different way into other hierarchies in order to satisfy the financial reporting.

But please give me feedback if this does not seem to make sense at all.

Neither of the above. You can create multiple hierarchies so you can have some consolidations which include particular sub-totals but not others, others which have everything, and so on. In some cases you can exclude some items from a total using a weighting of -1 but generally it's better to structure according to what you need to report.

Thanks, i will investigate creation of severe hierarchies instead. But how flexible is the hierarchies, is it possible to manipulate aggregations using values from different levels?

Once again, thanks for all input you guys (especially Allan) are giving me. Its not an optimal situation, and I would actually need more time just playing around. But since that possibility is limited right now i appreciate it!

[java_to_tm1]

A. TM1 is a viable choice: It is not the PERFECT solution for storing property info: but it's definitely not the worst either.
B. Identifying what goes into dim, what goes into attr, and what becomes a measure will be crucial. As Alan rightly points out, you will need to think about the reports the user wants to see before you finalize your data model.
C. Consider how the dimensions may change. You might end up putting into 1 hierarchical dim, elements that should ideally have gone into two different dims. Slowly Changing Dimensions are not easily handled in most OLAP tools. TM1 is no exception.
D. Consider working backwards from the reports rather than forward from the data. PoCs generally tend to work better when you convince the end users that you understand their goals than if you convince them that you understand the source data.

The reason I stress on getting the data model right is the same reason why lotsaram recommends professional help in building the model.
Rules (Feeders actually) can be notoriously tricky even if your data model is perfect. Rules that need to be written on inefficiently designed cubes can be way more painful to get right.

[mixim]

Reply...

Java_to_tm1, thanks for your input. I liked how you put that with making them show that you understand the goals rather than data.
At this point, I actually have an excel which is the current excel report. So I have a pretty good knowledge of the level of detail is needed.

Currently it is at each one of these levels.
Country -> Store -> Subareas (m2)
Country -> Store -> Number of parking Spaces (number)

For each store there are around 50 different size variables. These variables are then used in order to define the Subarea totals.

What is needed then is for the input to be possible at the lowest level (Modifying each of the 50 variables per store) but that reporting is done on the totals.

Regarding time dimension, they only want a yearly value for reporting. So tied to financial year.