Xcelsius Vs SAP Design Studio

  

ü  A very routine question from clients is about which dashboarding tool is better

                       SAP Dashboards (formerly Xcelsius) or SAP Design Studio

ü  Ever since SAP added SAP Design Studio to its offerings, this has become a constant debate in the dashboarding community.

Read sheets from excel

Using looping concept read all sheets from excel. This is follow chart for easily understanding

To write scripts we have to initialize the global variables:

To create variables go to Tools->variables->global

Varaible1:

$G_SHEET_NO: Set data type-varchar, length- 11, value-1

Initialize the variable for looping

Varaible2:

$G_LIST_SHEET: Set data type-varchar, length- 11, value-1

Get the sheet name

write scripts using global variables:

Script-1:

$G_SHEET_NO =1;

Sql(‘New_Datastore’,’delete multiple_excel_sheets’)

Script-2:

$G_LIST_SHEET =’sheet||$G_SHEET_NO;

Print(‘Loading data from:’||$G_LIST_SHEET);

Script-3:

$G_SHEET_NO =$G_SHEET_NO +1;

write condition for while loop:

$G_SHEET_NO <=10;

Here , we will check the count of the file names is been correct or not

Just like let’s say we have totally 10 files to be loaded.

Then 1<=10 next

After increment 2<=10 like this loop will be iterated upto the condition is been dissatisfied.

After 11<=10 it goes to false path linked object

Files Path:

Source1  

Local: F:\EXCELFILE

Excel name:SHEET_LIST.xls

Work sheet: $G_LIST_SHEET

Codepage:utf-8

Looping conecepts for load multiplefiles into single table

Using looping concept load multiple files into single table in single job

Using looping concept we can load multiple files into single table in single job. This is follow chart for easily understanding

                            

To write scripts we have to initialize the global variables:

To create variables go to Tools->variables->global

Varaible1:

$FILE_NAME: Set data type-varchar, length- 21, value-1

we will try to fetch the filename by using this . And the current filename’s data will be fetched and loaded to DB.

Varaible2:

$FILE_NO : Set data type-int, length- 4, value-1

Initialize the variable for looping

Varaible3:

$FILE_COUNT: Set data type-varchar,length- 20

: It is a variable which we used to store the count of the files.

write scripts using global variables:

Script-1:

$FILE_NO=1;

Script-2:

Dir F:\smartfolder\GL_Position\*.txt/b /a:-d>  F:\smartfolder\csvfiles\GL_position.csv        

$FILE_NAME=sql(‘New_Datastorename’, ’SELECT FILENAME FORM (SELECT FILENAME,ROWNUM R FROM SRC_GP_DEATILS) WHERE R=’||$FILE_NO)||’.txt’;

$FILE_COUNT= sql(‘New_Datastorename’, SELECT count(*) FROM SRC_GP_DEATILS) ;

Print(‘Loading data from:’||$FILE_NAME);

Script-3:

$FILE_NO=$FILE_NO +1;

write condition for while loop:

$FILE_NO<=$FILE_COUNT;

Here , we will check the count of the file names is been correct or not

Just like lets say we have totally 50 files to be loaded.

Then 1<=50 next

After increment 2<=50 like this loop will be iterated upto the condition is been dissatisfied.

After 51<=50 it goes to false path linked object

Files Path:

Source1  

Local: F:\CSV Files\GL_Postion

Filename:$FILENAME

Column delimiter ‘|’ pipe

Skipped row header: yes

To get all file names into one csv file

Here we will try to load all the filenames from a defined folder to one file by using a command as

Dir F:\smartfolder\GL_Position

\*.txt/b /a:-d>  F:\smartfolder\csvfiles\GL_position.csv        

If files are in server

Dir \\192.0.0.0\projectdevelopemnt\smartfolder\GL_Position folder\*.txt/b /a:-d> \\192.0.0.0\projectdevelopemnt\smartfolder\csvfiles\GL_position 

 Overall process is first we load all the names of the files into a table in DB. First initialize the variable value 1.we will try to fetch the filename by using query. And the current filename’s data will be fetched and loaded to DB. Increment the variable until condition is true (total count of files reached) , so that load next filename of all files.