BO XI 3.1 SP3 issue related to daylight savings time change

Posted by & filed under Administration, SDK.

There is a quite serious issue in BO XI 3.1 SP3 related to the daylight savings time change. See the SAP note 1448881 for details.

After time change, BO generates thousands of failed instances for scheduled reports.  The CMS database is growing very fast. BO system becomes very slow and stops working because of lack of space in tablespace or on the disk.

The quickest way to check if you have the issue is to connect to CMS database and query the number of rows in cms_infoobjects6.

SELECT Count(*) FROM CMS_INFOOBJECTS6

The normal amount of rows is usually less then 10 thousands. If there are more then 100 thousands rows, the system is probably affected by the issue.

To resolve the issue the reports should be rescheduled and the failed instances should be removed (see the SAP note for details). The error is fixed in FP3.6.

Read more »

How to Obfuscate Password for Command Line Kettle

Posted by & filed under Kettle.

Pentaho Kettle does not provide possibility to obfuscate password in batch files (though the connection passwords are obfuscated in XML files).

You can write:

kitchen.bat /rep repos /job test /user admin /pass admin

but this will not work:

kitchen.bat /rep repos /job test /user admin /pass "Encrypted 2be98afc86aa7f2e4cb79ce71da9fa6d4"

Here "Encrypted 2be98afc86aa7f2e4cb79ce71da9fa6d4" is encrypted version of "admin". You can get such string using kettle tool encr.bat.

This post describes a workaround.

Solution

A solution is to create a java wrapper for Kitchen.

1. Create java file Kettle1.java in the Kettle root with the following code:

import org.pentaho.di.core.exception.KettleException;
import org.pentaho.di.kitchen.Kitchen;
import org.pentaho.di.core.encryption.Encr;

public class Kitchen1 {
   public static void main(String[] args) throws KettleException {
      // find argument /pass
      int i = 0;
      for (; i < args.length; ++i)
         if (args[i].equals("/pass") || args[i].equals("/password"))
            break;
      // decrypt password if necessary
      if (++i < args.length)
         args[i] = Encr.decryptPasswordOptionallyEncrypted(args[i]);
      // run Kitchen
      Kitchen.main(args);
   }
}

2. Open command line. Run kitchen.bat without any parameters. This will initialize the necessary environment variable CLASSPATH.

3. Compile the java file with the following command:

javac Kitchen1.java

4. Copy Kitchen.bat to Kitchen1.bat and correct the last line. Replace

java %OPT% org.pentaho.di.kitchen.Kitchen %_cmdline%

with

java %OPT% Kitchen1 %_cmdline%

Now you can use Kitchen1.bat instead of Kitchen.bat. Both commands will work:

kitchen1.bat /rep repos /job test /user admin /pass admin
kitchen1.bat /rep repos /job test /user admin /pass "Encrypted 2be98afc86aa7f2e4cb79ce71da9fa6d4"

5. Remove source file Kitchen1.java. Close command line.

Have fun :)

How to Restore a Table in Oracle

Posted by & filed under Oracle.

Simple and easy to use querying tool for Oracle SQL Tools has a little drawback. Trying to remove search string from the filter, you might hit Del. This does not remove the search string but drops the table. Of course it asks confirmation. But if you accidentally dropped the table, the following command might help:

FLASHBACK TABLE table_name TO BEFORE DROP;

The latest version of the tool (1.6) has a nice feature – read-only connection to database.

Fun with Query Builder

Posted by & filed under SDK.

I cannot explain this.

SELECT SI_ID, SI_NAME, SI_CUID FROM CI_INFOOBJECTS WHERE SI_ID=23
1/1
Properties
SI_NAME Root Folder
SI_CUID ASHnC0S_Pw5LhKFbZ.iA_j4
SI_ID 23
SELECT SI_ID, SI_NAME, SI_CUID, SI_PARENTID, SI_PARENT_CUID
FROM CI_INFOOBJECTS WHERE SI_PARENTID=23
1/10
Properties
SI_NAME Report Conversion Tool
SI_CUID AY9zJ8BgaF9OucZ2h2slcJM
SI_PARENT_CUID ASHnC0S_Pw5LhKFbZ.iA_j4
SI_PARENTID 0
SI_ID 123

Why the hell SI_PARENTID=0 in the result if SI_PARENTID=23 is in the query restriction?

How to Generate Random Data in Oracle

Posted by & filed under Oracle.

DROP TABLE t;
CREATE TABLE t (
  x VARCHAR(20),
  y NUMERIC(20,2),
  z INT
);

BEGIN
  FOR i IN 1..20000 LOOP
    INSERT INTO t VALUES (
      dbms_random.string('L', 20),
      dbms_random.Value,
      dbms_random.value(1,1000)
    );
  END LOOP;
  COMMIT;
END;
/

Hierarchies in Webi

Posted by & filed under Web Intelligence.

The task is to display a fixed level hierarchy together with totals for each level. This can be done using breaks. The most straightforward implementation might look like this:

If there are many levels and the level names are long, the table will be very wide, with a lot of empty space. It would be better to have more compact layout, for example:

This post describes how to implement such layout. Read more »

Loading Fact Tables with PyGramETL

Posted by & filed under Data Warehousing.

The previous post described how to load dimension tables with PyGramETL. This one is about fact tables.

The source CVS file has the following structure:

cust_id;res_date;future_guests;future_revenue
105;2008/04/01 00:00:00.000;2;300
106;2007/04/17 00:00:00.000;4;380
106;2007/04/17 00:00:00.000;4;768
...

Fact Table

CREATE TABLE reservation_fact(
    customer_sk INT,
    reservation_date DATETIME,
    future_guests FLOAT,
    future_revenue FLOAT
)

Kettle

PyGramETL

Read more »

Loading Dimension Tables with PyGramETL

Posted by & filed under Data Warehousing.

There is a promising Python framework for ETL – PyGramETL (http://pygrametl.org/)

This post demonstrates an example how to import data from a CSV file and load it into a dimension table using PyGramETL in comparison with the same functionality implemented in a Kettle transformation.

The input file customer.txt has the following structure:

customer_id;first_name;last_name;age;phone_number;address
101;Paul;Brendt;19;(212) 555 2146;10 Jasper Blvd.
102;Robin;McCarthy;29;(214) 555 3075;27 Pasadena Drive
103;Peter;Travis;34;(510) 555 4448;7835 Hartford Drive
...

Dimention Table

MySQL is used for data warehouse. The following code create the dimension table.

CREATE TABLE customer_dim
(
    customer_sk INT,
    customer_id INT,
    valid_from DATETIME,
    valid_to DATETIME,
    version INT,
    phone_number VARCHAR(20),
    first_name VARCHAR(20),
    last_name VARCHAR(20),
    age INT,
    address VARCHAR(50)
)

INSERT customer_dim (customer_sk, version) VALUES (0, 1)

Kettle

Let's consider how this can be done in Kettle

The transformation has two steps:

The first step "Text file input" reads the CSV file. The second step "Dimensional lookup/update" generates surrogate key customer_sk as well as technical fields: valid_from, valid_to, version. The update type for first_name and last_name is set to Punch through.

PyGramETL

The same can be done with the following code using PyGramETL

import MySQLdb
import pygrametl
from pygrametl.datasources import CSVSource
from pygrametl.tables import SlowlyChangingDimension

myconn = MySQLdb.connect(user='test', passwd='test', db='test')

connection = pygrametl.ConnectionWrapper(myconn)
connection.setasdefault()

customer_dim = SlowlyChangingDimension(
    name='customer_dim',
    key='customer_sk',
    attributes=['customer_id', 'valid_from', 'valid_to', 'version',
                'first_name', 'last_name', 'age', 'phone_number', 'address'],
    lookupatts=['customer_id'],
    versionatt='version',
    fromatt='valid_from',
    toatt='valid_to',
    type1atts=('first_name', 'last_name'),
    cachesize=-1)

customer_dim.defaultidvalue = 0

def load_customer_dim():
    customer = CSVSource(file('./customer.txt', 'r', 16384), delimiter=';')
    for row in customer:
        printrow
        customer_dim.scdensure(row)
    connection.commit()

def main():
    load_customer_dim()

if __name__ == '__main__':
    main()

On the one hand, it is much easier to get overview of a transformation in Kettle. On the other hand, when the ETL transformation is small, it is much easier to see details in the Python code because you do not have to open each step to see its settings.