Dylan's BI Study Notes

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

Archive for the ‘BI’ Category

Business Intelligence

Is ETL still necessary?

Posted by Dylan Wan on January 22, 2019

ETL stands for Extract, Transform, and Load.

Extract and Load, their existence itself implies that the source data and target data are stored separately, so you need to extract from source and load the data into the target data store.

Extract and Load won’t go away if the data used for reporting is not stored in the same place as the data captured in the source system for transactional processing.

However, sometime, we use the term, Replicate, which refers to simply copying the data from the source to the target without any transformation.

In a way, the ETL was not necessary when we said that we can have a reporting solution directly from the transactional system.   In the old day, Oracle DBI running off Oracle E-Business Suite does not require ETL.  It does has batch programs for Summarization, but it does not extract and load data.

ETL went away at once when a replication or backup based approach is being used.  A solution such as Oracle Golden Gate, some how can eliminate the need of Extract and Load since it does Extract and Load in one step.  SOmetime people call this ELT, Extract and Load first, then Transform.

The real question is how easy these tranditional ETL programs can be developed and maintained.

The real question is whether an ETL tool and ETL programs running outside the BI tool is necessary.  The processes, at least the Transform process, won’t go away as long as the data need to prepared for reporting.

Are we able to get rid of the heavy ETL development time? Are we able to get rid of the separate ETL tool?  Are we able to stop maintaining those ETL programs? Definitely.



Posted in BI, Business Intelligence, ETL | Leave a Comment »

Use Surrogate Key in Data Warehouse

Posted by Dylan Wan on August 31, 2017

Using surrogate key is part of dimensional modeling technique for populating a data warehouse using a relational database.

The original idea was to generate the sequence generated IDs and use them in between the fact and dimension table, so we can avoid using the concatenated string or using composite key to join.  Also, due to the size of the column, it becomes a compression of the data and thus performs better.

However, maintaining the surrogate keys itself has actually a big portion of the ETL process:

Read the rest of this entry »

Posted in BI, Business Intelligence, Data Warehouse, ETL | Leave a Comment »

Prebuilt BI Contents should replace BI Tools

Posted by Dylan Wan on August 13, 2017

Most school districts need the same kind of reports and dashboard for measuring the performance of students, teachers, and schools.   They do not really need to have IT to build reports for them if the vendors can provide the reports OOTB.

There is really hardly a need to have a custom reporting tool for building or customizing the reports if the OOTB reports do the jobs. Read the rest of this entry »

Posted in BI, BI Application, Business Intelligence | Tagged: | Leave a Comment »

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 »

Data Lake and Data Warehouse

Posted by Dylan Wan on April 7, 2017

This is an old topic but I learned more and come up more perspectives over time.

  • Raw Data vs Clean Data
  • Metadata
  • What kind of services are required?
  • Data as a Service
  • Analytics as a Service

Read the rest of this entry »

Posted in BI, Big Data, Business Intelligence, Data Lake, Data Warehouse, EDW, Enrichment, Master Data Management | Tagged: , | Leave a Comment »

RTI and Actionable BI

Posted by Dylan Wan on April 1, 2017

RTI stands for Response to Intervention.  It is one the Data Driven Instruction methods. It is a way to monitor students’ progress and to determine what interventions students may need, assign the appropriate intervention to them, and monitor the progress closely.

This is a good demonstration of the need of actionable BI.  The system collects the information about students and identify the students that need the interventions.  The information may be learning related or behavior related problems.   Read the rest of this entry »

Posted in BI, Business Intelligence | Tagged: | Leave a Comment »

How to access Fusion Cloud for building custom BI?

Posted by Dylan Wan on December 20, 2016

It was a challenge when we tried to build a BI application for Fusion Cloud application as Fusion Cloud applications, unlike those acquired solutions, such as RightNow, Elouque, and Taleo, do not have web services at that time.

It was the reason why Oracle BI Application Configuration Manager was introduced.  It fills the gap by building an agent at the Fusion  cloud application side.  The agent talks to Fusion Apps like how OTBI talks with Fusion Apps.  It queries the physical layer of the objects from Fusion Apps and download the data into csv files.  It is not a web service based solution but for building a custom BI that requires bulk access any way, it is a goo choice.

Once the data is downloaded from Fusion Apps as CSV files, it can be accessible via FTP.  Here is the documentation about configuring Oracle BI Apps but I guess that it will work for other FTP client as well.

It seems that the situation improved and now multiple alternates are available.

Another possible way is to use BI Publisher.  If you have the BI Publisher Data Model Developer role, you will be able to “data model” in publisher.  Here is the documentation.

Posted in BI, BI Application, Business Intelligence, OBIA, Oracle, OTBI | Leave a Comment »

Cloud BI Features – Amazon QuickSight

Posted by Dylan Wan on October 17, 2015

Here is a list of features available from Amazon QuickSight:

Data Source Connect to supported AWS data sources
Data Source Upload flat files
Data Source Access third-party data sources
Data Preparation Data Preparation Tools
Visualization Build Visualizations
Visualization Access all chart types
Visualization Filter Data
Data Access Capture and Share, Collaborate
Data Access API/ODBC connection to SPICE
Security Encryption at Rest
Security Active Directory Integration
Security Fine-grained User Access Control
Security Enable Audit Logs with AWS CloudTrail
Performance In-memory calculation with SPICE
Performance Scale to thousands of users
Performance Support up to petabytes of data


I categorize the features into these groups:

  1. Data Source
  2. Data Preparation
  3. Visualization
  4. Data Access (or Alternate Access)
  5. Security
  6. Performance

They are almost same features available from other BI tools, such OBIEE, except the in-memory engine, and perhaps the scalability.  Here are some questions I have. Read the rest of this entry »

Posted in BI, Business Intelligence | Tagged: , , , , | Leave a Comment »

Data Mashup in OBIEE 12c

Posted by Dylan Wan on October 14, 2015

Data Mashup is a new feature from OBIEE 12c.

It is one of the two main features that OBIEE 12c.  The other one is the visual analyzer.

When I tested the data mashup features, it supports these two scenarios.  Read the rest of this entry »

Posted in BI, Business Intelligence | Tagged: , | Leave a Comment »

Incremental ETL in BI Apps (1)

Posted by Dylan Wan on May 10, 2013

The data warehouse needs to be refreshed periodically to make sure that the data warehouse have the up to date information.  The Incremental Load is the regular ETL process running everyday.  The frequency can vary, but it is commonly seen as nightly job.  To ensure that the incremental ETL job can be finished within a limited time window, incremental ETL load typically identifies the changes happened in the source system and only deal with the delta to avoiding doing a Full ETL Load.

In BI Apps, if you are not leveraging the Golden Gate and the SDS (Source Dependent Data Store), the incremental ETL is accomplished by directly comparing the last update date captured in the source table with the last refreshed date.

BI Apps ODI based ETL does not use separate ETL tasks for incremental load and full load.  The logic is determined by whether the data has been executed before.  In other word, it is based on whether the data has been refreshed.  This was a productivity gain for BI App developers and also make the support of incremental ETL becomes clear.  In the past, you may see the Incremental Load Informatica Workflow and Full Load.  Every time when there is a logic change, the workflows need to be modified.  It was a source of code bugs before.  By using a better ETL architecture, we eliminate the needs of maintaining two separate tasks.

Here is a high level summary of how the incremental ETL is accomplished in BI Apps ETL:

1. Use the W_ETL_LOAD_DATES table for tracking all the ETL execution

2. Use the IS_INCREMENTAL Variable for holding and passing the state

3. Refresh Variable in a Package Step.  If there is an entry in the W_ETL_LOAD_DATES, perform an incremental ETL.

4. Use the RUN_INCREMENTAL to turn on and off the incremental filter

5. Optionally, use the ODI Variable Evaluate Step to branch the Full and Incremental ETL logic within a package

6. Table Maintenance, take the ETL Load Date information to determine the operations, including Drop Index, Truncate Table before Full Load, Rebuild the index after Load, and Analyze Table after Load, tec.








Posted in BI, BI Application, ETL, ODI, Oracle, Oracle Data Integrator | Tagged: , , , | Leave a Comment »

ODI Topology

Posted by Dylan Wan on April 13, 2012

ODI has a very clever design to isolate the deployment objects from design objects.

The designer only interface to deployment information (topology) via logical schema.  The project objects only indirectly get the dependency via model.  The “context” can determine the physical resources and allow you switch among the development, testing and production env.

From prebuilt package apps perspective, the codes are shipped under a set of logical schema.    The logical schema represents the assumption about the various source and target systems.

The concept of “context” really enables the support of unlimited multiple source instances using a single set of codes.

Posted in BI, Business Intelligence, ETL, ODI, Oracle Data Integrator | Tagged: , | Leave a Comment »


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 »

User Defined Function (UDF) in ODI

Posted by Dylan Wan on March 21, 2011

User Defined Function (UDF) is a very powerful feature from ODI.

One of features that are absent from other ETL tool is to address the need to support different database platforms.  I won’t blame those ETL tools since they are not really designed for pre-package BI Apps.

Who will need to switch database platform like that?

If your data warehouse is deployed on Oracle, you can use Oracle SQL.  If you are using Teredata, you can use Teradata.  You know that your PeopleSoft is running on DB2, you can write the DB2 SQL.  In the custom data warehouse ETL environment, switching database platforms is uncommon, one time only task.  You do not need to switch among different database platforms within your code.

A prepackaged BI apps ETL developers, however, are facing different challenges.  You do not know if the source apps is running on which database platform.  Also, you want to give customers the choices on the database platforms to deploy the data warehouse.

ODI UDF comes very handy.  You can create a UDF to use in your SQL, you can have multiple implementation of the UDF for different database platform.  You can use GetDate() for MS SQL and use SYSDATE for Oracle database in the implementation, but you can create you own function such TODAY() and use in your SQL.

User Defined Function is not a new idea.  You may see something similar in other tools.  However, to be able to use UDF in SQL and to be able to use UDF with multiple implementations under different technologies, I only see the feature in ODI.

I won’t be surprised to see those “me too” products in the near feature.

More and more companies are moving to use prepackaged BI apps.

Posted in BI, BI Application, BI Work, Business Intelligence, Data Warehouse, ETL, Infomatica, OBIA, ODI, Oracle, Oracle Data Integrator | Tagged: , | 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 3)

Posted by Dylan Wan on November 12, 2010

EDW and BI Apps integration is a fun topic. I heard that more and more organizations are facing this situation. The reason is that many people buy the prepackage BI Application even though they already have an enterprise data warehouse.

I found that an interesting thing is that their existing enterprise data warehouse covers many more subject areas specific to their business, but when they touch the data from the ERP or CRM apps, they still would like to use the prepackaged BI Apps applications. The reason is that it saves a lot of their efforts.

Since BI Applications supports the ERP or CRM apps, their BI Application deployment typically supports the horizontal business functions. On the ERP side, it supports back office operations in financial, procurement, order management, and human resource. Their CRM apps supports marketing, sales, and services. However, the core business system may not be using the prepackaged enterprise apps. The data source for the enterprise data warehouse are industry specific or even in-house built systems.

This leads into the following scenario about integrating the EDW and BI Apps. The integration is really about integrating the Vertical data warehouse with the Horizontal data warehouse. Conformed dimension is a key successful factor for this integration.

There are multiple of technical approaches of doing the integration, such as building a cross reference table or directly sharing the logical or physical layers as I mentioned in the prior posts. No matter which technical approach are taken, I think that they should follow some data warehouse conformance process.

Some people just jump directly into the process of comparing the data warehouse schema. It seems both data warehouse has the party dimension. Let’s merge them. It seems both has the location dimension. Let’s create a cross reference.

I think that it is dangerous to look at the problem in this way. Just because that both data warehouse has something with the name of “Party” does not mean that they are the same thing.

It is important to go through the follow steps if you are involved in a such project:

1. What are the business questions you would like to answer via the conformance and integration?

2. What are the data available in each of your systems?

3. Where are the data required stored?

4. Determine the technical approach to integrate.

Many valuable information you can get from the prepackaged Horizontal BI apps that can be leveraged as part of conformance project:

1. BI Apps collects your people (employee/resource) information from your enterprise apps.
– It may also give your the headcount and reporting structure information.
– The people / resources may have various roles depending on the sets of enterprise apps are deployed.

2. It collects your customer information from your enterprise apps
– It may also provide you the revenue information by major customer related attributes such as geography and industry.
– If financial is being used, you can get the payment and credit information as well.

3. It may have your supplier information if you are using the procurement or Financial payables apps.

4. It has the GL account / Financial reporting structure information
– It already has the cost / expense information collected from various places for accounting

5. It has the internal organization structure information
The org structure defined for business processing as well the org structure defined for reporting / management reporting are there.

6. It has the calendar / fiscal year and quarter definition
If you have the accounting system, the fiscal calendar will be there.

7. It has the product / item information
– It could be the products the organization is selling.
– The items the deploying organization is building
– It can also include the product that the deploying organization is buying.

These of course depend on the nature of the business.

Posted in BI, BI Application, BI Work, Business Intelligence, Data Warehouse, EDW | 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 »

EDW and BI Apps (Part 1)

Posted by Dylan Wan on October 8, 2010

This may be an old topic, but people seem like to see and look for conflicts and arguments which do not really exist, and keep bringing this topic up.

Some vendors sell the data models, particularly, the enterprise data warehouse model, and tell you that it is their BI solution. For me, data model alone is NOT a BI solution. It is solving a small percentage of your problem if it solves problems at all.

What efforts and costs you really need to spend on is to

– Understanding your reporting requirement
– Determine how to use the analytics to get you the competitive advantage
– Determine how the analytics can be used as part of your decision making process which can be through out the organizations everywhere.
– Analyze where the data were originated and captured
– Analyze and determine how the information can be extracted and delivered
– Design how the data can be organized, consolidated, and transformed so the data can become closer to answer your business questions

What so called enterprise data warehouse model, does not answer most of the above questions.

Actually I cannot believe that people are comparing between a BI solution with the full layers of solutions with the enterprise data warehouse model. There is no comparison. The full layers of solutions are the data model design for answering your business questions, the analytics metadata describes how the data are organized and thus can be easily retrieved, the library of the metrics that you can select from, the prebuilt analytics flow with the guided navigation, the prebuilt adapter to the enterprise business applications. The last point includes the analysis about the source system model and features, as well as defining and comparing the semantic meanings from each system and defining the logical mapping. Also, the tuning of the various layers such as SQL tuning for the BI query and the performance tuning the batch job that extract and deliver the data. Even though you buy a EDW model, I guess that those above tasks still exist.

Please note that I also like the enterprise data warehouse concept, as a tool for consolidating data across multiple systems within an enterprise. I also like to read those books such as the Data Model Reference Guide. I like that they describe the business rule and assumptions using a model approach. However, you will need to see if your organization is really follow those same business assumption. Every organization has different business rules and process. Enterprise apps, such as ERP and CRM vendor, try to make their system adaptable to your process or try to sell their solution as the best practice. The truth is that not all business are the same. Same also hold true for the data warehouse model. A enterprise data warehouse model is a reference model, is a starting point, and is valuable, but please do not compare it with the prepackage business intelligence applications that have done a lot of the tasks I mentioned above.

There are questions about how to integrate between EDW and BI Apps. When the question is being asked, I think that one should prepare that there are manual work involved. First of all, as far as I know, many such enterprise data warehouse does not have prepackage ETL. If you have unique data consolidation requirements, you can go for that EDW model, but you will still need to analyze how to consolidate those data. One thing you can leverage from the pre-packaged BI Apps is that they already have the ETL and know how to extract the data, so you may be able to leverage your investment in that way. However, you will need to keep this in mind. The prepackage apps are defined with the reporting requirement in mind so it may not extract all the information. The EDW modeler, however, does not keep the reporting requirement as the model is designed. Many of them are designed for consolidating data, so you can get study the ETL to get the source data structure but you may not get all the attributes if you are thinking of populating the EDW from the extracted prepackaged BI apps data warehouse.

Also, possible solution is to use the consolidated enterprise data warehouse as the source for BI Apps. I can see the flow makes sense since EDW may have more granular data than the prepackage BI apps. However, it is really hard to have a standard assumption of what EDW should be, typically you won’t get the prebuilt adapter, which I feel is one of the major cost of your efforts.

(to be continued…)

Posted in BI, BI Application, Business Intelligence, Data Warehouse, OBIA | 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 »

Use ANSI SQL for Outer Join

Posted by Dylan Wan on July 4, 2008

The OLTP source applications like PeopleSoft and Siebel applications can run on many different databases including Oracle, MS SQL, or DB2. The target data warehouse can also run on different database platforms, incluidng the above databases, plus Teradata.

Various technologies can be used to enable the cross database platform support in the pre-packaged BI apps. Oracle BI Enterprise Edition allows you configure the database connections using the native drivers. ODBC can also be used to access different databases.

In order to deliver to deliver the pre-packaged ETL adapters, two technologies can be used : ANSI SQL and ETL tool specific SQL. It is preferable to avoid the dependency on the ETL plarform and use ANSI SQL92 syntax. When we move from ETL to ELT, Read the rest of this entry »

Posted in BI, BI Application, BI Work, ETL, Infomatica, Oracle, Oracle Data Integrator | Leave a Comment »