Thursday, April 4, 2013

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.


 




 

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.

 

Tuesday, June 5, 2012

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.

Wednesday, May 30, 2012

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.

Thursday, May 24, 2012

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.


PeriodMay-12
Actuals-Current YearBudget/Plan-Current YearVariance (Actuals-Plan)
MetricPeriod to DateQuarter to DateYear to DateTrailing 12 MonthsPeriod to DateQuarter to DateYear to DateTrailing 12 MonthsPeriod to DateQuarter to DateYear to DateTrailing 12 Months
Metric1------------
Metric2------------
Metric3------------




PeriodMay-12
Actuals-Current YearActuals-Prior YearYear Over Year
MetricPeriod to DateQuarter to DateYear to DateTrailing 12 MonthsPeriod to DateQuarter to DateYear to DateTrailing 12 MonthsPeriod to DateQuarter to DateYear to DateTrailing 12 Months
Metric1------------
Metric2------------
Metric3------------

Wednesday, May 23, 2012

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.

MetricPeriod TypeAmount
AssetsYTD300
LiabilitiesYTD250
Assets-LiabilitiesYTD50
Paid-in CapitalYTD100
RevenuesYTD100
ExpensesYTD80
DividendsYTD20
Treasury StockYTD50
Paid-in Capital+(Revenue-Expenses)-Dividend-Treasury StockYTD50


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.