I found this diagram while surfing some other OBIEE blogs and forums today.  I think it is very useful so I wanted to post it here as well.  It outlines how variables are stored and accessed in the different parts of OBIEE.  Hope this helps.  Enjoy!

What is Oracle Business Intelligence (OBIEE).  I have seen this question coming up more and more on different blogs and websites, yet there aren't any really good answers.  Most people who ask this question really have no idea what the software is or does and still walk away wondering what it really is.  I recently hosted a discussion panel on OBIEE at Oracle's ALM (Asset Life-cycle Management) conference in Daytona, Florida.  The discussion panel started with a very brief introduction to some of the pieces of OBIEE and then was opened up to the floor for an extended question and answer period.  In the end I thought the session went quite well, but I still felt that many people left with a little more information about this product called OBIEE but still were not quite sure what they would be buying if they had to purchase it that day.  So in this post I am hoping to give you a little bit of an idea as to what OBIEE is and what it would take to implement such software.

What is OBIEE?

Oracle Business Intelligence Enterprise Edition is a complete analytics tool including server, administration tool, answers and dashboards.  The tool was acquired by Oracle when they purchased Siebel analytics.  The product itself has not changed much with the change to Oracle.  I believe there are other aquisitions that were involved but I don't think that has any relevance here.

Something I have learned along the way is that simply telling people what the different components of OBIEE are isn't enough for them to truly understand what it is, so I am going to give a brief breakdown of what each component is used for.

Oracle BI Server

o   Is the core behind Oracle Business Intelligence

o   Provides access to physical data sources effieciently and intelligently

o   Transparent to both end users and query tools, functions as the integrating component of a complex decision support system by acting as a layer of abstraction and unification over the underlying databases.

o   This allows users to ask business questions that span information sources across the enterprise and beyond

Oracle BI Repository

o   The Oracle BI Repository is built and managed with the Oracle BI Administration tool and provides the information and measures to the clients used in creating reports.

o   Import metadata from databases and other data sources

o   Simplify and reorganize the metadata into business models

o   Structure business models for presentation to users.

Oracle BI Presentation Services

o   Presentation services handle the communication between the Administation tool and the Oracle BI Client.

o   Provides the processing to visualize the information for client consumption

o   Is implemented as an extension to a web server

o   Uses a catalog to store saved content such as dashboards and requests

o   Receives data from an Oracle BI client, retrieves data from Oracle BI Server and provides it to the client that requested it

o   Is run as a service on the Oracle BI Server with BI server service

Oracle BI Client

o   The client is a webbased tool that allows users to create Answers or Reports using the data exposed from the BI Administation tool.  Users can then take these Reports and add them to dashboards with filters and prompts.

Physical Data Sources

o   OBIEE supports multiple connections to almost any type of data source such as Oracle, SQL Server, Microsoft Access, ODBC, XML, etc.

It is important to know that OBIEE is a complete analytics tool as I mentioned before.  It should be given it's own server to run the BI server and host the repository.  If you are working with very large amounts of data then a data warehouse is generally best practices but the BI Administration tool can create direct connections to the physical datasources as well. 

One other topic I do want to touch on because I feel it is important for people who are trying to make the decision as to whether or not OBIEE is for them or thier organization,   is what are the advantages of OBIEE over what I currently have?

This question is better answered by asking the question, what is the advantage of an analytics tool vs a standard reporting tool?  I will try to give a few points that may help you in deciding.

Analytics (OBIEE) Vs. Standard Reporting (Discoverer, Actuate, etc)

·         Analytics tools offer a way to interac with the data and ask questions without needing any knowledge of SQL or database programming.  The need for knowing the data is taken away from the report writer.

·         Standard reporting is simply a snapshot of a certain set of data and one report generally only answers one question.

·         One Answer or Report in OBIEE or an analytics tool can answer the sames questions as multiple Discoverer or SQL reports

·         With standard reporting tools you need to have IT resources available to create new reports when users need them or the users need to wait.  This causes a delay in productivity.  With OBIEE Answers users can easily create new reports or modify existing ones without needing deep technical skills.

·         Users see and intuitive way to buid Answers and Reports.  Do not need to have any knowledge of SQL or databases.  Drag and drop capabilities allow them to create and modify reports and answers quickly and easily

·         More complete dashboard capabilities that are simple for the user to manipulate

·         More robust customizations and filtering capabilities.

·         User based web interface.  The ability to have multi language reports and dynamic data based on server variables such as locations and time zones.

These are just a few of the advantages that came to mind while writing this blog but hopefully this will help give people a deeper understanding of what OBIEE is and what it can give you as a reporting and analysis tool.

I just recently returned from the Oracle ALM (Asset Life Cycle Management) / RCM Summit where I participated in three lectures.  One of the discussions I presented was on Oracle's new reporting software, which is really the old Sybel analytics tool, OBI EE.  There was quite a bit of interest and I think Oracle customers are really going to start to realize the strength of this application as it grows in the market space.  In light of the excitment around OBI EE and what I found to be a lack of knowledge or real understanding of the product from the customers out there, I have decided to start a new tutorial section that will focus on the development of OBI EE reports and meta layers. 

The first tutorial that has been added is a quick one on how to add a View By filter to a report in OBI EE.  This will allow you to have a single report that can dynamically change based on the selected value.  So instead of needing 3 reports to view costs by Company, Department and Products, you could have one report with a View By drop down where the use can select which break down they would like to see.

To view the tutorial click on the following link.  I was a bit rushed on this tutorial but I do believe it covers the necessary points.

Just laying around and thought I would write a quick post on writing basic SELECT statements.  I realize that some of my blogs will seem simple to some, but the main goal of my blog and the main website is to help consultant, programmers and/or anyone else for that matter.  I have met far too many consultants who bear the title "Technical Consultant" that cannot perform the most basic technical tasks.  With this in mind I have started this site and blog to help give consultants or anyone else a place to learn and share.

Alright, back to the topic at hand.  Many times the first place to start when approaching a database problem is to view the data in question.  This can be as simple as selecting all the data in one table, some of the data in one table, or it can be far more complex, joining many tables with complex conditions.  I am going to focus on the simple cases that most consultants would see on a regular basis.

The most basic structure of a SELECT statement is as follows

SELECT column1, column2, .....columnN FROM table_name;

This will return all rows of data in the table but only the columns which are listed.  A very common alternative is

SELECT * FROM table_name;

This will return all columns and all rows from the given table name.  But what if we don't want all the rows.  In this case we apply a WHERE clause to the end of the SELECT statement.

SELECT * FROM table_name WHERE condition1 [AND, OR] condition2 .....[AND, OR] conditionN;

This will select all columns from the table but only those rows that meet the specified conditions.  For example

SELECT COMPANY_NAME, ADDRESS FROM COMPANY_INFO WHERE STATE = 'New York';

Will return all company names and addresses for the state of New York only.  We could add more conditions using AND as well as OR as follows

SELECT COMPANY_NAME, ADDRESS FROM COMPANY_INFO WHERE (STATE = 'New York' OR STATE='Texas');

Another common situation that I come across is checking to see if a given value exists in a list.  For this we can use the IN statement.

SELECT COMPANY_NAME, ADDRESS FROM COMPANY_INFO WHERE STATE IN ('New York','Texas');

Alternatively this list could come from another table.  This requires us to use and embedded SELECT statement.  We do this by surrounding the second where clause with parenthesis.

SELECT COMPANY_NAME, ADDRESS FROM COMPANY_INFO WHERE STATE IN ( SELECT STATE FROM STATE_TABLE WHERE STATE_NAME IN ('New York', Texas') );

The last case that I want to cover before falling asleep is when the data we want to view is also in different tables.  Lets assume, although maybe a little extreme, that there is a table COMPANIES for company names, STATES for the state and ADDRESSES for the address of the company.  All table contain the same primary key of COMPANY_NAME.  If we wanted to select the same data as we have above we could write

SELECT c.COMPANY_NAME, a.ADDRESS, s.STATE
FROM COMPANIES c, ADDRESSES a, STATES s
WHERE
a.COMPANY_NAME = c.COMPANY_NAME AND
a.COMPANY_ NAME = s.COMPANY_NAME;

I don't want to get into too much detail on joins in this post but this would be an inner join.  We are only going to return the values where the COMPANY_NAME is equal between the COMPANIES table and the other two tables.

One last point that almost all consultants will need to know at some point is how to return the number or records returned by a given query.  We do this using the COUNT statement.

SELECT COUNT(*) FROM table_name;

This will return the total number of rows for the table given.  Alternatively we can supply the COUNT method with any SQL query that returns any number of rows.  For example

SELECT COUNT( SELECT COMPANY_NAME, ADDRESS FROM COMPANY_INFO WHERE STATE = 'New York')) ;

I know this is far from a perfect tutorial but hopefully it can help someone looking for a quick answer.

I have added a new tutorial group for Microsoft Access on the main site.  There are a few tutorials on how to perform different functions on an Access database through the use of VBA (Visual Basic for Applications).  The tutorials include, creating table, deleting tables, renaming tables, creating and modifying record sets etc.  These simple functions can be very useful when performing large data migrations etc.

Link to Microsoft Access Tutorials

I have added a tutorial to the main web site the outlines how to create a Captcha control using Silverlight 2 and WCF services.  It is a simple solution that worked best for what I needed it for.  If anyone has any ideas on how to improve this I would be more than happy to incorporate them into the tutorial.

Link To Tutorial

Source Code:

I struggled with this concept for almost a week before finally coming up with an answer.  I have to give full credit to the users of the OTN (Oracle Technology Network) for helping me through this problem.  The time and effort that I went through to solve this problem has prompted me to create a blog about it in hope of saving someone else the time.

Problem:  How to get the Day, Month and Year values returned by a Calendar prompt in OBI EE.  The reason I needed this was because the Calendar prompt will always return it's value in the format dd/mm/yyyy (eg. 01/01/2009).  This remains true even if you modify the settings in your localedefinitions.xml file.  You can change the way the Calendar prompt shows the values but the returned value will always be the same.  This still doesn't explain the actual problem.  In my case I wanted to create an AS OF DATE.  So if the user selected a date, and a period (Year, Month, Quarter), the report would change to show the appropriate columns such as YearAgo, Month Ago or QuarterAgo.  This meant that I needed to know the Year and Month of the currently selected date. 

The real problem exists not in the Calendar functionality but more in the way the YEAR and MONTH functions work.  These functions will only return a result if the date format provided is dd-MMM-yyyy (eg. 01-JAN-2009).  Casting the value to change the format from within Answers I found to be impossible (I am more than open to someone proving me wrong on this ).  So in the end the answer was to extract the Month, Year and Day information through the use of Substring.  The reason this works is because the date format from the Calendar never changes, so in theory this should work for all users regardless of area.

Solution: 

Create a prompt with a calender prompt that populates a Presentation Variable called AsOfDate

Day: substring( '@{AsOfDate}{01/01/1900}' from (locate('/','@{AsOfDate}{01/01/1900}',1) +1)for (locate('/','@{AsOfDate}{01/01/1900}' ,4) - locate('/','@{AsOfDate}{01/01/1900}',1)-1))

Month: substring( '@{AsOfDate}{01/01/1900}' from 1 for (locate('/','@{AsOfDate}{01/01/1900}' ,1) -1))

Year: substring( '@{AsOfDate}{01/01/1900}' from (locate('/','@{AsOfDate}{01/01/1900}' ,4) +1) for 4)

I then used these values to extract the information I needed from my periods table.

In closing, if the user selects Period = Year and from the calendar they select AsOfDate=01/01/2009 my report will dynamically select the Year column, some other measure, and some other measuer a year ago where the year is less than 2009.  Hence, giving the report of Year and Year ago as of 2009.  I hope this helps.  And if anyone can show me a better or cleaner solution to this I would really be happy

Seeing as this is the first blog I have ever written I thought I should keep it fairly simple.  Today has been marked as the official launch of ConsultantWarehouse.com.  The site is obviously still in the early stages of development but is well on it's way.  The first tutorial was added today, which is why I have decided to name today as the official launch date.  Original I know .  The tutorial covers how to implement a Captcha control using Silverlight 2 and WCF services.  There is plenty more content waiting to be added but I also encourage anyone who has a topic they would like covered or even a solution they would like to see on the site to please let me know.  All credit will be given to the authors of new content.  I hope this site can grow into a community where consultants and programmers can find quality solutions to common problems without having to search through blogs and forums.  That's all for now.  Enjoy