Preventing data entry

[jonathan.d]

Hi All,

Was wondering... is there a better/more efficient way to prevent data entry in certain cells from TM1, without using rules or formulas in excel front-end?


Suggestions are appreciated as always!

[Alan Kirk]

Was wondering... is there a better/more efficient way to prevent data entry in certain cells from TM1, without using rules or formulas in excel front-end?

Suggestions are appreciated as always!

Standard TM1 security can prevent them from writing back as required.

If the cells contain consolidations the users wouldn't be able to write to them, though data spreading would still work. (TM1 security permitting.)

And of course you can always unlock the cells that you want the user to be able to enter to, then protect the worksheet which effectively locks the other cells.

These are the usual methods, or is there something specific to your situation that I'm missing?

[jonathan.d]

Hi Alan,

Thanks for that!

Although I was thinking of something more dynamic to cater to the scenario of 'Budget Entry'. Where you want users entering numbers in the right spot.

To have it done dynamically in tm1, as month after month where they enter data changes.

I hope it makes sense.

[John Hobson]

Your best bet is probably to attach some VBA to a button with a mixture of conditional formatting, and setting the Excel locked property based on an elapsed period flag.

Not totally dynamic I grant you.

Hope this helps

John

[Steve Vincent]

settings cube or attribute would also work;

eg. a TI loads actual data each month. TI prompts the user for the period to load to. The same process sends the value to a cube that holds a "current month" element. Another TI can be used to lock all periods other than the one in the settings cube. It could even be done with rules, but i'd debate the need for speed (heheh) over the hassle-free method it would give you.

[Martin Ryan]

Everyone else is being careful not to mention cell level security because it can be a bit slow if not written correctly, but this will give you complete control over who can write to where. I quite like it and have found that using it with rules is pretty good.

Usually I'll create a pseduo security cube that has only the dimensions that I want to drive security by. For example I might use version, year and months, because I want to have 2010 completely open for the budget, Oct thru Dec 2009 open for the forecast and only July 2009 open for Actuals. Then in the cell security cube you can write a rule like so

Code: Select all

[] = S: DB('PseudoCube', !Version, !Years, !Months);

which will pull across the security for all your other dimensions. You may wish to vary to cater for different groups who have different access rights.

Note that if a cell in the cell security cube is blank, then the cube/dimension security mentioned above will take effect.

Martin

[David Usherwood]

I agree with you - and I have also found that cell security with simple rules is not that slow in practice. Indeed, my technique is pretty nearly identical to yours. Do we have a Newton Leibniz issue?
http://en.wikipedia.org/wiki/Leibniz_an ... ontroversy
Not forgetting Tom Lehrer's Lobachevsky song
http://www.sing365.com/music/lyric.nsf/ ... 7D0024B8B9

[jonathan.d]

Thanks guys!

Cell level security along with rules definitely seems like the way to go.

I'll give it a go..now

[jonathan.d]

Sorry...forgot to add this bit to the previous post.

But, as I want to color code the cell in which users can enter data...is there a way to go like...

If CellIsUpdatable

orange

Else

white


Guessing the TM1 API has to be used, would TM1ValIsUpdatable return something on which I can base my conditional formatting in excel on?

I haven't played around much with the api.

[lotsaram]

If you are going down the cell security route then you could do it all in Excel and read the cell security cube contents into a hidden range of columns, then just base conditional formatting for the input range off the security cell values ...

[wissew]

I favor the cell security method but with the use of attributes that update automatically when the reporting month changes.

#Example allows for writing to outer months in the current year and all months in outer years:
[Category:'Outlook']=S:if(
((attrn('Year', !Year, 'OuterYear')=1
%(( attrn('Year', !Year, 'OuterYear')=0)&(Attrn('Month', !Month, 'ReadMonth')=0)))
#This exception allows for a department to be locked from input. i.e. The source is a feed from another server.
& Attrn('ExpDept', !ExpDept, 'Always locked')=0)
,'Write','Read');

On a 64 bit OS it has no impacts on performance.

Wes

[Jeroen Eynikel]

@ wissew:

do you mean that you see a clear performance degradation on 32 bit but none whatsoever on 64 bit? That would seem weird to me.

I.e. I can imagine less degradation on 64 Bit purely because the CPU will be more performant. I.e. the odds are pretty high that the average 64 bit system out there is a lot more recent and has more performant CPU's than the average 32 bit system. I do not see why a 64 bit system would be faster when using cell level security than a 32 bit if all other things are alike though.

[Steve Rowe]

I think the issue it that alot of us have long memories and first impressions stick...
When we first got cell level security we would have mostly all been on 32 bit systems. Also as is often the case when the functionalty is new it was not perfectly implemented and the system developers did not have the experience (or documentation!) on the best way to use it.

So the first impression of cell level security was that it slowed down performance. This kind of impressions sticks and for a long time I've certainly been guilty of trying to avoid implementing it because of my first impression.

Subject to the limitations of the 32 bit environment it's probably safe to implement in either 32 or 64 bit, but as ever the only thing you can do is suck it an see!
HTH

[Martin Ryan]

A minor consideration is that quality control do not test cell security when it is populated by rules. They do however test string rules, and cell security cubes separately, so the effect is the same. I don't think it's a problem, but something to be aware of.

Martin