Display a list of dashboards based on permissions

A way to display a list of dashboards based on permissions :

Copy and paste the below script in the text box and mark  " Contains HTML" box.

<img id="loading" src="/analytics/res/sk_blafp/catalog/loading-indicator-white.gif" />
<div id="dash_list"></div>
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.10.1/jquery.min.js"></script>
<script type="text/javascript">
$.ajaxSetup({
    beforeSend:function(){
        $("#loading").show();
    },
    complete:function(){
        $("#loading").hide();
    }
});
$.ajax({
url: "saw.dll?getDashboardList"
}).done(function( data ) {
    var start = data.indexOf('[');
    var end = data.lastIndexOf(']');
    var len = end-start+1;
    var json_str = data.substr(start,len);
    var json_obj = jQuery.parseJSON(json_str);
    var str = 'You have access to the following dashboards:<br/> <table align="left" border=1>';
   var loopend=0;

str+='<tr>';
    $.each(json_obj, function() {

        if (loopend%5==0)
{
str+='</tr>';
str+='<tr>';
}
loopend++;
        if (this.folderName!=='Welcome'){
            str += '<td valign="top" width="15%" height="15%" ><div style="float:left;margin:5px 10px;";><b>' + this.folderName + '</b><br\>';
            $.each(this.portals, function() {
                    str += '<a href="saw.dll?Dashboard&PortalPath=' + this.portalPath + '">' + this.portalName + '</a><font color="red"> / </font>';
              });
            str += '</div></td>';
        }
    });
str+='</tr>';  
str+='</table>'
$('#dash_list').html(str);
});
</script>

OBIA – Customizations

Introduction to Customization

• By customization we mean changing the data warehouse structure, ETL logic and OBIEE repository and front-end to cover the customer specific needs that aren’t covered by the out-of-the-box solution
  • Each business has it’s own reporting requirements and they might be very special
  • Business environment changes and new requirements come up constantly
• The out-of-the-box BI Apps offers a good framework to build custom reporting
• Best practice customization is additive so we don’t lose the out-of-the-box objects when we customize BI Apps

Reasons for Customization

Transactional Applications Modifications 
The source system might have been customized for customer’s needs
• User Interface
• Data model extensions
• New entities (business components)
• Different relationships between components
• Modified visibility rules
• Different authorization/authentication
• These changes have to be addressed in BI Apps, too, with customization
BI Applications Extensions
When it comes to reporting, these are some of the typical requirements
• Company-specific dashboards, analysis, navigation
• New KPI’s or refining out-of-the-box ones
• Custom hierarchies
• Differences in change data capture logic
• New facts and/or dimensions for unsupported functionality
• Additional source systems or data warehouses which have to be integrated

Typical Effort & Customization Balance

80% of time is spent on dashboards en reports. 20% on more complex things.

OBIEE (Oracle Business Intelligence Enterprise Edition) Dashboard Customization 

Easiest and most frequently done customization, for example

• Changes of format, structure, navigation etc. in the existing reports, say
  • Data format such as date or currency format
  • Data presentation in pivot table, graph etc.
  • Grand totals, ordering, new filters, selection steps, dashboard prompts
  • Custom navigation from one report to another
• Adding new out-of-the-box or custom attributes or measures to the reports
• Building new dashboards and analyses using out-of-the-box or custom objects

OBIEE Metadata Customization

• Changes in OBIEE repository, the following are some typical changes
• Changing the structure of subject areas:
  • Drag and dropping more presentation columns
  • Deleting unnecessary columns from the presentation layer
  • Reordering and renaming objects
  • Changing the security (visibility) of subject areas
  • Creating new subject areas with different contents to different user groups
  • With OBIEE 11g combining several subject areas in the same report is so easy that they should be kept small
  • Implement repeating changes ones in the repository and not over and over again in every report.
• Creating new fact measures
  • OBIEE fact columns can be :
    • Base measures, which come from the data warehouse and do not involve any calculation in OBIEE
    • Derived measures, which are derived in OBIEE repository from base measures
  • New derived measures can easily be calculated from the base measures
    • Level-based measures to create totals on different levels of dimensions are another extremely powerful option in OBIEE
    • Case-statement and filters can often help you create custom measures based on the out-of-the-box measures
• Sometimes new attributes can also be built in OBIEE based on the existing attributes
• Changing logical dimensions
  • Adding new levels to the logical dimensions
  • Changing preferred drill-down path
  • Changing drill-down display columns
  • Creating new logical dimensions
• Adding new logical and physical data sources
  • Mapping new custom columns or custom tables
  • New aggregate tables
  • Aliasing out-of-the-box tables, for example to create new custom date dimensions
  • Adding additional data sources, for example relational databases or Essbase cubes

 ETL Customization

ETL customization involves changes in the data warehouse structure and Informatica mappings

• The main effort is done in the database and Informatica, but the changes have to be mapped also in DAC and OBIEE repository

Customization Scenarios

• Type 1 – New columns from source systems added to the data warehouse
• Type 2 – New fact or dimension tables added to the data warehouse
  • We have to build the corresponding SDE and SIL mappings
• Type 3 – New data from additional data sources extracted using the universal adapters

Type I Customization

Typical Steps to Extend Mappings

• Copy the appropriate workflows, sessions and mappings to a custom Informatica folder
  • Only modify objects in the custom folders
  • Update DAC task properties to point to the custom folders
• Add the new column(s) to staging and target tables
  • Create the column(s) in the database
  • Then import them to Informatica, DAC and OBIEE repository
  • Add new query indexes for the target table columns in DAC, if needed

Adding Columns to Existing Tables

•  Customize the out-of-the-box SDE and SIL mappings
  •  Modify the extract query to include the new column(s)
  •  If new tables are needed import them and be careful that no rows are lost or unwanted rows returned because of new joins
  •  Take the new column(s) through the two mappings following the path of the placeholder column X_CUSTOM
  •  After changes validate the corresponding Informatica sessions
• Modify DAC execution plan
  • Generate parameters and set values for the custom DAC folders
  • Re-build the execution plan
• Map columns in the logical and presentation layer in OBIEE

Safe Path 

• Most mappings have a single placeholder column, named X_CUSTOM, that marks a safe path through the mapping.
• All extension logic should follow the same route through the mapping as X_CUSTOM. You can add additional transformations to the mapping, but they should follow the same route through the mapping as X_CUSTOM. The graphic shows the pre-configured logic in grey. You should not modify anything contained within these objects. You should add customizations to the existing mapping, which allows them to run parallel to the existing logic.

Points to Remember

• Encapsulated objects must never be customized unless directed by Oracle
  • An exception are the extract mapplets in SDE mappings which can be modified
• Exposed objects can be extended but must never be otherwise modified
• Minimize the number of changes to exposed objects by using custom objects
  • Custom objects are never changed during an upgrade

Type 2 Customization

Adding Additional Tables

• Sometimes we have to create new dimension and fact tables
• Custom SDE and SIL mappings have to be created to populate each custom table

Table Definitions

• Create new table(s) in database
  • Remember to include the required system columns
  • Then import them to Informatica, DAC and OBIEE repository
  • Add ETL indexes and new query indexes in DAC
  • Define table type and foreign key columns in DAC – important for dependency detection

More on Indices

• Staging tables typically do not require indices
• Create indices on all columns that the ETL uses for dimensions and facts
• Define indices to improve query performance
• If you create indices in the database be sure to register them in DAC

Custom Mappings

• Create new mappings in Informatica to contain your ETL logic
  • Always create custom objects in your custom folders
  • There should be only one target table per mapping
  • Thanks to the change capture and update logic we can normally use the same mapping for a full load and incremental load of a table
  • However, if necessary, you can create a different mapping for full and incremental ETL
• Reuse existing source and target definitions, transformations, mapplets and mappings to save development time and to follow the best practices
  • Take advantage of the out-of-the-box encapsulated logic such as lookups and mapplets to retrieve ETL_PROC_WID, convert
    currencies etc.
  • Adapt the out-of-the-box logic to your custom mappings, for example:
    • Treatment of surrogate keys and system columns
    • Change capture and update strategy logic
    • Creation of unspecified records, slowly changing dimension, aggregates etc.

Custom Workflows

• Create a session for each mapping
  • Set the appropriate source and target connection values in Workflow Manager
• Create custom workflows
  • Each workflow should load only one table
  • The workflow name should match a session name that is used inside the workflow
• You have to create a workflow and session for both full load and incremental load
  • Usually the sessions and workflows can simply be duplicated with two different names

DAC Development 

• Create new tasks in DAC
  • In the properties of a DAC task you can define the command for incremental and full load – these are the names of workflows in Informatica
  • Other important settings are for example execution type, folder name, source and target and task phase
  • Synchronize the tasks to add the source and target tables
    • Set the properties for the tables, for example the target table truncate options 
• Create new subject area in DAC
  • If your customization is an enhancement of an existing star then assemble the corresponding DAC subject area again
  • If you’ve built a new star then create a custom subject area in DAC
• Create new execution plan
  • Reuse existing execution plan or create a new one
    • Add the new subject areas, if any
  • Generate and set the parameters
  • Build

More on Truncating Target Tables

• Truncating should be done through the DAC
• A single DAC task has one placeholder for a full load, and one for an incremental load

OBIEE Development

• New data warehouse tables need to be mapped in OBIEE
  • Tables imported, aliased and joined in the physical layer
  • New logical dimensions, dimension tables and facts defined in the logical layer
  • New subject areas created in the presentation layer

Naming Conventions

Data Warehouse Object	Naming Standard
Custom DWH tables	WC_<table_name>_<table_type>, for example WC_CUSTOMER_D
Custom column in OOTB table	X_<column_name>

• Separate your custom development as well as possible from out-of-the-box with naming convention in OBAW, Informatica, DAC and OBIEE
  • For example prefixes Custom_ and C_ can be used
  • In OBIEE dummy columns can be used to separate the out-of-the-box and custom columns. 
  
  
  Ref:  http://technology.amis.nl/2013/04/01/ayts-summary-of-oracle-business-intelligence-applications-customizations/

Introduction of OBIA

Oracle Business Intelligence Applications – Overview

• BI Challenges
• What are Oracle BI Applications?
• Oracle BI Applications Components & Architecture

BI Challenges

Today’s Typical BI Landscape

Hard to Maintain, Duplication and Inconsistency of Tooling

Business questions touch multiple processes.

Building BI Solutions is Challenging

Start from scratch?

Oracle BI Applications Provide a Single Integrated View of Enterprise Information

• Integrated enterprise-wide intelligence
• Summary level to lowest level of detail
• Data warehousing best practices – conformed dimensions, lowest level of granularity, full change histories for time comparisons, built for speed, extensible

On top we have the datamodel (warehouse) to answer business questions.
ETL: Extraction Transformation Loading

What are Oracle BI Applications?

Prebuilt BI Solutions for EBS, PeopleSoft, Siebel, JD Edwards, Fusion Applications & more.
Customers always have the same kind of questions.

• Benefits
  
  • Add insight to CRM and ERP applications
  • Easy to adapt and extend
• Unique features
  
  • Tight integration with OLTP systems (OnLine Transaction Processing)
  • Works with existing IT environment
  • Fast time to value; Low TCO
  • Over 4,000 customers

BI Applications – Rapid Performance Insight (samples)

• Employee Productivity
• Project Revenue
• Resolution Rates

Has over 6,500 pre-defined assets (dashboards, dashboard pages, reports and metrics).

Provide Rich Analysis based on best practices  

Analytic Workflows – e.g. Financial Analytics

Overview of what’s in Financial Analytics 7.9.6.3

Alignment Across the Enterprise. eg: Assess impact of product mix and discounts on revenue and margins

More than just dashboards and reports: Value of BI Applications lies under the surface

Conformed dimensions: eq same for sales and finance departments.

Robust Application Infrastructure

• Prebuilt Integrations: Fusion Applications, E- Business Suite, Peoplesoft, JD Edwards, Siebel, SAP. Action links to click through to a specific ‘order’ in the operational system.
• Rich Data Model: 10 years of best practices from BI modeling accommodating source system complexities
• Extensive BI related transformations: Slowly Changing Dimension support, Hierarchy flattening support, Currency Conversion, UOM conversion, Dynamic Data translations, Code standardization (Domains), Historical Snapshots, Cycle and process lifecycle computations, Balance Facts
• Flexible ETL Architecture: Multi Source, Multi Technology, Simplified but Optimized Orchestration Plan, High availability with ‘Follow the Sun’ support, Near Real Time Support with Micro-ETL
• Broad Deployment Choices: Heterogeneous Database & Operating System Neutral Deployment Support
• Support for Custom Configuration and Extensions: Support for Key Flex fields and Descriptive Flex fields, Extensible attribute s(JDE), and Conformed Domains
• Highly tuned Performance: Optimized for BI and analytic queries, Prebuilt aggregates for scalable end user performance, Incremental extracts and loads, Incremental Aggregate, Automatic table index and statistics management, Parallel ETL loads, Low latency Micro ETL support, Bitmap Indexes, Partitioning support

Speeds Time To Value and Lowers TCO 

Key Benefits

Improve visibility and  insight into  performance, processes, and  customers

•   Compare operational results to plans in real-time•   Quickly identify and respond to problems & opportunities•   Drive revenue and profit growth with better targeting•   Increase customer profitability and share of wallet

Align strategy and tactics across  functions

•   Manage and execute at all levels based on common view of information and common performance metrics•   Improve efficiency and reduce costs while maintaining good product quality and customer satisfaction•   Identify and replicate operational best practices

Leverage existing data,  applications, and IT  staff

•   Add value and insight to CRM and ERP applications•   Get faster time-to-value with lower cost and risk•   Lower the total cost of ownership compared to custom built solutions

Oracle BI Applications Components & Architecture

Oracle BI Applications Components

• Source system (eBS, Peoplesoft, JDE, Siebel, Fusion, SAP)
• ETL (Data Warehouse Administration Console (DAC) + metadata, Informatica + metadata) – Probably informatica will be replaced somewere in the future by ODI.
• Data Warehouse Database (Prebuilt schema)
• Oracle BI Enterprise Edition (Repository metadata, Web Catalog metadata) – repository contains the mapping of the physical db schema into the logical business attributes,  Web catalog contains reports and dashboards
• Client Tools to maintain all components

 Oracle BI Applications Architecture 

 ETL – DAC & Informatica

• Highly Parallel execution
• Multistage and Customizable
• Supports Deployment Modularity

What is the DAC?

Tool to manage the prebuilt OBIA data warehouse

• Create prebuilt data warehouse schema
• Contains high level semantic ETL metadata
• Run & Monitor ETL processes (Executes Informatica workflows) Knows the correct execution order.

About DAC Repository Objects

• Containers / Adaptors
  
  • Information on source system metadata (per version)
• Tables
  
  • Schema information
  • Table relationships
  • Indices (ETL / Query)
• Tasks
  
  • Source / Target tables
  • Full and incremental commands
  • Phases
•  Subject Areas
  
  • Defined by one or more star schemas
  • Assembling a Subject Area automatically collects the tasks that need to be run to populate the star schemas
• Execution Plans
  
  • Defined by one or more Subject Areas from one or more containers/adaptors
  • Building an Execution Plan results in ordered execution graph of the collection of tasks from the Subject Areas

What is Informatica?

Informatica is a Data Integration Platform

• A platform to define logic to Extract-Transform-Load data into data warehouse

ETL Objects

• Mappings – define transformation logic to load a certain warehouse table
• Sessions – Compiled versions of Mappings
• Workflows – A collection of Sessions

How does DAC interact with Informatica?

DAC Orchestrates ETL routines written in Informatica
Uses the command line interface tools of Informatica to run workflows

• PMCMD / PMREP

Consolidates runtime / database specific / SQL / application level parameters into a parameter file while invoking the workflows
Collect statistics

• Workflow status
• Number of rows processed
• Read/Write throughputs

Informatica Mappings – 2 Stage-Loading

Source Dependent Extract (SDE)

• Extracts (changed) data from source(s) to staging table

Source Independent Loads (SIL)

• Loads data from staging table to warehouse table

Informatica Mappings 

Data Warehouse 

• Abstracted Rich Data Model
• Conformed Dimensions
• Heterogeneous Database support
• Database specific indexing

Syntax

• W_     Warehouse
• _DS   Dimension staging
• _D     Dimension
• _FS    Fact staging
• _F      Fact

OBIEE – Repository

• Logical to Physical Abstraction Layer
• Calculations and Metrics Definition
• Visibility & Personalization
• Dynamic SQL Generation

Common Enterprise Information Model

Single Consistent View and User Self-Sufficiency

One more thing… Even more value under the surface

Metrics used in Reports & Dashboards - Not all measures in presentation layer used in reports & dashboards
Metrics in Subject Areas                       - Subset of logical measures are exposed in presentation layer
Metrics in Logical Layer                        - Aggregations, time series calculations and derived calculated measures extend physical measures
Metrics in Physical Warehouse              - Measures from physical columns in data warehouse

OBIEE – Reports & Dashboards 

• Role Based Dashboards
• Analytic Workflow
• Guided Navigation
• Alerts & Proactive Delivery
• Security / Visibility
• Integrated BI

All BI Applications are BI Mobile Certified

Typical Effort & Customization balance

Ref : http://technology.amis.nl/2013/04/01/ayts-summary-of-the-introduction-of-oracle-business-intelligence-applications/