Pulling data into PAS from Excel and Oracle

[Former Member]

Hi Experts,

     I am new in SSM, I want to load data to PAS from Excel and also from Oracle table. Please guide me thru.

Also provide me the sample excel sheet, which format(xlxs, csv..etc), and how the procedure is written for that sheet in IDQL.

And same for Oracle too.

Thank you,

Max.

[Former Member · Accepted solution]

Are you wanting to create dimension or to load data into a PAS structure where the dimensions already exist?

If creating a dimension, are Ename, Job and Deptno three levels of a single dimension?

[Former Member · Accepted solution]

Hi Clifford,

     based on the data that was read, i want to create d dimensions...So plz guide me.

Please check d below procedure, which creates d dimension as well,

Is the data loaded now, if yes...how to check the loaded data...???

System> job oracle_pro_scott
LSS> clear status
LSS>
LSS>  Access LSlink
LSLink>
LSLink> connect oracle_scott
LSLink>
LSLink> Select ename, job, hiredate, deptno, sal from emp
LSLink>
LSLink> peek only 10

      ENAME                              JOB                             HIREDATE                             DEPTNO            SAL 

    1 SMITH                              CLERK                           12/17/1980                            20.00         800.00 
    2 ALLEN                              SALESMAN                        02/20/1981                            30.00        1600.00 
    3 WARD                               SALESMAN                        02/22/1981                            30.00        1250.00 
    4 JONES                              MANAGER                         04/02/1981                            20.00        2975.00 
    5 MARTIN                             SALESMAN                        09/28/1981                            30.00        1250.00 
    6 BLAKE                              MANAGER                         05/01/1981                            30.00        2850.00 
    7 CLARK                              MANAGER                         06/09/1981                            10.00        2450.00 
    8 SCOTT                              ANALYST                         04/19/1987                            20.00        3000.00 
    9 KING                               PRESIDENT                       11/17/1981                            10.00        5000.00 
   10 TURNER                             SALESMAN                        09/08/1981                            30.00        1500.00 
LSLink>
LSLink> begin
>  construct dim2
>  level ename, job, hiredate
>  label deptno, sal
>  preface "custom 5 allocate 100,50,1"
> end
LSLink>
LSLink> end
LSS>
LSS> compile dim dim2
4 Members Roll Into Multiple Outputs
All 33 Members of DIM2;TEST Selected

Thanks,

Maxban.

[Former Member · Accepted solution]

To understand if you've created a dimension, issue the pas command dir dim. You should see Dim2 in the result. Actually the message at the end of your post tells you the creation was successful.

To see the contents of the dimension use the PAS command DIM DIM2. This will open the editor on DIM2.

I doubt that the dimension is built to your requirement. Between a lower level and a higher level there should be a many to one relationship.

You has built a three level hierarchy with ename rolling into job rolling into hiredate. It is more likely you want ename into job or ename into deptno. Job into Deptno is not a good fit because it is a many to many indicatging these are members of differnt dimensions.

You have also specified deptno and sal as labels. I do not see any column in your SQL tat looks like a label. You would be best off omiting the label command.

[Former Member · Accepted solution]

Thanks for the quick help.

Few Questions.:

What about the salary, means in business prespective the KPIs.??

I am planing to group the salary, Job wise, how it can be done.??

Thanks

Maxban.

[Former Member · Accepted solution]

There are two ways to treat salary.

First it can be a measure (KPI). In this case salary costs would be loaded as a separate proc after the dimensions are built.

Secondly it can be a dimension or a level in a dimension. For each of these you would group it into bands in the ORacle SQL and so have a text field something lke <$500, $500-$1000, >$1000.

If Ename is your lowest level in a dimension, then salary band could be a higher level in the same dimension. If Job is a dimension level then Salary should be a separate dimension because a job (e.g. sales) may fall into more than one salary band.

.

[Former Member · Accepted solution]

HI Cliffy

For the Following procedure,

clear status

set period jan 1980 - dec 1981

select var sal

select dim2 input

Across variable down dim2, time

Access Lslink
connect Oracle_scott

select ename, hiredate, sal from emp

peek only 10

lss create dim2 = ename
LSS create time = hiredate

read

end

I am able to read data and also skipped some.

Now please tell me ...how to see d data whch is loaded.

System> job test
LSS> clear status
LSS>
LSS> set period jan 1980 - dec 1981
LSS>
LSS> select var sal
1 Variable Currently Selected
LSS>
LSS> select dim2 input
14 Members of DIM2 Selected
LSS>
LSS> Across variable down dim2, time

Across List:        # Selected
  VARIABLES   1  SAL

Down List:
  DIM2       14 of 20    SMITH, ALLEN, WARD, JONES, MARTIN, BLAKE, CLARK...
  TIME    

Period 1980/1/1 - 1981/12/31
Attached Databases: TEST, APLIB, SMREPORT  User: ADMIN
LSS>
LSS> Access Lslink
LSLink> connect Oracle_scott
LSLink>
LSLink> select ename, hiredate, sal from emp
LSLink>
LSLink> peek only 10

      ENAME                              HIREDATE                             SAL 

    1 SMITH                              12/17/1980                        800.00 
    2 ALLEN                              02/20/1981                       1600.00 
    3 WARD                               02/22/1981                       1250.00 
    4 JONES                              04/02/1981                       2975.00 
    5 MARTIN                             09/28/1981                       1250.00 
    6 BLAKE                              05/01/1981                       2850.00 
    7 CLARK                              06/09/1981                       2450.00 
    8 SCOTT                              04/19/1987                       3000.00 
    9 KING                               11/17/1981                       5000.00 
   10 TURNER                             09/08/1981                       1500.00 
LSLink>
LSLink> lss create dim2 = ename
LSLink> LSS create time = hiredate
LSLink>
LSLink> read
Record: 8

TIME: 04/19/1987 is Not a Selected Member of TIME

Record: 11

TIME: 05/23/1987 is Not a Selected Member of TIME

Record: 14

TIME: 01/23/1982 is Not a Selected Member of TIME

11 Record(s) Read, 3 Record(s) Skipped.
LSLink>
LSLink> end

Thanks,

Maxban

[Former Member · Accepted solution]

You are able to read the data; 11 of your records read.

The three that skipped were outside your selected date range (Jan 80 - Dec 81)  and so should reject.

Everything is working as it should.

To see the data

set period jan 1980 - dec 1981

select var sal

select dim2

Across variable down dim2, time 

list

btw, are you aware of the custom of awarding points for helpful answers?

[Former Member · Accepted solution]

Hi Cliffy,

I have marked, some of ur replies as Helpful.

You didnt answer my previous question....

How can see d the data which is read wd Salary and the Ename??

Thanks for all your Patiences

Max.

[Former Member · Accepted solution]

I am not understanding your question. The code in my last post will display the salary data with a row per ename.

Alternatively use the command

Across variable, time down dim2

List

Perhaps you could post a mock-up of what you want to see.

[Former Member · Accepted solution]

THanks for al your help Cliffy....

[Former Member · Accepted solution]

Last but not the least,

the code u gave, can dsplay only ename n sal.....wat if

I want o/p such as

job_type      SALARY

salesman     sum of all emp salaries in salesman.

or

ename      job_type      sal

Please help on dis.

[Former Member · Accepted solution]

I am missing some parts of your dimensional structure. It depends on whether job_type is a level in Dim2 or is a dimension of it's own. I suspect you are thinking in terms of RDBMS whereas PAS (and all OLAPs) have a different paradigm.

If job_type is a higher level of dim2, then "select dim2" will automatically ensure tob type totals will get displayed. "List Hier " will  provide an indented display showing parents (job_type) and children (ename).

If you only want job_type totals, replace the command "select dim2" with "select dim2 output".

If job_type is a separate dimension, let me know as it requires different commands.

Finally the totals will not appear until after you've done a consolidate of sal. After loading the data, try this.

Select Dim2

set period jan 1980 - dec 1981

Consolidate SAL

After that totals will be available.

[Former Member · Accepted solution]

Cliffy,

Thanks for d code..its now consolidated...but its giivn an error for LIST HIER command.

saying... Excepted ...hier... i dnt exactly remember. bt related expected somthng. dats it.

set period jan 1980 - dec 1981

select var sal

select dim2

Across variable down dim2, time 

list hier

dis was d code.

plz suggest..

[Former Member · Accepted solution]

Check the help as I don't have a system to look at. It was either

List Hier

or

List order Hier

or

Order hier

List

The other thing I just noticed you have Across variable down dim2, time  . This will make time the last down dimension. You want a list of Dim2 (ename, jobno) so dim2 has to be the last down. Try across var, time down dim2

[Former Member · Accepted solution]

Hi Cliffy,

     None of them is able to work, all throwing some Errors.

Please do tell me, wat wl be d actuall command.

Thanks,

Maxban.

[Former Member · Accepted solution]

across var, time down dim2

order dim2 hier

list

For these things check the help file. As I said I don't have access to a system so I will not always have the correct syntax in my answers. My previous post said "order hier" when it should have said "order dim2 hier". Looking up the Order command in the help would have given you the correct syntax.

Cheers

Cliff 

[Former Member]

Hi,

I have pulled data from Oracle without any errors. Using...

System> job oracle_pro_scott
LSS> clear status
LSS>
LSS>  Access LSlink
LSLink>
LSLink> connect oracle_scott
LSLink>
LSLink> Select ename, job, hiredate, deptno, sal from emp
LSLink>
LSLink> peek only 10

      ENAME                              JOB                             HIREDATE                             DEPTNO            SAL 

    1 SMITH                              CLERK                           12/17/1980                            20.00         800.00 
    2 ALLEN                              SALESMAN                        02/20/1981                            30.00        1600.00 
    3 WARD                               SALESMAN                        02/22/1981                            30.00        1250.00 
    4 JONES                              MANAGER                         04/02/1981                            20.00        2975.00 
    5 MARTIN                             SALESMAN                        09/28/1981                            30.00        1250.00 
    6 BLAKE                              MANAGER                         05/01/1981                            30.00        2850.00 
    7 CLARK                              MANAGER                         06/09/1981                            10.00        2450.00 
    8 SCOTT                              ANALYST                         04/19/1987                            20.00        3000.00 
    9 KING                               PRESIDENT                       11/17/1981                            10.00        5000.00 
   10 TURNER                             SALESMAN                        09/08/1981                            30.00        1500.00 
LSLink>
LSLink> end

Now my aim is to write/store this data into dimensions, how i shld go about?, also tell how to create it and d code, if pssble??

I am also planing to group the salary, Job/Deptno wise, how it can be done.??

Thanks

Maxban.

[Former Member]

Hi Friends,

     I am pulling just normal data from excel.

This is the procedure.

CLEAR STATUS

SET PERIOD 09/01/01 - 12/12/31

SELECT VAR M1

ACROSS variable DOWN time

ACCESS LSLINK

CONNECT Excel

SELECT * from Jan

Peek only 3

READ

END

When I run this with JOB command, its fetches 3 records and gives the following ERROR.

..

..

..

..

LSS> Peek only 3

               1 

    1       2.00 
    2       3.00 
    3       4.00 
LSS>
LSS>
LSS> READ
[Microsoft][ODBC Excel Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.
SQLSTATE: 37000
SQL System code: -3500
SELECT VAR M1
             ^
Expected FROM
ACC136:
ACCESS LSLINK
^
You cannot use the ACCESS Command within the ACCESS Sub-System.
DAT049:
VARIABLES Has No Selected Members

Please help.

[Former Member]

It looks like you tried to rerun the proc while in the Access subsystem. PAS has a number of subsystems. Access LSLink is one. In these you have different commands available. Your proc issues an Access LSLink command taking you into the access subssystem. If your proc crashed on an ealier run or failed to include a end command to exit the Access subsystem, the next time you tried to run it , it would assume you were already inside it prior to issuing the  Access LSLink command. Then when you issues the PAS command SELECT VAR M1 it tried to treat that as a SQL command and so threw an error.

It is not always obvious when you're still in the Access LSLink subsystem but there should be a status message at the bottom of the screen.

[Former Member]

Hi Clifford,

     For Excel load, i am getting a error

LSLink>
LSLink> SELECT * from Jan
LSLink>
LSLink> Peek only 8

               1 

    1       2.00 
    2       3.00 
    3       4.00 
LSLink>
LSLink>
LSLink> READ
LSLink>
LSLink> END
ACC001:
There is NO Corresponding Field for TIME

My excel file has values such as

JAN
1
45
2
3
and so on till 10

JAN is a named ranged.

Whats wrong here, can you please explain.???

Do i need to clear the M1 variable, and how to clear it.???

Can we append the values, say as if 1, 2, 3 and if we agian load wd 4, 5.......... it should be 1,2,3,4,5.?????

Main things is related to Oracle, What changes we need to make in Procedure part, Access LSLINK is used in Oracle Procedure also???

If not, please tell me know, how to load data from a oracle table to PAS and what all things needed for that in PAS.

Will be thankful...

Max.

[Former Member]

You've got a few questions here. Let's see I I can gover them without confusing the issues.

Your accross/down command reads

ACROSS variable DOWN time

and so does not match your table / Excel. You can change the accross/down or change the table. To change the table, you need two columns.

• the first must be called time and have values of Jan 2013 in every row.
• the second is what you already have but change the column name to M1

There is no need to clear the M1 Var

By default PAS reads additively. 1 +  45 + 2 + 3 will all be summed into M1. A "read replace" command overwrites the previous value.

To access Oracle you need an LSLink pointing at the Oracle connection. You create a system DSN in windows and then create an LSLink pointing at the system DSN. If you're on a 64 bit system be sure to use the ODBC from Windows/SysWOW64 and not the one from the control panel.

Then clone your proc and replace the "connect excel" line with connect "myOraclelink". You can also use control variables to use a single proc for multiple database sources but let's save that for another day.

Cheers

Cliff

[Former Member]

hi,

I am bit confused, sorry for dat.

This is my excel file now.

Time	M1
1/1/2013	1
1/2/2013	2
1/3/2013	45
1/4/2013	3
1/5/2013	4

This is the Procedure:

CLEAR STATUS

SET PERIOD 09/01/01 - 12/12/31

SELECT VAR M1

ACROSS variable DOWN time

ACCESS LSLINK

CONNECT Excel

SELECT * from Jan

Peek only 8

READ

END

Still the same Error

ACC001:

There is NO Corresponding Field for TIME.

Please let me know.

Related To ORACLE

This is the Procedure

CLEAR STATUS

SET PERIOD 09/01/01 - 12/12/31

SELECT time, VAR M1

ACROSS variable DOWN time

ACCESS LSLINK

CONNECT Oracle_GL

SELECT  Load_date as time, FK_ID_GL_ACCOUNTS as GL   from GL_BALANCE_FACT

Peek only 16

READ

END

On JOB, i am getting the followin Error,

..

..

LSS> Peek only 16

      TIME                                  GL 

    1 01/29/2013                        407.00 
    2 01/29/2013                        408.00 
    3 01/29/2013                        412.00 
    4 01/29/2013                        496.00 
    5 01/29/2013                        497.00 
    6 01/29/2013                       8435.00 
    7 01/29/2013                        217.00 
    8 01/29/2013                        503.00 
    9 01/29/2013                       1623.00 
   10 01/29/2013                       1629.00 
   11 01/29/2013                       9368.00 
   12 01/29/2013                         55.00 
   13 01/29/2013                         73.00 
   14 01/29/2013                        153.00 
   15 01/29/2013                        156.00 
   16 01/29/2013                        157.00 
LSS>
LSS>
LSS> READ
OLEDB:Error: 80040e14 ORA-00922: missing or invalid option
COM:Code: 80040e14 IDispatch error #3092
SELECT time, VAR M1
                   ^
Expected FROM
ACC136:
ACCESS LSLINK
^
You cannot use the ACCESS Command within the ACCESS Sub-System.
DAT049:
VARIABLES Has No Selected Members

Few questions i have here,

1. I can see the data is read, but is it stored in PAS now or not yet?I guess NO, What can be done to write the data??

2. Related to the 1st Select stmt, what is the purpose of it??

3. What i need to do to hold the data, Please correct me in the procedure.

4 Also tell me how to hold time data.

Check the follwin procedure,

CLEAR STATUS

SET PERIOD 09/01/01 - 12/12/31

SELECT VAR M1

ACROSS variable DOWN time

ACCESS LSLINK

CONNECT Oracle_GL

SELECT load_date as time, amount from GL_BALANCE_FACT

Peek only 5

Read

END

we are geting the follwin ERROR.

.

.

.

LSLink>
LSLink> Peek only 5

      TIME                              AMOUNT 

    1 01/29/2013                             - 
    2 01/29/2013                             - 
    3 01/29/2013                             - 
    4 01/29/2013                             - 
    5 01/29/2013                             - 
LSLink>
LSLink> Read
There is NO Corresponding DATABASE Field for M1.
It Will Not be Used in the READ/WRITE.

LSLink>
LSLink> END
LSS>
LSS>
ACC056:
No Fields Match the Current ACROSS Specification.
This Must be Corrected Before any READ or WRITE can take Place.

Actually facing lot of prblm.

Thanks,

Maxban.

[Former Member]

That's a lot of questions.

Conmcerning the Excel did you extend the named range. Your SQL (select * from Jan) must refer to a named range (Jan) which must cover both your columns.

[Former Member]

For Oracle, the errro

OLEDB:Error: 80040e14 ORA-00922: missing or invalid option

COM:Code: 80040e14 IDispatch error #3092

SELECT time, VAR M1

                   ^

Expected FROM

ACC136:

ACCESS LSLINK

^

You cannot use the ACCESS Command within the ACCESS Sub-System.

DAT049:

VARIABLES Has No Selected Members

tells me you did not end the Access LSLink subsystem and so the PAS commands (select time var M1) are being passed to Oracle where they are rejected as invalid SQL.You must end your Access LSLink session with an end command at the end of your proc. If yourproc crashes prior to reaching the end command you must manually issue it before rerunning.

[Former Member]

The final code has "ACROSS variable DOWN time" and so the SQL must return a column as either "variables" (or measures, I can't remember now?) or a column as M1.

Change the SQL to SELECT load_date as time, amount as M1 from GL_BALANCE_FACT

[Former Member]

Hi,

It has covered both the columns, but getting the error, and fetching some wrng data i guess. It shld peek 8, and its reading 3. Excel file is same as above.

.

.

ink>
LSLink> Peek only 8

               1 

    1       2.00 
    2       3.00 
    3       4.00 
LSLink>
LSLink>
LSLink> READ
LSLink>
LSLink> END
ACC001:
There is NO Corresponding Field for TIME.

[Former Member]

That output does not match the file you showed earlier. It had values 1,2,45,3,4. Are you sure you ave the right spreadheet?

Also note that the peek is treating the first data row as a header. it seems your named range starts a row early or a row late.

Finally I can't remember how PAS treats Excel dates but remeber this; Excel holds dates as a number representing the days since 1900. (Take any date field and try formatting it as a numer to see what happens.) You might have to ensure the date column is a string representation of a date, e.g. 'Jan 2013

[bruno_heissler2]

Hi Maxban,

Load data from Oracle tables to PAS Dimensional Model, you need be aware a few steps:

1) Take the procedures on the directory: "C:\Program Files (x86)\SAP BusinessObjects\Strategy Management\InternetPub\procs\oracle_procs" and copy (subscribes) to the follow directory: "C:\Program Files (x86)\SAP BusinessObjects\Strategy Management\InternetPub\procs".

This is a step needed when you extract data from Oracle tables.

2) Go to Application Server client, and create a Link ID (besides the connection SSM_CB_EA) pointing to your Oracle Database (source data tables).

3) After this, into Application Server client, you need develop some procedures (IDQL syntax) using the connection created and extracting the Oracle data to PAS Dimensional Model.

P.s: I´m assuming that you already builded your structure (dimenions and measures) into Cube Builder Interface.

4) Follow a procedure as example extracting data from Oracle tables and loading into existing measures:

................................................

CLEAR STATUS

SET PERIOD 09/01/01 - 12/12/31

SELECT VAR KPI168_ACT, KPI168_TAR

ACROSS variable DOWN time

ACCESS LSLINK

CONNECT BOPRD

SELECT DATA  AS TIME, SUM(F_ECON_MES.VALOR_REALIZADO)/1000 AS KPI168_ACT, SUM(F_ECON_MES.VALOR_PLANEJADO)/1000 AS KPI168_TAR FROM F_ECON_MES WHERE (F_ECON_MES.CODIGO_CONTA_DRE  = 350) GROUP BY DATA

Peek only 100

READ

END

................................................

You can create some procedures to attend your specifically measures, and after this, create a main procedure calling all the others.

Issues, please, let us know.

I hope this help you.

Regards,

Bruno Heissler

[Former Member]

Bruno's answer is thorough and essentially correct. I would like to suggest a couple of points.

The copy of the procs (Bruno's step #1) is only needed if SSM is installed on Oracle. The procs are used for Cube builder and Entry and Approval. They are not needed for the sort of data extract you are doing. 

It is possible to install SSM over MaxDB or SQL Server and still read data from Oracle. In this case the procs to use are the MaxDB (default) or SQL Server.

The Connect BOPROD in Bruno's example is based on the BOPROD being the name of the Link Id you created in Bruno's step 2

If you extract from Excel then a named range in an Excel works just like a table in Oracle, e.g. select * from MyNamedRange

Finally it is possible to put control variables into your SQL inside your proc and so make the proc dynamic, even to the point of dynamically controlling what columns to return.

Good luck

[Former Member]

Hello Bruno,

Can we automate the Set Period step? I mean to say that is there any option to capture the current date or month from the system and set the Period as same month so that we need not to edit the procedure again and again for every month.

In SAP notes, i have read that there are few system variables for PAS server like:

SET PERIOD DAILY

SET LATEST TODAY

SET EARLIEST LATEST-365

Can we use any one out of this which can set the period to current date or month?

Please help me in this.

Thanks.