Skip to Content
avatar image
Former Member

Adding one or more fields to an existing ODS

Trying to understand the steps BW (3.x) goes through when we alter an existing ODS structure to include a couple new fields (Oracle 9i is the underlying database).

Business rules state that the fields only need to be available on a go forward basis (i.e. no data needs to be updated on the 'old' records).

From current experience, it appears that the ODS structure is unloaded to temp table (maybe? was not clear), the structure is altered, and all records that exist are issued an update set command that sets the new field(s) to default or nulls.

Is this accurate?  If so, is there an alternative to issuing such an update (time to perform is the issue). If there is no alternative, is it fair to say that once we create an ODS (or series of partitioned ODS's) and they fill with data over time, if we ever want to add a field or two going forward, we will have a very large conversion effort to contend with?

Obviously, we are hoping there is the equivalent of an alter table command (with nulls), rather than a large update set of commands (caused by an alter table statement with defaults).

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

1 Answer

  • Nov 28, 2003 at 03:43 PM

    Hi,

    according to my knowledge, the DDIC adjusts the table by ALTER TABLE .. ADD COLUMN statements followed by an UPDATE statement which sets the new fields to 0.

    For changes to key fields, this is not possible.

    Kind Regards,

    Alex Peter, SAP AG

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      My understanding of your post:

      There is no way to have BW skip the UPDATE statement.

      Assuming this:

      What is the recommendation for large ODS's in BW.  We are building a three year repository of data, and at any given point in the future, additional requirements may force the expansion of a physical table. Is the recommendation to sunset the old ODS's, and begin building new ones?

      Is the recommendation to convert all the old ODS's?

      Note - we are talking about a large repository here.  40 days into this three year history we had to add a field. It took 14 hours. I need to understand the approach necessary going forward into the multi TB size ODS's (each ODS is split logically and physically, and viewed by a multi provider).

      My guess is we need the RIG to come up with an approach here, but just checking quick before I go off on a wild goose chase.

      Thanks again!

      Mark