View result in .csv format

[Former Member]

Hello Gurus,

we have a requirement to calculate some figures from a ~1.5b table in an analytic view and save the output in a .csv file (~50k rows).

We were thinking of 3 possible solutions:

a. A procedure that calls the exported sql of the an. view and use the EXPORT sql syntax

b. A procedure that calls the an. view and called from BODS and the result saved in a file

c. A procedure that calls the an .view fills a table and the table gets downloaded with the EXPORT sql syntax

We have not tried any of them (they might not work at all, but soon we will get there...) but the main question is which is the best solution regarding the performance. Is there any better/other solution that you can think of?

Thanks for all the replies!!!!

[abanip]

Hi David,

What's the recordsize of the 50k output? What's the size of the output file?

Here is what I'd to get the best performance for your scenario. Since the underlying fact table has 1.5b records, make sure you have the right partitioning strategy based on your query pattern specifically if you have any kind of distinct count calculation.

Let's evaluate your options

Option C - I'd not consider this option, unless there is a compelling reason to materialize the data into an table? It does not make sense to for performance reason

Option A & B - I'm not sure what you mean by "exported sql of the an.view". I'm assuming you meant executing a SQL-statement against an Analytical View (in a HANA store procedure). Again if you are exporting file on the HANA server, there is hardly any performance difference between Option A & B. In this scenario BODS is only used for scheduling. So this is just matter of convenience. BTW, starting from HANA SP08, you can also schedule job using SAP HANA XS scheduler.

Personally I'd prefer option - A, since the whole can process can be managed with in HANA itself.

Regards

Abani

[Former Member]

Thanks Guys!

Any thoughts on performance? ...as I said the view needs to calculate measures from a ~1.5b table

Thanks,

David

[Former Member]

Hi,

You can also try to write a simple XSJS script to call the procedure and export the results and store it as a CSV/XL in you client filesystem.

Hope this helps

Regards,

Suhas

[Former Member]

Hi David,

I believe EXPORT syntax normally face performance issue in case of huge volume.

Try below thing , where EXCEL can be connected to HANA and data can be easily exported in to excel file for analysis.

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/e03fef5e-d82f-2f10-8898-859c4ed57...

Thanks

Siva