Vidya Bala's Blog

Search My Blog

Add RSS Feed

Tuesday, January 30, 2007

Informatica Step by Step to create a Simple Workflow Run successfully:

As a follow-up to my previous post
This post will cover a)How to create Repository User accounts and managing security in Informatica

b)Create a mapping , session, workflow and successfully execute a workflow

How to create Repository User accounts and managing security in Informatica

1) Login to Repository Server Admin console.Connect to the Repository Server



Right click and create new Repository
4)give the following :
repository name
db connect string
db username : pcenter1
db password : pcenter1

license key information

when you click Apply the Repository content will get created.

5)Once the Repository is created loginto Repsoitory Manager
You can loginto the Repository either as
a) The Repository username/ password provided in the above step
b) Or Administrator
Go the Security > Manage Users and Privileges


By default 2 grps are created “Administrator” and “Public”
2 users are created “Administrator” and “Repository User”
Privileges tab lists all the privileges that are available. This security window can be used to manage Security and privileges – refer help guide for further information on security and privileges.

Create a mapping , session, workflow and successfully execute a workflow

I will use the HR schema to demonstrate how you can create a mapping, session and workflow. The HR schema has the following tables

COUNTRIES
DEPARTMENTS
EMPLOYEES
JOBS
JOB_HISTORY
LOCATIONS
REGIONS
COUNTRY_REGION is table that has country_name and region_name.

To populate the country_region table : is a join between the country table and the region table
To create a mapping:

Open Repository Manager – Connect to the Repository and create new folder within the Repository using Repository Manager.
Connect to Designer

Open the folder up and you should see Sources, Targets , Cubes , Dimensions etc.
From the Sources menu import from source Database objects you need: in this case you will import COUNTRIES and REGION
Open Warehouse Designer and Import TargetsImport COUNTRY_REGION from Target Menu.
Sources and Targets Menu should be as above

Open Mapping Designer.
Drop in the sources to the Mapping Designer, Drop in the target as well to Mapping Designer.Include a Join Transform to join appropriately the COUNTRY and REGION table.
Name the mapping as COUNTRY_REGION_MAPPING. While saving the mapping make sure parsing completed with no errors. Errors will be reported on the Output window of the Designer.
Once you have saved the Mapping you can now open up your Workflow Manager to create a session and a workflow.
Tasks - Create - creates a new Session or Task
select the COUNTRY_REGION mapping and save the Repository.
Click on Connections/Relational to create 2 new connections for your Source and Target databases.
once the connections are created Click on the Task and you should see the following properties window open up
click on the Mappings Tab and verify the connections are set appropriately.
When you are ready to create the Workflow – Open Workflow Designer and drag and drop the mapping.
Name the Workflow as COUNTRY_REGION_WORKFLOW
Save Repository and in the Output window verify that the workflow is valid.

Before you start running the workflow make sure to register the Power Center Server

Open Workflow Manager – Server – Server Registrationgive all the Power Center Server Registration Properties and define your PMRootDir

Click OK and , Right Click Server and assign the workflow you want to run using the Server. Once you have assigned the workflow to the server you can start the workflow – right click the workflow and click start


workflow monitor should start indicating the status of the run
Right click the workflow and task and you should be able to view the workflow log and session log. From workflow Manager workflows can also be scheduled.
If you run into any issues running a workflow – feel free to post comments. The next 2 posts will cover a) versioning b)debugging using informatica.

Monday, January 29, 2007

Deployment Options with XML Publisher

This post will discuss the different ways XML Publisher can be used along with Microsoft Word Template builder to generate Reports.

XML Publisher (also called BI Publisher) has the following Deployment options

1) Oracle Applications (will not be discussed in this post)
2) XML Publisher Desktop Edition
Installs XML Publisher Template Builder in Microsoft word that helps you build templates for your Reports. The templates can be stored as rtf files. Following are the Source Data Options using Template Builder in word
a)XML File
b)SQL Query , needs connection information to source database
c)XML Schema
d)XML generated by Siebel Analytics Answers (I have not been able to get this to work , it may be something that will be available in the next releases and more easily integrated in the next few releases of Siebel Analytics)
3) XML Publisher Enterprise Edition
a)provides a web based console that can be used to publish multiple reports
b)XML Publisher enables you to define your reports and separate the data from the layout of the reports .
c)XML Publisher can run on any J2EE compliant Application Server

XML Publisher Desktop Edition:

If you have installed the Desktop Edition


Template Builder Options will be available from MS Word menu.
Template Builder – Data – Load XML Data (XML File) , XML Schema , Report wizard (lets you give database connect information and the sql to extract the data)
Below will list a quick example on how the Report Wizard can be used (connecting to the hr schema to get a list of Departments)


give the database connect information and sql query for the data that needs to be retrieved.We will choose the Default Template Layout in this example.
preview the Report and then save the RTF file (in this example we save the RTF file as hr_departments.rtfXML Publisher Enterprise Edition

As mentioned XML Publisher enterprise edition can run on any J2EE compliant Application Server.

The Admin and Reports directories are available under
Install_dir/xmlpserver

The following files have the port numbers used by the application.
HTTP Port 15101 install_dir/default-web-site.xml
RMI Port 15111 install_dir/rmi.xml
JMS Port 15121 install_dir/jms.xml

Default URL to access XML Publisher Application http://host:15101/xmlpserver (default username/pwd admin/admin)

create new folder and new Report in the corresponding folder.
Edit the Report to define the following properties:
i)datasource for the Report (new datasources can be created in the Admin window)
ii)Data Model: Define the sql query

iii)New List of Values: If the Report uses LOV’s

iv)Parameters: if any parameters are needed for the Report

v)Layouts: create a new template called hr_departments
upload hr_departments.rtf and tie it to the hr_departments template.View the results
you can see that the template is chosen by default and the different output formats available. The above is a very simple illustration of how XML publisher will let your users design their own Reports(and manage changes to design templates of reports) while IT can focus on the data needed for the Reports and other important tasks.



Tuesday, January 23, 2007

Oracle Text 9i Bug searching XML Data

My last post I discussed that there was a 9i (9.2.0.6) bug using section_group_type auto section group . This group type automatically creates a zone section for each start-tag/end-tag pair in a XML document. The section names derived from XML tags are case sensitive as in XML.
Searches with auto_section_group work in 9.2.0.6 but not for attributes within a tag. For example
Book title="A" author="B"
attributes title and author cannot be searched using auto_section_group section type in 9.2.0.6. The bug has been fixed in 9.2.0.8

Thursday, January 11, 2007

9i bug using Oracle Text to search XML data

Using Oracle Text to Search XML Data: XML Data inserted in DATA column in TEST table

Create table test(id integer,DATA CLOB)


Department name="CS"
Employee
Vidya Bala
/Employee
/Department



Step1:
--------

Create an auto section group

begin
ctx_ddl.create_section_group('myautosectiongroup', 'AUTO_SECTION_GROUP');
end;

Step2:
-------
create index test_index on test(DATA)
indextype is ctxsys.context
parameters ('SECTION GROUP myautosectiongroup');

Step3:
---------
SELECT DATA FROM TEST
WHERE CONTAINS(DATA, 'Vidya WITHIN Employee') > 0;
1 Row Returned

SELECT DATA FROM TEST
WHERE CONTAINS(DATA, 'CS WITHIN Department@name') > 0;

0Rows (10g returns 1 row – 9i returns no row – Support is working on getting bug fix for the issue)

Tuesday, January 09, 2007

OracleXml putxml limitations

OracleXml putxml limitations:

Example1:xml file(test.xml):

ROWSET
ROW num="2"
ID 15 /ID
/ROW
/ROWSET

(using the java API for XDK) the below command
java OracleXML putXML -user "vidya/vidya" -ignorecase –filename "test.xml" "emp"

will load one row into table emp.

Example2:Xml file with namespaces (test1.xml):

ns:EMP
ns:ITEM
ns:ID 2 /ns:ID
/ns:ITEM
/ns:EMP

(using the java API for XDK) the below command
java OracleXML putXML -user "vidya/vidya" -ignorecase –filename "test1.xml" "emp"

if “OracleXML” cannot identifiy the ROWTAG the above command can be modified as below

java OracleXML putXML -user "vidya/vidya" -ignorecase –rowtag “ITEM” –filename "test1.xml" "emp"

note the above will error as OracleXML doesn’t seem to be working on
a file with namespaces. The only way to probably get around this is by applying a stylesheet.

Thursday, January 04, 2007

Siebel Analytics Answers and Dashboards (a quick getting started guide based on HR schema)

Siebel Analytics Answers and Dashboards (a quick a quick getting started guide based on HR schema)

The last post on Sibel Analytics discussed how we build a physical , Business and presentation layer using Siebel Analytics Administration.
This post will focus on using Answers and Dashboards.
Make sure Siebel Analytics Web is up.
For the purpose of this post , I have demonstrated on how you can build Reports using “Answers” against the “hr” schema.

Physical Layer - Make sure to define the object relationships appropriately in the physical layer
COUNTRIES
DEPARTMENTS
EMPLOYEES
JOB_HISTORY
JOBS
LOCATIONS
REGIONS

Busines Layer - Make sure to define the object relationships appropriately in the Business layer
COUNTRIES
DEPARTMENTS
EMPLOYEES
JOB_HISTORY
JOBS
LOCATIONS
REGIONS

Dimensions:
Department Dimension (3 level Dimension)
-Department
-Region
-Country
Job Dimension
-Job Detail

Presentation Layer defined as follows:
Employee
First Name
Last Name
Email
Phone Number
Hire Date
Department
Department Name
City
Region Name
Country Name
Job
Job Title


Once the Presentation Layer is defined you are ready to move on to Answers to build your Siebel Analytics Reports


If you have all Siebel Client Tools on your Desktop ; Siebel Analytics web will take you to the Analytics web page


The default installation
Default username : AdministratorDefault password: no password
once you have logged in you will see the following Tabs
Dashboards,Answers, Advanced Reports,Marketing,Delivers,Disconnected,Admin,My-Account

Step1:
Before proceeding to answers to create Reports let us first create a Shared Folder named “hr” where we can save our Reports. Click on Answers and on the left hand side you should see My Folder > Shared Folder > Manage Catalog

click on Manage catalog > Shared Folder and you should be able to create a new folder
make sure to Refresh Display to verify that the changes have taken effect

Step2:
Now you are ready to create Reports using Answers
Click on Answers > Answers is basically your web interface to building Reports
on the right handside you should see Subject areas
click on HRDetails
you will now see on the left hand side bar all the Data Items that were defined in the Presentation Layer defined available in Answers
Employee
First Name
Last Name
Email
Phone Number
Hire Date
Department
Department Name
City
Region Name
Country Name
Job
Job Title

Example1 – Report1
To build a Report
First Name,LastName,Department Name,Country Name, Region Name, City
Click on the attributes so that they appear under columns:

click on Results and you should see the Results
save your Report to the “hr” shared folder.

The above Report was HR Employee Demographic Details (List of employees by name,department,city,county)


Example2 – Report 2

Pie Chart to give an overview of number of employees by DepartmentClick on create new request

Edit Column formula to display first name and last name as a single column called “Employee Name”

The next step would be to get a count of employees by DepartmentCriteria Tab add Department name and modify “Employee Name” column formula to “count(Employee."First Name"' ' Employee."Last Name")”
The Results would be as above. Now to covert this to a pie chart (make sure to start Siebel Analytics Java Host)
Click on the Pie Chart
Save the Request as “Employees by Department”

Example 3:We now have 2 Reports

“Employees by Department”
“HR Employee Demographic Details”

lets now add the above 2 Reports to a Dashboard

Click on the Admin Tab

Click on Manage Intelligence Dashboards.

Create Dashboard. Name the dashboard as “HR Details”
you should see the “HR Details” dashboard appear near the “My Dashboard”
Click to Add content and you should be able to add the 2 Reports we created in the “hr” shared folder.
The output would be as below

The layout can be changed by editing properties of sections in the Dashboard

Page Option > Edit Dashboard
Click on the Properties Tab of sections if you want to edit the layout of sections.In the below view we have arranged the Reports Horizontally.
Example 4: Drill down from the Pie Chart to the Report Table in the HR Details Dashboard.

a)To Navigate from Dashboard to Answers easily include the modify link in the Dashboard.Page Options > Edit Dashboard
for each section select Properties > Report Links > Modify. Save and go back to Dashboard now you should see the modify links.
b) To set the navigate link on the Pie Chart.Modify the pie chart > Results > edit view of the pie chart


select Additional Charting Options
Interaction Tab > select Navigate > and give the Navigation Page > “HR Details” Dashboard Page 1
c) Add an is prompted filter to the Table Report

from the dashboard click on Modify link of the Table. This will take you to criteria tab on Answers.Add a filter and set Department Name to “is prompted” and save the filter
d) create a Dashboard Prompt click on Answers
you should see an icon on the Left Menu to create a new Dashboard Prompt
scope=Dashboardselect the column name on which you want to set the prompt to
save the Dashboard Prompt.
Now go to your Dashboard > Edit Page Options > Drop the Dashboard Prompt in the Section you would like to appear in and you should now see the Prompt on your Dashboard

you can drill down by Department Name from the Dashboard Prompt drop down.
e) column sorting on Employee Demographic details
assume you also wanted the ability to sort by columns on the Table Report.Click on the Modify link on the Table Report > go to the Results Tab on Answers> Click on the edit view of the Table and then edit Table properties

check “ enable column sorting on Dashboard”

At this point you have a fully functional Dashboard with Drill down ability both from the Dashboard prompt and from the pie chart to the Table.

The next posts will discuss:
1)XML Publisher and Answers/Dashboards

2)Admin Options with Answers(managing security, ibots etc)