Reading from SAP BW and writing to PA in parallel

Post Reply
JohnO
Posts: 8
Joined: Mon Jul 29, 2019 5:02 am
OLAP Product: Planning Analytics
Version: 2.0.8
Excel Version: Office 365 16

Reading from SAP BW and writing to PA in parallel

Post by JohnO » Tue Dec 24, 2019 11:13 am

Has anyone been able to do this using Cognos Integration Server or some other mechanism?

At the moment we extract to .csv from BW BI queries, split using VB Script then load in parallel using Runprocess in TI's (And flag files to manage completion status of the called processes). We do the parallel liad because of volume.

I really want to flip the approach and have PA in charge and send parallel queries to BW by executing SAP BI queries with passed parameters such as year and week. Not a complex requirement.

The documentation on Cognos Command Center and Cognos Integration Server is not that great. As I understand it, Cognos Integration Server cannot load to PA in parallel as it is just a standard client.

Without going into more detail on the options I have considered does anyone have experience/ ideas?

EvgenyT
Community Contributor
Posts: 324
Joined: Mon Jul 02, 2012 9:39 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: 2016
Location: Sydney, Australia

Re: Reading from SAP BW and writing to PA in parallel

Post by EvgenyT » Tue Dec 24, 2019 10:21 pm

We used to connect to SAP BW with a help of SAP connector for tm1, but this was before PA. I believe SAP connector has been depreciated too, correct me if I am wrong

JohnO
Posts: 8
Joined: Mon Jul 29, 2019 5:02 am
OLAP Product: Planning Analytics
Version: 2.0.8
Excel Version: Office 365 16

Re: Reading from SAP BW and writing to PA in parallel

Post by JohnO » Thu Dec 26, 2019 10:11 pm

The old connector which could be used as a source for TI was deprecated. However they introduced new BW read/write functionality as part of Cognos Integration Server.

EvgenyT
Community Contributor
Posts: 324
Joined: Mon Jul 02, 2012 9:39 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: 2016
Location: Sydney, Australia

Re: Reading from SAP BW and writing to PA in parallel

Post by EvgenyT » Thu Dec 26, 2019 10:51 pm

Cool, thought so.

Did you look at running this as batch with CIS command line utility?
Then you could trigger it from a TI process/Chore with specified year/month etc parameters.

Let me know, will be interested in seeing how you go!

Thanks

Evgeny

JohnO
Posts: 8
Joined: Mon Jul 29, 2019 5:02 am
OLAP Product: Planning Analytics
Version: 2.0.8
Excel Version: Office 365 16

Re: Reading from SAP BW and writing to PA in parallel

Post by JohnO » Mon Dec 30, 2019 4:21 am

No I haven't got that far. I'm thinking that would need to work the following way:

1) Use RunProcess and run parallel TI's within TM1 which each would:
a) run the batch with parameters which would execute a SAP BI query and write the results to a text file - (as I understand it a direct write to TM1 is possible using CIS but no transform is possible)
b) On successful production of the text file then run a load TI which apply the required transforms

For this approach to be of benefit is would need SAP BI to be able to run the separate requests in parallel

Might be possible?

EvgenyT
Community Contributor
Posts: 324
Joined: Mon Jul 02, 2012 9:39 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: 2016
Location: Sydney, Australia

Re: Reading from SAP BW and writing to PA in parallel

Post by EvgenyT » Mon Dec 30, 2019 5:49 am

Yes, something along those lines.

Also, consider a staging cube to land SAP data? Then use it as a data source for further upstream transformations. Saves you from a hassle of dealing with csv files. Moreover, if you are dealing with numerous threads, consider using RushTi to manage processing queue.

Thanks

Evgeny

Post Reply