Dylan's BI Study Notes

My notes about Business Intelligence, Data Warehousing, OLAP, and Master Data Management

Archive for the ‘OBIEE’ Category

Oracle Business Intelligence Enterprise Edition

Users of Analytics Applications

Posted by Dylan Wan on May 21, 2017

Business User who are consuming the data and the report.  They see the information pushed to them.  They can see alerts in their phone.  They see emails.  They add the page to a bookmark in their browser and periodically look at them.   They are executives, managers, busy users who have other duties.   They don’t spend much time.  They may not come to see the data every day.  In a school system, they are teachers, principals, vice principals, school counselors.  In an corporation, they are the business partners of IT.  They are controllers, account payables, contractor admin, buyers, billing clerks.  Many of them know what information they would like to have and information is essential for their jobs.  They do not like complex navigation.  They would like to see dashboards or data integrated into their portal or the applications they have to use. Read the rest of this entry »

Posted in BI, Business Intelligence, Incorta, OBIEE | Leave a Comment »

Schema-less or Schema On Demand

Posted by Dylan Wan on January 29, 2017

I am trying to define what are the criteria for a schema-less, or a schema on demand data storage.

In relational database, we use DDL, data definition language, to define schema.

We have to create table first, before we can insert data into a table.  When we update data, we update the data by mentioning the name of columns.  We use DDL before we use DML.

We have to know the name of columns in order to construct the SQL statement with specific select clause.  If a column does not exist, the system throws an error when we try to query. SELECT * FROM does not have the requirement.  CREATE SELECT also kind of giving us some tolerance, but the table it creates will be unstable.  INSERT.. SELECT * will be a bad practice as when the source schema changes, this statement becomes broken.

Schema describes the name of table, the names and orders of the columns, and the data type (or domain) of the columns.

Here are what I feel possible and something we can pursue: Read the rest of this entry »

Posted in Business Intelligence, Data Warehouse, EDW, OBIEE | Leave a Comment »

OTBI vs. OBIA

Posted by Dylan Wan on October 5, 2011

Several people are curious about what are OTBI and OBIA, and what are the differences between OTBI and OBIA.  I will discuss these in this article.

OTBI stands for Oracle Transactional Buisness Intelligence. 

OBIA stands for Oracle Business Intelligence Applications.

Let’s start with OBIA.  OBIA is the pre-packaged BI Apps that Oracle has provided for several years.  It is the data warehouse based solution.  It is based on the universal data warehouse design with different prebuilt adapters that can connect to various source application to bring the data into the data warehouse.  It allows you to conslidate the data from various sources and bring them together.  It provides a library of metrics that help you measure your business. It also provides a set of predefined reports and dashboards.  OBIA works for multiple sources, including E-Business Suite, PeopleSoft, JDE, SAP, and Fusion Applications.

OTBI is different.  First of all, it is a real time BI.  There is no data warehouse or ETL process for OTBI.  Second, it is for Fusion Apps only.   OTBI is leveraging the advanced technologies from both BI platform and ADF to enable the online BI queries agains the Fusion Applications database directly.  In addition, in some area, such as Financial, you can also connect to the Essbase cubes.  Unlike OBIA, OTBI does not have a lot of prebuilt dashboards and reports.  The reason is that for some advanced analysis, the data need to be prepared.  You cannot get eveything you can get from the OBIA data warehouse in OTBI. 

Both OTBI and OBIA are available from the same metadata repository.  Some of the repository objects are shared between OTBI and OBIA.  It was designed to allow you have the following configurations:

  • OTBI Only
  • OBIA only
  • OTBI and OBIA coexist

If you implement Fusion Apps, you can enable OTBI.  You can use the BI EE Answer to access the prebuild metadata and metrics those are built against the Fusion Apps.  You may not get the full powerful prebuild dashboard and repost and prebuilt navigation workflow.  However, you can start experiencing what the BI EE based reports look like.  You can start bring the data out from your OLTP system.  You can provide training to the users to get familar with the subject areas, some of which are shared with OBIA. 

If you enjoy OTBI and want to further get OBIA with a data warehouse based solution.  You can implement OBIA later.  Some of the OTBI reports maybe switched to run against OBIA.  Some of OTBI reports can continue connecting to Fusion Apps directly.  They can coexist in a single BI server and a single BI answer client.

Both OTBI and OBIA are accessing Fusion Apps via the ADF.   This is a more advanced topic. 

 

 

 

Posted in BI, BI Application, BI Links, Business Intelligence, Data Warehouse, DBI, essbase, ETL, Infomatica, OBIA, OBIEE, Oracle, Oracle BI Suite EE, OTBI | 1 Comment »

Dimension Hierarchy Support in Data Warehouse (1)

Posted by Dylan Wan on June 7, 2011

This is my first post about dimension hierarchy support in a data warehouse.

I will first starting with the requirement assumptions in this post and later posts will talk about the implementations.

Dimension is mainly about “View By”,  “Group by”, and “Filter By”.  You say that you want to view your last year sales by regions.  Last year is a filter and “By region” is the “view by” or “group by”, so there are two dimensions involved here:  the Calendar dimension and the Region dimension.

Each year, such as year 2010, is a dimension member in the Calendar dimension.  Each region, such as the East Region, is a dimension member.

Sometime there are hierarchical relationship among the dimension members.  for example, year 2010 is a dimension member, and the month “January 2010” is also a member and we know that the member year 2010 can be related to another 12 dimension members.  the data for the dimension member year 2010 can actually further break down by those twelves members.  we call this relationship between the member year 2010 and the member month January 2010  is a hierarchical relationship.  The year 2010 is a parent member and the  month “January 2010” is a child member.

The relationship is useful in BI since you can see where the data come from.  Basically if you know that the year 2010 is consistent of 12 child members, January 2010, February 2010, …, to December 2010, it would be great if BI allows you to drill from the group by view with the year 2010 to the view by the child members of year 2010.

If there is hierarchical relationships among regions, it would be great that when you view any region in your report, you can further see what are the other regions that the region is consist of and see the details, especially see how the figure is made from.

For example, if you see the sales for 2010 for the East region is 21M and the east region is consist of three child regions, region A, region B, and region C.  You may want to see how this 21M come from.  Whether the figure is 7M for each region, or the figure is actually unbalanced among regions may mean different for you and different action plan may come up.

The requirement assumptions are

  • Dimension Hierarchy is for supporting drill down reporting.  You should be able to drill into a dimension member and see the further details about the member.
  • Dimension hierarchy let you see the break down.  Browsing the dimension members is not the main purpose.  The purpose of having the dimension support in BI is for viewing the metrics along with the dimension.
  • The number that is associated with the parent dimension members would typically be a number that can be added up from the child members.

Next post, I will talk about BI tool implementation.

Posted in BI, BI Work, Business Intelligence, Data Warehouse, OBIA, OBIEE, Oracle BI Suite EE | 1 Comment »

Why I do not use PowerConnect to access PeopleSoft Tree

Posted by Dylan Wan on March 16, 2011

1. It does not allow you to use parameters to the PeopleSoft connect. It may be changed later. However, it was a big issue when we try to address customer issues.

2. It requires EFFDT as an input.
It expects that people change the EFFDT using Mapping Editor. How can a business user does that every month?

3. It asks for a Tree Name. Many PeopleSoft tree structure supports multiple trees. Tree is just a header of the hierarchy. Whenever you add a new Tree, you need to create a new mapping!!

It does not make sense to use PowerConnect due to the customer demands. All above requirements are from customers.

We have no choice but stop using it.

It is a nice feature, but it was not designed for a prepackaged apps.

Posted in BI, BI Application, Business Intelligence, Data Warehouse, ETL, Infomatica, OBIEE, PeopleSoft | Leave a Comment »

EDW and BI Apps (Part 2)

Posted by Dylan Wan on October 11, 2010

Last week I talked about EDW as a data model offering. I also mentioned that it is possible to use the enterprise data warehouse as the source for BI Apps, but it requires a lot of manual work. The topic discussed is about evaluating if you need to have the EDW data model or BI Apps, or both.

There is another commonly seen scenario that you may already have an existing enterprise data warehouse. If you already have an enterprise data warehouse and already have the integration from various in-house systems, what do you do?

First of all, I think that it is not wrong to co-exist. You have existing investments in your EDW and you should consider keeping it for the value it already provides. However, considering the benefits and the cost and time saving you may get from the prepackaged BI apps, you may still want to deploy a prepackaged BI Apps. There is really no conflict.

The other questions come from those people who think of one plus one should not be two, but more. I think that it is possible to integrate the EDW and prepackaged BI apps in several ways to gain the additional values:

1. Dashboard and report level integration

BI tool, such as Oracle BI EE, allows you to have multiple data sources for your BI. You can put the reports or regions from different data warehouses into the same end user business flow. You can even put them into the same page if it makes sense.

If you include a cross reference table or cross reference from at least one side, you can actually drill into from one to the other.

The integration between the two will be similar from the integration between the BI apps and an OLTP system, such as E-Business Suite. For example, you can navigate to a EBS page as long as the page is callable. You can use URL rewrite to pass the context. The URL can encrypt the identifier so the data can still be secured. You can the URL as an presentation layer attribute that can be a derived attribute that include the object instance ID from the record.

You can also define the page navigation from one BI page to the other.

I will call this loosely-decoupled approach.

2. Logical Layer Integration via Data Federation

We can also use the data federation feature from a BI tool such as Oracle BI EE. The concept of the data federation is very simple. Basically, as an end user of BI, you should not need to know where your data is physically located. Your BI design architect can tell the BI system as part of the metedata repository about where the data is physically located and what the semantic layer of the data mode should be and how the data are related. During run time, the BI tool can get the data from the various physical database systems or even the text files and spreadsheet data. It will merge the results and show the data to the users.

3. Data Warehouse and Database level integration

The data federation is done via the BI tool. There is mot much impact to the ETL process as long as we can identify the share nature key.

The Data warehouse level integration means that you can not only make the BI Apps co-exist with your enterprise data warehouse, you may actually build some integration via the ETL process.

For example, you do not really need duplicate the Date dimension. The Calendar Date is an important dimension in the data warehouse. However, the definition of the Gregorian calendar is defined outside your organizations. Most of data warehouses have the similar design on the Date dimension. If the various facts can get the agreement on how to derive the Date dimension foreign key, you do not really need to have two Date dimension tables.

You need to be careful for going into this approach though. The benefit of using a prepackaged BI apps is not just cut your initial cost, but also reduce your lifetime maintenance. When you upgrade your OLTP apps, or when you want to implement additional modules, it may be the time for you to upgrade your BI Apps. The prepackaged BI Apps can provide the upgraded adapters to the latest version of the OLTP. If you change the out of the box date dimension from the BI Apps, you make need to find a strategy to keep the change isolated from the upgrade to make the change be easily redone or be protected from the upgrade.

(to be continued…)

Posted in BI, BI Application, BI Work, Business Intelligence, Data Warehouse, EBS, ETL, OBIA, OBIEE, Oracle BI Suite EE | Leave a Comment »

Data Security in Oracle BI Apps

Posted by Dylan Wan on July 15, 2008

I am describing how data security works in Oracle BI Apps.

Read the rest of this entry »

Posted in BI, BI Application, BI Work, Business Intelligence, OBIA, OBIEE, Oracle, Oracle BI Suite EE, Siebel Analytics | Tagged: | 1 Comment »

Oracle BI Applications 7.9.5 is out

Posted by Dylan Wan on May 30, 2008

Oracle BI Applications 7.9.5 is released early this month. Here is a quick summary of the features introduced in this release and where you can get more information about it.

Read the rest of this entry »

Posted in BI, BI Application, BI Work, Business Intelligence, Data Warehouse, ETL, Infomatica, OBIA, OBIEE, Oracle, Oracle BI Suite EE, PeopleSoft, Siebel Analytics | Tagged: | 3 Comments »

A good OBIEE web demo from New York City’s City Wide Performance Reporting

Posted by Dylan Wan on February 15, 2008

New York City now enables the public to monitor the city’s performance by making the OBIEE based dashboard and report online.

Here is the link to their CPR (City Wide Performance Reporting) site : http://www.nyc.gov/html/ops/cpr/html/home/home.shtml

Read the rest of this entry »

Posted in BI, BI Links, Business Intelligence, OBIEE, Oracle, Oracle BI Suite EE, Siebel Analytics | 2 Comments »

BI Applications and Embedded BI, Part 4

Posted by Dylan Wan on January 29, 2008

This is my 4th post about the embedded BI. The key is that a OBIEE and OBIA warehouse based solution is embeddable to the OLTP system as long as the OLTP system can provide the basic support.

In this article, I will discuss one of the key enabling technology – integrated authentication.

Read the rest of this entry »

Posted in BI, BI Application, BI Work, Business Intelligence, EBS, OBIA, OBIEE, Oracle, Oracle BI Suite EE, Siebel Analytics | 2 Comments »

BI Applications and Embedded BI, Part 3

Posted by Dylan Wan on January 28, 2008

I would like to go over some of the embedded BI related technologies you can use to make the data from BI Apps embedded in the OLTP systems.

In this post I will describe the Micro ETL feature from DAC.

Read the rest of this entry »

Posted in BI, BI Application, BI Work, DAC, Data Warehouse, OBIA, OBIEE, Oracle, Oracle BI Suite EE | 1 Comment »

Your questions about OBIEE or OBIA

Posted by Dylan Wan on January 24, 2008

Some people left me messages in my MeeboMe. If you have questions about Oracle BI Suite Enterprise Edition and Oracle BI Applictions. Here are the links to the public discussion forums:

Oracle BI Suite Enterprise Edition

Oracle BI Applications

Posted in BI, BI Application, BI Links, Business Intelligence, Data Warehouse, OBIEE, Oracle, Oracle BI Suite EE, Siebel Analytics | Leave a Comment »

Oracle BI Applications 7.9.4 is available on OTN

Posted by Dylan Wan on January 8, 2008

The latest version of Oracle Applications is now available in Oracle Technology Network.

http://www.oracle.com/technology/software/products/ias/htdocs/101320bi.html

Here is the direct link to software.

Here are some of the new features of this releases:

Read the rest of this entry »

Posted in BI, BI Application, BI Work, Business Intelligence, OBIEE, Oracle, Oracle BI Suite EE, PeopleSoft | Leave a Comment »

Oracle VPD and Oracle BI EE (Part 1)

Posted by Dylan Wan on January 4, 2008

Oracle database allows you secure the access to the table rows using the virtual private database feature.  It is one of the enabler for the EBS organization based security.

Oracle BI EE also provide the data security in the BI server based on repository setup.  However, can we also use the database feature together with our BI deployment?

Read the rest of this entry »

Posted in BI, BI Work, OBIEE, Oracle, Oracle BI Suite EE | Leave a Comment »

Key Roles involved in a BI Data Warehouse Project

Posted by Dylan Wan on November 30, 2007

To develop or deploy a BI solution for your organizations, you need to have the right people involved in the time time. Here are typical roles involved in a BI data warehouse project.

  • Project Sponsor
  • Project Manager
  • Functional Analyst
  • SME
  • BI Architect
  • ETL Developers
  • DBA

The job description and responsibilities are listed in this table: Read the rest of this entry »

Posted in BI, BI Application, BI Links, BI Work, Business Intelligence, Data Warehouse, ETL, Infomatica, OBIEE, Oracle Data Integrator, OWB | 5 Comments »

UDML in Oracle BI Server

Posted by Dylan Wan on October 22, 2007

I recently learned a way to retrieve and manipulate the Oracle BI repository file without using the Admin Tool. Although they are not officially documented and supported, they are extensively used by many savvy users. You can see the following screen shot to get the idea on how to retrieve the repository definition in a text format.

Read the rest of this entry »

Posted in BI Application, OBIEE, Oracle, Oracle BI Suite EE, Siebel Analytics | 5 Comments »

Oracle BI Applications version 7.9.3 is now available on OTN

Posted by Dylan Wan on August 20, 2007

It is great to hear that Oracle BI Applications version 7.9.3 is now available on Oracle Technology Network for download. – Oracle Business Intelligence (10.1.3.X) download web site.

The technical note 676: Summary of Enhancements and New Features in Oracle Business Intelligence Applications Version 7.9.3 is also available on supportweb.siebel.com. The highlights of the key features introduced in this release are as follows: Read the rest of this entry »

Posted in BI, BI Application, BI Work, Business Intelligence, OBIEE, Oracle, Oracle BI Suite EE, Siebel Analytics | Leave a Comment »

Use Oracle Instant Client for OBIEE

Posted by Dylan Wan on August 16, 2007

Oracle BI Server shipped the Oracle Merant ODBC Driver from DataDirect technology.  The ODBC driver files are stored under X:\OracleBI\DAC\oraclemerantodbc

It does a license check so you may receive a warning when you connect to a non-Siebel database.  I received a message when I tried to recalculate the row count in the Oracle BI Server Administration Tool for connecting to a non-Siebel database..

I just installed the Oracle ODBC Instant Client to solve this problem.  It does not require Oracle Home and the installation is very simple.  It took less then 10 minutes for downloading and installing the files.

I do not have a ORACLE_HOME in my machine since I am using the Oracle XE.  The only problem I have found for using XE is that BIEE and XE are running different OC4J servers so I have multiple web server instances on my machines.  Fortunately Oracle Express takes the port 8080 as the default and the OC4J for analytics is running under the port 9704, so they are not conflicting with each other.

To run the Oracle BI Application, you don’t need to install a separate application server.  The OC4J server is packaged with the Oracle BI Enterprise Edition platform and you can put your web page files under X:\OracleBI\oc4j_bi\j2ee\home\default-web-app.

Posted in BI Links, OBIEE, Oracle, Oracle BI Suite EE | 2 Comments »

BI EE Upgrade Survey in OracleBI Blog

Posted by Dylan Wan on August 16, 2007

If you are using Oracle BI EE/Apps, you have a chance to provide your feedback to Oracle about the upgrade to the next release.   You can fill the form – BI EE Upgrade survey available in OracleBI Blog.

Posted in BI, BI Application, BI Links, OBIEE, Oracle, Oracle BI Suite EE | 1 Comment »

BI Applications and Embedded BI, Part 1

Posted by Dylan Wan on July 7, 2007

I now also create my blog entries on blogs.oracle.com/dylanwan. This is actually the first entry in my blog in the blogs.oracle.com. I want to choose a topic related to what I learned lately.

Oracle BI Applications is build on top of the platform from the Oracle BI Enterprise Edition. The dashboard and report components can be seamlessly embedded into Oracle Applications, like how it is integrated with Siebel application. However, it is also a very typical data warehouse architecture.
Read the rest of this entry »

Posted in BI, BI Application, BI Work, Business Intelligence, Data Warehouse, OBIEE, Oracle, Oracle BI Suite EE, Siebel Analytics | Leave a Comment »