Configuring Format in Export to Excel

Lee Gledhill

Hi All,

I've had a search in the forum, fully expecting this to have come up before now but was unable to find anything.

Okay, firstly lets address the elephant in the room:

I know Exporting to Excel is a contentious matter (on the forum, and in every business using OBIEE) I'm not new to BI, I appreciate OBIEE is Enterprise reporting and thus isn't a data dump tool nor Operational/Transactional reporting and we shouldn't be addressing large data-sets let alone dumping fully formatted Pivot tables with umpteen million cells but here I am, in a position where I need to make this happen.

I'm mid-implementation of OBIEE 12.2.1.2.0 - We have some pretty large reports that need to be exported to reconcile with stand-alone systems (currently) not part of the BI environment.

We've upped limits to exporting but it takes an age and I'm fearful the end user will think nothing is happening and either re-run the large report or re-run the export taking up temporary space and/or hammering the server.

Exporting to CSV isn't an option (leading 0's, pivot format, Excluded columns get included, yada yada yada I'm sure you've heard it all before)

Scheduled or Published reports isn't an option either, the users have 'live' Mat Views which at month-end need to be ran ad-hoc and exported and BIP doesn't have the size capability to handle these reports.

What I'm looking into is whether stripping formatting from an Excel export is possible, and how to go about doing this?

Basically what I need is somewhere between CSV and Excel 2007 exports - I need Pivot formatting and leading 0's and dates and the like, but no other formatting.

Or a way that doesn't format every single cell upon export, to format cell ranges instead, formatting single cell is really resource intensive.

Any advice (that isn't "you shouldn't have bought BI") is really welcome. I've seen reformatting of exports before but never been involved nor understand the limitations of this.

Thanks, Lee

Robert Angel

I think the advice would be use an ETL tool or other data pump of choice.

Really, OBIEE isn't the droid that you are looking for.