When you drag and drop a column from a physical table that is not currently being used in your logical table in BMM layer, the physical table containing such column gets added as a new Logical Table Source (LTS).

When in BMM layer, you use more than one table as source table, it is called multiple logical table sources. You can have a Fact table as multiple logical table sources when it uses different physical tables as source.

Example

Multiple LTS are used to convert Snowflakes schema to Star schemas in BMM layer.

Let us say you have two dimensions − Dim_Emp and Dim_Dept and one fact table FCT_Attendance in the Physical layer.

Here your Dim_Emp is normalized to Dim_Dept to implement Snowflakes schema. So in your Physical diagram, it would be like this −

Dim_Dept<——Dim_Emp <——-FCT_Attendance

When we move these table to the BMM layer, we will create a single dimension table Dim_Employee with 2 logical sources corresponding to Dim_Emp and Dim_Dept. In your BMM diagram −

Dim_Employee <———–FCT_Attendance

This is one approach where you can use concept of multiple LTS in BMM layer.

Specifying Content

When you use multiple physical tables as sources, you expand table sources in BMM diagram. It shows all multiple LTS from where it is picking up the data in BMM layer.

To see table mapping in BMM layer, expand the sources under logical table in BMM layer. It will open Logical table source mapping dialogue box. You can check all tables which are mapped to provide data in logical table.

OBIEE – Calculation Measures

Calculated measures is used to perform calculation of facts in logical tables. It defines Aggregation functions in Aggregation tab of logical column in the repository.

Create New Measure

Measures are defined in logical fact tables in repository. Any column with an aggregation function applied on it is called a measure.

Common measure examples are − Unit Price, quantity sold, etc.

Following are the guidelines to create measures in OBIEE −

All aggregation should be performed from a fact logical table and not from a dimension logical table.
All columns that cannot be aggregated should be expressed in a dimension logical table and not in a fact logical table.

Calculated measures can be defined in two ways in logical tables at BMM layer in Administration tool −

Aggregations in logical tables.
Aggregations in logical table source.

Create Calculated Measures in Logical Tables using Administration Tool

Double-click on the column name in the logical Fact table, you will see the following dialog box.

Go to Aggregation tab and select the Aggregate function from the drop-down list → Click OK.

You can add new measures using functions in Expression builder wizard in Column source. Measures represent data that is additive, such as total revenue or total quantity. Click on the save option at the top to save the repository. This is also called creating measures at logical level.

Create Calculated Measures in Logical Table Source using Administration Tool

You can define Aggregations by a double-click on Logical table source to open logical table dialogue box.

Click on Expression builder wizard to define expression.

In Expression builder, you can choose multiple options like – Category, functions, and mathematical functions.

Once you select the category, it will show the subcategories inside it. Select the subcategory and mathematical function, and click on the arrow mark to insert it.

Now to edit the value to create measures, click on source number, enter the calculated value like multiple and divide → Go to Category and select logical table → Select column to apply this multiple/division to an existing column value.

Click OK to close the Expression builder. Again click OK to close the dialog box.

OBIEE – Dimension Hierarchies

Hierarchies is a series of many-to-one relationships and can be of different levels. A Region hierarchy consists of: Region → Country → State → City → Street. Hierarchies follow top-down or bottom-up approach.

Logical dimensions or dimension hierarchies are created in BMM layer. There are two types of dimensional hierarchies that are possible −

Dimensions with level-based hierarchies.
Dimension with Parent-Child hierarchies.

In level-based hierarchies, members can be of different types and members of the same type come only at single level.

In Parent-Child hierarchies, all members are of the same type.

Dimensions with Level-based Hierarchies

Level-based dimension hierarchies can also contain parent-child relationships. The common sequence to create level-based hierarchies is to start with grand total level and then working down to lower levels.

Level-based hierarchies allows you to perform −

Level-based calculated measures.
Aggregate navigation.
Drill down to child level in dashboards.

Each dimension can only have one grand total level and it doesn’t have a level key or dimension attributes. You can associate measures with grand total level and default aggregation for these measures are grand total always.

All lower levels should have at least one column and each dimension contains one or more hierarchies. Each lower level also contains a level key which defines unique value at that level.

Types of Level-based Hierarchies

Unbalanced Hierarchies

Unbalanced hierarchies are those where all the lower levels don’t have the same depth.

Example − For one product, for one month you can have data for weeks and for other month you can have data available for day level.

Skip Level Hierarchies

In skip-level hierarchies, few members don’t have values at higher level.

Example − For one city, you have state → country → Region. However for other city, you have only state and it doesn’t fall under any country or region.

Dimension with Parent-child Hierarchies

In parent-child hierarchy, all the members are of the same type. The most common example of parent-child hierarchy is the reporting structure in an organization. Parent-child hierarchy is based on a single logical table. Each row contains two keys – one for the member and another for the parent of the member.

So, this brings us to the end of blog. This Tecklearn ‘Multiple Logical Table Sources , Calculation Measures and Dimension Hierarchies’ blog helps you with commonly asked questions if you are looking out for a job in Oracle BI. If you wish to learn OBIEE and build a career in Business Intelligence domain, then check out our interactive, Oracle Business Intelligence Enterprise Edition (OBIEE) Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

https://www.tecklearn.com/course/oracle-business-intelligence-enterprise-edition-training-and-certification/

Oracle Business Intelligence Enterprise Edition (OBIEE) Training

About the Course

Oracle Business Intelligence Enterprise Edition (OBIEE) Training and Certification training course lets you master the Oracle Business Intelligence and Analytics platform. You will gain in-depth knowledge on OBIEE advanced features, BI dashboards, reports, etc., through hands-on training sessions. This course is meant to provide detailed knowledge of the OBIEE 12c. You will be trained in various Business Intelligence scenarios, BI life cycle, concepts of OLAP and working with charts and graphs.

Why Should you take OBIEE Training?

The average salary for “oracle business intelligence consultant” ranges from approximately $76,703 per year for Business Consultant to $113,285 per year for Full Stack Developer. -Indeed.com.
Oracle Business Intelligence has a market share of around 8% globally.
OBIEE gives you experience with next-generation solutions that can help strategize, plan and optimize business operations. Allow you to become an Oracle Certified Expert or Oracle Implementation Specialist of an in-demand solution.

What you will Learn in this Course?

Data Modelling and Data Warehousing Concepts

Introduction to Oracle Business Intelligence Enterprise Edition (OBIEE)
Data models
Data warehousing
Data modelling implications and the impact of Data modelling on business intelligence

Business Intelligence Concepts

Overview of Business Intelligence
Description of BI Stack: BI technology, BI Server and BI Scheduler
Need for reporting in business
Difference between OLTP and OLAP
Multidimensional and relational analytical processing

OBIEE Installation

OBIEE Installation (12c)
Oracle Business Intelligence Suite
Architecture of OBIEE
Key features and components

Online Analytical Processing (OLAP)

Concept of Online Analytical Processing
Significance of OLAP in business intelligence life cycle
Star Schema and Designing with Star Schema
Enterprise information model
Snow flake and constellation

Oracle BI Repository

What is Oracle Business Intelligence Repository?
Directory structure installation, services,
Analytics and interactive reporting,
Dashboard creation and multiple report creation

Business Intelligence Repository Business Model

How to build a Business Model and Mapping Layer in BI Repository
Data format, Conditional format, Removing filters,
Report Saving
Creation of new folder
Working with the Enterprise Manager
Testing and validation of the Repository
Understanding the process of Cache disabling
Dashboard prompt and filtering

Dashboard Creation

Creation and Setup of OBIEE Dashboard
Basics of OBIEEE dashboard
Deploying Dashboard Builder for building Dashboards
Edit, Share and Save options in Dashboard analysis
Process of Cache creation and clearing
ODBC functions
Logical Table Source, Summary and Detail Report

OBIEE Repository

OBIEE Repository
Creation of Test Report and adding calculations
Deployment of OBIEE analysis
Repository variables, session and presentation variables

OBIEE Security & Management

Securing the Oracle Business Intelligence Suite with Enterprise Manager
Creation of alerts and notifications
Administration, Maintenance and Grouping
Types of security in OBIEE
Task and folder level security

Differences between OBIEE 11g and 12c

Hands on Variables

New Features of OBIEE 12c

Repository Development
Catalog Development
Deployment-Online
RPD Deployment

661