Outer Join - Oracle and ANSI SQL Syntax Answer

This  refers to the previous blog here.
The Standard Oracle syntax is pretty straightforward.

SELECT fooa.foo1,
  fooa.foo2,
  foob.foo2
FROM foo fooa,
  foo foob
WHERE fooa.foo1 =foob.foo1(+)
AND fooa.foo3   ='Coming'
AND foob.foo3(+)='Going'


The ANSI SQL syntax is a little trickier.

SELECT fooa.foo1,
fooa.foo2,
foob.foo2
FROM foo fooa
LEFT OUTER JOIN foo foob
ON (fooa.foo1 =foob.foo1
AND foob.foo3 ='Going')
WHERE fooa.foo3='Coming'

There you go for outer joins.

This should be a very good interview question.
 

Outer Join - Oracle and ANSI SQL Syntax Question

Outer joins are really fun. Well kind of. Anyway, here is an example.

The Optimizer blog from Oracle is very useful as well. Oracle Optimizer Team Blog

The setup.

create table  foo (foo1 number, foo2 varchar2(5),foo3 varchar2(10));

insert into foo values (1,'In1','Coming');

insert into  foo values (1,'Out1','Going');
insert into  foo values (2,'In2','Coming');
commit;

select * from foo;

FOO1	FOO2	FOO3
1	In1	Coming
1	Out1	Going
2	In2	Coming

 We need the following output for a report.

 

FOO1	FOO2	FOO2
1	In1	Out1
2	In2	(Null)

 

Try this for yourself. The answer is in the next blog.

An excellent reference.

https://blogs.oracle.com/optimizer/entry/outerjoins_in_oracle

 

 

OBIEE 11.1.1.7 is now out

OK..it has been a while. I am hoping to have some time to update this blog.

Oracle  released the OBIEE 11.1.1.7 version of the software. It is available for download.

The Link

As for Exalytics, this version is not certified yet. It will be soon.

 

OBIEE 11g Displays Table Pivot Ticker

One of the interesting thing is that if you actually read a document, you actually get to know neat things. For e.g., this document in OBIEE 11g tells the different options you have for displaying data in OBIEE dashboards and answers. For example, you can control how your pivot table, table and tickers look in OBIEE. This is set up in instanceconfig.xml. This table in Oracle documentation is a good reference.
For example, a pivot setting could look as follows in the instanceconfig.xml file.

<Pivot>
        <MaxCells>6000</MaxCells>
        <MaxVisibleColumns>300</MaxVisibleColumns>
        <MaxVisiblePages>1000</MaxVisiblePages>
        <MaxVisibleRows>500</MaxVisibleRows>
        <MaxVisibleSections>25</MaxVisibleSections>
        <DefaultRowsDisplayed>30</DefaultRowsDisplayed>
</Pivot>

You could use the table to control the various display settings for Graph, Table, Pivot Table, Ticker and a narrative.

Books on Analytics

The whole purpose of Business Intelligence is better Analytics. Analytics is the framework for data driven decision making instead of using the "gut instinct". "Gut Instinct" can take you some distance but analytics can give you the competitive advantage over people and firms who use just the instinct. There are two books that are popular in this area. One is "Competing on Analytics-The new science of winning" and the other is "Analytics at Work: Smarter Decisions, Better Results". Both books are insightful and are useful in making the case for analytics and its role in decision making. Both of them are written by Thomas H. Davenport and Jeanne Harris. It is a good read.

Financial Dashboards

Over the years, working with Finance people, they constantly ask for these metrics.

They are usually interested in Period to date, Quarter to Date, Year to Date and Trailing 12 months numbers. And they like to know the actuals, budgets, variance between actuals and budgets, Prior year numbers and year over year growth or decline as the case may be.

So when working with these folks, if you can build these metrics proactively, you will win the trust of the teams in accounting and finance.

Period

May-12

Actuals-Current Year

Budget/Plan-Current Year

Variance (Actuals-Plan)

Metric

Period to Date

Quarter to Date

Year to Date

Trailing 12 Months

Period to Date

Quarter to Date

Year to Date

Trailing 12 Months

Period to Date

Quarter to Date

Year to Date

Trailing 12 Months

Metric1

-

-

-

-

-

-

-

-

-

-

-

-

Metric2

-

-

-

-

-

-

-

-

-

-

-

-

Metric3

-

-

-

-

-

-

-

-

-

-

-

-

Period

May-12

Actuals-Current Year

Actuals-Prior Year

Year Over Year

Metric

Period to Date

Quarter to Date

Year to Date

Trailing 12 Months

Period to Date

Quarter to Date

Year to Date

Trailing 12 Months

Period to Date

Quarter to Date

Year to Date

Trailing 12 Months

Metric1

-

-

-

-

-

-

-

-

-

-

-

-

Metric2

-

-

-

-

-

-

-

-

-

-

-

-

Metric3

-

-

-

-

-

-

-

-

-

-

-

-

Accounting Equation and BI

If ever there was a place where engineering meets the business world, this would be it. So what has BI got to do with the Accounting Equation? What is the accounting equation anyway? This link explains it well.

The basic accounting equation is as follows.
Assets = Liabilities + Owner's Equity

Assets and Liabilities are clear enough. But what is owner's equity? So we expand it to be more meaningful.

Assets = Liabilities + (Paid-in Capital + (Revenues – Expenses) – Dividends – Treasury Stock)

Assets-Liabilities = Paid-in Capital+(Revenue-Expenses)-Dividend-Treasury Stock

There is a little caveat. While Assets, Liabilities, Paid-in-Capital, and treasury stock are a point-time, revenue and expense are for a period. So Revenue and Expenses have to be at a point-in-time as well which is typically year-to-date. Dividends are slightly different but they need to be Year to Date as well.

Revenue-Expense is actually Net Income but you have to use the year to date metric.

So a dashboard which looks as follows will give a lot of confidence to the accounting and finance staff as it meets the accounting equation requirement.

Metric	Period Type	Amount
Assets	YTD	300
Liabilities	YTD	250
Assets-Liabilities	YTD	50
Paid-in Capital	YTD	100
Revenues	YTD	100
Expenses	YTD	80
Dividends	YTD	20
Treasury Stock	YTD	50
Paid-in Capital+(Revenue-Expenses)-Dividend-Treasury Stock	YTD	50

In this case, the assets less the liabilities equal the total of Paid-in Capital+(Revenue-Expenses)-Dividend-Treasury Stock. In any case, the accounting staff is going to check these numbers on their favorite calculators.

So from a BI perspective, if you can design a high level dashboard that meets this requirement, you have won the trust of the accounting and the finance people. It makes the reporting meaningful and it saves a lot of trouble. You could actually test your dashboards for Financial accounting data with this simple equation.

When I build these dashboards, I use this to ensure that I QA the data and if it does not add up, you know you have work to do.