Advertisements

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 … https://businessintelligence.technology/2013/10/11/how-to-clear-the-bi-server-cache-using-command-line-script-or-via-the-issue-sql-page/

 

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 … https://businessintelligence.technology/2017/08/10/implementing-data-level-security-in-oracle-bi-obiee/

 

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
  • String functions, such as, CONCAT, LEFT, RIGHT, REPLACE, SUBSTRING, TRIMBOTH, UPPER
  • 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!

 

Advertisements

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.
DataLevelSecurity_UsersBUs

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.
DataLevelSecurity_AllData

When data-level security is applied …

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

Bill will able to access/see the following data:
DataLevelSecurity_BU3000_and_BU4000

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

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
ManageVariables

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.

New_Session_InitBlock

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

Then click Edit Data Source
InitBlockDialog

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.

InitBlockSQL

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.

SessionInitBlock_RowWiseTargetVariable
Then click OK to save the Init Block.

InitBlock_SetupComplete

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
ManageIdentity

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.
IdentityManager_ApplicationRole

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.
DataFilter

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!

How to generate detailed Oracle BI (OBIEE) Repository Documentation

In this post, I will show the steps for using the OBIEE “Repository Documentation” utility to generate repository (RPD) lineage information.  I will also provide a couple example of how this documentation (output file) can be used.

To access and run the Repository Documentation utility,  from the BI Admin Tool menu, select Tools -> Utilities.

biadmintool_menu_tools_utilities

From the Utilities dialog, select “Repository Documentation”, and click “Execute…”

utilitiesdialog

In the “Save As” dialog, select the destination and enter the name you would like for the output file.

saverepositorydocumentationdialog

When it finishes, it will generate the output csv file.  Note  – this will likely be a large file.  It will contain all your repository objects.

obieerepositoryoutputfile

The RPD documentation file will contain the following columns:
Subject Area, Presentation Table, Presentation Column, Description – Presentation Column, Business Model, Derived logical table, Derived logical column, Description – Derived Logical Column, Expression, Logical Table, Logical Column, Description – Logical Column, Logical Table Source, Expression, Initialization Block, Variable, Database, Physical Catalog, Physical Schema, Physical Table, Alias, Physical Column, Description – Physical Column

You can use this file to quickly track lineage from physical sources to the logical columns to the presentation columns and identify all the logic and variables in between.
You can also use it to identify where and how much a specified table, column, variable, etc. is used which will help you to identify dependencies and know the effect of making changes or deleting elements.

Development, Data Governance, and Quality Assurance teams may find this information useful in this format.

OBIEE data source types and data retrieval methods

OBIEE is capable of connecting to and retrieving data from a variety of data sources.  The type of data sources that OBIEE can connect to are OLTP, OLAP, Data Warehouses (ROLAP), and Files.

  • OLTP databases – these include the normalized-design databases including ERP, CRM and other LOB systems.
    – The relational databases supported are: Oracle databases, Microsoft SQL Server, IBM DB2, and Teradata Warehouse.
    – And the ERP/CRM sources supported are: Oracle E-Business Suite, Oracle Peoplesoft, Oracle Siebel CRM, Oracle JD Edwards, and SAP.  Note: any ERP/CRM system running on one the databases mentioned above can be supported, but those mentioned here are special ERP/CRM sources.
  • OLAP databases – these include dimensional-databases including applications based on dimensional databses.
    – The OLAP datases supported are: Oracle Essbase, Oracle OLAP, Microsoft Analysis Services, and SAP Netweaver BI.
    – And the OLAP applications sources supported are: Oracle Hyperion Planning and Oracle Hyperion Financial Management.
  • Dimensionally-modeled data warehouses – these are relational databases designed with a star-schema / dimensional model, on one of the 4 supported relational databases mentioned above.
  • Files – Microsoft Excel, XML files, Flat files.

The data retrieval methods used to connect to these sources are:

  • OLTP – SQL
  • OLAP – MDX
  • Data Warehouse – SQL
  • Files – ODBC

OBIEE has the ability to connect to multiple of these data sources at the same time, and the data sources can be of the same or different types.  So, for example, an OBIEE Server can source data from an Oracle 11g Data Warehouse, and from an Oracle Essbase 11g OLAP cube at the same time, and join the data together for user consumption.
Similarly, file datasources can also be added to provide additional information, for example from an external source, and joined to data from the other sources mentioned above.  This “joining” of data is handled by the OBIEE BI Repository and BI Server.

To the end user accessing the data from a front-end tool (Analysis Editor / Answers), it seems like a single data source. That is one of the features that makes OBIEE such a great tool particularly for heterogeneous database environments.

Developing requirements for an OBIEE project

Eliciting and creating requirements for an OBIEE project is a very important step in creating a successful, pervasive OBIEE system in an organization.

Throughout the requirements elicitation and creation process, you need to keep in mind that all requirements must be testable.  The only way to verify if a requirement has been met is to successfully test it, and therefore, all requirements must be specific and detailed enough to allow for a QA person to verify it.

A huge and essential component of OBIEE projects is the reports being delivered in one form or another – and therefore, another set of characteristics to keep in mind are that the reports and their form of delivery need to be: accurate, relevant, timely, and actionable.

Typically an OBIEE project involves significant effort, and can take several months to complete, but visible progress can be made in a shorter time.  Requirements may need to be prioritized to handle the most critical ones first (a phase 1 for example), and postpone some for later in the project – a phase 2 for example.  However, it should not take months to see some results, because OBIEE is a great platform for an agile methodology – allowing the project to show some results early and ongoing, as the project becomes more and more completed.

To elicit requirements, there are a number of methods that can be used.  You will need to choose the most appropriate method based on the particular scenario – who is the user, what area does the requirements cover, etc.  Some of the methods used can include: interviews, observation, reviewing existing reports (from a previous system for example); soliciting information from colleagues in other companies; and developing/showing report concepts and getting feedback; brainstorming – from strategic goals and reporting needs to tactical/operational.  However, you should try to learn as much as possible about the business, processes and people beforehand; and always try to be a good listener.

Requirements for an OBIEE project can be grouped into the following groups of questions:

What information does the business users need to see? 

This is often driven by the company’s strategic goals. The data needs to be in aid of answering business questions that users will need to aid their decision making in order to realize operational and tactical goals that support the strategic goals.

The information could be enterprise wide, departmental, or specific subject matter.

The reporting requirements could also be classified as strategic, tactical, or operational.  The strategic requirements are usually enterprise wide, while the tactical and operational requirements are usually relevant to a departmental, group or individual role.  Strategic requirements can at times be monitored and tracked via Key Performance Indicators (KPIs) which can be developed and presented in OBIEE.  Operational requirements at times will need Agents or iBots that trigger some action based on an event.  And tactical requirements are usually satisfied using reports that display valuable metrics about the business operations.

What are your business objectives and what metrics will help you to monitor progress toward those objectives?  What information do you wish you had to do your job better?

Where will the data be sourced from?  In other words, what are the source systems?

The answer to this question could include Data warehouses or data marts, ERP systems, Line of Business systems (LOBs), Flat files, External sources, OLTP, OLAP, etc.

The data sources need to be defined in the OBIEE BI Repository (RPD) via Connection Pools, and the metadata for the relevant tables imported.  OBIEE data sources can be relational (OLTP), multi-dimensional (OLAP) or files (Excel, XML, ADF).  The OLAP data sources supported by OBIEE are Oracle Essbase, Oracle OLAP, Microsoft SQL Server Analysis Services, and SAP BW.

However, for better performance, it is best if the data sources are multi-dimensional – either star-schema relational or OLAP.

What data is required from those systems?  And what data needs to be calculated or derived?

Analysis needs to be done to determine what subset of data (if not all) is needed from each of the source systems. What measures, dimensions, hierarchies, and attributes are required? What lookup tables are required?

And it’s always a good idea to ask “Why?”  Why is this data needed?  How will it be used?

This involves reports, and it is important to keep in mind that all report/reporting data need to be accurate, relevant, timely, and actionable.

What data that is not in the source system but can be derived? Calculations, Associations, mappings, etc – these derived items can be created in the OBIEE repository BMM layer, and exposed to users as necessary.

What granularity of data is needed?  Summary, Detail, both

What time range (including the time granularity) of data is needed?  Historical, Current, Real-time, Day, Month, Quarter, Year

What KPI’s are required to track the state of the business?

What data needs to be filtered out/in from each data source tables in the various scenarios?

What are some of the frequently used filter criteria?  à this could drive some of the repository variables created in OBIEE

What are some frequently used values for analysis?  à this could drive dashboard prompts in OBIEE

Will the business users need to perform data mining or need the results of data mining?

How frequently does the data need to be updated?

If the data is not directly connected to the source, then how often should the data be updated – real-time, hourly, daily, weekly, monthly, on-demand, etc?

Who needs to see what data?  And who needs access to what functionality?

This is in essence a security question.  What are the various groups/roles that need access to data, and what data should each group/role have access to?

Can the reporting system be integrated with the company’s existing LDAP? This is typically the case for most modern reporting systems including OBIEE which integrates with popular LDAP systems including Active Directory.

Does row-level security need to be implemented?  OBIEE allows for row-level security.

Can all users use all features of the reporting platform?  Or will only specific users be granted access to specific functionality?

What dashboards and reports will each group of users be able to see?

How will the information be shared with business users?  What modes of information delivery need to be used?

Will reports be shared?  email, saved to a directory, web dashboard, file (pdf, word, excel, html)?

Do users need to be proactively notified of events? – for example, a user or group needs to be notified if stock levels fall below a threshold.

The answers to this question will drive the Agents/iBots that need to be created.

Will the reports be run on a predefined schedule or based on some predefined condition?  Or will they be run on-demand?  This will also drive Agents/iBots and Conditions.

Do users need to download information?  This will drive the ‘report links’ that are placed on the dashboard pages.

Do report results need to be preserved or can/should they be overwritten?

Will users be allowed to create their own analyses or perform adhoc analysis? And if yes, how will that activity be monitored and supported?

What visualization features are required for each report or set of data?  Dashboards, Scorecards, Charts, graphs, tables, pivots, gauges, icons, colors, fonts, etc.

Will the users need to be able to drill from summary to detail reports?  Rollup from detail to summary?

Will the users be able to interact with the data?  Prompts, View Selectors, Column Selectors, etc

What are some of the system level requirements?

What level of system performance is required?

Dashboard and report creation tools

Does the reporting system need to be able to access/connect to multiple data sources at a time?  OBIEE allows for multiple data sources connected at the same time.
Does the reporting system need to be able to access/connect to relational, multi-dimensional, and file data sources?

Does the reporting system need data mining capabilities?

Does the system need to support drill-down, rollup functionality?

What are the critical usage times for the system?  In other words, what are times when the system must be available? For example, during the month-end close process or during the holiday sales season.  This will drive when changes can be made to the system.

What are the highest usage times for the system?  What hardware do we need to support that usage?

How will changes be handled? In other words, what is the change control process?

It is very important that all the relevant players are included in the requirements process – business leaders and business users, SMEs, technical staff, database administrators, OBIEE Developers (report developers, rpd developers), OBIEE architect, ETL developers, ETL architect.  Before development officially starts, it is important to get all relevant sign-offs on the requirements.  This will ensure that everyone is on the same page, and that the business users are getting what they need.

This post will be a “living” document, as I will be coming back and updating this post from time to time to add more detail and more OBIEE specifics.

OBIEE Repository (rpd) development tips

A few tips on BI Repository (RPD) Development …

Physical Layer

  • Import metadata when possible, instead of creating it manually
  • Only import objects that you need or forsee you may need in the near future
  • Import Tables, Keys, and Foreign Keys – which is the default option
  • Create Aliases of Physical Tables – to eliminate unwanted joins and to satisfy any naming conventions you may have chosen
  • Use the Physical diagram to configure joins
  • Create all relevant joins in a star-schema / dimensional model

BMM Layer

  • Use terminology that users are familiar with – words of the business instead of technical physical table and column names
  • Use the Rename Wizard to rename most of your logical objects when possible (this can save you a lot of time)
  • Delete unnecessary objects from your BMM layer
  • Create derived columns for calculations that you forsee using in multiple reports/presentation areas

Presentation Layer

  • Only move the columns you need users to see and work with to your Presentation Layer
  • Rename objects to match the language of the business and ensure that the meaning is very clear
  • Organize your presentation columns in a logical/meaningful order – for example, in a hierarchical order or alphabetic order such as Product Category, Product sub-category, Product -or- Region, Country, State, City, Zip

General

  • Check Global Consistency & resolve warnings and errors
  • Disable caching during development & testing
  • Verify your design by checking the SQL generated and sent to the database

Data Federation, Horizontal Federation and Vertical Federation in OBIEE

Data Federation is the process of and implementation of integrating metadata from multiple data sources into a single metadata database to allow for the integrated reporting of the data from those multiple sources. In OBIEE terms, Data Federation involves bringing in the metadata from multiple sources into the physical layer of the RPD (BI Repository) and integrating the metadata into a single business model and possibly a single subject area.  In OBIEE, the data sources can be relational (OLTP databases or star-schema data warehouses), multidimensional (OLAP), or files (such as Excel or flat files); and the data can be of varying levels of aggregation in these sources.  This is one of the most powerful features of OBIEE.

When an Analysis (report) is run from Answers that uses data from these sources, the BI Server creates the appropriate SQL (OLTP) or MDX (OLAP) statements to retrieve the data from the appropriate source and then integrates the data for display to the user.  The user does not need to know what the source of the data is, how many sources there are, or how the data is retrieved.

There are two common patterns of how data can be federated in OBIEE for reporting – these patterns/methods are termed Horizontal Federation and Vertical Federation. I will explain them here.

Horizontal Federation involves setting multiple data sources for a common single logical table object in the Business Model and Mapping (BMM) layer of the RPD, such that the granularity of the data from the various sources is at the same level, and some columns of the single logical table come from one source, and some from another source(s) – basically each source adding columns (not rows) to the logical dataset.  A typical scenario for this pattern is where there is related data in multiple sources for a particular subject, but no single source holds the entire body of data for that subject – and this helps to bring all the descriptive data for the subject together into one.

Vertical Federation involves setting multiple data sources for a common single logical table object in the BMM layer of the RPD, such that the data for each columns could be coming from multiple sources, but at varying granularity levels – basically each set adding rows (not columns) to the logical dataset.  A typical scenario for this pattern is where data at an aggregated granularity is sourced from an aggregated OLAP data source or an aggregated OLTP data source, while data at a detailed granularity is sourced from the transaction level (OLTP or detailed-level star-schema) data source.