Is it still worth working on a cube auto promotion tool?

Post Reply
User avatar
JulianS
Posts: 20
Joined: Fri Jul 07, 2017 1:34 pm
OLAP Product: TM1
Version: 10.3
Excel Version: 2013

Is it still worth working on a cube auto promotion tool?

Post by JulianS » Thu Jul 13, 2017 9:20 pm

Hi All,

During my early days in TM1 I got really fed up with trying to copy a cube from one server to another usually so i could develop a cube without taking the entire server across :D

It used to be
Bring it up, oops missed an element attributes cube.
Shut it down
Insert }element attributes cube.
oops .dim file missing
shut it down insert dim
Restart
Forget Insert Element attribute .rux file.
Restart
Hey, where did all my views go,
and on it went.

And I thought that there must be a simpler way to do this.

So I began writing (on and off) an Excel VBA program to copy all the

.cub files, .rux files, .dim files, .vue files (both public and private), .sub files (both public and private), .feeders, .blb

And all the relevent }element_attribute cubes rules etc.

From a tree view of the cubes

It works well at the moment, but doesn't do anything too fancy like picklists with 'dimension' etc that aren't a dimension of the selected cubes.

The code hasn't been changed in the last four years.

I was thinking of spend some weekend evenings trying to spruce it up and add some new functionality, but with things like Pulse now on line, do you think its worth it?

Do you think there is any appetite for a free tool like this, or have you guys got something simpler already?

Jules
Attachments
Capture30.PNG
Capture30.PNG (49.64 KiB) Viewed 788 times

Drg
Posts: 52
Joined: Fri Aug 12, 2016 10:02 am
OLAP Product: tm1
Version: 10.2.0 - 10.3.0
Excel Version: 2010

Re: Is it still worth working on a cube auto promotion tool?

Post by Drg » Fri Jul 14, 2017 10:33 am

Hi. nice screenshot and cool idea.

same function places in perfomance modeler.

for models crated in architect i use powershel script.Wich compare each files in two models and generate object to copy(but this is no good solution anyone).

User avatar
tomok
MVP
Posts: 2317
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Is it still worth working on a cube auto promotion tool?

Post by tomok » Fri Jul 14, 2017 12:21 pm

You know you can always use the replication functionality to do a live promotion from one server to another. Just make sure to replicate and not synch. I have had clients use this before and it works fine. I really wouldn't do it during normal working hours because it can tie up the server and since that is the case it's less risk to do an object copy. However, if you have a problem remembering all the files that need copying it can be a solution.

Edward Stuart
Community Contributor
Posts: 188
Joined: Tue Nov 01, 2011 10:31 am
OLAP Product: TM1
Version: All
Excel Version: All
Location: Manchester

Re: Is it still worth working on a cube auto promotion tool?

Post by Edward Stuart » Fri Jul 14, 2017 1:08 pm

Depending on the environment in question there are a multitude of solutions which fit the brief.

This post around tracking changes contains a lot of useful information and insight: http://www.tm1forum.com/viewtopic.php?f ... ion#p61609

TM1 Transfer was very buggy and temperamental when I did some testing on it, however, this was many moons ago so things could have improved.

In terms of a free tool, Marius Wirtz has a lot of functionality already completed with the REST API and python. I've been meaning to get to grips with it for some time and contribute but work/ life has been getting in the way. It is well worth a look

https://github.com/MariusWirtz/TM1py

User avatar
mce
Community Contributor
Posts: 260
Joined: Tue Jul 20, 2010 5:01 pm
OLAP Product: Cognos TM1
Version: TM1 10.2
Excel Version: 2010 2013
Location: Istanbul, Turkey

Re: Is it still worth working on a cube auto promotion tool?

Post by mce » Fri Jul 14, 2017 3:35 pm

Hi Julian,

I never copy cubes from one environment to the other. Because I do not need to.
I only copy the updated "code" files from one environment (Dev) to the other one (Test/Prod).
The "code" builds the cube and the necessary dimensions by itself.
Indeed, there should not be a need to copy cube or dimension objects (other than restoring from backup). They are effectively data or metadata, which must be unique to each environment. In Prod, you have prod data, in Test you would have test data. This logic makes it easy to deploy changes from one environment to the other one.
Hence you never need to copy cubes or dimensions. You only copy your updated or new code objects.

Start by separating Code, Config, Data and Logs in your TM1 data server. By default, they are all in the same folder, but you can separate. You can define multiple databasedirectories in your TM1s.cfg file allowing you to have separate ones for TM1Data, TM1Config, and TM1Code. Then, you only need to move your TM1Code folder from one environment to the other one.

Code: Select all

ServerName=FRS
DataBaseDirectory=D:\TM1\FRS\TM1Data;D:\TM1\FRS\TM1Config;D:\TM1\FRS\TM1Code
LoggingDirectory=D:\TM1\FRS\TM1Logfiles
RawStoreDirectory=D:\TM1\FRS\TM1Logfiles
  • TM1Config folder will hold objects specific to an environment, such as security objects and environment config files, including tm1s.cfg file. You may need to have seperate admins for each environment. Hence this folder is never copied from one environment to the other one.
    TM1Code folder will hold all *.pro file you created, rule files in *.TXT format, and any *.csv files that define structure of measure dimensions and non-changing dimensions. This folder will have all the necessary processes and code objects to create all cubes and dimensions from scratch using a main Rebuild process. This is the folder you keep committing to your code repository such as Subversion.
    TM1Data folder will hold all remaining TM1 objects, including cube file, and dim files. This folder effectively holds your cube data and meta-data. You should never need to copy this folder from one environment to the other one.
I also use separate folders in the same parent folder such as:
  • TM1MasterDataFiles folder holds all master data maintenance files in CSV format.
    TM1SourceFiles folder holds all data files that you import into TM1 cubes. It may have subfolders as needed.
    TM1ExportFiles folder holds your data files exported from TM1 model for any purpose.
..etc.

Regards,
TM1ModelFolderStructure.JPG
TM1 Model Folder Structure
TM1ModelFolderStructure.JPG (14.37 KiB) Viewed 714 times

User avatar
JulianS
Posts: 20
Joined: Fri Jul 07, 2017 1:34 pm
OLAP Product: TM1
Version: 10.3
Excel Version: 2013

Re: Is it still worth working on a cube auto promotion tool?

Post by JulianS » Fri Jul 14, 2017 4:19 pm

Cheers Guys,

I'm gonna leave the VBA alone, and have a dabble with something more current like, the Python / REST stuff / Java etc.

I've not had a chance to do anything with the new interfaces, just the old TM1 API from years ago.

Thanks MCE, wow! Thats really cool, all the servers I've ever used have everything dumped In one folder so I'll have a look at that.

Just out of interest MCE is TM1 intelligent enough to move all the files into their respective folders from the one folder containing everything, if I down the service, then change config, then restart it?

Wim Gielis
MVP
Posts: 1517
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Is it still worth working on a cube auto promotion tool?

Post by Wim Gielis » Fri Jul 14, 2017 4:26 pm

JulianS wrote:I'm gonna leave the VBA alone,
Is it a treeview that you use in the Userform ?
That object does not play nicely in several versions of Excel, or Excel 64 bits.
I know that Excel MVP Jan-Karel Pieterse offers a free alternative with classes:
http://www.jkp-ads.com/articles/treeview.asp
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 103 TM1 articles and a lot of custom code
Newest blog article: TM1 message log analysis with Power Query

User avatar
JulianS
Posts: 20
Joined: Fri Jul 07, 2017 1:34 pm
OLAP Product: TM1
Version: 10.3
Excel Version: 2013

Re: Is it still worth working on a cube auto promotion tool?

Post by JulianS » Fri Jul 14, 2017 5:17 pm

Hi Wim,

Yes its a standard tree view from the Microsoft add ins, and I did have quite a few issues with it, when I upgraded excel. :D

It was originally written before we even had ribbons in excel. (2003 I think).

You can tell how old it is by the icons, I think they were from the Applix days! (ahh, the memories, 3GB of ram for the service top, 32 bit server application good days)

Thanks for the info about the new Tree view, it does look a lot better than the standard MS version. Its FREE and works with Mac! (Which is always a bonus) :)

Kind Regards

Jules

User avatar
paulsimon
MVP
Posts: 548
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: 10.1.1 and 10.2.2
Excel Version: 2013

Re: Is it still worth working on a cube auto promotion tool?

Post by paulsimon » Fri Jul 14, 2017 9:49 pm

Hi

That is a nice looking utility. Presumably you can decide which of the related objects you want to promote? One of the downsides of a number of tools that work out all the connections is that they assume that you will always want to promote all the related objects, which isn't always the case.

I have a utility that generates the TI Script needed to create a cube and all the views on it. Some people say that you should always generate everything from code, but I find that this gives me the convenience of being able to create a view using the cube viewer which is much quicker than writing code, and then have the utility generate the code, which can be promote and amended if necessary.

In general I wouldn't promote the .cub since you would not generally want Dev data going into Prod, unless it is a first release and it is just a reference cube, but there are always exceptions to this.

We still tend to do most promotions overnight, since we don't generally want things changing in the middle of the day. The overnight utility copies in the required files, restarts the service and the start up chore can trigger can import a list of processes to be run to complete the promotion. For example, promoting a new Dim Build Process is all very well, but you will generally need a way to run it, after the promotion. We normally promote to a test environment first to ensure that the promote works correctly before going to Prod.

I think that the Rest API is probably the way to go for live promotions.

Regards

Paul Simon

User avatar
mce
Community Contributor
Posts: 260
Joined: Tue Jul 20, 2010 5:01 pm
OLAP Product: Cognos TM1
Version: TM1 10.2
Excel Version: 2010 2013
Location: Istanbul, Turkey

Re: Is it still worth working on a cube auto promotion tool?

Post by mce » Wed Jul 19, 2017 9:43 am

JulianS wrote:
Fri Jul 14, 2017 4:19 pm
Just out of interest MCE is TM1 intelligent enough to move all the files into their respective folders from the one folder containing everything, if I down the service, then change config, then restart it?
No. But you can move the objects to proper folders and they will stay there. For moving .pro files from TM1Data folder to TM1Code folder, it does not require server restart. After you move, if you change the process, it will update the file under new location.

TM1Config folder will have minimal number number of objects, you will move once while server is stopped and then you will not need to do anything further.

After you set the folders and move the objects to relevant folders, the only ongoing thing you will need to do during development process is moving new .pro files from TM1Data folder (default folder of all newly created objects) to TM1Code folder without restarting the TM1 server.

User avatar
JulianS
Posts: 20
Joined: Fri Jul 07, 2017 1:34 pm
OLAP Product: TM1
Version: 10.3
Excel Version: 2013

Re: Is it still worth working on a cube auto promotion tool?

Post by JulianS » Sun Jul 23, 2017 7:52 pm

Cheers MCE, thats fantastic

There's always something new to learn on here, and seeing how other people tackle the problems in completely different ways.

I'm going to give that a go on our test server.

Jules
:)

User avatar
JulianS
Posts: 20
Joined: Fri Jul 07, 2017 1:34 pm
OLAP Product: TM1
Version: 10.3
Excel Version: 2013

Re: Is it still worth working on a cube auto promotion tool?

Post by JulianS » Sun Jul 23, 2017 8:33 pm

Hi Paul,

Yes you can choose multiple cubes and it will pick up all the stuff required.

Its history

It was originally written in PERL as a script but it became too cumbersome (one cube at a time) so I re-wrote it in VBA:

It was very handy for us as originally, we had three completely different systems in production, each system ran independently of the other.

The company frequently re-organised itself, and our development system wasn't very good ( I think it was a Pentium 2 with about 4Gb of ram).

We used this to generate a development copy of part of the prod system to work on, and the subsequent mapping TI's from prod with tons of changes rules etc as the business changed).

This then went to a UAT system (which was then a back to back with prod at year end, but in the re-orged hierarchies and fully reconciled with the help of the accounting teams)

The Current system:

The system also has extra functionality to produce a spreadsheet of all items promoted (just for Audit), and a file state check.

So not only did we have a system backup, any files were that were targeted to be overwritten on the target system had the original files copied into a separate rollback folder. (as a belt and braces approach).


But time marches on:

I've not changed it really in years except for, as Wim says the tree view broke as it doesn't play nice with different versions of excel, and therefore needed tweaking.

It worked but it it never felt polished. (and things like drop down pick lists of dimensions that weren't part of the main cube I never got round to)

I think i'm going to have a go at MCEs way of doing it, then have a nice REST :D

Cheers Paul

Jules

Post Reply