Oracle Bi Solutions

  • Subscribe to our RSS feed.
  • Twitter
  • StumbleUpon
  • Reddit
  • Facebook
  • Digg

Tuesday, 15 January 2013

[OBIEE11g] Time Series functions Ago And ToDate

Posted on 11:37 by Unknown

In Sales business, The sales manager want to see the sales data for one month ago, one year ago or from some amount of time to current date or starting from a period and ending in another period they want to calculate the sales amount. How will you do that?
Yes, Absolutely Time series functions providing the way to make the comparison between the time to calculate a measure because SQL is not providing any direct way to make time comparisons. So by using Time series function we can calculate a measure for the above explained requirements. 
Note: Before going to use the time series functions we have to create Time Dimension hierarchy first.
We are creating a measure using time series functions in BMM Layer of Administration tool.
In OBIEE 10g we are having two time series functions
1. Ago( )
2. ToDate( )



1. Ago( )
    This function calculates aggregated value for a measure as of some time period ( a month 
    ago, or a year ago) from the current time. This function is passing three parameters.
        Syntax: Ago(<<Measure>>, <<Level>>, <<Number of Periods>>) 
     i. Measure: A measure column which you to make use in this function 
    ii. Level : On which level you want to calculate this measure. This level is selected from 
        the Time Dimension hierarchy
   iii. Number of Period: A numerical value which will go for how many level 
        you want to go before from current time.
        E.g.: Ago("SH"."SALES"."AMOUNT_SOLD" ,  "SH"."Time Dimension"."Month" , 1) 
        In the above example I have created a Column called 'Sales Month Ago' using Ago() 
        function.


2. ToDate(  )
     This function aggregates a measure from a beginning of a specified time  period to the 
     currently displayed time. We can create a calculated column using this function by 
     following the same procedure how we have created a column using 'Ago' function.
     Syntax: ToDate(<<Measure>>, <<Level>>) 
     For e.g: ToDate("SH"."SALES"."AMOUNT_SOLD" ,  "SH"."Time Dimension"."Year" ) 
     With the above example 'Amount Sold' Column is the measure and 'Year' is the level 
     Which will calculated the measure  from the beginning of the year to a specified time.


In OBIEE11g with the above function there is one more function has been added


3. PeriodRolling( )
    This function allow us to create a aggregated measure across a specified set of query   
    grain period, rather than within a fixed time series grain. The common use of this 
    function is to create a Rolling Average such '10-Week Rolling Average'
              


Syntax: PeriodRolling(<<Measure>>, <<Starting Period Offset>>, <<Ending Period Offset>>)
     Measure: represents the logical measure column from which you want to derive.
     Starting Period Offset: identify the first period used in the rolling aggregation.
     Ending Period Offset: Identify the last period used in the rolling aggregation.
     For e.g: PeriodRolling( "SH"."SALES"."AMOUNT_SOLD", -2, 0)
    With the above example I'm creating three months rolling sum of  column 'Amount Sold'. It 
    includes past two months with the current month.
    The numerical value '-2' in the offset indicates the month Jan-12, Feb-12 if our current   
    month is 'Mar-12' The numerical value '0' in the offset indicates the Current Month
Note:
PeriodRolling( ) function which will not the calculate the Average sum of three months for the measure. Average (AVG( ) function in OBIEE) computes the average of the database rows accessed at the storage grain. So If you want to calculate the 3-Month sales average for the above explained Period Rolling example we have to write the syntax like below:
3-Month Sales Average = 
(PeriodRolling( "SH"."SALES"."AMOUNT_SOLD", -2, 0) )/3

Email ThisBlogThis!Share to XShare to FacebookShare to Pinterest
Posted in OBIEE | No comments
Newer Post Older Post Home

0 comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Popular Posts

  • Upper Function
    In Oracle/PLSQL, the  upper function  converts all letters in the specified string to uppercase. If there are characters in the string that ...
  • OBIEE 11g Hide/Show Sections based on Dashboard Prompt
    allow a user’s interaction to hide/show certain sections of a dashboard. In this particular case the user wanted to choose either ‘Quarterly...
  • [OBIEE11g] - OBIEE Dashboard for Informatica Metadata Reporting
    The metadata that Informatica Power Center 8 retains in its repository can be exposed via OBIEE reports and dashboards. This metadata includ...
  • [ODI] - Frequently Asked Questions (FAQ)
    Here is a list of FAQs about Oracle Data Integrator 1) What is Oracle Data Integrator (ODI)? 2) What is E-LT? 3) What components make up Ora...
  • OBIEE 11g not showing new dashboard in the drop down menu
    When creating New dashboard in  OBIEE 11g, I have faced with issue that dashboard name did not show up in drop down dashboard menu. 1. When ...
  • Data Modeling: Schema Generation Issue with ERwin Data Modeler 7.3
    We are using Computer Associate’s ERwin Data Modeler 7.3 for data modeling. In one of our engagements, we are pushing data model changes to ...
  • Installation Informatica Powercenter 9.1 on Oracle Enterprise Linux 5.6
    Ingredients: Program Version Filename Oracle Express 11G 11.2.0 oracle-xe-11.2.0-0.5.x86_64.rpm SQL Developer 3.0 sqldeveloper-3.0.04.34-1.n...
  • OBIEE 11g - Query Limit
    Query limit and number of minutes a query can run per physical layer database connection, follow the below steps. > Login to Repository u...
  • [OBIEE11g] - Creating Dashboard Traversing Through Graph
    The general requirement asked for by customers is that they want to Click on the Main Dashboard Page’s Graph and be transferred to the other...
  • Informatica Powercenter Workflow Manager Repository Navigator docking float issue
    In case you’re also experiencing where your Repository Navigator is not dock or attached and it seems just floating within Workflow Manager ...

Categories

  • BI Publisher
  • DAC
  • DataWarehouse
  • Hyperion
  • Informatica
  • OBIEE
  • ODI
  • Oracle Applications EBS 12.1.3
  • Oracle Database
  • PL/SQL
  • SQL
  • Unix/Linux

Blog Archive

  • ▼  2013 (500)
    • ►  November (8)
    • ►  October (1)
    • ►  July (4)
    • ►  June (9)
    • ►  May (15)
    • ►  April (24)
    • ►  March (43)
    • ►  February (73)
    • ▼  January (323)
      • Uninstalling Obiee 11g instance on a linux red hat
      • OBIEE 11g not showing new dashboard in the drop d...
      • OBIEE11g Installation
      • Starting OBIEE 11g Services on Linux
      • OBIEE11g Timestamp differencess
      • DAC11g Installation on Windows Server 2008R2.
      • BI Apps 7.9.6.4 Installation in widows server 2008R2
      • [OBIEE11g] - Eventually succeeded, but encountered...
      • [OBIEE11g] - Blue Screen Error While Login With Bi...
      • [OBIEE11g] - No Log Found Error
      • [OBIEE11g] - Stream Closed Error when Click on cor...
      • OBIA 7.9.6.4 RPD And Catalog Shared
      • [OBIEE11g] - Destination Path too Long error while...
      • [OBIEE11G] - Lookup table is a new feature in obie...
      • [OBIEE11g] - Create Veriable in OBIEE11g.
      • [OBIEE11g] - Configuring LDAP Server to provide OB...
      • [OBIEE11g] - Authentication Failure in OBIEE 11g
      • [OBIEE11g] - Bing Map Integration with OBIEE 11g
      • [OBIEE11g] - OBIEE Dashboard for Informatica Metad...
      • Informatica PowerCenter Upgrading from Version 8.6...
      • Data Modeling: Schema Generation Issue with ERwin ...
      • [OBIEE11g] - DAC Reporting in OBIEE11g
      • [OBIEE11g] - Publisher 11g – Performance Monitorin...
      • [OBIEE11g] - Auto Start OBIEE 11g using Windows Se...
      • [OBIEE11g] - Upgrade OBIEE 11.1.1.5 To Latest Vers...
      • OBIEE11g - User Right Click Interaction Control w...
      • [OBIEE11g] - Customizing Prompts ‘All Column Value...
      • [OBIEE11g] - Choosing the Right OBIEE Visualization
      • OBIEE11g - 11.1.1.6 New Features
      • [OBIEE11g] - Certification with Siebel Marketing f...
      • [OBIEE11g] - Creating a Stacked Bar Chart.
      • [BI EE11g] – Managing Host Name Changes
      • [DAC] - Multi Source Loads With OBIA
      • [Informatica] - ERROR CODES: [CNX_53021 ],[DOM_100...
      • [Informatica] - Informatica PowerCenter Repository...
      • [Informatica] - Processing UNICODE Characters in I...
      • [Linux] - Unix/Linix Commands
      • [DAC] - Full Load Vs Incremental Load
      • [Informatica] - Installation of Informatica 9.0.1 ...
      • [Informatica] - SF_34004- Service initialization ...
      • [Oracle Database] - Linux OS and Oracle database S...
      • [Oracle Database] - Installion Oracle database11g ...
      • [Informatica] - RR_4053 : Row error occurred while...
      • [OBIEE11g] - Change the placement of currency name
      • [OBIEE11g] - Exception Occuring During OBIEE 11.1....
      • What is Indexing in a Database
      • [OBIEE11g] - Setting up OBIEE11g Admin Tool for OD...
      • [OBIEE11g] - Getting Top-N Sales Reps Using the TO...
      • [OBIEE11g] - Getting Top-N Sales Reps Using Result...
      • [OBIEE11g] - Getting Top-N Sales Reps for Year and...
      • [OBIEE11g] - Analyzing Sales for “N Years Top-10 S...
      • [OBIEE11g] - Drill Down to Sub Reports Passing Mul...
      • [OBIEE11g[ - Configuring BI Scheduler for iBots on...
      • [OBIEE 11g] - How Application Roles, Groups and Us...
      • [OBIEE11g] - Setting up Access Permissions to Repo...
      • [OBIEE11g] - Fixing Weblogic and bi_server1 startu...
      • [OBIEE11g] - Deleting and Re-Creating Users in We...
      • [OBIEE 11g] - Backup and Restore of OBIEE Filesyst...
      • [OBIEE11g] - Creating Effective Bar Graphs
      • [OBIEE] - Useful SQL statements in Business Intell...
      • [OBIEE11g] - Creating Dashboard Traversing Throug...
      • [OBIEE11g] - Database Connection Failure while cr...
      • [DAC] - Admin password recovery
      • [Oracle 11g] - Oracle Database 11g installation on...
      • [OBIEE11g] - Variables in Oracle OBIEE 11g
      • [OBIEE11g] - Installing OBIEE 11g on Linux Fedora 17
      • [OBIEE11g] - Table view Date Column controlled by...
      • [OBIEE11g] - Adding Tooltips and conditional colo...
      • [OBIEE11g] - Show top-N Sales Persons in BI Publi...
      • [OBIEE11g] - Creating Scrolling Ticker Views
      • [OBIEE11g] - Authentication first with LDAP then ...
      • [OBIEE11g] - Relocation of OBIEE MetaData Reposit...
      • [OBIEE11g] - Hierarchical Roll-Up and Individual T...
      • [OBIEE11g] - Creation of Sales Reps Hierarchy wit...
      • [OBIEE11g] - Using external table to Filter BI Ans...
      • [OBIEE11g] - Configuring of RPD deployed on Linux...
      • [OBIEE11g] - Configuring an ODBC DSN for the Oracl...
      • [ODI] - Frequently Asked Questions (FAQ)
      • [OBIA] - Oracle BI Applications - Frequently Asked...
      • [OBIEE 11g] - Maps - Frequently Asked Questions (FAQ)
      • [OBIEE11g] - The 11g Features You Maybe Didn't Know!
      • [OBIEE11g] - New Features with OBIEE 11.1.1.6
      • [OBIEE11g] - Dashboard Prompt - "Prompt User"
      • [OBIEE11g] - [46153] The configuration file (O:\us...
      • [Informatica] - Multiple Chart of Accounts Configu...
      • [OBIEE11g] - Customizing Pivot Table Error
      • [OBIEE11g] - How to get Month Start Date and Month...
      • [OBIEE11g] - How to get Week Start Date and Week E...
      • [OBIEE11g] - How to rename My Dashboard
      • Table Organization in OBAW (Oracle Business Analyt...
      • [OBIEE11g] Uninstall OBIEE 11g
      • [OBIEE11g] - Command Line Merging in OBIEE 10g/11g
      • BI Publisher report is showing incorrect date(Show...
      • [OBIEE11g] - Connectivity issue from OBIEE (in Sol...
      • [OBIEE 11g] - Installation on Red Hat Linux
      • [OBIEE11g] - Different ToolTip for different rows ...
      • [OBIEE11g] - Integrating OBIEE 11g with EPM worksp...
      • [DAC] Fail to create indices during DAC execution ...
      • [DAC] Oracle DAC issue in 64 Bit Machine
      • [OBIEE11g] Connection Pool Select Button is Disabl...
Powered by Blogger.

About Me

Unknown
View my complete profile