managing rules and dimensions worksheets

[bihints.com]

I am wondering if any of you has got some method to make sure that:
.all power users will load a rule/dimension worksheet from the right folder and not some local version that they might have
.all power users saved the new rule/dimension in the right folder after updating the rule/dimension on the system

I am thinking to set power users' tm1p.ini file read-only, but that goes out of the window if they start working from another workstation.


Personally, I'd rather live without rule/dimension worksheets because of the above issues, it is just too easy to mess up a system.
So is there a bulletproof best practice on that matter to eradicate these pitfalls?

[David Usherwood]

I think you more or less fed yourself the answer.

Rules worksheets are dead. Not that either editors are good - but they are better than Excel at least. (I recall that Cognos are dropping the pretty but flakey 9.1 editor.)

Dimension worksheets - mainly used by people who go back to version 6. (I do too, but I believe in moving on...). But...

It's not pleasant to maintain a large complex dimension with lots of 'hierarchies' (the concept TM1 doesn't have) in either the editor or XDIs. I recommend our clients maintain a flat CSV format which they can filter with Excel and import with TI. Simple, slowly changing dims - what's wrong with the editor? (Except on 9.1, where the last one or two items don't show, for some weird reason.)

[John Hobson]

Rules worksheets are dead. Not that either editors are good - but they are better than Excel at least.

haha

Not round here they're not David.

Not until Cognos provide something which even approaches the flexibility and ease of use of rule worksheets!

Does that make me a Luddite - maybe , but I'm a happy one

[Eric]

I haven't gotten around to it, but when I do I plan to maintain the hierarchies in an MS Access Dbase and update info in there and then use a TI script to update TM1.

[Eric]

Ohh forgot to comment on rule.

I have tried the spreadsheet apporach but am not a fan. I just go straight in rule editor and use font color and size to flag items. I am still on 9.0 so I have not had a chance to use the new editor but I am looking forward to it when I upgrade to a newer version.

[jim wood]

I tried using both dimension and rule worksheets here, but with the ever changing nature of network it proved to be a bit of a nightmare. I have used a combination of CSV files and TI scripts for some dimensions and the editor for the majority. I have also used the rules editor for all of my rules. I do however do them in notepad first and copy them in. (I like living dangerously. If they load without an error I am of course a coding god. If they don't then I am a little school boy. Guess which one I normally am?)

Jim.

[Mike L]

It is common for power users to be just the sort to hang themselves if given enough rope. One solution is to make them check-out and check-in metadata objects through an administrator who enforces some sort of version management protocol. The administrator need not be a power user, just a bureaucrat. This is actually easier to enforce with Excel files: restrict write access to the spreadsheet directory.

[Alan Kirk]

I think you more or less fed yourself the answer.

Rules worksheets are dead. Not that either editors are good - but they are better than Excel at least. (I recall that Cognos are dropping the pretty but flakey 9.1 editor.)

Dead???? {Sucks in breath through teeth...} Heeeeeathen!!!!!

Iboglix will have to pry .xrus (with their full array of Excel formatting capabilities, the ability to generate similar blocks of code using string concatenation and other formulas, and their almost infinite flexibility in adding commentary) from my cold, dead hand.

Dimension worksheets - mainly used by people who go back to version 6. (I do too, but I believe in moving on...).

So do I, when there's a reason to. With dimensions, unless they're > 65536 members long or they're created via an interface from another system (such as our chart of accounts from our GL system), there isn't. Again, I can put commentary against any elements I want when they're in an .xdi. I can mention when a consolidation was created, who requested it, what it's used for, who's using it. If I have multiple trees, I can colour code them so that I can easily identify them and modify them seperately. I can't do ANY of that with either TI or (worse), the accursed dimension editor.

Let me add that kind of metadata easily (and I don't regard whacking in a string attribute which can't be formatted and is still (until 9.Fword at least) limited to 254 characters), and I might, maaaaaaaaybe, change my view.

But...
It's not pleasant to maintain a large complex dimension with lots of 'hierarchies' (the concept TM1 doesn't have) in either the editor or XDIs. I recommend our clients maintain a flat CSV format which they can filter with Excel and import with TI. Simple, slowly changing dims - what's wrong with the editor? (Except on 9.1, where the last one or two items don't show, for some weird reason.)

I disagree about using an .xdi not being pleasant. Copy, paste, use formulas... all a lot easier IMHO than a lot of the alternatives.

[Alan Kirk]

I haven't gotten around to it, but when I do I plan to maintain the hierarchies in an MS Access Dbase and update info in there and then use a TI script to update TM1.

Go for the hairy-chested he-man approach like I do; when connecting Access and TM1, I always use VBA code calling the TM1 API.

[Eric]

Go for the hairy-chested he-man approach like I do; when connecting Access and TM1, I always use VBA code calling the TM1 API.

Still haven't messed with the API. So sadly I haven't earned my Captain Kirk Badge of honor or received my mail in He-Man sword* (Waiting to hold it in the air a scream "I HAVE THE POWER!!") Also can't wait for my jetpak http://martinjetpack.com/ check out the videos!


* Thinking if Alan has the He-Man sword with it must have TM1 engraved on it somewhere. Then does the Skelator Sword have have a TM1 Web and EV engravement on it?

[Simon Bradshaw]

I hardly ever use the .xru sheets since upgrading from 8.3.3 and having access to the new rules editor.
It's awesome compared to its predecessor.
No more dumping into Notepad and changing to Tahoma 12 point.
When will we have something similar in TI?

Conversely though I never use the dimension editor, it always seems simpler in a .xdi.
Maybe thats because it's mainly finance based and makes efficient use of spreadsheet functionality.
For example, all the attributes are maintained within .xdi sheets.

[paulsimon]

Hi

I agree that Rule and Dimension Worksheets can be dangerous because they can easily get out of step with the underlying dimension. If you save a Dimension Worksheet that doesn't have all the elements in the underlying dimension, because it has got out of step, then you lose data.

By choice, I only use Dimension Worksheets for Time Dimensions. I mainly use TI, or a little Dimension Editor. However, Dimension Worksheets can be good at the prototyping stage.

In some cases the hierarchy we want doesn't exist in the source system. In that case I prefer to hold it externally in a CSV file or database. That way you can use TI to create the base level elements and whatever hierarchy you can get from the source system, and then add additional hierarchy from the CSV/database. Then you only have to maintain the hierarchy source in one place, and that source will be the same whether the user is running a local server or a network server.

I prefer to avoid using the new dimension editor where possible. I much preferred the old V7 dimension editor. That editor allowed you to drag and drop elements more reliably. I did ask Applix at the time to give users the option of the old or the new editor, but the old editor disappeared. The old editor also allowed you to rename elements - great for sorting out naming conventions. In the new editor, you need to delete and re-add, which can cause problems. I know that renaming did a delete and add behind the scenes but that doesn't matter for consolidations, and a warning message that you are about to lose data would have been preferable to withdrawing the ability to rename altogether.

For Rules, I still prefer the old Rules Editor. I previously used Rule Worksheets when it was necessary to use Excel to auto-generate rules. However, with the advent of the CONTINUE statement that really isn't necessary anymore.

I don't like the new 9.1 SyncFusion Editor, and always turn it off. My main concerns are that it is buggy and causes crashes in the client, and secondly that, when adding references to another cube, it just puts in commas between the dimensions, and not !dim names, which makes it harder to work out what you need to provide to the other cube. It has the potential to be a step in the right direction, but like dynamic worksheets, it is a half finished enhancement.

Dimension Worksheets do provide a way to move a dimension from the dev server to the prod server. However, I have TI scripts to do that, and they can take the related items such as attributes over as well.

Regards

Paul Simon

[Alan Kirk]

Hi
I agree that Rule and Dimension Worksheets can be dangerous because they can easily get out of step with the underlying dimension. If you save a Dimension Worksheet that doesn't have all the elements in the underlying dimension, because it has got out of step, then you lose data.

I think that's a danger that just needs to be addressed by administrative policies, though. In our setup only the Admins can modify dimensions, not users. We know which dims come in from TI, we know which dims are maintained by .xdi. Each dimension has (generally) ONE way of being modified, and should not be modified in any other way.

The sole exception is our chart of accounts dimensions; in some cases we need to add in custom consolidations which don't exist in the source ledger system. However before doing that, we ALWAYS generate a new .xdi immediately beforehand.

As with Simon Bradshaw, we also find .xdis useful for maintaining attribute values in the one place. In particular it's useful for automatically generating and feeding "Num+Description" style aliases for GL codes; enter the description, create the attribute by string formula, and DBSend or use Beachware copy / paste.

I know that renaming did a delete and add behind the scenes but that doesn't matter for consolidations, and a warning message that you are about to lose data would have been preferable to withdrawing the ability to rename altogether.

That may have just been Iboglix covering their backsides against the potential of irate users. But I'm inclined to agree with you on that one.

I previously used Rule Worksheets when it was necessary to use Excel to auto-generate rules. However, with the advent of the CONTINUE statement that really isn't necessary anymore.

It can be; we have some rules which are similar but subtly different depending on inputs. It's more "bullet-proof" to have these constructed through formulas than rely on the typing of the entire rule being correct.

[bihints.com]

Thanks everyone for your input!

In the end I am going with Mike L's approach.

It is common for power users to be just the sort to hang themselves if given enough rope. One solution is to make them check-out and check-in metadata objects through an administrator who enforces some sort of version management protocol.

So I made a script to monitor changes to rules files (.RUX and .xru). It compares rules files on the production server against yesterday's backup. So I can spot immediately any (un)wanted change.
There you can find it: http://www.bihints.com/monitor_rules_and_processes

I think it is a simple and general approach that can also be applied to monitor other objects like processes, chores, views, subsets...