Temporary Tablespace for Infocube Tables

[Former Member]

Hi Experts,

Does anybody know what structure or tablespaces are involved for storing the infocubes data and also for ods objects.

I do know that data is stored in fact tables and dimension tables, but for all these underlying there are tablespaces allocated where can we find this information in db02 does not give much information.

I found some structures and internal tables, So when a cube is loaded with data, underlying it stores in tables, and for these tables there are temporary tablespaces, where can we find this information, is there any function modules involved for this tablespace information. Can anybody help me in this please it will great.

Thanks in advance

Prabs.

[Former Member]

Not real sure what you are looking for or why. Some of what you are asking about may vary from DB to DB, and tablespace names may vary from installation to installation.

Fact tables, Dimensions, ODS, and Master Data all have associated table and index tablespaces.

PSA tables have system assigned names that look like

/BIC/B0000103001 Data

/BIC/B0000103001~0 Index

They are found in the ODS table and index tablespaces.

Your Database also has a tablespace for temporary objects, e.g. intermediate results in a query, sorts, etc, and some other tablespaces for housekeeping, redo, rollback, system.

DB02 will show you what tablespaces exist and what tables or indexes are in those tablespaces.

ODSs are a little different than cubes, as there are 3 tables involved, the active table, the table that new loads go to and the change log. The activation process moves/updates the data from the new data table to the active table and change log.

What's your role at your site? Not sure what you are looking for or why? Tablespaces are usually a concern of the DBA and as long as there is adequate space allocated, shouldn't be a big concern.

[Former Member]

Hi Pizzaman,

This is my requirement, In our product, we are relocating the data from bw infoproviders to remote database(oracle).

So we have options to select the data based on infoproviders chars and keyfigures to relocate.

So at some times if the infocubes are huge and has large no.of records say around 100 million records or so, the bw system crashes, so solution was to increase the temp tablespace in bw and in worked fine.

So my requirement now is i have to find out where we get this infomation about temp tablespace and whether in bw does it use some internal tables, structures etc to store this temp tablespace, and i have get this in my load fm to incorporate so that i have keep track how this temp tablespace is filled etc.

So that's why i wanted to know where the temp tablespace is stored in bw for infoproviders.

Any help will be of great use to me.

Thanks,

Prabs.

[Former Member]

What DB are you using for BW? I'll assume Oracle.

How are you getting the data out of BW?

If it involves a query being run against the InfoProvider, then it will certainly use space in the Oracle Temp tablespace. Queries use temp space to hold all the interim result sets of table joins, sorts, etc. How much temp space a given query will use will involve a number of things, one of which is the number of rows in the InfoProvider, how many other tables are joined to in the query, how many rows are in those tables, what type of joins are done, how many columns are begin selected, how many sorts, merges or are performed, etc.

Other than running for a smaller number of records and having a DBA monitor the temp space usage, I don't know how you would come up with an estimate for temp space usage, and then the full extract would have to use the exact same execution plan to make that approach valid.

You could certainly spend time with this, but all you might conclude is that you need to allocate more temp space. So I think the more practical apporaches are to just add more temp space in order to get the data out and then adjust it back to where it was, or just extract it in smaller chunks, which probably has otjher benefits as well, e.g. smaller batches of data with more predictable times to complete the conversion steps.

Curious, are you abandoning the BW? If so, can you share why?

[Former Member]

Hi Prabhakaran,

The DEFAULT TEMP TABLESPACE is PSAPTEMP.

Usually PSA data is stored in PSAPODSD tablespace.

If you created your data classes, Do ensure you have a look at those tablespaces too. These usually end with PSAP****D.

Transaction DB02 is a good place to find more information about the tablespaces and others.

Usually, the size of the TEMP tablespace which SAP recommends is TWICE the size of the largest CUBE. TEMP tablespace is used for various activities like sorting data if mem is not enough, etc.

Regards,

GPK

[Former Member]

Hi Praveen,

Could u please let me know where these temp tablespaces are stored and declared whether like and internal tables or some structures etc

<b>TEMP tablespace is used for various activities like sorting data if mem is not enough, etc</b>

I want the information about temp tablespace and capture the memory usage it does.

I searched in db02 through RSORAT0T , it gives only some information about internal tables etc.

Could u please let me know where they are declared for infocube and ods objects.

It will be of great use to me.

Thanks,

Prabs.

[Former Member]

They are temporary tables, they do NOT exist beyond the duration of the extract.

The database creates them in the the database's PSATEMP tablespace and deletes them when it is done with them.

You are not going to find a listing of them and sizes.

[Former Member]

Hi Prabhakaran,

The TEMP tablespace is created by the DBA's. In one DB INSTANCE there can be only one TEMP Tablespace. Usually the DBA's follow the below procedure

1. During Day Time the TEMP is reasonable

2. During nights the loads are huge and backups are huge, hence they have TEMP of very big size.

TEMP doesn't hold any data. Anything in it is for a temporary time period. It is the responsibility of the DBA to maintain the temp tablespace.

Coming to BW Developer, he is transparent in regards to this.

Regards

GPK.

[Former Member]

Hi Praveen,

I got ur point, but according to my requirement, I need to check or write a test program in abap to check how much size is occupied by this temp tablespace and how much of memory usage it is occupied, How do i find this I thought of some of the function modules.

Get_tablespace_names , get_tablespace_list, etc,

Do u have any idea of some of the function modules which will give information about the temp tablespace information in bw.

thanks in advance.

Prabs.

[Former Member]

Hi,

The best bet would be using DBA_TABLESPACE view. This view is accessable using a program on SE38. It looks like RSORA. (Sorry don't remember the name of the program exactly).

This is the strange req. I have ever heard.

Regards

GPK.