OBIEE 11g vs OBIEE 12c – What’s new in OBIEE 12c

In this post I highlight a few of the new features of OBIEE 12c, and in some cases show how they differ from OBIEE 11g.

The OBIEE Home Page looks a bit different, and includes a new “Data Exploration & Discovery” option and functionality.



OBIEE 12c has a new visualization feature:  Mouse-over highlights the selected area with animation.

For example, the below image shows what it looks like when you mouse-over the “14.8% purple” slice.

Note: In reports where the selection drills on the entire stacked column (such as in a vertical stacked graph), the drilling will operate the same as before – that is – if the drill was done on the selected area only in 11g, then that will continue to occur; If the report was drilling on the entire stack/column when clicked in 11g, that will continue to occur also (even though the animation only happens on the area that was clicked).


The Dashboard “Page Options” icon has changed from the “3 lines with the down arrow” to a “gear” icon.



OBIEE 12c now provides the ability to Sort in graphs by right-clicking and using the pop-up menu. So, you will now see the Sort option along with the Action Links when you right-click on a graph.


For report developers:

In OBIEE 12c: It is now possible to modify Column Properties from the Results tab – more efficient.


In OBIEE 12c: There is a new “Scale for % (x 100)” option in the Column Properties – Data Format tab.


In OBIEE 12c: You can create a new Calculated Column in the Results tab by clicking the “ruler” icon.


After adding the column, use the new “Save Column As” option to save the column. This is great feature that will allow for re-using calculated columns instead of having to re-enter the formula each time.


OBIEE 12c introduces 2 new visualization options – Tree Map and Heat Index



OBIEE 12c introduces a new Global Variable type, that can be a value or an expression.

OBIEE 11g  Whats_new_OBIEE12c_Variable_Types_11g                OBIEE 12c Whats_new_OBIEE12c_Variable_Types_12c

In OBIEE 12c, there is a new “Subject Area Sort Order” option available in Account properties.



OBIEE 12c provides the ability to search a subject area by keyword entered, and to sort folders and columns in a subject area.

OBIEE11g  Whats_new_OBIEE12c_Subject_Areas_11g                OBIEE 12c Whats_new_OBIEE12c_Subject_Areas_12c

In OBIEE 12c, there are a few new analytic functions.  A new Analytics folder contains new functions Cluster, Evaluate Script, Outlier, Regr, and Trendline. The Aggregate folder contains a new function, Bin. And the Time Series Calculations folder contains a new function, Forecast.

Whats_new_OBIEE12c_New_Analytic_Functions_12c_1          Whats_new_OBIEE12c_New_Analytic_Functions_12c_2          Whats_new_OBIEE12c_New_Analytic_Functions_12c_3


Creating a Business Intelligence (BI) & Analytics Strategy and Roadmap

This post provides some of my thoughts on how to go about creating a Business Intelligence (BI) & Analytics Strategy and Roadmap for your client or company.  Please comment with your suggestions from your experience for improving this information.


When creating or updating the BI & Analytics Strategy and Roadmap for a company, one of the first things to understand is:

Who are all the critical stakeholders that need to be involved?

Understanding who needs and uses the BI & Analytics systems is critical for starting the process of understanding and documenting the “who needs what, why, and when”.

These are some of the roles that are typically important stakeholders:

  • High-level business executives that are paying for the projects
  • Business directors involved in the usage of the systems
  • IT directors involved in the developing and support of the systems
  • Business Subject Matter Experts (SME’s) & Business Analysts
  • BI/Analytics/Data/System Architects
  • BI/Analytics/Data/System Developers and Administrators


Then, you need to ask all these stakeholders, especially those from the business:

What are the drivers for BI & Analytics? And what is the level of importance for each of these drivers?

This will help you to understand and document what business needs are creating the need for new or modified BI & Analytics solutions. You should then go deeper to understand … what are the business objectives and goals that are driving these business needs.  This will help you to understand and document the bigger picture so that a more comprehensive strategy and roadmap can be created.

The questions and discussions surrounding the above will require deep and broad business involvement. Getting the perspective of a wide range of users from all business areas that are using the BI & Analytics Systems is critical.  The business should be involved throughout the process of creating the strategy and roadmap, and all decisions should tie back to support for business objectives and goals. And the trail leading to all these decisions must be documented.

Some examples of business drivers include:

  • Gain more insight into who our best customers are and how best to acquire them.
  • Understand how weather affects our sales/revenue.
  • Determine how we can sell more to our existing customers.
  • Understand what causes employee turnover.
  • Gain insight into how we can improve staffing schedules.


And examples of business objectives and goals may include things like:

  • Increase corporate revenues by 10%
  • Grow our base of recurring customers
  • Stabilize corporate revenues over all seasons
  • Create an environment where employees love to work
  • Reduce payroll costs without a reduction in staff, for example, reduce turnover.


Then, turn to understanding and documenting the current scenario (if not already known). Identify what systems (including data sources) are in place, who are using them (and why and how), what capabilities do they offer, what are the must-haves, and what are the pain points and positive highlights.

Also, you will need to determine the current workload (and future workload if it can be determined) of the primary team members involved in developing, testing, and implementing BI & Analytics solutions.

This will help you understand a few things:

  • Some of the highest priority needs of the users
  • Gaps in capabilities and data between what is needed and what is currently in place (including an understanding of what is liked and disliked about the current systems)
  • Current user base knowledge and engagement
  • IT knowledge and skills
  • Resource availability – when are people available to work on new initiatives


What are the options and limitations?

  • Can existing systems be customized to meet the requirements?
  • Can they be upgraded to a new version that has the needed functionality?
  • Do we need to consider adding a new platform or replacing one or more of the existing systems with a new platform?
  • Can we migrate from/integrate one system to/with another system that we already have up and running?
  • Are any of our current systems losing vendor support or require an upgrade for other reasons? Has the pricing changed for any of our software applications?
  • What options does our budget permit us to explore?
  • What options do our knowledge and skills permit us to explore?


Once you have identified these items …

  • Identify and engage stakeholders, and document these roles and the people
  • Identify and document business drivers, objectives and goals
  • Understand and document the current landscape – needs (including must-haves), technology, gaps, users, IT staff, resource availability, and more
  • Identify and document options – based on current landscape, technology, budget, staff resources, etc.

… you can develop a “living” Strategy and Roadmap for BI & Analytics. And when I say “living”, I mean it will not be a static document, but will be fine-tuned over time as new information emerge and as changes arise in business needs, technology, and staff resources.


Your Strategy and Roadmap for BI & Analytics should include, but is not limited to:

  • BI & Analytics that will be used to satisfy business drivers, objectives and goals
  • Data acquisition and storage plan for meeting the analytics needs
  • Technology platforms that will be used to process and store data, and deliver the analytics
  • Information about any new technologies that needs to be acquired or implemented, and schedules
  • Roles and Responsibilities for all stakeholders involved in BI & Analytics projects
  • Planned staffing allocations and schedules
  • Planned staffing changes and schedules
  • User training (business users) and Delivery team training (technical implementers & developers for example)
  • List dependencies for each item or set of items

QlikView Sheet Objects Quick Overview

This post provides a quick overview for each of the QlikView Sheet Objects.
Sheet Objects are the various objects that a developer or analyst places on a QlikView document’s sheet to provide the rich functionality of dashboards.

By right-clicking on an open area in a sheet, the below menu will appear.  Select “New Sheet Object” to see the available Sheet Object options – which are – List Box, Statistics Box, Multi Box, Table Box, Chart, Input Box, Current Selections Box, Button, Text Object, Line/Arrow Object, Slider/Calendar Object, Bookmark Object, Search Object, Container, Custom Object, and System Table.

The sheet objects can also be created from the Design Toolbar selections.

Some of our example images are taken from the Movies Database application that comes with QlikView.

List Box
The “List Box” is one of the most heavily used QlikView sheet objects.  It lists all the distinct values of a particular field. For example, you may have a List Box that displays all the products available within your data set.  The List Box is often used as a menu, where a user selects a value to analyze the data based on that value.
The above example displays 3 List Boxes – the Director list box, the Actor list box, and the Title list box. When a user selects one or more values from one or more list boxes 9or any other object), the data in all other objects cascade to show only records relevant to that combination of selections.

Statistics Box
The “Statistics Box” is used to show aggregated representations of the data.  There are several statistical aggregation functions that are available, such as Sum, Average, Min, Max, Std Dev, Null Count, and several more.
The above example shows “Statistics” for the Rating field – the number of ratings, the average rating, the min and max ratings, and the average and median ratings.

Multi Box
A Multi Box allows you to display several fields in a single object, with the values for each field available via a drop down. This object can be described as a set of List Boxes compacted together. The example screenshot below shows a Multi Box with the 3 fields, Director/Actor/Title, and by clicking any of the fields, you will get a drop down of all the values for that field – similar to a List Box for that field.

Clicking on Director will display the list of all Directors, as shown below. Similarly, you can Actor or Title to get those respective drop down lists.

Table Box
The Table Box, as the name implies, displays a table of records for the selected data fields.

The Chart object is used for creating charts of various types. Some of the chart types available include bar, line, combo, radar, scatter, grid, pie, block, funnel, gauge, and mekko charts, and pivot and straight tables.
The above shows one of the properties dialog tabs for the chart object – which shows some of the various chart types you can create.

Input Box
The Input Box object allows for users to enter a value. (which sets a variable that can be used for further processing)

Current Selections Box
The Current Selections Box displays all the selections that the user has selected. The Current Selections box is usually placed at the top of a sheet (dashboard page) in a position that allows users to easily see the selections that have been made.
Users are able to clear selections by clicking on the eraser beside the selection in the Current Selections Box, and the data on the sheet will change accordingly.
In the example above, the user has selected Rating 5 and Length Range 1 to 1.5 hrs.

The Button object creates a button that can be configured to execute various types of actions. When clicked the configured action will be executed.

Text Object
The Text Object is used for adding simple text to the document/sheet. It can be used to display informational messages, directions, descriptions or titles and labels, or any kind of information that needs to be shared with viewers/users of the document.

Line/Arrow Object
This object is used to create a line with or without arrows, and vertical or horizontal or diagonal. This object can be used for many purposes such showing relationships and flows.

Slider/Calendar Object
The Slider/Calendar object is used to create either a slider with multiple values that the user can slide across to select, or a calendar that the user can use to find and select a date.
Qlik_SheetObject_Slider                 Qlik_SheetObject_Calendar
In the above slider, the user has selected Rating 4.  In the calendar object, clicking the grid icon opens a calendar that allows selection.

Bookmark Object
The Bookmark object is used to save the selection state of the current sheet. If a bookmark is created after a specific Actor has been selected, when you select that bookmark, the selection and corresponding data in the sheet will be displayed based on that saved selection state of the bookmark.
Multiple bookmarks can be saved for each sheet. Each having a different set of saved selections.

Search Object
The Search Object allows users to search for values in entire data set or within a specific set of fields.

The Container is a sheet space saving object that can contain multiple other objects, such as multiple chart objects. This allows you to display multiple views of the data within the same sheet “real estate” and change the view by clicking on the appropriate tab to get to each chart.
In the above example, the container contains 3 objects (3 charts), and therefore displays 3 tabs across the top, one for each chart. There are different possible orientations for the tabs – they can be to the left, right, top (as in this example), or bottom.

Custom Object
The custom object is a sheet object specifically intended to carry custom defined OCX replacement controls.
The Custom Object: Object Menu can be accessed from the Object menu, when the custom object is the active object.
Replacement controls are windowless OCX controls programmed by Qlik, yourself or third party vendors, which comply with the specifications for QlikView replacement OCX controls. The OCX control will be drawn in a rectangle on the sheet outlined by the underlying custom object. Communication between the OCX control and the QlikView document is maintained via the internal macro interpreter.

System Table
The System Table object creates a pivot table that shows the data structure of the qlikview document.

In future posts, we will be covering each object in depth.

Oracle Business Intelligence (OBIEE) Interview Questions and Answers – Set 1

These are a set of questions and answers to help you prepare for interviews for roles involving Oracle Business Intelligence (OBIEE).  I recommend that you do not simply try to memorize these questions and answers, but use them as a guide or to help you determine what you need to work on more to improve your knowledge and skills.
IMPORTANT DISCLAIMER: I cannot guarantee the correctness of any of these answers, and anyone using them should verify their correctness using other sources.

Q1. The business users mention that a particular report is not returning the correct results. How would you go about identifying if there is an issue and what the issue is?

A1. The answers to this question could vary widely because there are a few options of what you may do first, second, etc.

I would first determine why the users think the results are wrong.  Compare their expected results with the report results to determine what data values are being dropped or added. This may require a detailed-data to detailed-data comparison.

Next, I would determine when the “wrong results” started up.  Based on that, I would check if anything changed within the timeframe that could have affected this. If anything changed, you or another team member can investigate the details of the change.

Next, I would try to determine if the data is correct by comparing the source system data with the data from the report source, such as the data warehouse.  If the data in the data warehouse is correct, then it would indicate that something might be wrong with the report.  if the data in the data warehouse is not correct, then that indicates there might be a problem with the ETL process or logic.  Check filters, aggregation logic, selection steps, and more in the area that needs further examination (whether the analysis or the ETL).

If necessary, I would get the SQL generated by OBI for the analysis via the session logs, and run that SQL directly on the database, removing or adding to the SQL as necessary to investigate various scenarios with the data.

This could be one of the first things that you do, but if I had not found the issue as yet because everything looks good so far, clear the cache and see if that resolves the issue.


Q2. Can you create an analysis from multiple subject areas? And if yes, how would you go about doing it?

A2. Yes, you can create an analysis/report using multiple OBIEE Subject Areas.  First create an analysis as normal, by selecting the first subject area and then selecting the desired columns, and performing any desired calculations, formatting, or other manipulations on those columns.  Then, from the Subject Areas pane, click the Add Subject Area icon (cube with a plus sign) and select the second subject area, from which you will then select the desired columns.  You will need to union or join the data from these subject areas.


Q3. What is the purpose of the OBIEE RPD?

A3. The OBIEE RPD (Repository) is a metadata layer between the data sources (such as a relational databases or files) and the OBIEE front-end that is accessed through a web browser, which includes the Dashboard & Analysis Editor used by report developers or analyst, along with the published dashboards & analyses (reports) that the users see.  The RPD allows developers to create a business representation of the data, and create a business friendly view of the model, including renaming of columns to business friendly vocabulary, creating new data elements (such as metrics) from calculations and manipulations, defining hierarchies useful to business processes, and more.  This allows report developers and power users and analysts to be able to drag and drop columns to create analyses (reports).


Q4. Name and describe the various layers of the OBIEE Repository (RPD).

A4. There are 3 layers in the OBIEE Repository (RPD).  The Physical layer, the Business Model and Mapping layer, and the Presentation layer.

The Physical layer is where you define the data sources, including connection details, that you will use to source data for your OBIEE environment.  In this layer, you will import or define your table metadata, create aliases (a recommended practice), define the joins between tables (typically using the alias tables), create opaque views (“select” tables), and set caching options.

The Business Model and Mapping layer, referred to as BMM or logical layer, is where you will define the business model of the data from the physical model.  The business model is geared toward providing specific information needed for your specific business scenario.  The business model typically simplifies the representation from the physical model to form a more business friendly view of the data.

The BMM layer is where you will rename objects to more business friendly names, create business metrics from the data, create hierarchies useful for various business processes, define logical tables and columns and joins,

The Presentation layer is where you define the view seen by users in the front end reporting and analysis tools, such as, OBIEE Answers.  This layer allows you to structure/organize/label all data elements from the BMM layer into an easily understood, business friendly model – further simplifying the BMM model and making it more business friendly – that facilitates drag and drop usage for end users.


Q5. What are some of the types of analysis views that are available in OBIEE?

A5. Some of the types of analysis views available in OBIEE are:  table (straight table), pivot table, graph, funnel, gauge, trellis, filters, column selector, view selector, narrative, ticker, and static text.


Q6. What are some of the graph types available in OBIEE?

A6. Some of the types of graphs available in OBIEE are: bar (vertical, horizontal, and stacked); line; line-bar; area, pie; pareto; scatter; bubble; radar


Q7. Describe the steps for creating an analysis?

A7. Understand the requirement. Confirm that the data elements are available.  From the menu, New -> Analysis.  Select the appropriate subject area.  Find the columns that you need.  Bring them into the report.  Perform calculations and other data manipulations as necessary on one or more columns.  Rename and format columns as necessary.  Create the data views that provide the best representation of the data and/or that meets users’ requirements.  Verify the results by testing various scenarios – such as different time frames, different data elements, testing with prompt selections, and all the elements that need to validated to confirm you are meeting the users’ requirements.


Q8. What are the different types of variables in OBIEE?

A8.  There are two types of variables available in OBIEE and they are: (1) repository variables and (2) session variables.

Repository variables can have only a single value at any point in time, and are system-wide (repository-wide), hence the name Repository variable.

Repository variables can be used in ways similar to how you would use a constant or literal value in expressions in the RPD or in an analysis.

Repository variables have two sub-types: (i) static and (ii) dynamic

A static repository variable has a fixed value that is defined in the variable definition in the RPD (OBIEE repository), and stays that way until changed by a developer/administrator.

A dynamic repository variable (as the name implies) changes (is refreshed) based on the results returned from Initialization Block SQL queries that run on a defined schedule.

Session variables can contain more than one value and are created and assigned a value “for each session” when each user logs on, hence the name session variable.  Each user’s session variable may be different depending on the logic used to generate the value for the variable.

Session variables have two sub-types: (i) system and (ii) non-system

System session variables are special variables used by OBIEE for specific “system” purposes and the same variable names cannot be used for other variables. An often used system session variable is “USER” that gets set to the value of the current logged in user’s ID.

Non-system session variables are custom defined variables, typically set by an initialization block.  An often used non-system session variable scenario is one in which the variable values for each user is used in data filters to implement dynamic data-level that changes for each user.


Q9. What is an Initialization Block?

A9. An Initialization Block (Init Block) is an object defined with a “block” of SQL that is executed to “initialize” a variable specified in the Initialization Block’s definition. Init Blocks are used to initialize dynamic repository variables, system session variables, and non-system session variables.


Q10. How do you refresh the cache in OBIEE?

A10. One of the quickest ways is to run the “call SAPurgeAllCache();” statement in the Administration -> Issue SQL window.

You can get more details here …


Q11. How do you create navigation from one report to another based on the user clicking on a data value in the first report?

A11. You would create an Action Link on the navigate-from column (in the Interaction tab of the column properties) in your first report. In the Action Link, set the appropriate action, such as “Navigate to BI Content”, to specify the second report that you need to navigate to.


Q12. Describe the steps involved in building an OBIEE repository (RPD).

A12. The steps involved in building an OBIEE RPD can be separated into 3 sets of steps: (1) Build the Physical Layer, (2) Build the Business Model and Mapping (BMM) Layer, (3) Build the Presentation Layer

(1) Build the Physical Layer

  • Create the repository
  • Import metadata
  • Create aliases
  • Create physical keys and joins between the appropriate tables

(2) Build the BMM Layer with objects from the Physical Layer

  • Review and adjust (if necessary) the Logical Joins
  • Rename logical columns
  • Add logical table sources (as necessary)
  • Create derived columns
  • Create metrics
  • Remove unneeded logical objects
  • Create hierarchies

(3) Build the Presentation Layer

  • Create a Subject Area
  • Create or drag over Presentation tables
  • Create Presentation columns
  • Rename Presentation columns
  • Rearrange/organize Presentation columns into a user friendly view

Then, upload and test the RPD using analyses created in Answers.


Q13. Why is it recommended that you use Alias Tables in OBIEE?

A13.  Alias tables are defined in the Physical Layer of the RPD.  They are used to create a version of a physical table with a different user determined name, therefore allowing for the re-use of tables for multiple joins/data sets within the physical layer.  Another benefit of aliases is if there is a change to the physical table, in some cases those changes can be isolated by, for example, mapping the new columns in the physical table to existing columns in the alias, and preventing the need for other changes to the data model and in the various layers of the RPD.


Q14. How would you go about resolving performance issues with a specific report in OBIEE?

A14. Run the report through the dashboard.  Capture the SQL associated with the report.  Run that SQL directly on the database (using a tool such as SQL Developer or Toad) to see if it is performing poorly there also.  If it is, then we can deduct that the issue is on the database side or the report needs to be changed enough to make it generate a different SQL.  If it runs fine directly on the database, then the issue is somewhere else along the stack.

Taking the first scenario – runs poorly directly on database – review the SQL or run an explain plan on the SQL and determine what changes can be made to improve it.  This may involve adding indexes to tables on columns used in joins and in filtering criteria; reducing records in tables as appropriate before joining; removing unnecessary joins; changing the data model of the tables used, such as creating star schemas or creating aggregate tables. If necessary, work with a DBA to get help.

Taking the second scenario – runs fine directly on the database – review the analysis to determine what type of views are being used and determine by elimination if any of them are causing an issue; play around by removing columns and re-running to determine if any specific columns or calculations are causing an issue; check the logs to see if there any relevant messages to your scenario and adjust configuration parameters accordingly and re-run to determine if any effect.


Q15. What would you do if you are unable to figure out an OBIEE issue?

A15. There could be several reasonable answers to this question. A few good responses include … ask a co-worker, use a search engine (google/bing/etc) to try to find a solution, clear the cache, restart all processes at an appropriate time, search Oracle’s support site, create a Service Request (SR) with Oracle Support, post a description of your issue to relevant online groups/communities and ask for help, (when appropriate) meet with others in your environment to try to determine what has changed that you are unaware of that may have caused the issue. There could be many other valid responses.


Q16. What are some recent OBI dashboards that you have created?  -OR- Please describe some recent OBI projects that you have worked on.

A16. There are many ways to answer these open ended questions, but a few things I would suggest are:

  • describe the project
  • describe your role in the project
  • (where applicable) briefly describe your development process/methodology
  • (where applicable) describe how you worked with the business users to determine or review the requirements, perform training, perform validation, resolve issues, etc.
  • describe how you sourced the data (source systems)
  • describe how you designed and/or developed the solution (include some details without being too long, such as explaining what areas you designed/developed – data model, and/or RPD and reports, or just RPD, or just reports, etc.)
  • describe any challenges you ran into, and how you/team resolved
  • describe how you may have assisted others or worked with others or trained others
  • as you describe all the above, make sure it demonstrates what you brought to the project
  • And then finally, share the end result – for example, share if the users loved the solution and the kind of feedback that made you know that, what it helped them to do, if it saved them a lot of time, if this led to increased application usage, etc.


Q17. How do you move/migrate an OBIEE solution from one environment to another, such as, from your DEV to TST environment?

A17. The answer to this question could vary a bit, but may include things such as:

  • Use the same scripts from DEV to create any new database objects in TST.
  • Use Archive/Unarchive to move OBIEE catalog objects by Archiving the objects from DEV and unarchiving them into TST  -OR-  Use the Catalog Manager tool to move the catalog objects from DEV to TST.
  • Take the RPD from DEV and upload and activate it in TST  -OR-  merge the approved RPD changes from DEV into the TST RPD
  • Apply the appropriate security permissions to the objects in TST.
  • If there was a new ETL process involved in the solution, ensure that the ETL objects are also migrated to the ETL TST environment.
  • Restart the TST environment servers
  • Validate that everything is good, and if not, resolve by migrating anything that’s missing


Q18. How do you implement data-level security in OBIEE?

A18. First, determine how each user’s data-level access will be identified, that is, determine what table will house the data that specifies the access that each user  has to the data.  For example, if the data is to be secured by department, the table would contains records of each user and the department(s) that they have access to.

Then, create an Initialization Block that selects the departments for each user and assigns them to a session variable (DEPT_VAR).

Next, identify the appropriate roles for which the data-level security rules need to be applied, and set the filters (table.department = ‘DEPT_VAR’) on the appropriate data sets using the above variable.

Test the solution.

You can get more details here …


Q19. What is an Agent?  And when would you use it?

A19. An Agent (formerly called iBot in OBIEE 10g) is a scheduled or conditionally triggered process that runs and executes a specified report (analysis) based on hitting the schedule or condition.  Once the Agent runs, the analysis results can be sent to a user via email (attached or embedded), or to the dashboards in the form of an alert that the user will see when he/she accesses the dashboards. So, agents can be used to provide analyses’ results to specified users on some specified schedule or condition without any manual intervention.  Another use of Agents is, the can be used to seed the OBI cache over night after the nightly ETL has completed, to make the reports faster for the first set of users in the morning.


Q20. What are some functions that you have used in OBIEE Answers to manipulate column data?

A20. There could be wide range of answers here, but some of the commonly used functions include:

  • Aggregate functions, such as, MIN, MAX, SUM, AVG, COUNT, TopN
  • Mathematical functions, such as, ROUND, FLOOR, TRUNCATE, ABS
  • Datetime functions, such as, CURRENT_DATE, TimeStampAdd, TimeStampDiff, Year, Month, Now
  • Conversion functions, such as, CAST, IfNULL, CASE

However, your response should include the functions you have used, and be able to explain how you used them.


Thanks for reading.  More sets will be available in the future. Good luck!


Implementing Reports-To data-level security in Oracle BI (OBIEE)

In a previous post, Implementing data-level security in Oracle BI (OBIEE), I described data-level security and how to implement it in Oracle Business Intelligence (OBIEE).  In this post I will describe a special type of data-level security, called Reports-To security, and how to implement it in OBI.

For Reports-To data-level security, we want to secure data in such a way that we allow a user access only to data for his/her direct and indirect reports. In other words, each user will be able to see data only for people that are below him/her in the organization hierarchical chain.

Take a look at this example diagram:


If Reports-To security is applied to this example, Position# 303 would only be able to see information for Position# 409; and Position# 305 would only be able to see information for Position#’s 410, 411, 412; and a final example, Position# 201 would be able to see the information for Position#’s 303, 304, 305, 306, and 409, 410, 411, 412.

I use “Position” as the driving entity in the hierarchy instead of “Employee” because there are times when a position is vacant (no employee) and so it’s better to use the position which will always have a value.  However, you can use Employee if that works better in your scenario or if that’s what your data supports.

Let’s move on to how to implement this type of security.  The steps are similar to the steps in a previous post, Implementing data-level security in Oracle BI (OBIEE), but with some key differences.  (Refer to that post for some of the more detailed steps not reiterated in this post.)

First, build a Reports-To data table and create the necessary ETL to ensure that it remains correct and up-to-date.  This table will contain each position (employee/user) and what position (employee) they report to. The data for this table will likely come from your HR system (such as PeopleSoft, Oracle EBS, SAP, Workday, home-grown system, etc.) that contains all the position and employee data.  Using the Organization Position Hierarchy diagram example, the table (REPORTS_TO_DATA) may look something like this:


Next, create a Session Initialization Block (Init Block) with row-wise Initialization that will be used to get the list of all positions that report to the position of the current user and store them in a defined Target Variable.  If you log in, the Init Block will generate the list with all the positions (or employees) that report to you; and when Jane logs in, the Init Block will generate the list of all the positions (or employees) that report to her.

An important component of the SQL in the Init Block is that it needs to be recursive, because for each person, it needs to retrieve their direct reports, and then retrieve the people reporting to their direct reports, and so on down the line.  Using the above Organization Position Hierarchy diagram example, when the user in Position 202 logs in, the SQL needs to retrieve the positions reporting to 202 (which are 307 & 308), and then recursively retrieve the positions reporting to 307 and 308, and so on. The Target Variable used for storing the values in this example is: REPORTS_TO_POSITIONS

The Init Block, its SQL, and variable definition may look something like this:



Then finally, we need to create the data filters on the appropriate data sets (that need to be secured) using the variable containing the “list of positions” reporting to the current user (REPORT_TO_POSITIONS variable).  The needs to be done for each role that will access the reports that need to be secured by Reports-To security.


After this is all set, then Reports-To Security will be in effect for the filtered data sets and the reports that use them.

If you need to make it such that each user can only see data for his or her direct reports, the SQL can be modified to remove the recursion, and just return the direct report positions.

One final point … as you would with all changes, but particularly with solutions involving sensitive data, test your solution thoroughly – including making sure to perform both positive and negative testing.

Thanks for reading!


Implementing data-level security in Oracle BI (OBIEE)

Data Level Security involves securing the data available in an application in such a way that each user will see only the data that he/she is authorized to see, resulting in each user possibly seeing different results on the same report.   In this post I will describe how to implement data-level security in Oracle Business Intelligence (OBIEE).

Let’s use an example to describe data-level security.  Each user of the BI system works in or is assigned to a particular Business Unit.  Each user is allowed to see only the data for his or her assigned Business Unit.

In our example, the below table lists the 4 users and the Business Unit that each of them works in or is assigned to, and therefore, should have access to.  We will call this the USER_TO_BUSINESSUNIT table.

Jane and Xing should only be able to see data for Business Unit BU2000, Bill should be able to access data for both BU3000 and BU4000, and Venkat should be able to access data for BU4000.

Now, we will use the below table as the example data set that we need to secure with the Business Unit data-level security.  We will call this table TRANSACTION_DATA.

When data-level security is applied …

Jane and Xing will be able to access/see the following data:

Bill will able to access/see the following data:

And Venkat will be able to access/see the following data:

So, now let’s move on to how to implement data-level security in OBI to achieve what was described above.

First, ensure that the USER_TO_BUSINESSUNIT table data is correct and up-to-date, and that there is an ETL in place or some other method of keeping that data updated. You want to ensure that if and when a user’s Business Unit changes, it is reflected in this table so that the user will have access to the appropriate data.

Next, create a Session Initialization Block with row-wise Initialization that will be used to get the list of Business Units that a user has access to.

Open the RPD -> Manage -> Variables

In the Variable Manager -> Action -> New -> Session -> Initialization Block

This needs to be a “Session” Init block so that it will run each time a user logs in, and gets that user’s list of Business Units; and it needs to be row-wise because some users will have more than 1 value returned.


In the Session Variable Initialization Block Dialog, enter a Name for the Init Block.

Then click Edit Data Source

In the Data Source dialog, enter the SQL to get the Business Units for the current logged in user.  Click OK when done which closes this window and brings you back to the Session Variable Initialization Block Dialog.


Click Edit Data Target in the Session Variable Initialization Block Dialog.

Enter your Variable name and check “Row-wise initialization”. As mentioned above, we need to select row-wise because our Init Block SQL may return more than 1 value for some users.   For example, when Bill in our example above data logs in, the Initialization Block will return values BU3000 and BU4000, and store them in the Target Variable, “BUSINESS_UNIT”.

You may also check “Use caching” to store the values in cache. Click OK when done.

Then click OK to save the Init Block.


Next, apply data filter(s) to the appropriate data set(s) for the appropriate role(s) using the Target Variable above.  You may have role(s) specifically used for data-level security and will need to apply it there, but if not, you will need to apply the filters in each role that has access to the datasets/dashboards/reports that you want to apply data-level security to.

Manage -> Identity

Go to the Application Roles tab, and select the Application Role to which you would like to apply the data-level security.  In the APplication Role dialog, click Permissions.

In the Permissions dialog, select the layer and data table that you want to apply the data security to, and then enter the appropriate filter.  In this example, you are filtering by BUSINESS_UNIT.  This will cause the data to be filtered to only include each users’ Business Units.

Save your changes.  You have now applied data-level security.  This is what will happen now:

User logs in -> Init Block runs and selects the Business Units associated with the user’s User ID -> Init Block assigns value(s) to the variable BUSINESS_UNIT -> if the user is a member of a role that has data security applied to -and- the user visits the report -> the data filter will be triggered/run -> User only sees data for the Business Units the user is allowed to see.

Look out for my upcoming post on implementing a special type of data-level security: Reports-To Data Level Security.

Thanks for reading!

Terminology Primer – Goals, Objectives, Measures, Metrics, Key Performance Indicators (KPIs), Meters

The essential purpose of business intelligence is to provide information that will allow people throughout an organization to make informed decisions relevant to their business processes and responsibilities.  This post is a simple terminology primer that describes the meaning of a few terms commonly used in business intelligence, and explain how they relate to each other, and their relevance to supporting the overall goals of an organization.

Goals are a business’ desired outcomes. They are typically around growth, cost savings, innovation, improvement in efficiency, the company’s workforce, and the competition, but may include a lot of other things.
Goals help a company to stay focused by providing team members within a company with an aim to work towards.

Objectives are specific strategies and steps that a business needs to take to achieve the goals that have set. These objectives are usually specific and measurable.  Success toward achieving objectives usually indicates progress toward achieving goals.
Objectives are sometimes referred to as Critical Success Factors because they are critical to the success of achieving the goals.

Measures are numeric representations of various transactions that occur through various business processes. For example, when the company makes a sale (during the sales process), some measures that are generated in that transaction are:  sales amount, discount amount, number of items sold, and number of items discounted.  Then from this, other measures can be determined, such as total sales for all customers, total number of items sold, total number of sales for each customer, and so on.  Measures are numeric and therefore can have mathematical calculations performed on them – such as sum, avg., min, max, etc. – to generate metrics.

Metrics are calculations derived from one or more measures. For example, as mentioned in an example above, you may have the measures “Discount Amount” and “Number of Items Discounted”, and you may use these measures to calculate a metric of “Average Discount Amount per Discounted Item” or “Average Discount Amount per Sale”.  As another example, you may simply add up all the Discount Amounts over a specific time period, such as month, to get a “Total Discount Amount by Month” metric.

Key Performance Indicators (KPIs) are metrics that measure how well a company is doing toward their objectives. Companies will have hundreds or thousands of metrics, but there will be a few key ones that the executive team wants to keep a close eye on for the overall company or divisions, and other managers will want to keep an eye on KPIs relevant to their respective departments.  Those key metrics are the KPIs. All KPIs are metrics, but not all metrics are KPIs.

Meters are a group of metrics that collectively provide a broader, overall view of a subject area.
For example, you may have individual metrics for Sales to Date, Sales in Pipeline, Number/Value of Expiring Contracts, Avg. Time to Close Sales, etc. Putting these all together in a Meter presents the user with a lot of related information that provides a broad, overall picture of sales (and loss of sales) which would allow for analysis such as determining the chances of meeting sales targets. The Meter in this example could be called “Sales Forecast” for example.

Thanks for reading!