Advertisements

The CONCATENATE statement, concat() function, and string concatenation operator (&) in QlikView

In QlikView, there is the CONCATENATE statement, the CONCAT() function, and the string concatenation operator (the ampersand – &).  These are all very different and in this post I will explain each of them.

CONCATENATE statement

The CONCATENATE statement is used in conjunction with the LOAD statement.  It appends the rows of one table to another.  It is similar to a UNION ALL statement in SQL.

Let’s take a look at a couple examples using the following tables:

Employee_Location

Employee_ID Employee_Name Employee_Location
1 John NY
2 Mary NJ

Employee_Office

Employee_ID Employee_Name Employee_State
3 Jane FL
4 Evan NY

Employee_Position

Employee_ID Employee_Name Employee_Position
5 Paul Cashier
6 Sonia Salesperson

If we concatenated the Employee_Location and Employee_Office tables using the following load script …

[Employee_Location]:
 LOAD
 [Employee_ID]       as [%Employee ID],
 [Employee_Name]     as [Employee Name],
 [Employee_Location] as [Employee Location]
 FROM [… data source details for Employee_Location …]

CONCATENATE (Employee_Location)
 LOAD
 [Employee_ID]        as [%Employee ID],
 [Employee_Name]      as [Employee Name],
 [Employee_State]     as [Employee Location]  --aliased column
 FROM [… data source details for Employee_Office …]

We would get this result …

Employee_Location

Employee ID Employee Name Employee Location
1 John NY
2 Mary NJ
3 Jane FL
4 Evan NY

Now, if we concatenated the Employee_Location and Employee_Position tables using the following script…

[Employee_Information]:
 LOAD
 [Employee_ID]       as [%Employee ID],
 [Employee_Name]     as [Employee Name],
 [Employee_Location] as [Employee Location]
 FROM [… data source details for Employee_Location …]

CONCATENATE (Employee_Information)
 LOAD
 [Employee_ID]        as [%Employee ID],
 [Employee_Name]      as [Employee Name],
 [Employee_Position]  as [Employee Position]
 FROM [… data source details for Employee_Position …]

We would get this result …

Employee_Information

Employee ID Employee Name Employee Location Employee Position
1 John NY  
2 Mary NJ  
5 Paul   Cashier
6 Sonia   Salesperson

Notice that the concatenation works even if the tables do not have the same number of columns.  This provide more flexibility than the UNION or UNION ALL statements in SQL where you need to add a dummy column to the select list of your first table before performing the union.

Concat() function

The concat() function concatenates all the values of a column into a single delimited string.  The column and the delimiter are specified as parameters in the function.  You also have the option of producing the result string with only distinct values.

For example, if you have the following table …

Product_ID Product_Description Product_Category
1212 Pen Office Supplies
3214 Paper Paper
1345 Sharpener Office Supplies
1177 Eraser Office Supplies
2780 Calculator Electronics
2901 Computer Electronics
This statement: CONCAT(Product_Category, ‘,’)

Produces: Electronics, Electronics, Office Supplies, Office Supplies, Office Supplies, Paper
Notice there is a space after the comma in the delimiter, and therefore, there is a space after the comma in the output

This statement:   CONCAT(Product_Category, ‘|’)

Produces: Electronics|Electronics|Office Supplies|Office Supplies|Office Supplies|Paper
Notice there is no space in the delimiter, and therefore, no space between the values in the output

This statement:  CONCAT(DISTINCT Product_Category, ‘ | ’)

Produces:             Electronics | Office Supplies | Paper
Notice spaces in the delimiter, and distinct output.

Concatenation operator ( & )

When you need to concatenate 2 strings together, you use the concatenation operator – the ampersand (&).  For example, if you have address values in separate columns as in the table below …

Street City State Zip
123 Main St Orlando FL 32801

… you can output the address as one concatenated string by using the concatenation operator as shown in the script below …

[Street] & ‘, ‘ & [City] & ‘, ‘ & [State] & ‘ ‘ & [Zip]

[Notice a comma and a space is concatenated between Street, City and State; 
and only a space is concatenate between State and Zip]

… and that would produce the following result …
123 Main St, Orlando, FL 32801

Advertisements

Oracle Advanced Security Summary

With the expansion of Self-Service BI, BI Teams need to be more vigilant about protecting sensitive data.
This is a summary of options available for protecting data in Oracle databases.
The information in this post was found here and summarized for a quick read: https://docs.oracle.com/database/121/ASOAG/toc.htm

The 3 features available are (1) Transparent Data Encryption, (2) Data Redaction, and (3) Data Masking and Subsetting Pack.
Here is a quick summary.

(1) Transparent Data Encryption (TDE)

  • Encrypt data so only authorized people can see it
  • Use it to protect sensitive data that maybe in an unprotected environment, such backup data sent to a storage facility
  • You can encrypt an individual column or an entire tablespace
  • Applications using encrypted data can function just the same

(2) Data Redaction

  • Enable the redaction (masking) of column data in tables
  • Redaction can be full, partial, based on regular expressions, or random
    • Full redaction: replaces strings with a single blank space ‘ ‘; numbers with zero (0); dates with 01-JAN-01
    • Partial redaction: replaces a portion of the column data; for example SSN: ***-**-1234
    • Regular expressions: can be used to perform partial or full redactions
    • Random: generates random values for display when accessed
  • The redaction takes place at runtime; not in the permanent data stored

(3) Oracle Enterprise Manager Data Masking and Subsetting Pack

  • enables you to create a “safe” development or test copy of the production database

 

Let’s look into some more details …

(1) Transparent Data Encryption (TDE)

  • TDE uses a two-tiered key-based architecture
  • TDE column encryption uses the two-tiered key-based architecture to transparently encrypt and decrypt sensitive table columns. The TDE master encryption key is stored in an external security module, which can be an Oracle software keystore or hardware keystore. This TDE master encryption key encrypts and decrypts the TDE table key, which in turn encrypts and decrypts data in the table column.
  • A Key Management Framework is used for TDE to store and manage keys and credentials.
    • Includes the keystore to store the TDE master encryption keys and the management framework to manage keystore and key operations
    • The Oracle keystore stores a history of retired TDE master encryption keys, which enables you to change them and still be able to decrypt data that was encrypted under an earlier TDE master encryption key.
  • Types of Keystores
    • Software keystores
    • Hardware, or HSM-based, keystores
  • Types of Software Keystores:
    • auto-login software keystores that are local to the computer on which they are created.
    • cannot be opened on any computer other than the one on which they are created.
    • typically used for scenarios where additional security is required while supporting an unattended operation
    • Password-based software keystores
      • protected by using a password that you create. You must open this type of keystore before the keys can be retrieved or used.
    • Auto-login software keystores
      • protected by a system-generated password, and do not need to be explicitly opened; automatically opened when accessed.
      • can be used across different systems; ideal for unattended scenarios.
    • Local auto-login software keystores
  • Steps for configuring a Software Keystore
    • Step 1: Set the Software Keystore Location in the sqlnet.ora File
    • Step 2: Create the Software Keystore
    • Step 3: Open the Software Keystore
    • Step 4: Set the Software TDE Master Encryption Key
    • Step 5: Encrypt Your Data
  • Oracle Database checks the sqlnet.ora file for the directory location of the keystore, whether it is a software keystore or a hardware module security (HSM) keystore.
  • You cannot change an existing tablespace to make it encrypted
  • You can create or modify columns to be encrypted

 

(2) Data Redaction

  • Define data redaction policies to specify what data needs to be redacted
  • Use policy expressions to set whether a user sees the redacted data or the full data
  • Policy Procedures
    • DBMS_REDACT.ADD_POLICY
    • DBMS_REDACT.ALTER_POLICY
    • DBMS_REDACT.ENABLE_POLICY
    • DBMS_REDACT.DISABLE_POLICY
    • DBMS_REDACT.DROP_POLICY
  • Sample scrip
    • BEGIN
      DBMS_REDACT.ADD_POLICY(
      object_schema => ‘hr’,
      object_name => ’employees’,
      column_name => ‘commission_pct’,
      policy_name => ‘redact_com_pct’,
      function_type => DBMS_REDACT.PARTIAL, –partial;  use DBMS_REDACT.FULL for full
      function_parameters => DBMS_REDACT.REDACT_US_SSN_F5,  — many standard params, but it can also be custom
      expression =>  ‘SYS_CONTEXT(”SYS_SESSION_ROLES”,”MGR”) = ”FALSE”’); –allows MGR role to see data
      policy_description => ‘Partially redacts 1st 5 digits in SS numbers’,
      column_description => ‘ssn contains Social Security numbers’);
      END;
      /
  • Use DBMS_REDACT.ALTER_POLICY and action => DBMS_REDACT.ADD_COLUMN to redact multiple columns
  • Redaction takes place on select lists and not on where clauses
  • Be aware of the scenarios when using redacted tables to build other tables or views

 

(3) Oracle Enterprise Manager Data Masking and Subsetting Pack (DMSP)

  • DMSP enables you to create a development or test copy of the production database, by taking the data in the production database, masking this data in bulk, and/or creating a subset of the data, and then putting the resulting masked data and/or subset of data in the development or test copy.
  • You can still apply Data Redaction policies to the non-production database, in order to redact columns
  • Used to mask data sets when you want to move the data to development and test environments.
  • Data Redaction is mainly designed for redacting at runtime for production applications

——–

I hope you found this helpful to get you started on taking the steps to protect your data internally and externally.
You can visit the link I provided above to find more details.