BusinessObjects Universe: Defining Complex Outer Join

October 13, 2014 Leave a comment

A complex join contains more than one condition in the join expression. Consider the following expression where one line of sales order joins with a line on invoice,

INVOICE_LINE.ORDER_NUM=ORDER_LINE.ORDER_NUM
and INVOICE_LINE.ORDER_LINE_NUM=ORDER_LINE.ORDERLINENUM

If we define a complex join in BusinessObjects Universe Design Tool, than the option to make it outer becomes disabled.

clip_image002

There are three possible ways to get around this problem (that I found), which are described below.

 

1) Create two separate join between

Create two separate join to split the complex expression into simple ones. This will enable the ‘Outer join’ checkbox. While creating the query, the universe will automatically combine the two join into one complex expression.

clip_image006

 

2) Set the Outer Join first, then specify complex expression (ANSI92 only)

Change the expression to simple temporarily; this will enable the ‘Outer join’ checkbox. Select the ‘Outer join’ and change the join expression back to the designed complex expression. Universe Designer will not only remember that ‘Outer join’ flag but also generate the right query at runtime with outer join clause.

Note: This solution only works if ANSI92 parameter is set to ‘Yes’. That is, join/on clause syntax is used for creating SQL query.

clip_image004

The universe will generate SQL similar to the following:


SELECT DISTINCT
INVOICE_LINE.INVOICE_NUM,
INVOICE_LINE.INVOICE_LINE_NUM,
ORDER_LINE.ORDER_NUM,
ORDER_LINE.ORDERLINENUM
FROM
ORDER_LINE RIGHT
OUTER JOIN INVOICE_LINE
ON (INVOICE_LINE.ORDER_NUM=ORDER_LINE.ORDER_NUM(+)
and INVOICE_LINE.ORDER_LINE_NUM=ORDER_LINE.ORDERLINENUM(+))

 

3) Directly specifying the complex expression with (+) syntax

We can directly specify the complex join expression and make it outer by using (+) syntax. Off course, this will only work when ANSI92 parameter is set to ‘No’.

clip_image008

Generated SQL will be something like:


SELECT DISTINCT
INVOICE_LINE.INVOICE_NUM,
INVOICE_LINE.INVOICE_LINE_NUM,
ORDER_LINE.ORDER_NUM,
ORDER_LINE.ORDERLINENUM
FROM
INVOICE_LINE,
ORDER_LINE
WHERE
( INVOICE_LINE.ORDER_NUM=ORDER_LINE.ORDER_NUM(+)
and INVOICE_LINE.ORDER_LINE_NUM=ORDER_LINE.ORDERLINENUM(+) )

 

The above applies to ‘Universe Design Tool’ version 14.0.7.  More at Official SAP BusinessObjects Universe Design Tool Guide

Designing BusinessObjects Universe: Join problems in schema and resolution techniques

October 10, 2014 Leave a comment

An essential aspect of creating a BusinessObjects universe is to define joins among tables in ‘Universe Design Tool’. Even if the joins are correctly defined from relational point of view, the universe might not generate the correct results in certain cases. There are few pitfalls to avoid which are specific to the business of designing universe.  These pitfalls, generally referred as join problems, come in three major varieties: loops, chasm traps and fan traps.

Loop

Loop is the existence of more than one join paths between two tables. In other words, if we move among tables following joins and we can reach back to the original table using a different path, then we have a loop.

Loops will cause less number of records to be returned than expected.

BO Universe Loop

The Universe Design Tool can automatically detect loops and suggest solutions (aliases and contexts).

Chasm Trap

We are in Chasm Trap when a table joins with two others in one-to-may relationship. Learn more about them here.

Chasm Trap

 

Fan Trap

Fan trap is a less severe problem than other two. It does *not* affect the number of records returned  from the query, rather it affects the aggregation of an attribute.

Consider, an invoice record with three lines. While summing invoice total if it gets accounted thrice (once for each invoice line), than we are in Fan Trap.

Fan Trap

Techniques for resolving join problems

As universe designer, the toolbox consists of following to overcome the join problems:

Alias

A table can be duplicated in schema by giving it an alias (Both table and its alias refers to the same underlying table in database). This is the most common way to resolve join problems. Use of an alias can break loops or undesirable join paths.

Contexts

Context is a set of joins. It divides the universe in overlapping sets of tables and joins. If your table is part of more than one context, the report designer tool might ask the user to select the context if it can’t be inferred.

Self Restricting Join

Join can be defined as condition on a single table that limits the records returned from it. Such a join (which is not actually a join at all) is called Self Restricting Join.

Derived Table

Derived Table is like a database view. It allows us to create new tables in Universe based on a SQL query.

Shortcut Join

As the name says, it is shortcut to a longer join path. It is important to identify a shortcut join, otherwise we may have a loop.

 

 

The above applies to ‘Universe Design Tool’ version 14.0.7.  More at Official SAP BusinessObjects Universe Design Tool Guide

Chasm Trap: Designing SAP BusinessObjects Universe

August 27, 2014 1 comment

While designing SAP BusinessObjects universe with Universe Design Tool, Chasm Trap is one of the major join problems that one can come across.

We are in Chasm Trap when a table joins with two others in one-to-many relationship. Chasm Trap produces more data than expected.

Example

Consider the following ER diagram where DEPARTMENT is having 1-to-many relationships with EMPLOYEE and CONTRACT_STAFF tables.

Let’s assume we have following data in these tables.

image

BusinessObjects universe will produce something similar to the following query to join the above 3 tables:

select depart_name, employee_name, name "contract staff"
from department d
  inner join employee e on d.department_no = e.department_no
  inner join contract_staff c on c.department_no = d.department_no

Result of the query will have more rows than expected.

image

Resolution

Universe Design Tool doesn’t detect chasm traps automatically. They can be identified by visually inspecting the ER diagram.

Using Contexts

The recommended way of resolving Chasm Trap is to use contexts which will separate the two tables into separate contexts (employee and contract_stafff in our case). If both tables are used in the report, separate queries will be generated resulting in two tables in Web Intelligence report.

Using Derived Tables

Another approach which I have found handy in many cases is to use Derived Table functionality. Derived Table in universe designer can merge the two tables into one. Additional column containing record type can be introduced to still be able to tell the records apart (in our case, record type would be employee or contract_staff). This simplifies the design and avoids the Chasm Trap.

More at Official SAP BusinessObjects Universe Design Tool Guide

Git Push error: refusing to update checked out branch (Source Control)

Recently, I cloned a repository on my machine and made some changes to it. As I was pushing my changes back to the original repo, push failed with the error message:

“refusing to update checked out branch: refs/heads/master. By default, updating the current branch in a non-bare repository is denied…”

There are two things to note here in the error message. First the original repo is non-bare, secondly the push is to a branch which is currently checked out. Following are two possible ways to overcome the issue relating to bare repos and currently checked out branch.

Set Repository as ‘Bare’

Bare repositories do not have any working copy. Changes are not done directly to these repositories, rather changes are pushed from clones. Repositories hosted at GitHub are of this type.

One way to resolve the above issue is to make the repository ‘bare’. This can be done by running below command in original repo folder:

git config –bool core.bare true
 

Change the checked out branch

If you don’t want to convert your original repository to bare, another option is change the current branch. This can be done by:

git checkout <other_branch>
 

In case, there is only one branch in the repository, a temporary branch could be created and checked out:

git checkout -b temp
 

Once the push is executed, run the following commands to bring things back to original state:

git checkout master
git branch -d temp
 

Above will delete the temporary branch and checkout the master branch.

More at http://stackoverflow.com/questions/11117823/git-push-error-refusing-to-update-checked-out-branch

Kit functionality in IBM Maximo EAM system

Categories: Maximo EAM Tags:

Async / Await and SynchronicationContext (C# .Net)

Following innocuous looking async / await code will cause deadlock.

private void button1_Click(object sender, EventArgs e)
{
  Task<string> task1 = LongRunningProcess();

  textBox1.Text = task1.Result;
}

public async Task<string> LongRunningProcess()
{
  string txt = await Task.Run(() =>
    {
      System.Threading.Thread.Sleep(5000);
      return "results";
    }
  );

  return txt;
}

To understand why, lets go into what await does in the above case.

1 Execution starts when the button is clicked and button1_Click event is fired on the UI thread.
2 The method, named LongRunningProcess, is invoked.
3 The lamda expression passed to Task.Run() executes in a separate thread (lets call it thread_B)
4 Now await keyword is encountered, rather than completing the rest of the method, control returns back to button1_Click event to continue execution after the call to long running method.
5 Calling task1.Result makes the current thread wait on thread_B to complete and provide the results. So UI thread is now waiting for thread_B to complete.
6 As thread_B completes the task, it has to run the remaining part of long running method. Run time ensures that this code executes on the right context. That is, if the initial part of long running method was executed on the UI thread, then remaining part will also be executed in the same thread context.
7 Therefore, thread_B now attempts to run the remaining part of long running method on UI thread, while UI thread is waiting for thread_B to finish.
8 As UI thread and thread_B are waiting for each other, this creates the deadlock.
How to avoid deadlock?

In this case, deadlock can be avoided by anyone of the following ways:

1- Use await keyword while calling long running method. This makes the button click event asynchronous also.

private async void button1_Click(object sender, EventArgs e)
{
  textBox1.Text = await LongRunningProcess();
}

2- Call ConfigureAwait(false), this will inform the run time that the remaining part of long running method doesn’t need to execute on the UI thread, it can continue running on the thread pool.

public async Task<string> LongRunningProcess()
{
  string txt = await Task.Run(() =>
    {
      System.Threading.Thread.Sleep(5000);
      return "results";
    }
  ).ConfigureAwait(false); //avoids the deadlock

  return txt;
}
Why thread contexts are synchronized?

Consider the UI Controls (Windows Forms or WPF), they are not thread safe. Therefore, any update to the UI controls must be done only from the UI thread. To take care of this, any remaining code after await keyword in an asynchronous method will also execute on the thread context which initiated the method call.

Similarly, in an ASP.Net application, running the code on same thread context is important because the Culture, Principal and other information of the request are stored in the thread.

How thread contexts are synchronized?

To manage all this tricky context synchronization, we have SynchronizationContext class in .Net. There are framework specific implementations (derived classes) for Windows Forms, WPF/Silverlight and ASP.Net which handle the SynchronizationContext in their own ways for the framework to function properly.

The Windows Forms implementation uses Control.Invoke method to accomplish this (more details here). For ASP.NET, execution takes place on a different thread but the context is captured and passed on to the new thread.

There is an excellent article on MSDN regarding the SynchronizationContext which I recommend for details.

 

Creating and Consuming Async methods (C# .Net)

The code we want to run asynchronously could either be CPU-bound or IO-bound. CPU-bound code keeps the CPU busy, requiring intensive processing, calculations etc. On the other hand, IO-bound code frees up the CPU while waiting for an IO operation to complete, for instance, get some data from a web service. Both kinds of asynchronous methods are illustrated below.

 

 Creating Asynchronous method

 

CPU-bound Async method
public async Task<string> OurMethodAsync()
{
  string x = await Task.Run(() =>
    {
      // performs CPU intensive work
      return LongRunningTask();
    });

  return x;
}

Above method doesn’t do anything special. Rather than performing the work itself, it creates a thread and delegates the work to it. It will not make the task run faster, in fact, it may take more time due to multi-threading overhead. One reason to do things this way is to free the calling thread and not keep it busy for long time. This is required in case of User Interface threads, for instance.

 

IO-Bound Async method

For IO operation such as working with file system, requests to web servers etc., .Net framework already provides us with methods which run asynchronously. These methods uses lower levels OS calls to provide asynchronous behavior for blocking IO operations.

Below is our asynchronous method which uses .Net Framework’s WebClient.DownloadStringTaskAsync method.

public async Task<string> GetWebPage(string url)
{
  var webClient = new WebClient();
  string txt = await webClient.DownloadStringTaskAsync(url);

  return txt;
}

 

 Consuming asynchronous method

 

Consuming asynchronous method with await
private async void button1_Click(object sender, EventArgs e)
{
  textBox1.Text = await GetWebPage("http://www.yahoo.com");
}

 

This button click event consumes the IO-bound async method defined above. If GetWebPage method is taking a long time, the control will return back to the caller method. Once results of GetWebPage are available, execution will start again with the instruction after await keyword. This will ensure that the event does not block the UI thread.

Calling asynchronous methods concurrently

We can also use asynchronous method to run tasks in parallel.

private async void button1_Click(object sender, EventArgs e)
{
  Task<string> task1 = GetWebPage("http://www.yahoo.com");
  Task<string> task2 = GetWebPage("http://www.google.com");

  await Task.WhenAll(task1, task2);

  textBox1.Text = task1.Result;
  textBox2.Text = task2.Result;
}

 

Notice we are not using await keyword now when invoking GetWebPage method. This causes the return type to change also, we are expecting Task<string> rather than string object.

The flow control is also very different. If GetWebPage is blocking, the control doesn’t return to the caller method, rather execution continues to the next statement in the event.

We have await in the third line which will relinquish control to the caller method if task1 or task2 are not completed yet. When the result of the two tasks are available the last two lines of code are executed on the UI thread.

Follow

Get every new post delivered to your Inbox.

Join 44 other followers