Feed aggregator

Oracle BIN_TO_NUM Function with Examples

Complete IT Professional - Fri, 2016-11-18 05:00
In this article, I explain what the Oracle BIN_TO_NUM function does and show you some examples. Purpose of the Oracle BIN_TO_NUM Function The BIN_TO_NUM function converts a bit vector to a number. A bit vector is a combination of values to make up a binary number. For example, the binary value of 1 is equal […]
Categories: Development

Oracle Container Cloud Service is available now!

I am happy to let you know that finally Oracle Container Cloud Service is publicly available. Oracle Container Cloud Service, a new service where customers can bring their own Docker containers and...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Reminder: Upgrade to EM 12.1.0.5

Steven Chan - Fri, 2016-11-18 02:05

Oracle periodically produces Fusion Middleware patchsets -- cumulative collections of fixes -- between major product releases.  All Oracle customers have a limited amount of time to upgrade from the prior patchset when a later patchset is released.  This is called the "grace period."  You cannot request new patches for the prior patchset after the grace period ends.

Where are Fusion Middleware grace periods documented?

The specific grace periods for upgrading from one Fusion Middleware patchset to another are published here:

Do these apply to EBS customers?

Yes.  These dates apply to all Oracle customers, including E-Business Suite customers. 

What is the impact on EBS customers using Application Management Pack?

Enterprise Manager 12.1.0.5 was released in June 2015.  Oracle continued to create new patches for EM 12.1.0.4 until June 2016.  New patches for EM 12.1.0.4 are no longer available. New patches are being created only for EM 12.1.0.5.

EBS 12.1 and 12.2 customers who need new patches for their Enterprise Manager components must be on EM 12.1.0.5 and AMP 12.1.0.4 BP3, at minimum.  No new patches for earlier versions of AMP will be available.

EBS customers should consider upgrading to Enterprise Manager 13c and AMP 13.1 or higher:

Related Articles


Categories: APPS Blogs

Links for 2016-11-17 [del.icio.us]

Categories: DBA Blogs

New Utilities Testing Solution version available (5.0.1.0)

Anthony Shorten - Thu, 2016-11-17 17:55
Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4

We have released a new version(5.0.1.0) of the Oracle Functional/Load Testing Advanced Pack for Oracle Utilities (OFTAPOU) and is available from Oracle Delivery Cloud for customers and partners.This new OFTAPOU version now includes support for more versions  of our products. The packs are now cloud compatible i.e., they can be used for testing applications on Oracle Utilities Cloud services.

The pack now supports the following:

  • Oracle Utilities Customer Care And Billing 2.4.0.3 (updated), 2.5.0.1 (updated) and 2.5.0.2 (updated)
  • Oracle Utilities Mobile Workforce Management 2.2.0.3 (updated)
  • Oracle Real Time Scheduler 2.2.0.3 (updated)
  • Oracle Utilities Mobile Workforce Management 2.3.0 (updated) – with added support for Android/IOS mobile testing.
  • Oracle Real Time Scheduler 2.3.0 (updated) – with added support for Android/IOS mobile testing.
  • Oracle Utilities Application Framework 4.2.0.3, 4.3.0.1, 4.3.0.2 and 4.3.0.3.
  • Oracle Utilities Meter Data Management 2.1.0.3 (updated)
  • Oracle Utilities Smart Grid Gateway (all adapters) 2.1.0.3 (updated)
  • Oracle Utilities Meter Data Management 2.2.0 (new)
  • Oracle Utilities Smart Grid Gateway (all adapters) 2.2.0 (new)
  • Oracle Utilities Work And Asset Management 2.1.1 (updated)
  • Oracle Utilities Operational Device Management 2.1.1 (updated)

The pack now includes integration components that can be used for creating flows spanning multiple applications known as integration functional flows.

Components for testing mobile application of ORS/MWM have been added. Using the latest packs, customers will be able to execute automated test flows of ORS/MWM application on Android and IOS devices.

In addition to the product pack content, the core test automation framework has been enhanced with more features for ease of use.For example, the pack now includes sanity flows to verify installations of individual products. These sanity flows are the same flows used by our cloud teams to verify cloud installations.

The pack includes 1000+ prebuilt testing components that can be used to model business flows using Flow Builder and generate test scripts that can be executed by OpenScript, Oracle Test Manager and/or Oracle Load Testing. This allows customers to adopt automated testing to accelerate their implementations and upgrade whilst reducing their risk overall.

The pack also includes support for the latest Oracle Application Testing Suite release (12.5.0.3) and also includes a set of utilities to allow partners and implementers to upgrade their custom built test automation flows from older product packs to the latest ones.

/* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:"Times New Roman"; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;}

First installation of SQL Server on Linux REHL 7.2

Yann Neuhaus - Thu, 2016-11-17 16:42

As you certainly know Microsoft announced yesterday the first preview of SQL Server vNext on Linux. Obviously we are far from a final version suitable for production and the list of unsupported features will probably be good indicator of the production maturity over the time.

So as many people, I could not wait to do a try of installing SQL Server on Linux version. The installation process is well documented and I would like to share my first experiences about the installation process.

Firstly, it was a good memory to re-install a Linux server on my lab environment and I have to admit I lost some habits. But keep motivating and I’m sure my reflexes will get back with time. Well, I decided to start my Linux story by installing the SQL Server vNext on the top of a Red Hat Linux 7.2.

For this first attempt, I decided to use a lot of default configurations but for sure it will change in the future. But for the moment it doesn’t matter, I just wanted to focus on the installation process of SQL Server.

I used my existing lab environment that includes Hyper-V on Windows 8.1. I decided not to install the Linux Integration Services for Hyper-V because it seems not to be certified for REHL 7 versions. The installed virtual machines have the corresponding specifications:

–> 4 VCPUS

blog 109 - 0 - 2 - install linux

–> 4GB of memory

blog 109 - 0 - 3 - install linux

–> 50GB of disk space for the root file system but it seems to be too much  … at least for the moment :-)

blog 109 - 0 - 1 - install linux

So according to the Microsoft documentation the first step consists in using the Red Hat package manager YUM to download and install the SQL Server packages after adding the Microsoft SQL Server Red Hat repository file into the /etc/yum.repos.d folder.

blog 109 - 0 - 4 - install linux

Then after running the SQL Server configuration script (/opt/mssql/bin/sqlservr-setup) I faced my first error. Well, more memory to add in order to run SQL Server. Let’s do it!

blog 109 - 1 - install linux

After fixing this issue I may ran the installation process successfully in interactive mode. The installation process created two symbol links that will be managed by systemd and related to the SQL Server Service engine and telemetry (the latter has appeared since SQL Server 2016). We will have the opportunity to discuss further when we will go into more details in this area.

blog 109 - 2 - install linux

blog 109 - 2 - 1 - install linux

 

The second step consists in installing the client tools which include both sqlcmd and bcp. This step is pretty similar to the previous one because we have first to download the corresponding Microsoft SQL Server Red Hat repository file before installing the client tools. After downloading their packages, I faced a dependency issue as shown below – A kind of issue I almost forgot when I left the Linux World a couple of years ago  :-) – This is a good opportunity to notice that we will use ODBC on linux to connect to SQL Server.

blog 109 - 3 - install linux

Well, let’s install the related missing library libtltdl.so.7()(64bit). I took a look at the internet and I downloaded the libtool-ltdl-2.4.2.21-e17_2.x86_65 rpm package that includes it. After installing the aforementioned package no issue to continue to install the SQL Server client Tools.

blog 109 - 4 - install linux

blog 109 - 5 - install linux

Let’s perform a last check to figure out which port my SQL Server instance is listening

blog 109 - 7 - install linux

TCP 1433 as expected !

And finally let’s connect to my SQL Server instance test via SQLCMD tool and let’s perform my first query:

blog 109 - 8 - install linux

The final test is not new and you already saw this picture directly from Microsoft events, webcasts and so on .. However, it was a good immersion in Linux world with SQL Server and the next time we will try to do more. There are still a lot of pending questions which I have to answer in a near future  :-)

Stay tuned!

 

 

 

Cet article First installation of SQL Server on Linux REHL 7.2 est apparu en premier sur Blog dbi services.

Oracle’s Work Life Solutions can support every industry—especially this one

Linda Fishman Hoyle - Thu, 2016-11-17 14:59

Mining is an industry that is pivotal to the world’s economy. We wanted to share this recent article from Australia Mining that reports on the needful intersection of the mining industry and Oracle’s HR cloud applications.

As with many industries, mining is being reshaped by external forces, which is causing challenges for human resources.

One of those challenges is the result of increased automation. HR professionals need to ensure the company retains its talent amidst “the drive toward increased productivity and innovation,” says John F Hansen, Oracle’s vice president for Human Capital Management in JAPAC.

Another challenge is the staff’s mental health. The mining industry pays well, but the work can be in remote locations, time intensive, and physically challenging. According to PWC research, conditions such as these can lead to absenteeism, reduced productivity, and compensation claims.

Help comes in the form of innovative HR strategies. Both of these issues can be addressed by the latest generation of HCM technology. Hansen points to Oracle’s Work Life Solutions, which “can be used to help employees manage the intersection of their work and personal lives.”

These applications can produce a more engaged productive workforce. Equally important is that the “ROI for these programs in the mining industry is the highest across all industries.”

Oracle Midlands : Event #18 (Cancelled)

Tim Hall - Thu, 2016-11-17 09:06
Cancelled!

Don’t forget Oracle Midlands Event #18 next week!

om18

If you live near, please show your support for the event and come along. It’s free thanks to the sponsorship by RedStackTech.

Cheers

Tim…

Oracle Midlands : Event #18 (Cancelled) was first posted on November 17, 2016 at 4:06 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

ECMA Script 6 / Nashorn / Java 9 and SQLcl

Kris Rice - Thu, 2016-11-17 08:57
This blog post brought to you by the letter M as in Martin . Follow @martindsouza . He asked me on twitter if SQLcl via Nashorn could use ECMA Script 6 features yet.  The answer is yes.  So, for the brave that want to try out the latest greatest ECMA Script 6 features it can be done with SQLcl. This is a good reference for what's in version 6 that could be useful.  The only caution is not

Enabling the Mobile Workforce with Cloud Content and Experience - Part 3

WebCenter Team - Thu, 2016-11-17 08:41

Author: Mark Paterson, Director, Oracle Documents Cloud Service Product Management

Continuing my series on quick tips on how to use key features of our apps to drive effective mobile collaboration (part 1 and part 2 of the series noted), I’d like to quickly cover how easy it is to review videos and audio flies. For a quick refresher, Oracle Documents Cloud Service is an enterprise-grade content and social collaboration cloud solution that allows you to work and drive decisions with your content and your ecosystem anytime, anywhere and on any device.

Need to review or approve a new video on the move? Oracle Documents Cloud Service provides full support for videos right within our mobile app.

  1. Install the Oracle Documents apps on your iPhone, iPad, or Android device, log on to Oracle Documents Cloud Service, and you’re ready to go. Use it anywhere. It’s designed to be familiar, swiping to navigate and tapping to open folders and files. The app guides you through what to do.

  2. Watching a video stored in Oracle Documents is easy. Launch Oracle Documents, browse to the folder where your video is stored and tap on it, and then tap on Play. That’s it, your video will start playing immediately, no need to wait for it to fully download, everything is streamed.

Normal 0 false false false EN-US X-NONE X-NONE /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin-top:0in; mso-para-margin-right:0in; mso-para-margin-bottom:8.0pt; mso-para-margin-left:0in; line-height:107%; mso-pagination:widow-orphan; font-family:"Calibri","sans-serif"; mso-ascii- mso-ascii-theme-font:minor-latin; mso-hansi- mso-hansi-theme-font:minor-latin; mso-ansi-language:EN-CA;}

If you need to play an audio file you can follow the same steps, no 3rd party player required.

Check out our latest "how-to" video to see it in action.

You can always find the Oracle Documents mobile apps in the App stores.

Normal 0 false false false EN-US X-NONE X-NONE -"/> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin-top:0in; mso-para-margin-right:0in; mso-para-margin-bottom:8.0pt; mso-para-margin-left:0in; line-height:107%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-ansi-language:EN-CA;}

Short and sweet this time round, next time we’ll explore changes we’ve made that make it super simple to upload all the great assets you create on your mobile device into Oracle Documents Cloud. In the meantime, if you haven't already, simply get started on your free Oracle Documents Cloud Service trial today at https://cloud.oracle.com/documents.



Normal 0 false false false EN-US X-NONE X-NONE /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin-top:0in; mso-para-margin-right:0in; mso-para-margin-bottom:8.0pt; mso-para-margin-left:0in; line-height:107%; mso-pagination:widow-orphan; font-family:"Calibri","sans-serif"; mso-ascii- mso-ascii-theme-font:minor-latin; mso-hansi- mso-hansi-theme-font:minor-latin; mso-ansi-language:EN-CA;}

Oracle CUME_DIST Function with Examples

Complete IT Professional - Thu, 2016-11-17 05:00
In this article, I’ll cover what the Oracle CUME_DIST function is and show you some examples. Purpose of the Oracle CUME_DIST Function The purpose of the CUME_DIST function is to find the cumulative distribution of a value in a list of values. The value that is returned will be between 0 and 1. The function […]
Categories: Development

Common OBIEE Implementation Mistakes That Impact Performance

Rittman Mead Consulting - Thu, 2016-11-17 04:19

I've recently performed several assessments to help our clients diagnose and fix their OBIEE performance problems. They often share similar design or implementation issues, and I thought it would be useful to present them here as a handy reference:

Mistake 1: No Software Monitoring

The key to happy users is to know there’s a problem before they do, and even better, fix it before they realise. Availability of comprehensive performance metrics is vital to achieve this goal, and Usage Tracking data represent the natural starting point. When enabled in OBIEE, Usage Tracking collects statistics for each query and is helpful to understand usage patterns across dashboards and users, and to analyse performance trends and diagnose specific issue. Whilst Usage Tracking is generally always enabled and data it generates reviewed regularly, other software metrics are not gathered at all or are not available to the BI team, giving only a partial view of the picture.

In addition to gathering and monitoring Usage Tracking data, we would strongly recommend:

  • Proper OS monitoring to capture full breadth of OS metrics on both application and database servers including CPU %, Load Average, Memory, Disk I/O, Disk Busy %, Network I/O. Store historic data for trend analysis with appropriate aggregations (so as to not blunt peaks), and near-realtime granular data for interactive performance diagnostics.

  • OBIEE Dynamic Monitoring Service (DMS) metrics collection. These metrics provide information regarding Oracle Fusion Middleware components' performance, state, and on-going behaviour.

    OBIEE Performance Analytics Dashboards

All of these can be done and visualised with Rittman Mead OBIEE Performance Analytics Dashboards. Have a look at these videos to see exactly how the dashboards can help, and get in touch with us to request a demo!

Mistake 2: Randomly Changing Configuration Settings

It’s a common belief, or hope depending on available hardware, among online gamers that by changing configuration settings you can fix any software performance issue. Unfortunately OBIEE is not a game and this is not true! Whilst OBIEE does not provide any Boost Performance switch, the defaults are generally a good starting point, and unnecessary fiddling without good reason should be avoided:

  • You can introduce additional problems by mistake.

  • It makes it hard to track what's changed from a default config.

  • If it fixes the problem, could be coincidence - no actual understanding of the underlying problem without proof of it.

We at Rittman Mead are firm believers in a practical and logical approach to performance analytics and optimisation. Eschewing the drunk man anti-method of ‘tuning’ configuration settings at random, we advocate making a clear diagnosis and baseline of performance problems before changing anything. Once a clear understanding of the situation is established, steps are taken in a controlled manner to implement and validate one change at a time.

Mistake 3: Data Transformation at Query Time

You can usually find a lot of data transformation being done in the RPD itself. This is one of the most useful functionality of OBIEE, since data re-modelling in-flight at query time is usually a lot quicker to implement rather than changing the underlying data model and / or ETL processes.

Wrangling data this way in a small number of instances is common, but widespread use is indicative of a data model and / or ETL design that needs modifying to take into account the report requirements, it's difficult to maintain, and it results in less efficient SQL, as well as unnecessary complex SQL.

  • Conditional transformation - Business is always changing and it may require to group data in a way that was not expected when data model has been designed. For example, you can create a logical column using CASE WHEN statement to define a new country grouping which is not present in your data model.

    Conditional transformation example

  • Data cleansing - Sometimes you may need to integrate social networks data into your corporate reports, but you have limited or no control over these contents. For example, you can use the TRIM function to deal with blanks that are expected in a column containing users' reviews for your products.

    Data cleansing example

  • Data conversion - The classic use case for data conversion is when you have to manipulate dates to provide the required format. For example, you can use CAST and Calendar/Date functions to create the format YYYY-MM, required to define the Month level's key of your Date hierarchy, from a date column.

    Data conversion example

  • Opaque views - An opaque view is a physical layer table that consists of a SELECT statement. The typical use case for opaque views is when you have to turn a transactional data model into a star schema. However opaque views mask the true data structure from OBIEE, preventing it from best generating optimal data access queries, and they should be used only when there are no other available solutions.

We recommend to do this kind of transformations once, at ETL time, rather than every time a particular table or column is used in a query.

Mistake 4: Underuse of Double Column Feature

Double Column is probably one of the most undervalued and unused features of OBIEE. It provides a mechanism for associating two logical columns: one column provides the display and description values such as the description of an item; the second column provides a descriptor ID or code column.

Use should be made of the Double Column feature to enable OBIEE to generate more efficient database physical SQL. The principle behind this is that instead of queries running using string-based predicates which may not be indexed in the database or could be less efficient, the user sees the string version of an attribute whilst OBIEE uses the corresponding numerical ID column in the generated query when it queries the database, thus generally creating more optimal execution plans.

As an example, to do this we start with assigning the P0 Product Number column as the descriptor ID column in the Business Model and Mapping layer for P1 Product column:

Double column feature setup example

Let's now create a simple report in Answers containing T05 Per Name Year and 1- Revenue columns, and apply a filter on P1 Product as well. If you are selecting is equal to / is in, is not equal to / is not in or is between operators, then filtering by code values checkbox will be available:

Filter by code values example

Let's check it, and then see, in terms of SQL, how the query filter is generated:

select sum(T216.Revenue) as c1,  
     T795.PER_NAME_YEAR as c2
from  
     BISAMPLE.SAMP_PRODUCTS_D T451 /* D10 Product (Dynamic Table) */ ,
     BISAMPLE.SAMP_TIME_QTR_D T795 /* D03 Time Quarter Grain */ ,
     BISAMPLE.SAMP_REVENUE_FA2 T216 /* F21 Rev. (Aggregate 2) */ 
where  ( T216.Prod_Key = T451.Prod_Key  
         and T216.Prod_Key = 7
         and T216.Bill_Qtr_Key = T795.QTR_KEY
         and T451.Prod_Key = 7 ) 
group by T795.PER_NAME_YEAR  

Double Column feature can also be used to define language independent filters as my former colleague Venkat already noted in another post.

Mistake 5: Suboptimal Report Design

Certain suboptimal report designs that I've seen recently include overcrowded analyses, abuse of table / pivot prompts, and OBIEE used as a data extraction tool.

Overcrowded analyses / Overuse of the Excluded area in Views

An overcrowded analysis is an analysis with an awful lot of columns included in Criteria tab and several different views with many excluded columns. These views can then be displayed all together or spread out different dashboard pages.

This pattern is quite common with clients that migrated from Hyperion Interactive Reporting and the main motivation for it - there is only one analysis to maintain - may sound good. However, since Oracle BI Server retrieves results for all columns specified in Criteria tab, we recommend to create several analyses with a single view rather than adopt this pattern. In this way the BI Server generates more efficient queries and avoids on the fly data aggregation.

As an example, you can use Oracle Sample Application v607 to create and run an analysis with a Line-Bar view as follows. Note the huge number of columns in the Excluded area.

Overcrowded analysis example

This will results in a simple chart with 3 bars and a line connecting 3 points:

Line-Bar view example

Let's open obis1-query.log log file and have a look at the physical SQL generated by the BI Server:

select sum(T418.Units) as c1,  
     sum(T418.Revenue) as c2,
     T762.Name as c3,
     T666.Office_Dsc as c4,
     T666.Company as c5,
     T418.Order_Status as c6,
     T451.Prod_Dsc as c7,
     T451.Brand as c8,
     T437.Employee_Key as c9,
     T42.Calendar_Date as c10,
     T42.Per_Name_Year as c11,
     T666.Office_Key as c12,
     T762.Cust_Key as c13,
     T451.Prod_Key as c14,
     T666.Company_Key as c15
from  
     BISAMPLE.SAMP_PRODUCTS_D T451 /* D10 Product (Dynamic Table) */ ,
     BISAMPLE.SAMP_TIME_DAY_D T42 /* D01 Time Day Grain */ ,
     BISAMPLE.SAMP_CUSTOMERS_D T762 /* D60 Customers */ ,
     BISAMPLE.SAMP_OFFICES_D T666 /* D30 Offices */ ,
     BISAMPLE.SAMP_EMPL_D_VH T437 /* D50 Sales Rep (Parent Child Hierarchy) */ ,
     BISAMPLE.SAMP_EMPL_PARENT_CHILD_MAP T490 /* D51 Closure Table Sales Rep Parent Child */ ,
     BISAMPLE.SAMP_REVENUE_F T418 /* F10 Billed Rev */ 
where  ( T42.Calendar_Date = T418.Bill_Day_Dt and T418.Cust_Key = T762.Cust_Key and T418.Prod_Key = T451.Prod_Key and T418.Empl_Key = T490.Member_Key and T418.Office_Key = T666.Office_Key and T437.Employee_Key = T490.Ancestor_Key )  
group by T42.Calendar_Date, T42.Per_Name_Year, T418.Order_Status, T437.Employee_Key, T451.Prod_Key, T451.Prod_Dsc, T451.Brand, T666.Company_Key, T666.Office_Dsc, T666.Company, T666.Office_Key, T762.Name, T762.Cust_Key)  

The physical SQL code doesn't look as simple as the resulting view! But the worst is yet to come: it will take about 7 seconds to complete and 256k rows (1.5 GB) will be retrieved from the database!

Let's remove all the excluded columns from the analysis and see what happens to the physical SQL:

select sum(T216.Units) as c1,  
     sum(T216.Revenue) as c2,
     T795.PER_NAME_YEAR as c3
from  
     BISAMPLE.SAMP_TIME_QTR_D T795 /* D03 Time Quarter Grain */ ,
     BISAMPLE.SAMP_REVENUE_FA2 T216 /* F21 Rev. (Aggregate 2) */ 
where  ( T216.Bill_Qtr_Key = T795.QTR_KEY )  
group by T795.PER_NAME_YEAR  

Definitely far better than it looked before. Moreover it will take about 0.2 seconds - 97% less - to complete and only 3 rows (3 KB) - 99% less - will be retrieved from the database.

Abuse of Table / Pivot prompts

Table / Pivot prompts provides an interactive result set that enables users to select the data that they want to view. However Table prompts doesn’t apply any WHERE condition to the query issued by the BI Server: it will retrieve the full result set first, and then select the data to display on the fly. For this reason we recommend to use Dashboard prompts rather than Table / Pivot prompts.

As an example, you can create and run an analysis with a Pivot Table view as follow:

Pivot prompts example

This will results in a pivot table with 8 rows and 25 columns:

Pivot Table view example

Again, let's open obis1-query.log log file and have a look at the physical SQL generated by the BI Server:

select sum(T418.Units) as c1,  
     sum(T418.Revenue) as c2,
     T666.Company as c3,
     T451.Prod_Dsc as c4,
     T451.Brand as c5,
     T653.Per_Name_Month as c6,
     T653.Per_Name_Year as c7,
     T666.Company_Key as c8,
     T451.Prod_Key as c9
from  
     BISAMPLE.SAMP_PRODUCTS_D T451 /* D10 Product (Dynamic Table) */ ,
     BISAMPLE.SAMP_TIME_MTH_D T653 /* D02 Time Month Grain */ ,
     BISAMPLE.SAMP_OFFICES_D T666 /* D30 Offices */ ,
     BISAMPLE.SAMP_REVENUE_F T418 /* F10 Billed Rev */ 
where  ( T418.Prod_Key = T451.Prod_Key and T418.Bill_Mth_Key = T653.Mth_Key and T418.Office_Key = T666.Office_Key )  
group by T451.Prod_Key, T451.Prod_Dsc, T451.Brand, T653.Per_Name_Year, T653.Per_Name_Month, T666.Company_Key, T666.Company  

As expected no WHERE condition based on selected values in pivot prompts was applied to the query. It will take about 0.3 seconds to complete and 2k rows (10 MB) will be retrieved from the database.

Now let's replace pivot prompts with dashboard prompts and see what happens to the physical SQL:

select sum(T418.Units) as c1,  
     sum(T418.Revenue) as c2,
     T451.Prod_Dsc as c3,
     T653.Per_Name_Month as c4,
     T451.Prod_Key as c5
from  
     BISAMPLE.SAMP_PRODUCTS_D T451 /* D10 Product (Dynamic Table) */ ,
     BISAMPLE.SAMP_TIME_MTH_D T653 /* D02 Time Month Grain */ ,
     BISAMPLE.SAMP_OFFICES_D T666 /* D30 Offices */ ,
     BISAMPLE.SAMP_REVENUE_F T418 /* F10 Billed Rev */ 
where  ( T418.Prod_Key = T451.Prod_Key and T418.Bill_Mth_Key = T653.Mth_Key and T418.Office_Key = T666.Office_Key and T451.Brand = 'BizTech' and T653.Per_Name_Year = '2013' and T666.Company = 'Genmind Corp' )  
group by T451.Prod_Key, T451.Prod_Dsc, T653.Per_Name_Month  

It looks better than before, huh? The optimised code will take about 0.1 seconds - 67% less - to complete and 96 rows (0.2 MB) - 95% less - will be retrieved from the database.

OBIEE as a data extraction tool

A large number of clients are using OBIEE mostly as a feed for Excel-marts. Using OBIEE simply as a data extraction tool for lots of data into Excel or other destinations can cause both performance and functional problems. OBIEE is designed to be primarily used as a reporting and analytics tool, with users interacting with the data within OBIEE and, as my colleague Francesco Tisiot already noted in another post, almost any transformation doable in Excel can be achieved in OBIEE, probably faster and with zero impact on local workstations.

This behaviour could highlight a lack of end user training and / or communication between them and BI developers. End users may don't know OBIEE at all, but are used to working with Excel: at the end of the day people just want their job done as painlessly as possible. We recommend to talk to your end users about what they're doing with the data in their destination system, and whether the same can be achieved within OBIEE. If users really do just need the data in Excel, then you should look at how best to do it (Oracle Doc ID 1558070.1).

Conclusion

I've shown here some of the pitfalls that are commonly made with OBIEE. They can impact the performance for end-users, as well as making life more difficult for those building and maintaining the reports.

If you'd like Rittman Mead to take a look at your OBIEE implementation and make sure you're getting the best out of it, please do get in touch!

Categories: BI & Warehousing

Diff BW Optimizer SQL Engine

Tom Kyte - Thu, 2016-11-17 03:06
Hi, I would like to know the difference Between Optimizer,SQL Engine and PL/SQL Engine? I asked this question So many times But I did not get answers from you. Thank you
Categories: DBA Blogs

Oracle Database Migration from HP-UX 64bit (Big-endian) to RHEL 64bit (Little-endian)

Tom Kyte - Thu, 2016-11-17 03:06
Hi Tom, We are planning to migrate Oracle Database from HP-UX 64bit (Big-endian) to RHEL 64bit (Little-endian). My question is can we migrate database from Big-endian format to Little-endian format using RMAN convert command? (like converting d...
Categories: DBA Blogs

comparing two databases, put result into new table

Tom Kyte - Thu, 2016-11-17 03:06
Hi Tom Thank you for having this service available I'm trying to create an application that will allow me to compare data from two tables by a common ID I would like to either: put the results (non matches) into a new table OR remove th...
Categories: DBA Blogs

cursor with dynamic sql

Tom Kyte - Thu, 2016-11-17 03:06
Hi , I need to write a procedure which has a cursor that stores the list of table names. We need to loop the cursor and for each table name it should look for a code in that particular table. The final output of the procedure should be the list of ta...
Categories: DBA Blogs

Track users who make updates to a EBS database table from backend

Tom Kyte - Thu, 2016-11-17 03:06
Hi, We have custom triggers on a few Oracle EBS tables to track & log any insert, update & delete. The trigger captures any changes made through the front end screens. However, if a user does an insert, update or delete to a table in the back end ...
Categories: DBA Blogs

Spool is starting new line after 500 characters

Tom Kyte - Thu, 2016-11-17 03:06
I've got Spool working in that the output from a query is returning everything, but after 500 characters, it starts a new line. So if if I had a line that was 502 characters ending with 'bananasarecool', then it would look something along the lin...
Categories: DBA Blogs

RMAN backup

Tom Kyte - Thu, 2016-11-17 03:06
Hi,guy! I use RMAN to backup the database on Nov 13 through the COMMVAULT software,but on Nov 15,the oracle server CPU is high,then I connect to the server and check,found that the wait event is 'RMAN backup&rcovery I/O',and the logon time is the ...
Categories: DBA Blogs

Update query in after insert trigger

Tom Kyte - Thu, 2016-11-17 03:06
Hello Tom- my requirement is When a record is inserted into ERR table, a post-event trigger will fire updating the D field to accept sys date (3 columns has to update in my original requirement). I've written the below code, however after the recor...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator