Skip to Content
Author's profile photo Marc Kuipers

SAP FC – Archiving; what technically happens to the consolidation tables

Introduction

 

You can use the Archiving Tool to store historical consolidated data in archive databases and still access it from the Financial Consolidation application.

This document shows what technically happens at DB level to the tables

 

Procedure

 

The consolidations in FC are stored in ct_coXXXX tables. The XXXX indicates a unique identifier and the data for each consolidation definition will be stored in one single table.

 

You can determine what table holds the data by using this SQL

 

/* Consolidated */

select p.id, a.name as ‘category’,

ltrim(str(1900+(p.updper&536608768)/262144)) + ‘.’ +

right(’00’+ltrim(str((p.updper&253952)/8192)),2) as ‘period’,

  1. b.name as ‘scope’,
  2. c.name as ‘variant’,
  3. d.name as ‘currency’

from ct_coref p, ct_phase a, ct_scope_code b, ct_variant c, ct_curncy d

where a.id=p.phase and

  1. p.scope = b.id and
  2. p.variant=c.id and
  3. p.curncy = d.id

 

 

For example, the consolidation data for {A – 2016.01 – SAP – SAP – USD} is stored in ct_co0050

 

/wp-content/uploads/2016/02/p1_891540.jpg

 

 

/wp-content/uploads/2016/02/p2_891541.jpg

When you archive (for full documentation, see section “12 Archiving tool” of the “SAP BusinessObjects Financial Consolidation Administrator’s Guide”

you first connect to the source database (and also specify the target database, in my example FC_BAK)

 

/wp-content/uploads/2016/02/p3_891557.jpg

 

You then select the consolidation(s) you want to archive.

In my example, I take the consolidation {A – 2016.01 – SAP – SAP – USD}

 

/wp-content/uploads/2016/02/p4_891558.jpg

 

When selecting “Execute” the system will show what table will be backed up. Here you can verify that the correct ct_coXXXX table is selected (in this example, it is ct_co0050)

 

/wp-content/uploads/2016/02/p5_891559.jpg

 

The archiving completes (100%)

 

/wp-content/uploads/2016/02/p6_891560.jpg

 

What has been done in DB’s

 

1. In the original DB (FC10) the table has been deleted

 

/wp-content/uploads/2016/02/p7_891561.jpg

 

2. In the backup database (FC_BAK, in this example), the table has been created

 

/wp-content/uploads/2016/02/p8_891562.jpg

 

3. In the original DB, there is now a ‘view’ ct_co0050 which is linked to the archived table (i.e. a view to a different DB schema)

 

The archive tool uses this during the ‘archive’ process

 

CREATE VIEW dbo.viewname

AS

  SELECT column_names

    FROM [AnotherServer].[AnotherServerDatabase].dbo.[Table1];

 

/wp-content/uploads/2016/02/p9_891566.jpg

 

Result

 

The result is that on the original database, the archiving of the consolidation table is completely transparent

If you run an SQL (e.g. FC report, Cube Designer deployment), the result will still be the same, although the table is physically in a different DB

 

/wp-content/uploads/2016/02/e_891568.jpg

Assigned Tags

      7 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Vielen Dank

      Author's profile photo Yvonne Benz
      Yvonne Benz

      Very helpful! Thank you.

      Author's profile photo Pedro Fernandes
      Pedro Fernandes

      Hey Marc,

      Can you also post it on the JAM.

      Cheers,

      PF

      Author's profile photo Marc Kuipers
      Marc Kuipers
      Blog Post Author

      Will do - thanks

      Jam L.

      Author's profile photo Rizwan Tahir
      Rizwan Tahir

      Thank you, Marc! This is indeed helpful clarification.

      - Rizwan

      Author's profile photo Former Member
      Former Member

      An excellent post and great explanation. Thank Marc,

      My question is, what are the benefits of archiving production database?

      Will there be a performance drop when users try to retrieve data?

      Author's profile photo Marc Kuipers
      Marc Kuipers
      Blog Post Author

      Hello Mojtaba

      The key benefit is that your DB will be smaller, so maintenance tasks like backup, will be a lot faster.

      Obviously, there will also be less disk space used (although you still need space for your archive DB)

      In my tests, there was no performance issue at all. Selecting data from a view that is connected to another physical DB will depend on the connection between the 2 DBs.

      Marc