Schedule the Reports using alert mail in OBIEE 11g?

To achieve this, we have to deploy the mail details in Enterprise Manager & we have to create agent in analytics for scheduling the report.

Deploy the mail details in Enterprise Manager

Go to the http://localhost:7001/em . configure mail settings as per below

Configure Email settings:

Step1): Login to Fusion Middleware Control Enterprise manager (http://yourservername:7001:/em) using Admin user credentials

Step2):Navigate to Mail tab (Business Intelligence > coreapplication > Deployment>Mail

Step3):Click Lock and Edit Configuration to enable changes to be made.

What is Business intelligence & Why?

Business intelligence tools are a type of application software designed to retrieve, analyze, transform and report data for business intelligence. The tools generally read data that have been previously stored, often, though not necessarily, in a data warehouse or data mart.

What is ETL?. & Its Advantages

ETL, or Extract, Transform and Load, eases the combination of heterogeneous sources into a unified central repository. Usually this repository is a data warehouse or mart which will support enterprise business intelligence.

Extract - read data from multiple source systems into a single format. This process extracts the data from each native system and saves it to one target location. That source data may be any number of database formats, flat files, or document repositories. Usually, the goal is to extract the entire unmodified source system data, though certain checks and filters may be performed here to ensure the data meets an expected layout or to selectively remove data (e.g. potentially confidential information).

Transform - in this step, the data from the various systems is made consistent and linked. Some of the key operations here are:

Standardization - data is mapped to a consistent set of lookup values (e.g. US, USA, United States and blank/null - all mapped to the standard ISO country code)

Cleansing - perform validity checks and either remove or modify problem data

Surrogate keys - new key values applied to similar data from different source systems prevent key collisions in the future and provide a cross reference across these systems

Transposing - organizes data to optimize reporting. Many source systems are optimized for transactional performance but the warehouse will be primarily used for reporting. Often this involves denormalizing and re-organizing into a dimensional model.

Load - the transformed data is now written out to a warehouse/mart. The load process will usually preserve prior data. In some instances existing warehouse data is never removed, just marked as inactive. This provides full auditing and supports historical reporting.

YTD, MTD date calculated functions in obiee 11g?

Current Date: 

Syntax:
TIMESTAMPADD(SQL_TSI_DAY, 0, CURRENT_DATE) 
Or

Current_Date

Current Year:

Syntax:

YEAR(CURRENT_DATE)

Current Month:

Syntax:

MONTH(CURRENT_DATE)

Current Week:

Syntax:

WEEK(CURRENT_DATE)

Current Month Number: 

Syntax:
MONTH(CURRENT_DATE)

Current Quarter Number:

Syntax:
QUARTER_OF_YEAR(CURRENT_DATE)

Current Year Number:

Syntax:
YEAR(CURRENT_DATE)

Current Month Name:

Syntax;
MONTHNAME(CURRENT_DATE)

Week of the Year

Syntax:

WEEK_OF_YEAR(CURRENT_DATE)

Last Year:

Syntax:

TIMESTAMPADD(SQL_TSI_Year, -1,CURRENT_DATE) 

Last Month:

Syntax:

TIMESTAMPADD(SQL_TSI_Month, -1,CURRENT_DATE)

Last Year Same Period:

Syntax:

1.TIMESTAMPADD(SQL_TSI_YEAR, -1, CURRENT_DATE)
2.SELECT TO_CHAR(ADD_MONTHS(SYSDATE, -12), 'YYYYMMDD') FROM DUAL;
3.SELECT DISTINCT BUSINESS_DATE,TO_CHAR(ADD_MONTHS(BUSINESS_DATE, -12), 'YYYYMMDD') FROM W_GL_BAL_CRF_F CRF

Last Year-First Day Of Current Month;

Syntax:

TIMESTAMPADD(SQL_TSI_YEAR, -1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE) )

Last Year-Last Day Of Current Month;

Syntax:

TIMESTAMPADD(SQL_TSI_YEAR, -1, TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))) )

Converting Char To Date :

Syntax:

1.CAST( SUBSTRING(CAST(CURRENT_DATE AS CHAR) FROM 1 FOR 4) ||  '/07/31/'  AS DATE)

2.CAST('31-Jul-' || TRIM(BOTH ' ' FROM  CAST(      YEAR("F_PL_BAL"."BUSINESS_DATE")       AS CHAR) ) AS DATE)

Current Week End:

Syntax:

TIMESTAMPADD(SQL_TSI_DAY, 7-DAYOFWEEK(MAX(current_date)), MAX(current_date))

Previous Week End

Syntax:

TIMESTAMPADD(SQL_TSI_DAY, 7-DAYOFWEEK(MAX(current_date))-7, MAX(current_date))

Covert Week Number to Date

Syntax:

week_of_year(TIMESTAMPADD(SQL_TSI_DAY, 7-DAYOFWEEK(MAX(current_date)), MAX(current_date)))

First Day of the Previous Year :

Syntax:
TIMESTAMPADD( SQL_TSI_YEAR , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE)) 

First Day of Previous Quarter:

Syntax:

TIMESTAMPADD( SQL_TSI_QUARTER , -1, TIMESTAMPADD( SQL_TSI_DAY , DAY_OF_QUARTER( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

First Day of the Previous Month :

Syntax:
TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)) 

First Day of the Previous Week:

Syntax:

TIMESTAMPADD(SQL_TSI_DAY, ((DAYOFWEEK(CURRENT_DATE) *-1)-6),CURRENT_DATE)

Last Day of Previous Year:

Syntax:
TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1,  CURRENT_DATE)) 

Last Day of Previous Quarter;

Syntax:
TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_DAY , DAY_OF_QUARTER( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

Last Day of the Previous Month:

Syntax:

TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)) 

Last Day of the Previous Week:

Syntax:
TIMESTAMPADD(SQL_TSI_DAY, (DAYOFWEEK(CURRENT_DATE) *-1),CURRENT_DATE) 

First Day of the Current Year :

Syntax:
TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE) 

First Day of Current Quarter:

Syntax:

TIMESTAMPADD( SQL_TSI_DAY , DAY_OF_QUARTER( CURRENT_DATE) * -(1) + 1, CURRENT_DATE) 

First Day of the Current Month :

Syntax:

TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE) 

First Day of the Current Week:

Syntax:

TIMESTAMPADD(SQL_TSI_DAY, ((DAYOFWEEK(CURRENT_DATE) *-1)+1),CURRENT_DATE)

Last Day of Current Year:

Syntax:

TIMESTAMPADD(SQL_TSI_YEAR, 1, TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE))) 

Last Day of Current Quarter:

Syntax:

TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_QUARTER , 1, TIMESTAMPADD( SQL_TSI_DAY , DAY_OF_QUARTER( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))) 

Last Day of Current Month:

Syntax:

TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))) 

Last Day of Current Week:

Syntax:

TIMESTAMPADD(SQL_TSI_DAY, ((DAYOFWEEK(CURRENT_DATE) *-1)+7),CURRENT_DATE) 

First Day of the Next Year:

Syntax:

TIMESTAMPADD( SQL_TSI_YEAR , 1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE)) 

First Day of Next Quarter:

Syntax:

TIMESTAMPADD( SQL_TSI_QUARTER , 1, TIMESTAMPADD( SQL_TSI_DAY , DAY_OF_QUARTER( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

First Day of the Next Month :

Syntax:

TIMESTAMPADD(SQL_TSI_MONTH, 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

First Day of the Next Week;

Syntax: 

TIMESTAMPADD(SQL_TSI_DAY, ((DAYOFWEEK(CURRENT_DATE) *-1)+8),CURRENT_DATE)

Last Day of the Next Year:

Syntax:

TIMESTAMPADD(SQL_TSI_YEAR, 2, TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE))) 

Last Day of Next Quarter:

Syntax:

TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_QUARTER , 2, TIMESTAMPADD( SQL_TSI_DAY , DAY_OF_QUARTER( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))

Last Day of the Next Month:

Syntax:

TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 2, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))) 

Last Day of the Next Week:

Syntax:

 TIMESTAMPADD(SQL_TSI_DAY, ((DAYOFWEEK(CURRENT_DATE) *-1)+14),CURRENT_DATE)

Week End Date:

Syntax:

TIMESTAMPADD(SQL_TSI_DAY, 7-DAYOFWEEK(MAX(“Dim – Date”.” Date Column”)), MAX(“Dim – Date”.”Date Column”))

DAYOFWEEK returns an integer in the range of 1 to 7. A value of 1 represents the Monday.

Week Start Date:

Syntax:

TIMESTAMPADD(SQL_TSI_DAY,- DAYOFWEEK(MIN(“Dim – Date”.” Date Column”)) + 1, MIN(“Dim – Date”.” Date Column”))

Current Month End:

Syntax:

TIMESTAMPADD(SQL_TSI_DAY,DAYOFMONTH(MAX(current_date)) * -1 , TIMESTAMPADD(SQL_TSI_MONTH, 1, MAX(current_date)))

Previous  Month End:

Syntax:

TIMESTAMPADD(SQL_TSI_DAY,DAYOFMONTH(MAX(current_date)) * -1 , TIMESTAMPADD(SQL_TSI_MONTH, 0, MAX(current_date)))

Current Week  End:

Syntax:

TIMESTAMPADD(SQL_TSI_DAY, 7-DAYOFWEEK(MAX(current_date)), MAX(current_date))

Previous  Week  End:

Syntax:

TIMESTAMPADD(SQL_TSI_DAY, 7-DAYOFWEEK(MAX(current_date))-7, MAX(current_date))

Send odi error/ success log status to mail in odi 11g?

ODI Send mail

To achieve this, we have to create two procedures.

Procedure_1: Used for load odi logs into flat file &

Procedure_2: Used for send odi log file into mail.

Procedure_1:  Odi master repository (oracle) db have internal tables. An ODI internal table stores the odi execution details/odi logs. Those are

1. SNP_SESSION - stores the session_id and detailed related to session

2. SNP_SESS_STEP - All interface package, scenario and procedure details.

By using these tables I have chosen some columns like SESS_NO, JOB_TYPE, JOB_NAME, TABLE_NAME, JOB_BEGIN, JOB_END, TIME_IN_MINUTES, ERROR_MESSAGE, SESS_STATUS.

See the following view, it has end of the day log details only. 

Step1): Create view in DB

CREATE OR REPLACE FORCE VIEW ODI.ODI_LOG_STATUS
(
   SESS_NO,
   JOB_TYPE,
   JOB_NAME,
   TABLE_NAME,
   JOB_BEGIN,
   JOB_END,
   TIME_IN_MINUTES,
   ERROR_MESSAGE,
   SESS_STATUS
)
AS
   SELECT T1.SESS_NO,
          CASE
             WHEN T2.STEP_TYPE = 'F' THEN 'Interface'
             WHEN T2.STEP_TYPE = 'VD' THEN 'Variable declaration'
             WHEN T2.STEP_TYPE = 'VS' THEN 'Set//Increment variable'
             WHEN T2.STEP_TYPE = 'VE' THEN 'Evaluate variable'
             WHEN T2.STEP_TYPE = 'V' THEN 'Refresh variable '
             WHEN T2.STEP_TYPE = 'T' THEN 'Procedure'
             WHEN T2.STEP_TYPE = 'OE' THEN 'OS command'
             WHEN T2.STEP_TYPE = 'SE' THEN 'ODI Tool'
             WHEN T2.STEP_TYPE = 'RM' THEN 'Reverse-engineer model'
             WHEN T2.STEP_TYPE = 'CM' THEN 'Check model'
             WHEN T2.STEP_TYPE = 'CS' THEN 'Check sub-model'
             WHEN T2.STEP_TYPE = 'CD' THEN 'Check datastore'
             WHEN T2.STEP_TYPE = 'JM' THEN 'Journalize model
              WHEN T2.STEP_TYPE = 'JD' THEN 'Journalize datastore'
             ELSE 'UNKNOWN'
          END
             AS JOB_TYPE,
          T1.SESS_NAME AS JOB_NAME,
        T2.table_name AS TABLE_NAME,
          T1.SESS_BEG AS JOB_BEGIN,
          T1.SESS_END AS JOB_END,
          (T1.SESS_DUR / 60) AS Time_in_Minutes,
          T1.ERROR_MESSAGE AS ERROR_MESSAGE,
          CASE
             WHEN T1.SESS_STATUS = 'D' THEN 'Success'
             WHEN T1.SESS_STATUS = 'E' THEN 'Error'
             WHEN T1.SESS_STATUS = 'Q' THEN 'Queued'
             WHEN T1.SESS_STATUS = 'W' THEN 'Waiting'
             WHEN T1.SESS_STATUS = 'M' THEN 'Warning'
             ELSE 'UNKNOWN'
          END
             AS SESS_STATUS
     FROM SNP_SESSION T1, SNP_SESS_STEP T2
    WHERE T1.SESS_NO = T2.SESS_NO
          AND TO_CHAR (T1.SESS_BEG, 'dd-mm-yyyy') =
                 TO_CHAR (SYSDATE, 'dd-mm-yyyy');

Step2): Execute the query, select * from ODI_LOG_STATUS

Step3): Open Odi Studio.  Go to designer Navigator. Create new Project ,named as CASHFLOW. Select procedure. Create new procedure, named as log_data_error

Step4): select add command

Step5): In command on target, select technology: Odi Tools.  Using odiSqlUnload tool, load oracle table data into flat file without creating interface. See the following code

OdiSqlUnload "-FILE=D:\TEXT\error_log.txt" "-DRIVER=oracle.jdbc.OracleDriver" "-URL=jdbc:oracle:thin:@192.168.0.0:1521:odi" "-USER=odi" "-PASS=hpfHiT7Ql0Hd79KUseSWYAVIA" "-FILE_FORMAT=VARIABLE" "-FIELD_SEP= |  " "-ROW_SEP=\r\n"  "-DATE_FORMAT=yyyy/MM/dd HH:mm:ss" "-CHARSET_ENCODING=ISO8859_1" "-XML_CHARSET_ENCODING=ISO-8859-1"
 SELECT * FROM ODI_LOG_STATUS

Step6):  Execute this package. It creates the log file in the path of D:\TEXT\error_log.txt

Step7): see the output:

Procedure_2:  Used for send odi log file into mail.

Step1): Create new procedure in the same project. Named as error_mail

Step2): select add command

Step3): In command on target, select technology:Jython.  Using following  jython code, we can easily send log_data_error file to mail.

Command:

import smtplib, os
from email.MIMEMultipart import MIMEMultipart
from email.MIMEBase import MIMEBase
from email.MIMEText import MIMEText
from email.Utils import COMMASPACE, formatdate
from email import Encoders
FROM = 'kavya.arivemula@leratechnologies.com'
TO = 'kavya.arivemula@leratechnologies.com' # must be a list
SUBJECT = 'today_odi_error_log_status'
TEXT = 'This is an automated message; please do not reply to this message'
SERVER ='smtpout.secureserver.net'
PORT = 25
USERNAME='kavya.arivemula@leratechnologies.com'
PASSWORD= '********'
message = MIMEMultipart()
message['Subject'] = SUBJECT
message['From'] = FROM
message['To'] =TO
message.attach( MIMEText(TEXT) )
part = MIMEBase('application', 'octet-stream')
part.set_payload(open('D:\TEXT\error_log.txt','rb').read())
Encoders.encode_base64(part)
part.add_header('Content-Disposition', 'attachment; filename= '+ 'error_log.txt')
message.attach(part)
s = smtplib.SMTP('smtpout.secureserver.net',25)
s.login('kavya.arivemula@leratechnologies.com', '********')
s.sendmail(' kavya.arivemula@leratechnologies.com','rajesh.R@gmail.com',message.as_string())
s.quit()

Step4): Execute this procedure.

Step5): Check the mail. We will get mail from odi.

Step6): error log text file attached to this mail. Open the mail.

Note:

If we want execute these procedures at time, we can drag these two procedures into single package.

Data Management Solutions

The purpose of this blog is to provide Data Management Solutions for Retail, Banking, Finance, Insurance & Hospitality domains, helping improve businesses. The essence of Data Management is ability of a Business to have access to high quality data of business relevance anytime. Timely and Accurate data is the backbone of effective decision making. Hence, to drive revenue growth and operational efficiency, it is very important for any institute or organization to gain control over its data. The informed decision making relies on input of high quality and relevant data, at the right time. Availability of right data significantly impacts decisions on Strategy, Risk, Investments, Compliance, and Competitive positioning, especially in the highly competitive market place of modern business.