TI flat file loading by non-admins

[David Usherwood]

I'm working with a client reimplementing their system. Currently they use some rather large DBSW worksheets for updating, and these are fairly slow and can lock up the server. My thought is that writing flat files and reading in via TI would be better. So far, so good.

(The client is now on 8.4.3 but we are recommending 9.0SP3U9 for the reimp.)

What I'm pondering is the security and multi-user implications. The data is prepared by and owned by non-admins. They will have either an 'entity' each or possibly several. They can be given accesss to run the TI load(s) but the TI load itself runs with full access to the server. I would like not to have lots of nearly identical TIs with custom security - nasty maintenance.

We've had a look at the 'get user via MDX' approach but aren't keen as we'd have to work from there via groups to entities.

My present thinking is to create a control cube dimensioned by entity and something random eg year. The user running the job puts a 1 against the entities they are loading - they can only do this for 'their' entities. The TI refers to the cube and only updates the marked entities. At the end of the job the control cube is cleared.

Need to consider collisions. Under 9.0SPxx a TI locks the server while it runs. For this setup that behaviour is perfect - users can't update the control cube while another load is running. Under 9.1/9.4 - not so clear - I can't find explicit descriptions in the reference area, just the absence of lockoff and lockon (which I saw no need of here anyway). So I have some concerns going forward about mixups.

Views/feedback? Better ways?

[Eric]

I do not know f it is better, but here is a thought to consider if users and the server are on the same LAN. I believe security is intact when you use the excel interface "Process Data" on the excel toolbar. Remember the tool before TI, it might be old school, but could work.

Just a thought.

[David Usherwood]

I agree - the security would work as for a full XLS DBSW. But I think the performance would be about the same too

[jim wood]

If you upgrade their software to version 9.1 SP3 U2 then you will not have to worry about locking the server as well,

Jim.

[Steve Rowe]

Isn't process data getting dropped in an upcoming release?

Also I would expect perforamnce to be worse with a process worksheet since you would probably process one value at a time and be in DBS world rather than using DBSW which would use the whole netwrok packet.

Something to consider about the performance of the DBSW sheets rather than network traffic is what happens when the data gets server side. Is it triggering feeders for example? What is the performance of the DBS sheets if you take the rules for the cube out? This should tell you if the problem is network or feeder related.

With regard to TIs and flat file selection I've had to do something similar recently.
1 A TI that will load the flat file it is pointed at.
2 Flat files have a specific naming convention (not under the users control, from the GL that spits the file out) that associates them with an entity or group of entities.
3 VBA controls the selection of the file to be loaded which is passed to the TI. Since VBA knows the user it can work out the entity files that should be presented to the user for selection.
4 SInce the TI is launched using VBA / API the TI is not visible to the user, only the user the API conencts as.

Not quite sure what problem your trying to solve....

If you don't use the TM1 user and reference an attribute or security to control which entity they can load too, then you have to create the relationship manually at run time (your control cube). If you add the user dim to control cube does this prevent the issue with overlapping TIs?

Anyway HTH...

[David Usherwood]

If you upgrade their software to version 9.1 SP3 U2 then you will not have to worry about locking the server as well,

Jim.

Clearly a slip of the keyboard - I'm sure you meant to write 'downgrade'. 9.4 looks promising (hold off for MR1).We have exactly one client on 9.1 and I intend to keep it that way.

Having said that, I hear from David Corbett at last week's Cognos bash at Bedfont Lakes UK (see Martin's excellent writeup) that the _very latest_ 9.1 (SP4?) shares a lot of code with 9.1 so we are planning to give that a thrashing, as it sidles neatly round the no way back from unicode issue.

[David Usherwood]

Responding to Steve:
I would agree about the performance hit from process worksheets.
We didn't write the present system and I suspect the DBSW workbooks could stand some optimisation. I haven't really examined them yet.
Networking _should_ be OK (Lan not Wan). Feeder firing may well be relevant and something we can keep a beady eye on as the rebuild progresses.
The aim is to get the data in quickly without building some monster subsystem which is hard to maintain.
In my testing, with 9.0 on my laptop, TI reads in 40k records per second which seems faster than a spreadsheet would go. Think we'll do some time tests.
And under 9.0, I don't think contention will be an issue. In due course, with 9.4, I'm mulling over having our own contention flag - TI tests and exits (or pauses?) if set, other TI turns off when finished. Since different departments handle different entities contention isn't really a big deal.

[Steve Vincent]

Without doubt the TI route is the right one, any type of data loading from Excel is always going to be far slower. As for security, if it's that much of an issue then an Excel front end to run the TI is probably the easiest. So much more you can do with Excel / VBA in regards to checking and user control but might take some time to get right.