Surrogate Key Generator In Datastage 8.1 With Example

Posted : admin On 18.04.2020

When using the Surrogate Key Generator Stage with Database Sequence, please note that before using the sequence values:

This will ensure the Datastage to create and remove the Surrogate key file everytime you run the job Now coming to the Transformer NextSurrogateKey function mapped against your target field which is supposed to generate the SK sequence will retrieve the value from the file.

VersionDownloadsLast updated3010,887,58717,0914,305,7211,886,7757/4/2/29/2/20/22/6/201836912/3/2/20//4//10/2/8/3/20174176/8/20173786/5/20174436/2/2017Info.last updated 1/15/2020.License:.(37.64 KB).Statistics.256,327 total downloads.10,881 downloadsof current version.244 downloadsper day (avg)Owners.Authorsmattosaurus TagsShare. Https wp2pgpmail com pgp key generator windows.

Answer / murali. Yes ur right but we have another way to generate s key by using transformer.in transformer we have a surrogate key tab click the skey tab in that we hav state file and db sequence. Answer / suresh. Surrogate key is alternate to primary key,it allows duplicates also,while maintain the hystorical data we use surrogate key. For example, a table that includes information on employees may use Social Security Number as the primary key. This type of key is called a natural key. The second is to use a new field with artificially-generated values whose sole purpose is to be used as a primary key. This is called a surrogate key. A surrogate key has the following. May 30, 2014  5) It is not visible to end users. End users should not see a surrogate key in a report. I will not go in details of surrogate key, I will post it later, But we will see how to generate surrogate keys in DataStage. Encryption key generator. You can use a Surrogate Key Generator stage to perform the following tasks: Create or delete the key source before other jobs run.


1. The 'Sequence' needs to be created in the database in order to use it. Sequence creation script:
CREATE SEQUENCE Sequence_Name INCREMENT BY 1 START WITH 1 NOMAXVALUE CACHE 10;
2. For a Database sequence, note here to use appropriate action with respect to the following:
CYCLE:
Specify CYCLE to indicate that the sequence continues to generate values after reaching either its maximum or minimum value. After an ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum value.
Specify NOCYCLE to indicate that the sequence cannot generate more values after reaching its maximum or minimum value. This is the default.
3. Test the Sequence on the database server side with script:
4. Create sequence environmental variable on DataStage server side, to make the stage/job reusable
5. Use the following in the Surrogate Key Generator Stage:
Source Name =#db_server#.#db_name#.#schema#.<sequence_name>
The source name here should not be the<table_name> but the oracle sequence name, which we had created in the above steps.
6. Alternatively, In the Target DB Stage you can when simplyinserting into your target table, use <sequence name>.nextvalin your insert statement.
Thanks!
Slowly Changing Dimensions (SCDs) are dimensions that have data that changes slowly, rather than changing on a time-based, regular schedule.
The Type 1 methodology overwrites old data with new data, and therefore does not track historical data at all.
Here is an example of a database table that keeps supplier information:
Supplier_KeySupplier_CodeSupplier_NameSupplier_State
123ABCAcme Supply CoCA
In this example, Supplier_Code is the natural key and Supplier_Key is a surrogate key. Technically, the surrogate key is not necessary, since the table will be unique by the natural key (Supplier_Code). However, the joins will perform better on an integer than on a character string.
Now imagine that this supplier moves their headquarters to Illinois. The updated table would simply overwrite this record:
Supplier_KeySupplier_CodeSupplier_NameSupplier_State
123ABCAcme Supply CoIL
The Type 2 method tracks historical data by creating multiple records for a given natural key in the dimensional tables with separate surrogate keys and/or different version numbers. With Type 2, we have unlimited history preservation as a new record is inserted each time a change is made.
In the same example, if the supplier moves to Illinois, the table could look like this, with incremented version numbers to indicate the sequence of changes:
Supplier_KeySupplier_CodeSupplier_NameSupplier_StateVersion
123ABCAcme Supply CoCA0
124ABCAcme Supply CoIL1
Another popular method for tuple versioning is to add effective date columns.
Supplier_KeySupplier_CodeSupplier_NameSupplier_StateStart_DateEnd_Date
123ABCAcme Supply CoCA01-Jan-200021-Dec-2004
124ABCAcme Supply CoIL22-Dec-2004
The null End_Date in row two indicates the current tuple version. In some cases, a standardized surrogate high date (e.g. 9999-12-31) may be used as an end date, so that the field can be included in an index, and so that null-value substitution is not required when querying.

Surrogate Key Generator In Datastage 8.1 With Example Pdf

Step 1: Create a datastage job with the below structure-
  1. Source file that comes from the OLTP sources
  2. Old dimesion refernce table link
  3. The SCD stage
  4. Target Fact Table
  5. Dimesion Update/Insert link
Step 2: To set up the SCD properties in the SCD stage ,open the stage and access the Fast Path
Step 3: The tab 2 of SCD stage is used specify the purpose of each of the pulled keys from the referenced dimension tables.
Step 4: Tab 3 is used to provide the seqence generator file/table name which is used to generate the new surrogate keys for the new or latest dimesion records.These are keys which also get passed to the fact tables for direct load.
Step 5: The Tab 4 is used to set the properties for configuring the data population logic for the new and old dimension rows. The type of activies that we can configure as a part of this tab are:
  1. Generation the new Surrogate key values to be passed to the dimension and fact table
  2. Mapping the source columns with the source column
  3. Setting up of the expired values for the old rows
  4. Defining the values to mark the current active rows out of multiple type rows

Surrogate Key Generator In Datastage 8.1 With Example Software

Step 6: Set the derivation logic for the fact as a part of the last tab.

Surrogate Key Generator In Datastage 8.1 With Example For Kids

Step 7: Complete the remaining set up, run the job