Microsoft Excel - working with excel's lookup function

 Working...

📘 Working with Excel Lookup Functions

🔹 Microsoft Excel VLOOKUP() Function

Searches for a value in the first column of a table

Returns a corresponding value from another column

Widely used but has limitations (left-to-right only)

Syntax:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

📌 Best for simple vertical lookups

🔹 Microsoft Excel HLOOKUP() Function 

Searches for a value in the first row of a table

Returns data from rows below

Less commonly used than VLOOKUP

Syntax:

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

📌 Best for horizontally structured data

🔹 Microsoft Excel INDEX() Function 

Returns a value from a cell based on row and column number

Very powerful and flexible

Syntax:

=INDEX(array, row_num, [column_num])

📌 Does not perform lookup by itself

🔹 Microsoft Excel MATCH() Function 

Finds the position of a value in a row or column

Often used with INDEX

Syntax:

=MATCH(lookup_value, lookup_array, [match_type])

📌 Returns position, not the value

🔹 INDEX() + MATCH() Combined

A powerful alternative to VLOOKUP

Can lookup left, right, up, or down

Example:

=INDEX(B2:B10, MATCH(E1, A2:A10, 0))

📌 More flexible and efficient than VLOOKUP

🔹 INDEX() + MATCH() (Advanced) 

Handles:

Dynamic columns

Large datasets

Multiple criteria (with helper columns)

More resilient to column insertions/deletions

📌 Preferred by Excel power users

🔹 Dynamic HLOOKUP() using MATCH() — Advanced

MATCH dynamically identifies the row number

Prevents formula breakage when structure changes

Example:

=HLOOKUP(A1, A1:D10, MATCH("Sales", A1:A10, 0), 0)

📌 Makes HLOOKUP adaptable and robust

✅ Summary Table

OIC - Processing Files Larger Than 1 GB in Oracle Integration Cloud (OIC)

Processing large files (greater than 1 GB) in Oracle Integration Cloud (OIC) requires a well-thought-out design. OIC is an integration and orchestration platform, not a bulk file-processing engine. Therefore, the recommended approach is to offload heavy file handling and let OIC coordinate the process.

This blog covers the most effective and supported patterns to handle large files in OIC reliably.

Core Strategies for Large File Processing

1. Chunking (Segmentation)

File Chunking involves splitting a large file into smaller chunks and processing them one by one.

How It Works

The source system divides the file into smaller parts (for example, 50–100 MB chunks) 

Each chunk is sent separately 

OIC processes or routes each chunk

The target system reassembles the chunks into the original file

Benefits

Reduces memory pressure on OIC

Supports retries at chunk level instead of reprocessing the whole file

Improves reliability over unstable networks

Enables parallel processing if required

OIC Usage Notes

Pass chunk metadata (file name, chunk number, total chunks)

Use asynchronous integrations

Store chunk status externally if reassembly is required

2. Streaming

Streaming processes large files without loading the entire content into memory.

Benefits

Highly memory efficient

Faster processing start time

Suitable for continuous or large data flows

OIC Perspective

OIC does not support full custom streaming logic

Adapters such as FTP Adapter and OCI Object Storage Adapter internally stream files

Avoid mapping large payloads into integration variables

Best practice: Let adapters stream the file; keep OIC orchestration lightweight.

3. Cloud Object Storage (Preferred Pattern)

Using OCI Object Storage (or similar cloud storage) is the recommended approach for handling very large files.

Pattern Overview

Source uploads the file directly to Object Storage

OIC receives only file metadata (object name, bucket, URL)

OIC orchestrates downstream processing

Target system downloads the file directly from Object Storage

Benefits

Designed for multi-GB and TB-scale files

Built-in multipart upload support

High durability and scalability

Keeps OIC payload sizes small

Additional Recommended Approaches

4. OCI SDK / REST API (Multipart Uploads)

To bypass OIC’s payload size limitations:

Use OCI SDKs or OCI REST APIs

Perform multipart uploads directly to Object Storage

OIC only triggers or monitors the process

Benefits

No 1 GB payload limitation

Better performance and reliability

Ideal for custom upload/download logic

5. Oracle Functions for File Handling

Oracle Functions can act as a lightweight processing layer between systems.

Typical Use Case

Oracle Function downloads a large file from source

Performs validations or transformations if needed

Uploads the file to Object Storage using multipart upload

OIC is notified after completion

Benefits

Serverless and scalable

Keeps OIC free from heavy processing

Ideal for pre-processing or post-processing large files

Recommended Architecture

Source System → Object Storage / OCI SDK / Oracle Functions → OIC (Orchestration) → Target System

OIC’s role should be:

Triggering integrations

Orchestrating workflows

Handling errors and notifications

Tracking metadata (not file content)

Conclusion

When dealing with files larger than 1 GB in Oracle Integration Cloud:

✔ Use Object Storage as the central file repository

✔ Prefer chunking or streaming over full payload loads

✔ Leverage OCI SDKs, REST APIs, or Oracle Functions

✔ Keep OIC focused on orchestration, not file movement

Following these patterns ensures scalability, reliability, and compliance with OIC best practices.

OIC - Oracle Integration Cloud (OIC): Design Best Practices & Common Pitfalls to Avoid

Introduction

Oracle Integration Cloud (OIC) is a powerful platform for connecting cloud and on-premises applications. However, poor integration design can lead to performance issues, timeouts, backlogs, and scalability problems.

In this blog, we’ll explore key design best practices recommended by Oracle and highlight common pitfalls you should avoid while designing integrations in Oracle Integration Cloud (OIC 3).

Why Integration Design Matters

A well-designed integration:

• Improves performance and scalability
• Prevents stuck instances and scheduler backlogs
• Reduces timeout and failure scenarios
• Ensures smooth interaction between systems

Poor design, on the other hand, can impact the entire OIC instance, not just a single integration.

Different Best Practices:

1. Create Scheduled Integrations Mindfully

The Problem

Having too many scheduled integrations running at the same time can:

• Exhaust scheduler resources
• Cause delays in execution
• Lead to integrations not starting on time
• Long-running scheduled integrations are especially dangerous.

Best Practices

✔ Avoid clustering multiple schedules at the same time 

✔ Do not create long-running scheduled integrations (for example, those running over 1 hour) 

✔ Spread schedules across different time slots

Recommended Design Pattern

Instead of doing heavy processing inside a scheduled integration:

Convert the scheduled integration into a REST Adapter–triggered integration

Create a lightweight scheduler that:

Triggers the REST-based integration asynchronously

Completes within milliseconds

Benefits

Scheduler starts on time 

Reduced resource contention 

Better scalability and reliability

📌 Tip: Start converting:

Longest-running schedules

High-frequency schedules (every 10 minutes or less)

2. Follow Guidelines for Synchronous Integrations

A synchronous integration should not depend on asynchronous request–response services.

• One-way (fire-and-forget) asynchronous calls are okay.
• Oracle Integration does not support true asynchronous request–response modeling. Since scheduled integrations internally work in an asynchronous manner, calling a scheduled integration from a synchronous flow is a bad design practice.
• If a synchronous integration calls multiple services and the total processing time goes beyond five minutes, OIC may mark it as a stuck thread.

Best Practices

✔ Keep synchronous integrations short and fast

✔ Avoid calling scheduled integrations from synchronous flows

✔ Fire-and-forget (one-way) asynchronous calls are acceptable

3. Design Long-Running Integrations as Asynchronous

The Golden Rule

If an integration takes more than 2 minutes, it should be asynchronous.

Why?

• Client applications may time out
• OIC has server-side timeout limits
• Long waits block system resources

Recommended Approach

✔ Expose long-running processes as asynchronous integrations 

✔ Return immediate acknowledgment 

✔ Continue processing in the background

This improves user experience and system stability.

4. Understand Timeouts in Synchronous Integrations

Timeout Reality in OIC

• Synchronous calls must complete within 300 seconds (5 minutes).

But there’s more:

• Oracle Public Cloud proxy default timeout: 120 seconds
• On-premise proxies/firewalls may have their own timeout values

Key Insight

If multiple layers have timeouts configured, the first timeout reached causes failure.

Best Practice

✔ Avoid chaining multiple synchronous calls

✔ Minimize dependency on slow external systems

✔ Use asynchronous patterns wherever possible

5. Send Data to Third-Party Systems Efficiently

The Challenge

Sending data to multiple third-party systems in parallel without hitting timeout limits.

Recommended Design Pattern: Parent–Child Integrations

Step 1: Parent Integration

Receives and processes data

No outbound calls

Step 2: Child Integrations

Handle individual outbound REST calls

Triggered asynchronously

Communication Options

✔ Asynchronous REST (fire-and-forget)

✔ Publish/Subscribe model (queues, events)

Benefits

Parallel processing

Reduced timeout risks

Better resource utilization

🚫 Avoid making all outbound synchronous calls in a single integration—it can easily exceed the 5-minute limit.

Common Design Pitfalls to Avoid

❌ Too many scheduled integrations at the same time

❌ Long-running synchronous integrations

❌ Using schedules for request-response patterns

❌ Chaining multiple synchronous service calls

❌ Ignoring proxy and firewall timeouts

Conclusion

Designing integrations in Oracle Integration Cloud is not just about making systems talk—it’s about performance, scalability, and reliability.

By following these best practices:

You reduce failures and stuck instance 

Improve scheduler efficiency 

Build integrations that scale with business growth

A small design decision today can prevent major production issues tomorrow.

Reference:

https://docs.oracle.com/en/cloud/paas/application-integration/integrations-user/common-integration-style-pitfalls-and-design-best-practices.html

OIC - Understanding Message Pack Consumption in Oracle Integration Cloud (OIC)

Introduction

Oracle Integration Cloud (OIC) licensing is based on message pack consumption, and misunderstanding how messages are billed can lead to unexpected cost overruns. Each integration design choice—payload size, trigger type, file handling, and invoke responses—directly impacts message usage.

This blog explains:

• How OIC message packs are consumed
• Key billing rules you must consider
• Real-world use cases
• Practical solution steps to optimize message consumption

What Is a Message Pack in OIC?

A message pack represents a predefined number of messages processed by an OIC instance.

Consumption Models

BYOL (Bring Your Own License)

1 pack = 20,000 messages/hour

Non-BYOL

1 pack = 5,000 messages/hour

OIC for SaaS

Metered monthly

1 pack = 1 million messages/month

Each message is calculated in increments of 50 KB.

Core Rules That Impact Message Consumption

1️⃣ Trigger Rule

Every inbound trigger is billed

Payload size is rounded up in 50 KB blocks

Examples:

30 KB → 1 message

70 KB → 2 messages

120 KB → 3 messages

📌 Trigger payload size is the most critical cost driver

2️⃣ Invoke Rule

Invoke request → ❌ Not billed

Invoke response:

≤ 50 KB → ❌ Not billed

50 KB → ✅ Billed in multiples of 50 KB

📌 Large responses from REST, SOAP, BIP, or SaaS APIs increase message usage.

3️⃣ File Server Rule

Applies to scheduled/file-based integrations

Each file > 50 KB is counted as messages

Billing is done in ceil(file_size / 50 KB)

Example:

170 KB file → ceil(170/50) = 4 messages

4️⃣ Internal Calls Rule

Calls within the same OIC instance are not billed

Integration → Integration

Process → Integration

Visual Builder → Integration

⚠️ Calling another OIC instance incurs billing on:

Target instance (always)

Source instance (if response > 50 KB)

Common Real-World Use Cases & Consumption Analysis

Use Case 1: REST Inbound → SaaS Update

Scenario

REST inbound payload: 40 KB

SaaS invoke response: 20 KB

Consumption

Trigger: ceil(40/50) = 1

Response: Not counted

✅ Total = 1 message

Use Case 2: Large REST Inbound Payload

Scenario

REST inbound payload: 120 KB

Consumption

Trigger: ceil(120/50) = 3

✅ Total = 3 messages

Use Case 3: File-Based Scheduled Integration

Scenario

Scheduled job downloads 3 files:

20 KB

40 KB

170 KB

Consumption

Only file > 50 KB is counted

ceil(170/50) = 4 messages

✅ Total = 4 messages

Use Case 4: Database Adapter with Loop Invokes

Scenario

DB adapter pulls 2 rows (20 KB)

REST invoke per row returns 20 KB

Consumption

Trigger: ceil(20/50) = 1

Invoke responses < 50 KB → Not billed

✅ Total = 1 message

Use Case 5: SOAP Trigger + Multiple File Downloads

Scenario

SOAP inbound: 70 KB

Files downloaded:

20 KB

170 KB

40 KB

Consumption

Trigger: ceil(70/50) = 2

File > 50 KB: ceil(170/50) = 4

✅ Total = 6 messages

Solution Steps to Optimize Message Consumption in OIC

✅ Step 1: Minimize Trigger Payload Size

Avoid sending unnecessary fields

Use query parameters instead of body where possible

Use lightweight JSON over SOAP when feasible

✅ Step 2: Control Invoke Response Size

Use REST APIs with selective fields

Avoid returning full object payloads

Use pagination for large datasets

✅ Step 3: Handle Large Files Smartly

Split large files before processing

Filter files before download

Process only delta files instead of full loads

✅ Step 4: Prefer Internal OIC Calls

Reuse integrations within the same OIC instance

Avoid cross-instance integration unless mandatory

✅ Step 5: Use Scheduled Integrations Carefully

Schedule only when needed

Avoid unnecessary polling

Combine multiple lightweight operations into one flow

✅ Step 6: Monitor Usage Regularly

Use Usage Metrics → Billable Messages

Track hourly (OIC) or monthly (OIC for SaaS) usage

Identify spikes caused by large payloads or files

Key Takeaways

• Trigger payload size is always billed.
• Invoke responses matter, not requests
• Files > 50 KB increase cost quickly
• Good integration design = lower licensing cost

Understanding these rules helps architects and developers design cost-efficient, scalable OIC integrations without compromising functionality.

Reference:

https://docs.oracle.com/en/cloud/paas/integration-cloud/oracle-integration-oci/monitoring-billable-messages.html

https://www.oraclespot.net/2023/05/oracle-integration-cloud-oic.html?m=0

Microsoft Excel - Working with excel's conditional functions

1. Microsoft Excel – Working with Excel’s Conditional Functions

Conditional functions perform calculations or return values based on conditions.

Common conditional functions:

• IF()
• COUNTIF(), COUNTIFS()
• SUMIF(), SUMIFS()
• IFERROR()
• AND(), OR(), NOT()

These are widely used in reporting, validation, dashboards, and analysis.

2. Working with Excel Name Ranges

A Named Range assigns a meaningful name to a cell or range.

Example: 

Instead of using

=SUM(A1:A10)

Use:

=SUM(Sales)

How to create a Named Range:

• Select the cells
• Go to Formulas → Define Name
• Enter the name (no spaces)
• Click OK

3. Advantages and Disadvantages of Excel Name Ranges

✅ Advantages

Improves readability of formulas

Easier maintenance

Reduces formula errors

Useful in complex models

Works well with conditional functions

❌ Disadvantages

Difficult to manage in very large workbooks

Hidden names can cause confusion

Not always intuitive for beginners

Broken references if ranges are deleted incorrectly

4. Editing an Excel Name Range

To edit a Named Range:

Go to Formulas → Name Manager

Select the name

Click Edit

Change range or name

Click OK

5. Using Excel’s IF() Function

The IF function returns one value if a condition is TRUE, another if FALSE.

Syntax:

=IF(logical_test, value_if_true, value_if_false)

Example:

=IF(A1>=50,"Pass","Fail")

6. Excel’s IF() Function with a Name Range

Assume:

Named Range: Score → A1

=IF(Score>=60,"Qualified","Not Qualified")

This makes formulas easier to understand and reuse.

7. Nesting Functions with Excel

Nesting means using one function inside another.

Example:

=IF(A1>=50, IF(A1>=75,"Distinction","Pass"),"Fail")

8. Nesting Excel’s AND() Function within the IF() Function

Use AND() when multiple conditions must be TRUE.

Syntax:

=IF(AND(condition1, condition2), value_if_true, value_if_false)

Example:

=IF(AND(A1>=50, B1="Yes"), "Eligible", "Not Eligible")

9. Using Excel’s COUNTIF() Function

Counts cells that meet one condition.

Syntax:

=COUNTIF(range, criteria)

Example:

=COUNTIF(A1:A20, ">=60")

Counts students scoring 60 or above.

10. Using Excel’s SUMIF() Function

Adds values that meet a specific condition.

Syntax:

=SUMIF(range, criteria, [sum_range])

Example:

=SUMIF(A1:A10,"Electronics",B1:B10)

Sums sales where category is Electronics.

11. Using Excel’s IFERROR() Function

Handles errors gracefully.

Syntax:

=IFERROR(value, value_if_error)

Example:

=IFERROR(A1/B1, 0)

Prevents #DIV/0! errors.

🔑 Practical Business Use Case

=IFERROR(

   IF(AND(Sales>=Target, Region="East"),"Bonus","No Bonus"),

"No Data")

Microsoft Excel - working with excel power pivot tools

1. Introduction to Excel Power Pivot

Power Pivot is an advanced data modeling and analytics feature in Microsoft Excel that allows you to:

• Work with large datasets (millions of rows)
• Combine data from multiple sources
• Create relationships between tables
• Use DAX (Data Analysis Expressions) for powerful calculations

Power Pivot turns Excel into a lightweight BI (Business Intelligence) tool.

2. Why Power Pivot?

Power Pivot is used when normal Excel formulas and PivotTables are not enough.

Key Benefits:

• 🚀 Handles huge data volumes efficiently
• 🔗 Supports relational data modeling (like databases)
• 📊 Creates advanced calculations using DAX
• 🔄 No need for VLOOKUP/XLOOKUP across tables
• 📈 Enables KPIs, calculated columns, and measures

When to use Power Pivot:

• Multiple tables (Sales, Customers, Products, Dates)
• Complex aggregations (YTD, MTD, growth %)
• Performance issues with traditional Excel

3. Activating the Excel Power Pivot Add-In

Power Pivot is disabled by default.

Steps to enable:

• Open Excel
• Go to File → Options
• Click Add-ins
• At the bottom, select COM Add-ins → Click Go
• Check Microsoft Power Pivot for Excel
• Click OK

👉 A new Power Pivot tab appears in the Excel ribbon.

4. Creating Data Models with Power Pivot

A Data Model is a collection of related tables used for reporting.

Ways to add data:

Import from Excel tables

Import from SQL Server / CSV / Access

Use Power Query → Load to Data Model

Steps:

Open Power Pivot → Manage

Import tables

Clean and shape data (optional via Power Query)

Create Calculated Columns and Measures

📌 Data is stored in compressed, in-memory format for fast performance.

5. Excel Power Pivot Data Model Relationships

Relationships connect tables using common key columns.

Example:

Sales[CustomerID]

Customers[CustomerID]

Relationship Types:

One-to-Many (1:*) – most common

Active vs Inactive relationships

How to create:

Power Pivot → Diagram View

Drag key column from one table to another

Ensure:

Same data type

Unique values on “One” side

✔ Eliminates lookup formulas

✔ Enables cross-table analysis

Option1:

Option 2:

6. Creating PivotTables based on Data Models

PivotTables built on Data Models are more powerful than standard ones.

Steps:

Insert → PivotTable

Choose Use this workbook’s Data Model

Build PivotTable using fields from multiple tables

Advantages:

Use fields from different related tables

Use Measures (DAX) instead of simple sums

Better performance

📊 Example:

Sales Amount by Region and Product Category

Customer-wise revenue trends

7. Excel Power Pivot KPIs

KPIs (Key Performance Indicators) measure performance against targets.

KPI Components:

Base Measure – actual value (e.g., Total Sales)

Target Measure – goal (e.g., Sales Target)

Status – visual indicator (green/yellow/red)

Steps to create KPI:

Create a Measure

Copy code

DAX

Total Sales := SUM(Sales[Amount])

Right-click the measure → Create KPI

Define target and thresholds

Choose icons (traffic lights, arrows)

📌 KPIs are visible in PivotTables and dashboards.

OIC -Event-Driven Notification Framework in Oracle Integration Cloud (OIC) Using Apache Kafka

Working...

🧩 Use Case

Enterprises need a reliable, scalable, and asynchronous notification platform that can handle:

• High-volume events
• Multiple notification channels (SMS, App, Email)
• Controlled retries
• Failure isolation
• End-to-end observability

The challenge is to integrate Apache Kafka with Oracle Integration Cloud (OIC) while ensuring:

• Guaranteed message delivery
• Retry and dead-letter handling
• Clean separation between orchestration and channel delivery

---

🎯 Solution Overview

This solution leverages:

• Apache Kafka as the event backbone
• Oracle Integration Cloud (OIC) for orchestration
• Retry Topic + Dead Letter Topic (DLT) pattern
• Channel-specific child integrations
• External notification providers

---

🏗️ High-Level Architecture

Core Components

• Kafka Topics
  • notification-main-topic
  • notification-retry-topic
  • notification-dead-topic
• OIC Integrations
  • Publisher Integration
  • Main Consumer Integration
  • Retry Consumer Integration
  • Child Channel Integrations (SMS, App, Email)

---

🔄 End-to-End Flow

1️⃣ Event Publishing to Kafka

• Source system (CCS / upstream app) sends notification request to OIC.
• OIC Publisher Integration publishes the event to:
  • Kafka Main Topic

OIC connects to Kafka via REST Proxy or OCI Service Connector / API Gateway (depending on deployment).

---

2️⃣ Kafka Main Topic Consumption

• OIC Main Consumer Integration consumes messages from:
  • notification-main-topic
• Performs:
  • Payload validation
  • Header enrichment (correlationId, retryCount)

---

3️⃣ Notification Orchestration

The OIC Main Service Orchestrator:

• Identifies notification type
• Routes to the correct child integration:
  • SMS
  • App Push
  • Email

---

4️⃣ Channel-Specific Child Integrations

Child Integration	External System
OIC-Child-SMS	Twilio
OIC-Child-App	Azure
OIC-Child-Email	SendGrid

Each child:

• Invokes external provider
• Returns success or failure response

---

5️⃣ Retry Handling with Kafka Topics

If processing fails:

• Message is published to Kafka Retry Topic
• retryCount is incremented
• Optional delay logic applied (consumer-side or scheduler-based)

Retry Consumer Integration:

• Consumes from notification-retry-topic
• Reprocesses message
• Republishes to Main Topic if retry count < max

---

6️⃣ Dead Letter Topic (DLT) Handling

When retry count exceeds threshold:

• Message is published to:
  • notification-dead-topic
• Dead messages are:
  • Logged to Datadog
  • Stored for audit and manual reprocessing

---

🔁 Retry Logic Summary

Stage	Action
Main Topic Failure	Send to Retry Topic
Retry Count < Max	Reprocess
Retry Count = Max	Send to Dead Topic
Dead Topic	Monitoring + Alerting

---

📊 Design Diagram (Kafka + OIC)

Source System (CCS)
        |
        v
   OIC Publisher
        |
        v
+-----------------------+
| Kafka Main Topic      |
| notification-main     |
+-----------------------+
        |
        v
 OIC Main Consumer
        |
        v
 OIC Main Orchestrator
   |        |        |
   |        |        |
  SMS      APP     EMAIL
   |        |        |
 Twilio   Azure  SendGrid
   |
Failure?
   |
   v
+-----------------------+
| Kafka Retry Topic     |
| notification-retry    |
+-----------------------+
        |
        v
 OIC Retry Consumer
        |
Retry < Max?
   |        |
  Yes      No
   |        |
   v        v
Main     Kafka Dead Topic
Topic    notification-dead
             |
             v
          Datadog

---

🛠️ Kafka Design Best Practices

✅ Separate topics for main, retry, and dead messages
✅ Include retryCount and correlationId in headers
✅ Use consumer groups for scalability
✅ Idempotent processing in OIC
✅ Externalized retry thresholds
✅ Centralized logging and monitoring

---

📌 OIC Integration Design Notes

• Use App-Driven Orchestration for consumers
• Use Stage File or ATP for temporary persistence (optional)
• Secure Kafka via:
  • SASL / SSL
  • API Gateway
• Use Tracking ID for end-to-end visibility

---

🚀 Benefits of Kafka-Based Design

✔ High throughput & durability
✔ Decoupled producers and consumers
✔ Controlled retries without message loss
✔ Cloud-agnostic architecture
✔ Easy scaling across consumers

---

🔚 Conclusion

By integrating Apache Kafka with Oracle Integration Cloud, organizations can build a resilient, scalable, and fault-tolerant notification framework that supports enterprise-grade messaging with full observability and retry control.