Get Last Business Day of each month using OBIEE

Hello

Today I want to share with you a technique that I use to intelligently get last business day of each month in OBIEE taking accounts of holidays, this technique is useful when all you have is just a basic time dimensions with year, month and date to start with:

Now, the first step is to identify all of the day number of each months and the maximum day number of each month. OBIEE has native function to get you the day number in a month.

So I am going to create 3 columns:

Date number: dayofmonth(date)
Last Day of the month:  Max(Dayofmonth(date) by year, month)
Condition: Compare Date number and Last Day of the month, if they are equal, then 1, else 0

The result of the above report will look like the following, and the row that's highlighted is going to be the last day of each month, because the maximum date number in Jan is always 31 and it happens to be equal last day of the month for THIS ROW.

Now, simply apply a filter where condition = 1, then the report will always give you last day of every month:

Now that we have the last day of the month, we now need to determine whether that happens to be the business date or not. So if it falls on saturday, then the business day has to be one day prior; if Sunday, then 2 days prior; if on Monday and holiday, then maybe 3 days prior depending on your company's rules. So we can program these things using case statement with timestampadd.

First, add another 3 columns:

Last Business Day: This converts day into day names, such as Monday, Tuesday, Friday etc.
Last Business Day Case: This is where the main logic of determining what's business day is created.

Here in my company, the rules are pretty simple for holidays. If the last Monday of May happens to be May 31st, which is memorial day, then last business day of May of that year should be 3 days prior; if 12/31 falls on Tuesday thru Saturday, then last business day should be 1 day prior; if Sunday, then 2 days; if Monday then 3 days. The rest, just weekend scenarios, which is either minus 1 day or 2 days.

So my formula is the following:
case when DAYNAME("Time"."Date") =  'Sun' THEN TIMESTAMPADD(SQL_TSI_DAY, -2 , "Time"."Date") when DAYNAME("Time"."Date") =  

'Sat' then TIMESTAMPADD(SQL_TSI_DAY, -1 , "Time"."Date") when DAYNAME("Time"."Date") in ('Sat','Fri', 'Thu', 'Wed', 'Tue') 

and "Time"."Month" = 'Dec' then TIMESTAMPADD(SQL_TSI_DAY, -1 , "Time"."Date")  when DAYNAME("Time"."Date") = 'Sun' and 

"Time"."Month" = 'Dec' then TIMESTAMPADD(SQL_TSI_DAY, -2 , "Time"."Date")  when DAYNAME("Time"."Date") = 'Mon' and 

"Time"."Month" = 'Dec' then TIMESTAMPADD(SQL_TSI_DAY, -3 , "Time"."Date")  when "Time"."Month" = 'May' and DAYNAME

("Time"."Date") = 'Mon' then TIMESTAMPADD(SQL_TSI_DAY, -3 , "Time"."Date")
else "Time"."Date" end

Now for the use case. The user want to be able to select any integer from 1 to 12, and the report should show data for the last business days of each month for the last 1 to 12 months including the date the user pass from the prompt.

Therefore, This report has to have a filter on date, which is Date <= @{Date}. This not only accepts the user input date, but also allows the report the show all of the past days too.

Add another column that Rank dates: Rank(date). This column will return integer as a ranking number from high to low.

More importantly, apply a filter on rank(date) column:
Rank(date) <= @{N}.  This will allow the report to run for N number of past month's last business date based on user inputs.

Now lets test the report. As you can see, my default user input date is 5/2/2016, and default number is 6. Now I am getting 5/2/2016 as well as 5 other dates in the past that happens to be past month's last business date.

An actual report may look at this as below where the user select dates, period level and number of period on the left and he report shows last N months or week's of last business date from user selected date:

Thanks, until next time

Read More

Learning python for BI

Hello All

What does Python have to do with BI report? Well maybe no much directly in enterprise environment, but python can be very useful for my garage project. I want to build a Data visualization product that shows real time update of certain market driven products and get users to subscribe to it for a monthly fee, that's what python can be handy for my ETL process.

Just to give an idea, I may choose to store my data in one of these open source DBs like Hadoop Hive, Cassandra or Mongo DB, and I can use python to pull data from whatever source that i want, for example zillow.com, if i want to analyze all of the real estate properties in America. For visualization, i can use Tableau to display the market trend and my analysis..

Now you may think that my idea sucks and there are so many real estate websites out there already. Well of course the idea sucks because this is not what I am really trying to explain you here, if i had a great idea, I wouldn't be letting it out until i secure my billion dollar patent first. Duh..

Anyways, Python can be a very powerful yet quite sample programming languages that supports various DB commends (of course, much more than just doing things with DB), so it's worth your time to look into it if you are a techy guy.

Get good at it and you can write your own ETL tool.

A good place to start if you have very little experience is here:
http://learnpythonthehardway.org/book/

Thanks

Until next time

Read More

Tableau Visualization -- How to build Waterfall chart

Waterfall chart can be used in accounting to show trends in the horizontal time span usually as result of positive and negative influences in sequential order. We will look at the product's profit over years.

To start, lets have profit in row and  product and time in column:

As you can see, some product creates positive profit and some creates negative profit.

Now let's add a new table calculation off profit to get the running total of the profit:

Now the chart looks like this:

We want to distinguish the negative from the positive. To do that we create condition on profit by adding a new calculated field ('profitable ?') with the following formula: SUM([Profit])>=0

This will create a binary output: greater than 0 or less than 0

Putting 'Profitable ?' into the color windows, the chart is colored:

Now change the view from automatic to ''Gnatt" and the chart changes:

Put profit into size windows so that all of the Gnatt size will change based on the profit amount:

Now you might notice that all of the blue color Gnatt bars are growing downwards while the yellow ones are growing upwards. This is because the blue color happens to represent negative profits. Some user maybe okay with the way this chart looks, while others may prefer to flip the blue bars and make it go upwards just like the rest. To do so, let's create another calculated field call 'Negative profit' with the following formula:
-[Profit]

Put 'negative profit' into size windows and now all of the bars are facing the same direction:

Thanks

Until next time

Read More

OBIA Financial analytic AP Process 101

Today we are looking at the basic process of AP or Accounts Payable in Oracle EBS for financial analytic. This is to give you a basic understanding of how AP process works for most companies that implements Oracle EBS. In order to build AP reports out of EBS source, you need to understand the basics.

Although different companies will have different accounting process and different product dealings, the basic AP process is comparable.

Based on the below diagram, the AP process includes 4 main blocks:

Purchase Orders:

These are the purchase orders that the company has decided to execute through Oracle IProcurement. It basically means that the company has decided to buy, this can include items for the office, materials that the company products need, the contract for consultants, laptops, speakers, insurance services or electricity and gas.

PO usually consist of 3 hierarchies:
Header - highest level.
Line - under each header, there can be multiple lines.
Distribution - Under each line ordered, it can be used for different locations or cost centers. Manage A from this department uses it for a few weeks and then Manager B from another department uses it. From accounting perspective, it may belong to different cost centers

Invoices:
This is when the invoices of these PO have been entered. They have mirror the hierarchical structure as Purchase order. Each of the 3 AP Invoice tables can join to the corresponding PO tables.

Receives
Now that the company has been invoiced by vendors, they will receive goods and services that they purchase. The act of receiving is recorded in RCV_TRANSACTIONS table as transactions. Note that receives are at invoice line level. If your company makes a lot of order from one vendor and the goods will be delivered throughout a period of time continuously, you may want to check the Receipt_Date and take the Max of this date for each transaction, or it could potentially influence the grain of each invoices. As an example, most of the time you are interested in seeing how much you have totally spent in your trip to the local grocery store but not necessarily interested at each items you purchase, or at the end of a period, how many chairs does your company receive rather than how many chairs received on each day.

Hold
Hold indicates the status of your company's decision on handling the invoices. Let's say after receiving the invoices, the department realized that it doesn't have all of the goods that they thought they ordered. This could be categorized into 2 main reasons:

1. The company makes a mistake in dealing with the transactions. Maybe the goods are delivered to the wrong department or miscounted. The error lies within the company, therefore they are still responsible for making the payment on time according the to invoice.

2. The company thinks that the vendor has not completely delivered their services according to the PO. The order says 100 laptops but the company only receives 95, therefore the company puts this invoice on hold and waiting for the delivery of the remaining 5.

These information will be stored in AP_INVOICE_HOLD_DETAIL table and HOLD_TYPE will indicate what type of hold this invoice is. For each Invoice_ID, there can be multiple HOLD_LOOKUP_CODE to tell you the specific reasons for being on hold.

Now if the hold is deemed to be vendor not fulfilling its responsibility, the HOLD_RELEASE_DATE column will be used for the invoice payment due date, that is, until the error has been corrected, the hold will be released.

The payment_date and due_date are stored in various tables, but AP_PAYMENTS_ALL, which is the block at the bottom of the 4 blocks in the diagram, generally stores this information.

So for accounting purposes, the payment date and due date are based on HOLD conditions.

The process of determining what HOLD condition is, or whether there should be a hold or not, the called 3-way match, which means you have to compare the PO to invoice to the receipts to see if they matches or not.

If there is reporting requirements that needs OBIEE report that does these kind of comparisons, you should already know the logic and the type of report that will make sense.

Thank you

Until next time

Read More

Tableau Visualization -- How to build Gnatt Chart

Gnatt chart can be useful in showing status of large number of items and things with respect to time. An example of that is displaying the various flight time from each route and determine which has the longest delay or longest flight time as reflected on the chart by the size of each Gnatt.

In my example, i am going to show for a given year, how are the order to ship time is like for each product in the warehouse.

So start with product dimension (Row section) and order date (Column section):

change the order date display format to 'exact date'

Now to figure out the exact order to ship time, we simply go a date diff between order date and ship date. This is similar to timestampdiff function in SQL, but in tableau we use a different syntax by create a new calculated field called 'Time to ship' with the following formula:

DATEDIFF('day',[Order Date],[Ship Date] )

Sometimes, there are different types of shipping. So if you want to make a distinction among the dimension with certain categorization, we simply put the categorizing field into the color window. So in this case, different product has different ship mode that we want to distinguish by, so simply drop ship mode into the color section as shown below and the chart is colored.

Now move 'time to ship' field into size windows. The size of each bar will start to differ more noticeably based on the number of days differ.

We also want to include the ship date as part of the display in details for validation purpose. So we pick ship date and drop it into detail windows

Now the chart is done. We can move the mouse over any bars and the detail information will display from the details:

here you can see the time to ship for applicance is 17 days and it shows you the date ordered and the date shipped

Thanks

Until next time

Read More