HELP: ASCIIOUTPUT issues
-
- Regular Participant
- Posts: 226
- Joined: Thu Apr 02, 2009 2:51 pm
- OLAP Product: IBM Planning Analytics
- Version: Latest version
- Excel Version: 2003 to 2019
HELP: ASCIIOUTPUT issues
Hi All,
I ran into some problems while trying to use TI to Ascioutput data from a cube. Here’s the details:
• The cube has 5 dimensions, including 1 measure dim.
• The measure dim has 10 elements, a mixture of S and N.
• Out of the 10 elements, I need to export data from the 5 elements, also a mix of S and N.
• The condition to include in the export is a Status measure element. If Status is “sendingâ€, then include in file, else itemskip.
• This is how I want each line of the text file to look like, across columns:
o Column A: Dim A
o Column B: Dim C
o Column C: Measure Element 1
o Column D: Measure Element 2
o Column E: Measure Element 3
o Column F: Measure Element 4
o Column G: Measure Element 5
• I wrote the Asciioutput as follows:
o ASCIIOUTPUT(filename, dim A, dim C, vValue) where vValue is a variable that points to the data of the measures.
• Looking at the code, I know something is wrong. True enough, the export file ends up having Dim A and Dim C repeated over 5 rows, the 3rd column of each row contains the data of each of the 5 measure elements. This has to do with how TI reads the codes (i place this code in Metadata tab) as it loops from 1 measure element to another, thus treating each as a row entry.
• I tried to write the code this way but TI prevents me from saving, prompting a string error:
o ASCIIOUTPUT(filename, dim A, dim C, elt1, elt2, elt3, elt4, elt5) where each “elt†is a CellGetS or CellGetN of the actual measure element that corresponds to the dimensions.
• What should I do to achieve the desired result?
• Also, I notice if I open the export file using Notepad, each cell is encased in double quotes.
• In addition, numeric elements will end with a “.â€, e.g. “550.â€.
• If the receiving system insists on removing the double quotes and “.â€, how should I do it?
Many thanks!
Regards,
Harry
I ran into some problems while trying to use TI to Ascioutput data from a cube. Here’s the details:
• The cube has 5 dimensions, including 1 measure dim.
• The measure dim has 10 elements, a mixture of S and N.
• Out of the 10 elements, I need to export data from the 5 elements, also a mix of S and N.
• The condition to include in the export is a Status measure element. If Status is “sendingâ€, then include in file, else itemskip.
• This is how I want each line of the text file to look like, across columns:
o Column A: Dim A
o Column B: Dim C
o Column C: Measure Element 1
o Column D: Measure Element 2
o Column E: Measure Element 3
o Column F: Measure Element 4
o Column G: Measure Element 5
• I wrote the Asciioutput as follows:
o ASCIIOUTPUT(filename, dim A, dim C, vValue) where vValue is a variable that points to the data of the measures.
• Looking at the code, I know something is wrong. True enough, the export file ends up having Dim A and Dim C repeated over 5 rows, the 3rd column of each row contains the data of each of the 5 measure elements. This has to do with how TI reads the codes (i place this code in Metadata tab) as it loops from 1 measure element to another, thus treating each as a row entry.
• I tried to write the code this way but TI prevents me from saving, prompting a string error:
o ASCIIOUTPUT(filename, dim A, dim C, elt1, elt2, elt3, elt4, elt5) where each “elt†is a CellGetS or CellGetN of the actual measure element that corresponds to the dimensions.
• What should I do to achieve the desired result?
• Also, I notice if I open the export file using Notepad, each cell is encased in double quotes.
• In addition, numeric elements will end with a “.â€, e.g. “550.â€.
• If the receiving system insists on removing the double quotes and “.â€, how should I do it?
Many thanks!
Regards,
Harry
Planning Analytics latest version, including Cloud
-
- MVP
- Posts: 3113
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: HELP: ASCIIOUTPUT issues
Hello Harry
For creating the correct output, loop ONLY through the Status measure element, as far as the measures dimension is concerned.
From there on, you can piece together the string containing what you would like to output (the 5 elements).
Use CellGetN's and CellGetS's to get to the other elements.
The Roundp function could also be useful.
Also note that Turbo Integrator contains functions to format the output in the text file. Consider the DatasourceASCIIQuoteCharacter function as well as other DatasourceASCII[xxx] functions.
Wim
For creating the correct output, loop ONLY through the Status measure element, as far as the measures dimension is concerned.
From there on, you can piece together the string containing what you would like to output (the 5 elements).
Use CellGetN's and CellGetS's to get to the other elements.
The Roundp function could also be useful.
Also note that Turbo Integrator contains functions to format the output in the text file. Consider the DatasourceASCIIQuoteCharacter function as well as other DatasourceASCII[xxx] functions.
Wim
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Re: HELP: ASCIIOUTPUT issues
Hi Harry,
Did you get this to work? If so, could you please post the code? I'm trying to do the same thing, except simpler, because I just want to export 1 dimension, and all the measures associated. (It's a very simple cube that shouldn't really be a cube!).
Wim, can you possibly help out?
Thanks
Did you get this to work? If so, could you please post the code? I'm trying to do the same thing, except simpler, because I just want to export 1 dimension, and all the measures associated. (It's a very simple cube that shouldn't really be a cube!).
Wim, can you possibly help out?
Thanks
- 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: HELP: ASCIIOUTPUT issues
It would have been useful to know the exact structure of the cube; number of dimensions and data type for a start.Misty wrote:Did you get this to work? If so, could you please post the code? I'm trying to do the same thing, except simpler, because I just want to export 1 dimension, and all the measures associated. (It's a very simple cube that shouldn't really be a cube!).
Working off the assumption that you have:
* One dimension (the one that you describe as wanting to "export"),
* A second dimension which contains your measures (which you want output into a single row of data), and
* Perhaps an unknown number of other dimensions each of which has a "Total" element,
you could approach this as follows (Prolog tab):
- Use the DimSiz function to get the size of the dimension that you want to export.
- Use the same function to get the size of your measures dimension.
- Use a While loop to iterate through the elements of the dimension that you want to export. Use the DimNm function to get the name of the element.
- Inside that While loop, use a second loop to iterate through the members of the measures dimension. Again, use DimNm to get the element name.
- For each element of that dimension, use a CellGetN or CellGetS function (as the case may be) to retrieve the value from the cube for the current element of the export dimension, the current element of the measures dimension, "Total" elements for all other dimensions. Convert that to a string using Trim() and Str(), and append it to a string variable.
- End the loop that iterates through the measures dimension.
- AsciiOutput the string variable.
- End the loop that iterates through the dimension that you're exporting.
If this isn't what you're after, some further clarification would be useful.
Edit: Corrected the function name; should be DinmNm not DimIx.
"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.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
Re: HELP: ASCIIOUTPUT issues
Hi Alan,
Thanks so much for the information. I'm very new to TM1 and don't have any experts around me.
I only have one Dimension, and it's only at leaf level. (It's a really silly, totally unsuitable way of collecting relational data from business users through Perspectives - don't ask!)
So my cube is as simple as this: 1 normal dimension with 1 measures dimension with 5 members. I've got no 'Total' element. I simply want to create a flat file structure so that I can export into an Oracle table.
One thing I've noticed from the outset is that in Turbo Integrator, shouldn't there just be 2 variables (the normal dimension and the measures dimension)? I've got something called 'Value' which is really confusing me. Actually the whole 'Variables' tab in TI is a bit confusing but I will consult the user guide. Any tips though?
I'm not much of a programmer, but I'm getting some help from someone who is (although he's never seen TM1 before) - can you think of any other info he might require?
Thanks again.
Thanks so much for the information. I'm very new to TM1 and don't have any experts around me.
I only have one Dimension, and it's only at leaf level. (It's a really silly, totally unsuitable way of collecting relational data from business users through Perspectives - don't ask!)
So my cube is as simple as this: 1 normal dimension with 1 measures dimension with 5 members. I've got no 'Total' element. I simply want to create a flat file structure so that I can export into an Oracle table.
One thing I've noticed from the outset is that in Turbo Integrator, shouldn't there just be 2 variables (the normal dimension and the measures dimension)? I've got something called 'Value' which is really confusing me. Actually the whole 'Variables' tab in TI is a bit confusing but I will consult the user guide. Any tips though?
I'm not much of a programmer, but I'm getting some help from someone who is (although he's never seen TM1 before) - can you think of any other info he might require?
Thanks again.
- 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: HELP: ASCIIOUTPUT issues
If it's a two dimensional cube it's exactly the same; you just don't worry about any other dimensions because there aren't any. I did just notice that I typed DimIx in that last post rather than DimNm; the disadvantage of rushing a reply.Misty wrote: Thanks so much for the information. I'm very new to TM1 and don't have any experts around me.
I only have one Dimension, and it's only at leaf level. (It's a really silly, totally unsuitable way of collecting relational data from business users through Perspectives - don't ask!)
So my cube is as simple as this: 1 normal dimension with 1 measures dimension with 5 members. I've got no 'Total' element. I simply want to create a flat file structure so that I can export into an Oracle table.
One cube that's simiilar to the one that you describe is the }ClientProperties cube. The general principles for exporting the content of that should be easily translatable to your own situation:
Code: Select all
# The following cube consists of two dimensions.
# The }Clients one is the one that we're exporting.
# The }ClientProperties one is effectively the "measures"
# one though in reality this is a meaningless concept in TM1.
# Just to add some spice to the mix, }ClientProperties consists
# of a mixture of string and numeric values so that we can see
# how to do both.
# We'll export the file as a tab delimited text file.
SC_CUBE = '}ClientProperties';
# Make sure that the rows of output aren't surrounded
# by double quotes.
DatasourceASCIIQuoteCharacter = '';
# How long are the two dimensions?
l_DimSizClients = DimSiz ( '}Clients');
l_DimSizClientProperties = DimSiz ( '}ClientProperties');
# Initialise the counters.
l_ClientIdx = 1;
l_PropertyIdx = 1;
# Now we loop through the clients
While ( l_ClientIdx <= l_DimSizClients );
#Get the name of the client element.
s_Client = DimNm ( '}Clients', l_ClientIdx );
# This is the variable that we'll use for output.
# We reinitialise it for every row by putting in the name
# of the client.
s_ExportRow = s_Client;
# Now iterate through the "measures".
# You have to reinitialise the counter with each loop.
l_PropertyIdx = 1;
While ( l_PropertyIdx <= l_DimSizClientProperties );
# Get the name of the "measure"
s_Property = DimNm ( '}ClientProperties', l_PropertyIdx );
# What type of element is that?
s_ElType = DType( '}ClientProperties', s_Property );
If ( s_ElType @= 'N' );
# Get the value and convert it to a string.
# The next two could be done as a single step; this is just for clarity.
l_Value = CellGetN( SC_CUBE, s_Client, s_Property );
s_Value = Trim ( Str ( l_Value, 15, 0 ) );
# Get the string value, no conversion required..
ElseIf (s_ElType @= 'S' );
s_Value = CellGetS( SC_CUBE, s_Client, s_Property );
EndIf;
# Append the measure that we've just read to the output string..
# Char(9) is a horizontal tab character to create a tab delimited string.
s_ExportRow = s_ExportRow | Char(9) | s_Value;
l_PropertyIdx = l_PropertyIdx + 1;
End;
#Write the full output string to the file.
AsciiOutput ( 'D:\TM1\Temp\Output.txt', s_ExportRow);
l_ClientIdx = l_ClientIdx+1;
End;
A value in a TM1 cube is stored at the intersection of one element from every dimension. A two dimensional cube is actually very easy to visualise; just think of an Excel worksheet.Misty wrote:One thing I've noticed from the outset is that in Turbo Integrator, shouldn't there just be 2 variables (the normal dimension and the measures dimension)? I've got something called 'Value' which is really confusing me. Actually the whole 'Variables' tab in TI is a bit confusing but I will consult the user guide. Any tips though?
A single value (say, 500) might be stored in say, cell D10. To get to that value, you need to provide two coordinates, the row (10) and the column (D). Those are like the two variables that you're describing. However they're merely the coordinates, they aren't the value. Therefore you need a third variable to tell you what the actual value is.
To get all of the information from that excel sheet you'd therefore have three variables, not two:
Column = D
Row = 10
Value = 500
Variables only come into play if you need to define (say) a cube view as a data source. In the instance above, however, you don't need to have a data source and go to the trouble of defining a view and destroying it afterwards, though you could if you really wanted to; it's just as easy to simply loop through the two dimensions.
If you have a multi-dimensional cube where you need to use zero suppression, that's a different matter.
"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.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
Re: HELP: ASCIIOUTPUT issues
Thanks again so much for your help!
For the benefit of the forum, here is the code I used. (I chose 'none' as the data source, and just had the following code in the Prolog tab)(I've changed a couple of things to make it anonymous but hopefully it will make sense still).
I know it's a hack, but the 'TM1 Dummy Row' is a dummy dimension allowing me to have all other data as elements in one 'data' dimension. This is how I've managed to defy the sensible use of TM1 to allow my users to input relational data into an OLAP tool!
I also wanted to convert the Business Effective From Date to a date (in case they put the wrong format into the cell). Also, if 'Department' or 'Business Effective From Date' were empty for any of the rows of data, I did not want to export those rows.
#****Begin: Generated Statements***
#****End: Generated Statements****
DatasourceASCIIQuoteCharacter = '';
s_TABLE_NAME = 'HIERARCHY';
SC_CUBE = 'Hierarchy';
ODBCOpen('database','schema','password');
ODBCOutput('database','delete from '|s_TABLE_NAME);
DUMMY_DIMENSION = 'Hierarchy_Dummy_Row';
l_NumRows = DimSiz (DUMMY_DIMENSION);
l_RowNum = 1;
While ( l_RowNum <= l_NumRows);
s_RowNumName = DimNm (DUMMY_DIMENSION,l_RowNum);
s_SQL = 'INSERT INTO '|s_TABLE_NAME|' (';
s_SQL = s_SQL | 'TM1_DUMMY_ROW, ';
s_SQL = s_SQL | 'DEPARTMENT, ';
s_SQL = s_SQL | 'DIVISION_SECTOR, ';
s_SQL = s_SQL | 'DIVISION, ';
s_SQL = s_SQL | 'BUSINESS_GROUP, ';
s_SQL = s_SQL | 'BUSINESS_EFFECTIVE_FROM_DATE, ';
s_SQL = s_SQL | 'DELETE_FLAG, ';
s_SQL = s_SQL | 'BATCH_ID, ';
s_SQL = s_SQL | 'SOURCE_SYSTEM_ID, ';
s_SQL = s_SQL | 'SOURCE_CREATE_DATETIME';
s_SQL = s_SQL | ')';
s_DEPARTMENT = CellGetS(SC_CUBE,s_RowNumName,'Department');
s_DIVISION_SECTOR = CellGetS(SC_CUBE,s_RowNumName,'Division_Sector');
s_DIVISION = CellGetS(SC_CUBE,s_RowNumName,'Division');
s_BUSINESS_GROUP = CellGetS(SC_CUBE,s_RowNumName,'Business Group');
s_BUSINESS_EFFECTIVE_FROM_DATE = CellGetS(SC_CUBE,s_RowNumName,'Effective From Date');
s_DELETE_FLAG = CellGetS(SC_CUBE,s_RowNumName,'Delete Flag (Y/N)');
s_SQL = s_SQL | ' VALUES (';
s_SQL = s_SQL | ''''| s_RowNumName |''',';
s_SQL = s_SQL | ''''| s_DEPARTMENT |''',';
s_SQL = s_SQL | ''''| s_DIVISION_SECTOR |''',';
s_SQL = s_SQL | ''''| s_DIVISION |''',';
s_SQL = s_SQL | ''''| s_BUSINESS_GROUP |''',';
s_SQL = s_SQL | 'to_date('''| s_BUSINESS_EFFECTIVE_FROM_DATE |''',''dd/mm/yyyy hh24:mi:ss''),';
s_SQL = s_SQL | ''''| s_DELETE_FLAG |''',';
s_SQL = s_SQL | 'NULL,';
s_SQL = s_SQL | '''27'',';
s_SQL = s_SQL | 'sysdate';
s_SQL = s_SQL | ')';
If ((s_DEPARTMENT @= '') % (s_BUSINESS_EFFECTIVE_FROM_DATE @= ''));
Else;
ODBCOutput('database',s_SQL);
Endif;
l_RowNum = l_RowNum + 1;
End;
For the benefit of the forum, here is the code I used. (I chose 'none' as the data source, and just had the following code in the Prolog tab)(I've changed a couple of things to make it anonymous but hopefully it will make sense still).
I know it's a hack, but the 'TM1 Dummy Row' is a dummy dimension allowing me to have all other data as elements in one 'data' dimension. This is how I've managed to defy the sensible use of TM1 to allow my users to input relational data into an OLAP tool!
I also wanted to convert the Business Effective From Date to a date (in case they put the wrong format into the cell). Also, if 'Department' or 'Business Effective From Date' were empty for any of the rows of data, I did not want to export those rows.
#****Begin: Generated Statements***
#****End: Generated Statements****
DatasourceASCIIQuoteCharacter = '';
s_TABLE_NAME = 'HIERARCHY';
SC_CUBE = 'Hierarchy';
ODBCOpen('database','schema','password');
ODBCOutput('database','delete from '|s_TABLE_NAME);
DUMMY_DIMENSION = 'Hierarchy_Dummy_Row';
l_NumRows = DimSiz (DUMMY_DIMENSION);
l_RowNum = 1;
While ( l_RowNum <= l_NumRows);
s_RowNumName = DimNm (DUMMY_DIMENSION,l_RowNum);
s_SQL = 'INSERT INTO '|s_TABLE_NAME|' (';
s_SQL = s_SQL | 'TM1_DUMMY_ROW, ';
s_SQL = s_SQL | 'DEPARTMENT, ';
s_SQL = s_SQL | 'DIVISION_SECTOR, ';
s_SQL = s_SQL | 'DIVISION, ';
s_SQL = s_SQL | 'BUSINESS_GROUP, ';
s_SQL = s_SQL | 'BUSINESS_EFFECTIVE_FROM_DATE, ';
s_SQL = s_SQL | 'DELETE_FLAG, ';
s_SQL = s_SQL | 'BATCH_ID, ';
s_SQL = s_SQL | 'SOURCE_SYSTEM_ID, ';
s_SQL = s_SQL | 'SOURCE_CREATE_DATETIME';
s_SQL = s_SQL | ')';
s_DEPARTMENT = CellGetS(SC_CUBE,s_RowNumName,'Department');
s_DIVISION_SECTOR = CellGetS(SC_CUBE,s_RowNumName,'Division_Sector');
s_DIVISION = CellGetS(SC_CUBE,s_RowNumName,'Division');
s_BUSINESS_GROUP = CellGetS(SC_CUBE,s_RowNumName,'Business Group');
s_BUSINESS_EFFECTIVE_FROM_DATE = CellGetS(SC_CUBE,s_RowNumName,'Effective From Date');
s_DELETE_FLAG = CellGetS(SC_CUBE,s_RowNumName,'Delete Flag (Y/N)');
s_SQL = s_SQL | ' VALUES (';
s_SQL = s_SQL | ''''| s_RowNumName |''',';
s_SQL = s_SQL | ''''| s_DEPARTMENT |''',';
s_SQL = s_SQL | ''''| s_DIVISION_SECTOR |''',';
s_SQL = s_SQL | ''''| s_DIVISION |''',';
s_SQL = s_SQL | ''''| s_BUSINESS_GROUP |''',';
s_SQL = s_SQL | 'to_date('''| s_BUSINESS_EFFECTIVE_FROM_DATE |''',''dd/mm/yyyy hh24:mi:ss''),';
s_SQL = s_SQL | ''''| s_DELETE_FLAG |''',';
s_SQL = s_SQL | 'NULL,';
s_SQL = s_SQL | '''27'',';
s_SQL = s_SQL | 'sysdate';
s_SQL = s_SQL | ')';
If ((s_DEPARTMENT @= '') % (s_BUSINESS_EFFECTIVE_FROM_DATE @= ''));
Else;
ODBCOutput('database',s_SQL);
Endif;
l_RowNum = l_RowNum + 1;
End;
-
- Posts: 52
- Joined: Thu Feb 17, 2011 2:52 pm
- OLAP Product: TM1
- Version: 9.5
- Excel Version: 2007
Re: HELP: ASCIIOUTPUT issues
Hello,
I read through this post and it has been very helpful to me in developing and modifying my ODBCOUTPUT to a SQL Table. I have leverage both Alan and Misty's code to develop my code. However, I am still having trouble with writing all the cube data to the SQL table. My source cube currently has two entries that I expect would produce two unique records in the SQL table. I am receiving an error from from Turbo Integrator
Desi.
I read through this post and it has been very helpful to me in developing and modifying my ODBCOUTPUT to a SQL Table. I have leverage both Alan and Misty's code to develop my code. However, I am still having trouble with writing all the cube data to the SQL table. My source cube currently has two entries that I expect would produce two unique records in the SQL table. I am receiving an error from from Turbo Integrator
. Below is the code that I am using. Any help or advice will be greatly appreciated.Error: Prolog procedure line (60): Invalid key: Dimension Name: "SNO_Model", Element Name (Key): ""
Desi.
Code: Select all
[b]Prolog tab[/b]
#****Begin: Generated Statements***
#****End: Generated Statements****
# Make sure that the rows of output aren't surrounded
# by double quotes.
DatasourceASCIIQuoteCharacter = '';
s_TABLE_NAME = ' [SNO].[dbo].[SafetyStocks] ';
SC_CUBE = ' SNO_SafetyStock1';
ODBCOpen('ODS', 'tm1svc', 'Tm!$vc');
## Truncate data on the SNO Data Model table (Storage)
vStatement1 = 'Truncate Table [SNO].[dbo].[Storage] ';
ODBCOutput('ODS',vStatement1);
vsCreateTime=TODAY(1);
Model = ''| SNO_Model|'' ;
UserID = ''| vsUserID|'';
RowID = ''| SNO_RowID|'';
# How long are the dimensions?
#l_DimSizSNO_Model = DimSiz ( 'SNO_Model');
#I_DimSizClient = DimSiz( '}Clients');
I_DimSizSNO_RowID = DimSiz( 'SNO_RowID');
#l_DimSizSNO_SS_Measures = DimSiz ( 'SNO_SafetyStock_Measures');
# Initialise the counters.
#l_SNO_Model_Idx = 1;
#I_SNO_Clients_Idx = 1;
I_SNO_RowID_Idx = 1;
#l_SNO_SS_Measures_Idx = 1;
# Loop through the SNO_RowID
While ( I_SNO_RowID_Idx <= I_DimSizSNO_RowID );
# Get the name of the SNO_RowID element.
s_SNO_RowID = DimNm ( 'SNO_RowID', I_SNO_RowID_Idx );
# INSERT INTO [SNO].[dbo].[SafetySyock] ( [Model], [ItemCode], [Location], [SSType], [Safety], [UserID], [CreateDate] )
s_SQL = 'INSERT INTO '|s_TABLE_NAME|' (';
s_SQL = s_SQL | '[Model],';
s_SQL = s_SQL | '[ItemCode],';
s_SQL = s_SQL | '[Location],';
s_SQL = s_SQL | '[SSType],';
s_SQL = s_SQL | '[Safety],';
s_SQL = s_SQL | '[UserID],';
s_SQL = s_SQL | '[CreateDate]';
s_SQL = s_SQL | ')';
s_ItemCode = CellGetS(SC_CUBE,Model, UserID, RowID, 'Item');
s_Location = CellGetS(SC_CUBE,Model, UserID, RowID,'Location');
s_SSType = CellGetS(SC_CUBE,Model, UserID, RowID,'SSType');
n_Safety = CellGetN(SC_CUBE,Model, UserID, RowID,'Safety');
s_Safety = Trim ( Str ( n_Safety, 15, 0 ) );
s_CreateTime = vsCreateTime;
#VALUES ( ''%vsModel%'', ''%ItemCode%'',''%Location%'', ''%SSType%'',%Safety%, ''%UserID%'', ''%CreateDate%'' )' ) ;
s_SQL = s_SQL | ' VALUES (';
s_SQL = s_SQL |''''|Model | ''',';
s_SQL = s_SQL |''''|s_ItemCode | ''',';
s_SQL = s_SQL |''''|s_Location | ''',';
s_SQL = s_SQL |''''|s_SSType | ''',';
s_SQL = s_SQL |''|s_Safety |',';
s_SQL = s_SQL |''''|UserID | ''',';
s_SQL = s_SQL |''''|s_CreateTime |''' ';
s_SQL = s_SQL | ')';
If ( Model @= '') ;
Else;
ODBCOutput('ODS',s_SQL);
Endif;
I_SNO_RowID_Idx = I_SNO_RowID_Idx + 1;
End;
[b]Epilog tab[/b]
#****Begin: Generated Statements***
#****End: Generated Statements****
asciioutput('temp.txt', s_SQL);
ODBCCLOSE('ODS');
-
- MVP
- Posts: 600
- Joined: Wed Aug 17, 2011 1:19 pm
- OLAP Product: TM1
- Version: 9.5.2 10.1 10.2
- Excel Version: 2003 2007
- Location: York, UK
Re: HELP: ASCIIOUTPUT issues
Hi Desi,
I am confused at the use of both s_SNO_RowID and RowID. Is this deliberate and if so what is the difference?
Cheers,
Duncan.
I am confused at the use of both s_SNO_RowID and RowID. Is this deliberate and if so what is the difference?
Cheers,
Duncan.
-
- Posts: 52
- Joined: Thu Feb 17, 2011 2:52 pm
- OLAP Product: TM1
- Version: 9.5
- Excel Version: 2007
Re: HELP: ASCIIOUTPUT issues
Duncan,I am confused at the use of both s_SNO_RowID and RowID. Is this deliberate and if so what is the difference?
There is not difference. The s_SNO_RowID is leftover code from following Misty's code that i carried forward. I can replace s_SNO_RowID with RowID.
Thanks,
Desi
-
- MVP
- Posts: 2831
- 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: HELP: ASCIIOUTPUT issues
The error message is directing you to line 60 of the Prolog tab. Have you bothered to look at the code on line 60 and see why you are passing an empty parameter for the SNO_Model dimension?
-
- Posts: 52
- Joined: Thu Feb 17, 2011 2:52 pm
- OLAP Product: TM1
- Version: 9.5
- Excel Version: 2007
Re: HELP: ASCIIOUTPUT issues
Tomok,
LINE 60 -
I did look at the referenced code in the prolog tab. See below. Why is the Cellgets statement returning a null?The error message is directing you to line 60 of the Prolog tab. Have you bothered to look at the code on line 60 and see why you are passing an empty parameter for the SNO_Model dimension?
LINE 60 -
Code: Select all
s_PenaltyCostType = CellGetS(SC_CUBE,Model, UserID, RowID, 'Cost Type');
s_ModelSelection = CellGetS(SC_CUBE,Model, UserID, RowID,'Model Selection');
s_NodeType = CellGetS(SC_CUBE,Model, UserID, RowID,'Node Type');
n_PenaltyCostValue = CellGetN(SC_CUBE,Model, UserID, RowID,'Penalty Cost');
s_PenaltyCostValue= Trim ( Str ( n_PenaltyCostValue, 15, 0 ) );
s_CreateTime = vsCreateTime;
-
- Posts: 52
- Joined: Thu Feb 17, 2011 2:52 pm
- OLAP Product: TM1
- Version: 9.5
- Excel Version: 2007
Re: HELP: ASCIIOUTPUT issues
Tomok,
Thanks for directing my attention to the CellGetS.
I changed the code to include following and it is now working. However, I do want to ensure that this is the best way of doing what I am doing. Any advise or guidance will be greatly appreciated.
Regards,
Desi
Thanks for directing my attention to the CellGetS.
I changed the code to include following and it is now working. However, I do want to ensure that this is the best way of doing what I am doing. Any advise or guidance will be greatly appreciated.
Regards,
Desi
Code: Select all
# How long are the dimensions?
l_DimSizSNO_Model = DimSiz ( 'SNO_Model');
I_DimSizClient = DimSiz( '}Clients');
I_DimSizSNO_RowID = DimSiz( 'SNO_RowID');
#l_DimSizSNO_SS_Measures = DimSiz ( 'SNO_SafetyStock_Measures');
# Initialise the counters.
l_SNO_Model_Idx = 1;
I_SNO_Clients_Idx = 1;
I_SNO_RowID_Idx = 1;
#l_SNO_SS_Measures_Idx = 1;
# Loop through the SNO_RowID
While ( I_SNO_RowID_Idx <= I_DimSizSNO_RowID );
# Get the name of the SNO_RowID element.
RowID = DimNm ( 'SNO_RowID', I_SNO_RowID_Idx );
Model = DimNm ( 'SNO_Model', l_SNO_Model_Idx );
UserID = DimNm ( '}Clients', I_SNO_Clients_Idx );
-
- MVP
- Posts: 3652
- Joined: Fri Mar 13, 2009 11:14 am
- OLAP Product: TableManager1
- Version: PA 2.0.x
- Excel Version: Office 365
- Location: Switzerland
Re: HELP: ASCIIOUTPUT issues
Desi / you're not making it easy to help. Darned difficult actually.
Your problem stems from what I assume to be line 22 when you assign the value to the string variable "Model"
Model = ''| SNO_Model|'' ;
incidentally quite bizarrely as SNO_Model wrapped in 2 empty strings (why not simply Model = SNO_Model; ?)
I would assume from the error that the value for SNO_Model is empty and therefore Model has the same fate.
So your issue must start back in the parameters as since this is the start of the prolog I assume that SNO_Model must be a string parameter.
also I assume that since Model is empty this would not only be causing all the CellGets to fail but also I presume the preceding SQL inserts would also fail due to Model not being specified ? ...
------
The above posted before seeing your last post. the reason your code is now working is simple. The variable for Model is now being set with a value!
Your problem stems from what I assume to be line 22 when you assign the value to the string variable "Model"
Model = ''| SNO_Model|'' ;
incidentally quite bizarrely as SNO_Model wrapped in 2 empty strings (why not simply Model = SNO_Model; ?)
I would assume from the error that the value for SNO_Model is empty and therefore Model has the same fate.
So your issue must start back in the parameters as since this is the start of the prolog I assume that SNO_Model must be a string parameter.
also I assume that since Model is empty this would not only be causing all the CellGets to fail but also I presume the preceding SQL inserts would also fail due to Model not being specified ? ...
------
The above posted before seeing your last post. the reason your code is now working is simple. The variable for Model is now being set with a value!
-
- Posts: 52
- Joined: Thu Feb 17, 2011 2:52 pm
- OLAP Product: TM1
- Version: 9.5
- Excel Version: 2007
Re: HELP: ASCIIOUTPUT issues
Hello All,
I need help with modifying my code below so that it exports all the UserIDs with data entered to SQL Server table. I have several UserIDs with data entered in the cube, so when I perform an Export data as Text on the cube I see all the data. The requirement is that for any user that executes the export to SQL server process will get all the records for every UserID with data entered in the cube. As always, any help, guidance, or assistance is greatly appreciated.
Thank you,
Desi
I need help with modifying my code below so that it exports all the UserIDs with data entered to SQL Server table. I have several UserIDs with data entered in the cube, so when I perform an Export data as Text on the cube I see all the data. The requirement is that for any user that executes the export to SQL server process will get all the records for every UserID with data entered in the cube. As always, any help, guidance, or assistance is greatly appreciated.
Thank you,
Desi
Code: Select all
#****Begin: Generated Statements***
#****End: Generated Statements****
# Make sure that the rows of output aren't surrounded
# by double quotes.
DatasourceASCIIQuoteCharacter = '';
s_TABLE_NAME = ' [SNO].[dbo].[BOM] ';
SC_CUBE = ' SNO_BOM1';
ODBCOpen('ODS', 'tm1svc', 'Tm!$vc');
# Truncate data on the SNO Data Model table (BOM)
vStatement1 = 'Truncate Table [SNO].[dbo].[BOM] ';
ODBCOutput('ODS',vStatement1);
vsCreateTime=TODAY(1);
Model = ''| SNO_Model|'' ;
UserID=TM1USER();
RowID = ''| SNO_RowID|'';
# How long are the dimensions?
l_DimSizSNO_Model = DimSiz ( 'SNO_Model');
I_DimSizClient = DimSiz( '}Clients');
I_DimSizSNO_RowID = DimSiz( 'SNO_RowID');
# Initialise the counters.
l_SNO_Model_Idx = 1;
I_SNO_Clients_Idx = 1;
I_SNO_RowID_Idx = 1;
# Loop through the SNO_RowID
While ( I_SNO_RowID_Idx <= I_DimSizSNO_RowID );
# Get the name of the Dimesnions element.
RowID = DimNm ( 'SNO_RowID', I_SNO_RowID_Idx );
Model = DimNm ( 'SNO_Model', l_SNO_Model_Idx );
UserID = DimNm ( '}Clients', I_SNO_Clients_Idx );
# INSERT INTO [SNO].[dbo].[BOM] ( [Model], [LocationName], [Process], [Step], [InputOutput], [ItemCode], [Quantity], [UserID], [CreateDate] )
s_SQL = 'INSERT INTO '|s_TABLE_NAME|' (';
s_SQL = s_SQL | '[Model],';
s_SQL = s_SQL | '[LocationName],';
s_SQL = s_SQL | '[Process],';
s_SQL = s_SQL | '[Step],';
s_SQL = s_SQL | '[InputOutput],';
s_SQL = s_SQL | '[ItemCode],';
s_SQL = s_SQL | '[Quantity],';
s_SQL = s_SQL | '[UserID],';
s_SQL = s_SQL | '[CreateDate]';
s_SQL = s_SQL | ')';
s_ItemCode = CellGetS(SC_CUBE,Model, UserID, RowID, 'Item');
s_LocationName = CellGetS(SC_CUBE,Model, UserID, RowID,'Location');
s_Process = CellGetS(SC_CUBE,Model, UserID, RowID,'Process');
s_Step = CellGetS(SC_CUBE,Model, UserID, RowID,'Step');
s_InputOutput = CellGetS(SC_CUBE,Model, UserID, RowID,'InputOutput');
n_Quantity = CellGetN(SC_CUBE,Model, UserID, RowID,'Quantity');
s_Quantity = Trim ( Str ( n_Quantity, 15, 0 ) );
s_CreateTime = vsCreateTime;
#VALUES ( ''%vsModel%'', ''%ItemCode%'',''%Location%'', ''%TransType%'',%Quantity%, ''%UserID%'', ''%CreateDate%'' )' ) ;
s_SQL = s_SQL | ' VALUES (';
s_SQL = s_SQL |''''|Model | ''',';
s_SQL = s_SQL |''''|s_LocationName | ''',';
s_SQL = s_SQL |''''|s_Process | ''',';
s_SQL = s_SQL |''''|s_Step | ''',';
s_SQL = s_SQL |''''|s_InputOutput | ''',';
s_SQL = s_SQL |''''|s_ItemCode | ''',';
s_SQL = s_SQL |''|s_Quantity |',';
s_SQL = s_SQL |''''|UserID | ''',';
s_SQL = s_SQL |''''|s_CreateTime |''' ';
s_SQL = s_SQL | ')';
If ( ( s_ItemCode @= '') % (Model @= '') % (s_ItemCode @='') % (s_LocationName @= '') % (s_Process @= '') % (s_Step @= '') % (s_InputOutput @= '') );
Else;
ODBCOutput('ODS',s_SQL);
Endif;
I_SNO_RowID_Idx = I_SNO_RowID_Idx + 1;
End;
-
- Posts: 52
- Joined: Thu Feb 17, 2011 2:52 pm
- OLAP Product: TM1
- Version: 9.5
- Excel Version: 2007
Re: HELP: ASCIIOUTPUT issues
Hello,
Any ideas why I am not getting all the data from the cube when executing this process?
Thanks,
Desi
Any ideas why I am not getting all the data from the cube when executing this process?
Thanks,
Desi
-
- Posts: 3
- Joined: Fri Jun 17, 2011 3:43 am
- OLAP Product: TM1
- Version: CX 10.1
- Excel Version: 2010
Re: HELP: ASCIIOUTPUT issues
thnaks heaps for this thread. It has given me an answer to a long standing issue that I've had with the leading and trailing double quotes in the asciioutput funciton!
BTW, I also use ascii characters for tab delimiters and single quote characters as well.
EG if I wanted to output the following line:
field1 <tab> field2's <tab> field3
I would code:
thanks again!
Darren
BTW, I also use ascii characters for tab delimiters and single quote characters as well.
EG if I wanted to output the following line:
field1 <tab> field2's <tab> field3
I would code:
Code: Select all
DatasourceASCIIQuoteCharacter='';
asciioutput ( 'c:\file.txt' , 'field1' | char(9) | 'field2' | char(39) | 's' | char(9) | 'field3' ) ;
thanks again!
Darren
-
- MVP
- Posts: 3113
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: HELP: ASCIIOUTPUT issues
Hello Darren
If you use a variable, the code will be more readable:
assuming your fields are not numeric.
If you use a variable, the code will be more readable:
Code: Select all
DatasourceASCIIQuoteCharacter='';
vTab=Char(9);
vDoubleQuote=Char(39);
asciioutput ( 'c:\file.txt' , field1 | vTab | field2 | vDoubleQuote | 's' | vTab | field3 );
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Posts: 10
- Joined: Fri Sep 02, 2011 2:14 pm
- OLAP Product: TM1
- Version: 9.5.2
- Excel Version: 2007
Re: HELP: ASCIIOUTPUT issues
Hi guys, i think, i can use the same aproach for: create multiple csv files as dimension elements, I mean, I have a cost center dimension into a cube, so I need to export data cube as scv files, one for each cost center. How can I achieve it?. Thanks in advanced.
-
- MVP
- Posts: 3113
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: HELP: ASCIIOUTPUT issues
The filename could be dynamic and contain the cost center:
asciioutput ( 'c:\data_' | vCC | '.txt', ... );
asciioutput ( 'c:\data_' | vCC | '.txt', ... );
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly