TM1 Forum

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.

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.

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

Thank, Alan.

(Self-administered dope-slap to forehead.)

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.
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.

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