define data source in TI code

Post Reply
User avatar
Carolyn
Posts: 30
Joined: Thu Jan 21, 2010 10:27 pm
OLAP Product: TM1
Version: 10.1
Excel Version: 2010
Location: Los Angeles, California

define data source in TI code

Post by Carolyn »

I'm willing to bet there is no good way to do what I want to do.

I want to define the data source in the TI code.

I have cubes named Sales2010 Sales2011 Sales2012 etc.

Salesyyyy is the datasource. The cubes are identical in terms of dimensions and elements. I would like to define the year as a parameter and then write code that defines 'Sales'|year to be the datasource.

Can this be done "easily"? If it's not straightforward, then I will go to Plan B, which is simply copy the process at the beginning of the year and edit the datasource.
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: define data source in TI code

Post by Alan Kirk »

Carolyn wrote:I'm willing to bet there is no good way to do what I want to do.

I want to define the data source in the TI code.

I have cubes named Sales2010 Sales2011 Sales2012 etc.

Salesyyyy is the datasource. The cubes are identical in terms of dimensions and elements. I would like to define the year as a parameter and then write code that defines 'Sales'|year to be the datasource.

Can this be done "easily"? If it's not straightforward, then I will go to Plan B, which is simply copy the process at the beginning of the year and edit the datasource.
Take a look in the Reference Guide under TurboIntegrator Local Variables. Specifically:
DatasourceNameForServer (name of the cube); and
DatasourceCubeview (name of the view).

Both are assigned in the Prolog.
"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
Carolyn
Posts: 30
Joined: Thu Jan 21, 2010 10:27 pm
OLAP Product: TM1
Version: 10.1
Excel Version: 2010
Location: Los Angeles, California

Re: define data source in TI code

Post by Carolyn »

DatasourceNameForServer (name of the cube); and
DatasourceCubeview (name of the view).
Thank, Alan.

(Self-administered dope-slap to forehead.)
User avatar
Carolyn
Posts: 30
Joined: Thu Jan 21, 2010 10:27 pm
OLAP Product: TM1
Version: 10.1
Excel Version: 2010
Location: Los Angeles, California

Re: define data source in TI code

Post by Carolyn »

I thought I'd flesh this out a bit in case someone comes in with the same question and the same "beginner" status that I have.

The datasourcenameforserer and datasourcecubeview in the Prolog essentially take precedence over a datasource and view defined in the first tab of Turbo Integrator. I also added datasoucetype = 'view'; before defining the new datasource.

To be more specific:

I have cloned cubes: Sales2009, Sales2010, Sales2011, etc. They have exactly the same structure, same dimensions, etc. Each has a view called ITD. (Inception to date)

I wrote a TI script that worked just great with Sales2011 in the TI datasource tab. Debugged, everything works great.

Next I wanted to run this script from an action button in Excel. The Excel spreadsheet has a user-filled cell called "First of the month". From that one cell, I have formulas to calculate the current year and other things. Thus, there is a cell named "cyear" which is now filled with 2011, thus my TI process should work from the Sales2011 cube as the datasource. Next January, it will say "2012" and I will want my process to calculate from Sales2012 as the datasource.

In the Parameters section of TI, I defined a string variable called "curryear". Then, in the Data portion of the script, I replaced everything that said 'Sales2011' with 'Sales'|curryear. Thus, next year, that will return Sales2012.

As Alan correctly informed me, I needed to put the following code into the Prolog section.

Code: Select all

Datasourcetype = 'view';
Datasourcenameforwerver = 'Sales'|curryear;
Datasourcecubeview = 'ITD';
Once that is all done, I am ready to roll. If I run the process from Perspectives (right-click, Run), it will ask me for the value of curryear. I type in 2011 or 2012, and the process runs with Sales2011 or Sales2012 as the datasource.

In addition, I made an action button in the Excel spreadsheet. In defining the properties of the action button, I referred the curryear parameter to the Excel named range cyear. Thus, when I run the process by clicking the action button, it will look at cyear, grab the year, then use the proper data source depending on the initializing year.

A final note. I can be so dumb. When I was entering the named range into the action button properties, it took me a good long time to figure out that just writing =cyear (the Excel range name) in the little dialog box does the trick. I tried using the spreadsheet navigator and that would only enter cell addresses. Not good, if you think you might insert rows or columns. Finally after much trial and error and stupidity on my part, I just typed =cyear and that was it. It was WAY simpler than I thought.
User avatar
Martin Ryan
Site Admin
Posts: 1988
Joined: Sat May 10, 2008 9:08 am
OLAP Product: TM1
Version: 10.1
Excel Version: 2010
Location: Wellington, New Zealand
Contact:

Re: define data source in TI code

Post by Martin Ryan »

For those who are looking, there's a more detailed example of building a source view on the fly in this post: http://www.tm1forum.com/viewtopic.php?f=21&t=1767
Please do not send technical questions via private message or email. Post them in the forum where you'll probably get a faster reply, and everyone can benefit from the answers.
Jodi Ryan Family Lawyer
Post Reply