Category: Performance Tuning

Business Intelligence, Business Intelligence (BI) Development, Business Intelligence Platform, Performance Tuning, Power BI June 27, 2023June 27, 2023

Power BI Storage modes

Power BI allows us to connect to many different data sources – from relational databases, NoSQL databases, files, and more – to source data for consumption in Power BI. From the data sourced, you can create additional data (new calculated columns, metrics, transformed data, etc.), build data models, and create reports and dashboards.

There are a few storage modes related to how the data is retrieved, stored, and processed in Power BI. The storage modes are Import, DirectQuery, Live Connection, and Dual. The storage mode is set at the table level for each table in the Power BI data model. I will now describe these modes.

Import

With the Import storage mode, Power BI imports and caches the data from the sources. Once the data import is complete, the data in Power BI will remain the same until is refreshed by the Power BI refresh process for that dataset.

This storage mode allows for the usage of the most Power BI features for data modeling and analysis. For example, Import mode is required for using two of the popular Power BI features, Quick Insights and Q&A. Also, this mode is almost always the best for performance. However, it’s not necessarily the best option in all scenarios. Since the data is imported, the file size can get large and can sometimes take a considerable amount of time to load. But generally, for relatively static, low volume data, it is the preferred choice.

Queries submitted to an imported dataset will return data from the cached data only.

DirectQuery

With the DirectQuery storage mode, no data is cached in Power BI, but the metadata of the source tables, columns, data types, and relationships is cached. Instead, the data is directly queries on the source database when needed by a Power BI query, such as when a user runs a Power BI report that uses the data.

For Since the data is not imported, if all the tables in the data model use DirectQuery, the Power BI file size will be very small compared to a model with imported data.

Live Connection

The Live Connection storage mode is a special case of the DirectQuery mode. It is only available when sourcing from Power BI Service datasets or Analysis Services data models. There are limitations when using this mode. Data modeling is limited to creating measures, and therefore, you cannot apply transformations to the data, and you cannot define relationships within the data. And you can only have one data source in your data model.

Dual

With the Dual storage mode, a table may use Import mode or DirectQuery mode, depending on the mode of the other tables included in the query. For example, you may have a scenario in which you have a Date table that is connected to one transaction table that needs to reflect the data in the source, and is therefore set to DirectQuery mode, and also connected to another transaction table that only has less than 100,000 rows and is set to Import storage mode. By setting the Date table to Dual storage mode, Power BI will use DirectQuery when the query involves the date table and the first transaction table, while using Import mode when the query involves the date table and the second transaction table.

The below table summarizes the Power BI data storage modes:

Import	DirectQuery	Live Connection	Dual
-Data is imported and cached in Power BI -Preferred for static, relatively small datasets -All Power BI functionality is available – including DAX, Calculated tables, Q&A and Quick Insights -Can connect to Analysis Services but Live Connection is preferred -Can have unlimited data sources -Typically provides the best performance	-Data is queried on the source when needed -Use for large datasets and when data changes in source need to be updated immediately -Features such as Q&A, Quick Insights, Calculated Tables, and many DAX queries are not supported -Limited data transformation functionality -Parent-child functionality not supported -For relational databases -Not supported for Analysis Services -Performance greatly dependent on the source data source	-A special case of DirectQuery -Used for connecting to multi-dimensional data sources, such as Analysis Services -Can be used only with Power BI datasets and Analysis Services -Can have only one data source -No data transformation available -Q&A and Quick Insights not available -Can create measures	-A combination of Import and DirectQuery -Power BI will choose the appropriate option based on the storage mode of the tables involved in the query -Can improve performance

Summary of Power BI storage modes

Note: the content in this post is relevant for the PL-300 Analyzing Data with Microsoft Power BI certification exam.

Thanks for reading! I hope you found this information useful.

Good luck on your analytics journey!

Business Intelligence Platform, Data Integration, Database Administration, Performance Tuning November 13, 2019

BI Application getting ORA-02391 error

Last week we rolled out a new dashboard that uses a new data source.
In one of our BI environments, the application was throwing an error:
“ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit at OCI call OCISessionBegin“

This is an Oracle Database error, and not an error directly from the BI Application.

For the “ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit” error …
The Cause is: An attempt was made to exceed the maximum number of concurrent sessions allowed by the SESSIONS_PER_USER clause of the user profile.
And the Action for resolution is: End one or more concurrent sessions or ask the database administrator to increase the SESSIONS_PER_USER limit of the user profile.

Turns out the SESSIONS_PER_USER parameter was set too low; it was set to 3 for the user being used to access the database from the BI application. This error could have also been observed from an ETL tool accessing the database with an ID with the same parameter setting.

One of the DBAs bumped this parameter up to 30 for the user, and that resolved the issue.
We requested for this change to be done on the BI application databases in all the environments – Development, Test, QA, and Production.

Although all seems to be well, we will now monitor to see how many sessions the application is using and if there is any negative impact on the source application. This will allow us to determine if we need to make any other adjustments.

Thanks for reading. I hope you found this information useful.

Business Intelligence, Data Analysis, Data Development, Data Governance, Data Integration, Data Quality Assurance, Data Security, Data Strategy, Data Warehousing, Performance Tuning November 6, 2019

Quality Assurance (QA) for Data Projects or Data Applications

This post discusses Quality Assurance (QA) activities for data projects.

What is Quality Assurance (QA)? Simply put, Quality Assurance, also called QA, Testing or Validation, is about testing an application or solution to ensure that all the stated/promised/expected requirements are met. It is a critically important activity for all software application development or implementations. Data applications are no different. They need to be tested to ensure they work as intended.

QA stands between development and deployment. And QA makes the difference between a delivered product and a high quality delivered product.

There are a number of things to keep in mind when you plan your Quality Assurance activities for data solutions. I present some of them in this post as suggestions, considerations, or prompting questions. The things mentioned here will not apply to all data applications but can be used as a guide or a check.

People / Teams

The number of people and teams involved in a project will vary depending on the size, scope and complexity of the project.

The technical team building the application needs to perform an initial level of validation of the solution.

If there is a Quality Assurance team that performs the validation tasks, then that team will need to perform the “official” validation.

The business analysts and end-users of the application also need to validate. Where possible, work with as many end users as efficiently possible. The more real users you have testing the application, the better the chances of finding issues early.

Where it makes sense, Test IDs that simulate various types of users or groups should be used to help test various usage and security scenarios. This is particularly useful in automated testing.

On large projects where there is a lot to be tested, it is best to break up the testing across multiple people or teams. This will help to prevent testing fatigue and sloppy testing and result in higher quality testing.

Plan ahead to ensure that access for all the relevant users is set up in the testing environments.

Communication

With all the teams and people involved, it is important to have a plan for how they will communicate. Things to consider and have a plan for include:

How will teams communicate within? Email, Microsoft Teams, SharePoint, Shared Files, are some options.
How will the various teams involved communicate with each other? In other words, how will cross-team communication be handled? As above, Email, Microsoft Teams, SharePoint, Shared Files, are some options.
How will issues and status be communicated? Weekly meetings, Status emails or documents, Shared files available on shared spaces are options.
How will changes and resolutions be tracked? Files, SDLC applications, Change Management applications are options.
How will teams and individuals be notified when they need to perform a task? Manual communication or automated notifications from tools are options.

Data

The most important thing to ensure in data projects is that the data is high quality, particularly the “base” data set. If the base data is incorrect, everything built on top of it will be bad. Of course, the correctness of intermediate and user-facing data is also just as important, but the validation of the base data is critical to achieving the correct data all over.

Ensure that table counts, field counts and row counts of key data are correct.
Does the data warehouse data match the source data?
Test detailed, low level records with small samples of data
Test to ensure that the data and the values conform to what is expected. For example, ensuring that there is no data older than 3 years old, or ensuring that there are no account values outside a certain range. The Data Governance Team may become involved in these activities across all projects.

Next in line is the “intermediate” data such as derived metrics, aggregates, specialized subsets, and more. These will also need to be verified.

Are the calculated values correct?
Are the aggregates correct? Test aggregate data with small, medium and large sets of data
Verify metric calculations

Then the user-facing data or data prepared for self-service usage needs to be validated.

Does the data on the dashboard match the data in the database?
Are the KPIs correctly reflecting the status?

Test the full flow of the data. The validity of the data should be verified at each stage of the data flow – from the source, to the staging, to the final tables in the data warehouse, to aggregates or subsets, to the dashboard.

Take snapshots of key datasets or reports so you can compare results post data migration.

Some additional data prep might be needed in some cases.

These include making sure that you have sourced adequate data for testing. For example, if you need to test an annual trend, then it might be best to have at least a year’s worth of data, preferably two.
You may need to scramble or redact some data for testing. Often Test data is taken from the Production environment and then scrambled and/or redacted in order to not expose sensitive information.
You may need to temporarily load in data for testing. For various reasons, you may need to load some Production data into the QA environment just to test the solution or a particular feature and then remove the data after the testing is complete. While this can be time consuming, sometimes it’s necessary, and it’s good to be aware of the need early and make plans accordingly.

Aesthetics & Representation of Data

Presentation matters. Although the most critical thing is data correctness, how the data is presented is also very important. Good presentation helps with understanding, usability, and adoption. A few things to consider include:

Does the application, such as dashboard, look good? Does it look right?
Are the components laid out properly so that there is no overcrowding?
Are the logos, colors and fonts in line with company expectations?
Are proper chart options used to display the various types of data and metrics?
Is the information provided in a way that users can digest?

Usage

The data application or solution should be user friendly, preferably intuitive or at least have good documentation. The data must be useful to the intended audience, in that, it should help them to understand the information and make good decisions or take sensible actions based on it.

The application should present data in a manner that is effective – easy to access, and easy to understand.

The presentation should satisfy the analytic workflows of the various users. Users should be able to logically step through the application to find information at the appropriate level of detail that they need based on their role.

A few things that affect usability include:

Prompts – ensure that all the proper prompts or selections are available to users to slice and filter the data as necessary. And of course, verify that they work.
Drill downs and drill throughs – validate that users can drill-down and across data to find the information they need in a simple, logical manner.
Easy interrogation of the data – if the application is ad-hoc in nature, validate that users can navigate it or at least verify that the documentation is comprehensive enough for users to follow.

Security

Securing the application and its data so that only authorized users have access to it is critical.

Application security comprises of “authentication”– access to the application, and “authorization” – what a user is authorized to do when he or she accesses the application.

Authorization (what a user is authorized to do within the application) can be broken into “object security” – what objects or features a user has access to, and “data security” – what data elements a user has access to within the various objects or features.

For example, a user has access to an application (authenticated / can log in), and within the application the user has access to (authorized to see and use) 3 of 10 reports (object-level security). The user is not authorized to see the other 7 reports (object-level security) and, therefore, will not have access to them. Now, within the 3 reports that the user has access to, he or she can only see data related to 1 of 5 departments (data-level security).

All object-level and data-level security needs to be validated. This includes negative testing. Not only test to make sure that users have the access they need, but testing should also ensure that users do not have access that they should not have.

Data for testing should be scrambled or redacted as appropriate to protect it.
Some extremely sensitive data may need to be filtered out entirely.
Can all the appropriate users access the application?
Are non-authorized users blocked from accessing the application?
Can user see the data they should be able to see to perform their jobs?

Performance

Performance of the data solution is important to user efficiency and user adoption. If users cannot get the results they need in a timely manner, they will look elsewhere to get what they need. Even if they have no choice, a poorly performing application will result in wasted time and dollars.

A few things to consider for ensuring quality around performance:

Application usage – is the performance acceptable? Do the results get returned in an acceptable time?
Data Integration – is the load performance acceptable?
Data processing – can the application perform all the processing it needs to do in a reasonable amount of time?
Stress Testing – how is performance with many users? How is it with a lot data?
How is performance with various selections or with no selections at all?
Is ad-hoc usage setup to be flexible but avoid rogue analyses that may cripple the system?
Is real-time analysis needed and is the application quick enough?

These items need to be validated and any issues need to be reported to the appropriate teams for performance tuning before the application is released for general usage.

Methodology

Each organization, and even each team within an organization, will have a preferred methodology for application development and change management, including how they perform QA activities.

Some things to consider include:

Get QA resources involved in projects early so that they gain an early understanding of the requirements and the solutions to assess and plan how best to test.
When appropriate, do not wait until all testing is complete before notifying development teams of issue discovered. By notifying them early, this could make the difference between your project being on-time or late.
Create a test plan and test scripts – even if they are high-level.
Where possible, execute tasks in an agile, iterative manner.
Each environment will have unique rules and guidelines that need to be validated. For example, your application may have a special naming convention, color & font guidelines, special metadata items, and more. You need to validate that these rules and guidelines are followed.
Use a checklist to ensure that you validate with consistency from deliverable to deliverable
When the solution being developed is replacing an existing system or dataset, use the new and old solutions in parallel to validate the new against the old.
Document test results. All testing participants should document what has been tested and the results. This may be as simple as a checkmark or a “Done” status, but may also include things like data entered, screenshots, results, errors, and more.
Update the appropriate tracking tools (such as your SDLC or Change Management tools) to document changes and validation. These tools will vary from company to company, but it is best to have a trail of the development, testing, and release to production.
For each company and application, there will a specific, unique set of things that will need to be done. It is best if you have a standard test plan or test checklist to help you confirm that you have tested all important aspects and scenarios of the application.

This is not an all-encompassing coverage of Quality Assurance for data solutions, but I hope the article gives you enough information to get started or tips for improving what you currently have in place. You can share your questions, thoughts and input via comments to this post. Thanks for reading!

Business Intelligence, Interview Questions, Performance Tuning August 20, 2017

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!

Performance Tuning July 26, 2016

OBIA Financial Analytics – SIL_APTransactionFact_DiffManLoad performance issues

We are on Oracle Business Intelligence Applications (OBIA) 7.9.6.3 and had been experiencing performance issues with the SIL_APTransactionFact_DiffManLoad workflow/mapping. We tried a number of things but only had minimal improvements. Eventually, I found a solution for the poor performance on Oracle Support. This change resulted in a drastic improvement of this workflow.

The solution can be found on Oracle Support (http://support.oracle.com – Oracle Doc ID: 1446397.1), but for your convenience I have included the content below. There are other mappings that have a similar problem.

————————————————————————

OBIA 7963: SIL_APTransactionFact_Diffmanload Mapping And Performance Issue (Doc ID 1446397.1)

In this Document
Symptoms
Cause
Solution

Applies to:
Informatica OEM PowerCenter ETL Server – Version 7.9.6.3 [AN 1900] and later Information in this document applies to any platform.

Symptoms
The OBIEE application (7.9.6.3) ETL task “SIL_APTransactionFact_DiffManLoad” has run over 68 hours during full load execution.

Cause

The size of these columns (DOC_HEADER_TEXT and LINE_ITEM_TEXT ) in DAC is 255 (except AP where its 1020 in DAC and Infa). But in Informatica the size for these two columns is 1020. Ideally it should be 255. This is a known performance issue.
The cause of the problem has been identified in unpublished Bug 12412793- PSR: B16 INCREMENTAL: SIL_GLREVENUEFACT,

Solution
Below are the steps you will follow to modify the size of the fields in the lookup.

Take a backup of existing Lookups ( LKP_W_AP_XACT_F and LKP_W_AR_XACT_F ).
Login to Informatica Designer >Transformations
Open the lookup and modify the size of the fields. The port lengths for the DOC_HEADER_TEXT and LINE_ITEM_TEXT were changed to 255 .
Save the changes
Rerun the test and confirm the performance issue is resolved and migrate the changes in PROD.

————————————————————————

Business Intelligence, Data Warehousing, Performance Tuning February 22, 2015

OBIEE Performance Tuning

This post describes a few tips and things to keep in mind for OBIEE Performance Tuning.

Be Proactive when possible
The need to performance tune can be proactive (tune before a major issue arises) or reactive (tune after a problem is reported by users for example). It is best to be proactive – so performance tuning should be built into your OBIEE maintenance schedule. For example, OBIEE’s Usage Tracking functionality should be used regularly to identify reports whose performance can be improved and then performance steps should be carried out on the worst performers.

Iterative Process – change one thing or set of things at a time
One of the first things to keep in mind is that performance tuning is an iterative process. And there is typically no one silver bullet that will resolve all your performance problems. You may need to analyze and make changes to multiple parts of the system, but you want to make the changes methodically. It is best to change one parameter or setting at the same time (or one related set of parameters). Adjust and test the settings for that one parameter/setting (or set of parameters) before moving on to another. If you change too much at one time, you may have a difficulty determining what is helping from what is hurting your efforts.

Fix user complaints first, worst performers next, and then the next bad performers down the list
Another thing to keep in mind, tune what users are reporting first, then tune the worst problems second, then move on to the next.

Team Effort – problem could be anywhere along the technology stack
Performance problems could be anywhere along the technology stack:
• OBIEE
• Database
• Server
• Network
Due to that span of technology, performance tuning is a team effort. OBIEE Admins and Developers, DBAs, and ETL Developers can all be key to solving performance issues.
Logs from all components may need to be reviewed depending on the scenario.

Try to isolate or narrow-down the source of the problem
For example, run the report SQL directly on the database and see if you have the same problem. If there is no issue when run directly on that the database, then you have eliminated the database as the problem.
Determine if other applications have been also been experiencing slowness which could indicate the possibility of a network problem.

If your users have reported an issue, then you need to get as much details as possible about the performance problems they are experiencing. When did this start happening? Is it just one report or many? Is it localized to one business area or multiple? Is it all the time or sometimes? Knowing this will help you to know where to focus.

Other questions to ask as you try to identify the source of the problem include but not limited to:
Has anything changed? If reports were running fine, but are now slow, the first thing to ask is …
When the issue start? Determining exactly when it started might be helpful when correlating with other system or company activity)
What has changed recently? Has there been any system changes, data changes, database updates, network changes, etc. (even if they seem unrelated)? For example, rolling into a new calendar year will cause new “Year” value(s) to be included in the data and can impact performance if statistics are not gathered.
Is there a possibility that an index was dropped and not recreated as expected?

Use OBIEE’s Usage Tracking information to analyze specific reports, analyze long running reports, or frequently run reports. You will want to capture and analyze the SQL from these reports to determine what can be done to improve their performance.

Database
DBAs can monitor the system in real-time, use various tools, or review logs for information that can be helpful in the tuning effort. Tools such as Oracle Enterprise Manager (EM) or SQL Tuning Advisor can be used to identify, analyze and tune high-load SQL.
OBIEE Usage Tracking can also be used to identify high-load SQL.
Without getting into much detail, these are some database features that could be used to help improve performance:
• Gather Statistics
• Results Cache database feature
• Partitioning

Servers
The System Admins can monitor the server resources to determine if there is an issue there.
• Use fast disk for the OBIEE cache and/or temporary files.

OBIEE-specific performance tuning tips

• OBIEE Caching
Are the tables being used set to cacheable?
Is caching turned on at the application level?
You may consider seeding the cache daily.
CACHE Settings:
o MAX_ROWS_PER_CACHE_ENTRY
o MAX_CACHE_ENTRY_SIZE
o MAX_CACHE_ENTRIES
o ——————-
o USE_ADVANCED_HIT_DETECTION

• Use Aggregation: Aggregate data when applicable
o You can use Aggregate tables or materialized views to realize this benefit.
o Aggregate Fact tables and corresponding Aggregate Dimensions.
o Make sure aggregation rules are applied to Fact table measures.
o Don’t necessarily merge all measures into a single fact.

• Joins and Indexes
o Do not create unnecessary joins.
o Verify that the joins on the tables being investigated are appropriate.
o Performance Indexing could be helpful. Again, this is an iterative process.

• Prompts and Filters
o Use LOV tables to drive prompt values when possible, instead of building prompts from large transactional data tables.
o Force filter selection / entry by making prompt values required. Do not allow open ended run of reports.

• Filter out unneeded data. If there is a significant amount of data that is not being used in one or more tables (especially if they are frequently used), then that data should be filtered out by the ETL before it gets joined in SQL, and then has to be filtered out in the RPD or at the report level.

• Enter the “Number of Elements at this level” value in the logical level in hierarchies.
• Also ensure that all logical level keys are unique.

• Avoid function in the where clause when possible.

• Be careful of sub-queries.

• Check out the features of the OBIEE Performance Monitor
http://server:port/analytics/saw.dll?Perfmon (enter your OBI server and port)

• When possible, do comparison analysis to determine for example, why is this report running fine, but this other seemingly similar report is not.

• Use fast disk for the OBIEE cache and/or temporary files.

Sometimes a complete overhaul might be required
Review the users’ workflow and determine if new and improved queries can be written or if the number of queries can be reduced.
Present information from a summary level first, and then provide increasing levels of details as requested by users through drill down or navigation. Basically, present detailed information only when necessary, and minimize the amount of detail provided at a time by filtering on user selections.

Oracle’s OBIEE Performance Tuning Guide
Apply recommendations from the “Best Practices Guide for Infrastructure Tuning Oracle® Business Intelligence Enterprise Edition 11g Release”. I would recommend applying 1 – 3 changes or set of changes at a time; don’t apply everything at the same time because if there is a problem, it will be more difficult to determine which change caused it.
https://blogs.oracle.com/proactivesupportEPM/entry/wp_obiee_tuning_guide

Business Intelligence, Performance Tuning March 5, 2014

New OBIEE Tuning Guide (Version 4) – released Jan 2014

A new version of the OBIEE Tuning Guide (Version 4) was released in January, 2014. It is suitable for OBIEE 11.1.1.6 and 11.1.1.7 versions.

It can be found on Oracle Support under Document ID 1333049.1. Or here is a direct link to the document (PDF).

—————————————-

New highlights from the previous document include:

Optimized JVM switches for JRockit / Sun JVM / IBM JVM
New tuning parameters settings / values for JavaHost / OPIS / OBIS components.
Improved performance monitoring techniques.
IBM WebSphere tuning parameters.
More WebLogic Server tuning parameters.
Windows Server 2012 tuning parameter.
New optimized Linux / AIX tuning parameters.
Additional Essbase ASO tuning parameters.
libOVD authenticator search tuning

—————————————-

As always, this document provides us with recommended baselines or starting points, but the appropriate settings for each environment will vary.

Performance Tuning October 11, 2013

How to clear the BI Server cache using command line script or via the Issue SQL page

This post will explain how to clear the BI Server cache using either (1) a script, or (2) the user interface.
You may need to clear your cache to allow the data in the cache to get refreshed.

(1) Clearing the cache using Command Line script …

This example is for Linux/Unix, but can be easily adjusted for Windows.

Create a file called purgecache.txt … and place it at … [FMW_HOME]/instances/instance1/bifoundation/OracleBIApplication/coreapplication/setup

In the file, enter the code “call SAPurgeAllCache()” – (without the quotes) – which is a special BI Server command for clearing the entire cache.

Then create a shell script called purgecache.sh, located in a directory where you store your custom scripts … which includes the following 2 commands …

(Note: there is a space after the dot in the below command)
. /[FMW_HOME]/instances/instance1/bifoundation/OracleBIApplication/coreapplication/setup/bi-init.sh

(Note: the below is ONE long command; not 2 commands)
[FMW_HOME]/Oracle_BI1/bifoundation/server/bin/nqcmd -d AnalyticsWeb -u administrator -p pswd –s [FMW_HOME]/instances/instance1/bifoundation/OracleBIApplication/coreapplication/setup/purgecache.txt

Then you may execute the shell script just as you would any other script (for example ./purgecache.sh ) -or- you may add this new script to your crontab if desired – for example, to run each night before your cache seeding reports run.

(2) Clearing the cache using the Issue SQL page …

Log into OBIEE Analytics using an ID that has the “Administration” link privilege.
http://server:9704/analytics

Click on the “Administration” link located at the top right of the page.

Click on the “Issue SQL” link at the bottom left of the page.

In the Issue SQL window, enter the command: “call SAPurgeAllCache()” (without the quotes)
Click the “Issue SQL” button to execute the command.

You should see a message indicating that the command was executed successfully (assuming you have caching turned on).

Your cache directory located at …
[FMW_HOME]/instances/instance1/bifoundation/OracleBIServerComponent/ coreapplication_obis1/cache
… should now be empty.

Data Integration, Performance Tuning September 30, 2013

Informatica Unconnected Transformations

There are 3 Informatica transformations (External Procedure, Lookup, and Stored Procedure) that can be unconnected in a valid mapping. An unconnected transformation is one that is not connected to the pipeline – that is, it is not connected to any other transformation via a link. Unconnected transformations, especially unconnected lookup transformations, are widely in OBIA mappings.

Unconnected transformations are defined in a mapping and are called / invoked from another transformation in the mapping. They can be called from any transformation that supports the transformation expression language.

Below is a list of the 3 Informatica Unconnected Transformations and how they are called or invoked from another transformation:

Transformation	How its called / invoked
External Procedure	:EXT.external_procedure_transformation(argument1, argument2, …)
Lookup	:LKP.lookup_transformation(argument1, argument2, …)
Stored Procedure	:SP.stored_procedure_transformation( argument1, argument2, [, PROC_RESULT])

Any mapping with an unconnected transformation must also include another transformation that calls the unconnected transformation.

All of the 3 Unconnected Transformations can also be used in connected mode. However, when you want to execute the transformation logic for a subset of the rows passing through the mapping (especially when it’s a small percentage of the rows), you will want to consider using the transformations in unconnected mode and with conditional logic. The conditional logic will cause the unconnected transformation to execute only when necessary, and therefore, may improve perfiormance.

The example below shows a call to an unconnected lookup transformation (lkp_item_id) that is invoked only when the desired item_id value is NULL. In a case where only a small percentage of the rows have an item_id that is NULL, then the lookup would only be invoked for a small number of rows, as opposed to all rows if the transformation was connected.

IIF ( ISNULL(item_id), :LKP.lkp_item_id ( item_name ), item_id )

As a side note, the Sequence Generator transformation can also be invoked from a function call, but it is a connected transformation.

Transformation	How its called / invoked
Sequence Generator	:SEQ.sequence_generator_transformation.CURRVAL

Data Warehousing, Performance Tuning, Reporting March 7, 2013

OBIEE Tuning Whitepaper from Oracle (has been updated)

Oracle has released an updated version of their OBIEE Tuning Whitepaper.

You can find the document here …

https://blogs.oracle.com/pa/entry/test

… or here …

https://support.oracle.com/epmos/faces/ui/km/DocumentDisplay.jspx?_afrLoop=212370301476321&id=1333049.1&_afrWindowMode=0&_adf.ctrl-state=w65avp7pa_30

You will need to have an Oracle ID to access it (which is a free sign up).

In addition to all the great information that was in the original document, the updates to the document include:

New improved HTTP Server Caching algorithm
Oracle iPlanet Web Server tuning parameters
New tuning parameters settings / values for OPIS/OBIS components

The topics included in the document are:

1.0 Performance Overview

1.1 Introduction to Oracle Business Intelligence EE Performance
1.2 Performance Terminology
1.3 Understanding Key Performance Drivers

2.0 Top Tuning Recommendations for OBIEE

2.1 Tune Operating Systems parameters.
2.2 Tune Oracle WebLogic Server (WLS) Parameters
2.3 Tune 64bit Java Virtual Machines (JVM)
2.4 Tune 32bit Java Virtual Machines (JVM)
2.5 Tune HTTP Server Parameters
2.6 Tune HTTP Server Compression / Caching
2.7 Tune Oracle Database Parameters

3.0 Performance Monitoring OBIEE

3.1 Built-in BI Metrics for Performance Monitoring
3.2 Performance Monitoring In Windows Environment
3.3. Performance Monitoring In Unix Environment

4.0 Tuning OBIEE Components

4.1 Oracle BI Presentation Services Component
4.2 Oracle BI Server Component

5.0 Tuning Essbase

5.1 Essbase ASO Tuning

The Data Community