Data mart and dwh

Datawarehouse and DataMart:Definition & Difference

Data warehousing and Data mart are tools used in data storage.With passage of time, small companies become big & this is when they realize that they have amassed huge amounts of data in various departments of the organization. Every department has its own database that works well for that department. But when organizations intend to sort data from various departments for sales, marketing or making plans for future, the process is referred to as Data Mining. Data Warehousing and Data Marts are two tools that help companies in this regard. Just what the difference between data warehousing and data marts is and how they compare with each other is what this article intends to explain.

Data Warehousing

A data warehouse is a collection of data marts representing historical data from different operations in the company. This data is stored in a structure optimized for querying and data analysis as a data warehouse. Table design, dimensions and organization should be consistent throughout a data warehouse so that reports or queries across the data warehouse are consistent. A data warehouse can also be viewed as a database for historical data from different functions within a company.This is the place where all the data of a company is stored. It is actually a very fast computer system having a large storage capacity. It contains data from all the departments of the company where it is constantly updated to delete redundant data. This tool can answer all complex queries pertaining data.

Data Mart

A data mart is a segment of a data warehouse that can provide data for reporting and analysis on a section, unit, department or operation in the company, e.g. sales, payroll, production. Data marts are sometimes complete individual data warehouses which are usually smaller than the corporate data warehouse.It is an indexing and extraction system. Instead of putting the data from all the departments of a company into a warehouse, data mart contains database of separate departments and can come up with information using multiple databases when asked.

IT managers of any growing company are always confused as to whether they should make use of data marts or instead switch over to the more complex and more expensive data warehousing. These tools are easily available in the market, but pose a dilemma to IT managers.

Difference between Data Warehousing and Data Mart

It is important to note that there are huge differences between these two tools though they may serve same purpose. Firstly, data mart contains programs, data, software and hardware of a specific department of a company. There can be separate data marts for finance, sales, production or marketing. All these data marts are different but they can be coordinated. Data mart of one department is different from data mart of another department, and though indexed, this system is not suitable for a huge data base as it is designed to meet the requirements of a particular department.

Data Warehousing is not limited to a particular department and it represents the database of a complete organization. The data stored in data warehouse is more detailed though indexing is light as it has to store huge amounts of information. It is also difficult to manage and takes a long time to process. It implies then that data marts are quick and easy to use, as they make use of small amounts of data. Data warehousing is also more expensive because of the same reason.

Summary • Data mart and data warehousing are tools to assist management to come up with relevant information about the organization at any point of time • While data marts are limited for use of a department only, data warehousing applies to an entire organization • Data marts are easy to design and use while data warehousing is complex and difficult to manage

DWH Questions

What is an ODS?
An Operational Data store is a construct, which has been designed to overcome the need for Information (EIS) in the Operational area. It is similar to a data warehouse but differs in many respects. Sitting in between the operational, transaction processing environment and the enterprise data warehouse is this structure called the ODS. The ODS provides foundation to achieve tangible integrated operational results in a short time frame. While the data warehouse offers no relief to the organization struggling with nonintegrated operational systems, the ODS offers immediate relief.

What is a Federated Data Warehouse?
Most techniques used by organizations to build a data warehousing system employ either a top-down or bottom-up development approach. In the top-down approach, an

Most techniques used by organizations to build a data warehousing system employ either a top-down or bottom-up development approach. In the top-down approach, an enterprise data warehouse (EDW) is built in an iterative manner and underlying dependent data marts are created as required. In the bottom-up approach,  independent data marts are created with the view to integrating them into an enterprise data warehouse at some time in the future. There are many pros and cons of the two approaches but there is a steady trend toward the use of independent data marts, especially with the move toward the use of turnkey analytic application packages.

A solution must offer low cost and rapid ROI advantages of the independent data mart approach without problems of data integration in the future. Such a solution is called the federated data warehouse. Two components of a federated data warehouse are the common business model and shared information staging areas. What is a Virtual Data Warehouse? When end-users access the “system of record” (the OLTP system) directly and generate “summarized data” reports and thereby given the feel of a “data warehouse”, such a data warehouse is known as a “virtual data warehouse”. What is Top-Down architecture of Warehouse development? The interaction associated with the architecture begins with an Extraction, Transformation, Migration, and Loading (ETML) process working from legacy and/or external data sources. Extraction transformation, and migration, process data from these sources and output it to a centralized Data Staging Area. Following this, data and metadata are loaded into the Enterprise Data Warehouse and the centralized metadata repository. Once these are constituted, Data Marts are created from summarized data warehouse data and metadata. What is Bottom-up architecture of Warehouse development? The second data warehousing systems architecture, the "Bottom-up" architecture became popular because  the Top-down architecture took too long to implement, was often politically unacceptable,  and was too expensive. What is Normalization, First Normal Form, Second Normal Form , Third Normal Form? 1.Normalization is process for assigning attributes to entities–Reduces data redundancies– Helps eliminate data anomalies–Produces controlled redundancies to link tables  2.Normalization is the analysis of functional dependency between attributes /  data items of user views,It reduces a complex user view to a set of small and stable subgroups of fields / relations 1NF: Repeating groups must be eliminated, Dependencies can be identified,  All key attributes defined,No repeating groups in table 2NF: The Table is already in1NF, includes no partial dependencies– No attribute dependent on a portion of primary key, Still possible to exhibit transitive dependency,  Attributes may be functionally dependent on non-key attributes. 3NF: The Table is already in 2NF, Contains no transitive dependencies Why do we need a different schema for Data warehousing/Business Intelligence?  A data warehouse stores historical data that is collected from various sources and the data is time variant.  The data warehouse is updated in a scheduled and controlled manner. An online system usually does not  store historical data, however if history data is stored it would pose severe performance issues,  further being an Online environment 2 users accessing the same set of information are  likely to derive different results. An online system would provide data only from a single source,  whereas a data warehouse usually has data being put in from various sources. What are the various features of Enterprise Data Warehouse?  The various features of an EDW are:  An EDW may or may not interact with Data Mart. The most important aspect of any  Data Warehouse is the consistency of information. An EDW may have same level of granularity as a Data Mart.  This means that the data from the Data Mart will be dumped into an EDW. An EDW may have more detailed level of data than a Data Mart.  This means that the data requirement  is at a more granular level that may not be catered by the Data Mart.

What are the primary differences between the OBIEE 10g and 11g security models

What are the primary differences between the OBIEE 10g and 11g security models and what happens during upgrade?

Security Task/Object	OBIEE 10g	OBIEE 11g	What happens during upgrade from 10g to 11g?
Define Users and Groups in RPD file using OBIEE Admin Tool	Default	N/A. By default, users are defined in embedded WLS LDAP via FMW EM Console, or alternatively, in external LDAP.	By default, existing users and groups migrated to embedded WLS LDAP. Existing groups are automatically mapped to an Application role.
Defining security policies	Policies in the catalog and repository can be defined to reference groups within a directory.	Policies are defined in terms of application roles, which map to users and groups in a directory.	10g catalog groups are automatically migrated in the upgraded catalog and assigned the same privileges, access and membership.
“Administrator” user	Unique user with full administrative privileges.	No single user named tor full administrative privileges. Administration can be performed by any user who is member of BIAdministrators group.	“Administrator” user automatically added as member of “BIAdministrators” group in embedded WLS LDAP and granted Administrator role. The user specified during OBIEE 11g installation (i.e. “weblogic”, “biadmin”) is also a member of the BIAdministrators group.
Repository Encryption	Available on sensitive elements only - i.e. user passwords, connection pool passwords, etc.	Entire RPD encrypted via a password.	Prompted to set a repository password while running the upgrade assistant. Do not lose this password as there is no feature to recover a lost password.
External Authentication and OBIEE Initialization (Init) Blocks	Init blocks are requiredfor external PDAP or external table authentication.	Init blocks not required for WLS embedded LDAP. Init blocks are required for external LDAP or external table authentication.	Upgraded RPD will continue to point to 10g LDAP or external tables. Initblocks may need to be modified to ensure that depreciated, or reserved word, variable names are renamed. NOTE: If you intend to use another LDAP server, such at Oracle Identity Management (OID), then you must upgrade to the embedded LDAP server. Please see Upgrade Guide for further details.
Catalog Groups	Defined in Presentation Server Administration link	Available for backward compatibility. Use of Application Roles in FMW EM Console recommended.	Existing groups will be migrated. Recommendation is to use application roles instead. Privileges on catalog objects may be granted to an application role via BI Presentation server Administration link.
SA System Subject Area	Optional	Available for backward compatibility and requires init blocks and external tables. Use of Embedded LDAP is recommended.	Upgraded 10g RPD will point to external tables, Initblocks may need to be modified to ensure that depreciated, or reserved word, variable names are renamed.
“Everyone” Presentation Server Group	Default	Replaced with AuthenticatedUser role.	“Everyone” group migrated to AuthenticatedUser role.

DAC FAQ...

Oracle DAC is an essential part of BI Apps, which is seldom being introduced in a systematic training course, although we use them all the time. There can be quite a lot of things to ask about when it comes to working with DAC, especially during interviews for BI Apps related projects. So I am going to gather some of the common interview questions with regard to DAC.

1. Name some of the DAC source system parameters:
TYPE2_FLAG, GLOBOL1_CURR_CODE, Initial_extract_date etc.. (The goal is just to name a few and of course, nobody remembers exactly the spelling)

2. To configure for initial full load, what are the things that needs to be done:
A, in DAC, set the value for initial_extract_date to avoid loading way too many data into target
B, to load base table W_DAY_D, nullify all of the refresh date to enable to full load. Do the same for all other aggregated time table like W_Week_D etc. At each task level where day dimension is being part of (SIL_daydimension), set the $$start date and $$end date parameter values at the task level to determine how long period your day dimension should store.
C. If your company does have multiple currency, then you need to configure currency in DAC by assigning currency code and exchange rate to DAC parameters like globol1 (2,3)_curr_code and globol1 (2,3)_curr_rate_type. BI Apps support up to 3 types of currency.
D. Configure GL Hierarchy so the info stores in W_Hierarchy_D. No DAC configuration needed
E. DATASOURCE_NUM_ID is a DAC parameters that determine which datasource system the extraction is taking place. In physical data source tab under 'setup' view, this field can be edited with integer number from 1 to 10 to represent different DB source.

3. Explain how to set up metadata in DAC to load data into the target

For basic intro on how DAC work in terms of executing the tasks, find out here

4. How to configure incremental loading in DAC
A. The refresh date under physical data source stores the last ETL run time, by nullifying this, the DAC will run full load or it will run incremental load based on the refresh date value.
B. Under task, there is 'incremental load' commend, by checking this, it will do either full load or incremental load regardless of refresh date.


-------------------------------------------------------------------------------

Below are the list of questions about DAC found through googling, since these questions have NOT been provided with answers, I have provided my answers, feel free to read it for your reference:

1. Over all architecture of DAC ?
DAC server and DAC Client. They must co-locate with Informatica Integration service, repository service and Informatica repository


2. Why we should use DAC and not control all execution through informatica ?
For better performance management, such as creating index, dropping index, truncating before load. Without DAC a custom ETL process will be needed, which has to survive the upgrate

3. Can we run multiple execution plan at the same time in DAC ?
Yes. only if the execution plan are not loading into the same table or using the same phyiscal table source

4. Explain DAC export/import
A way to import or export DAC repository metadata for upgrade or backup. Logica, System, runtime objects can be import/export

5. Have you change any of the DAC parameters ? If so which one and why ?
You have to understand what are the DAC parameters and the purpose of each. For example, Initial_extract_date can be modified when configure for initial full load, so the value for initial extract date will be used to filter out records from the source that are older than this date.

6. How do you Determine the Informatica Server Maximum Sessions Parameter Setting in DAC?
One you register informatica server in Dac client

7. Can dac send an email just in case of any failures ?
In DAC Client, toolbar, click email recipient, then in Tools--> DAC Server setup, Email configuration

8. Can you execute the sql scrip through DAC ? If yes how ?

Yes, at task level, in execution type, select SQL file. As a bonus to this answer, this article explains how to run store procedures in DAC.

9. in DAC How you can disable table indexes before loading and enable the index once load is complete ?
Just drop and recreate index

10.Let say you are running the normal incremental load. But just for today you want to extract data from AP_INVOCIES_ALL from 12/12/2011? How you can achieve this ?

Modify the refresh date to be 12/12/2011


11.How DAC Determines the Order of Task Execution within an Execution Plan ?
Based on tasks source/target table, Task phase (extract dim, load fact etc) and 'truncate always' properties, to run them in particular order, create task group


12.What are Micro ETL Execution Plans ? How can you Build and run them ?

According to Oracle document:
Micro ETL execution plans are ETL processes that you schedule at very frequent intervals, such as hourly or half-hourly. They usually handle small subject areas or subsets of larger subject areas. The DAC tracks refresh dates for tables in micro ETL execution plans separately from other execution plans and uses these refresh dates in the change capture process.

in design -- subject areas, create copy of subject area, inactive the unwanted tasks and create new execution plan for this subject area

13.From you past experience – explain scenario where Micro ETL Execution Plans produced wrong results on reports?

According to Oracle Document:
CAUTION:  Micro ETL processes can cause issues with data inconsistencies, data availability, and additional load on the transactional database. Therefore, you should consider the following factors before implementing a micro ETL process:

For related star schemas, if one schema is omitted from a micro ETL execution plan, the cross-star reports may be inaccurate. For example, if the Person fact table is refreshed more frequently than the Revenue fact table, a report that spans the Person and Revenue dimensional schemas may produce inconsistent results.
If you omit dimension tables from a micro ETL execution plan, the foreign keys for the fact tables will point to Unspecified rows for the new dimension records. The foreign key references will be resolved when the Complete ETL execution plan is run, but users of the reports should be aware of such inconsistencies.
If you do not include aggregate tables in micro ETL execution plans, the reports that use data from these tables will be inconsistent with the reports that use data from the detailed fact tables. However, if aggregate tables are included in the micro ETL execution plan, the aggregate calculations are performed for each ETL process, which will take a constant amount of time and may be inefficient to perform at such frequent intervals.
Hierarchy tables are rebuilt during every ETL execution plan by querying the base dimension tables. This operation takes a constant amount of time. If the base tables are big, this operation may take a long time and may be inefficient if the micro ETL execution plan runs several times a day. However, if you avoid populating the hierarchy tables during micro ETL processes, data inconsistencies will occur.
With micro ETL execution plans, caching will occur more frequently, which may have performance implications.
Micro ETL execution plans will put more load on the transactional database because of the frequent extracts.


14. Let say you can not use DAC scheduler to schedule you execution plan. What other options do you have ? How you can achieve this ?

Use Informatica scheduler.

15.Does DAC keeps track of refresh dates for all the source/target tables ?

According to Oracle Document:
Refresh dates are tracked only for tables that are either a primary source or a primary target on tasks in a completed run of an execution plan. The DAC runs the full load command for tasks on which a table is a primary source or target if the refresh date against the table is null. When there are multiple primary sources, the earliest of the refresh dates will trigger a full load or an incremental load. If any one of the primary source tables has no refresh date, then the DAC will run the full load command.


16.Consider the scenario as below for task T1
Primary Source has not null last refresh date
Primary Target has null last refresh date
Will task T1 executes in full or incremental ?

Based on answers provided from question 15, what do you think?


17.Explain the upgrade/merge options for DAC 7.8.4 & below and new versions ?

Use upgrade/merge wizzard.
1. Repository Upgrade (DAC 784) --- upgrade Dac
2. Refresh Base --- For upgrading BI Apps
3. Simplified Refresh From Base -- This option is similar to the Refresh Base option. It allows you to upgrade the DAC Repository from an older release of Oracle BI Applications to a new release without comparing repositories and creating a Difference Report.
4. Replace Base --- Upgrade when phasing out older transaction system to newer one
5. Peer to Peer Merge  --- Mergre different DAC instance of repository

18. Using DAC command line – write a script to check weather informatica services are up or not ?

use dacCmdLine InformaticaStatus. Below is the list of all commend lines according to Oracle:

19.Can we have two DAC server on the same machine ?
You can run two DAC servers on the same machine as long as they are listening on different ports and pointing to two different repositories

20.Explain briefly What kind of DAC Repository Objects Held in Source System Containers ?

Subject Areas -- A logical grouping of tables related to a particular subject or application context. It also includes the tasks that are associated with the tables, as well as the tasks required to load the tables. Subject areas are assigned to execution plans, which can be scheduled for full or incremental loads

tables -- Phsyical tables in DB

Indexes -- Just like your physical DB indexes

Tasks -- Unit of work for loading tables

Task groups  ---- Grouping of tasks that can be bundled to run as a group

Execution plans -- A data transformation plans defined on subject areas that needs to be transformed at certain frequencies of time

Schedules -- Determine how often execution plan runs.


21.What is Authentication file ? If you have dac client installed can you access DAC repository without Authentication file ?

According to Oracle Document:
When you configure a connection to the DAC Repository, the configuration process includes creating a new authentication file or selecting an existing authentication file. The authentication file authenticates the database in which the repository resides. If you create a new authentication file, you will specify the table owner and password for the database.

22.Explain Index, Table and Task Actions in DAC ?

According to Oracle Document:
Index action: Override the default behavior for dropping and creating indexes

Table action: Override the default behavior for truncating and analyzing tables

Task action: Can add new functionality of task behavior, such as precedinf action, success action, failure action, upon failure restart



23.How DAC Handles Parameters at Runtime ?

According to Oracle Document:
During an ETL execution, DAC reads and evaluates all parameters associated with that ETL run, including static and runtime parameters defined in DAC, parameters held in flat files, and parameters defined externally to DAC. DAC consolidates all the parameters for the ETL run, deduplicates any redundant parameters, and then creates an individual parameter file for each Informatica session. This file contains the evaluated name-value pairs for all parameters, both static and runtime, for each workflow that DAC executes. The parameter file contains a section for each session under a workflow. DAC determines the sessions under a workflow during runtime by using the Informatica pmrep function ListObjectDependencies.

The naming convention for the parameter file is

....txt

DAC writes this file to a location specified in the DAC system property InformaticaParameterFileLocation. The location specified by the property InformaticaParameterFileLocation must be the same as the location specified by the Informatica parameter property $PMSourcefileDir.

24. How DAC Determines Tasks Required for any given subject area ?

According to Oracle Document:
You define a subject area by specifying a fact table or set of fact tables to be the central table or tables in the subject area. When a subject area is defined, DAC performs the following process to determine the relevant tasks:

DAC identifies the dimension tables associated with the facts and adds these tables to the subject area.

DAC identifies the related tables, such as aggregates, associated with the fact or dimension tables and adds them to the subject area definition.

DAC identifies the tasks for which the dimension and fact tables listed in the two processes above are targets tables and adds these tasks into the subject area.

Tasks that DAC automatically assigns to a subject area are indicated with the Autogenerated flag (in the Tasks subtab of the Subject Areas tab).

You can inactivate a task from participating in a subject area by selecting the Inactive check box (in the Tasks subtab of the Subject Areas tab). When the Inactive check box is selected, the task remains inactive even if you reassemble the subject area.

You can also remove a task from a subject area using the Add/Remove command in the Tasks subtab of the subject Areas tab, but when you remove a task it is only removed from the subject area until you reassemble the subject area.

DAC identifies the source tables for the tasks identified in the previous process and adds these tables to the subject area.

DAC performs this process recursively until all necessary tasks have been added to the subject area. A task is added to the subject area only once, even if it is associated with several tables in the subject area. DAC then expands or trims the total number of tasks based on the configuration rules, which are defined as configuration tags. This process can be resource intensive because DAC loads all of the objects in the source system container into memory before parsing.


25.Difference between Homogeneous and Heterogeneous execution plans.

According to Oracle Document:

Homogeneous

This type of execution plan extracts data from multiple instances of the same source system. For example, a business might have an instance of Oracle EBS 11i in one location and time zone and another instance of Oracle EBS 11i in another location and time zone. In such cases, the timing of data extraction from the different instances can be staggered to meet your business requirements.

Heterogeneous

This type of execution plan extracts data from one or more instances of dissimilar source systems. For example, a business might have an instance of Siebel 7.8 in one location, an instance of Oracle EBS 11i in another location, and a second instance of Oracle EBS 11i in yet a third location. You can also stagger the timing of data extraction when you use this type of execution plan.

OBIEE How to get started

 In this “sticky” article I want to give you some basic starting points.
Get familiar with the basic terminology:

• http://en.wikipedia.org/wiki/Business_intelligence

Before you start with anything get up to speed with dimensional modelling. 

• http://www.kimballgroup.com/
  

(Get familiar with the works of Ralph Kimball)
Brush up your modelling capabilities, ADAPT is a good method to start with:

• http://www.symcorp.com/downloads/ADAPT_white_paper.pdf

Read up on your Data Warehouse /  BI basics:

• http://blogs.oracle.com/bimadeez/2010/06/data_warehousing_architect_-_a_beginners_guide.html

Get a clean (virtual) machine and first install an Oracle database. The 10g express version is OK for starters.

• http://www.oracle.com/technology/software/products/database/index.html

Maybe you want to brush up your DB skills:

• http://www.oracle.com/technology/obe/11gr1_db/index.htm

Next get the latest version of OBIEE

• http://www.oracle.com/technology/software/htdocs/devlic.html?url=/technology/software/products/ias/htdocs/101320bi.html

Download the documentation:

• http://www.oracle.com/technology/documentation/bi_ee.html

If you have everything installed work trough the OBE tutorials on OBIEE.

• http://www.oracle.com/technology/obe/obe_bi/bi_ee_1013/index.html

Don’t not only copy the click but be sure you understand what is happening!
Download and install the extended sales sample RPD and CAT. Dissect every report and dashboard to understand how the reports work.

• http://download.oracle.com/technology/products/bi/files/oracle_bi_sample_analysis_setup_files.zip

Have a good look around at the OTN forums. Most questions have been asked before.

• OBIEE forum: http://forums.oracle.com/forums/forum.jspa?forumID=378
• OBISE forum: http://forums.oracle.com/forums/forum.jspa?forumID=404

Read the works of Stephen Few on how to design a good dashboard from the visual standpoint:

• http://www.perceptualedge.com/articles/Whitepapers/Common_Pitfalls.pdf
• http://www.amazon.com/Information-Dashboard-Design-Effective-Communication/dp/0596100167

Learn to create reports and dashboard which people need, which is often completely different of the reports and dashboard they intentionally asked for…

• http://www.orm.net/

If your start an 11g project brush-up your weblogic skills:

• http://www.oracle.com/technetwork/middleware/fusion-middleware/documentation/index.html

Configure your OTN account so that you “watch” certain users. Looking around at the forum you soon learn which users give the most valuable answers.
Start following the OBIEE related blogs. At this moment there are about 10 your really need to watch. (Check my blog list).
Must read material:

• http://greatobi.wordpress.com/2010/01/07/can-you-just-turn-answers-on/  {Jeff McQuigg}
• http://greatobi.wordpress.com/2009/08/24/using-report-specs-for-requirements/  {Jeff McQuigg}
• http://exoticexpedition.wordpress.com/2010/01/15/6-guiding-principles-of-obiee-dashboard-design/ {Deepanshu Mehta}
• http://it.toolbox.com/blogs/eye-on-obi/understanding-the-oracle-bi-server-see-the-light-31891 {Gerard Braat}
• http://www.biconsultinggroup.com/obiee-tips-and-tricks/ {BICG}
• http://greatobi.wordpress.com/2011/07/06/prototyping-with-obi/ {Jeff McQuigg}

If you need more practices check out the Oracle Learning Library:

• http://apex.oracle.com/pls/apex/f?p=OTNCR:1:0 

Till Next Time

The Three Most Common Mistakes Made in Oracle BI Application projects

For a variety of reasons, completing a successful Oracle BI Applications project is not as straightforward as one might think considering that the BI Applications are touted as a pre-built, end-to-end solution out of the box.

Based on our experience with either implementing new Oracle BI Applications projects or following on to failed projects, there are three common mistakes made that can determine the success or failure of the project: 

1.   Failing to follow the installation and configuration guides

This may seem difficult to believe but there are many cases where projects have been implemented without following the specific instructions in the installation guide and/or the configuration guide.  

The installation guide is critical for setting up the infrastructure for Informatica, DAC, and the OBIEE platform.

Oracle Business Intelligence Applications Installation Guide for Informatica PowerCenter Users

Some of the more common steps missed include:

• Setting up the SSE_ROLE for the target data warehouse user
• Configuring the proper Code Page settings for data movement between source and target
• Failing to review and apply the supplied Oracle database parameter settings in the parameter template file (for example the one for Oracle 11g named init11g.ora)
• Not setting PowerCenter Integration Services custom properties - specifically the  overrideMpltVarWithMapVar parameter which enables Informatica to evaluate parameters within mapplets.

The configuration guide provides instructions on how to set up both common Oracle BI Application areas and dimensions as well as functional area configuration steps.

Oracle® Business Intelligence Applications Configuration Guide for Informatica PowerCenter Users

Under common dimensions, it is critical that the exchange rate and calendar configuration is followed correctly and relates to the specific source system environment that will be used.

For the functional areas, there are a series of configuration files that must be reviewed and edited to conform to the source system.   For example, the Human Resources functional area requires that the  
band dimension files for Person Age, Job Requisition Age,  Performance Ratings, and Period of Service are configured before the data is loaded.

Also domain CSV files for Ethnic Group, Sex, Employment, and other HR attributes should be reviewed prior to the data load,  For HR Analytics, the most critical domain file that must be configured is the one that populates the Workforce Event dimension.  This file (named domainValues_Wrkfc_EventType_psft.csv for Peoplesoft implementations) maps each employee assignment event from the PS_JOB table to a standard set of values for Hires, Terminations, and other job related activities.   This file should be reviewed with knowledgeable HR subject matter experts to properly categorize each Action/Reason Code combination into the standard event types.

2.   Developing dashboards without continuous user involvement

The four words that strike fear into the heart of any Oracle BI applications consultant are "I have a spreadsheet".    In many implementations, dashboard development requirements are taken directly from one or more existing spreadsheets that are passed among various business organizations.   This approach more often than not leads to a disappointed user base when a final dashboard is delivered because OBIEE, while quite powerful, cannot always replicate the form and function that is easily built into a spreadsheet.

A far better approach is to take the existing spreadsheets and work through a fit-gap analysis to understand the business requirements and metrics that drive the spreadsheet.  After that is completed, the OBIA data model should be modified to reflect those requirements before any actual dashboard and analysis configuration is started.    Once the data model is ready and available with either actual or test data, workshops should be scheduled with users to demonstrate the capabilities of OBIEE on top of that data model.   Rather than duplicating spreadsheets, focus on the data model and the flow of an analysis.  Many spreadsheets have thousands of rows that are filtered by the user and then pivoted to create other summary analyses.   

Start with a top down approach on the dashboards, focusing on:  

• dashboard prompts to filter reports automatically 
• drilling and navigation 
• conditional highlighting
• ranking reports to identify outliers and top performers
• charts that visually display trends
• multiple view types of the same data using view selectors
• column selectors
• filters and calculations driven by presentation variables 

The key is to get users to think about interactive analysis instead of data dumps and scrolling through long table format reports.   

It is important to push back on users when they ask for features that are not easily achieved in the OBIEE tool or require significant modification to the data model just to meet a very specific reporting requirement.  Balancing the development and maintenance of any OBIEE code with what can be occasionally excessively specific user report requirements should be considered before heading down a path that can lead to project delays. 

Involve users throughout the development process to get their input and feedback.  With the rapid development capabilities of Answers, it is very easy to modify the layout of dashboards and analyses on the fly to get buy-in from the users. 

3.   Implementing the RPD without modification

The delivered metadata repository (RPD) that comes with the Oracle Business Intelligence Applications should not be considered a final product.    On every OBIA project, one of the first tasks that should be performed is an RPD Review with the business users to develop a list of customizations that will make the Presentation Layer of the RPD a more effective representation of the business.    Performing this process early on will greatly reduce the development time later on when reports are developed.   It also is very helpful in improving the user adoption experience if they are new users to the OBIEE Answers tool.

The three R's of the RPD review process are:  Rename, Remove, and Reorder.

Rename any presentation column or table to reflect the business definition.  It is far easy to rename a column than to get user's to convert their known business vocabulary to match that of OBIA.  For example, rename the Out of the Box Employee Organization table and columns to be Department.

Remove any presentation columns and tables that are not required for analysis.  This includes any columns that may be exposed in the Presentation Layer but are not populated by the ETL for the particular source system for the implementation.   Work under the assumption that any column exposed in the Presentation Layer must be populated by ETL, unit tested for accuracy, and useful for creating analyses.    Simplicity yields project success. 

Reorder presentation tables and columns to be more effective for users.   Put most frequently used columns at the top of presentation tables.    Put dimension tables at the top and facts at the bottom of the subject area.    Group similar metrics together either by purpose or by time series.    Make good use of presentation table foldering to minimize the number of attributes and metrics displayed. 

Conclusion:

There are no guarantees of success when implement a BI application.   But there are certainly ways to increase the possibility of attaining the ultimate goal:  satisfied users with a useful business analysis tool delivered on time and on budget.    It can be done.