What is DAC?

Data Warehouse Administration Console (DAC)

The DAC is a metadata driven “ETL process management tool” and provides a framework for the entire life cycle of a data warehouse implementation. It enables ETL administrators to create, configure, execute, and monitor modular data warehouse applications in a parallel, highperforming environment. The DAC complements the ETL platform. It provides application specific capabilities that are not prebuilt into ETL platforms. For example, ETL platforms are not aware of the semantics of the subject areas being populated in the data warehouse nor the method in which they are populated. The DAC provides the following application capabilities at a layer of abstraction above the ETL execution platform:

• Dynamic generation of subject areas and execution plans

• Dynamic settings for parallelism and load balancing

• Intelligent task queue engine based on user-defined and computed scores

• Automatic full and incremental mode awareness

• Index management for ETL and query performance

• Embedded high performance Siebel OLTP change capture techniques

• Ability to restart at any point of failure

• Phase-based analysis tools for isolating ETL bottlenecks

The benefits of the DAC include

• Pin-point deployment of required Oracle BI Applications modules

• Load balancing / parallel loading

• Reduced load windows

• Fine-grained failure recovery

• Index management

• Database statistics collection

ETL Process Overview

In Oracle BI Apps architecture we have seen ETL is one of the key component. In this post let us see what exactly ETL does...

Following figure illustrates the ETL Overview

Here in the above figure we can see two stages SDE and SIL..

Let us see the each component of  Source Dependent Extract(SDE) in detail.

Business Component: 

The Business Component presents source data elements in commonly understood business terms. It translates operational data from your sources into meaningful business terms, hiding the complexity of the source systems. The Business Component performs joins in the data source to help extract data efficiently and to facilitate incremental extraction. The Business Component is part of Source Dependent Extract (extract mapping) that enables data extracts into universal staging tables in preparation for the source independent loads. Business components and SDE mappings provide significant out-of-the-box value as described below:

• There are thousands of tables in each of the operational systems. It takes time and effort to figure out which of those tables are required for integrating data from the operational source into the Business Analytics Data Warehouse. This provides significant time and effort savings.

• The mechanism to extract data from various operational systems is different. Some have a proprietary API (e.g. ABAP in SAP R/3); others need access to underlying database tables. Oracle BI Applications’ business component configuration has a uniform architecture that leverages different tools (e.g. Informatica Power Connect for SAP R/3, Informatica Power Connect for PeopleSoft etc) from the data integration platform to build the extraction layer, which hides these complexities.

• The components are architected to extract data from the operational sources in the least intrusive form – e.g, rather than join tables in the operational systems again and again for different data warehouse entities, the extract components have been architected to get the data once, which is then leveraged wherever required. This results in reducing the data warehouse load on the operational systems, freeing up the cycles for supporting operations, which is what they are meant to do.

• Wherever possible, SDE mappings are designed to extract data from the operational sources in an incremental fashion and refresh the enterprise data warehouse. This reduces the load on the operational systems considerably and significantly reduces the time required to refresh the data warehouse.

• The SDE mappings are designed to leverage indexes in the operational systems that can speed up the data extraction.

• SDE mappings have a configuration layer where business rules specific to the implementation can be configured. This avoids spaghetti code and results in a consistent, highly maintainable data integration environment.

• The data integration components are provided with a sample extension column (X_CUSTOM) that can be used as a reference for bringing additional custom fields into the Business Analytics Data Warehouse. Hence the task for extending the data model is reduced to identifying the right source columns, adding them to the data model and connecting it in the extraction layer using the sample column as a reference. This significantly reduces the time and effort required to extend the data model with required fields.

• Adherence to strict naming conventions for the data integration components helps increase understandability and maintainability. The data integration components are organized such that integration tasks can be run in parallel wherever possible, depending on the availability of resources. This can help reduce the time required for extracting data from the operational systems and refreshing the enterprise data warehouse.

Source Adapter:

The Source Adapter Mapplet which is part of SDE (Source Dependent Extract) mapping converts source-specific data elements from the source tables into the source independent data elements required for the Source Independent Load mappings for Data Warehouse code standardization.

Universal Staging Area:

Universal staging tables are used as temporary storage for extracted source data prior to transformation in the Source Independent Load process for loading into the Business Analytics Data Warehouse. The two main purposes of the staging tables are:

• Quick and efficient data extraction: Staging tables allow for faster data extraction, leaving complex transformations for later and allowing for a shorter operational window.

• Flexibility with data load: Staging tables integrate data from different source systems.

For Source Independent Load(SIL) refer to the previous post... 

Source Independent Load(SIL) in ETL

The Source Independent Load Mapplet is the key component of the Source Independent layer of the Oracle BI Applications. It represents fundamental business concepts such as sales orders, purchase orders, products, customers, suppliers etc. from various operational systems like Oracle EBS, PeopleSoft, Oracle’s Siebel CRM , and SAP R/3, which often vary widely in the ways each represent or define these concepts - creating challenges for an analytic solution that must integrate data from many operational sources. The Source Independent Mapplet provides a core consistency through a consolidation layer that “normalizes” these differences into a single common definition of each fundamental business concept across the entire value chain. The transformations packaged inside the Source Independent Mapplet consolidate the data from multiple source systems by enforcing commonalities and by preparing the data for source independent loading into the enterprise data warehouse.

The transformations performed by the SIL (Source Independent Load) Mapplet include:

• Lookup descriptions for data elements which are normally present as codes in the operational sources. The SIL Mapplet provides the flexibility to bypass the lookup for descriptions when they are provided in the input. To truly integrate data from multiple sources, the domain values for data elements should be standardized, and source supplied values need to be transformed to the corresponding values from the standardized set. The SIL Mapplet supports this functionality.

• The Business Analytics Data Warehouse maintains relationships between fact tables and dimension tables using surrogate keys for better query performance. The SIL Mapplet generates the surrogate keys used for maintaining dimension tables.

• The SIL Mapplet supports Type I and Type II slowly changing dimensions. The SIL Mapplet of a dimension provides a Type II flag, which can be configured to indicate a INTERNAL USE ONLY Oracle Confidential 36 Type II change depending on the data  elements for which history tracking is required. The SIL Mapplet handles the creation of a new  record in the dimension table for each Type II change and all the records are effectively dated to facilitate dimension key lookups.

• The SIL Mapplet has a dimension key lookup transformation for resolving the relationships between fact tables and dimension tables. Since the Business Analytics Data Warehouse has Type II support for slowly changing dimensions, the SIL Mapplet performs the lookup using the transaction date to preserve history.

• Currency conversions are performed by exchange rate lookups depending on the transaction date. SIL Mapplets provide complete flexibility and bypass the currency conversion logic when the amounts in all three global currency denominations are provided as input.

• The SIL Mapplet has transformations to figure out the update strategy for an input record, whether it needs to be inserted in the data warehouse, or whether it needs to be updated or handled as a Type II slowly changing dimension. For the latter case it is inserted and a corresponding record in the data warehouse is updated.

The SIL Mappings provide several key benefits:

• Data integration from every operational source uses the SIL Mapplet to load data into the Business Analytics Data Warehouse, enforcing consistency.

• Most of the transformations required to load the Business Analytics Data Warehouse are encapsulated in the SIL Mapplet, which is reused for building data integration components from every source. This results in significant savings of time and effort required to build the data integration components, and reduces overall risk.

• The SIL Mapplet provides a lot of flexibility in the transformations that it encapsulates. The behavior can be controlled by configuring the inputs according to need. The SIL’s ability to resolve integration issues and move data across different platforms makes it an invaluable resource for any data warehouse administrator.

Oracle Business Intelligence Applications Architecture

Here i am providing brief information about OBI Apps Architecture.. 

The architecture contains four logical layers as shown below in Figure:

BI Presentation Services (Web Catalog)

BI Presentation Services include pre-built metrics, analytic workflows, reports, dashboards and alerts used to deliver information, stored in the Business Analytics Data Warehouse, to information consumers. These services are built on the Oracle Business Intelligence Enterprise Edition delivery platform, which is designed to meet the requirements of an enterprise business intelligence solution. Pre-built reports and dashboards are built on industry best practices, and provide rich content covering functional areas including sales, service, marketing, finance, supply chain, order management, procurement and human resources.

Pre-built Metadata (BI Server Repository)

The metadata layer includes the definition of role based views, dimensions, metrics, hierarchies, calculations, aggregation rules, canonical time dimensions, time series metrics, roles and security. The metadata layer is built on the Oracle Business Intelligence Enterprise Edition platform using the Admin Tool, a core component of the BI server, which is designed to meet the requirements of the Common Information Model to deliver consistent and integrated information to business users, by capturing the data from multiple source systems.

Business Analytics Data Warehouse

The Business Analytics Data Warehouse is the persistent layer where information is stored for retrieval when required. The data warehouse resides in a relational database platform like  oracle, IBM DB2, Teradata or Microsoft SQL Server. It consists of transaction grain tables used to capture facts from the key business processes across the entire value chain of an organization. Current snapshots and periodic snapshots are provided for supporting complex analysis needs and aggregate tables are built for faster retrieval. Hundreds of dimension tables conformed across all the fact tables contribute to enrich the analyses.

Data Integration Components (ETL/E-LT)

Data Integration components are used to integrate data from various operational systems such as E-Business Suite, PeopleSoft Enterprise, Oracle's Siebel CRM Applications and third party systems like SAP R/3 Applications, and load the information in the Business Analytics Data Warehouse. The data integration components are built using either the Informatica Power Center data integration platform or Oracle Data Integrator. Oracle Data Integrator delivers unique next-generation Extract Load and Transform (E-LT) technology that improves performance and reduces data integration costs, even across heterogeneous  systems.

Installation of Informatica

Pre-Installation Tasks

1.  Create the databases. Create the databases for the repository, domain configuration, and Data warehouse databases
2. Create database user accounts. Create user accounts for the repository, domain configuration, and Data warehouse databases.

          Repository user

          Informatica

          Domain User

          Infadom

Installation Process

Stop informatica service before starting installation

Copy Informatica into any location on your hard disk

1) To start the installer from a hard disk, run install.exe from the below directory.

   

Server installation

2)On the main PowerCenter installation window, select Server. The Welcome window introduces the Power Center Standard Edition installation.

3)Click Next.

4)Click Next.

5)The PowerCenter License Key window appears enter the location and file name of the PowerCenter license key or click Browse to locate the license key file.

6)Click Next.

7)The Installation Prerequisites window displays the supported platforms and databases and the disk space requirements. Make sure that all PowerCenter installation requirements are met before you continue the installation.

8)Click Next.

9)The Installation Set window displays the PowerCenter installation options Choose Install PowerCenter 8.1.1. 

10)Click Next.

11)The Installation Directory window appears. Use the default directory or click Browse to select a directory.

The default directory on Windows is D:\Informatica\PowerCenter8.1.1

12)Click Next.

13)The Pre-Installation Summary page appears. Review the pre-installation summary to verify that the installer will install PowerCenter components in the correct location and with the options you select.

14)Click Install to continue After you click Install, the installer copies the files for the selected components. When the file copy process completes, the Create or Domain window appears. 

 

Property	Description
Database type	Type of database to store the domain configuration. Database type may be Oracle,
Database URL	Host name and port number for the database instance in the format <host name>:<port>.
Database user ID	Domain configuration database account.
Database password	Password for the domain configuration database user account.
Database service name	Service name for Oracle

15)Click Next.

16)The Configure Domain window appears. Uncheck the Create Repository Service .

Property	Description
Domain name	Name of the PowerCenter domain to create. The domain name must be in 7-bit ASCII format and must be less than 79 characters. The domain name cannot contain spaces or the following characters: \ / : * ? > < " |
Domain host name	Host name of the machine on which to create the PowerCenter domain. If you create a domain on a machine with a single network name, do not change the default host name. If you create a domain on a machine with multiple network names, you can modify the default host name to use an alternate network name. Optionally, you can use the IP address of the machine on which to create the domain.
Node name	Node name for the current machine. This node name is the gateway node for the domain. This is not the host name for the machine.
Domain port no.	Port number for the current machine. The installer displays a default port number of 6001. If the port number is not available on the machine, the installer displays the next available port number.
Domain user name	User name of the domain administrator. You use this user name to log in to the PowerCenter Administration Console. The user name must be less than 79 characters and can only contain alphanumeric characters.
Domain password	Password for the domain administrator. The password must be between 3 and 16 characters.
Confirm password	Reenter the password.
Create Repository Service	Allows you to create a Repository Service for the PowerCenter repository..

17)Click Next.

18)On Windows, the Configure Informatica Services window appears.Uncheck the Install Informatica with differentnt user account.

19)Click Next.

12)When the installation completes, the PowerCenter Installation Summary window indicates whether the installation completed successfully.

Client Installation

1)On the PowerCenter main installation window, select Client. The Welcome window introduces the PowerCenter Client installation.

Click Next.

The Installation Set window displays the PowerCenter Client installation options.

2)Click Next.

3)The Installation Set window displays the PowerCenter Client installation options.

 4)To select the PowerCenter products to install, select Custom and click Next.

5)The PowerCenter Client Components window appears. To install only the PowerCenter Client, clear the Metadata Manager option.

6)Click Next.

7)The Installation Directory window appears.

8)Click Next.

9)The installer displays a message about whether the installation completed successfully. It also shows the location of the PowerCenter Client.

Where can we use repository variables in obiee

We can use repository variables in three places :

• in BI Server
• in BI Presentation Service
• in Delivers

Syntax

VALUEOF("NQ_SESSION.Variable Name")

1)In BI Server:

Connection Pool

The picture below show a repository variable call but you can use in the same way the session variable syntax.

Opaque View / Select Statement

In the bookshel Version 10.1.3.2, it's written that Only repository variables can be used in the definition. An error will generate if a session variable is used in the view definition.

  

           With the version 10.1.3.4, a warning is generated:

WARNINGS: GLOBAL: The repository variable, 
NQ_SESSION.MYYEAR, has no value definition.

But BI Server process it without problem.

-------------------- SQL Request:
SET VARIABLE QUERY_SRC_CD='Report',SAW_DASHBOARD='/users/administrator/_portal',SAW_DASHBOARD_PG='MyYear',
SAW_SRC_PATH='/users/administrator/MyYear/Answers',MYYEAR='2001';SELECT TIMES_VIEW.calendar_year saw_0 FROM Test 
ORDER BY saw_0


+++Administrator:2e0000:2e0008:----17/12/2009 06:19:16

-------------------- General Query Info:
Repository: Star, Subject Area: Test, Presentation: Test


+++Administrator:2e0000:2e0008:----17/12/2009 06:19:16

-------------------- Logical Request (before navigation):

RqList  distinct 
    TIMES_VIEW.calendar_year as c1 GB
OrderBy: c1 asc


+++Administrator:2e0000:2e0008:----17/12/2009 06:19:16

-------------------- Execution plan:

RqList <<150282>> [for database 3023:156:orcl SH,31] distinct 
    TIMES_VIEW.calendar_year as c1 GB [for database 3023:156,31]
Child Nodes (RqJoinSpec): <<150293>> [for database 3023:156:orcl SH,31]
    (select time_id, calendar_year 
from times
where
calendar_year > VALUEOF(NQ_SESSION.MYYEAR)) as T6563
OrderBy: c1 asc [for database 3023:156,31]


+++Administrator:2e0000:2e0008:----17/12/2009 06:19:16

-------------------- Sending query to database named orcl SH (id: <<150282>>):

select distinct T6563.calendar_year as c1
from 
     (select time_id, calendar_year 
from times
where
calendar_year > 2001) T6563
order by c1

2) Presentation Services

Logical SQL in Answers

OBIEE - Logical Sql:

VALUEOF(NQ_SESSION."Variable Name")

Other places

You can call a server variable by using this syntax :

biServer.variables['VariableName']

Example in a dashboard text object:

3)In Delivers

@{NQ_SESSION.Variable Name}

Presentation Variables in OBIEE and where can we use them

The Presentation Variable is the only variable offered by the Presentation service. It will take the datatype of the presentation column on which the prompt is created.

Syntax: @{variables.<variableName>}{<default>}[format]

variables: Optional

variableName: It refers to the variable which should be different from the existed variable names.

Default: The default value of the variable.

Format: It represents the format in which the variable values need to be displayed.

You can reference presentation variables in the following areas 

• Answers :
  • Title Views
  • Narrative Views
  • Column Filters
  • Column Formulas
  • Conditional Formatting conditions
  • Chart scale markers.
  • Gauge range settings.
  • Static text.
• Direct Database Requests
• Dashboard prompts
• iBot Headlines and text

Some examples are given below with a presentation variable “Year” and as default value the max of the year :

@{Year}{max(Calendar."Calendar Year" by) }

Column Formula:

You can also set it in a formula. Example :

CASE 
   WHEN @{Year}{max(Calendar."Calendar Year" BY) } = '2001' THEN 'It''s the year 2001' 
   ELSE 'You choose an other year' end

or

CASE  
WHEN  @{Year}{max(Calendar."Calendar Year" BY) } <  Calendar."Calendar Year" 
   THEN 'The Year is greater than the presentation variable ' || 
       cast(@{Year}{max(Calendar."Calendar Year" BY) }  AS char)   
WHEN @{Year}{max(Calendar."Calendar Year" BY) } =  Calendar."Calendar Year" 
   THEN 'The Year is equal to the presentation variable ' || 
       cast(@{Year}{max(Calendar."Calendar Year" BY) }  AS char)  
ELSE 'The Year is smaller than the presentation variable ' || 
      cast(@{Year}{max(Calendar."Calendar Year" BY) }  AS char) end

Filter:

Title View: