Thursday Sep 12, 2013
Thursday Aug 29, 2013
By Saurabh Verma on Aug 29, 2013
NOTE: This support is only available for Oracle database.
When a LP is executed by an ODI agent then it creates ODI sessions for the scenario steps and sends appropriate SQL to the DB for execution, which in turn creates DB sessions. For long running jobs (or when there is a suspicion that some sessions are hanging in ODI) we need to correlate ODI sessions and the corresponding DB sessions.
This is achieved by examining the content of the ACTION column in the V$SESSION, which has the following format:
SESSION_ID: ODI Session ID. It is displayed in operator log.
STEP_NB: Step number (ex: the step number in the package). It is displayed in operator log.
STEP_RUN_NUMBER: If you restart the same session then this will provide Nth run info. Note that if the LP scenario step is set to create a new session in case of failure for LP restart then this number will always show 1.
TASK_ORDER_NUMBER: Task order number for the step. It is displayed in operator log.
Finding out the SQL statement being executed in the database:
The following SQL in the database will provide all the DB sessions being executed by the user '<user_name>'. Run it on the database with DBA user and replace <user_name> with the DB user being used by ODI to create DB sessions.
select a.action, b.sql_text
from v$session a, v$sqlarea b
and a.action is not null
Look into the content of the ACTION column to correlate it back to ODI sessions.
In the following example the SESSION_ID: 38420, STEP_NB: 2 and TASK_ORDER_NUMBER: 21 is in wait state. It is third re-start of the same session, marked by STEP_RUN_NUMBER.
This is the result of the SQL:
update SCOTT.TARGET_EMP T set ( T.ENAME, T.JOB, T.MGR, T.HIREDATE, T.SAL, T.COMM, T.DEPTNO ) = ( select S.ENAME, S.JOB, S.MGR, S.HIREDATE, S.SAL, S.COMM, S.DEPTNO from SCOTT.I$_TARGET_EMP S where T.EMPNO =S.EMPNO ) where (EMPNO) in ( select EMPNO from SCOTT.I$_TARGET_EMP where IND_UPDATE = 'U' )
Correlating this SQL to the ODI session step in the operator:
Monday Jun 10, 2013
By Saurabh Verma on Jun 10, 2013
Authored by : Manjunath Gangadhar
UOM or Unit Of Measure is the method of expressing the quantity of an item so that it can be tracked and accounted for in a system.
A data warehouse that stores and analyzes Items or Products must have a UOM standard defined.
This post describes OBIA requirements, design and implementation of UOM
What is UOM Conversion
Let’s start with an example:
Let’s say on 15th Jan, 2013 100 Ea of Mont Blanc pens were sold. Here Ea is Each which is a Unit of measure that says 10 individual Mont Blanc pens were sold.
On 25th Jan, 2013 10 DZN Mont Blanc Pens were sold
In this example Ea(Each) and DZN(Dozen) are the UOMs for the Item Mon Blanc Pens
Let’s say the user wants to see a report that provides the Total Number Of Mont Blanc Pens sold in Jan 2013. If we just sum the two numbers (100 + 10) and show 110 Total Pens sold, that would be wrong.
Instead the Total should be 220 Ea, since 1 DZN = 12 Ea
This conversion where 1DZN = 12 Ea is defined as the UOM Conversion.
Why is UOM needed in OBIA
OBIA caters to various source systems like EBS, PSFT, Siebel, JDE, Fusion etc. Each system has its unique way of defining UOMs in the OLTP. The systems also define the UOM conversion to aggregate and show data correctly.
Most of our customers implement multiple OLTP systems. This presents a unique opportunity in BI to define a universal conversion as the data flows into OBIA DW.
Let’s take examples of some source systems that OBIA supports.
EBS allows a “Standard UOM” at the Item Level, defined for the Master Organization. This Standard UOM can be overwritten at the Individual Inventory Org level and is defined as the “Primary UOM”. (Inventory Org is the organization/Location where the Item is physically manufactured or stocked)
EBS also allows UOMs to be classified into various UOM Classes. UOM classes are used to group together units of measure that have similar characteristics or that measure the same dimension. Example: Length, Area, Weight, Volume, Time etc.
The conversions within EBS are of two types;
Inter Class: meaning across classes. These conversions are stored in EBS, from one UOM class to another UOM class at the Base UOM level. The Base UOM code is the lowest UOM Code for the UOM Class e.g., ml for the Volume Class, inches for the Length Class, etc.
Intra Class: meaning within the class. These conversions are stored in the EBS, form one UOM Code to the Base UOM Code within that UOM Class.
Further the UOM conversions can be Item specific (e.g., Pens 1 Box = 100 Ea) or Item Independent (e.g., 1 DZN = 12 Ea)
Item is defined with a Standard UOM at the Item Level for the Business Unit (BU). This UOM can be overridden at the individual Inventory Org Level and is defined as the “Primary UOM”.
UOM Conversions are defined in both directions. I.e., UOM A to UOM B, and the vice-a-versa.
Further the UOM conversions can be Item specific (e.g., Pens 1 Box = 100 Ea) or Item Independent (e.g., 1 DZN = 12 Ea)
Fusion OLTP UOM design is very similar to EBS. We have similar concepts of UOM Class, InterClass and IntraClass conversions. The EBS concepts of Standard UOM and Primary UOM very well serve the same purpose in Fusion Apps.
Siebel does not have a concept of UOM Conversion.
OBIA Design and Solution
OBIA UOM Extraction Logic
EBS/Fusion – Both the interclass and intraclass conversions related to an Item and conversions which are independent of an item (called as Resource UOMs) are extracted into OBIA.
PSFT – Peoplesoft does not have a concept of UOM class. However the Item Specific and Item independent UOM and related conversions are similar here and both are extracted into OBIA.
Conformed UOM Domain
OBIA Design uses the Conformed Domains Concept for UOM. Since OBIA DW contains data from different source systems, and UOM codes/names could be different across sources, the Conformed Domain for UOM is helpful.
Example – A dozen could be called DZ in EBS and Dzn in PSFT. However when we analyze Facts or quantities along with the related UOM in OBIA, we use a conformed UOM which could be called “Dozen”.
Configuration Manager and UOMs
Let’s dig some more details on UOM Domains. We have a Source Domain called UOM and a Conformed Domain W_UOM. The Source Domains will get populated through the Domains ETL which the user would execute before running the Main ETL. Once the Source Domain UOMs are present in Configuration Manager, the customer has two options to map the source UOMs to Conformed UOMs–
- Enter all the Conformed UOM codes into CM and map the Source UOMs to Conformed UOMs. Example – Map DZ of EBS and Dzn of PSFT to the newly created Conformed UOM Dozen.
- Use the “Sync to Source” option of CM. By using this method, we can create Conformed UOM codes for all the Source UOMs present in a particular Source system and CM automatically maps them in a 1:1 fashion. This is the preferred method since the conformed UOMs would be similar to that of one of the Source systems that the user has.
In case of multi source environments, the user will have to map the unmapped UOMs from the other Source System(s) to the corresponding Conformed UOMs.
UOM Data in OBIA PS1 (22.214.171.124.1)
W_UOM_CONVERSION_G is a table which stores the UOM Conversions. It stores attributes like From UOM, To UOM and Conversion Rate. Irrespective of whether source systems store both A->B and B->A conversions, this table will have conversion rates captured for both permutations. This table stores the Conformed UOMs.
Master Item and Inventory Item – The Master Item information is captured in W_PRODUCT_D and W_INVENTORY_PRODUCT_D stores the Inventory Item information. As mentioned in previous sections, an UOM defined at Item level is called Standard UOM and UOM defined at Inventory level will be the Primary UOM. Hence, it becomes a no-brainer that W_PRODUCT_D will store the Standard UOM and W_INVENTORY_PRODUCT_D will have Primary UOM in OBIA.
These two tables store the Source value for UOMs. There are reusable Lookups built upon these tables LKP_PRODUCT_BASE_UOM and LKP_INVENTORY_PRODUCT_PRIMARY_UOM. These 2 Lookups have the logic to return the Conformed UOM Code.
Fact Tables will store the Conformed UOM codes. Fact tables will use the Conformed UOMs returned by the LKP_PRODUCT_BASE_UOM and LKP_INVENTORY_PRODUCT_PRIMARY_UOM during ETL for UOM Lookup and UOM Conversion Rate Lookup. The related UOM User Defined Functions (UDFs) are LKP_UOM_CONVERSION_CODE and LKP_UOM_CONVERSION_RATE.
As we have seen, ETL uses Conformed UOM codes at all places. Since the ETL has a dependency on the Source UOM to Conformed UOM Domain mapping, it becomes mandatory that the user does this Domain mapping before running the main ETL.
Pseudo-Code of UOM in OBIA
Fact ETL will have the following pseudo-code for UOM conversion and the conversion rate obtained here will be either used in ETL or RPD to sum up the Quantity related metrics.
Use the Reusable Lookups (LKP_PRODUCT_BASE_UOM and LKP_INVENTORY_PRODUCT_PRIMARY_UOM). Get the Primary Conformed UOM and Standard Conformed UOM.
Pass these as inputs along with Product Id, Inventory Org Id(present in Fact Staging and sourced from Source Tables) to UOM UDFs. (LKP_UOM_CONVERSION_CODE and LKP_UOM_CONVERSION_RATE). UDF’s logic is as mentioned below:
If Primary Rate is null
-> Return Standard Rate -> If that is null return default value of 1
If Primary Code is null -> Return Standard Code -> If that is null return the From UOM Code (the input value)
Also the Fact Tables might store the UOM Code and in such cases, the UOM stored in Fact Tables will be Conformed UOM codes.
Let’s take a look at a Dashboard page. In Procurement àSpend Analyzerà Item Details dashboard page, we have a report called “Spend Details By Item”. The report shows the spend details for each Item, along with item details, other metrics like Invoice Unit Price (average, minimum, maximum), Invoice Quantity and UOM Code. A screenshot of the report is shown below. The Invoice Quantity metric used the Conversion Rate which was calculated in ETL. Even though the transaction for Invoiced Quantity could have been happened in a different UOM (say Box) we have converted it to Ea and hence the summed up Quantity shows the accurate value as seen in the report.
Screenshot showing the metrics: Invoice Quantity calculation uses the Standard Conversion Rate calculated in ETL as mentioned in section “Pseudo-Code of UOM in OBIA”.
Invoice Quantity calculation:
The report also shows the UOM Code column. This comes from the Fact table itself as shown by the Lineage information below. Fact Tables stores the Conformed UOMs.
The Fact table W_AP_INV_DIST_F stores the Conformed UOM Code in this column. The Conformed UOM Code is evaluated in ETL using an UDF called as Domain Member Map. The pseudo code of the UDF is as shown below. It accepts the Source UOM Code and returns the Conformed UOM code. The user should have done the Source UOM à Conformed UOM mapping as mentioned in section “Configuration Manager and UOMs” for this UDF to work correctly.
DOMAIN_MEMBER_MAP( 'UOM', <Source UOM Value>, #DATASOURCE_NUM_ID, 'W_UOM' )
UOM is widely used across subject areas and it plays an important role in evaluating quantity metrics. We exclusively use Conformed UOMs in Model, ETL and Reporting Layers as explained in this post.
Tuesday May 21, 2013
By Saurabh Verma on May 21, 2013
Authored by : Sushanta Chakraborty
The purpose of this document is to provide enough information one might need while attempting an implementation of one or more offerings and subject areas using the Oracle BI Applications Universal Adaptors. There are several myths around what needs to be done while implementing Universal Adaptors, where can things go wrong if not configured correctly, what columns are to be populated as a ‘must’, how to provide ‘delta’ data set while shooting for incremental ETL runs and so on. All of these topics are discussed in this document.
Understanding the ‘entry points’ required to implement a subject area is the key to a successful implementation of Universal Adaptors.
Oracle BI Applications provide packaged ETL mappings against source OLTP systems like Oracle Fusion Applications, Oracle E-Business Suites, PeopleSoft, JD Edwards and Siebel across various business areas such as Human Resources, Supply Chain & Procurements, Order Management, Financials, Service and so on. However, Oracle BI Applications does acknowledge that there can be quite a few other source systems, including home-grown ones, typically used by SMB customers. And to that extent, some of the enterprise customers may also be using SAP as their source. Until it gets to a point where Oracle BI Applications can deliver pre-built ETL adaptors against each of these source systems, the Universal Adaptor becomes a viable choice.
A mixed OLTP system where one of them has pre-built adaptor for and the other doesn’t – is also a scenario that calls for the usage of Universal Adaptors. For instance, the core portion of Human Resources may be in PeopleSoft systems, but the Talent Management portion may be maintained in a non-supported (yet) application such as “Taleo”.
In order for customers to enable pulling in data from non-supported source systems into the Data Warehouse, Oracle BI Applications have created a so called “Universal Adaptor”. The reason this was doable in the first place was the fact that the ETL architecture of Oracle BI Applications had the evident support for this. Oracle BI Applications’ Data Warehouse consists of a huge set of facts, dimensions and aggregate tables. The portion of the ETL that loads to these ‘end’ tables are typically Source Independent (loaded using the folder SILOS, acronym of “Source Independent Load Objects”). These ETL maps/interfaces start from a staging table and load data incrementally into the corresponding end table. Aggregates are created upstream, and have no dependencies to the source system where the data came from. The ETL logic in SDE folder (acronym of “Source Dependent Extract”) that extracts into these staging tables (also called Universal Stage Tables) are the ones that go against a given source system, like EBS or PSFT FUSION and so on. For Universal, they go against a similarly structured CSV file. Take any Adaptor – the universal stage tables are exactly the same, structurally. The grain expectation is also exactly the same for all adaptors. And no wonder, while all these conditions are met, the SILOS logic will load the data (extracted from Universal) from the universal stage tables seamlessly.
Why did Oracle BI Applications decide to source from CSV files? In short, the answer to this is “to complete the end-to-end extract-transform-load story”. We will cover this in a bit more details and what the options are, in the next section.
One myth that implementers have while implementing Universal Adaptors is “Data for the universal staging tables should always be presented to Oracle BI Applications in the required CSV file format”.
If your source data is already present in a relational database, why dump it to CSV files and give it to Oracle BI Applications? You will anyway have to write brand new ETL mappings that read from those relational tables to get to the right grain and right columns. Then why target those to CSV files and then use the Oracle BI Applications Universal Adaptor to read from them and write to the universal staging tables? Why not directly target those custom ETL maps to the universal staging tables? In fact, when your source data is in relational tables, this is the preferred approach.
However, if your source data comes from 3rd party sources which you have outsourced, and probably have agreements with them to send you data files/reports/feeds once in a while, and if that 3rd party source doesn’t allow you to access their relational schema, then probably CSV files is the only alternative. A typical example would be Payroll data. A lot of organizations typically outsource their Payroll to 3rd party companies like ADP systems and so on. In those cases, ask for the data in the same manner that you expect in the Oracle BI Applications CSV files. Another valid example might be SaaS applications which usually do not allow direct database accesses. File is the best option for them.
Also, if your source data lies in IBM mainframe systems, where it is typically easier to write COBOL programs or whatever to extract the data in flat files, presenting CSV files to Oracle BI Applications Universal Adaptor is probably easier. Irrespective of how to populate the universal staging tables (relational sources or CSV sources) five very important points should always be kept in mind:
- Grain of the universal staging tables are met properly.
- The uniqueness of records do exists in the (typically) INTEGRATION_ID columns.
- The mandatory columns are populated the way they should be.
- The relational constraints are met well while populating data for facts. In other words, the natural keys that you provide in the fact universal staging table, must exist in the dimensions. This is with respect to the FK resolution (dimension keys into the end fact table) topic.
- Incremental extraction policy is well set up. Some overlap of data is OK, but populating the entire dataset to the universal staging tables will prove to be non-performing.
Note: For the rest of the document, we will assume that you are going the CSV file approach, although re-iterating, it is recommended that if your source data is stored in a relational database you should write your own extract mappings.
There are several entry points while implementing a subject area using Universal Adaptors. The base dimension tables and base fact tables have their corresponding CSV files where you should configure the data at the right grain and expectations. Other kinds of tables include ‘Exchange Rate’ and ‘Codes’ (Domains). Exchange Rate (W_EXCH_RATE_G) has its own single CSV file, whereas the Codes table (W_CODE_D) has a CSV file, one per each code category. To get to see all code-names well enough in the dashboards/reports, you should configure all the ‘required’ code CSV files for the subject area in question.
Note: The Codes table has been replaced with Domains architecture in newer releases of BI Applications.
- Start with populating the offering specific common dimension CSV files, like Job, Pay Grade, HR Position, GL Account, etc., depending on your offering (such as HR, or FINANCE etc). It is not necessary that all offerings would have “their set of common dimensions used in all facts within the offering”.
- Then configure subject area specific dimensions, like Job Requisitions, Recruitment Source etc (when implementing Recruitment) or Learning Grade, Course (when implementing Learning) or Pay Type etc (when implementing Payroll) or Absence Event, Absence Type Reason (when implementing Absence) and so on. These are examples from HR offering. Similarly, it applies for Financials subject areas such as AR or AP etc., where you should consider area specific dimension needs at this time.
- Then configure related COMMON class dimensions applicable for all, like Internal Organizations (logical/applicable partitions being “Department”, “Legal Entity” , “Business Unit” etc), or Business Locations (logical/applicable partitions being “Employee Locations”, “Inventory Locations” etc.).
- Consider other shared dimensions and helper dimensions like Status, Transaction Types, and Exchange Rate etc. Special handling of time dimension should be addressed here (such as Fiscal or enterprise calendars etc).
- Then consider the code dimensions. By this time you are already aware of what all dimensions you are considering to implement, and hence, can save time by configuring the CSVs for only the corresponding code categories. For newer releases, you would configure the domain set up at this point.
- For facts, start with configuring the primary fact for your offering. Since the dimensions are already configured, the natural key of the dimensions are already known to you and hence should be easy to configure them in the fact. For some offerings, there isn’t a concept of a “primary fact”. If so, go to the next step.
- Primary fact should be followed by subject area specific facts, like Payroll, Job Requisition Event, Applicant Event, etc (under HR offering) or AR, AP etc (under Financial offering) and so on.
- Now that all the CSV files for facts, dimensions, and helper tables are populated, you should move your focus towards Domains. For E-Business Suite & PeopleSoft Adaptors, we do mid-stream lookups against preconfigured lookup CSV files. The map between source values/codes to their corresponding domain values/codes come pre-configured in these lookup files. However, for Universal Adaptor, no such lookup files exist. This is because of the fact that we expect that the accurate domain values/codes will be configured “along-with” configuring the base dimension tables where they apply. Since everything is from a CSV file, there is no need to have the overhead of an additional lookup file acting in the middle. Domain value columns begin with “W_” [excluding the system columns like W_INSERT_DT and W_UPDATE_DT] and normally they are mandatory, cannot be nulls, and the value-set cannot be changed or extended. We do relax the extension part on a case by case basis, but in no way, the values can be changed. The recommendation at this stage is that you go to the DMR guide (Data Model Reference Guide), get the list of table-wise domain values, understand the relationships clearly in cases there exists any hierarchical or orthogonal relations, identify the tables where they apply and then their corresponding CSV files, look at the source data and configure the domain values in the same CSV files. Note that if your source data is in a relational database and you have chosen to go the ‘alternate’ route of creating all extract mappings by yourself, the recommendation is to follow what we have done for E-Business Suite Adaptors and PeopleSoft Adaptors and create separate domain value lookup CSV files, and do a mid-stream lookup.
- Note that the above discussion on Domains has been revamped in newer releases on BI Applications. Now, we only have the CODE columns in our data model and the NAME and DESC columns have been taken away. We still do a mid-stream lookup in EBS/PSFT adaptors to resolve the target domain code against the source domain code, but no longer use lookups to resolve the target domain names and descriptions based on source/target domain codes. Rather, these name/description lookups happen on the fly at the RPD level, where the name or description is brought back to the report depending on the code and language preferred in reports. The domain mapping happens in Configuration Manager now, and no longer in CSV files. You will be taken to the step of configuring or mapping them in Configuration Manager.
- Last, but not the least, configure the ETL parameters. Read up the documentation for these parameters, understand their expectations, study your own business requirements and then set the values accordingly.
Domain values constitute a very important foundation for Oracle Business Intelligence Applications. We use this concept heavily all across the board to ‘equalize’ similar aspects from a variety of source systems. The Oracle Business Intelligence Applications provide packaged data warehouse solutions for various source systems such as E-Business Suite, PeopleSoft, Siebel, JD Edwards and so on. We attempt to provide a “source dependent extract” type of a mapping that leads to a “source independent load” type of a mapping, followed by a “post load” (also source independent) type of mapping. With data possibly coming in from a variety of source systems, this equalization is necessary. Moreover, the reporting metadata (OBIEE RPD) is also source independent. The metric calculations are obviously source independent.
The following diagram shows how a worker status code/value is mapped onto a warehouse domain to conform to a single target set of values. The domain is then re-used by any measures that are based on worker status.
Domain values help us to equalize similar aspects or attributes as they come from different source systems. We use these values in our ETL logic, sometimes even as hard-coded filters. We use these values in defining our reporting layer metrics. And hence, not configuring, incorrectly configuring, or changing the values of these domain value columns from what we expect, will lead to unpredictable results. You may have a single source system to implement, but still you have to go through all the steps and configure the domain values based on your source data. Unfortunately, this is small price you pay for going the “buy” approach VS the traditional “build” approach for your data warehouse.
One of the very frequently asked question is “what is the difference between domain value code/name pairs VS the regular code/name pairs that are stored in W_CODE_D (or W_DOMAIN_MEMBER_G and W_DOMAIN_MEMBER_MAP_G along with their corresponding Translation tables in newer versions of BI Applications)”.
If you look at the structure of W_CODE_D table, it appears to be also capable of standardizing code/name pairs to something common. This is correct. However, we wanted to give an extensive freedom to users to be able to do that standardization (not necessarily equalization) of their code/names and possibly use that for cleansing as well. For example, if the source supplied code/name are possibly CA/CALIF or CA/California, you can choose the W_CODE_D approach (using Master Code and Master Map tables – see configuration guide for details) to standardize on CA/CALIFORNIA. The equivalent of the above is the W_DOMAIN_MEMBER_MAP_G table in newer versions of BI Applications.
Now, to explain the difference of domain value code/name pairs Vs the regular source code/name pairs, it is enough if you understand the significance of the domain value concept. To keep it simple, wherever we (Oracle Business Intelligence Applications) felt that we should equalize two similar topics that give us analytic values, metric calculation possibilities etc, we have “promoted” a regular code/name pair to a domain value code/name pair.
If we have a requirement to provide a metric called “Male
Headcount”, we can’t do that accurately unless we know which of the headcount
is “Male” and which is “Female”. This metric therefore has easy calculation
logic: Sum of headcount where sex = Male. Since PeopleSoft can call it “M” and
EBS can have “male”, we decided to call it a domain value code/name pair,
W_SEX_MF_CODE (available in the employee dimension table). Needless to say, if
you didn’t configure your domain value for this column accurately, you won’t
get this metric right.
The Oracle BI Applications mostly use Name and Description columns in the out-of-the-box dashboards and reports. We use Codes only during calculations, wherever required. Therefore, it is obvious that if the names and descriptions didn’t resolve against their codes during the ETL, you will see blank values of attributes (or in some cases, depending on the parameter setting, you might see strings like <Source_Code_Not_Supplied> or <Source_Name_Not_Supplied> and so on). In newer versions of BI Applications where names and descriptions are resolved using on-the-fly RPD lookups and not in the ETL, the reports might show values such as ‘__UNSASSIGNED__’, if not mapped well enough in Configuration Manager.
Another point to keep in mind is that all codes should have distinct name values. If two or more codes have the same name value, at the report level you will see them merged. The metric values may sometimes appear in different lines of the report, because OBIEE Server typically throws in a GROUP BY clause on the lowest attribute (code).
Once implemented, you are free to promote the source code columns from the logical layer to the presentation layer. You might do this when you know your business users are more acquainted to the source code values rather than the name values. But that is a separate business decision. The general behavior is not like that.
Although you can choose to supply the entire dataset during incremental runs, for all practical reasons, this is not recommended. Firstly because then the ETL has to process all the records and determine what needs to be applied and what can be rejected. Secondly, the decision ETL takes may not be accurate. ETL decisions are based on the values of the system date columns like CHANGED_ON_DT, AUX1_CHANGED_ON_DT, AUX2_CHANGED_ON_DT, AUX3_CHANGED_ON_DT and AUX4_CHANGED_ON_DT columns only. We do not explicitly compare column-by-column and determine whether an update is required. We believe that if something has changed, probably one of the four date columns must have changed. And in that case, we simply update. If all 5 date columns are same, we pretty much tend to reject. The base of this decision is the correctness of the date columns. If your source system does not track the last updated date column on a record well enough, it becomes your responsibility to force an update, no matter what. An easy way to do this is to set SESSSTARTTIME in one of these columns during extract. This will force to detect a change, and we will end up updating.
No wonder, this is not be the best idea. By all means, you should provide the true “delta” data set during every incremental run. A small amount of overlap is acceptable, especially when you deal with flat files. Our generally accepted rules for facts or large dimensions are either:
· Customer does their own version of “persisted staging” so they can determine changes at the earliest opportunity and only load changes into universal staging tables
· If absolutely impossible to determine the “delta” or to go the “persistent staging” route, Customer only does full load. Otherwise doing a full extract every time and processing incrementally will take longer.
Follow the principles below to decide on your incremental strategy:
- (Applies to relational table sources) Does your source system capture last update date/time accurately in the source record that change? If so, extracting based on this column would be the best idea. Now, your extract mapping may have used 2 or 3 different source tables. Decide which one is primary and which ones are secondary. The last update date on the primary table goes to the CHANGED_ON_DT column in the stage table. The same from the other two tables go to one of the auxiliary changed on date column in the stage table. If you design your extract mapping this way, you are almost done. Just make sure you add the filter criteria “primary_table.last_update_date >= LAST_EXTRACT_DATE parameter”. The value of this parameter is usually maintained within the ETL orchestration layer.
- (Applies to CSV file sources) Assuming there is a mechanism which you can trust that gives you the delta file during each incremental load; does the ‘delta’ file come with a changed value of system dates? If yes, you’re OK. But if not, then you should add an extra piece of logic in the out of the box SDE_Universal** mappings that sets SESSSTARTTIME to one of the system date columns. This will force an update (when possible) no matter what.
- (Applies to CSV file sources) If there are no mechanisms to easily give your delta file during incremental, and it seems easier to get a complete ‘dump’ every time, you have actually couple of choices:
a. Pass the whole file in every load, but run true incremental loads. Note that this is not an option for large dimensions or facts.
b. Pass the whole file each time and run full load always.
c. Do something at the back-office to process the files and produce the ‘delta’ file yourselves.
The choices (a) and (b) may sound a bad idea, but we’ve seen it to be a worthwhile solution compared to (c), if the source data volume is not very high. For an HR Analytics implementation (as an example), this could be OK as long as your employee strength is no more than 5000 and you have no more than 5 years of data. The benchmark might be different for a Financial Analytics implementation.
The choice (c) is more involved but produces best results. The idea is simple. You store the last image of the full file that your source gave you [call ‘A’]. You get your new full file today [call ‘B’]. Compare A & B. There are quite a few data-diff software available in the market, or better if you could write a Perl or python script on your own. The result of this script should be a new delta file [call ‘C’] that has the lines copied from B that has changed as compared to A. Use this new file C as your delta data for your incremental runs. Also, discard A and rename B as A, thereby getting ready for the next batch.
Having said that, it is worthwhile to re-iterate that the “Persisted Staging” is a better approach as it is simpler and uses the ETL logic to do the comparison. Oracle BI Applications have used this technique in HR adaptors for E-Business Suite and PeopleSoft, in case you wanted to refer to them.
If there are other options not considered here, by all means, try them out. This list is not comprehensive, it is rather indicative.
Tuesday May 14, 2013
By Saurabh Verma on May 14, 2013
Authored By : Don Co Seng
Oracle BI Applications (BIAPPS) 126.96.36.199.1 was recently released and is now available for download. One of the biggest features in this release is the introduction of Oracle Data Integrator (ODI) as the embedded ETL tool. With the introduction of ODI, a new method of orchestrating the task of loading your data warehouse is now required. This requirement brings about the introduction of Load Plan Generator (LPG).
LPG is a utility for generating ODI load plans for a desired subset of fact tables to be populated into BIAPPS Data Warehouse against one or more source systems. An ODI load plan is an executable object in ODI allowing you to organize tasks based on pre-defined order on the basis of the fact tables being loaded. LPG is invoked from Configuration Manager (CM) and makes use of metadata stored in CM and ODI repository. There is no separate repository required for LPG. This results in significantly lower metadata development and maintenance costs as LPG uses same metadata as in the ETL tool repository.
Before we get to the inner workings of LPG, let’s take a quick look at a few key concepts that drive the LPG behavior.
BI Apps taxonomy
At the top of the BIAPPS taxonomy are the different offerings available such as Financial Analytics, HR, CRM, etc. Under the offerings are the functional areas such as accounts payable in finance or payroll in HR. Below the functional areas are the different fact groups like “AP Transactions and Balance” or “Payroll Balance”. For each fact group, there are dimension groups associated with it. A dimension group can be specific to a particular fact group or shared across different fact group. The BI apps taxonomy drives both load plan generation as well as the setup flows in Functional Setup manager.
Offering (contains) -> Functional Areas (contains) -> Fact Groups (associated to) -> Dimension Groups
As for BIAPPS load phases, it is similar to that of previous BIAPPS releases. There are 3 main phases: Source Data Extract (SDE), Source Independent Load (SIL), and Post Load Process (PLP). The SDE phase consists of tasks extracting data from your different source systems. This phase loads all your staging tables and requires source system downtime. The SIL phase loads data from your staging tables into their respective dimension or fact tables. The PLP phase loads data into aggregate tables or some other facts requiring additional data processing. The SIL and PLP phase requires data warehouse downtime to complete the load process.
With BI apps 188.8.131.52.1, due to clear separation of SDE and SIL/PLP phases it’s possible to control and optimize the source system / warehouse downtime to the corresponding phases.
Design Time vs. Runtime dependencies
The LPG algorithm deals with the design time dependencies and run time dependencies separately. Any intra-entity specific design time dependencies are seeded in the repository. For example if a dimension has multiple steps to load the target table in SIL phase, these steps are seeded once in the ODI repository as they are known at design time and never change. The run time dependencies i.e. association of dimension to fact, or association of a dimension or fact to corresponding staging tables on the basis of sources are calculated by the LPG engine.
This approach reduces the number of iterations and the metadata required to get task ordering correct at the entire graph level as the design time dependencies are always consistently honored. LPG behavior is deterministic and does not require multiple iterations to get your entire graph behave correctly.
Load Plan Generator
Now, let’s take a closer look at how LPG works. At the core of LPG are load plan components. The load plan components capture the design time dependencies. There is a load plan component per dimension or fact per phase in general. Load plan components are used as building blocks by LPG to generate a complete load plan. Load plan components are further classified into two categories:
Development components are defined at the grain of fact groups or dimension groups as described earlier. Each component contains one or more references to actual ODI scenarios. Each development component requires all steps for loading a particular dimension or fact group. The order of scenarios is pre-defined during development. Most of the development components are for a specific dimension or fact group. However, there are also those defined for supporting tables like persistent staging or general tables.
System components are defined to capture the phases and ordering of phases. The system components are mostly static, since BIAPPS load phases rarely change. For special cases, the dependencies across dimension groups and fact groups are defined here. Also, inclusion of support tables such as persistent staging table is controlled here. System components contain references to development components.
Generating a Load Plan
As mentioned earlier, LPG is invoked from CM. Generating a load plan is a two step process. First, a user creates a load plan definition using CM. A load plan definition is mainly a list of one or more fact groups per source system which the user desires to load. The user then invokes LPG to generate a load plan based on this load plan definition. When complete, the generated load plan is stored back in a load plan folder in ODI repository.
Load Plan generation logic
So, how does LPG figure out which dimension or fact group component to include in the load plan to be generated? LPG generates a load plan utilizing the following information:
1. Fact tables belonging to selected fact groups
o LPG determines the fact tables and fact group association via an ODI flex field at the fact table level in the ODI data store.
2. Dimensions dependencies to Facts
o The main source of dependency information between dimensions and facts are the foreign key constraints between them. Dimension to dimension dependency (snow-flake) is also supported up to the 2nd level.
3. Staging tables related to Facts and Dimensions
o Staging and warehouse tables are resolved via BIAPPS data model naming standard. For example XXX_DS is the staging table for XXX_D dimension table.
4. Related PS or TMP tables used as source in scenarios
o PS and TMP dependencies are resolved based on which are used as source or lookup table in scenarios required for the dimension or fact group.
5. Keywords in load plan steps for domains and class dimensions
o An additional resolution is through the use of keywords in load plan steps which are used mainly for resolving steps within class dimensions or domains. These are generic tables used across facts / dimensions and the keywords help associate as subset task for these tables to specific facts or dimensions.
For table maintenance, additional steps are added in the generated load plan to perform truncate, index management, and table analysis based on pre-defined settings and table types. We will cover this in more detail in a follow up post.
For multi-source support, the user can create a load plan definition
containing one or more source system for a fact particular fact group. There are 3 sequencing options in this
release for multi-source support:
1. Extract-Load, Extract-Load, Extract-Load (EL, EL, EL) – LPG generates separate load plans containing all phases for each source system. Each load plan is executed serially of each other.
2. Extract, Extract, Extract, Load (E,E,E,L) – LPG generates multiple SDE only load plan for each source and a single SIL/PLP load plan. Each SDE only load plan will be sourcing from a particular adaptor. The SIL/PLP load plan should be executed after all of the SDE load plans. Each load plan is executed serially of each other.
3. Extract-Extract-Extract-Load (E-E-E-L) – LPG generates a single load plan containing multiple SDE phases and one SIL phase. This load plan will simultaneously extract data from different source systems. After the SDE phase completes, the SIL followed by PLP phase ensues.
For more details and information on LPG, please refer to documentation available in OTN.
Thursday May 09, 2013
By Saurabh Verma on May 09, 2013
From the announcement today
Oracle Business Intelligence (BI) Applications 184.108.40.206.1 (also known as 11g PS1) is now available on the Oracle Software Delivery Cloud (eDelivery) and on the Oracle Technology Network (OTN). This is the first major release on the 11g code line leveraging the power of ODI, and certified with the latest version of Oracle BI Foundation 220.127.116.11.
This major new release of Oracle Business Intelligence Applications 11g adds:
1. New in-memory analytic applications
2. Significantly expand functional content across existing CRM and ERP Oracle BI Applications
3. Re-design to leverage the power of Oracle Data Integrator and, optionally, GoldenGate
4. New out-of-the-box utilities to increase productivity and further drive down Total Cost of Ownership (TCO).
New Oracle BI Applications:
· Oracle Student Information Analytics enables academic institutions to improve recruitment, lower dropout rate by tracking student attendance in real-time, monitor graduation rate, manage student financial transactions, and match up school resources with student needs more effectively.
· Oracle Indirect Spend Planning – a prebuilt planning application that teams with Oracle Procurement and Spend Analytics to enable businesses to optimize spend through what-if modeling
New Content Enhancements: Significant expansion of content improves existing front and back-office Oracle BI Applications including:
· Oracle Human Resources Analytics expands analysis on HR programs and workforce performance with new
Time and Labor Analysis, global Payroll Analysis and Headcount Gain and Loss.
· Oracle Financial Analytics improves company Financial Performance with new Fixed Assets Analysis, providing complete lifecycle of assets from acquisitions, capitalization to retirements. Budgetary Control Analysis, ideal for public sector, delivers insight into expense & revenue budgets including analysis of budgeted amounts, encumbrances, expenditures, funds available, overspending, and recognized revenues.
· Oracle Project Analytics expands insight into project-based data with new Resource Management Analysis, providing visibility into project requirements, trending, utilization, competencies and alignment. Reconciliation of project sub-ledger and General Ledger assist project accountants in reconciling differences between the two ledgers for cost and revenue transactions. Perspective based dashboards provide an optimized Project Analytics user interface that orients the content around specific user perspectives.
· Oracle Supply Chain & Order Management Analytics introduces new Enhanced Inventory Analysis, providing deep insight into Inventory Cycle Count, Inventory Aging, Inventory Expiration/Obsolescence tracking and Inventory balances and transaction details at lot level. Item Cost History Analysis allows insight into historical cost of Supply Chain related items.
· Oracle Procurement & Spend Analytics introduces new Sourcing Analysis, providing a new comprehensive Sourcing Business Process that includes negotiations, awards, savings and performance. Award decisions will help identify trends and check if there is a margin for any savings opportunity.
· Oracle CRM Analytics expands insight into front office Analytics with Indirect Forecasting and Service Contracts support. Forecasting Analysis is now possible when submissions are made up a forecast hierarchy that is outside of the reporting hierarchy. Oracle Price Analytics has added E-Business Suite to the list of supported sources. Customers now have a ready means to assess pricing performance and arrest revenue and margin leakage. Robust price waterfall analysis possible with the new Waterfall view.
Using New Oracle BI Foundation Suite 18.104.22.168 Features:
· Visualization Enhancements, and User Interaction Improvements
· BI Mobile HD Enhancements: BI Applications available on mobile without additional development
New Oracle Data Integration: BI Apps are completely re-architected to leverage Oracle Data Integrator and Oracle GoldenGate.
· BI Applications is now available with next-generation Data Integration. Oracle Data Integrator (ODI) delivers unique next-generation, Extract Load and Transform (E-LT) technology that improves performance and reduces data integration costs—even across heterogeneous systems, and improves productivity.
· Functional Setup Manager provides a Web based Administrative User Interface to track and manage implementation projects and their required functional setup steps
· Configuration Manager provides a centralized Web based administrative user interface to simplify the setup and maintenance of Oracle Business Intelligence Applications.
· A new optional pre-packaged solution, that leverages GoldenGate, eliminates the need for an ETL batch window and minimizes the impact on OLTP systems, while providing near real-time reporting and enabling more reliable change data capture and delete support.
· Customers may continue alternatively to use Oracle BI Apps 7.9.6.x and Informatica ETL.
Monday Apr 22, 2013
By Saurabh Verma on Apr 22, 2013
A lot of times, a question comes up as to what value a warehouse based solution brings. Below are a few use cases that exemplify the need for a persistent storage. Both real time and warehouse based analytics are complementary to each other and serve different use cases. In a follow up post we will also cover the use cases best served by real time and not warehouse based solution.
Sample Use Case
4 corporate level and functional currency available in warehouse
Show my worldwide bookings for last week in US dollars
e.g. Bookings / Billings / Backlog report
Common UOM reporting
Show me units shipped worldwide in Sales UOM for a product
Snapshots / Trend Reports
Balances / Snapshot tables providing trend reporting
Inventory Balance, Headcount Balance, Opportunity Revenue trend views
“What changed” report
Time Series Calculations
Enable usage of time series calculations
Year Ago, Qtr Ago, Month Ago
YTD, QTD, MTD etc, % Growth metrics
Assignment record having multiple transitions
# of hires, # of transfers, # of promotions, # of resignations require event interpretation and comparison to prior records
Hierarchies / SCD / As Is vs As Was reports
Report on various versions of hierarchies / historical views
Show me revenue numbers with current sales org over last 3 years
Also, performance is one of the key value of the warehouse which is due to the following transformations or services provided by the warehouse.
Most Entities are denormalized in the warehouse
2 – 3 x lesser joins in queries
All queries have same shape.
One pattern to tune
Normalized schemas result in unpredictable query shapes requiring case by case tuning
All columns are (bitmap) indexed
Can be freely used in prompts, filters, group by etc
OLTP tables have optional relationships
Large number of outer joins causing tuning and modeling issues
Overloaded Tables containing multiple entities
Sometimes cause query plan distortions
Use column A or column B depending on transaction type
Require a case statement
Cannot be indexed / used in filters etc
Timestamp join to date joins require date functions
Suppress index usage
No aggregates in OLTP
Enable exalytics usage / other performance tweaks
Monday Nov 26, 2012
By Saurabh Verma on Nov 26, 2012
A typical data warehouse contains Star and/or Snowflake schema, made up of Dimensions and Facts. The facts store various numerical information including amounts. Example; Order Amount, Invoice Amount etc.
With the true global nature of business now-a-days, the end-users want to view the reports in their own currency or in global/common currency as defined by their business.
This presents a unique opportunity in BI to provide the amounts in converted rates either by pre-storing or by doing on-the-fly conversions while displaying the reports to the users.
OBIA caters to various source systems like EBS, PSFT, Sebl, JDE, Fusion etc. Each source has its own unique and intricate ways of defining and storing currency data, doing currency conversions and presenting to the OLTP users.
For example; EBS stores conversion rates between currencies which can be classified by conversion rates, like Corporate rate, Spot rate, Period rate etc. Siebel stores exchange rates by conversion rates like Daily. EBS/Fusion stores the conversion rates for each day, where as PSFT/Siebel store for a range of days. PSFT has Rate Multiplication Factor and Rate Division Factor and we need to calculate the Rate based on them, where as other Source systems store the Currency Exchange Rate directly.
The data consolidation from various disparate source systems, poses the challenge to conform various currencies, rate types, exchange rates etc., and designing the best way to present the amounts to the users without affecting the performance.
When consolidating the data for reporting in OBIA, we have designed the mechanisms in the Common Dimension, to allow users to report based on their required currencies.
OBIA Facts store amounts in various currencies:
Document Currency: This is the currency of the actual transaction. For a multinational company, this can be in various currencies.
Local Currency: This is the base currency in which the accounting entries are recorded by the business. This is generally defined in the Ledger of the company.
Global Currencies: OBIA provides five Global Currencies. Three are used across all modules. The last two are for CRM only. A Global currency is very useful when creating reports where the data is viewed enterprise-wide. Example; a US based multinational would want to see the reports in USD. The company will choose USD as one of the global currencies. OBIA allows users to define up-to five global currencies during the initial implementation.
The term Currency Preference is used to designate the set of values: Document Currency, Local Currency, Global Currency 1, Global Currency 2, Global Currency 3; which are shared among all modules. There are four more currency preferences, specific to certain modules: Global Currency 4 (aka CRM Currency) and Global Currency 5 which are used in CRM; and Project Currency and Contract Currency, used in Project Analytics.
When choosing Local Currency for Currency preference, the data will show in the currency of the Ledger (or Business Unit) in the prompt. So it is important to select one Ledger or Business Unit when viewing data in Local Currency. More on this can be found in the section: Toggling Currency Preferences in the Dashboard.
When extracting the fact data, the OOTB mappings extract and load the document amount, and the local amount in target tables. It also loads the exchange rates required to convert the document amount into the corresponding global amounts.
If the source system only provides the document amount in the transaction, the extract mapping does a lookup to get the Local currency code, and the Local exchange rate. The Load mapping then uses the local currency code and rate to derive the local amount. The load mapping also fetches the Global Currencies and looks up the corresponding exchange rates.
The lookup of exchange rates is done via the Exchange Rate Dimension provided as a Common/Conforming Dimension in OBIA.
The Exchange Rate Dimension stores the exchange rates between various currencies for a date range and Rate Type. Two physical tables W_EXCH_RATE_G and W_GLOBAL_EXCH_RATE_G are used to provide the lookups and conversions between currencies. The data is loaded from the source system’s Ledger tables. W_EXCH_RATE_G stores the exchange rates between currencies with a date range. On the other hand, W_GLOBAL_EXCH_RATE_G stores the currency conversions between the document currency and the pre-defined five Global Currencies for each day. Based on the requirements, the fact mappings can decide and use one or both tables to do the conversion.
Currency design in OBIA also taps into the MLS and Domain architecture, thus allowing the users to map the currencies to a universal Domain during the implementation time. This is especially important for companies deploying and using OBIA with multiple source adapters.
Some Gotchas to Look for
It is necessary to think through the currencies during the initial implementation.
1) Identify various types of currencies that are used by your business. Understand what will be your Local (or Base) and Documentation currency. Identify various global currencies that your users will want to look at the reports. This will be based on the global nature of your business. Changes to these currencies later in the project, while permitted, but may cause Full data loads and hence lost time.
2) If the user has a multi source system make sure that the Global Currencies and Global Rate Types chosen in Configuration Manager do have the corresponding source specific counterparts. In other words, make sure for every DW specific value chosen for Currency Code or Rate Type, there is a source Domain mapping already done.
This section will briefly mention the technical scenarios employed in the OBIA adaptors to extract data from each source system.
In OBIA, we have two main tables which store the Currency Rate information as explained in previous sections. W_EXCH_RATE_G and W_GLOBAL_EXCH_RATE_G are the two tables.
W_EXCH_RATE_G stores all the Currency Conversions present in the source system. It captures data for a Date Range. W_GLOBAL_EXCH_RATE_G has Global Currency Conversions stored at a Daily level. However the challenge here is to store all the 5 Global Currency Exchange Rates in a single record for each From Currency. Let’s voyage further into the Source System Extraction logic for each of these tables and understand the flow briefly.
EBS: In EBS, we have Currency Data stored in GL_DAILY_RATES table. As the name indicates GL_DAILY_RATES EBS table has data at a daily level. However in our warehouse we store the data with a Date Range and insert a new range record only when the Exchange Rate changes for a particular From Currency, To Currency and Rate Type. Below are the main logical steps that we employ in this process.
- (Incremental Flow only) – Cleanup the data in W_EXCH_RATE_G.
- Delete the records which have Start Date > minimum conversion date
- Update the End Date of the existing records.
- Compress the daily data from GL_DAILY_RATES table into Range Records. Incremental map uses $$XRATE_UPD_NUM_DAY as an extra parameter.
- Generate Previous Rate, Previous Date and Next Date for each of the Daily record from the OLTP.
- Filter out the records which have Conversion Rate same as Previous Rates or if the Conversion Date lies within a single day range.
- Mark the records as ‘Keep’ and ‘Filter’ and also get the final End Date for the single Range record (Unique Combination of From Date, To Date, Rate and Conversion Date).
- Filter the records marked as ‘Filter’ in the INFA map.
- The above steps will load W_EXCH_RATE_GS. Step 0 updates/deletes W_EXCH_RATE_G directly.
- SIL map will then insert/update the GS data into W_EXCH_RATE_G.
These steps convert the daily records in GL_DAILY_RATES to Range records in W_EXCH_RATE_G.
We do not need such special logic for loading W_GLOBAL_EXCH_RATE_G. This is a table where we store data at a Daily Granular Level. However we need to pivot the data because the data present in multiple rows in source tables needs to be stored in different columns of the same row in DW. We use GROUP BY and CASE logic to achieve this.
Fusion: Fusion has extraction logic very similar to EBS. The only difference is that the Cleanup logic that was mentioned in step 0 above does not use $$XRATE_UPD_NUM_DAY parameter. In Fusion we bring all the Exchange Rates in Incremental as well and do the cleanup. The SIL then takes care of Insert/Updates accordingly.
PeopleSoft:PeopleSoft does not have From Date and To Date explicitly in the Source tables. Let’s look at an example. Please note that this is achieved from PS1 onwards only.
1 Jan 2010 – USD to INR – 45
31 Jan 2010 – USD to INR – 46
PSFT stores records in above fashion. This means that Exchange Rate of 45 for USD to INR is applicable for 1 Jan 2010 to 30 Jan 2010. We need to store data in this fashion in DW.
Also PSFT has Exchange Rate stored as RATE_MULT and RATE_DIV. We need to do a RATE_MULT/RATE_DIV to get the correct Exchange Rate.
We generate From Date and To Date while extracting data from source and this has certain assumptions:
If a record gets updated/inserted in the source, it will be extracted in incremental. Also if this updated/inserted record is between other dates, then we also extract the preceding and succeeding records (based on dates) of this record. This is required because we need to generate a range record and we have 3 records whose ranges have changed. Taking the same example as above, if there is a new record which gets inserted on 15 Jan 2010; the new ranges are 1 Jan to 14 Jan, 15 Jan to 30 Jan and 31 Jan to Next available date. Even though 1 Jan record and 31 Jan have not changed, we will still extract them because the range is affected.
Similar logic is used for Global Exchange Rate Extraction. We create the Range records and get it into a Temporary table. Then we join to Day Dimension, create individual records and pivot the data to get the 5 Global Exchange Rates for each From Currency, Date and Rate Type.
Siebel: Siebel Facts are dependent on Global Exchange Rates heavily and almost none of them really use individual Exchange Rates. In other words, W_GLOBAL_EXCH_RATE_G is the main table used in Siebel from PS1 release onwards.
As of January 2002, the Euro Triangulation method for converting between currencies belonging to EMU members is not needed for present and future currency exchanges. However, the method is still available in Siebel applications, as are the old currencies, so that historical data can be maintained accurately. The following description applies only to historical data needing conversion prior to the 2002 switch to the Euro for the EMU member countries. If a country is a member of the European Monetary Union (EMU), you should convert its currency to other currencies through the Euro. This is called triangulation, and it is used whenever either currency being converted has EMU Triangulation checked.
Due to this, there are multiple extraction flows in SEBL ie. EUR to EMU, EUR to NonEMU, EUR to DMC and so on. We load W_EXCH_RATE_G through multiple flows with these data. This has been kept same as previous versions of OBIA.
W_GLOBAL_EXCH_RATE_G being a new table does not have such needs. However SEBL does not have From Date and To Date columns in the Source tables similar to PSFT. We use similar extraction logic as explained in PSFT section for SEBL as well.
What if all 5 Global Currencies configured are same?
As mentioned in previous sections, from PS1 onwards we store Global Exchange Rates in W_GLOBAL_EXCH_RATE_G table. The extraction logic for this table involves Pivoting data from multiple rows into a single row with 5 Global Exchange Rates in 5 columns. As mentioned in previous sections, we use CASE and GROUP BY functions to achieve this. This approach poses a unique problem when all the 5 Global Currencies Chosen are same. For example – If the user configures all 5 Global Currencies as ‘USD’ then the extract logic will not be able to generate a record for From Currency=USD. This is because, not all Source Systems will have a USD->USD conversion record.
We have _Generated mappings to take care of this case. We generate a record with Conversion Rate=1 for such cases.
Before PS1, we had a Mapplet for Currency Conversions. In PS1, we only have reusable Lookups- LKP_W_EXCH_RATE_G and LKP_W_GLOBAL_EXCH_RATE_G. These lookups have another layer of logic so that all the lookup conditions are met when they are used in various Fact Mappings. Any user who would want to do a LKP on W_EXCH_RATE_G or W_GLOBAL_EXCH_RATE_G should and must use these Lookups. A direct join or Lookup on the tables might lead to wrong data being returned.
Changing Currency preferences in the Dashboard:
In the 796x series, all amount metrics in OBIA were showing the Global1 amount. The customer needed to change the metric definitions to show them in another Currency preference. Project Analytics started supporting currency preferences since 7.9.6 release though, and it published a Tech note for other module customers to add toggling between currency preferences to the solution.
List of Currency Preferences
Starting from 11.1.1.x release, the BI Platform added a new feature to support multiple currencies. The new session variable (PREFERRED_CURRENCY) is populated through a newly introduced currency prompt. This prompt can take its values from the xml file: userpref_currencies_OBIA.xml, which is hosted in the BI Server installation folder, under :< home>\instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1\userpref_currencies.xml
This file contains the list of currency preferences, like“Local Currency”, “Global Currency 1”,…which customers can also rename to give them more meaningful business names. There are two options for showing the list of currency preferences to the user in the dashboard: Static and Dynamic. In Static mode, all users will see the full list as in the user preference currencies file. In the Dynamic mode, the list shown in the currency prompt drop down is a result of a dynamic query specified in the same file. Customers can build some security into the rpd, so the list of currency preferences will be based on the user roles…BI Applications built a subject area: “Dynamic Currency Preference” to run this query, and give every user only the list of currency preferences required by his application roles.
Adding Currency to an Amount Field
When the user selects one of the items from the currency prompt, all the amounts in that page will show in the Currency corresponding to that preference. For example, if the user selects “Global Currency1” from the prompt, all data will be showing in Global Currency 1 as specified in the Configuration Manager. If the user select “Local Currency”, all amount fields will show in the Currency of the Business Unit selected in the BU filter of the same page. If there is no particular Business Unit selected in that filter, and the data selected by the query contains amounts in more than one currency (for example one BU has USD as a functional currency, the other has EUR as functional currency), then subtotals will not be available (cannot add USD and EUR amounts in one field), and depending on the set up (see next paragraph), the user may receive an error.
There are two ways to add the Currency field to an amount metric:
- In the form of currency code, like USD, EUR…For this the user needs to add the field “Apps Common Currency Code” to the report. This field is in every subject area, usually under the table “Currency Tag” or “Currency Code”…
- In the form of currency symbol ($ for USD, € for EUR,…) For this, the user needs to format the amount metrics in the report as a currency column, by specifying the currency tag column in the Column Properties option in Column Actions drop down list. Typically this column should be the “BI Common Currency Code” available in every subject area.
- Select Column Properties option in the Edit list of a metric.
- In the Data Format tab, select Custom as Treat Number As.
- Enter the following syntax under Custom Number Format: [$:currencyTagColumn=Subjectarea.table.column] Where Column is the “BI Common Currency Code” defined to take the currency code value based on the currency preference chosen by the user in the Currency preference prompt.
Thursday Sep 13, 2012
By Saurabh Verma on Sep 13, 2012
Welcome to Oracle BI applications blog! This blog will talk about various features, general roadmap, description of functionality and implementation steps related to Oracle BI applications. In the first post we start with an overview of the BI apps and will delve deeper into some of the topics below in the upcoming weeks and months. If there are other topics you would like us to talk about, pl feel free to provide feedback on that.
The Oracle BI applications are a set of pre-built applications that enable pervasive BI by providing role-based insight for each functional area, including sales, service, marketing, contact center, finance, supplier/supply chain, HR/workforce, and executive management. For example, Sales Analytics includes role-based applications for sales executives, sales management, as well as front-line sales reps, each of whom have different needs.
The applications integrate and transform data from a range of enterprise sources—including Siebel, Oracle, PeopleSoft, SAP, and others—into actionable intelligence for each business function and user role.
This blog starts with the key benefits and characteristics of Oracle BI applications. In a series of subsequent blogs, each of these points will be explained in detail.
Why BI apps?
- Demonstrate the value of BI to a business user, show reports / dashboards / model that can answer their business questions as part of the sales cycle.
- Demonstrate technical feasibility of BI project and significantly lower risk and improve success
- Build Vs Buy benefit
- Don’t have to start with a blank sheet of paper.
- Help consolidate disparate systems
- Data integration in M&A situations
- Insulate BI consumers from changes in the OLTP
- Present OLTP data and highlight issues of poor data / missing data – and improve data quality and accuracy
BI apps support prebuilt integrations against leading ERP sources: Fusion Applications, E- Business Suite, Peoplesoft, JD Edwards, Siebel, SAP
- Co-developed with inputs from functional experts in BI and Applications teams.
- Out of the box dimensional model to source model mappings
- Multi source and Multi Instance support
Rich Data Model
BI apps have a very rich dimensionsal data model built over 10 years that incorporates best practises from BI modeling perspective as well as reflect the source system complexities
- Conformed dimensional model across all business subject areas allows cross functional reporting, e.g. customer / supplier 360
- Over 360 fact tables across 7 product areas
- CRM – 145, SCM – 47, Financials – 28, Procurement – 20, HCM – 27, Projects – 18, Campus Solutions – 21, PLM - 56
- Supported by 300 physical dimensions
- Support for extensive calendars; Gregorian, enterprise and ledger based
- Conformed data model and metrics for real time vs warehouse based reporting
- Multi-tenant enabled
Extensive BI related transformations
BI apps ETL and data integration support various transformations required for dimensional models and reporting requirements. All these have been distilled into common patterns and abstracted logic which can be readily reused across different modules
- Slowly Changing Dimension support
- Hierarchy flattening support
- Row / Column Hybrid Hierarchy Flattening
- As Is vs. As Was hierarchy support
- Currency Conversion :- Support for 3 corporate, CRM, ledger and transaction currencies
- UOM conversion
- Internationalization / Localization
- Dynamic Data translations
- Code standardization (Domains)
- Historical Snapshots
- Cycle and process lifecycle computations
- Balance Facts
- Equalization of GL accounting chartfields/segments
- Standardized values for categorizing GL accounts
- Reconciliation between GL and subledgers to track accounted/transferred/posted transactions to GL
- Materialization of data only available through costly and complex APIs e.g. Fusion Payroll, EBS / Fusion Accruals
- Complex event Interpretation of source data – E.g.
- What constitutes a transfer
- Deriving supervisors via position hierarchy
- Deriving primary assignment in PSFT
- Categorizing and transposition to measures of Payroll Balances to specific metrics to support side by side comparison of measures of for example Fixed Salary, Variable Salary, Tax, Bonus, Overtime Payments.
- Counting of Events – E.g. converting events to fact counters so that for example the number of hires can easily be added up and compared alongside the total transfers and terminations.
- Multi pass processing of multiple sources e.g. headcount, salary, promotion, performance to allow side to side comparison.
- Adding value to data to aid analysis through banding, additional domain classifications and groupings to allow higher level analytical reporting and data discovery
- Calculation of complex measures examples:
- COGs, DSO, DPO, Inventory turns etc
- Transfers within a Hierarchy or out of / into a hierarchy relative to view point in hierarchy.
Configurability and Extensibility support
BI apps offer support for extensibility for various entities as automated extensibility or part of extension methodology
- Key Flex fields and Descriptive Flex support
- Extensible attribute support (JDE)
- Conformed Domains
BI apps offer a modular adapter architecture which allows support of multiple product lines into a single conformed model
- Multi Source
- Multi Technology
- Orchestration – creates load plan taking into account task dependencies and customers deployment to generate a plan based on a customers of multiple complex etl tasks
- Plan optimization allowing parallel ETL tasks
- Oracle: Bit map indexes and partition management
- High availability support
- Follow the sun support
BI apps support several utilities / capabilities that help with overall total cost of ownership and ensure a rapid implementation
- Improved cost of ownership – lower cost to deploy
- On-going support for new versions of the source application
- Task based setups flows
- Data Lineage
- Functional setup performed in Web UI by Functional person
- Test to Production support
BI apps support both data and object security enabling implementations to quickly configure the application as per the reporting security needs
- Fine grain object security at report / dashboard and presentation catalog level
- Data Security integration with source systems
- Extensible to support external data security rules
Extensive Set of KPIs
- Over 7000 base and derived metrics across all modules
- Time series calculations (YoY, % growth etc)
- Common Currency and UOM reporting
- Cross subject area KPIs (analyzing HR vs GL data, drill from GL to AP/AR, etc)
Prebuilt reports and dashboards
- 3000+ prebuilt reports supporting a large number of industries
- Hundreds of role based dashboards
- Dynamic currency conversion at dashboard level
Highly tuned Performance
The BI apps have been tuned over the years for both a very performant ETL and dashboard performance. The applications use best practises and advanced database features to enable the best possible performance.
- Optimized data model for BI and analytic queries
- Prebuilt aggregates& the ability for customers to create their own aggregates easily on warehouse facts allows for scalable end user performance
- Incremental extracts and loads
- Incremental Aggregate build
- Automatic table index and statistics management
- Parallel ETL loads
- Source system deletes handling
- Low latency extract with Golden Gate
- Micro ETL support
- Bitmap Indexes
- Partitioning support
- Modularized deployment, start small and add other subject areas seamlessly
Source Specfic Staging and Real Time Schema
- Support for source specific operational reporting schema for EBS, PSFT, Siebel and JDE
The BI apps also allow for integration with source systems as well as other applications that provide value add through BI and enable BI consumption during operational decision making
- Embedded dashboards for Fusion, EBS and Siebel applications
- Action Link support
- Marketing Segmentation
- Sales Predictor Dashboard
- Territory Management
The BI apps data integration choices include support for loading extenral data
- External data enrichment choices : UNSPSC, Item class etc. Extensible
- Spend Classification
Broad Deployment Choices
- Exalytics support
- Databases : Oracle, Exadata, Teradata, DB2, MSSQL
- ETL tool of choice : ODI (coming), Informatica
Extensible and Customizable
- Extensible architecture and Methodology to add custom and external content
- Upgradable across releases
Thanks for reading a long post, and be on the lookout for future posts. We will look forward to your valuable feedback on these topics as well as suggestions on what other topics would you like us to cover.
- Snapshot Facts in OBIA (3)
- Snapshot Facts in OBIA (2)
- Snapshot Facts in OBIA (1)
- BI apps 22.214.171.124.1 Cumulative Patch 1 is available now
- How to Implement Object Security in Project Analytics in OBIA 126.96.36.199.1
- Notes for implementing Universal adapter for OBIA Project analytics
- How to include Fusion DFFs Into OBIA 188.8.131.52.1 In Premise Data Warehouse
- BIAPPS List of ODI Variables
- How to Compare RPDs
- Configuring Enterprise Calendar for Oracle BI Apps Time Dimension