Infosys’ blog on industry solutions, trends, business process transformation and global implementation in Oracle.

February 18, 2018

Bring the power of Excel to Oracle EBS with Web ADI

Very often we come across business users keeping an Excel sheet beside them and keying in the data into Oracle EBS. If you ask them about automating the process, most often the alternate option that they are  aware of is sharing the file in a predefined csv format to IT support and having them upload it. But what they don't like here is that, any errors will have to be shared with them by IT support and the time and effort involved in the initial upload and the error correction is significant. They feel the effort involved is not really worth it, and they are better off keying this data manually! It is very surprising to see how technology such as Web ADI (Oracle Web Applications Desktop Integrator) is not more commonly used to automate such manual data entry when you already have the data in an Excel or other documents. Most of the time customers hire temps or interns to key in this high volume data into the application.

Without going deep into the architecture of Web ADI, I will put Web ADI as an EBS capability that enables us to create Excel sheets that will automatically connect to EBS and perform the function that it is configured to do. When Web ADI is implemented, the Excel downloaded from EBS will have a new menu for Oracle.

Oracle_Web_ADI_Excel_Menu.jpg

In my experience so far, whenever we have demonstrated the capabilities of Web ADI, the customer response has been enthusiastic . The features that excite them the most -

  • You don't have to login to EBS or navigate/download the Excel every time. You download the Excel once; you can access it by just opening it from desktop. If EBS SSO (Single Sign On) is enabled it automatically understands who you are, if not, a pop from Excel, requests you to login.
  •  You have got all the features of Excel at your disposal - You can drag a cell, copy-paste data, use formulas to derive data, filter for certain values, have your own macros.
  •  You have the option to selectively upload only certain rows from the Excel.
  •  If the solution involves launching a concurrent program in the background, you can monitor the status from the excel itself, without having to login to EBS.
  • Business validations can be built into the Excel to show any issues to the user in the Excel itself. The user can chose to correct it or not upload the error record and proceed with others.
Validation_Error_In_Excel.jpg
  • Can work with the Excel offline. Of course, the user needs to be connected to validate or upload the data
  • The Excel can be configured for List of Values (LoV), double clicking on the Excel cell will open a HTML page that will give the capability  search for valid values and select it.
List_Of_Values.jpg
  • The excel sheet can also be configured to have drop down values.
Drop_Down.jpg
  • When a user wants a report in an Excel periodically, we can have the Web ADI configured for reporting only. The user just needs to open the Excel from desktop and refresh

Web ADI being a module within Oracle EBS, it inherits all the security framework of EBS. When the user is downloading or uploading data from Excel, they are doing it as an EBS user from a valid responsibility. User and responsibility specific restrictions can be implemented. For example, data can only be validated by a user/responsibility and uploaded by a different user/responsibility.

There are of course some areas where Web ADI is not a good option. Web ADI is only for user interface (UI) with one master-child relationship, with one header and optionally multiple child records. If we are looking for data upload with the UI having more than one master-child, it may not be fit for purpose. For example, Web ADI is good to mass update lines for a specific Purchasing Contract, but not fit for a requirement where we need to update the lines, shipments and distributions of a Purchase Order. While these features can be technically implemented, the solution may not be essentially user friendly. Moreover, Web ADI should not be seen as an alternate UI or technology for a Forms or OAF based UI, since it is not possible to dynamically enable/disable columns, show additional info/warning/error message at user key strokes at field level or move control dynamically to different fields in the Excel.

That said, here are the best opportunities where we can use Web ADI effectively

  • Where data needs to created/updated in bulk, where the data may already be there in Excel, etc.
  • Where we need the capability to work with the data offline and sync it up with server when online.
  • Where we may have to review a set of data and correct it - Web ADI can download the data that qualifies the condition into Excel. The user can make the changes and upload back.
  • Existing concurrent programs based solutions where a flat file is shared with the IT support team and the support team uploads it and shares the error records back, on which corrections are made and shared again to the support team for upload.
  • Reports that you want to refresh frequently.

Excel being Excel, most users will be comfortable with it and will not need any special training to use it. Web ADI being a technology module within EBS does not need any additional license. If you already have EBS, you can apply the required patches and start using it. If you look at it the other way, it is a module that the customer has already paid for, and not using it! Identifying the opportunities to implement the Web ADI based Excel solution can be a very good idea, that everyone on Oracle EBS should consider exploring for a better Return on Investment.

February 9, 2018

Getting Started with People soft Campus Solution

Overview on PeopleSoft Application

·         PeopleSoft is a comprehensive, multi-tasking ERP system

·         Various domains e.g. FSCM, HRMS, Campus solutions are integrated in the product

·         Flexibility in decision- making is enhanced due to integration of data in between different domains in PeopleSoft

·         Reporting can be in summarized and detailed analysis depending upon the requirement from single domain or consolidation of two or more domains

·         Robust facility for audit trail enables management to cater for user login transactions creating accountability across organizations

·         Reports and data can be synchronized for different accounting periods

Campus Solutions (CS 9.0, 9.1,9.2) Business Processes:

·         Student Management:

 

o   Recruit Prospects

o   Admin Students

o   Enroll in Classes

o   Advise & Transition Students

o   Manage Financial Services

o   Manage campus services

·         Academic Management

o   Schedule courses & Resources

o   Plan Academic Programs

o   Teach courses

·         Institutional Advancement

·         Campus Community

·         Personal Information Management

·         Maintaining Biography/Demographic/ Health Data

·         Maintaining Health Data

·         Maintaining Identification Data

·         Maintaining Participation Data

·         Organization Data Management

·         Maintaining Organization Data

·         Maintaining Event Data

·         Maintaining Committees

·         The 3Cs

o   Communications

o   Checklists

o   Comments

Continue reading " Getting Started with People soft Campus Solution " »

February 7, 2018

Data Visualization in OBIEE 12c

Backdrop

Data visualization is the presentation of data in a pictorial or a graphical format which takes care of a complex problem that could be easily overlooked and makes things easier using graphs, patterns, and design. It is useful in converting real-time data into rich reports and visualizations. Effective data visualization should be informative, efficient, appealing, and in some cases interactive and predictive.

Continue reading " Data Visualization in OBIEE 12c " »

February 1, 2018

Introduction to Oracle Analytics Cloud - OAC

In this blog, I will cover what is OAC and Why OAC. In my next, will deep dive into features and stuff.

OAC is Oracle Analytics Cloud, a PaaS service offered by Oracle. Its launched in 2014. Around 1400+ customers are already using as of May 2017.

As we all know BICS is already in market and it is offered under SaaS by Oracle. So what's new and why we need OAC is what we understand next in this blog.

BICS is an Oracle managed and OAC is Customer/Partner managed - major change in cloud offerings by Oracle.

Below are three offerings by Oracle in OAC

1)       Standard Edition (Data Visualization + Essbase)

2)       Data Lake Edition (Standard + Big Data Source)

3)       Enterprise Edition (Data Lake + Essbase Enterprise)

Features

Standard

Data Lake

Enterprise

DataVisualizationCloudService - DVCS

X

X

X

Oracle Smart View

X

X

X

Oracle Essbase Standard

X

X

X

Big Data Source

 

X

X

Data Modeling

 

 

X

Oracle Essbase Enterprise

 

 

X

Oracle Day by Day

 

 

X

 

Now we know various offering in OAC, lets understand difference between BICS and OAC

Difference between BICS and OAC

Features

BICS and DVCS today

OAC

Automatic Backup

Oracle

Client/Partner

Monitoring

Oracle

Client/Partner

Patch and Upgrade

Oracle

Client/Partner

CPU & Memory

Oracle

Client/Partner can decide and control

Server Access & Config

Oracle

Client/Partner can access through SSH

Network & Security Config

Limited Support

Client/Partner will have complete control to access network

DB Dependency

Have to buy Schema as Service (BIS50)

Customer needs to provide at least 10 CPU DBaaS

IaaS Dependency

None

Client is required to buy some IaaS storage

 

Various sources supported by OAC

SaaS/PaaS ->

OAC Fig1.JPG

Database - >

OAC Fig2.JPG

BIG Data - >

OAC Fig3_new.JPG

Generic ->

OAC Fig4.JPG



To get start with OAC instance set up, we need below as prerequisite

OSCS - Oracle Storage Cloud Service - Backup, restore, archive

Block Storage - Oracle IaaS service offering for flexible and scalable compute and block storage

DBCS -Data Base Cloud Service - DbaaS service offered by Oracle

Why OAC? What's new in OAC?    

OAC Fig5_new1.JPG

















Will deep dive into features and advances features in my next blogs on OAC. Thanks.

January 31, 2018

Configuring Task Flow dependency and Teradata ODBC connection in Informatica cloud

In this blog, I am going to cover Configuring dependency between the two task flows and configuring ODBC connection for Teradata relational Database in Informatica cloud.

This blog will help to run one task flow when the dependent task flow completed successfully and create an ODBC connection to connect Teradata Relational Database which can be used in Mappings/Data Synchronization/Data Replication jobs to load/retrieve the data.

Configuring Task Flow dependency

In real time, we would require the taskflows to run in certain order or in other words, we should trigger a task flow (taskflow2) after the completion of a task flow (taskflow2). This can be achieved by runajobcli package. This package helps to triggering a taskflow from command prompt (API call). 

Steps to configure the package and trigger the task flow

 

Step 1: Ask Informatica cloud support to activate the Runajob package on the secure agent or ORG if it is not already available.

Step 2: Configure restenv.properties file present in below path

Path: C:\Program Files\Informatica Cloud Secure Agent\downloads\package-runAJobCli.2\package\

 

Step 3: Give full control access to the Informatica cloud installed folder to the user (secure agent installed user).

Step 4: Try to call one dummy task from command prompt by going to the runajob package path.

Ex: C:\Program Files\Informatica Cloud Secure Agent\downloads\package-runAJobCli.2\package\

    cli.bat runAJobCli -n W_ETL_RUN_D_AMDOCS_End_MCT -t MTT

Step 5: If you get 'java internal external command not found', then add java path environment variable in 'My computer'->Advanced properties

EX: Add- C:\Program Files\Informatica Cloud Secure Agent\jre\bin(C or D directory)  

Step 6: Then try to call the same command from post processing command of the MCT. If you get the java error in the log, then add full java path to CLI.bat file

Example CLI.bat: @echo off

pushd %~dp0"Informatica Cloud Secure Agent\jre\bin\java.exe" -cp .;runAJobCli.jar;lib\* com.informatica.saas.utilities.plugins.RunAJobPlugin %* popd @echo on

or

@echo off

pushd %~dp0"D:\Apps\Informatica Cloud Secure Agent\jre\bin\java.exe" -cp .;runAJob

 

Configuring Teradata ODBC Connection

To establish Teradata ODBC connection in Informatica cloud you would require below utilities which can downloaded from below URL

https://downloads.teradata.com/download/tools/teradata-tools-and-utilities-windows-installation-package


Once you have downloaded follow the below steps

Step 1: Run the installer (.exe) from the Downloaded directory and Choose the Language and click on 'Next'.

Step 2: Installation Wizard will be opened. Click on 'Next'.

 

Step 3: Accept the License Agreement by clicking 'I accept the terms in the license agreement' and click on 'Next'.

Step 4: After selecting destination folder , Click on 'Next'.

Step 5: Do check 'ODBC Driver for Teradata *' and Click on 'Install'.

Step 6: Click on 'Finish'.

Step 7: Run the installed one more application file from the following path :

EX:D:/TeradataODBC_windows_indep.15.10.17.00/TeradataODBC/x64/SharedICU-64bit

Step 8: Click on 'Next'.

Step 9: Accept the License Agreement by clicking 'I accept the terms in the license agreement' and click on 'Next'.

Step 10: Click on 'Finish'.

Step 11: Run one more installed application file from the following path

EX: D:/TeradataODBC_windows_indep.15.10.17.00/TeradataODBC/x64\TeraGSS-64bit

 

Click on 'Next'.

Step 12: Select 'Modify' and click on 'Next'.

 

 

Step 13: Select the required feature and click on 'Next'.

Step 14: Click on 'Install' to start installation.

Step 15: Under System DSN, set the ODBC Driver Setup for Teradata Database by providing user Name and PWD And click on 'OK'.

Step 16: Test the ODBC connection in command prompt.

Step 17: Using the ODBC connection you've created, you can create a 'New connection' in Informatica Cloud.

Step 18: If test connection fail, restart the secure agent and try again.

Continue reading " " »

January 30, 2018

AWS Offerings for Dummies

What is Cloud Computing:

Cloud Computing is the on-demand delivery of IT resources and applications via the Internet with pay-as-you-go pricing.


Some of the popular Vendors for Cloud computing are listed below

Amazon: AWS, Microsoft: Azure, Oracle: Oracle Cloud, IBM: IBM Cloud

 

What is Amazon Web Services-AWS :

·         a) Provides On-Demand Cloud Computing Platforms.

·         b) Services provided towards Big corporations, SMBs and public services agencies etc

Why AWS :

The below table quickly describes on need for moving toward cloud.

AWS5.JPG
















AWS10.jpg



Data Integration in Einstein Analytics

This Blog explains various ways to integrate and load data into Einstein Analytics

Data Integration is one of key aspects of BI Tool, Einstein Analytics does exceedingly well in this department with seamless integration, as it doesn't require data to in particular format (like star or snowflakes schema).

Unlike the other BI tools, Einstein Analytics stores the data itself in the cloud not just the metadata. Hence, we need to refresh data time to time. In order to ensure we are working or using the latest data. Storing the data along with inverted index (the way data is stored in Einstein Analytics) boost the performance of the tool.

The options available to load data into Einstein Analytics are

  1. Salesforce: The salesforce objects can be directly loaded into Einstein Analytics using dataset builder or dataflow.
  2. CSV: Csv files can be directly uploaded into Einstein Analytics.
  3. Informatica Rev: The data from external data source that can be loaded into Einstein Analytics using Informatica Rev.
  4. ETL Connector: ETL tools like Informatica cloud, mulesoft, Boomi, snaplogic, etc have connector for Einstein analytics using which you can load data into Einstein analytics.

Screenshot taken from Einstein Org


The data integration can be done at

Dataflow- Dataflow view in Einstein Analytics has etl transformation like Extract, Augment(Join), Append(Union), Slicer, etc., One can leverage this option to integrate data coming from different data sources. Dataflow is also used for data refresh in Einstein Analytics.

Using ETL Connectors- (Eg: Informatica Cloud, the data can be integrated at informatica cloud and integrated data can be loaded into Einstein analytics using "salesforce analytics" connectors.

Apart from this you can also establish connection between datasets at dashboard level using bindings, connect data sources and SAQL mode.
  • Binding: The changes in one step/widget triggers change in other step/widget in a dashboard, this is achieved using binding if the step(s)/widget(s) are created using 2 different datasets.
  • Connect Data Source: This option is available at dashboard level, using which you can connected two columns from two different datasets.
  •        SAQL:  You can write a SAQL query to fetch data from two different datasets at dashboard level.

January 29, 2018

Excel to App - a journey beckoning finance world to the future of technology? A view from the Oracle Analytics Cloud Consultants' Lenses

Around last thanksgiving there were two interesting articles in Wall Street Journal both concerning the finance world, one mentioned the diktat given out by CFOs across companies to get rid of the spreadsheet-based finance analysis and reporting within the finance organizations, and the other from the finance analysts and planners rebuking that the Microsoft Excel continues to be their one powerful tool for analysis and reporting of financial numbers.

For an Oracle BI-EPM consultant like me implementing Essbase, planning and other technology solutions predominantly for Finance organizations of my clients both articles throw an interesting perspective on the transition that is imminent, from a conservative macro based analysis to the futuristic app based analysis, and I wanted to write about the possibilities that organizations can draw a balancing line with Oracle's latest offering in the cloud to ensure none of the goodies of spreadsheets are lost and at the same time an organization can plan to have a nimble, and efficient solution that can bring value to the CFOs table.

In the first article "Stop Using Excel, Finance Chiefs Tell Staff.", Adobe Inc.'s finance chief Mark Garrett says that it takes days for his team to put together the job fulfilment report across the organization and to get the numbers to analyze the impact of salary on budgeting. Garrett wants his finance staff to focus more on the analysis than spending time on getting that data from disparate systems into the spreadsheets.

As a direct opposite to this view, the second article carried the view of Finance professionals who say "You'll Have to Pry Excel Out of Their Cold, Dead Hands". This shows the comfort level of maintaining and analyzing huge amounts of data for most of the professionals in finance as well as analyzing the data for insights following cumbersome ways without utilizing the features that modern technology solutions provides. The article opines that finance analysts spend a good amount of time trying to develop a Point of View (PoV) from the spreadsheet and carry a sense of pride around it and take ownership of the analysis.

There is technology supporting Spreadsheet

In FP&A systems specifically Oracle Essbase has gained so much user base due to its ability to combine the best of the worlds in terms of executing complex calculations using hierarchical data model and be able to showcase specific Points of View (PoVs) using Excel Add-in - SmartView. This tool has so much flexibility in the hands of the end users that it enables them to actually not worry about data or its integrity and actually gives ample flexibility for end users to perform business analyses needed for their organizations.

Excel is my favorite 

Despite the fact that a small formula error could lead to large differences in the value reported through spreadsheets, excel remains the favorite choice of analysts. One of the reasons that I think the reason behind this strong conviction is that all the business logic applied and all the calculations are visible to the analysts right in front and they do not get any surprises to say that logic was applied but the value came out wrong. Whereas, in a system there is a set standard as far as data entry, business logic configuration, and expected output. If junk is fed to the system, junk comes out. So, this encapsulation in a system in terms of backend data processing becomes a bigger hindrance in embracing such a system by financial analysts.

Oracle Analytics Cloud (OAC)

Essbase in Cloud is perfectly suited to give best of the two worlds in terms of calculation performance and flexibility to end users as well as be able to define business calculations and dimensionality by the actual end users / consultants. It gives power in the hands of the right people. It does not stop at that, OAC perfectly aligns to the technological future state that top level executives across organizations are looking forward to, a tech savvy futuristic state where finance data is available along with insights and commentary at the "Hi" of a voice command and seamlessly across different types of corporate and personal devices for the financial analysts' of the millennial generation.

Transformation leads to Transition

Oracle Analytics Cloud (OAC) in my opinion will emerge as a fusion arena where it brings the solid features of being a PaaS offering combined with the SaaS features in the FP&A domain. In relation to the existing on cloud applications for financial planning and consolidation (EPBCS & FCCS), OAC comes up with a good packaging option along with PaaS advantages of performance, scalability, and to serve not only the finance community but also the ability to incorporate the needs of corporate reporting across the organization through its DVCS, Data Lake, and BI packages combined with power punching, number crunching Essbase.

What's ahead?

As transformation progresses from on premise to cloud, demand for app based, real time insights with commentary can be enabled through Oracle Mobile applications such as Synopsis, Oracle Mobile HD, and the uber-cool Oracle Day-by-Day is sure to transform the consumption of data from spreadsheets to insights on-the-go through futuristic apps in finance.

January 23, 2018

Oracle Cloud Manufacturing integration with Project Costing.

Oracle Cloud is buzzing now days in ERP market. Most of the organization are looking for cloud transformation. However as the Oracle Cloud is evolving, still there are few functions, integrations are missing and not yet released with latest Oracle cloud R13 release. One of the major Project Portfolio Management sub module missing in Oracle Cloud R13 is Project Manufacturing.

As in Oracle EBS, Project Manufacturing play acute role to integrate Project Manufacturing process with Project Costing. Project manufacturing help to account and integrate Project related manufacturing cost to project costing, to gives complete picture of project cost across the sub-ledger which help for better decision making.

In most of the project based manufacturing organization, Project manufacturing is key sub module without it not possible to complete project manufacturing business process cycle. As Project manufacturing is not yet released by Oracle Cloud, so it's creating gap between manufacturing and project costing process. There is no integration to import project related manufacturing cost from SCM cloud to cloud project costing. Which is becoming an obstacle for project manufacturing organization to move on Oracle Cloud.

However there are some workaround available to bridge the gap between manufacturing and project costing process. We are going to explore below:

 

1.    Project Transaction Type:

Oracle Cloud allow to create Misc Issue to Project and Misc Receipt from Project transaction. This are standard process provided in Oracle Cloud even as Project Manufacturing is not available.

If it need to transfer a cost on Inventory item purchase to Project then can follow below process.

1.     Create Inventory PO

2.     Receive a PO

3.     Create Misc issue to transfer inventory PO cost to Project

4.     Run Cost accounting

5.     Transfer Cost to Project

Similarly you can perform Misc issue to Project to transfer cost to project for other business process.

We can use this workaround in scenario, where simple project manufacturing business process and less number of project manufacturing transactions get generated.

PJM_New01.jpg


2.   File Based Data Import (FBDI) :

In case of complex project manufacturing business process where the large number of project related manufacturing transaction gets generated, in such scenario it's tedious to keep track and perform manual transaction as "Misc Issue to Project" to transfer cost to Project. In this case FBDI is alternate option.

FBDI template feature helps to import bulk data from third-party system or other Oracle ERP applications or create new transaction data in Oracle Cloud. The FBDI template# Import Project Inventory Costs Imports inventory cost transactions from third-party applications or creates new project inventory cost transactions.

There can be following process to upload the manufacturing data using FBDI Template.

1.     Explore DFF to collect project details for project related manufacturing transaction.

2.     Create an OTBI Report to extract all the eligible project related costed manufacturing transaction, which are not already imported to project. Here Original Transaction Reference field form FBDI template can utilized. It can link with inventory transaction id to avoid duplication of records.

3.     Upload the project manufacturing transaction to FBDI template# Import Project Inventory Costs

4.     Import the FBDI using following scheduled process.

a.     Load Interface File for Import

b.     Import Costs

Interface Table# PJC_TXN_XFACE_STAGE_ALL

PJM_02v1.jpg


This solution can be automate using Web Services.

Summary:

            Oracle cloud SCM to Project costing integration provide the more clarity of project manufacturing cost. It help to bring project manufacturing cost, which gives the complete picture of project cost which helps to better cost tracking and management.


 


January 22, 2018

Vroom Vroom... with the Infosys Automotive Solution

Automotive Industry has been largely ahead of the innovation curve bringing in more technology to the vehicle towards the needs of the market. But all this while, they were challenged working with their own archaic systems. Good customer experience does not just mean good client facing applications but also the entire supply chain has to be customer oriented. Each of the supply chain elements need to be integrated to the get the part/vehicle at the right place at the right time.

Writing in fear of being cliché, an Automotive supply chain has its own complexities which sometimes are not as intuitive to anyone who does not live and breathe this industry. This is where Infosys Automotive Solution has been crafted and perfected over the years, to cater to such specific supply chain challenges.

1.       Supersessions: This is where the rubber hits the road. Almost every leading ERP product in the market has functionality to define supersessions but is it integrated to the entire process?? The answer will be "No".  The complexity does not end with ensuring we are selling always the oldest part of the supply chain but we are also buying the latest part in the chain. Ensuring the End of life and forecasting processes for the product chain are tied together. Even from a pricing perspective, how is the solution going to align the prices along the chain or create incentives for driving buying behavior from dealers?

2.       Referrals: Referral is a concept beyond Promising. How does one ensure we refer to the next nearest warehouse to meet the demand to ensure customer experience does not take a hit? While doing this, how do we keep the logistics cost minimal? How do we ensure we follow the milk run routes or do rate shopping real time? How do we ensure routes are combined together? While traditionally these problems are solved through transport integrations but many have solved this problem too much downstream.

3.       Fair share: When we are in a back order situation in the entire network and there are continuing supply constraints, how do we ensure that the incoming supplies are transferred and is fair shared across all distribution centers. Should it be based on FIFO, or customer priority etc.? These are problems that applications have continued to ignore putting these as execution problems.

4.       Slotting: Warehouse space is real state, how do we ensure that the fastest goods are always picked fastest. Also will the fastest always remain fastest? Or will there be seasonality, trends which we have to cater to. Slotting is ensuring that a continuous proactive process.

5.       Dealer incentives: This is an important part of the supply chain, often ignored. Supply chains are like humans, unless we build in incentives, we won't be able to drive the required behavior from the supply chain constituents.  The big question would be what should we stock at dealer inventories which are client facing and what we stock at middle level warehouses vs central warehouses. At the end of the day, inventory budget and customer service levels will drive the decisions but a dealer would only be concerned about their own profitability.

While we covered some of the nuances of the automotive spare part supply chain, there are many more such niche challenges which are unique and have been built in Infosys Automotive Supply Chain Solution. The solution not only covers the spare part supply chain but also caters to vehicle business as well. Additionally, we have solution flavor catering to Tier1 suppliers as well. To know more, reach out to us at Oracle Modern Supply Chain event at San Jose.@ OracleMSCE @Infy from 29-31st January 2018.

Subscribe to this blog's feed

Follow us on

Blogger Profiles

Infosys on Twitter