In this article we will look into an interesting feature of creating a BI Report using a static CSV file.
Sometimes, we might get some input file from vendors and we need to fetch other details of employees within that list of employees or validate some fields. And this feature will be handy in such cases.
- We can fetch additional details for employees from the files sent by Vendors.
- We can prepare HDL DAT file from the input file shared by the client. We can get additional fields and create etext template to get the HDL DAT file.
- We can import external CSV data with different delimiters (comma, tab, pipe, semicolon) and show that info on the existing report or a new report.
Without delay, let’s get into details on how to do it.
Creating Data Model using Static CSV file
First let’s consider we have a sample CSV file with pipe delimiter something like below with the first field being person number and other fields having something else.
120|Manager|Consulting 519|Test Assistant|Support 3672|Technical Lead|Quality 5149|Assistant Manager|Support 4626|Consultant|Consulting
Now, lets try to import this file to Data Model and map the person number to another SQL based data set and fetch employee’s Name.
Let’s Navigate to BI Catalog and select New Data Model.
On the next screen, give some name to Data Set and select Data Source as Local and click the upload button to upload the local CSV file.
if the first row has header with field names, then check the box which will skip the header row from showing up on the data.
We can use CSV file with 4 delimiters (pipe | OR semicolon ; OR Tab OR Comma ,)
Browse for the local csv file and click upload.
Once we upload and select the delimiter as pipe and click ok the data set will get saved like below:
Click on Data and view data
Now, lets, go back to Diagram tab and add a new SQL based data set to pull the employees names using below query.
select papf.person_number, ppnf.full_name from per_all_people_f papf, per_person_names_f ppnf where papf.person_id = ppnf.person_id and sysdate between papf.effective_start_date and papf.effective_end_date and ppnf.name_type = 'GLOBAL' and sysdate between ppnf.effective_start_date and ppnf.effective_end_date
Now, let’s map the Column1 to Person Number field so that we get the relevant person names. Drag and Drop the Column1 on to Person Number field and we can check the mapping.
Now go to data tab and view the data.
We can see that we are able to get the Name from the SQL data set which is mapped from column1 from the static input file.
Create an online report layout and drag fields from two datasets like below:
Final Output will look like:
We can create BI template as per the requirement based on the requirement using rtf/etext if HDL DAT file is the requirement.
Download Files:You do not have access to this VIP only Content. Please subscribe to VIP Plan to check this section.
Top 10 Popular Posts from our Knowledgebase
- DataFusing FREE Cloud based SQL Developer like tool for Oracle Cloud
- Core HR Entity Relationship Diagram (ERD) for HCM Cloud
- Most Frequently used Document IDs from Oracle Support
- FREE Oracle Training & Accreditation with Oracle Learning Explorer
- How HDL Transformation Formula works with ElementEntry Sample
- FREE Oracle Fusion Demo Instance (VISION) Access
- Top 10 FREE useful tools for Fusion Consultants
- Learning Fusion HCM Technical Skillset
- Sample HCM Data Loader (HDL) files for all Business Objects
- Most frequently used Tables list in Fusion HCM