Error ORA-00904: "S"."ROW_ID":invalid identifier in ODI 11g

When i tried to populate a dimension using incremental update and  mapped a data base sequence to ROW_ID column in target for surrogate key i got the following error.

ORA-00904: "S"."ROW_ID":invalid identifier

Solution:

When i checked the IKM used for this mapping it is IKM Oracle Incremental Update change this to IKM SQL Incremental Update then the mapping it will work properly.

Normalizer Transformation in Informatica

Normalizer Transformation

• Active and Connected Transformation
• Normalizer transformation convert  rows that having multiple occurring columns to a single row for each instance of multiple occurring data.

Scenario

We want to convert quarter sales in a shop to a single sales column.

Source 

Expected Output

Mapping using Normalizer Transformation.

Step 1:

Import Source and target to Source analyzer and Target Designer under Designer tab.

Step 2:

• Drag source and target to mapping designer and import normalizer transformation into the mapping.
• We can't drag ports directly form source qualifier to normalizer. to add ports we can use Normalizer tab.

• Add the output ports you need as output for this transformation.
• As we want to convert 4 Quarter sales to a single sales column output give Occurs of SALES to 4
• This will create 4 input ports and we can map this Quarter sales to this ports

• In transformation tab you can change the name of the transformation.

• Properties tab you can see two options Reset and Restart
• Reset option is used to reset the key value to the value that is used before the session.
• Restart option is used to start the generated sequence from 1 and restart for each session.
• Tracing level represents the amount of information that informatcia server writes in a log file.

Step 3:

• After creating ports and mapping input ports to the transformation you can map the transformation ports to the target
• You can see two additional ports are created in transformation 




• GK: Generated Key - The integration service increments the generated key sequence number each time it process a source row. When the source row contains a multiple-occurring column or a multiple-occurring group of columns, the normalizer transformation returns a row for each occurrence.



• GCID: Generated Column ID - The GCID is an index for the instance of the multiple-occurring data. For example, if a column occurs 3 times in a source record, the normalizer returns a value of 1,2 or 3 in the generated column ID.
• Map GCID colum to Quarter in target table

Step 4:

• Now save your mapping and generate a workflow and run your workflow

Step 5:

• You can check the status of your workflow in Workflow Monitor 

Thank you.

SSL Configuration in OBIEE

Steps to Convert a PFX Certificate into a JKS Certificate to configure it on OBIEE

Step 1:

Set up the environment for the domain, by executing the setDomainEnv.cmd script, typically located atC:\MW_HOME\user_projects\domains\bifoundation_domain\binsetDomainEnv.cmd 

Step 2:

Use OpenSSL to check the pfx certificate's content.[Note: OpenSSL is open source software to implement basic cryptographic functions and provides various utility functions]

Download openssl and set the path for openssl.exe openssl pkcs12 -in <certificate.pfx> -out KEYSTORE.pem –nodes Eg: openssl pkcs12 -in Companyname.pfx -out KEYSTORE.pem -nodes

At this point, a password for the pfx file will be requested. 

Expected output:openssl pkcs12 -in <certificate.pfx> -out KEYSTORE.pem -nodes Enter Import Password: MAC verified OK

Step 3:
Open KEYSTORE.pem file, from step 2. This should look similar to this: You will find three certificates on it and the private key:

Bag Attributes Microsoft Local Key set: <No Values> localKeyID: 01 00 00 00 friendlyName: le-36c42c6e-ec49-413c-891e-591f7e3dd306 Microsoft CSP Name: Microsoft RSA SChannel Cryptographic Provider Key Attributes X509v3 Key Usage: 10 -----BEGIN RSA PRIVATE KEY----- MIIEpQIBAAKCAQEAtPwoO3eOwSyOapzZgcDnQOH27cOaaejHtNh921Pd+U4N+dlm . . . EDITING . . . R5rsB00Yk1/2W9UqD9Nn7cDuMdilS8g9CUqnnSlDkSG0AX67auKUAcI= -----END RSA PRIVATE KEY----- Bag Attributes localKeyID: 01 00 00 00 friendlyName: *.something.com subject=/serialNumber=sj6QjpTjKcpQGZ9QqWO-pFvsakS1t8MV/C=US/ST=Missouri/L=CHESTERFIELD/O=Oracle_Corp, Inc./OU=Oracle/CN=*.something.com issuer=/C=US/O=GeoTrust, Inc./CN=GeoTrust SSL CA -----BEGIN CERTIFICATE----- MIIErzCCA5egAwIBAgIDAIH6MA0GCSqGSIb3DQEBBQUAMEAxCzAJBgNVBAYTAlVT . . . EDITING . . . wA5JxaU55teoWkuiAaYRQpuLepJfzw+qMk5i5FpMRbVMMfkcBusGtdW5OrAoYDL9 4rgR -----END CERTIFICATE----- Bag Attributes friendlyName: GeoTrust Global CA subject=/C=US/O=GeoTrust Inc. /CN=GeoTrust Global CA issuer=/C=US/O=GeoTrust Inc. /CN=GeoTrust Global CA -----BEGIN CERTIFICATE----- MIIDVDCCAjygAwIBAgIDAjRWMA0GCSqGSIb3DQEBBQUAMEIxCzAJBgNVBAYTAlVT . . . EDITING . . . 5fEWCRE11azbJHFwLJhWC9kXtNHjUStedejV0NxPNO3CBWaAocvmMw== -----END CERTIFICATE----- Bag Attributes: <Empty Attributes> subject=/C=US/O=GeoTrust, Inc. /CN=GeoTrust SSL CA issuer=/C=US/O=GeoTrust Inc. /CN=GeoTrust Global CA -----BEGIN CERTIFICATE----- MIID2TCCAsGgAwIBAgIDAjbQMA0GCSqGSIb3DQEBBQUAMEIxCzAJBgNVBAYTAlVT . . . EDITING . . . TpnKXKBuervdo5AaRTPvvz7SBMS24CqFZUE+ENQ= -----END CERTIFICATE-----

Step4:

Identify and store contents from KEYSTORE.pem certificate, to proceed and create jks files: At this point, you will find  three certificates on  KEYSTORE.pem and the private key. 4.1 Private Key.

To identify the private key, look for the following headings:-----BEGIN RSA PRIVATE KEY----- -----END RSA PRIVATE KEY----- Both above mentioned tags will be surrounded the private key. Go ahead and save the content of it into a file called: my_key_pk.pem. This has to include the headings.  Expected file:-----BEGIN RSA PRIVATE KEY----- MIIEpQIBAAKCAQEAtPwoO3eOwSyOapzZgcDnQOH27cOaaejHtNh921Pd+U4N+dlm . . . EDIT . . . Y4ZrW12PRa9/EOBGTG5teKAEada/K4yKReTyQQAGq6j5RjErmuuKkKgPGMSCjvMS R5rsB00Yk1/2W9UqD9Nn7cDuMdilS8g9CUqnnSlDkSG0AX67auKUAcI= -----END RSA PRIVATE KEY-----

4.2 Root Certificate.

To identify the Root Certificate, look for the following headings:subject=/C=US/O=GeoTrust Inc./CN=GeoTrust Global CA issuer=/C=US/O=GeoTrust Inc./CN=GeoTrust Global CA

Subject and issuer must be the same. Go ahead and save the content of it into a file called: my_key_root.pem. Include all the content from BEGIN CERTIFICATE TO END CERTIFICATE, both included.

4.3 Intermediate Certificate.

To identify an Intermediate Certificate, look for the following heading:subject=/C=US/O=GeoTrust, Inc./CN=GeoTrust SSL CA issuer=/C=US/O=GeoTrust Inc./CN=GeoTrust Global CA

Subject and issuer are different only on the CN. Go ahead and save the content of it into a file 

called:my_key_intermediate.pem. Include all the content from BEGIN CERTIFICATE TO END CERTIFICATE, both included.

NOTE: This certificate is optional and there are some cases where it'll not be present. If this is the case, go ahead and skip this step. In any other case, this needs to be added to the identity keystore jks file. 

4.4 Server Certificate.

 To identify a Server Certificate, look for the following heading:

friendlyName: some.thing.com
subject=/serialNumber=sj6QjpTjKcpQGZ9QqWO-pFvsakS1t8MV/C=US/ST=Missouri/L=CHESTERFIELD/O=Oracle_Corp, Inc./OU=Oracle/CN=some.thing.com

        A server certificate includes a heading called Friendly Name. Go ahead and save the content of it into a file called: my_key_crt.pem. Include all the content from BEGIN CERTIFICATE TO END CERTIFICATE, both included. 

Step 5:

Create a Trust Keystore and import the Root certificate into it.Run this commands from the file that contain your certificates

keytool -import -trustcacerts -file my_key_root.pem -alias my_key_root -keystore my_key_trust.jks -storepass <store_pass> -keypass <key_pass> 

Expected Output:

Certificate already exists in system-wide CA keystore under alias <geotrustglobalca> Do you still want to add it to your own keystore? [no]: yes Certificate was added to keystore Step 6: Generate an Identity Keystore and import Server into it. Java utils.ImportPrivateKey -keystore my_key_identity.jks -storepass <store_pass> -storetype JKS -keypass <key_pass> -alias server_identity -certfile my_key_crt.pem -keyfile my_key_pk.pem -keyfilepass <pfx_password>
 
With these instructions, two jks files will be produced:

·         my_key_identity.jks
·         my_key_trust.jks

With both files, the next step is to configure Custom Identity and Custom Trust on Weblogic

Steps to configure Custom Identity and Custom Trust with Weblogic

Step 1: Login to Weblogic Admin console 

Step 2: Click on Lock & Edit

Step 3: 
Environment --> Servers --> < server_name_where_ssl_has_to_be_configured >

Step 4: Select Admin Server

Step 5:

·         Configuration -> General --> SSL Listen Port Enabled ( Check )

·         Note: The SSL Listen Port to 7002(make sure the port is available), change it if required. 

Step 6:

·         Click on Keystores tab under " Configuration " tab :

·         Click on the drop down menu next to Keystores and select " Custom Identity and Custom Trust " 

·         Click on Save

Step 7:

Now fill the following information:

---Identity---  

Custom Identity Keystore: < location_of_identity_keystore_that_you_have_created>

NOTE: By default WLS will look for this keystore file in domain_home location.

 Custom Identity Keystore Type: jks

 Custom Identity Keystore Passphrase: < This_would_be_your_storepass >

 ---Trust---

 Custom Trust Keystore: < location_of_trust_keystore_that_you_have_created>

 NOTE: By default WLS will look for this keystore file in domain_home location.

 Custom Trust Keystore Type: jks

 Custom Trust Keystore Passphrase: < This_would_be_your_storepass >

Step 8:

Now save the changes and click on “SSL” tab:

·         Private Key Alias: < This_would_be_your_certificate_alias>

·         Private Key Passphrase: < This_would_be_your_keypass >

Step 9:

·         Save the changes and click on the " >Advanced " field under the “SSL” tab:  

·         Set the " Hostname Verification: " to none (from the drop down menu).

Step 10: 

Repeat the same steps for bi_server1 set the ssl listen port as 9804(make sure the port is available)

Step 11: Click on Activate changes

Step 12: 

Configure node manager for SSL

1. Stop the Node manager service

2. Update the nodemanager.properties file at the location MW_HOME/wlserver_10.3/common/nodemanager by updating the following properties:

ListenAddress=<fully_qualified_server_name>, 

SecureListener=true

CipherSuite=SSL_RSA_EXPORT_WITH_RC4_40_MD5

DomainRegistrationEnabled=true

3. Add the following properties to this file:

KeyStores=CustomIdentityAndCustomTrust

CustomIdentityKeyStoreType=JKS

CustomIdentityKeyStoreFileName =<Identity keystore location>

CustomIdentityAlias= <Alias of identity keystore>

CustomIdentityPrivateKeyPassPhrase=<Identity Private Key pass phrase>

CustomTrustKeyStoreFileName ==<Trust keystore location>

CustomIdentityKeyStorePassPhrase=<Key Store Passphrase>

Step 13: Save the file and restart all services

Step 14:

Install .pfx certificate on your server

·         Double click on the certificate

·         Click on next

Specify the file you want to import

·         Type your pfx Password

Select certificate store as Trusted Root Certification Authorities

·         Click Flinish

·         You will see the message as The import was successful

Step 15:

·         Now log into your OBIEE using the new address

·         https://Domain name in certificate:9804/analytics

·         https://Domain name in certificate:7002/console