Posted by: daveb78 | March 26, 2009

Who Needs a Quick Date Dimension?

Here’s a quick and easy way to generate a date dimension.

SELECT TO_CHAR (dt, 'YYYYMMDD') date_id,
       dt date_value,
       TO_CHAR (dt, 'MM/DD/YYYY') date_formatted_name,
       TO_CHAR (dt, 'Mon DD, YYYY') date_name,
       TO_CHAR (dt, 'YYYY') year_num,
       TRUNC (dt, 'YYYY') year_start_date,
       ADD_MONTHS (TRUNC (dt, 'YYYY'), 12) - 1 year_end_date,
       TO_NUMBER (TO_CHAR (dt, 'Q')) quarter_num,
       'Q' || TO_CHAR (dt, 'Q') quarter_name,
       TRUNC (dt, 'Q') quarter_start_date,
       ADD_MONTHS (TRUNC (dt, 'Q'), 3) - 1 quarter_end_date,
       TO_CHAR (dt, 'YYYY') || '-' || 'Q' || TO_CHAR (dt, 'Q') year_quarter_name,
       TO_CHAR (dt, 'MM') month_num,
       TO_CHAR (dt, 'Mon') month_short_name,
       TO_CHAR (dt, 'Month') month_long_name,
       TRUNC (dt, 'MM') month_start_date,
       LAST_DAY (dt) month_end_date,
       TO_CHAR (dt, 'YYYY') || '-' || TO_CHAR (dt, 'Mon') year_month_name_1,
       TO_CHAR (dt, 'YYYY') || '-' || TO_CHAR (dt, 'MM') year_month_num_1,
       TO_CHAR (dt, 'Mon') || ' ' || TO_CHAR (dt, 'YYYY') year_month_name_2,
       TO_CHAR (dt, 'YYYY') || TO_CHAR (dt, 'MM') year_month_num_2,
       TO_CHAR (dt, 'WW') week_of_year_num,
       TO_CHAR (dt, 'W') week_of_month_num,
       TRUNC (dt, 'W') week_start_date,
       TRUNC (dt, 'W') + 6 week_end_date,
       TO_CHAR (dt, 'YYYY') || TO_CHAR (dt, 'WW') year_week_num,
       TO_CHAR (dt, 'YYYY') || '-' || TO_CHAR (dt, 'WW') year_week_name,
       TO_CHAR (dt, 'D') weekday_num,
       TO_CHAR (dt, 'Day') weekday_name
  FROM (SELECT     TO_DATE (:start_date) + ROWNUM dt
              FROM DUAL
        CONNECT BY TO_DATE (:start_date) + ROWNUM <= TO_DATE(:end_date));
Posted by: daveb78 | October 23, 2008

Chart of Accounts Dimension for Finance Data Mart


Creating a dimensional model to represent GL data is relatively straightforward.  Basically the goal is to capture 3 fact areas:  Actual Balances, Budget Balances, and Journal Entries.  Typically the model will consist of 2 fact tables:  GL_Balances and GL_Journal_Entries.  With this design Actual and Budget measures are both part of the GL_Balances fact.  They may be represented on separate rows and differentiated by a foreign key to a dimension that indicates “Actual” or “Budget”, or they may be pivoted and placed right next to one another.  The latter scenario is often preferable because it makes it very easy to represent Actual-to-Budget comparisons in reports.  Journal Entry data must be in a separate fact table because the granularity is lower than that of the GL_Balances fact.  However, both fact tables will share several dimensions:

GL Period
Set of Books
Account Type (Revenue, Expense, Asset, Equity, Liability)
Chart of Accounts

This technical brief will focus on how to design and build the Chart of Accounts dimension(s).


The Chart of Accounts (COA) is a concept that is common to most accounting systems.  It is a list of all the accounts that are tracked by the accounting system.  The COA is often represented as a hierarchy but may or may not actually implement a strict one-to-many relationship from top to bottom.  This is an important point because identifying this characteristic is critical to developing the appropriate design.  If the COA is represented as a strict hierarchy, then a single COA dimension table can be used.  If not, then it may be necessary to create a dimension table for each level in the COA.


In Oracle EBS, the COA is identified in the transaction records by a field called CODE_COMBINATION_ID.  This field maps back to the table GL_CODE_COMBINATIONS, which holds the hierarchy in fields called Segment1, Segment2, Segment3, etc.  The business meaning of the fields can be found by querying the some of the FND tables:

SELECT application_column_name,
  FROM fnd_id_flex_segments fs, fnd_id_flexs f
 WHERE fs.id_flex_code = f.id_flex_code
   AND f.application_table_name = 'GL_CODE_COMBINATIONS';

The results of this query will look something like this.  This information is helpful to determine appropriate table and/or columns names for the COA dimension table(s).

SEGMENT1    Company
SEGMENT2    Commercial Unit
SEGMENT3    Branch
SEGMENT4    Department
SEGMENT5    Account

Getting the data for the dimension also requires queries against the FND tables.  The approach will be different depending on which design is chosen.  If the COA can be implemented as a single dimension table then following query is a good start.

Approach 1 – Query to Generate the COA Hierarchy for a single COA Dimension:

SELECT   gcc.segment1 company_cd,
         fvc.description company_desc,
         gcc.segment2 commercial_unit_cd,
         fvcu.description commercial_unit_desc,
         gcc.segment3 branch_cd,
         fvb.description branch_desc,
         gcc.segment4 department_cd,
         fvd.description department_desc,
         gcc.segment5 account_cd,
         fva.description account_desc
    FROM gl_code_combinations gcc,
         fnd_flex_values_vl fvc,
         fnd_flex_values_vl fvcu,
         fnd_flex_values_vl fvb,
         fnd_flex_values_vl fvd,
         fnd_flex_values_vl fva
   WHERE fvc.flex_value_set_id =
                                (SELECT flex_value_set_id
                                   FROM fnd_flex_value_sets
                                  WHERE flex_value_set_name = 'XX_COMPANY')
     AND fvc.flex_value_meaning = gcc.segment1
     AND fvcu.flex_value_set_id =
                        (SELECT flex_value_set_id
                           FROM fnd_flex_value_sets
                          WHERE flex_value_set_name = 'XX_COMMERCIAL_UNIT')
     AND fvcu.flex_value_meaning = gcc.segment2
     AND fvb.flex_value_set_id = (SELECT flex_value_set_id
                                    FROM fnd_flex_value_sets
                                   WHERE flex_value_set_name = 'XX_BRANCH')
     AND fvb.flex_value_meaning = gcc.segment3
     AND fvd.flex_value_set_id =
                             (SELECT flex_value_set_id
                                FROM fnd_flex_value_sets
                               WHERE flex_value_set_name = 'XX_DEPARTMENT')
     AND fvd.flex_value_meaning = gcc.segment4
     AND fva.flex_value_set_id =
                                (SELECT flex_value_set_id
                                   FROM fnd_flex_value_sets
                                  WHERE flex_value_set_name = 'XX_ACCOUNT')
     AND fva.flex_value_meaning = gcc.segment5
ORDER BY 1, 3, 5, 7, 9

If the design calls for separate dimension tables for each level within the COA then one approach is to build a View that will act as the single source for each dimension table.  The code below displays how this is done.

Approach 2 – DDL for a view that will act as the data source for each COA-related dimension table:

   SELECT DISTINCT fvt.flex_value_id coa_flex_value_id,
                   fvt.flex_value_meaning coa_flex_value_meaning,
                   fvt.description coa_description,
                   fvs.flex_value_set_name coa_flex_value_set_name
              FROM fnd_flex_values fv,
                   fnd_flex_values_tl fvt,
                   fnd_flex_value_sets fvs
             WHERE fv.flex_value_id = fvt.flex_value_id
               AND fvs.flex_value_set_id = fv.flex_value_set_id
               AND fvs.flex_value_set_name IN
               AND fvt.source_lang = 'US'
          ORDER BY 1;

Approach 2 – Query against the view to generate the data for a dimension table representing an individual COA segment:

SELECT coa_flex_value_meaning branch_cd,
       coa_description branch_name
  FROM gl_coa_v
 WHERE coa_flex_value_set_name = 'XX_BRANCH';

A query, similar to the one above, would be used to generate the dimension table for each segment of the COA.  The only change necessary would be the column name and the filter in the WHERE clause.


The Chart of Accounts is a fundamental accounting concept and the heart of a GL data mart.  It is the glue that ties GL Balances to Journal Entries and it enables drill-down capabilities that are so crucial to financial analysis.  Implementing the COA dimension is not difficult, but determining where to find the data within EBS may be challenging for someone that is experienced with the data model.  The approaches detailed above should be a good starting point for anyone who is beginning to design a finance or GL data mart.

Posted by: daveb78 | October 23, 2008

BI Publisher Template Relief with Oracle’s Model Clause

Anyone who has worked with BI Publisher would probably agree that designing layout templates for reports with complex formatting can be pretty tricky.  Unless you’re an XSL wizard there is a lot of trial and error to get things working right.  Of course most of us don’t have the luxury of spending lots of time on trial and error.  One fairly obvious solution, that works for me, is to do as much as possible in the report’s SQL, rather than in the layout template.  The benefit is three-fold: 1) development time is decreased, 2) most of the complicated stuff in centralized in the SQL and 3) performance is increased.  Digging through MS Word form fields to uncover layer upon layer of business rules, aggregations, and formatting logic can be a nightmare for whoever has to maintain the reports.  Chances are that my SQL is complex anyway, so adding some additional complexity is a reasonable trade-off if it means that my layout templates can be straight-forward and easy to understand.  Increased performance is also a nice benefit.

The point of this rant is to expound on the benefits of Oracle’s Model clause.  The Model clause gives me the functionality I need to do the necessary data manipulations that allow me to represent my data set exactly as I want.  The more manipulations I can do to the data set through SQL the less I have to do in my BI Publisher layout template.  A good example of this are subtotals.  Subtotals are usually calculated in reports rather than stored in the database.  Calculating subtotals is quite easy with some reporting tools, but up until fairly recently, doing so in BI Publisher was not.  Like many BIP functions it had to be implemented in XSL or some variant syntax.  However, I believe the latest version of BIP plugin for Word offers this functionality from a wizard/dialog box.  I have found that implementing subtotals with the Model clause to be both fun and easy.  I say fun because the Model clause itself is just so cool that using it for any purpose is fun.  I say easy because once you wrap your brain around how the Model clause works it’s quite easy to make it work for you.

The Model clause allows you to iterate through your data set as if it were a multi-dimensional array.  The syntax for referencing elements of the array is similar to that of procedural languages that many of us are familiar with.  This gives the ability to manipulate the dataset cell-by-cell to make it “look” exactly as you want.  So basically you can use it to model the dataset to a specific report layout.

Taking the idea one step further, it can be helpful to use the Model clause query as the source for a Materialized View.  This can be quite useful in a data warehouse environment where data is refreshed on a schedule.  The MV can be refreshed as part of the ETL process and available for use by the reporting system.  The added benefit of this approach is a significant performance boost because now the reports can execute a very simple query against the MV and get back a report-specific dataset, very quickly.  Why burden the report with running a complex query if that overhead can be handed off the ETL process and incurred during off-hours.

Link for more info on Model clause.

When creating a GL data mart one of the most important components is the Chart of Accounts (COA). Here is one way to create dimension tables for each level in the COA hierarchy.

Create a view called GL_COA_V, replacing the flex_value_set_name values for your specific COA:

CREATE OR REPLACE FORCE VIEW gl_stage.gl_coa_v (coa_flex_value_id,
   SELECT DISTINCT fvt.flex_value_id coa_flex_value_id,
                   fvt.flex_value_meaning coa_flex_value_meaning,
                   fvt.description coa_description,
                   fvs.flex_value_set_name coa_flex_value_set_name
              FROM gl_fnd_flex_values_s fv,
                   gl_fnd_flex_values_tl_s fvt,
                   gl_fnd_flex_value_sets_s fvs
             WHERE fv.flex_value_id = fvt.flex_value_id
               AND fvs.flex_value_set_id = fv.flex_value_set_id
               AND fvs.flex_value_set_name IN
               AND fvt.source_lang = 'US'

This view can be used as the single data source for each dimension table mapping. All you need is a filter to get the required hierarchy level for the dimension that you are mapping.

Here’s an example of a mapping for a dimension table called GL_ACCOUNT_D. Mappings for all other COA dimensions will be the same except for the filter value.

Update:  The scenario described below involved an attempt to pass parameters from a Dashboard to a BI Pub report using OBIEE running in a stand-alone OC4J.  Attempting to accomplish the same goal using OBIEE running in OAS was much easier.  It worked as the documentation describes and required no work-around.

I recently developed a proof-of-concept implementation of OBIEE for a client. As part of this I wanted to have a dashboard page with prompted values that would be passed to BI Publisher and then used as parameter values in a report that I had previously built. This seems like a pretty standard requirement, but it was actually a bit tricky to get it working.

The BI Pub report uses a single SQL query against a materialized view as a data source. The materialized view’s sole purpose is to encapsulate the very complicated report logic, thereby allowing my BI Pub report and template to be simple and easy to understand. The materialized view is not included in the OBIEE metadata. If it was in the metadata it may have been easier to create an answers query against it and then use that as my BI Pub report data source.

The first thing I tried was to create the prompt and, for each of its elements, declare presentation variables with the same name as the relevant parameters defined in my BI Pub report. I then embedded the BI Pub report into the dashboard page. Unfortunately the report did not recognize the presentation variables as report parameters. The progress icon appeared, but the report just hung.

The Solution:

I created an Answers query as a Narrative View with all the query filters were set to “As Prompted”. I then used the Narrative View interface to build up a URL that would execute the BI Pub request, just dropping in variables as needed. I then wrapped the URL in HTML tags so that it would render as a link on the dashboard page. Next I added the query to the dashboard page that had the prompt I created earlier. The dashboard page now displays the prompt and a link. Clicking the link generates a BI Pub request which in turn pops up my report as a PDF.

I will post screen shots soon…

…Screenshots, finally:

Posted by: daveb78 | July 2, 2008

OWB Documentation Generation

As I sit here cranking through page after page of documentation for a client’s data mart project, I can’t help but wonder why OWB can’t do this for me. It seems fundamental that a tool as sophisticated as OWB would be able to execute a task as trivial as generating documentation. The metadata is all there, so it seems that some XSL (or whatever) could easily be applied to generate something that’s at least marginally useful. Sure I could use Toad’s HTML documentation generator, but that would just present the info from a database perspective and may not be as useful to whoever will eventually take ownership of this datamart via the OWB client.

Posted by: daveb78 | July 2, 2008

First Post

Well, after months of telling myself that I should start a blog, I’ve finally done it.  I’m working on a pretty interesting project for a client right now and I’ve got a few experiences to share.  I expect to post a few entries related to OWB, OBIEE, and BI Publisher in the coming days.  Stay tuned…