How to expose attributes to end users for entry

Post Reply
User avatar
John Hobson
Site Admin
Posts: 330
Joined: Sun May 11, 2008 4:58 pm
OLAP Product: Any
Version: 1.0
Excel Version: 2020
Location: Lytham UK
Contact:

How to expose attributes to end users for entry

Post by John Hobson »

Hi

I have an existing model with 16,000 products each having up to 12 attributes.

Some of these will be loaded by a hierarchy build, but others have to be entered by the end user.

We started off using a spreadheet with DBRA() formulae but the performance over a LAN was so abysmal that my client is now insisting on using Server Explorer (you know the admin only interface that Applix refused to develop into anything user friendly) where performance is acceptable.

The problem I now face is how to expose the attributes to end users for entry as the attribute cubes are control objects which makes me a bit jumpy (although I know they are really just or'n'ry cubes really).

My question is, assuming appropriate security on the other control objects is in place, is there any reason why I should not allow access to a control object to an end user?

TIA
John Hobson
The Planning Factory
User avatar
Renaud MARTIAL
Posts: 25
Joined: Thu May 15, 2008 10:18 am
Location: Paris, France

Re: How to expose attributes to end users for entry

Post by Renaud MARTIAL »

About your question, I've no answer :)
But if you're reluctant exposing the control cube, you can create a specific cube in which users would input values for attribute, and create a rule on the }elementattributes_xxx cube to retrieve those values.

I've not tested this by myself, but I think it could work ....

Regards,

Renaud.
User avatar
Steve Vincent
Site Admin
Posts: 1054
Joined: Mon May 12, 2008 8:33 am
OLAP Product: TM1
Version: 10.2.2 FP1
Excel Version: 2010
Location: UK

Re: How to expose attributes to end users for entry

Post by Steve Vincent »

aren't there restrictions on what you can do with them unless you have the admin license? never tried it myself, i know a normal user can view the control objects but i've never needed them to update anything before. a rule from a mirror cube might be a little heavy calculation wise, but same approach with a TI would also work.
If this were a dictatorship, it would be a heck of a lot easier, just so long as I'm the dictator.
Production: Planning Analytics 64 bit 2.0.5, Windows 2016 Server. Excel 2016, IE11 for t'internet
User avatar
John Hobson
Site Admin
Posts: 330
Joined: Sun May 11, 2008 4:58 pm
OLAP Product: Any
Version: 1.0
Excel Version: 2020
Location: Lytham UK
Contact:

Re: How to expose attributes to end users for entry

Post by John Hobson »

Thanks guys

Sorry - I should have said that I discounted the idea of holding the data twice and linking via a rule already as being "sub-optimal".
I think that waiting for or having to kick off a TI update would also be a bit clunky for end users used to more end-user friendly systems

I have a bit of a problem now as the only machine I have here has the admin licence on it.

Can anyone confirm if attribute values are updateable via the control cubes by users without admin licences?

(And can I just pass my warmest congratulations to the idiot who programmed the "edit Element Attributes" code - It's just taken 10 minutes to load this dimension's attributes at 100% on a blisteringly fast laptop with 3Gb of RAM. As I'd forgotten to log out as Test User and log back in as Admin I'm now waiting another 10 minutes and my laptop feels hot enough to fry an egg AAAGHHH!)
John Hobson
The Planning Factory
Marcus Scherer
Community Contributor
Posts: 126
Joined: Sun Jun 29, 2008 9:33 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2016
Location: Karlsruhe

Re: How to expose attributes to end users for entry

Post by Marcus Scherer »

John,
In your Excel sheet skip the DBRAs and generate a flat file instead. This would be imported with TI.

just thinking how to simplify things....

Marcus
User avatar
John Hobson
Site Admin
Posts: 330
Joined: Sun May 11, 2008 4:58 pm
OLAP Product: Any
Version: 1.0
Excel Version: 2020
Location: Lytham UK
Contact:

Re: How to expose attributes to end users for entry

Post by John Hobson »

Marcus

Thanks for the idea - I can see that working with one user - it's how we do the main hierarchy build after all, but I'm not sure about going into production with 50 people any of whom may need to update an attribute at any time and need to see an immediate result.

It's odd that Applix have kept attribute so unexposed in Server Explorer. I think it's another example of them not really thinking through how the feature might be used and making the false assumption that it's only and admin thing.
John Hobson
The Planning Factory
Marcus Scherer
Community Contributor
Posts: 126
Joined: Sun Jun 29, 2008 9:33 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2016
Location: Karlsruhe

Re: How to expose attributes to end users for entry

Post by Marcus Scherer »

John,
You want to keep things simple and that's OK. But I think that your requirements turn out to be not so simple. Your users are doing kind of planning/scenario processes with the attributes. That means you have to make some effort to bring the application close to the client and away from the admin point of view.
Renauds/Steves proposals are such a step.

Regards,
Marcus
User avatar
Steve Rowe
Site Admin
Posts: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: How to expose attributes to end users for entry

Post by Steve Rowe »

On you original sheet with the DBRA have you tried using
DBSA to send the attribute to dimension or
DBSS to send the value to attribute cube?

It's possible you will get better performance?
Cheers,
Technical Director
www.infocat.co.uk
User avatar
mattgoff
MVP
Posts: 516
Joined: Fri May 16, 2008 1:37 pm
OLAP Product: TM1
Version: 10.2.2.6
Excel Version: O365
Location: Florida, USA

Re: How to expose attributes to end users for entry

Post by mattgoff »

John Hobson wrote:Thanks guys

Sorry - I should have said that I discounted the idea of holding the data twice and linking via a rule already as being "sub-optimal".
I think that waiting for or having to kick off a TI update would also be a bit clunky for end users used to more end-user friendly systems

I have a bit of a problem now as the only machine I have here has the admin licence on it.

Can anyone confirm if attribute values are updateable via the control cubes by users without admin licences?
Just my opinion, but I think you should reconsider using a normal cube with rules updating the control cubes. While I agree that it's "suboptimal," it appears to meet your other requirements (real time, any user can access w/o Perspectives license). In the grand scheme, it's not really that suboptimal anyway-- just a small amount of redundant data that solves a real technical issue: 15k redundant cells of data + 15k rule-driven cells is a tiny blip in RAM/performance. You'll reap benefits by avoiding the need to retrain users about control cubes and how attribute cubes work differently from normal cubes.

Matt
Please read and follow the Request for Assistance Guidelines. It helps us answer your question and saves everyone a lot of time.
User avatar
Martin Ryan
Site Admin
Posts: 1988
Joined: Sat May 10, 2008 9:08 am
OLAP Product: TM1
Version: 10.1
Excel Version: 2010
Location: Wellington, New Zealand
Contact:

Re: How to expose attributes to end users for entry

Post by Martin Ryan »

Rules for numeric attributes can be tricky though. We've found the best way to deal with this is to rule the numeric data as string data, then process it via TI's StringToNumber into a numeric value stored in the attribute cube. I think this is because numeric attributes are of type "AN", rather than "N", i.e. it's not actually a straight forward numeric data type.

Running TI processes may negate the time savings made by using rules.

Not that I have a better idea...

Martin
Please do not send technical questions via private message or email. Post them in the forum where you'll probably get a faster reply, and everyone can benefit from the answers.
Jodi Ryan Family Lawyer
User avatar
John Hobson
Site Admin
Posts: 330
Joined: Sun May 11, 2008 4:58 pm
OLAP Product: Any
Version: 1.0
Excel Version: 2020
Location: Lytham UK
Contact:

Re: How to expose attributes to end users for entry

Post by John Hobson »

@Marcus
Your users are doing kind of planning/scenario processes with the attributes. That means you have to make some effort to bring the application close to the client and away from the admin point of view.
Don't worry - I have a wealth of experience of finding ways (mostly using VBA) of getting round the severe limitations that TM1 seems to place on end users performing even basic planning process (like selectively seeding data and adding new elements to dimensions).

I have, as you point out, been trying to keep this simple and I am also very aware that my client is not happy with the performance in this cube so far, so doubling up by storing the attributes in a normal cube and then putting them in the attribute cube with a rule , which I initially contemplated before posting, is unlikely to provide a satisfactory answer :(

I'm not sure why you suggest that my requirement is not simple though - it seems that it SHOULD be very simple to me. All I want to do is to allow end users to change values of data. The problem arises because this data has to be held as attributes if we are to be able to use filters on it, and TM1 does not expose attributes in the same way it exposes "normal" data. That doesn't make the requirement complicated but it does mean a solution that will be acceptable to an end user is not immediately obvious.

In this case the problem that gets in the way of the suggested solutions is size of the dimension (16k members), it's dynamic nature (products come and go all the time) and the requirement that any planner may need to change any attribute at any time and see an instant result.

You might think that having end users planning things like attribute values should not present problems in a "real time collaborative planning" environment. I certainly do, but unfortunately there does not seem to be an answer that is going to be acceptable unless I can expose the attribute cube itself to them.
This would work fine but I have not yet been able to get Cognos to confirm that this is an appropriate thing to do.

The "obvious" solution - using an Excel sheet just seems to be ponderously slow even when I get them to select only items within an individual sub product group (circa 100 items at a time?). I have a feeling that anything that ends up having to retrieve a dynamic list of products from a large dimension is going to be slow if it has to go over a WAN into Excel. I'd love to be proved wrong though.

@ Steve

I haven't tried that - Thanks for the idea - I'd still have to retrieve the current set of products and their initial values. I suppose I could have a macro that retreived the items and their current values then range valued the list and DBSD any changes. Apart from that initial load I can see this might be a bit quicker and might well be worth a look.

I'm not trying to be negative and I do appreciate the feedback - I'm just a bit frustrated by this one as it really should not be this problematic to get an end user to change an attribute value!

@ Martin / Matt

You posted as I was typing

I think the answer is going to have to be the double cube scenario but I'm not so sanguine as you about 16k x 12 attributes not having any performance impact!

I'll have to do some testing :D
John Hobson
The Planning Factory
User avatar
Steve Vincent
Site Admin
Posts: 1054
Joined: Mon May 12, 2008 8:33 am
OLAP Product: TM1
Version: 10.2.2 FP1
Excel Version: 2010
Location: UK

Re: How to expose attributes to end users for entry

Post by Steve Vincent »

John, just asked one of our users to check for you and assuming the security is setup to allow it, a normal client can turn on the control objects, open an }elementattributes cube and change values within it. If they use application folders then i'd suggest creating a public view and putting a link to it in there, otherwise they will have to have the control objects on all the time and if you model is as big as this one then it's easy to get swamped with them all.
If this were a dictatorship, it would be a heck of a lot easier, just so long as I'm the dictator.
Production: Planning Analytics 64 bit 2.0.5, Windows 2016 Server. Excel 2016, IE11 for t'internet
User avatar
Steve Rowe
Site Admin
Posts: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: How to expose attributes to end users for entry

Post by Steve Rowe »

John,

A thought as I have read your OP properly

The user needs to update some attributes against a large (or non-trivial sized) dimension.
If you do that in Excel it is too slow because TM1 updates everything not just the changes.
So we need to get just the changes sent across the network (since this is the slow bit)

So and this might sound silly but ....

1. Populate an excel sheet (using VBA or importing an output from TI) with the current values of the relevant set of attributes. Call this Original.
2. In the same work book create a copy of the sheet you created in step 1. Call this Values to Send.
3. In another sheet or on the Values to Send sheet create your DBSA formula, but instead of just having = DBSA ( blah blah), have = If (Original!A1 = ValuesToSend!A1, 0, DBSA (blah blah ))

Then the users change the values they want to on the Values to Send sheet but the DBSA only gets executed where the cell has changed. I've used a formula like this before and seen good performance.

Hope this makes sense and gives you a possible way forward...
Technical Director
www.infocat.co.uk
User avatar
John Hobson
Site Admin
Posts: 330
Joined: Sun May 11, 2008 4:58 pm
OLAP Product: Any
Version: 1.0
Excel Version: 2020
Location: Lytham UK
Contact:

Re: How to expose attributes to end users for entry

Post by John Hobson »

@ Steve V

Wow - thanks for doing that - I've been snowed under with other stuff and was going to try it on an old laptop tonight but you've saved me having to do that - I really appreciate that, and it sound like the answer I needed, Great idea about the application folder thing too. TVM!

@ Steve R

That's also a very interesting technique which I will take a look at. (Not silly at al BTW!)

That's why I love this forum :lol:
John Hobson
The Planning Factory
User avatar
Steve Vincent
Site Admin
Posts: 1054
Joined: Mon May 12, 2008 8:33 am
OLAP Product: TM1
Version: 10.2.2 FP1
Excel Version: 2010
Location: UK

Re: How to expose attributes to end users for entry

Post by Steve Vincent »

No problem John, there are several users in my office that only have client access so it wasn't that hard or timeconsuming to ask one to check it :) And the "switch" idea on the DBSA formulae is one i've used plenty of times, also on DBS too and works pretty well in our instances, defo worth a look at if you get the chance.
If this were a dictatorship, it would be a heck of a lot easier, just so long as I'm the dictator.
Production: Planning Analytics 64 bit 2.0.5, Windows 2016 Server. Excel 2016, IE11 for t'internet
Post Reply