Help Exporting All data from Cube to SQL Server tables

[dfrench77]

Hello All,

Any idea why the below code is not exporting all data records. It currently only exports the data for one specific user regardless of what user is executing the process. 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,

dfrench77

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;