Friday, January 12, 2024

Improving search relevancy powered by hybridization of semantic search and lexical search

Search mechanism


Lexical search and semantic search are two major approaches used in information retrieval and search engines to enhance the accuracy and relevance of search results. Lexical search relies on matching the literal or surface-level representation of words and phrases in the query with the content in the search index. It primarily involves looking for exact matches of keywords without considering the broader meaning or context of the words. Lexical search can be precise but might lead to missed results if the exact terms aren’t present in the document. If a user searches for “apple pie recipes,” a lexical search engine returns results that specifically contain the words “apple,” “pie,” and “recipes.”

Semantic search focuses on understanding the meaning of the query and the context of the information rather than just matching keywords. It uses natural language processing (NLP), machine learning (ML), and other advanced techniques to comprehend the intent behind a user’s query. Semantic search considers synonyms, related concepts, and the relationship between words to provide more contextually relevant results. If a user searches for “healthy recipes,” a semantic search engine might also include recipes that use terms like “nutritious meals” or “wholesome cooking.”

Semantic search aims to understand the meaning behind user queries and provide contextually relevant results, while lexical search relies on literal matching of keywords. Both approaches have their advantages and can be used in combination to improve the overall search experience by considering both the explicit terms and the underlying meaning of the user’s intent.

Improving search relevancy powered by hybridization of semantic search and lexical search

Strategies to improve search results


Improving search results involves optimizing the search algorithm and user experience to provide more accurate, relevant, and personalized information. You can enhance search results in the following ways:

  • Implementing semantic search algorithms that understand the meaning behind words and user intent
  • Utilizing NLP to analyze and interpret the context of queries
  • Incorporating entity recognition to identify and prioritize key concepts and entities in search queries
  • Personalization
    • Customizing search results based on user preferences, search history, and behavior
    • Using ML algorithms to adapt to individual user patterns and provide more relevant recommendations
  • Context awareness
    • Considering the user’s context, such as location, device, and previous interactions, to refine search results
    • Incorporating temporal aspects to prioritize recent and time-sensitive information
  • Multimodal search
    • Supporting various types of content, including text, images, videos, and audio, and enabling users to search across different modalities
    • Integrating image recognition and speech-to-text capabilities for more comprehensive search functionality
  • Feedback mechanisms
    • Implementing user feedback loops to learn from user interactions and continuously improve the relevance of search results
    • Allowing users to provide explicit feedback on the quality of search results
  • Combining lexical and semantic approaches
    • Integrating both lexical and semantic search methods to apply the precision of exact matches and the contextual understanding of meaning
    • Balancing the importance of keywords with the broader context of user queries
  • Rich snippets and structured data
    • UtiliUtilizing structured data to enhance the display of search results with rich snippets, making it easier for users to find relevant information quickly
    • Ensuring that content is marked up using schema.org or similar markup languages to provide search engines with structured information
  • Mobile optimization: Optimizing search results for mobile devices by considering mobile-friendly design, loading speed, and user interface elements
  • Accessibility: Ensuring that search results are accessible to users with disabilities by following web accessibility guidelines
  • Regular updates and maintenance
    • Keeping search algorithms up-to-date with the latest advancements in technology and user expectations
    • Regularly reviewing and refining the search index to remove outdated or irrelevant content
 
By incorporating these strategies, search engines can enhance the overall search experience and provide users with more accurate and valuable information.

In this blog post, we focus on the idea of combining the strength of lexical text and vector search, which has gained momentum in the field of search systems to improve search relevancy and accuracy. By integrating text-based lexical search with vector-based semantic search, we can enhance both the latency and accuracy of our search results.

This post depicts how easily you can create a hybrid setup that combines the power of text and vector search. This setup gives you the most comprehensive and accurate search results. We use OpenSearch as the search engine and Hugging Face’s SentenceTransformers for generating embeddings. The dataset we chose for this task is the XMarket dataset, described in greater depth in Cross-Market Product Recommendation, where we embed the title field into a vector representation during the indexing process. The data used in this script here is public data consisting of electronic products.

Dataset preparation


To begin, we initiate the indexing of our documents using SentenceTransformers. This library features pretrained models capable of producing embeddings for sentences or paragraphs, serving as distinctive fingerprints for text segments. In the indexing phase, we transform the title field into a vector representation and incorporated it into OpenSearch. You can achieve this goal by importing the model and encoding any textual field effortlessly.

We create an index named “products” by passing the following mapping:

Payload 1

   "products":{ 
      "mappings":{ 
         "properties":{ 
            "asin":{ 
               "type":"keyword" 
            }, 
            "description_vector":{ 
               "type":"knn_vector", 
               "dimension":384 
            }, 
            "item_image":{ 
               "type":"keyword" 
            }, 
            "text_field":{ 
               "type":"text", 
               "fields":{ 
                  "keyword_field":{ 
                     "type":"keyword" 
                  } 
               }, 
               "analyzer":"standard" 
            } 
         } 
      } 
   } 
}

Let’s define the following parameters:

  • asin: The document unique ID taken from the product metadata
  • description_vector: Where we store our encoded product title field
  • item_image: An image url of the product
  • text_field: The title of the product

We’re using standard OpenSearch analyzer, which knows to tokenize each word in a field into single keywords. OpenSearch takes these keywords and uses them for the Okapi BM25 algorithm.

We then use the model to encode the title field and create documents, which loaded to OpenSearch.

Code snippet 1

### Necessary imports ###

import json
import datetime
import numpy as np
from opensearchpy import helpers
from sentence_transformers import SentenceTransformer
from opensearchpy import OpenSearch, RequestsHttpConnection

### Necessary server and index definition ###

SERVER_URL = "http://localhost:9200"
INDEX_NAME = "products"

### Necessary model and file definition ###

METADATA_PATH = "metadata_in_Electronics.json"
model = SentenceTransformer(’sentence-transformers/all-MiniLM-L6-v2’)

### Normalize Data ###

def normalize_data(data):
    return data / np.linalg.norm(data, ord=2)

### Load Dataset ###
def load_file(file_path):
    try:
        json_objects = []
        with open(file_path, "r") as json_file:
            for line in json_file:
                data = json.loads(line)
                if type(data) is list:
                    for element in data:
                        if "imgUrl" in element and type(element["imgUrl"]) is not list:
                            imgUrl = list(json.loads(element["imgUrl"]).keys())[0]
                            element["imgUrl"] = imgUrl
                            json_objects.append(element)
                elif "imgUrl" in data and type(data["imgUrl"]) is not list:
                    imgUrl = list(json.loads(data["imgUrl"]).keys())[0]
                    data["imgUrl"] = imgUrl
                    json_objects.append(data)
        print("Done")
    finally:
        json_file.close()
    return json_objects

### Get Open Search Host Details ###
def get_client(server_url: str) -> OpenSearch:
    os_client_instance = OpenSearch(SERVER_URL, use_ssl=False, verify_certs=False,
                                    connection_class=RequestsHttpConnection)
    print("OS connected")
    print(datetime.datetime.now())
    return os_client_instance

### Create Index ###
def create_index(index_name: str, os_client: OpenSearch, metadata: np):
    mapping = {
        "mappings": {
            "properties": {
                "asin": {
                    "type": "keyword"
                },
                "description_vector": {
                    "type": "knn_vector",
                    "dimension": get_vector_dimension(metadata),
                },
                "item_image": {
                    "type": "keyword",
                },
                "text_field": {
                    "type": "text",
                    "analyzer": "standard",
                    "fields": {
                        "keyword_field": {
                            "type": "keyword"
                        }
                    }
                }
            }
        },
        "settings": {
            "index": {
                "number_of_shards": "1",
                "knn": "false",
                "number_of_replicas": "0"
            }
        }

    }
    os_client.indices.create(index=index_name, body=mapping)

### Create Vector Dimension ###
def get_vector_dimension(metadata: list):
    title = metadata[0]["title"]
    embeddings = model.encode(title)
    return len(embeddings)

### Bulk Upload Data to Index ###
def store_index(index_name: str, data: np.array, metadata: list, os_client: OpenSearch):
    documents = []
    for index_num, vector in enumerate(data):
        metadata_line = metadata[index_num]
        text_field = metadata_line["title"]
        embedding = model.encode(text_field)
        norm_text_vector_np = normalize_data(embedding)
        document = {
            "_index": index_name,
            "_id": index_num,
            "asin": metadata_line["asin"],
            "description_vector": norm_text_vector_np.tolist(),
            "item_image": metadata_line["imgUrl"],
            "text_field": text_field
        }
        documents.append(document)
        if index_num % 1000 == 0 or index_num == len(data):
            helpers.bulk(os_client, documents, request_timeout=1800)
            documents = []
            print(f"bulk {index_num} indexed successfully")
            os_client.indices.refresh(INDEX_NAME)

    os_client.indices.refresh(INDEX_NAME)

Hybrid search implementation


The strategy involves developing a user interface that accepts input, utilizes the SentenceTransformers model to generate embeddings, and runs a hybrid search. The user is also prompted to specify a boost level, indicating the degree of importance assigned to either text or vector search. This process enables users to prioritize one search type over the other based on their preferences. For example, if a user wants the semantic meaning of their query to carry more weight than the simple textual appearance in the description, they can assign a higher boost to vector search than text search.

Lexical search

We start a text search on the index using OpenSearch’s search method. This method accepts a query string and provides a list of documents that align with the query. The processing of text search in OpenSearch involves sending the following request body:

Payload 2

{
   "size": 20,
   "query": 
   {
     "match": 
      {
        "text_field": "DSLR Camera"
      }
   },
   "_source": ["asin", "text_field", "item_image"]
}

Because the ranking score algorithms for text and vector searches differ, we must standardize the scores to a common scale for result combination, which involves normalizing the scores for each document obtained from the text search. The maximum BM25 score signifies the highest possible score assigned to a document in a collection for a specific query, representing the utmost relevance. The value of this score relies on BM25 formula parameters like the average document length, term frequency, and inverse document frequency. Consequently, we computed the maximum score attained from OpenSearch for each query and divided the scores of each result by this maximum, ensuring scores range between 0 and 1. The normalization algorithm is illustrated in the following function:

Code snippet 2

def normalize_bm25_formula(score, max_score):
    return score / max_score

def normalize_bm25(bm_results):
    hits = (bm_results["hits"]["hits"])
    max_score = bm_results["hits"]["max_score"]
    for hit in hits:
        hit["_score"] = normalize_bm25_formula(hit["_score"], max_score)
    bm_results["hits"]["max_score"] = hits[0]["_score"]
    bm_results["hits"]["hits"] = hits
    return bm_results

Semantic search

Next, we run a vector search using the vector search method. This method accepts a list of embeddings and provides a list of documents that exhibit semantic similarity to the given embeddings.

The OpenSearch search query for this process is structured like the following example:

Payload 3

cpu_request_body = {
                "size": 20,
                "query": {
                    "script_score": {
                        "query": {
                            "match_all": {}
                        },
                        "script": {
                            "source": "knn_score",
                            "lang": "knn",
                            "params": {
                                "field": "description_vector",
                                "query_value": get_vector_sentence_transformers(query).tolist(),
                                "space_type": "cosinesimil"
                            }
                        }
                    }
                },
                "_source": ["asin", "text_field", "item_image"],
            }

Hybridize the outcomes and implement the specific boost


Now, we combine the two search results by interpolating the results so that every document that occurred in both searches appears higher in the hybrid results list. This way, we can take advantage of the strengths of both text and vector search to get the most comprehensive results.

The following function interpolates the results of keyword search and vector search. It returns a dictionary containing the common elements between the two sets of hits and the scores for each document. If the document appears in only one of the search results, we assign it the lowest score that was retrieved.

Code snippet 3

def interpolate_results(vector_hits, bm25_hits):
    # gather all product ids
    bm25_ids_list = []
    vector_ids_list = []
    for hit in bm25_hits:
        bm25_ids_list.append(hit["_source"]["asin"])
    for hit in vector_hits:
        vector_ids_list.append(hit["_source"]["asin"])
    # find common product ids
    common_results = set(bm25_ids_list) & set(vector_ids_list)
    results_dictionary = dict((key, []) for key in common_results)
    for common_result in common_results:
        for index, vector_hit in enumerate(vector_hits):
            if vector_hit["_source"]["asin"] == common_result:
                results_dictionary[common_result].append(vector_hit["_score"])
        for index, BM_hit in enumerate(bm25_hits):
            if BM_hit["_source"]["asin"] == common_result:
                results_dictionary[common_result].append(BM_hit["_score"])
    min_value = get_min_score(common_results, results_dictionary)
    # assign minimum value scores for all unique results
    for vector_hit in vector_hits:
        if vector_hit["_source"]["asin"] not in common_results:
            new_scored_element_id = vector_hit["_source"]["asin"]
            results_dictionary[new_scored_element_id] = [min_value]
    for BM_hit in bm25_hits:
        if BM_hit["_source"]["asin"] not in common_results:
            new_scored_element_id = BM_hit["_source"]["asin"]
            results_dictionary[new_scored_element_id] = [min_value]

    return results_dictionary

In the end, we possess a dictionary where the document ID serves as the key and an array of score values acts as the corresponding value. The initial array element represents the vector search score, while the second element signifies the normalized score from the text search. To conclude, we implement a boost to refine our search results. Iterating through the result scores, we multiply the first element by the vector boost level and the second element by the text boost level.

Code snippet 4

def apply_boost(combined_results, vector_boost_level, bm25_boost_level):
    for element in combined_results:
        if len(combined_results[element]) == 1:
            combined_results[element] = combined_results[element][0] * vector_boost_level + \
                                        combined_results[element][0] * bm25_boost_level
        else:
            combined_results[element] = combined_results[element][0] * vector_boost_level + \
                                        combined_results[element][1] * bm25_boost_level
    # sort the results based on the new scores
    sorted_results = [k for k, v in sorted(combined_results.items(), key=lambda item: item[1], reverse=True)]
    return sorted_results

Complete code snippet

import numpy as np
from sentence_transformers import SentenceTransformer
from opensearchpy import OpenSearch, RequestsHttpConnection

SERVER_URL = "http://localhost:9200"
INDEX_NAME = "products"

model = SentenceTransformer(’sentence-transformers/all-MiniLM-L6-v2’)

def normalize_bm25_formula(score, max_score):
    return score / max_score

def normalize_bm25(bm_results):
    hits = (bm_results["hits"]["hits"])
    max_score = bm_results["hits"]["max_score"]
    for hit in hits:
        hit["_score"] = normalize_bm25_formula(hit["_score"], max_score)
    bm_results["hits"]["max_score"] = hits[0]["_score"]
    bm_results["hits"]["hits"] = hits
    return bm_results

def run_queries(os_client):
    while True:
        query = input("Enter your vector search query: ")
        vector_boost_level = float(input("Enter how much vector search boost you want to apply: "))
        if query == "exit":
            break
        else:
            bm25_boost_level = float(input("Enter how much keyword search boost you want to apply: "))
            apu_request_body = {
                "size": 20,
                "query": {
                    "gsi_knn": {
                        "field": "description_vector",
                        "vector": get_vector_sentence_transformers(query).tolist(),
                    }
                },
                "_source": ["asin", "text_field", "item_image"],
            }
            # reduce the scores by 1 when using cpu
            cpu_request_body = {
                "size": 20,
                "query": {
                    "script_score": {
                        "query": {
                            "match_all": {}
                        },
                        "script": {
                            "source": "knn_score",
                            "lang": "knn",
                            "params": {
                                "field": "description_vector",
                                "query_value": get_vector_sentence_transformers(query).tolist(),
                                "space_type": "cosinesimil"
                            }
                        }
                    }
                },
                "_source": ["asin", "text_field", "item_image"],
            }

            bm25_query = {
                "size": 20,
                "query": {
                    "match": {
                        "text_field": query
                    }
                },
                "_source": ["asin", "text_field", "item_image"],
            }
            vector_search_results = os_client.search(body=cpu_request_body, index=INDEX_NAME)
            print("vector_search_results")
            print(vector_search_results)
            bm25_results = os_client.search(body=bm25_query, index=INDEX_NAME)
            bm25_results = normalize_bm25(bm25_results)

            combined_results = interpolate_results(vector_search_results["hits"]["hits"],
                                                   bm25_results["hits"]["hits"])
            sorted_elements = apply_boost(combined_results, vector_boost_level, bm25_boost_level)

            result_data_dictionary = extract_results_data(vector_search_results["hits"]["hits"],
                                                          bm25_results["hits"]["hits"])
            construct_response(result_data_dictionary, sorted_elements)

def extract_results_data(vector_data, bm25_data):
    result_data_dictionary = {}
    for vector_hit in vector_data:
        product_id = vector_hit["_source"]["asin"]
        img_url = vector_hit["_source"]["item_image"]
        text_description = vector_hit["_source"]["text_field"]
        result_data_dictionary[product_id] = [img_url, text_description]
    for bm25_hit in bm25_data:
        product_id = bm25_hit["_source"]["asin"]
        img_url = bm25_hit["_source"]["item_image"]
        text_description = bm25_hit["_source"]["text_field"]
        result_data_dictionary[product_id] = [img_url, text_description]
    return result_data_dictionary

def construct_response(result_data_dictionary, sorted_elements):
    for index, sorted_element in enumerate(sorted_elements):
        print(index + 1, result_data_dictionary[sorted_element])

def get_vector_sentence_transformers(text_input):
    return model.encode(text_input)

def normalize_data(data):
    return data / np.linalg.norm(data, ord=2)

def get_client(server_url: str) -> OpenSearch:
    os_instance = OpenSearch(SERVER_URL, use_ssl=False, verify_certs=False,
                             connection_class=RequestsHttpConnection)
    # print("OS connected")
    return os_instance

def get_min_score(common_elements, elements_dictionary):
    if len(common_elements):
        return min([min(v) for v in elements_dictionary.values()])
    else:
        # No common results - assign arbitrary minimum score value
        return 0.01

def interpolate_results(vector_hits, bm25_hits):
    # gather all product ids
    bm25_ids_list = []
    vector_ids_list = []
    for hit in bm25_hits:
        bm25_ids_list.append(hit["_source"]["asin"])
    for hit in vector_hits:
        vector_ids_list.append(hit["_source"]["asin"])
    # find common product ids
    common_results = set(bm25_ids_list) & set(vector_ids_list)
    results_dictionary = dict((key, []) for key in common_results)
    for common_result in common_results:
        for index, vector_hit in enumerate(vector_hits):
            if vector_hit["_source"]["asin"] == common_result:
                results_dictionary[common_result].append(vector_hit["_score"])
        for index, BM_hit in enumerate(bm25_hits):
            if BM_hit["_source"]["asin"] == common_result:
                results_dictionary[common_result].append(BM_hit["_score"])
    min_value = get_min_score(common_results, results_dictionary)
    # assign minimum value scores for all unique results
    for vector_hit in vector_hits:
        if vector_hit["_source"]["asin"] not in common_results:
            new_scored_element_id = vector_hit["_source"]["asin"]
            results_dictionary[new_scored_element_id] = [min_value]
    for BM_hit in bm25_hits:
        if BM_hit["_source"]["asin"] not in common_results:
            new_scored_element_id = BM_hit["_source"]["asin"]
            results_dictionary[new_scored_element_id] = [min_value]

    return results_dictionary

def apply_boost(combined_results, vector_boost_level, bm25_boost_level):
    for element in combined_results:
        if len(combined_results[element]) == 1:
            combined_results[element] = combined_results[element][0] * vector_boost_level + \
                                        combined_results[element][0] * bm25_boost_level
        else:
            combined_results[element] = combined_results[element][0] * vector_boost_level + \
                                        combined_results[element][1] * bm25_boost_level
    # sort the results based on the new scores
    sorted_results = [k for k, v in sorted(combined_results.items(), key=lambda item: item[1], reverse=True)]
    return sorted_results

os_client = get_client(SERVER_URL)
run_queries(os_client)

Results outcome


We searched for “headphones” with a 0.5 boost for vector search and a 0.5 boost for text search, and got the following examples in the top four lexical results:

Improving search relevancy powered by hybridization of semantic search and lexical search

Improving search relevancy powered by hybridization of semantic search and lexical search

Improving search relevancy powered by hybridization of semantic search and lexical search

Improving search relevancy powered by hybridization of semantic search and lexical search

  • B&O Play 1108426 Ear Set 3i Headphones with Mic (Black)
  • Compact Ball Head W/Rc2
  • Benro S4 Video Head (Black)
  • GoPro Head Strap and Quick Clip

The results aren’t very accurate from user experience is concerned, apart from the first one. The pure semantic search returned the following results:

Improving search relevancy powered by hybridization of semantic search and lexical search

Improving search relevancy powered by hybridization of semantic search and lexical search

Improving search relevancy powered by hybridization of semantic search and lexical search

Improving search relevancy powered by hybridization of semantic search and lexical search

  • Wireless Headphone, Rowkin Mini Sports Bluetooth Earbud Headset with Built-in Mic and Portable Charging Case
  • JVC Riptidz HA-S200-B On-the-ear Headphone
  • Parasom Headphones (Black)
  • Betron HD in-ear, Noise Isolating, Heavy Deep Base Headphone for iPhone, iPod, iPad, MP3 Players, Samsung Galaxy, Nokia, HTC

The results are quite accurate for user experience. All the results are providing the headphones and not the accessories.

For hybrid search with 0.5 boost for vector search and a 0.5 boost for text search, we got the following top four results:

Improving search relevancy powered by hybridization of semantic search and lexical search

Improving search relevancy powered by hybridization of semantic search and lexical search

Improving search relevancy powered by hybridization of semantic search and lexical search

Improving search relevancy powered by hybridization of semantic search and lexical search

  • Wireless Headphone, Rowkin Mini Sports Bluetooth Earbud Headset with Built-in Mic and Portable Charging Case
  • JVC Riptidz HA-S200-B On-the-ear Headphone
  • Parasom Headphones (Black)
  • Betron HD in-ear, Noise Isolating, Heavy Deep Base Headphone for iPhone, iPod, iPad, MP3 Players, Samsung Galaxy, Nokia, HTC

The results are accurate where user experience is concerned, all the results providing the headphones and not the accessories. More importantly, at least the top four results are exact the same as the pure semantic search, although the boost provided the same for both text and semantic-based algorithm.

Source: oracle.com

Thursday, January 11, 2024

A Practical Guide to Using Sequences in Oracle Analytics

Understanding Sequences


Sequences in Oracle Analytics serve as a powerful tool for organizing and executing data flows, datasets, and other sequences in a logical manner. Sequences are particularly beneficial if you need to execute these items on a set schedule or in a particular order, or you want to leverage parallel execution for optimized performance. In this article, we’ll explore the technical advantages of sequences through a fitness-related use case.

Fitness Use Case


Imagine you have data streaming from your wearable device that’s populating new records to an Oracle Autonomous Data Warehouse (ADW) table on a weekly basis. Your goal is to transform and cleanse this data to create a curated dataset for visualizing in a workbook. In addition, you want to train a machine learning model to predict the number of calories burned during workouts that you want to periodically retrain. Here is an overview of the steps:

  1. Data Preparation and Transformation: Use data flows to cleanse the raw wearable device data to create datasets to use in visualizations and for machine learning training and testing.
  2. No Code Machine Learning Model Training: Use the no code machine learning features in data flows to create a model to predict caloric burn.
  3. Model Performance Evaluation: Examine how the model performs on a test dataset and visualize the results in a workbook.
  4. Incorporate External Datasets: Reload a cached weather-related dataset to use in the workbook to analyze trends such as average run pace based on outside temperatures and common running conditions.

The following high-level architecture diagram depicts the solution using wearable device data to address the requirements in the previous list. This solution involves multiple artifacts and requires various job runs.

A Practical Guide to Using Sequences in Oracle Analytics

To simplify and automate this process, we can group these processes into a sequence that runs on a set schedule. Relying on a sequence eliminates the need to configure individual schedules for each artifact. Sequences not only simplify scheduling and execution, but they make sharing with other users much faster. Users can easily share sequences and automatically share their contents and associated artifacts with a few simple clicks.

Building an Efficient Workflow


The following sections explain how to construct the wearable device data solution. The solution is running in a scheduled sequence in a personal Oracle Analytics Cloud (OAC) environment to ensure the data is current.

Step 1: Data Preparation and Transformation

We create a data flow that cleans the wearable device data and creates a curated dataset to use for creating visualizations in a workbook. This data flow also creates training and testing datasets for machine learning purposes. The following screenshot illustrates various transformation steps that were applied, and the three output datasets that were generated.

A Practical Guide to Using Sequences in Oracle Analytics

I used this data flow to create the test and train datasets for machine learning. I used a Branch step to create a branch after the data is cleansed and an Add Columns step with the RAND() function. This function created a column with pseudo-random numbers that fall between 0 and 1. I created another branch to create the two distinct testing and training datasets. To create the testing dataset, I used the Filter step to selectively retrieve rows where the newly added column exceeded 0.7. To create the training dataset, I used the Filter step to retrieve rows where the values in the new column are less than or equal to 0.7. This process allowed me to randomly select train and test data.

A Practical Guide to Using Sequences in Oracle Analytics

Step 2: No Code Machine Learning Model Training

The second data flow involved in the solution uses the training dataset created in the data flow above to generate a numeric prediction model to predict the number of calories burned in each workout. In other words, the output of data flow 1 is used as input in data flow 2.

A Practical Guide to Using Sequences in Oracle Analytics

Step 3: Model Performance Evaluation

The third and final data flow applies the machine learning model generated above to the test dataset generated in the first data flow. The purpose is to validate how well the machine learning model predicts calories burned.

A Practical Guide to Using Sequences in Oracle Analytics

Step 4: Incorporate External Datasets and Group Items in Sequence

It’s clear that the data flows above have many dependencies (for example, data flow 1 generates artifacts used by data flows 2 and 3, meaning it needs to be executed first). This step involves adding these data flows to a sequence, along with a cached weather dataset that requires a refresh to pull up-to-date weather information. The following screenshot shows these three items in the sequence. Notice that the sequence items aren't listed in order in the following screenshot and that the Ordered toggle at the top of the page is unchecked. When this toggle is unchecked, the system executes as many tasks as possible in parallel to optimize performance. It takes into consideration any artifact dependencies to determine the order in which the items need to be executed. If the Ordered toggle is checked, the order in which you place the items matters; the items are executed in the order in which they are placed.

A Practical Guide to Using Sequences in Oracle Analytics

A Practical Guide to Using Sequences in Oracle Analytics

As mentioned earlier, this sequence is running on a schedule.

Visualizing the Results

The following screenshot shows the visualizations created as part of this solution. Because the sequence is running on a schedule, the data is always up-to-date. The first canvas contains visualizations that illustrate the most common workout type, how the running pace has varied throughout training, and how the pace varies based on the outdoor temperature.

A Practical Guide to Using Sequences in Oracle Analytics

The next canvas contains charts generated from the machine learning model predictions. From these visualizations, it’s clear that the model performs well at predicting the calories burned for certain workouts. The visualization depict total caloric expenditure vs predicted caloric expenditure for different workout types and specific activities.

A Practical Guide to Using Sequences in Oracle Analytics

Call to Action


I encourage you to draw inspiration from this article and to leverage the power of data flows and sequences to streamline analytic workflows. By leveraging sequences, you can optimize data processing, enhance automation, and accelerate time-to-value.

Source: oracle.com

Monday, January 8, 2024

Enhance Oracle Analytics Custom Visualization Plug-Ins with Color Palettes

Color is a significant design element in data visualizations that contributes to a more compelling and engaging story for your audience, spontaneously capturing their attention. People intuitively associate colors with sentiments, brands, food, and other concepts. These associations make your visualizations more meaningful and easier to understand.

The Color Series property in Oracle Analytics contains various unique color palettes that you can select with ready-to-use visualizations. This article explains how to enable custom visualization plug-ins to directly inherit and consume these ready-to-use color palettes.

Figure 1 shows a canvas with four ready-to-use visualizations that display the default Redwood color series.

Enhance Oracle Analytics Custom Visualization Plug-Ins with Color Palettes
Figure 1: The Default (Redwood) Color Series

You can change the color of the visualizations in two ways:

1. As shown in figure 2, click the menu in the top-right corner of the canvas, click Workbook Properties, and select a color series. You can also add a new color palette by clicking the Add Palette option.

Enhance Oracle Analytics Custom Visualization Plug-Ins with Color Palettes
Figure 2: Steps to change color series option (method 1)

2. As shown in figure 3 and 4, click the menu in the top-right corner of the chart, select Color, click Manage Assignments, and select a color series.

Enhance Oracle Analytics Custom Visualization Plug-Ins with Color Palettes
Figure 3: Steps to change color series option (method 2)

Enhance Oracle Analytics Custom Visualization Plug-Ins with Color Palettes
Figure 4: Steps to change color series option (method 2)

By selecting the Glacier color series option for instance, all visualizations in the canvas inherit that color series.

Enhance Oracle Analytics Custom Visualization Plug-Ins with Color Palettes
Figure 5: The Glacier Color Series

Plug-in code changes to inherit the Color Series property


Color series changes don't apply automatically to custom visualizations. To achieve this, you update your custom plug-in code.

To understand the code changes, see the example with a custom plug-in called Oracle Analytics Marimekko custom visualization plug-in. You can download this plug-in from Oracle Analytics extensions library.

Enhance Oracle Analytics Custom Visualization Plug-Ins with Color Palettes
   Figure 6: Marimekko plug-in files

Open the marimekkoViz.js file. The script has two parts: the generateData function and the render function.

Enhance Oracle Analytics Custom Visualization Plug-Ins with Color Palettes

You must make a few changes in both parts of the code.

Changes in the generateData function


219          MarimekkoViz.prototype._generateData = function(oDataLayout, oTransientRenderingContext){
220           
221          var oDataModel = this.getRootDataModel();
222                if(!oDataModel || !oDataLayout){
223                    return;
224                }
225              //  this.setRowDisplayNames([]);
226              this.setRowDisplayNames(new Map());
227               var aAllMeasures = oDataModel.getColumnIDsIn(datamodelshapes.Physical.DATA);         
228                var nMeasures = aAllMeasures.length;
229                var nRows = oDataLayout.getEdgeExtent(datamodelshapes.Physical.ROW);
230                var nRowLayerCount = oDataLayout.getLayerCount(datamodelshapes.Physical.ROW);
231                var oDataLayoutHelper = oTransientRenderingContext.get(dataviz.DataContextProperty.DATA_LAYOUT_HELPER);
232                var oColorContext = this.getColorContext(oTransientRenderingContext);
233                var oColorInterpolator = this.getCachedColorInterpolator(oTransientRenderingContext, datamodelshapes.Logical.COLOR);
234    
235                var rowLabels = [];
236               for(var nRow = 0; nRow < nRowLayerCount; nRow++){
237                
238                    var rowKey = oDataLayoutHelper.getLogicalEdgeName(datamodelshapes.Physical.ROW, nRow);
239                    var displayName = oDataLayout.getLayerMetadata(datamodelshapes.Physical.ROW, nRow, data.LayerMetadata.LAYER_DISPLAY_NAME);
240                    this.getRowDisplayNames().set(rowKey, {order: nRow, name: displayName});
241                    if(rowKey == "row"){
242                        this.setXDisplayName(displayName);
243                    }
244                    else if(rowKey == "color") {
245                        if (displayName)
246                        this.setYDisplayName(displayName);
247                    }

<strong>var oColorContext = this.getColorContext(oTransientRenderingContext);</strong>

The variable oColorContext (line #232) fetches the color context from the oTransientRenderingContext.

The oTransientRenderingContext provides the context for rendering a visualization. It reads the color series label for each row from the input dataset (in the generateData function) using the color context object.

Next, refer to the switch case statement for 'row Type' as 'color' (line #277), as shown in the following code example. It reads the value from the color grammar in the visualization. 

Changes in the 'color' case:


264       switch (rowType) {               
265                       case "row":
266           month = row!="" ? row + ", "              +oDataLayout.getValue(datamodelshapes.Physical.ROW, nRowLayer, nRow, false)
267                                : oDataLayout.getValue(datamodelshapes.Physical.ROW, nRowLayer, nRow, false);
268                                  if(gmonth == "")
269                                   {
270                                  gmonth = month;
271                                   }
272                                   else{
273                                      gmonth = gmonth+','+month;
274                                   }
275                          xSet.add(month);
276                           break;
277                        case "color":
278                    colorObj = this.getDataItemColorInfo(oDataLayoutHelper, oColorContext, oColorInterpolator, nRow, 0);
279                           color_hash = colorObj.sColor;
280                           cause = colorObj.sSeriesColorLabel;
281                           break;
282                    }
283                }
284              var aOutput =[];
285           
286           
287                aOutput = {month: gmonth, cause: cause, value: value, color: color_hash, row: nRow};
288                if(value >= 0)
289                {
290                outputMap.push(aOutput);
291                }
292            }

The 'color' case:

case "color":<strong>
            </strong> colorObj = this.getDataItemColorInfo(oDataLayoutHelper, oColorContext, oColorInterpolator, nRow, 0);
                           color_hash = colorObj.sColor;
                           cause = colorObj.sSeriesColorLabel;
                           break;

The variable 'color_hash' stores the hex color code of the inherited color series and the variable 'cause' holds the value from the data visualization color grammar.

Changes in the render function


Refer to the CSS style property (line #599) in the Marimekko plug-in that returns 'd.color' to inherit the data visualization color series. 'd.color' returns the value stored in the 'color_hash' variable.

566       causes.append("rect")
567            .attr("data-row", function(d) {
568                return d.row;
569            })
570            .attr("y", function (d) { …
584            })
585            .attr("height", function (d) { …
594            })
595            .attr("width", function (d) { …
597            })
598            .style("fill", function (d) {
599                  return d.color;        
600            })
601            .on("mouseover", function(d) { …
607            })
608            .on("mousemove", function(d) { …
613            })
614            .on("mouseout", function(d) { …               
617            })
618            .on("click", function(d, i ){ …       
633            })
634            .on("contextmenu", function (d, i) { …
644            });

.style("fill", function (d) {
                  return d.color;         
            })

With the code changes listed above, the custom plug-in considers the selected color series, but the color change isn't reflected instantly. To have the custom visualization instantly reflect changes to the color palette, add the following lines of code:

1198    MarimekkoViz.prototype._OnDefaultColorSettingsChange = function(/*oClientEvent*/){
1199    var oTransientVizContext = this.createVizContext();
1200    if(!this._handleVizPlaceholderState(oTransientVizContext)){
1201       //this.readyForData({aEventTriggers:[DEFAULT_COLOR_SETTINGS_CHANGED_EVENT_TRIGGER]});
1202        var oTransientVizContext = this.assertOrCreateVizContext();
1203      var oTransientRenderingContext = this.createRenderingContext(oTransientVizContext);
1204      this._render(oTransientRenderingContext);
1205    }
1206  };
1207
1208 MarimekkoViz.prototype._doInitializeComponent = function() {
1209       
1210   MarimekkoViz.superClass._doInitializeComponent.call(this);
1211
1212   this.subscribeToEvent(events.types.INTERACTION_HIGHLIGHT, this.onHighlight, this.getViewName() + "." +       events.types.INTERACTION_HIGHLIGHT);
1213   this.subscribeToEvent(events.types.DEFAULT_COLOR_SETTINGS_CHANGED, this._onDefaultColorsSettingsChange, "**");
1214
1215 };

Add a function, 'Marimekkoviz.prototype._onDefaultColorSettingsChange' to render the required context (line #1198).

Add subscribeToEvent 'DEFAULT_COLOR_SETTINGS_CHANGED' (line #1213).

With these code changes, when the default settings of color changes in the workbook, it re-renders all visualizations to reflect the changes. For example, on selecting the Glacier color series, the custom visualization (Marimekko plug-in on the left) inherits the selected color series, as shown in figure 7.

Enhance Oracle Analytics Custom Visualization Plug-Ins with Color Palettes
Figure 7: The Glacier color series

Adjusting text font-color based on the background color


When you implement a custom color series in the plug-in, sometimes the font color isn't legible, as shown in figure 8:

Enhance Oracle Analytics Custom Visualization Plug-Ins with Color Palettes
Figure 8: Default font color for custom plug-ins

To work around this issue, the following code sample explains an approach to display text in a white font if the background color is dark and a black font for light-colored backgrounds.

767   .style("fill", function(d) {
768          var hex = d.color;
769                var red = parseInt(hex.substring(1, 3), 16);
770                var green = parseInt(hex.substring(3, 5), 16);
771                var blue = parseInt(hex.substring(5, 7), 16);
772
773                    console.log(red, green, blue);
774                    if ((red*0.299 + green*0.587 + blue*0.114) > 130) {
775                  return "black";
776                } else
777                return "white";
778            }

The 'd.color' holds the inherited data visualization color. Break the hex code into three pieces to extract the individual red, green and blue colors. Each two digits of the code represent a value in hexadecimal (base-16) notation. You can read about the conversion details online.

Once you have the intensities for the individual colors, you can determine the overall intensity of the color and choose the corresponding text color. 

The Marimekko plug-in considers a threshold value as 130. If the converted value exceeds 130, the font color is black and, if the value is less than 130, then the font color is white. You can select a threshold value based on your judgement. Figure 9 shows an example of this font color implementation.

Enhance Oracle Analytics Custom Visualization Plug-Ins with Color Palettes
Figure 9: Font colors based on background color in a Marimekko chart

Call to Action:


Download custom plug-in code from the Oracle Analytics Extensions Library. Edit the custom plug-in code as explained in this article to leverage the color series property of the canvas into your custom visualizations, just like in any other prebuilt visualizations.

Source: oracle.com

Friday, January 5, 2024

Why Oracle closes its books twice as fast as SAP and Workday

Oracle closes the books 14 days faster than Workday and 12 days faster than SAP per quarter. In a year, that is 56 days with Workday and 48 days with SAP you are leaving on the table. Imagine what your finance team could accomplish with 48 more days in the year?

With the right data in your financial systems, not only can you close your books faster, but you can respond faster to changing demands and emerging opportunities in your business.

Why Oracle closes its books twice as fast as SAP and Workday
Figure 1.  Average number of days to close the books every quarter since 2015. Source: Refinitiv Eikon.

To be fast and nimble, you need the right data, not more data


There’s a tendency among application vendors like SAP and Workday to assume that putting more data into financials applications is better. With data-driven innovation in mind, one could indeed think that more is better—the more data you have, the more you can analyze and the more intelligent your enterprise appears to be. But what is the optimal amount of data for Finance? Where do you store data that you don’t absolutely require in your financials system? What types of analyses are useful? How much data do you need to benefit from data-driven innovation?

Let’s investigate:

In financials, a traditional ledger key consists at minimum of these data elements:

  • Company, depicting a whole or logical part of a corporation, legal entity, or organization
  • Account, to record balances and organize financial information and reporting
  • Center, a logical unit within your organization, like business unit, department or group

Organizations typically use more than just these three segments in their financials. Other examples of segments that are commonly used are project and product; most companies use about six segments. With Oracle Cloud ERP, the key can have 30 segments of 240 characters each. The total combined grouping of company, account, and center and the other segments you’ve defined is called a ledger. You can have as many ledgers as you like.

Oracle’s ledgers can also be put into a federated structure. That means that you have a structure of ledgers that may share some key data elements (like cost centers or accounts) but they can also have other independent data elements in their key. Each ledger can have its own:

  • Accounting method
  • Cost method
  • Cost granularity
  • Costing period

Maintenance is federated, so if you maintain the top ledger, the underneath ledgers can optionally inherit the updates from the top ledger. This way, you can have as many ledgers as you like, each with 30 segments, so theoretically an endless combination of key data elements in your financials.


Why Oracle closes its books twice as fast as SAP and Workday
Figure 2. The structure of the federated ledgers is graphical in Oracle Fusion Applications. Source: Oracle

Oracle believes in being smarter and more structured with financials data. In contrast, SAP and Workday simply add more data in the hope that it makes the user smarter.

SAP. With SAP S/4HANA, SAP introduced what is called the “universal journal.” SAP extended the number of segments that the universal journal can hold. SAP S/4HANA now has a total of 999 segments in S/4HANA Financials, making up the ledger key. As a result, you can get “customers,” “sales campaigns” and “employee group” as ledger keys in SAP’s financials. Running financials with this many segments is referred to as a “thick GL,” meaning that it has more data than you need from an accounting point of view. In contrast, a “thin GL” has enough data for financial reporting, while other data is stored elsewhere in the suite of applications.

Workday. In essence, Workday does the same as SAP: it has a thick ledger, with more ledger keys than you need from an accounting point of view. The data elements are called Worktags, and you can have approximately 100 different types of Worktags. These Worktags make up the ledger key. As with SAP, you can get “customers,” “sales campaigns” and “employee group” as ledger keys in Workday Financials.

In a nutshell, this is how it compares:

Why Oracle closes its books twice as fast as SAP and Workday
Figure 3. How Financials compare. Sources: SAP S/4HANA Finance, an introduction”, Jens Krüger, Rheinwerk Publishing and “Tales of the Cloud: The Story of Worktags,” Workday blog, 2012.

Get smarter and more efficient with the right data


To work smarter with all the data in your organization, close your quarterly periods faster and speed up the decision-making process, consider these steps:

  • Deploy machine learning (ML) techniques. The more data that is introduced to intelligent agents, the more accurate it becomes. But that doesn’t mean that you need to move more data into financials; ML can learn from data anywhere in your Oracle enterprise applications. It can even learn from data outside of Oracle Applications. ML also learns from how people interact with data, so that ML can personalize the response. ML doesn’t just look at how people handle data in financials, but again, anywhere in your Oracle enterprise applications.
  • Use real-time data from their source. You already have sales data in your customer experience applications, employee data in human capital management, supply chain data in supply chain management and financial and planning data in ERP. Oracle also provides a semantic data model in Oracle Fusion Analytics Warehouse (FAW) to make end-users self-sufficient; they can interact with data in all those applications using commonly understood business terms in their respective source systems, without depending on IT.

Consider upcoming scenarios. React faster to upcoming changes with timely financial views of the business with the packaged KPIs, scenarios, visualizations and analytics dashboards in

  • FAW, built on Oracle Analytics Cloud and the Oracle Autonomous Data Warehouse. There is no database or data warehouse design, and no tuning, data loading (ETL), or modeling; you just turn it on. Optionally, you can even include additional data from other applications and start creating scenarios.
  • Enforce accounting policies. Oracle Cloud Financials is the single repository of accounting logic. This single set of accounting rules transform transaction information from external systems to meet statutory, corporate, regulatory, and management reporting needs. With centrally maintained accounting rules, you’ll prevent errors and need to make fewer corrections in your financials.
  • Drill back to the source, don’t copy it. Keep industry-specific detailed transactional data in the source system, like high-volume billing data in the telecommunications or insurance industries. Let Oracle Fusion Cloud Accounting Hub (part of Oracle Cloud Financials) harmonize the data and send the result to your financials. Drill back to the source when you need more details.

Source: oracle.com

Wednesday, January 3, 2024

SQL Firewall now built into Oracle Database 23c

SQL injection attacks and compromised accounts are the two most common techniques that adversaries use to gain full access to sensitive data stored in databases. While user and developer education has helped, success remains limited. In certain cases, fixing application code might be unfeasible, especially for packaged or legacy applications. Web application firewalls (WAFs) claim to mitigate SQL injection attacks, but attacker can bypass these pattern-matching approaches. Allowlist-based and network-based SQL firewalls offer stronger mitigation, but not for local and encrypted traffic. Moreover, they don’t have a full run-time context required for fully analyzing SQL queries.

Considering the high value of the data stored in the database, we need an SQL firewall that comprehensively scans all SQL traffic irrespective of where it comes from, without exceptions. Enforcement hinges on the firewall’s ability to understand the SQL with its full context. This context is what the database builds while running any SQL statement, including steps to resolve synonyms, object names, or dynamically generated names, and current user context. For any security to be practical, the firewall must ideally run at wire speed and facilitate easy management and monitoring of not just one database but a fleet of databases.

Oracle Database 23c’s SQL Firewall integration


It was precisely this challenge that the Oracle database security team undertook. We're thrilled to announce the integration of a powerful SQL Firewall directly into Oracle Database 23c to effectively address both SQL injection attacks and compromised account issues. What’s equally exciting is that we offer an easy solution that can have minimal impact on performance. 

The new Oracle SQL Firewall offers the following features and benefits:

  • Oracle SQL Firewall inspects all incoming database connections and SQL statements, including those from PL/SQL (Oracle’s procedural extension to SQL), whether local or over the network, encrypted or clear text. It only allows explicitly authorized SQL. For all other SQL, it logs the offending statements and raises violations. This statement could have been a SQL injection attack or a new SQL statement that the authorized user hasn’t run before.
  • Customers can decide whether they want to block unauthorized SQL or only log it, giving them the flexibility on how to handle attacks.
  • Oracle SQL Firewall evaluates the complete SQL and the processing context. By running inside the Oracle database server, the firewall easily handles encoding of the SQL statement, synonyms, dynamically generated object names, and any SQL statements dynamically generated in PL/SQL units.
  • Oracle SQL Firewall relies on the allowlisting of the authorized SQL statements and associated trusted database connection paths while blocking the rest. You train the SQL Firewall by simply capturing authorized SQL statements for an application account. Subsequently, the firewall detects and prevents unauthorized SQL and potential SQL injection attacks.
  • Oracle SQL Firewall can also block connections not coming from trusted IP addresses, operating system usernames, or program names. This function is useful when you want to put some protection in place immediately, while you create the allowlist for your applications. This feature ensures that any direct access to your databases is coming exclusively from trusted endpoints.

By building Oracle SQL Firewall inside the database and streamlining its implementation, the performance overhead of Oracle SQL Firewall is negligible, making it suitable for all production workloads. Because Oracle SQL Firewall is inside the database, you don’t need to deploy or manage any external components, greatly simplifying deployment.

Figure 1 shows how Oracle SQL Firewall operates inline within the Oracle Database 23c kernel, evaluating every incoming SQL statement, regardless of origin. Oracle SQL Firewall always generates a violation log when it detects a violation of its rules. However, you can configure SQL Firewall to either allow or block violating SQL statements.

SQL Firewall now built into Oracle Database 23c
Figure 1: Oracle SQL Firewall built into the Oracle Database kernel

Managing Oracle SQL Firewall


Oracle SQL Firewall policies work at a database account level, whether of an application account or a direct database user, such as a reporting user or a database administrator. This flexibility allows you to gradually build up the protection level of the database, starting from either the database administrators or the application accounts.

You can manage Oracle SQL Firewall in multiple ways. We recommend PL/SQL procedures in the SYS.DBMS_SQL_FIREWALL package if you have a standalone implementation and are proficient in managing through commands.

If you’re looking for UI-based management or to manage multiple Oracle SQL firewalls centrally, Oracle Data Safe is your answer. If you’re already an Oracle Data Safe customer using it for user and security assessment, activity auditing, sensitive data discovery, and data masking, you can now use the same Data Safe cloud service for managing Oracle SQL Firewall and preserve your investment.

The Data Safe unified console has been extended to manage SQL Firewall. Administrators can use the console to collect SQL activities of application accounts, monitor the collection progress, create Oracle SQL Firewall policies with allowlist rules (allowed contexts, allowed SQL statements) from the captured SQL activities, and enable SQL Firewall policies. When a firewall policy is enabled, Data Safe automatically collects the firewall violation logs from the database and stores them in Data Safe. Those logs are then available for online analysis and reporting across your database fleet, as shown in Figure 2.

You can use the Data Safe REST APIs, software developer kits (SDKs), CLI, and Terraform for further automation and integration. You can also utilize the larger Oracle Cloud Infrastructure (OCI) ecosystem for integrating Oracle SQL Firewall violations with its alerts and notifications.

SQL Firewall now built into Oracle Database 23c
Figure 2: SQL Firewall dashboard in Oracle Data Safe

Complementing industry-leading database security features within the Oracle database for authentication, access control, encryption, and auditing, Oracle SQL Firewall inspects all incoming SQL statements and allows only authorized SQL statements. Oracle SQL Firewall solution provides unmatched security and administrative convenience through Data Safe.

Source: oracle.com

Monday, January 1, 2024

Schema-level privilege grants with Database 23c

Schema-level privilege grants with Database 23c

Many modern applications separate the data-owning schema from the application service or run-time account used to access that data. This schema provides for a separation of duty and the least-privilege model and can help lower the risk if the accessing account is compromised. But how do you manage this list? As application schemas change over time, how do you keep that list current?

Previously, developers had the following options:

  • Grant individual privileges on each table and view in the application schema
  • Grant ANY privileges: Select any table, update any table, and so on.

The first choice may be inconvenient because you need to identify every single table or view and then grant every run-time or service user permission individually. You could develop a script, but that’s an extra step. This option is also a suboptimal way to deal with application schema changes, such as adding new tables or views, because you must now remember to make corresponding privilege grants. Also, visualizing and verifying such detailed lists can be daunting.

The second choice of granting ANY privileges, while convenient, is suboptimal from a security angle because you grant that user the ability to select from every table in the database! If this user account is compromised, your entire database can be compromised.

Oracle Database 23c to the rescue

To address this, Oracle Database 23c introduces a new schema-level grant. If you GRANT SELECT ANY TABLE ON SCHEMA HR TO BOB, that user can see all the tables and views in the HR schema - and only in the HR schema. If a new table is added to the schema, they instantly have access to that new table. No extra management is needed, and you continue to support a least-privilege security model with appropriate separation of duties.

Schema-level privilege grants with Database 23c

Users can grant schema-level privileges on their own schema without having any special privileges. To grant schema-level privileges on someone else’s schema, you need either the GRANT ANY SCHEMA or GRANT ANY PRIVILEGE system privilege.

To see which schema privileges have been granted, use the DBA_SCHEMA_PRIVS view. You can also use ROLE_SCHEMA_PRIVS, USER_SCHEMA_PRIVS, and SESSION_SCHEMA_PRIVS views.

Source: oracle.com