Different Security for Excel App and Cube Viewer

[John Hammond]

We have the problem that we want users to enter data into cubes through Excel Applications (slice, active form), but we dont want them to be able to enter data through cube viewer, but we need them to have the OLAP functionality in order to query their data.

If you set access to the cube by right clicking on the cube and security... to 'read' then the OLAP works fine, but you cannot update in the Excel application.
If you set access to the cube to 'write' then you can update in cube viewer, and the Excel App works fine.

Clearly a dynamic update to the cube }cube_security to change access just prior to the user going into the excel application will not work since there are more than one thread/users per group using TM1.

It would be appreciated if anyone has managed to solve this problem or who knows that the problem is unsolveable even in the latest release.

As always thank-you for your input.

Regards

John

[lotsaram]

Hi John,

I am a bit flummoxed by your question but I think I know what you're on about with this.

Let's handle the easy bit first. Basically the answer to your question is NO! TM1 operates a client <-> server model where the TM1 server is totally "agnostic" about which client UI the user happens to be using. All the TM1 server cares about is the security rights assigned to the user, in this case read or write access to certain cube areas. The server doesn't care via which interface the cube is being updated. A central part of TM1 design philosophy is "do it once", that is you set up security once for the object in question and that security then applies to all UIs through which the object might be browsed.

However I do think that I understand where you are coming from as when designing an application you can put in place much more validation and checking with an Excel or websheet UI than you can via the cube viewer. And sometimes to go to the same length with security and validation in the cube viewer (even in 9.5.1) can be laborious and not worth the effort versus Excel. Generally this is OK as 99% of users will use the templates they are given. However the problem persists that the other 1% (your "adventurous" or "troublesome" users) might try entering data directly in another UI that doesn't have all your in-built controls and validations, ... and from a TM1 security perspective there is nothing you can do to stop them. The only route you therefore have open is to avoid this in the application design that is tightly manage the application UI so that TM1 web or EV is the only access to the server.

You can also effectively take away server explorer in the Excel UI via tm1p.ini settings but by the sounds of it you don't want to do this as you want your users to be able to slice and dice in the cube viewer. In this case your only answer might be to partition your model to put write back areas somewhere else where you more tightly control access. This isn't pretty as it introduces complexity. (Sometimes user training and education can be the least costly route ...)

It's not much but HTH.

[stephen waters]

John,
As lotsaram says, TM1 web ( and EV) gives you a much more controlled environment for both "Olap" funtionality and tailored Excel based, reports and interfaces.

However if you dont have this look at the In Spreadsheet Browser. You have not been specific about the funtions you need but the ISB gives you drill down, element selection, rotation, dimension stcking etc. As already pointed out, standard TM1 security will apply to the ISb just as to classic slices and server explorer.

One caveat, there have been suggestion from Applix\IBM Cognos in the past that they would discontinue the ISB. However it is still there and I am not aware of a current decommissioning statement.

[Alan Kirk]

One caveat, there have been suggestion from Applix\IBM Cognos in the past that they would discontinue the ISB. However it is still there and I am not aware of a current decommissioning statement.

Quite the opposite. They had previously included it in an end of life statement, but reversed that in the 9.5.1 read me.

[stephen waters]

Alan,
Thanks for the correction, need to get up to date with my release notes.

For interest, the story I heard was that the revival of the ISB was due to TM1 replacing Essbase for internal use within IBM. The IBM'ers were used to and liked the Essbase Excel add-in which is similar to the ISB.

[John Hammond]

Thanks everyone, especially LotsARam

I am a bit flummoxed by your question but I think I know what you're on about with this.

Your description of my problem was a lot more succint!

You can also effectively take away server explorer in the Excel UI via tm1p.ini settings but by the sounds of it you don't want to do this as you want your users to be able to slice and dice in the cube viewer. In this case your only answer might be to partition your model to put write back areas somewhere else where you more tightly control access. This isn't pretty as it introduces complexity. (Sometimes user training and education can be the least costly route ...)

Tried this and unfortunately tm1p.ini only reflects the settings in explorer so if you change DisplayCubes=F in the file you can just go to the menu and restore cubes to your tree.

Will have a play with TM1 Web/EV and see what can be done here.

Thanks John