Simples (Or how to OR or AND)

Setting a filter to find results that match one of your requirements is easy, e.g. Find me People who have used OBIEE in April or May. Peasy.

(You were thinking Multi-select on your Dashboard Prompt, right?)

But, what if we want to find people who have used OBIEE in April AND May. Still easy? Of course. That’s easy too – Loads of methods available from RCOUNT to using Filter By, possibly using a sub query or advanced JOIN.

Now, what if we want to give the end user a choice? Let them decide whether they want people who used OBIEE in April or May or Both April AND May.

How would you do it? Can you do it without a sub query or Advanced SQL? (I hate Sub queries BTW).

No cheating => Two queries with Conditional display is not allowed.

Thought of a method yet?

If you have, then share (use the Google OBIEE EMG group please).

One way is the following….

Log into OBIEE

Create Your Folders…

Create a Prompt (use of Presentation Variables)…

The secret is use of Presentation Variable prompts

Create the analysis…

The Totals @pv_month1/2 columns use a filter by function, where # of Queries is the Measure.

The Filter_Month1/2 columns use a filter by function, where # of Unique Users is the Measure.

The CASE Statement in the SQL Expression part is

CASE WHEN '@{pv_andor}' = 'BOTH' THEN 1 ELSE 0 END

Note: Clever use of Filters OR capability (not available for all Criteria types!)

More Notes:
The first filter does an OR for you.

The second filter kicks in only if you choose the AND option, and because it is ANDed with the first Filter you get an AND. If the second part of the filter does not apply then you are left with the OR.

Now, put them on your dashboard:

Two months selected, either can apply

Two Months selected, but both must apply:

Simples.

Blogs of the week

1. Oracle Business Intelligence Cloud Service Data Sync is out on OTN

Daan Bakboord writes:

A typical Oracle BI Cloud Service (BICS) flow looks like the following:

Load your data to the Cloud
Model and Secure your data
Author Analyzes,Visualizations and Interactive Dashboards
Use and Share BICS content

In the current version of BICS you have to upload your data into the Cloud. There are several ways to load the data:

BICS Data Loader
REST API
SQL Developer
PL/SQL Data Import
BICS Data Sync

He gives a series of links to reading material regarding the BICS Data Sync .

2. Collaborate 2015 – Final Thoughts

Debra Lilley gives her thoughts on Collaborate 2015.

3. Installing OWB into a 12c database

Pete Bunning says, “As a change of pace this week I’m looking at OWB and how to install it with a 12c database. Doing an in-place upgrade seems to be covered in the Oracle notes but to find out how to install it on a fresh database instance, I had to dig a little bit deeper.” Read on for more information.

4. Oracle BI Mobile App Designer v2 – Key Features

Javier Giaretta says, “Oracle BI Mobile App Designer (MAD) is the tool integrated on OBIEE 11g to perform analytics on mobile devices, as explained on a previous blog article, this article will extend the Oracle BI Mobile App Designer explaining the new features of the Mobile App Designer V2.”

He names the new features as:

Redesigned UI
Exploration Template
New Map Component
Calculated fields and filters
PlugIn SDK

5. OAAM_SAMPLE with different integration and/or deployment options

Kuldeep Shah says, “Multiple times in past, I have encountered questions/issues about OAAM_SAMPLE. So, thought to write a small post explaining how it can be used/configured to test (try out) different native integration options for OAAM.”

6. OBIEE – Selecting columns dynamically – part5, the single prompt solution

Boris Dahav covers:

What he intends to do?

Create the above prompt to allow the user to select desired columns.
Create a hidden set of prompts where I split the above prompt to column names and have additional hidden prompts for their actual formula.
Create an analysis where the column values are either the selected column or the None / Hidden column from solution 2 of post 4 of this series.

and how he will achieve it!

7. Purging the OBIEE Cache from Oracle Data Integrator

In this blog, Pete says, “One of the features of OBIEE is that it has a result cache. This can dramatically boost performance by replaying a result from cache rather than going back to the data sources, joining the rows and doing all that filter, aggregate and sort stuff that can be so time and resource consuming. One of the pitfalls of a cache is “what happens when the underlying data changes?” We then need to perform some kind of action to force OBIEE to go back to data sources and not the cached (and now incorrect) data. We have basically three options:

Not use the cache at all << Data is always right, but we get no performance benefits.
Set a Cache retention time on the objects << how do we know how long to wait before purging.
Invoke a process to purge some or all of the cached data.

To my mind, invoking a purge process is the best option and the best time to do the purge is from the ETL refresh schedule as a task dependant on the ETL build.”

8. Logstash and Oracle Database

Kris Rice says, “This blog post is a first cut at an input filter for Logstash to gather metrics, logs, or anything that can be expressed in sql.”

He points us in the direction of this video:

9. Oracle SQL Developer version 4.1 is Now Available

Jeff Smith tells us that SQL Developer version 4.1 is Now Available! So is SQL Developer Database Modeler version 4.1. And so is Oracle REST Data Services version 3.0. Check them out!

10. Oracle Data Miner (ODM 4.1) New Features

Brendan Tierney sums up the new features:

Data Source node can now include data sources that contain JSON data, generating JSON schema and has a JSON viewer
Create Table can now create data in JSON
JSON Query Node allows you to view, query and process JSON data, combine it with relational data, generate sub-group by, and nested columns to be part of input to algorithms
New PL/SQL APIs for managing Data Miner projects and workflows.
New ODMr Repository views that allows us to query and monitor our workflows.
Transformation Node now allows you different ways of handling NULLS.
Transformation Node now allows us to create Custom Bins, define bin labels and bin values
Overall Workflow and ODMr environment improvements to allow for greater efficiency in workflow behaviour and interactions with the database.

He tells us to keep an eye out for some blog posts over the coming weeks on how to explore and use these new features of Oracle Data Miner.

UKOUG shared a link to Tech15 submissions

ODTUG tweeted Join Steven Feurerstein & Tom Kyte for YesSQL! Day at #Kscope15