managing rules and dimensions worksheets

Post Reply
User avatar
bihints.com
Posts: 52
Joined: Tue May 20, 2008 8:56 am
OLAP Product: TM1
Version: 9.0.3
Excel Version: 2003
Contact:

managing rules and dimensions worksheets

Post by 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
Site Admin
Posts: 1453
Joined: Wed May 28, 2008 9:09 am

Re: managing rules and dimensions worksheets

Post by 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.)
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: managing rules and dimensions worksheets

Post by 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 :D
John Hobson
The Planning Factory
User avatar
Eric
MVP
Posts: 373
Joined: Wed May 14, 2008 1:21 pm
OLAP Product: TM1
Version: 9.4
Excel Version: 2003
Location: Chicago, IL USA

Re: managing rules and dimensions worksheets

Post by 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.
Regards,
Eric
Blog: http://tm1-tipz.blogspot.com
Articles: http://www.google.com/reader/shared/use ... /label/TM1


Production: 32 bit 9.0 SP2, Windows 2000 Advanced Server. Web: 32 bit 9.0 SP2, Windows 2000 Server. Excel 2003
User avatar
Eric
MVP
Posts: 373
Joined: Wed May 14, 2008 1:21 pm
OLAP Product: TM1
Version: 9.4
Excel Version: 2003
Location: Chicago, IL USA

Re: managing rules and dimensions worksheets

Post by 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.
Regards,
Eric
Blog: http://tm1-tipz.blogspot.com
Articles: http://www.google.com/reader/shared/use ... /label/TM1


Production: 32 bit 9.0 SP2, Windows 2000 Advanced Server. Web: 32 bit 9.0 SP2, Windows 2000 Server. Excel 2003
User avatar
jim wood
Site Admin
Posts: 3951
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: PA 2.0.7
Excel Version: Office 365
Location: 37 East 18th Street New York
Contact:

Re: managing rules and dimensions worksheets

Post by 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.
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
Mike L
Posts: 58
Joined: Fri Jun 13, 2008 4:01 pm

Re: managing rules and dimensions worksheets

Post by 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.
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: managing rules and dimensions worksheets

Post by Alan Kirk »

David Usherwood wrote: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.
David Usherwood wrote: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.
David Usherwood wrote: 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.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: managing rules and dimensions worksheets

Post by Alan Kirk »

Eric wrote: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. :D
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
User avatar
Eric
MVP
Posts: 373
Joined: Wed May 14, 2008 1:21 pm
OLAP Product: TM1
Version: 9.4
Excel Version: 2003
Location: Chicago, IL USA

Re: managing rules and dimensions worksheets

Post by Eric »

Alan Kirk wrote: 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. :D
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! :D


* 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? :lol:
Regards,
Eric
Blog: http://tm1-tipz.blogspot.com
Articles: http://www.google.com/reader/shared/use ... /label/TM1


Production: 32 bit 9.0 SP2, Windows 2000 Advanced Server. Web: 32 bit 9.0 SP2, Windows 2000 Server. Excel 2003
Simon Bradshaw
Posts: 7
Joined: Wed Jul 23, 2008 2:11 pm

Re: managing rules and dimensions worksheets

Post by 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.
User avatar
paulsimon
MVP
Posts: 808
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: managing rules and dimensions worksheets

Post by 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
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: managing rules and dimensions worksheets

Post by Alan Kirk »

PaulSimon wrote: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.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
User avatar
bihints.com
Posts: 52
Joined: Tue May 20, 2008 8:56 am
OLAP Product: TM1
Version: 9.0.3
Excel Version: 2003
Contact:

Re: managing rules and dimensions worksheets

Post by bihints.com »

Thanks everyone for your input!

In the end I am going with Mike L's approach.
Mike L wrote: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...
Post Reply