Unable to export all the rows

3371002

I have an analysis which returns 549214 rows when run but when I export the results to a xlsx or a csv, I am only seeing 4002 rows. Please refer to the attached screenshots if they are of any help. What is snipping off the results?

Gianni Ceresa

Are you sure the result is really 550'000 rows? Because the result of COUNT doesn't really mean that's the number of rows you get (there are aggregations happening).

Do you really have 550'000 on screen when you expand your table to show all the rows?

Also keep in mind OBIEE isn't a datapump tool, so default settings limit the size of exports as that's not what the tool is built for ...

Sherry George

Since you said xxx number of rows returned in analysis and during download you are getting less, then you might be hitting a limit set in your instanceconfig.xml. Also what version of MS Office are you using?

2961110

In your instanceconfig.xml

Search for the Tag

<DefaultRowsDisplayedInDownload>1000000</DefaultRowsDisplayedInDownload> to increase the value to your needs

under the <Views> Section. Be aware that there are 2 different Sections unbeneath <Views>.

One for the <Table>-View and one for the <Pivot>-View.

Helped me out ;-)

P.S.

If you get stuck in a little less than 65.000 rows in Excel-Export, you should use a newer Excel version that is capable of up to 1 million rows.

Guess at least Excel 2003, 2007 should work anyway.

3365160

search <PATH>/instances/instance1/config/OracleBIPresentationServicesComponent/coreapplication_obips1/instanceconfig.xml

and do this

      <Views>

      <Pivot>

<MaxCells>1920000</MaxCells>

<!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control-->

<DefaultRowsDisplayedInDelivery>1000</DefaultRowsDisplayedInDelivery>

<!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control-->

<DefaultRowsDisplayedInDownload>1000000</DefaultRowsDisplayedInDownload> *************what u need**********************

<!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control-->

<DisableAutoPreview>false</DisableAutoPreview>

<MaxVisibleColumns>50000</MaxVisibleColumns>

<!--MaxVisiblepages>2500</MaxVisiblepages-->

<MaxVisibleRows>999999</MaxVisibleRows>

<MaxVisibleSections>5000</MaxVisibleSections>

<DefaultRowsDisplayed>1000000</DefaultRowsDisplayed>

</Pivot>

<Table>

<MaxCells>1920000</MaxCells>

<!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control-->

<DefaultRowsDisplayedInDelivery>1000</DefaultRowsDisplayedInDelivery>

<!--This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control-->

<DefaultRowsDisplayedInDownload>1000000</DefaultRowsDisplayedInDownload>  *************what u need**********************

<MaxVisibleRows>9999999</MaxVisibleRows>

<MaxVisibleSections>5000</MaxVisibleSections>

<DefaultRowsDisplayed>1000000</DefaultRowsDisplayed>

</Table>

<Cube>

<CubeMaxRecords>999999</CubeMaxRecords>

<CubeMaxPopulatedCells>999999</CubeMaxPopulatedCells>

</Cube>

<Charts>

<JavaHostReadLimitInKB>8192</JavaHostReadLimitInKB>

<MaxVisibleColumns>50000</MaxVisibleColumns>

<!--MaxVisiblepages>2500</MaxVisiblepages-->

<MaxVisibleRows>9999999</MaxVisibleRows>

<MaxVisibleSections>5000</MaxVisibleSections>

</Charts>

</Views>

Gianni Ceresa

@3371002 : keep in mind to check what settings do you really need and want and not copy/paste a bunch of things in your config file, mainly when there are pieces managed by Enterprise Manager (as comments in the file tell you).

Christian Berg-0racle

1.) "and do this" ... nice generalization without explanation.

2.) Funny how you think that will work intelligently with <Cube> sections being lower than even the row count.

3365160

May not be explicit enough but a comment indicates exactly which line to edit, no need to be gifted to understand.

The expert!

Christian Berg-0racle

Well it would be nice to have provide some context rather than just randomly stating "do X"

Configuring for Displaying and Processing Data in Views

Because - for example - the DefaultRowsDisplayedInDownload is being modified but the DefaultRowsDisplayedInDownloadCSV is not.

Also posting instanceconfig,xml excerpts but pointing out tags which are NOT being changed in the file itself but through Enterprise Manager...

3371002

Are there any other files besides instanceconfig.xml that I should look at/edit?

3371002

I am looking at the various parameters Sherry (by looking I meant bumping them up) I am using Microsoft Office Professional Plus 2016

Christian Berg-0racle

Edit: yes it is: https://blogs.oracle.com/proactivesupportepm/patch-set-update:-oracle-smart-view-11125520