Advanced Parameters in Turbo Integrator

[Guang Ming]

Hi guys... Im now struggling using tm1 process using advanced parameters tab. I need to import data from oracle data source to cube. Data source Query result shows some variables, one of them has 'Year' variable. Query result shows 'year' : 2008, 2009, 2010, etc. Now Im thinking how to make a year parameter, so I need to create paramater in tab advanced, then parameters, then I insert new parameter, pYear. On Data tab, I write it : pYear = vYear; It caused when I run the process, dialog box popped up and ask me to insert the year. so when I insert year : 2008, only data with year 2008 will be imported to cube. The problem is, even though I tried to insert one of year like 2008, data still imported for all years. Can you help me to figure this out, how can I just filter one of the name of the year? Any guide would help me a lot, sorry for poor english...

[Michel Zijlema]

Hi,

You need to pass the parameter value to your SQL Query. See f.i. this post.

Michel

[Alan Kirk]

Hi guys... Im now struggling using tm1 process using advanced parameters tab. I need to import data from oracle data source to cube. Data source Query result shows some variables, one of them has 'Year' variable. Query result shows 'year' : 2008, 2009, 2010, etc. Now Im thinking how to make a year parameter, so I need to create paramater in tab advanced, then parameters, then I insert new parameter, pYear. On Data tab, I write it : pYear = vYear; It caused when I run the process, dialog box popped up and ask me to insert the year. so when I insert year : 2008, only data with year 2008 will be imported to cube. The problem is, even though I tried to insert one of year like 2008, data still imported for all years. Can you help me to figure this out, how can I just filter one of the name of the year? Any guide would help me a lot, sorry for poor english...

"Parameters, yur doing it wrong."

The place that you need to assign the year parameter is in the SQL statement that you use to query the results from Oracle. That would look something like this:

Code: Select all

select	COMPANY
,	ACCOUNT
,	DEPT
,	FISCAL_YEAR
,	ACCOUNTING_PERIOD
,	POSTED_AMT
from	YOURTABLE

This query, having no Where clause, would suck out all of the rows from that table. Accordingly you need to change the query to accept a parameter from you, like this:

Code: Select all

select	COMPANY
,	        ACCOUNT
,	        DEPT
,	        FISCAL_YEAR
,	        ACCOUNTING_PERIOD
,	        POSTED_AMT
from	        YOURTABLE
where	FISCAL_YEAR = ?pYear?

Note the item with question marks on either side of it. That's where we'll substitute your parameter so that it pulls ONLY the values for the year that you've specified.

If you are entering the parameter into the process' dialog, that's it, you're done. But if you're running the process as part of a chore and are picking up the year from, say, a control cube so that you don't have to manually enter it, you assign the value that you've read from the control cube not in the Data tab, but in the Prolog tab.

The prolog tab is run before any data is read. Supposing that you enter 2008 as the year, then what will happen is that:
- The Prolog tab will run; and
- The SQL code will be changed to read

Code: Select all

select	COMPANY
,	        ACCOUNT
,	        DEPT
,	        FISCAL_YEAR
,	        ACCOUNTING_PERIOD
,	        POSTED_AMT
from	        YOURTABLE
where	FISCAL_YEAR = 2008

THEN the query above will be run from Oracle. In that way you will receive only the 2008 year's records.

I note from the e-mail alerts that Michel beat me to this one but since I'm already this far through mine I'll post it anyway.

[Guang Ming]

Cool... It works for me... Thanks a lot... Here is what I've made :

select columnA, columnB, columnC
from mytable
where columnC = '?pYear?'

variables are : vColumnA, vColumnB, vColumnC

then I go to parameter tab and insert new one.

On Data tab, I put CellPutN(vcolumnB, 'cubeName', vColumnA, vColumnB, pYear);

note: vColumnA and vColumnB is dimension for A and B.

Hopefully I do a right thing and it works too for us..

Thanks again for sharing.

[Alan Kirk]

Cool... It works for me... Thanks a lot... Here is what I've made :

select columnA, columnB, columnC
from mytable
where columnC = '?pYear?'

variables are : vColumnA, vColumnB, vColumnC

then I go to parameter tab and insert new one.

On Data tab, I put CellPutN(vcolumnB, 'cubeName', vColumnA, vColumnB, pYear);

note: vColumnA and vColumnB is dimension for A and B.

I can't see that working.

You say that vColumnB is the name of a dimension (dimension element, I would hope you meant). That's fine, that's not a problem.

The problem is that for it to be an element name it would need to be a string.

However the value that you are passing to the CellPutN function is also vcolumnB. For that to work, it would need to be a numeric value (since CellPutN only works with those) but if it's numeric, then obviously it can't be a string which can be used as an element name. The reverse is also true.

The other thing is that I really wouldn't put pYear as the year dimension argument. It should work, but it's potentially misleading. You're getting the year back as vColumnC, and I think you should really use that.

Shouldn't it always be the same, I hear you ask?

Well, yes, it should... but I'd be just very wary about using a value that is not being supplied by the data source. If somehow (and no, I grant you that I can't think of how) but if somehow you get a row of data coming through with a bad year then you'd never detect it that way. The CellPutN would be using the parameter value and would think that everything is fine, when it's not. It's really only a "belts and braces" precaution.

But the issue regarding vColumnB is not; it can be either a number or a string, but it can't be both.

[Guang Ming]

Hi Alan Kirk...

Sorry for long response..

Yeah, that is what I meant.. It's so clear explanation man. Thanks again for your help...