TM1 Forum

Posted: **Thu Jul 08, 2010 4:32 pm**

Hello,

I'm trying to automate the daily upload of a csv file that is emailed by Google Analytics, and am having issues with the data format, it seems. I have two processes - one called eMediaFileSearch, which searches for the correct file according to today's date (the files are dumped daily by GA into a folder), and passes the date parameter onto the second process (called eMedia Stats Upload), which is supposed to pull the data into the eMediaStatsDaily Cube. The process says it finished executing normally in the Message Log. However, this is NO DATA in the cube. Here is my code from the Prolog of the eMediaFileSearch process:

Code: Select all

#****GENERATED STATEMENTS START****
#****GENERATED STATEMENTS FINISH****

# Path to the data files; this could be picked up from a control cube instead,
# but let's keep it simple.
SC_PATH = '\\Potfile01\Acctg$\Jennifer G\eMedia TM1 Uploads\';

s_Today = (timst(DayNo(Today(1)),'\Y\m\d') );

#s_BusUnit1 = 'aviationtoday';
#s_BusUnit2 = 'cablefax';




    s_FileName1 = 'Analytics_aviationtoday_' | s_Today | '_(aviationtoday com_Daily_GA_Report).csv';

    s_FileName2 = 'Analytics_cablefax_' | s_Today | '_(cablefax com_Daily_GA_Report).csv';



    If ( FileExists ( SC_PATH | s_FileName1 ) = 1);
# Found a file by that name. This is where we do the main code.
# This is done by creating a process to process the file,
# creating a parameter to receive the file name,
# then changing the data source to that file name.
# In this example z080821B is the name of the other process.

        ExecuteProcess('eMediaStatsUpload', 'FileName', SC_PATH | s_FileName1,'pBusUnitUpload', '2422');



ElseIf ( FileExists ( SC_PATH | s_FileName2 ) = 1);
 ExecuteProcess('eMediaStatsUpload', 'FileName', SC_PATH | s_FileName2, 'pBusUnitUpload', '2301');
#No more files found, exit the loop.

EndIf;

     

l_RetVal = ExecuteProcess('eMediaStatsUpload', 'FileName', SC_PATH | s_FileName1);
if(l_RetVal = ProcessExitSeriousError() );

ASCIIOutput('\\Potfile01\Acctg$\Jennifer G\eMedia TM1 Uploads\Error Messages\process_return_values.txt', 'Process exited with serious errors at', TIME
, 'on', TODAY);


EndIf;

And here is my code from the eMediaStatsUpload process:

Prolog tab:

Code: Select all

#****GENERATED STATEMENTS START****
OldCubeLogChanges = CUBEGETLOGCHANGES('eMediaStatsDaily');
CUBESETLOGCHANGES('eMediaStatsDaily', 0);
#****GENERATED STATEMENTS FINISH****


DatasourceNameForServer=FileName;

#StringGlobalVariable('vBusUnitUpload');
#vBusUnitUpload = pBusUnitUpload;	
#vBusUnit = vBusUnitUpload;

Metadata tab:
Note that I couldn't get the process to compile using the raw data from the file - I had to use the numbr function.

Code: Select all

#****GENERATED STATEMENTS START****
vYear=SubsetGetElementName('Years','Current Year',1);
vDate = Timst(DayNo(Today),'\m') | '/'| Timst(DayNo(Today),'\d');
vBusUnit='2422';
v_NewVisits=numbr(v5);
v_AvgPageviews=numbr(v7);
v_BounceRate=numbr(v8);
v_Visits=numbr(visits);
v_UnqVisitors=numbr(vUniqueVisitors);
v_Pageviews=numbr(Pageviews);
#****GENERATED STATEMENTS FINISH****



#vBusUnit = vBusUnitUpload;

#vBusUnit=2422;

Data Tab:

Code: Select all

#****GENERATED STATEMENTS START****
vYear=SubsetGetElementName('Years','Current Year',1);
vDate = Timst(DayNo(Today),'\m') | '/'| Timst(DayNo(Today),'\d');
vBusUnit='2422';
v_NewVisits=numbr(v5);
v_AvgPageviews=numbr(v7);
v_BounceRate=numbr(v8);
v_Visits=numbr(visits);
v_UnqVisitors=numbr(vUniqueVisitors);
v_Pageviews=numbr(Pageviews);
CellPutS(vTimeOnSite,'eMediaStatsDaily',vBusUnit,vDate,vYear,'Time on Site');
CellPutN(v_NewVisits,'eMediaStatsDaily',vBusUnit,vDate,vYear,'New Visits');
CellPutN(v_AvgPageviews,'eMediaStatsDaily',vBusUnit,vDate,vYear,'Average Pageviews');
CellPutN(v_BounceRate,'eMediaStatsDaily',vBusUnit,vDate,vYear,'Bounce Rate');
CellPutN(v_Visits,'eMediaStatsDaily',vBusUnit,vDate,vYear,'Visits');
CellPutN(v_UnqVisitors,'eMediaStatsDaily',vBusUnit,vDate,vYear,'Absolute Unique Visitors');
CellPutN(v_Pageviews,'eMediaStatsDaily',vBusUnit,vDate,vYear,'Pageviews');
#****GENERATED STATEMENTS FINISH****



ASCIIOutput('\\Potfile01\Acctg$\Jennifer G\eMedia TM1 Uploads\Error Messages\eMediaOutput.cma',  vDate, vBusUnit, vYear,Visits,vUniqueVisitors,
PageViews,vTimeOnSite,v5,v7,v8);

I also have two parameters set in the Paremeters tab, although the business unit parameter is being overrident by a hard value right now until I can get the upload issue resolved.

Any idea why the process may not be recognizing the data? In my cube, all the variables are number except for the Time on Site, which is string.

Thanks for any help!
Jennifer

Posted: **Fri Jul 09, 2010 12:46 pm**

Hi Jennifer,
what is written in the ASCIIOUTPUT-file eMediaOutput.cma?

You may keep programming simpler in the development stage. Take only some source rows as input and no errorhandling. If you run on an error, TI gives you the error and you know where to search. Always use ASCIIOUTPUT to check element names.

Marcus

Posted: **Fri Jul 09, 2010 3:20 pm**

Thanks for your response, Marcus. Here is what is written in the ascii file:

07/08,"2422","2010","5795","5251","12001","11:32:10 PM","0.581018119","2.07092321","0.71285591". Looks right to me!

I will try taking the error processing out and see what happens.

Thanks,
Jennifer

Posted: **Fri Jul 09, 2010 3:57 pm**

Hi Jennifer,

Some other things to check.

The processed successfully message is not 100% reliable, it's worth double checking the logging directory / message log for any error messages. This is especially true if you are using Execute process to call a second sub.

You only list 1 line as a result of the ASCII output, I'm assuming there are a lot more?

Try putting the ascciioutput into Excel and trying to send the values in with a DBS, this might through up an issue.

You have the variables defined in both the data and metadata tab in the wizard but you are not doing any work in the metadata. In the wizard you can set the vars to be only valid in the Data tab, this will save you some processing time.

HTH

Posted: **Fri Jul 09, 2010 5:39 pm**

Thanks Steve. I was able to figure out the problem as you suggested, using the dbs formula. Wouldn't you know it, it turned out to be a transcribed number.

I am also trying to use the result of my pBusUnitUpload parameter to define one of my variables, but when I try it like this in the Variables tab:

Code: Select all

vBusUnit=|pBusUnitUpload|;

I get this error: "Line 1: Syntax error on or before: |pBusUnitUpload|; invalid expression"

Is what I'm trying to do even possible?

Thanks,
Jennifer

Posted: **Fri Jul 09, 2010 6:33 pm**

Hi Jennifer,

The pipe symbol is used to concatenate strings in TI processes so what you have written doesn't make sense.

If you just want to assign the value of one variable to another then you can just use the = sign.

If you need to test for equivalence on string expressions then you need to use @=

Anyway
vBusUnit=pBusUnitUpload;
Is probably what you need.

You'll need to make sure you aren't assigning a numeric to a string since TI varaibles are type sensitive.
Once you've you had a bit of practice you'll want to drop the wizard as it's pretty constraining.

HTH

Cheers,

Posted: **Fri Jul 09, 2010 6:46 pm**

That worked! Thanks so much!

Jennifer

TM1 Forum

Upload csv file from Google Analytics

Upload csv file from Google Analytics

Re: Upload csv file from Google Analytics

Re: Upload csv file from Google Analytics

Re: Upload csv file from Google Analytics

Re: Upload csv file from Google Analytics

Re: Upload csv file from Google Analytics

Re: Upload csv file from Google Analytics