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.
Tuesday, June 5, 2012
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.
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 | - | - | - | - | - | - | - | - | - | - | - | - |
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.
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.
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.
Few is good
If you are into designing dashboards or into information display that is insightful, you probably know or need to know Stephen Few of Perceptual edge. His book and his blog is good. I would strongly recommend reading his books and if you can, attend the class. His book will help you design appealing dashboards that drive action and outcomes which is the ultimate satisfaction for a BI professional.
Tuesday, May 22, 2012
opmnctl
So what really is opmn? Here is a nice link that explains it.
OPMN stands for Oracle Process Manager and Notification Server.
In our Exalytics system (Oracle Enterprise Linux), when I type
opmnctl status
we get this.
Processes in Instance: instance1
---------------------------------+--------------------+---------+---------
ias-component | process-type | pid | status
---------------------------------+--------------------+---------+---------
coreapplication_obiccs1 | OracleBIClusterCo~ | 15105 | Alive
coreapplication_obisch1 | OracleBIScheduler~ | 15104 | Alive
coreapplication_obijh1 | OracleBIJavaHostC~ | 15103 | Alive
coreapplication_obips1 | OracleBIPresentat~ | 15102 | Alive
coreapplication_obis1 | OracleBIServerCom~ | 15101 | Alive
BTW opmnctl is actually a Perl script in case you did not know.
A couple of things to notice.
1.) The pid is the UNIX process id. You can actually confirm that by typing ps -fe | grep 15105 to get the actual process.
2.) The process-type has been abbreviated. They are listed below.
So given this, how do you start and stop individual BI components which are
1.) OracleBIClusterControllerComponent - coreapplication_obiccs1
2.) OracleBISchedulerComponent - coreapplication_obisch1
3.) OracleBIJavaHostComponent - coreapplication_obijh1
4.) OracleBIPresentationServicesComponent - coreapplication_obips1
5.) OracleBIServerComponent - coreapplication_obis1
Of course, you can start and stop them with "opmnctl startall" and "opmnctl stopall" respectively. One of the challenges I encountered was that sometimes the presentation service would not start and you had start it seperately.
So here are the commands to start the process individually. The order is also listed below.
opmnctl startproc ias-component=coreapplication_obiccs1
opmnctl startproc ias-component=coreapplication_obis1
opmnctl startproc ias-component=coreapplication_obijh1
opmnctl startproc ias-component=coreapplication_obips1
opmnctl startproc ias-component=coreapplication_obisch1
So here are the commands to stop the process individually.
opmnctl stopproc ias-component=coreapplication_obisch1
opmnctl stopproc ias-component=coreapplication_obips1
opmnctl stopproc ias-component=coreapplication_obijh1
opmnctl stopproc ias-component=coreapplication_obis1
opmnctl stopproc ias-component=coreapplication_obiccs1
OPMN stands for Oracle Process Manager and Notification Server.
In our Exalytics system (Oracle Enterprise Linux), when I type
opmnctl status
we get this.
Processes in Instance: instance1
---------------------------------+--------------------+---------+---------
ias-component | process-type | pid | status
---------------------------------+--------------------+---------+---------
coreapplication_obiccs1 | OracleBIClusterCo~ | 15105 | Alive
coreapplication_obisch1 | OracleBIScheduler~ | 15104 | Alive
coreapplication_obijh1 | OracleBIJavaHostC~ | 15103 | Alive
coreapplication_obips1 | OracleBIPresentat~ | 15102 | Alive
coreapplication_obis1 | OracleBIServerCom~ | 15101 | Alive
BTW opmnctl is actually a Perl script in case you did not know.
A couple of things to notice.
1.) The pid is the UNIX process id. You can actually confirm that by typing ps -fe | grep 15105 to get the actual process.
2.) The process-type has been abbreviated. They are listed below.
So given this, how do you start and stop individual BI components which are
1.) OracleBIClusterControllerComponent - coreapplication_obiccs1
2.) OracleBISchedulerComponent - coreapplication_obisch1
3.) OracleBIJavaHostComponent - coreapplication_obijh1
4.) OracleBIPresentationServicesComponent - coreapplication_obips1
5.) OracleBIServerComponent - coreapplication_obis1
Of course, you can start and stop them with "opmnctl startall" and "opmnctl stopall" respectively. One of the challenges I encountered was that sometimes the presentation service would not start and you had start it seperately.
So here are the commands to start the process individually. The order is also listed below.
opmnctl startproc ias-component=coreapplication_obiccs1
opmnctl startproc ias-component=coreapplication_obis1
opmnctl startproc ias-component=coreapplication_obijh1
opmnctl startproc ias-component=coreapplication_obips1
opmnctl startproc ias-component=coreapplication_obisch1
So here are the commands to stop the process individually.
opmnctl stopproc ias-component=coreapplication_obisch1
opmnctl stopproc ias-component=coreapplication_obips1
opmnctl stopproc ias-component=coreapplication_obijh1
opmnctl stopproc ias-component=coreapplication_obis1
opmnctl stopproc ias-component=coreapplication_obiccs1
Monday, May 21, 2012
OBIA Group Accounts
Oracle Business Intelligence Applications (OBIA) is really cool. They actually give you a data warehouse, pre-built ETL's that you can configure and get your data warehouse up and running for E-Business, JD Edwards and Peoplesoft. If you had to do it from scratch, it would take forever. In any case, the first thing you will notice that out of the box is that the grouping of accounts is done at the natural account level. So..a little background. General Ledger is made up of several segments. At a minimum, you will need two, a company segment and a natural account. But in reality, most companies have five or six. For example,
Segment1 - Company
Segment2 - Natural Account
Segment3 - Department
Segment4 - Line of Business.
Segment5 - Sub Account
Typically, revenue is reported by line of business and cost is reported by department. So the grouping of accounts would span segments.
Revenue - Segment2, Segment4 and Segment5
Cost - Segment2. Segment 3 and Segment5.
But, the out of the box ETL can do it on one segment- the natural account. So the first thing you would have to do is extend the ETL for the grouping to span segments. Oracle Metalink has a nice note with a how-to on this. It can't get better than this.
You can find it on metalink.oracle.com.
OBIA: How to Create Group Accounts Based On More Than One Segment [ID 1409595.1] An enhancement request has been logged for this as well.
BUG:9202898 - FILE_GROUP_ACCT_CODES_ORA.CSV - USING 4 CONCATENATED SEGMENTS
Or, we can show you how to do it...There are a couple of neat things you can do proactively on top of this to build dashboards that your CFO or others can use.
Segment1 - Company
Segment2 - Natural Account
Segment3 - Department
Segment4 - Line of Business.
Segment5 - Sub Account
Typically, revenue is reported by line of business and cost is reported by department. So the grouping of accounts would span segments.
Revenue - Segment2, Segment4 and Segment5
Cost - Segment2. Segment 3 and Segment5.
But, the out of the box ETL can do it on one segment- the natural account. So the first thing you would have to do is extend the ETL for the grouping to span segments. Oracle Metalink has a nice note with a how-to on this. It can't get better than this.
You can find it on metalink.oracle.com.
OBIA: How to Create Group Accounts Based On More Than One Segment [ID 1409595.1] An enhancement request has been logged for this as well.
BUG:9202898 - FILE_GROUP_ACCT_CODES_ORA.CSV - USING 4 CONCATENATED SEGMENTS
Or, we can show you how to do it...There are a couple of neat things you can do proactively on top of this to build dashboards that your CFO or others can use.
Correct Data
One of the big challenge in BI implementations is data correctness. Because of the E(Extraction), T (Transformation),L ( Loading) and sometimes T (Transformation) of data, the issue could lie along any of these areas and it can be difficult to troubleshoot and resolve. So we came up with an approach to resolve this.
The ETL or the EL-TL nature of BI Data can lead to this and being able to triage this quickly will be crucial not only during implementation but during the support of the system as well.
Starting and Stopping Exalytics
First thing we need to know is that, when you install OBIEE on Exalytics, it does not do a simple install. It does the Enterprise Install of OBIEE. That means the BI Server on the web logic is installed on port 9704. So what does this mean? It means that if you did a simple install, starting weblogic will start the Admin Server and the BI Server. But now, you have to start the Node manager and the BI Server seperately.
Startup:
1.) Start Times Ten
2.) Start Web Logic Admin Server
3.) Start Web Logic Node Manager
4.) Start Weblogic Managed Server - BI Server on weblogic
5.) opmnctl startall to start all the BI Components
URL's.
Web Logic Console: http://hostname:7001/console
Web Logic EM: http://hostname:7001/em
OBIEE: http://hostname:9704/analytics
BI Publisher: http://hostname:9704/xmlpserver
The shutdown order is the reverse of the above.
1.) opmnctl stopall to stop all BI components
2.) Stop Weblogic Managed Server - BI Server on Weblogic.
3.) StopWeblogic stop Admin server and node manager
4.) Kill the node manager process
5.) Stop Times Ten
I will publish the script in a couple of days after I clean it up for security reasons.
In any case, we used Chris's script on Oracle Forums to get this going. Chris, your rock. Chris Rock?
There is a master note in Oracle Metalink for OBIEE start/stop that is very useful as well as for reference. Master Note For Start / Stop Of OBIEE 11g [ID 1423264.1]
Startup:
1.) Start Times Ten
2.) Start Web Logic Admin Server
3.) Start Web Logic Node Manager
4.) Start Weblogic Managed Server - BI Server on weblogic
5.) opmnctl startall to start all the BI Components
URL's.
Web Logic Console: http://hostname:7001/console
Web Logic EM: http://hostname:7001/em
OBIEE: http://hostname:9704/analytics
BI Publisher: http://hostname:9704/xmlpserver
The shutdown order is the reverse of the above.
1.) opmnctl stopall to stop all BI components
2.) Stop Weblogic Managed Server - BI Server on Weblogic.
3.) StopWeblogic stop Admin server and node manager
4.) Kill the node manager process
5.) Stop Times Ten
I will publish the script in a couple of days after I clean it up for security reasons.
In any case, we used Chris's script on Oracle Forums to get this going. Chris, your rock. Chris Rock?
There is a master note in Oracle Metalink for OBIEE start/stop that is very useful as well as for reference. Master Note For Start / Stop Of OBIEE 11g [ID 1423264.1]
Oracle Exalytics Installation
We have installed Oracle Exalytics in our COE at Keste. The install is pretty easy and straightforward. After the machine is racked and the operating system which is installed at the factory by Oracle is stood up, the network interface(s) needs to be defined.
Then, you download the media pack from here. For the product pack, you need to chose Oracle Business Intelligence and for Platform, you chose Linux X86-64.
The install guide is well written, easy to follow and execute. It can be found here.
Then, you download the media pack from here. For the product pack, you need to chose Oracle Business Intelligence and for Platform, you chose Linux X86-64.
The install guide is well written, easy to follow and execute. It can be found here.
Exa Exa Exa
Keste is the company that I work for and we love Oracle Exa three fold. We are the only Oracle Partner in the US and maybe the world (as of 21-MAY-2012) to host on site in a center of Excellence. We have the Exadata for the database, Exalogic for Weblogic and Exalytics for Oracle Business Intelligence. And we have the Oracle Database Appliance as well.
As we say in Texas, yee-haw, yee-haw, yee-haw.
In any case, this is the Keste Exalytics blog. We will be posting a lot of technical content on this so you can learn, have fun and may be even talk to us about Exa.
As we say in Texas, yee-haw, yee-haw, yee-haw.
In any case, this is the Keste Exalytics blog. We will be posting a lot of technical content on this so you can learn, have fun and may be even talk to us about Exa.
Subscribe to:
Posts (Atom)