Sunday 30 June 2013
Data Warehousing Development Lifecycle
The Data Warehousing Development Lifecycle
Some Important steps must be taken before starting any development.
I have uploaded PDF file , which u can easily downloaded and you can get basics information about Data warehousing Development Lifecycle
Thanks,
Zafar Habib
Informatica - ETL Tool
The Informatica Platform Simplifies Your ETL Processes
Initiate ETL Projects Quickly and Cost-Effectively
Serving as the foundation for all data integration projects, the Informatica Platform lets IT organizations initiate the ETL process from virtually any business system, in any format. As part of the Informatica Platform, Informatica PowerCenter delivers robust yet easy-to-use ETL capabilities that simplify the development and deployment of smaller departmental data marts and data warehouses. In addition, the ETL capabilities facilitate reuse from one project to another.
Enhance ETL with Universal Data Access Capabilities
PowerCenter improves the flexibility of your ETL process with the ability to extract more enterprise data-types than any other technology on the market. Complemented by Informatica PowerExchange and PowerCenter Options, PowerCenter delivers successful ETL initiatives with access to virtually any enterprise data-type, including:
- Structured, unstructured, and semi-structured data
- Relational, mainframe, file, and standards-based data
- Message queue data
- Automate Most ETL Processes for Fewer Errors and Greater Productivity
PowerCenter makes your ETL developers' jobs easier with cross-functional tools, reusable components, and an enterprise-wide platform that automates many ETL processes. For data warehousing and ETL developers, that means fewer ETL errors and emergency fixes, less risk of rework, faster development time, and greater productivity.
Informatica Presentation
Session 1:
Click Here
Session 2:
How to practically work in Informatica?
Concept of KPI's in OBIEE world
Key performance indicator (KPI) are used as a tool to measure the performance of business set objective against a predefined benchmark like actual budget utilization % of a particular cost center is 95% for a particular month against a desired goal of 90% .So here it is lagging the benchmark by 5%.
As we know that KPI (Key performance Indicator) is used by organizations to measure performance, make strategies and set targets, Oracle OBIEE gives ability to define these performance, strategies and targets within OBIEE envirnment, basically it brings the corporate annual pictures to the desktop.
Thanks,
Zafar Habib
As we know that KPI (Key performance Indicator) is used by organizations to measure performance, make strategies and set targets, Oracle OBIEE gives ability to define these performance, strategies and targets within OBIEE envirnment, basically it brings the corporate annual pictures to the desktop.
Thanks,
Zafar Habib
Oracle Forms respond very slow in Windows 7 - Solution
http://notsomany.wordpress.com/2010/03/30/jinitiator-working-on-windows-7/
It appears that the change to windows basic theme has something to do with the direct-draw feature in Java. I stumbled on this somewhere on the internets and this fix seems to work in most cases.
Control Panel > JInitiator
Basic Tab
In the runtime parameters, add this
-Dsun.java2d.noddraw=true
Apply, close
It may not work all the time. Launching the JInitiator control panel app resets Windows back to basic theme, but launching the website application seems to be okay.
It appears that the change to windows basic theme has something to do with the direct-draw feature in Java. I stumbled on this somewhere on the internets and this fix seems to work in most cases.
Control Panel > JInitiator
Basic Tab
In the runtime parameters, add this
-Dsun.java2d.noddraw=true
Apply, close
It may not work all the time. Launching the JInitiator control panel app resets Windows back to basic theme, but launching the website application seems to be okay.
Thanks,
Zafar Habib
Benefit of using multiple Logical table Source in OBIEE 11g Admin Tool
[I have copied this answer from LinkedIn BI group - Answer given by Sandeep]
I have already explained and here goes my explanation for this group as well. :)
One of the use of multiple LTS is converting a snow flake schema to star schema.
Take below as example.
Let's say we have dept, emp and the attendance fact. In a warehouse we usually keep both employee and dept data in a single employee dimension. But lets assume we have them normalized for this example.
So in the rpd physical diagram would be
dept <- emp <- attendance fact
This is snow flaked and when we transfer these table to BMM layer we will create a single dimension like Dim - Employee, with 2 logical sources corresponding to dept and emp tables.
Another one would a fact for attendance and the BMM diagram would look something like as below
Dim - Employee <- Fact - Attendance
Now you can create a report like monthly attendance of employees or see how many employees were present department wise in a month etc., etc.
Another way of doing this would be that you create 2 dimensions in BMM layer. One would be Dim - Employee and other would be Dim - Department.
Employee dimension would only contain 1 LTS mapping to emp table in physical layer.
Your BMM diagram would look like as below this time.
Dim - Employee <- Fact - Attendance -> Dim - Department
Now in the physical layer emp table has a direct join with the attendance table, so no issues in that.
But the dept table does not have a direct join with attendance, in fact it needs to pass through emp table to get to attendance table.
So this is one of the case where you use the approach of multiple tables into a single logical table source.
You will create a new logical table like Dim - Department in BMM layer and then will add both emp table and dept table in the logical source with an equi join. This way when you are going to use a column from Dim - Department table in reports against Fact - Attendance, OBIEE is always going to add the below join in the physical query and hence defining a path to the attendance table from dept table.
where dept.deptno = emp.deptno
and emp.eno = attendance.eno
The same concepts we use while using OBIEE against OLTP systems where the tables are highly normalized and we need to jump several tables to get the table we have declared as fact, essential doing the whole work of ETL in the BMM layer.
But that's a whole different story and you can refer the below link to get a better understanding of the concept.
http://www.rittmanmead.com/2012/06/using-obiee-against-transactional-schemas-part-1-introduction/
Also, since OBIEE 11g do not have option to declare a table as bridge table, so we can use both of the above approaches to model a bridge table.
Below is a link where you can get some more insight of the concept.
http://docs.oracle.com/cd/E21764_01/bi.1111/e10540/busmodlayer.htm#BIEMG1386
Happy OBIEEying and hope someone can also add another comment to clear the concept.
Thanks,
Zafar Habib
I have already explained and here goes my explanation for this group as well. :)
One of the use of multiple LTS is converting a snow flake schema to star schema.
Take below as example.
Let's say we have dept, emp and the attendance fact. In a warehouse we usually keep both employee and dept data in a single employee dimension. But lets assume we have them normalized for this example.
So in the rpd physical diagram would be
dept <- emp <- attendance fact
This is snow flaked and when we transfer these table to BMM layer we will create a single dimension like Dim - Employee, with 2 logical sources corresponding to dept and emp tables.
Another one would a fact for attendance and the BMM diagram would look something like as below
Dim - Employee <- Fact - Attendance
Now you can create a report like monthly attendance of employees or see how many employees were present department wise in a month etc., etc.
Another way of doing this would be that you create 2 dimensions in BMM layer. One would be Dim - Employee and other would be Dim - Department.
Employee dimension would only contain 1 LTS mapping to emp table in physical layer.
Your BMM diagram would look like as below this time.
Dim - Employee <- Fact - Attendance -> Dim - Department
Now in the physical layer emp table has a direct join with the attendance table, so no issues in that.
But the dept table does not have a direct join with attendance, in fact it needs to pass through emp table to get to attendance table.
So this is one of the case where you use the approach of multiple tables into a single logical table source.
You will create a new logical table like Dim - Department in BMM layer and then will add both emp table and dept table in the logical source with an equi join. This way when you are going to use a column from Dim - Department table in reports against Fact - Attendance, OBIEE is always going to add the below join in the physical query and hence defining a path to the attendance table from dept table.
where dept.deptno = emp.deptno
and emp.eno = attendance.eno
The same concepts we use while using OBIEE against OLTP systems where the tables are highly normalized and we need to jump several tables to get the table we have declared as fact, essential doing the whole work of ETL in the BMM layer.
But that's a whole different story and you can refer the below link to get a better understanding of the concept.
http://www.rittmanmead.com/2012/06/using-obiee-against-transactional-schemas-part-1-introduction/
Also, since OBIEE 11g do not have option to declare a table as bridge table, so we can use both of the above approaches to model a bridge table.
Below is a link where you can get some more insight of the concept.
http://docs.oracle.com/cd/E21764_01/bi.1111/e10540/busmodlayer.htm#BIEMG1386
Happy OBIEEying and hope someone can also add another comment to clear the concept.
Thanks,
Zafar Habib
Email through Oracle database
First of all you installed STUNNEL [For GMAIL STMP]
Change on Stunnel.conf File
;
Use it for client mode
client
= yes
[ssmtp]
accept = 1925
connect
= smtp.gmail.com:465
Here I use port 1925 on my localhost
(unused as far as I know) to connect to smtp.gmail.com.
Start Stunnel.exe, and test the configuration:
Start Stunnel.exe, and test the configuration:
- Start cmd
- Write: telnet localhost 1925
- You should then see something like "220
mx.google.com ESMTP 5sm18031572eyh.34"
- Write: quit
Troubleshooting: If you cannot reach smtp.gmail.com, there can be any number of things gone wrong.
- Try a normal ping to smtp.gmail.com
- Check to see if stunnel.exe is excepted properly in all
firewalls (Windows native and other software firewalls)
Once stunnel is working, and if you are familiar with UTL_SMTP, don't bother reading on. This is the same as UTL_SMTP with any other smtp-host requiring authentication.
Now Execute this Package / Procedure
:
create or replace package apex_mail_p
is
g_smtp_host varchar2 (256) :=
'localhost';
g_smtp_port pls_integer :=
1925;
g_smtp_domain varchar2 (256) :=
'gmail.com';
g_mailer_id constant varchar2 (256) := 'Mailer by Oracle UTL_SMTP';
-- send mail using UTL_SMTP
procedure mail (
p_sender in varchar2
, p_recipient in varchar2
, p_subject in varchar2
, p_message in varchar2
);
end;
/
create or replace package body apex_mail_p
is
-- Write a MIME header
procedure write_mime_header (
p_conn in out nocopy utl_smtp.connection
, p_name in varchar2
, p_value in varchar2
)
is
begin
utl_smtp.write_data ( p_conn
, p_name || ': ' || p_value || utl_tcp.crlf
);
end;
procedure mail (
p_sender in varchar2
, p_recipient in varchar2
, p_subject in varchar2
, p_message in varchar2
)
is
l_conn utl_smtp.connection;
nls_charset varchar2(255);
begin
-- get characterset
select value
into nls_charset
from nls_database_parameters
where parameter =
'NLS_CHARACTERSET';
-- establish connection and
autheticate
l_conn := utl_smtp.open_connection (g_smtp_host, g_smtp_port);
utl_smtp.ehlo(l_conn, g_smtp_domain);
utl_smtp.command(l_conn, 'auth login');
utl_smtp.command(l_conn,utl_encode.text_encode('xyz@gmail.com', nls_charset, 1));
utl_smtp.command(l_conn, utl_encode.text_encode('password', nls_charset, 1));
-- set from/recipient
utl_smtp.command(l_conn, 'MAIL FROM: <'||p_sender||'>');
utl_smtp.command(l_conn, 'RCPT TO: <'||p_recipient||'>');
-- write mime headers
utl_smtp.open_data (l_conn);
write_mime_header (l_conn, 'From', p_sender);
write_mime_header (l_conn, 'To', p_recipient);
write_mime_header (l_conn, 'Subject', p_subject);
write_mime_header (l_conn, 'Content-Type', 'text/plain');
write_mime_header (l_conn, 'X-Mailer', g_mailer_id);
utl_smtp.write_data (l_conn, utl_tcp.crlf);
-- write message body
utl_smtp.write_data (l_conn, p_message);
utl_smtp.close_data (l_conn);
-- end connection
utl_smtp.quit (l_conn);
exception
when others
then
begin
utl_smtp.quit(l_conn);
exception
when others then
null;
end;
raise_application_error(-20000,'Failed to send mail due to the
following error: '
|| sqlerrm);
end;
end;
/
Then
begin
apex_mail_p.mail
('xyz@gmail.com', 'abc@gmail.com', 'Hi', 'How are you !');
end;
Enjoy .
Thanks,
Zafar Habib
Subscribe to:
Posts (Atom)
