Automation of BW scheduled Jobs linked to BO Webi reprts

AUTOMATION OF SCHEDULED JOBS LINKING TO BO REPORTS

 

CURRENT SCENARIO:

If BOBJ reports are scheduled to run on daily recurrence and for some reason if BW loads get delayed or failed, BOBJ report will not show the latest data.

 

PROPOSED SOLUTION:

To overcome the current scenario we are going to have BO reports scheduled based on file events. The file will be created based on the successful completion of BW data loads/process chains. This would make sure the report is generated and sent to users only if the process chain completes successfully.

 

In BW, we have created an ABAP program to create a test file on application server. This directory, where the file is created, is mounted on both BW server as well as BOBJ server. BOBJ reports will be scheduled prior to this file creation in BW. In BOBJ once the schedules starts, Event will continuously search for the file. In case the file is available prior to BOBJ report schedule time, Event doesn’t process. It will internally check with timestamp of BOBJ report schedule and file creation.  So, we are deleting the old file and replacing it with a new file after BW data loads.

 

Below is high level summary of the steps involved:

• Create a share drive in BW and mount it to BOBJ server so that it can be accessed by both BW and BOBJ server. This is required so that file can be created in BW side and read in BOBJ side.
• Create an ABAP Program to delete the previously created file
• Create an ABAP program to create a sample test file on Application server
• Create the File based Event on the BOBJ side and assign this event to the desired Report for scheduling

BENEFITS:

• Accuracy in data since the report generates only after BW data load finishes
• Incorrect data Reports aren’t sent to users if BW loads fail (especially useful on weekends wherein there is no monitoring)

DETAILED STEPS:

Creating a shared file path at OS level:

Create a share drive in BW and mount it to BOBJ server so that can be accessed by BW system and BOBJ system with full rights with an admin user.  For example, \\AZSXX2120\data\BOBJ\ is directory that we created in BW where test file will be written. This file can be read from the BOBJ side.  This step is done by our BASIS team.

BW Steps

We have created two ABAP program to delete and create the test file. We included these two program in a process chain and attached the process chain with our transactional data load process chain. This helped to create the file only at the end of all transactional data load.

File deletion ABAP code:
*&---------------------------------------------------------------------*
*& Report ZBO_ORDERS_DELETE_TEST
*&
*&---------------------------------------------------------------------*
*&
*& Sample Testing. This program will delete a file from Application *& Server
*&---------------------------------------------------------------------*

REPORT ZBO_ORDERS_DELETE_TEST.

data: p_s_path type pathextern. " Complete filepath
data: p_s_file type pathextern." File name
data: p_s_dir type pathextern. " Directory

p_s_file ='Orders.csv'. "file name to be deleted

p_s_dir = '\AZSXX2120\data\BOBJ\'. " Path from where file will be deleted

concatenate p_s_dir p_s_file into p_s_path.
condense p_s_path.

open dataset p_s_path for input in text mode encoding default.


if sy-subrc = 0.
close dataset p_s_path.
delete dataset p_s_path.

if sy-subrc eq 0.
write: 'File Deleted Succesfully', p_s_path.
else.
write: 'No Authorization to delete or no file exists', p_s_path.
endif.
else.

write: p_s_path, 'File does not exist'.
endif.

File Creation ABAP code:
*&---------------------------------------------------------------------*
*& Report ZBO_ORDERS_CREATE_TEST
*& Program will create a file and place it at application server
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*

REPORT ZBO_ORDERS_CREATE_TEST.
tables: rlgrap.
data: lv_file_str type string . "file Path For Application server file
data: lv_msg_text(50),
lv_filename type localfile.


data l_lines type i.
data ls_split type string.
data lt_split like table of ls_split.
data lp_file type localfile.
data lp_fil2 like rlgrap.
lp_file = 'Orders.csv'. "file name to be created
lp_fil2 = '\AZSXX2120\data\BOBJ'. " Path where file will be created


at line-selection.

call transaction 'AL11' .


*———————————————————————-*
* Start of selection
*———————————————————————-

start-of-selection.
split lp_file at '.' into lv_file_str lv_filename .

translate lv_filename to upper case .

if lv_filename = 'CSV' .

clear : lv_filename .



split lv_file_str at '\' into table lt_split.
describe table lt_split lines l_lines.
* file name

loop at lt_split into ls_split.
if sy-tabix eq l_lines.
lv_filename = ls_split.
endif.
endloop.

lv_file_str = lp_file .
if lv_file_str is not initial.
perform write_file .
else.
message 'File Path Needed .' type 'W'.
endif.
else.
message '.CSV File Needed .' type 'W'.
endif.


free :lv_filename ,lt_split ,l_lines ,lv_file_str.
*&———————————————————————*
*& Form write_file – This will create a file at Application Server
*&———————————————————————

form write_file .

concatenate lp_fil2 lv_filename '.csv' into lv_filename .
condense lv_filename no-gaps .
open dataset lv_filename for output in text mode
encoding default message lv_msg_text.

if sy-subrc eq 0.
write: 'File' ,lv_filename ,'created Successfully at Application server.'.
exit.
else.
write: 'File' ,lv_filename ,' cannot get created at Application server.'.
exit.
endif.
endform.

The above two program in the process chain are included in our transactional data load chain. This ensures that file would be created only after BW transactional load is completed. After all sequences, BW system will write file on /data/BOBJ mount or share drive.

BOBJ Steps:

From the Business Objects side, we will be creating file-based event and attaching the event to the report. Once the recurrence schedule starts, the event will continuously search for the files specified in the path. If the file occurs prior to schedule time, event will not trigger, internally it will check with time stamp of file creation and schedule time. In case if the file occurs then need to delete it and write the file. Once the file placed, event will get triggered and schedule process starts to run the report.

Load list of services

This code will load a list of all Services

private ServiceController msvc;
private ListViewItem ViewItem;
private Hashtable mcolSvcs = new Hashtable();
private bool fUpdatingUI ;

private void frmMain_Load(object sender, System.EventArgs e)
{
EnumServices();
}

private void EnumServices()
{
// Get the list of available services and load the list view control with the information
try
{
fUpdatingUI = true;
this.lvServices.Items.Clear();
if (!(mcolSvcs == null))
{
mcolSvcs = new Hashtable();
}
ServiceController[] svcs = ServiceController.GetServices();
foreach(ServiceController svc in svcs)
{
ViewItem = this.lvServices.Items.Add(svc.DisplayName);
ViewItem.SubItems.Add(svc.Status.ToString());
ViewItem.SubItems.Add(svc.ServiceType.ToString());
mcolSvcs.Add(svc.DisplayName,svc);
}
}
catch (Exception exp)
{
MessageBox.Show(exp.Message, exp.Source, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
fUpdatingUI = false;
}
}

SQL SERVER – 2008 – Interview Questions

1. What is Cursor?

Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time.In order to work with a cursor we need to perform some steps in the following order:

• Declare cursor
• Open cursor
• Fetch row from the cursor
• Process fetched row
• Close cursor
• Deallocate cursor

2. What is Collation?
Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case sensitivity, accent marks, kana character types and character width

3. What is Difference between Function and Stored Procedure?
UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be. UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables. Inline UDF’s can be thought of as views that take parameters and can be used in JOINs and other Rowset operations.

4. What is sub-query? Explain properties of sub-query?
Sub-queries are often referred to as sub-selects, as they allow a SELECT statement to be executed arbitrarily within the body of another SQL statement. A sub-query is executed by enclosing it in a set of parentheses. Sub-queries are generally used to return a single row as an atomic value, though they may be used to compare values against multiple rows with the IN keyword.
A subquery is a SELECT statement that is nested within another T-SQL statement. A subquery SELECT statement if executed independently of the T-SQL statement, in which it is nested, will return a resultset. Meaning a subquery SELECT statement can standalone and is not depended on the statement in which it is nested. A subquery SELECT statement can return any number of values, and can be found in, the column list of a SELECT statement, a FROM, GROUP BY, HAVING, and/or ORDER BY clauses of a T-SQL statement. A Subquery can also be used as a parameter to a function call. Basically a subquery can be used anywhere an expression can be used.

5. What are different Types of Join?
Cross Join
A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. The common example is when company wants to combine each product with a pricing table to analyze each product at each price.

Inner Join
A join that displays only the rows that have a match in both joined tables is known as inner Join. This is the default type of join in the Query and View Designer.

Outer Join
A join that includes rows even if they do not have related rows in the joined table is an Outer Join. You can create three different outer join to specify the unmatched rows to be included:

• Left Outer Join: In Left Outer Join all rows in the first-named table i.e. “left” table, which appears leftmost in the JOIN clause are included. Unmatched rows in the right table do not appear.
• Right Outer Join: In Right Outer Join all rows in the second-named table i.e. “right” table, which appears rightmost in the JOIN clause are included. Unmatched rows in the left table are not included.
• Full Outer Join: In Full Outer Join all rows in all joined tables are included, whether they are matched or not.

Self Join
This is a particular case when one table joins to itself, with one or two aliases to avoid confusion. A self join can be of any type, as long as the joined tables are the same. A self join is rather unique in that it involves a relationship with only one table. The common example is when company has a hierarchal reporting structure whereby one member of staff reports to another. Self Join can be Outer Join or Inner Join.

6. What are primary keys and foreign keys?
Primary keys are the unique identifiers for each row. They must contain unique values and cannot be null. Due to their importance in relational databases, Primary keys are the most fundamental of all keys and constraints. A table can have only one Primary key.
Foreign keys are both a method of ensuring data integrity and a manifestation of the relationship between tables.

7. What is User Defined Functions? What kind of User-Defined Functions can be created?
User-Defined Functions allow defining its own T-SQL functions that can accept 0 or more parameters and return a single scalar data value or a table data type.
Different Kinds of User-Defined Functions created are:

Scalar User-Defined Function
A Scalar user-defined function returns one of the scalar data types. Text, ntext, image and timestamp data types are not supported. These are the type of user-defined functions that most developers are used to in other programming languages. You pass in 0 to many parameters and you get a return value.

Inline Table-Value User-Defined Function
An Inline Table-Value user-defined function returns a table data type and is an exceptional alternative to a view as the user-defined function can pass parameters into a T-SQL select command and in essence provide us with a parameterized, non-updateable view of the underlying tables.

Multi-statement Table-Value User-Defined Function
A Multi-Statement Table-Value user-defined function returns a table and is also an exceptional alternative to a view as the function can support multiple T-SQL statements to build the final result where the view is limited to a single SELECT statement. Also, the ability to pass parameters into a TSQL select command or a group of them gives us the capability to in essence create a parameterized, non-updateable view of the data in the underlying tables. Within the create function command you must define the table structure that is being returned. After creating this type of user-defined function, It can be used in the FROM clause of a T-SQL command unlike the behavior found when using a stored procedure which can also return record sets.

8. What is Identity?
Identity (or AutoNumber) is a column that automatically generates numeric values. A start and increment value can be set, but most DBA leave these at 1. A GUID column also generates numbers; the value of this cannot be controlled. Identity/GUID columns do not need to be indexed.

9. What is DataWarehousing?
• Subject-oriented, meaning that the data in the database is organized so that all the data elements relating to the same real-world event or object are linked together;
• Time-variant, meaning that the changes to the data in the database are tracked and recorded so that reports can be produced showing changes over time;
• Non-volatile, meaning that data in the database is never over-written or deleted, once committed, the data is static, read-only, but retained for future reporting.
• Integrated, meaning that the database contains data from most or all of an organization’s operational applications, and that this data is made consistent. 

Working with XML files

In this tutorial, we will use the XmlDocument class to create and load XML files. You will use the XmlNode and XmlAttribute classes to build nodes. You will search for nodes by using the GetElementByName and SelectNodes methods, the last one using XPath.

Namespace Used

using System.Xml;

protected void btncreatexml_Click(object sender, EventArgs e) { XmlDocument xmlDoc = new XmlDocument(); XmlNode xnDeclaration = xmlDoc.CreateXmlDeclaration("1.0", "UTF-8", null); xmlDoc.AppendChild(xnDeclaration); XmlNode xnMenus = xmlDoc.CreateElement("Menus"); xmlDoc.AppendChild(xnMenus); XmlNode xnMenuItem = xmlDoc.CreateElement("MenuItem"); XmlAttribute xaTitle = xmlDoc.CreateAttribute("Title"); xaTitle.Value = "Home"; xnMenuItem.Attributes.Append(xaTitle); XmlAttribute xaLink = xmlDoc.CreateAttribute("Link"); xaLink.Value = "Home.htm"; xnMenuItem.Attributes.Append(xaLink); xnMenus.AppendChild(xnMenuItem); //Node xnMenuItem = xmlDoc.CreateElement("MenuItem"); xaTitle = xmlDoc.CreateAttribute("Title"); xaTitle.Value = "Members"; xnMenuItem.Attributes.Append(xaTitle); xaLink = xmlDoc.CreateAttribute("Link"); xaLink.Value = "Members.htm"; xnMenuItem.Attributes.Append(xaLink); xnMenus.AppendChild(xnMenuItem); //more nodes XmlNode xnSubMenu = xmlDoc.CreateElement("MenuItem"); xaTitle = xmlDoc.CreateAttribute("Title"); xaTitle.Value = "Add Member"; xnSubMenu.Attributes.Append(xaTitle); xaLink = xmlDoc.CreateAttribute("Link"); xaLink.Value = "AddMember.htm"; xnSubMenu.Attributes.Append(xaLink); xnMenuItem.AppendChild(xnSubMenu); //more nodes xnSubMenu = xmlDoc.CreateElement("MenuItem"); xaTitle = xmlDoc.CreateAttribute("Title"); xaTitle.Value = "Delete Member"; xnSubMenu.Attributes.Append(xaTitle); xaLink = xmlDoc.CreateAttribute("Link"); xaLink.Value = "deleteMember.htm"; xnSubMenu.Attributes.Append(xaLink); xnMenuItem.AppendChild(xnSubMenu); // xnMenus.AppendChild(xnMenuItem); // //Adding another menu xnMenuItem = xmlDoc.CreateElement("MenuItem"); xaTitle = xmlDoc.CreateAttribute("Title"); xaTitle.Value = "Schedule"; xnMenuItem.Attributes.Append(xaTitle); xaLink = xmlDoc.CreateAttribute("Link"); xaLink.Value = "Schedule.htm"; xnMenuItem.Attributes.Append(xaLink); xnMenus.AppendChild(xnMenuItem); //more nodes xnSubMenu = xmlDoc.CreateElement("MenuItem"); xaTitle = xmlDoc.CreateAttribute("Title"); xaTitle.Value = "Add Time"; xnSubMenu.Attributes.Append(xaTitle); xaLink = xmlDoc.CreateAttribute("Link"); xaLink.Value = "AddTime.htm"; xnSubMenu.Attributes.Append(xaLink); xnMenuItem.AppendChild(xnSubMenu); //more nodes xnSubMenu = xmlDoc.CreateElement("MenuItem"); xaTitle = xmlDoc.CreateAttribute("Title"); xaTitle.Value = "Delete Time"; xnSubMenu.Attributes.Append(xaTitle); xaLink = xmlDoc.CreateAttribute("Link"); xaLink.Value = "deleteTime.htm"; xnSubMenu.Attributes.Append(xaLink); xnMenuItem.AppendChild(xnSubMenu); // xnMenus.AppendChild(xnMenuItem); // xmlDoc.Save(Request.PhysicalApplicationPath + "\\XMLMenu.xml"); }

Next, you will add the code to generate a tree-like menu using the XML file that you created.

Double-click on the XML Menu button.

protected void btnxmlmenu_Click(object sender, EventArgs e) { XmlDocument xmlDoc = new XmlDocument(); xmlDoc.Load(Request.PhysicalApplicationPath + "XMLMenu.xml"); //GetElementsByTagName will return all parent and child nodes matching the tag name XmlNodeList xnl = xmlDoc.GetElementsByTagName("MenuItem"); PlaceHolder1.Controls.Add(new LiteralControl("")); foreach (XmlNode menuItem in xnl) { if (menuItem.ParentNode.Name == "Menus") { PlaceHolder1.Controls.Add(new LiteralControl(" ")); } else { PlaceHolder1.Controls.Add(new LiteralControl(" ")); } PlaceHolder1.Controls.Add(new LiteralControl(" "Link"].Value + ">" + menuItem.Attributes["Title"].Value + " "Link"].Value + ">" + menuItem.Attributes["Title"].Value + " ")); } }

The last thing is how to search through nodes by using XPath. Using the SelectNodes method of the XmlDocument object, you will pass to the parameter of this method the path to the node through a textbox.

It will return all MenuItem nodes at the level: /Menus/MenuItem

Click on the Search w/ XPath button.

protected void btnsearch_Click(object sender, EventArgs e) { XmlDocument xmlDoc = new XmlDocument(); xmlDoc.Load(Request.PhysicalApplicationPath + "XMLMenu.xml"); XmlNodeList xnl = xmlDoc.SelectNodes(TextBox1.Text); foreach (XmlNode n in xnl) { Response.Write(n.Attributes["Title"].Value + " "); } }

Article selected - Happy today

Article selected in Microsoft's Official site - 

My Article at http://www.dotnetfunda.com/articles/article616-multiple-document-interface-mdi-form.aspx has been selected as "Article of the Day" on Microsoft's official website http://windowsclient.net/