TM1 Forum

Hi All,

TM1 Version 10.1.1, issue using 'Pick Element' feature in MS Excel 2010.

We have various dimensions prefixed with 'x.' for example x.account or x.business unit. When I try to use 'Pick Element' in Excel and select any of the x.*** dimensions, subset editor opens but is completely empty. Pressing the 'All' icon does nothing and the subsets drop down is also empty. For reference the dimensions without the x. prefix work as expected.

These x. dimensions are used in various cubes, and work fine in cube viewer. I can open each of them and select from the list in subset editor when accessed via cube viewer. This problem only presents itself when using 'Pick Dimension' or 'Pick Element'.

I manually created an 'x.test' dimension and added a parent with 2 children to the new dimension. I then saved it and tried to access it via 'Pick Element'. Again it was blank.

From here:

http://www-01.ibm.com/support/docview.w ... wg27024713

I can see that 'AllowableChars[] = ".$%_`";'

Has anyone encountered this before, and is there a fix?

Thanks, Tom

No. So the best practice is that don't use those chars in the tm1 object names. Thanks for sharing!

I should add I didn't design this, it was done by an external consultancy.

tomsugden wrote:I should add I didn't design this, it was done by an external consultancy.

Tom,
I know what you mean. We are currently talking to 3 companies who have been left with rubbish TM1 systems designed by so-called consultancies who have no practical experience of TM1 and\or who out source the dev to third parties with even less experience!

Hope things are well and best of luck.

Knowing that, among other objects, dimensions are written to disk using their name and '.dim' appended at the end,
makes me very suspicious to use a dot in the dimension name.

A file called
D:\TM1\Data\my.long.dimension.name.dim
seems to be (at least) a candidate for troubles later on. In theory it will work fine, but somewhere a false split on the . and we're screwed.

An underscore character is a better alternative in my opinion.

Wim

Wim Gielis wrote:Knowing that, among other objects, dimensions are written to disk using their name and '.dim' appended at the end,
makes me very suspicious to use a dot in the dimension name.

A file called
D:\TM1\Data\my.long.dimension.name.dim
seems to be (at least) a candidate for troubles later on. In theory it will work fine, but somewhere a false split on the . and we're screwed.

An underscore character is a better alternative in my opinion.

Both thumbs up. It's one of the things that I've never liked about Bedrock either, come to that. I severely dislike any non-alphanumeric characters (including spaces) in object names but if they must be there, Wim's right; the underscore is generally the safest.

Stephen,

I've also been brought in recently to several opportunities that have poorly developed TM1 systems
I guess the upside is that we can at least rescue the ones that reach out

Unfortunately there seems to be so many bad ones out there

I'm making an assumption here but it seems like all the newbies are using PlanSamp as a starting point which is probably one of the worst models ever built

As for the underscore, I personally never use them and find them of no value
From a user perspective I find them annoying
What user wants to see underscores, especially in element names... Yuck!
If a dimension has two words then I typically shove them together and Capitalize the first letter of each word
For example: DataSource

I realize it's a personal preference but I think that looks so much better than Data_Source and I don't need to worry about the stupid underscore that shouldn't be there in the first place

Given that TM1 has always not been case sensitive or space sensitive there just never seems to be a reason for an underscore

However, for those of you that like them... so be it

Solanna

Solanna

It's personal preference, but the underscore is used to highlight several parts/topics within a single TM1 model:

FIN_CostCenter

could be a dimension for cost centers used in Finance (FIN prefix).

SAL_Customers

could be a dimension of customers for a Sales forecasting application.

Your capitalizing of each word is fine for me as well, but not for prefixes ("area").

Prefixes can also be used for the type of cube / dimension / process:

Rpt_XxxYyyZzzz (for Reporting cubes)
LU_XxxYyyZzzz or Param_XxxYyyZzzz (for Lookup or parameter cubes)
Act_XxxYyyZzzz (for cubes solely containing actuals)

Or suffixes:

LU_ExchangeRates_Msr (would be the measures dimension in the lookup cube for Exchange rates)
PL_Measures (could be the measures dimension in the P&L cube)

and so on, you get the picture

Question: how do you handle these cube names / dimension names / process names? What is your naming convention?

Wim,

I get you but I don't buy into it... never have
Granted, if you are going to repeat dimension names for multiple cubes (which again I typically don't buy into) then I guess your only solution would be to preface it with some abbreviation
I would still not use the underscore and I would probably make it more readable to the user like FinanceMeasures instead of FN_Measures but most likely my Measures dimension would satisfy every cube in my model so I wouldn't need a separate measures dimension in the first place

This concept of LU, PL, RP, etc. is for who's benefit?
Certainly not the users
Accountants and Analysts are not programmers or developers and I don't believe they should have to accept this type of nomanclature
This is actually a subject that has been bugging me for quite some time so this post finally gave me the opportunity to state my opinion

And yes, these days I come across these types of models all the time
Typically the user community has no clue what those abbreviations mean and most of the developers have simply copied the concept
I wish I knew who started this but this is certainly not the methodology 20 years ago

As for my naming convention, I like to keep things as simple and user friendly as possible
Dimensions are never plural except for Measures
Underscores are never used for Cubes, Dimensions, Elements, Attributes or TI processes
I tend to be a fan of the dash myself since I find it separates much better than the underscore

However, I will use zSys when naming subsets and views that are system/TI related since it's the only way to shove them to the bottom of the drop down list where the users will hopefully not click on them
I will also use Sys before specific TI processes that will be included in my Execute Process TI's where I will have the object name and then a description of what the TI process is doing
For example:
GL Cube - Monthly Load Process
TIME Dimension - Update Forecast Months

I've read many of your posts over the past several years and I definitely respect your experience
I just tend to look at TM1 development from the users world and not the developers world

I've been building TM1 solutions since 1994 but in 2005 I went to work at OutlookSoft (SAP BPC)
It was similar enough that the learning curve was quite short and the 4 years I worked with the product gave me tremendous insight on becoming a better TM1 developer since I've been able to incorporate many OutlookSoft methodologies in my TM1 designs

I remember working with a very intelligent gentleman and when he found out I came from a TM1 background he literally started laughing (not at me but at TM1)
"Why do you need so many cubes?"

I personally agreed with him that there shouldn't be so many cubes in a TM1 model so with that said I don't need to put prefixes and underscores before my cubes and dimensions because I don't overthink the model... yeah you can debate the complexity issue but most models don't need to be so complex

If I can hold most of my data in one or two cubes and use dummy elements, I will go down that path instead of mixing and matching to 20 cubes
I remember building a daily project reporting system where it was necessary to have multiple cubes for different functions but each cube was defined so that the users understood their meaning
For example:
Contract, Production, Headcount, Revenue, PoolCost, OtherExpense, Summary, etc.

I'm not expecting you to agree with my philosophy but I've had a lot of success over the years and I'm not aware of another developer having to clean up after me

Anyhow, this post is really just letting me speak my peace on the matter

As they say, you can't change city hall so I'm sure my opinions on this will be mostly ignored but I will continue down my path since so far it's worked for me and all of my users over the years

All the best.

Solanna

Solanna

Opinions differ, that's correct. The discussion of what is best, is probably moot.

Just a few remarks / questions from my side:

- if you have only 1 or a few cubes, it isn't a big(ger) model and these kind of problems / challenges will not need to be tackled. It sets the perspective.
- using the same measures dimension for several cubes, seems a bit problematic. Adding measures later on and using them in some cubes but not in other, seems an issue. The users will expect that they can use them. For example, % or ,000 values will not be necessary/available in every such cube.
- your dash, what character is it on the keyboard? the subtraction character - ? That's not what I would use, it seems like a subtraction itself.
In a TI process, TM1 will not recognize your variables when you use the - character.
Also, in Word documents, it would split up the TM1 object names over several lines. But I agree, an underscore might also not be ideal.
- you can use FinanceMeasures as a name but probably there will be more than 1 cube for finance. At least in the TM1 projects I undertook. BalanceSheet measures could be called "YTD value" while P&L measures would be called "Periodic value". 1 dimension with "Value" is possible but seems only a solution in the short term. What about new elements? What if security is different (read/write on elements)?
- LU_, PL_, Act_, Rpt_: mostly for the developers in any decent-size model, but also users (for example TM1 Web) will need to know to what cube they should navigate. Or they don't need to know what cubes it comes from, in an Excel report for example. Hence, a prefix to make the difference, prefixes that are explained in the user documentation. Or do you use the same cube for storing Actuals and reporting data, for example, when the granularity differs? You can use dummy elements but that is not clear to users (in my opinion and experience). Users should know where the data is stored and meaningful. Since TM1 9.4, Active forms can easily report on data stored in several cubes.
- Performance Modeler could eliminate the need for prefixes to some extent.
- I also like to keep it "KISS" but that's not always easy in models that go beyond the basics.
- If you use a TI process called "GL Cube - Monthly Load Process": does it kick off other processes? How are they called? How do you treat the dimension update processes as opposed to the data load processes?
- Why do we need so many cubes? For example,
* Storing different data in the same cube would make people click and click and "search" for their intersections
* What if one of the underlying cubes needs an extra dimension later on, after the model was set up? No TM1 model remains static as to the dimensions or cubes. Your cube architecture will benefit much more from more, but smaller cubes. You limit the number of the intersections to the ones that make sense.
- For cubenames like: Contract, Production, Headcount, Revenue, PoolCost, OtherExpense, Summary: how do you name lookup cubes? Parameter cubes? Cubes with archived data? Actuaks as opposed to budget or forecast? ...

Just my 2ç.

Wim

With regards to the dash symbol (I am also assuming you mean the subtraction symbol)...

I picked up a project a while ago that someone else had started, I won't say anything bad of the work done as it was exactly what was on a scope document but one thing I did find problematic was a dimension name that was "customer-product"... It functions perfectly well in TI etc but I did find it quite annoying in the Advanced Rules Editor (admittedly now put out to pasture) and to some extent Notepad 2.0 as they tried doing odd things (well sort of completely expected things) with the minus character so int he rules editor it would show !Customer part of the dim correctly but then have -Product colour coded as if it were a number or static string value. Not hugely problematic but annoying for my poor eyes.

With regards to the number of cubes, my general view is that "go with as few as possible but NEVER give up performance as a result"... and I would imagine that adding in extra dummy elements and unused measures would have some impact. At the end of the day reusing a measures dimension works with inter-related cubes but using the same one throughout the model IMHO would only be useful if the model was extremely simple i.e. the sort you see with some of the IBM sample DBs.

And I love prefixes on cubes as it groups them together so when making changes at a later point you can quickly see what is interdependent.

None of this is meant as a negative to your own viewpoints, just a few annoyances of my own added into the mix.

P.S. I regularly build objects using the _ and } symbols but nothing else, } just to keep them hidden away and the _ mainly for aesthetic sake without it being a character that has effect on calcs etc.

Hello Solanna

I just re-read your post, and it strikes me that you write:

Solanna wrote:... but most likely my Measures dimension would satisfy every cube in my model so I wouldn't need a separate measures dimension in the first place

Right. I hope that you wrote this as a kind of joke? (given the smiley) Or did you use to create 1 super-measures dimension containing elements Value1, Value2, Value3, Value4, Value5, ...
Some cubes use a few values, other cubes use other values. But overall, one dimension suffices.

"Sir, we need a new measure! And a dummy element as well !"
"Okay, no problem, let's add it to our suuuuper-measures dimension! Then we don't make the mistake to put it in the wrong dimension! Clever thought!"
[30 seconds later]
"Okay Sir, I'm done, I added measure Value6."
"Great job! Now we can continue searching our intersections in the cube, let's throw in a few dummy elements as well ! Can you also update the lookup cube that tracks which cubes use what measures? Thanks!"

Even in a much less ironic way, it's a rather bizarre way of TM1 modelling. It works for cubes where Excel could compete with TM1.

As so often with TM1 personal preference allows you to do what you please and it'll typically work for you.

However it my experience I would find Solanna's methodology would work best where
- the model is fairly small and homogeneous (e.g. just finance or just HR)
- users don't use the model very often so are likley to forget acronyms
- the model is unlikely to change or grow over time
- the model will be maintained by the same person who knows where everything is

Wim's style of method works caters better for models
- that cover a wider range of business activities
- where users are using it fairly often so pick up the meaning of abbreviations
- that will evolve
- that are maintained by multiple people or where the developer may move on

Solanna's views seem to me to work best when you're talking about evolving a spreadsheet up into a cube framework. IMHO a more robust (yes, that means techy) framework is required if you are building an enterprise scale model. While I understand a lot of TM1 developers come from finance or other operations areas, that doesn't mean that IT frameworks should be ignored as geeky and out of TM1's realm. TM1 is an IT tool.

Oh, and the way I get around having too much techy stuff in front of the users is using security to hide stuff they don't need (e.g. most TI process and my system cubes) and also providing either Excel based front ends, web based front ends, or grouping views - with user friendly names - into TM1 Applications folders.

Martin