Movember Time

Its Movember Again.

 

 

https://uk.movember.com/

Check those Balls Guys!

 

There are a few 12c Blogs coming out, but 11.x.9 is not dead yet.  See below

 

More on Usage Tracking in the next blog.

 

Ad.

 

blogBlogs of the week

1. Customizing Charts and Graphs in OBIEE 11.1.1.9 – Quickstart Video

Christian Screen writes:

In a previous blog post, we walk you through the steps for configuring the new OBIEE 11.1.1.9 method for setting the default color palette for charts and graphs. Here is the accompanying video to walk you through this process step-by-step:

2. How to Load On-Premise data to Oracle BICS Schema using Data Sync

Gajendra Krishna from Just Analytics talks us through the process, step by step. 

  • Installing BICS Data Sync
  • Creating Project, Connections, Tasks/Jobs
  • Creating Jobs & Loading On-Premise data to BICS Schema

3. SALE: Oracle SQL Developer Data Modeler Jump Start

In this post, Kent Graziano posts a video on Subviews

4. OBIEE BUNDLE PATCH 11.1.1.7.151020 AKA 11.1.1.7.12 (?) is available

Boris Dahav says, “OBIEE 11.1.1.7 new general bundle patch (number 11?), 11.1.1.7.151020 is available in Patch 21814325, it can be installed on any previous 11.1.1.7 release.

“5. Session Abstracts Visible for BIWA Summit 2016

Dan Vlamis posted Sessions for the BIWA Summit 

6. ORACLE OPEN WORLD 2015 – MUST-SEE SESSIONS – ORACLE FORMS, MOBILE, CLOUD & ADF

Mia Urman lists her Must See sessions for Oracle Open World.

7. SQLCL – Blob loading ALL the files

Kris Rice says, “I’m slowly building out a helper.js which is now posted and I’ll continue to update it on github here.”

8. Using the Modeler to Compare Two Schemas

Jeff Smith writes this post in response to this question:

“I am working in SQL developer 4.0. I am comparing my acceptance version of a schema to my production version of the same schema. We did some ‘clean-up’ of old tables and views in acceptance. We want to apply the same changes to production, when we go to prod. How do I tell SQL D to produce a ‘drop’ statement when running a diff and doing the compare?”

9. Time to Revitalize Your Data Warehouse

Ian Abramson tells us that it’s time to revitalise the data warehouse!

10. Customer References – Red Samurai ADF Performance Audit

Andrejus Baranovskis writes, “ADF 12.2.1 provides out of the box support for REST. You should read about all available REST functionality in ADF BC here – I have created very quick sample application to test how it works.

11. Oracle BI Bundle Patch OBIEE 11.1.1.9.5

This blog says, “The new bundle patch set for Oracle Business Intelligence Enterprise Edition and Oracle Exalytics OBIEE 11.1.1.9.5 was released recently.

There is currently no detailed patch description available. The patch is currently only available for Linux64. It’s tagged to be a recommended security patch. We will publish further news in this blog post.”

Read on for more.

12. Histogram Limit

Jonathan Lewis wrote a blog post to answer this question:

“Why does a query for “column = 999999999999999999” run slower than a query for “column > 999999999999999998” (that’s 18 digit numbers, if you don’t want to count them). With the equality predicate the query is very slow, with the range-based predicate perfomance is good.”

13. Announcing Oracle Data Integrator (ODI) 12.2.1 – Focus on Big Data and Lifecycle Management Innovations

Sandrine Riley talks about the highlights of Oracle Data Integrator 12.2.1. Including:

  • Big Data
  • Lifecycle Management
  • ODI Exchange
  • Better Oracle Connectivity
  • More Data Integration Pillar Integration – Enterprise Data Quality (EDQ)

14. Oracle Data Integrator 12c New Features Overview

A link to the PDF detailing the new features.

twitterThis week on Twitter

ORCL DataIntegration asks What is new in ODI 12C?

Accenture Technology at OOW15

Wayne D. Van Sluys tweeted Oracle Business Intelligence 12C downloads

 

LinkedinThis week on LinkedIn

Yasmin Morrison shared Oracle Data Integrator | 3 Ways to Improve Productivity in ODI Studio

Michel Miotto Barbosa says OBIEE 12C is available

Kashinathan K tells us How Nested Tables can be stored and retrieved in a Normal table-ORACLE PL/SQL

addidici logoPaper.li

Stories from Oracle Database

Videos such as:

Webinar : Best Practices with Oracle BI Financial Analytics


Webinar: A Mix of Art & Science: How to Achieve High Performance Business Intelligence

 

Building on top of the Users

The Usage Tracking (UT) project will build on top of the out of the box tables, and that includes using the “SA System” ‘Users’ table as a starting point for our Users table.

Have a look at Gerard blog/Wiki ( is that WikLog?) http://gerardnico.com/wiki/dat/obiee/sa

and also, the all important oracle Docs (11g)

http://docs.oracle.com/cd/E14571_01/bi.1111/e10541/sa_system.htm#BIESG3137

 

As usual we have the code we will be building in the Github, see

https://github.com/Addidici/usage_tracking/blob/master/011_create_users.sql

Next we will need to add the columns that help to answer those questions (remember those in http://blog.obiee.co.uk/catch-up/).

So, now we put our think caps on..

 

 

 

 

 

Delete that Index

All indexes are good, right? Wrong.

An index can make a join fly. but it can also slow you down. As I’ve said many times before, most of the time I delete or hide indexes, especially on an Exadata box and add carefully.

However, whilst query performance is helped by an index, and updates too that use a join, the populating of a table is slowed by the use of an index.

For our usage tracing project we are not going to use the BI_PLATFORM table for our use in OBIEE. Therefore we do not need the indexes, so the we are going to drop the primary key on S_NQ_ACCT. To do this first we drop the foreign keys on S_NQ_DB_ACCT and the Summary Advisor.

ALTER TABLE S_NQ_DB_ACCT DROP CONSTRAINT FK_S_NQ_DB_ACCT;

and

ALTER TABLE S_NQ_SUMMARY_ADVISOR DROP CONSTRAINT FK_S_NQ_SUMMARY_ADVISOR;

Now we can drop the primary key.

ALTER TABLE S_NQ_ACCT DROP CONSTRAINT S_NQ_ACCT_PK.

I am also dropping the NULL constraints, on all the tables.

Now, on with the news…

blogBlogs of the week

1. 12 More Things About Oracle Database 12c at OOW15 | Debra Lilley

In this 2 Minute Tech Tip, Oracle ACE Director Debra Lilley, Vice President of Cloud Solutions at Certus Solutions, shares information on this special set of mini-sessions presented by other members of the Oracle ACE program.

2. Report Carousel in APEX 5 UT

Dimitri Gielis says, “When you add a region to your page with a couple of sub-regions and you give the parent region the “Carousel Container” template it turns the regions into a carousel, so you can flip between regions.”

See it in action here.

3. Disrupting BI Part 3: Visualizing Better Data with Data Quality

 Lou Bushinsky tells us how to:

  • Achieve More Completeness and Consistency
  • Ensure More Data Accuracy
  • Embrace More Quality Data Practices

4. Migrating from Oracle Portal 10g to Oracle Application Express (APEX)

M&S Consulting start their post by saying, “There are several enterprises that are currently running the Oracle 10g middleware platform for their front-end applications. They have Oracle Portal 10g or prior versions delivering content for their Oracle Forms, Oracle Reports, and Oracle Discoverer based applications, all running on Oracle Application Server (OAS) 10g.”

5. Oracle BI Cloud Service – Part II: Loading Data

Nicholas Ribas says, “In the first post of the Oracle BI Cloud Service (BICS) series we reviewed some of its key features. In the following posts, we will explain how the service actually works, starting with the data provisioning. Check out part III of the BICS series in which we will cover how you can model your data in the cloud according to your business requirements.”

 

6. OBIEE – Line Breaks

Boris Dahav says, “The Customer asked for line breaks in analysis. This was basically covered before here byobiee10grevisited.blogspot.com but I felt it needs some more examples.” See the full blog for more.

7. Copy Data from BICS Schema Service to BICS DBaaS or on On-Premise DB

Richard Williams says, “This article will cover the steps to migrate the data from a BICS Schema Service Database, to a BICS DBaaS Database.  Similar steps could be used to move a database from BICS to on-premise, or from on-premise to a BICS DBaaS instance.”

8. SQLCL – More secure, now with REST !

Kris Rice posted that “A new SQLCL build was just posted go grab it and kick the tires.  There are well over 100 bug fixes in there so it’s better than ever.  Also there’s some new things.”

  • More secure
  • REST support

9. All Things SQL Developer at Oracle Open World 2015

Jeff Smith posted about Oracle Open World:

10. Creating Real Application Clusters (RAC) using Oracle Standard Edition SE2 (12.1.0.2.0) with Container Database (CDB) with Pluggable Database (PDB) – Multitenant Environment

Y.V RaviKumar posted “In this article, we are creating and configuring two node RAC (Real Application Clusters) setup with container database (CDB) with pluggable database in multitenant environment using Oracle Standard Edition SE2 (12.1.0.2.0)”

11. DISCOVERY AND MONITOR ORACLE DATABASE APPLIANCE (#ODA) USING #EM12C

Curtis posted a blog all about the plug-in for the Oracle Database Appliance. He also posted a list of articles that relate to installing agents in EM12C (if you don’t know how to do that already).

12. OpenWorld 2015 on your smartphone and tablet

Download the OpenWorld 2015 ap here.

13. Toggling the database in OBIEE 11g at runtime

Vishal Pathak writes a comprehensive blog about toggling the database in OBIEE 11g at runtime.  Read the full blog here.

14. Kscope 15 interview and how to stay up to date with EPM?

He says, “I had the honor and privilege to be interviewed by Opal Alapat at Kscope in Hollywood, Florida. Did you know Opal has an excellent blog?”

He also links to the video:

LinkedinThis week on LinkedIn

Alexander Hansal asked, What Will #OOW15 Bring for the Siebel Community?

Anmol Kaushik posted OBIEE Horizontal Clustering – DIY Guide – Part 1

twitterThis week on Twitter

Perficient&Oracle tweeted OBIEE 11.1.1.9 – BI Publisher Set Up Fails on Windows Server 2012

Robin Moffat shared

 

addidici logoPaper.li

Stories from thatjeffsmith.comkpipartners.com and jonathanlewis.wordpress.com

Videos such as: #Kscope 15: Thats a Wrap Part 2

and Webinar : Best Practices with Oracle BI Financial Analytics

 

 

Catch Up

When I announced last week that we are putting together a Usage Tracking project, I assumed that people would know WHY. To me it seemed obvious, having implemented this type of system for the last 15 years that I have worked in OBIEE (nee Seibel Analytics).

So, Why do we need a usage tracking ‘Warehouse’ solution when there are already database tables in place?

  1. You can Identify Who is not using the System,
  2. You can see more information about your users – ie. not just their Username, things like What Department they are in, What Rank, What Country.
  3. You can group the results into sets, e,g, User Type Totals or Weekly Averages vs Last 6 Months Average
  4. The results can be tuned. (see note below)
  5. You can show any correlation between Data Quality and Dashboard Usage
  6. You can mix different environments, projects or versions.

Tuning Capture and Use of Tracking Data.

On a project I work on their are literally millions of Usage Records.  Imagine if you are reporting on 20 million records using the out of the box table, while that table is being populated by live user activity!

How can you set query attributes for the table whilst not slowing it down for write?  The answer is, you don’t.

Take all indexes off the std Usage Tracking Tables (e.g S_NQ_ACCT), allow logging.  You can even put this in another database.

Each night (or more regularly if you prefer) move the data out of the S_NQ_ACCT table and put into a staging area.  From there we populate Dimensions and Facts.  Then update any aggregates.  Apply Stats (stored or refresh).  Refresh Materialized views.

 

This is where this project will focus first.  Create target dimensions, those that can be populated from Usage Tracking, Those from User Tables, Those from People tables (for Dept etc), Dates (as shown in part 1)  We then create facts and aggregates.

Using ODI we then build the ETL to load those tables.

 

Remember, if you want to join in, just email me or linkedin Message.  We have four so far so room for more.

 

blogBlogs of the week

1.  Map Reduce: File compression and Processing cost

Sunil says, “Recently while working with a customer we ran into an interesting situation concerning file compression and processing time. For a system like Hadoop, file compression has been always a good way to save on space, especially when Hadoop replicates the data multiple times.”

Read the full blog for more details.

2. Stupid Programming Trick No. 25 part A – Hybrid allocations the Wild but Crazy way

Cameron Lackpour talks about allocations of the most simple kind in this blog post. He says, ” To set the stage, this allocation post will:

  • Use a Hybridized version of Sample.Basic
  • Allocate the indirect Budget cost Distribution from a pool amount
  • Use a driver based on Actual Sales level 0 Product and Market as a percentage of Actual Sales Total Product and Total Market

I (and the world+dog of Essbase developers) have written allocations like this since the Year Dot.  I purposely chose this example because it is so basic and yet pervasive.  Never underestimate how easy Essbase makes this sort of logic.  I think sometimes we lose track of the still-revolutionary functionality of Essbase.”

3. Smart Combo Box LOV with Filter

Andrejus Baranovski blogs, “Combo box LOV with filtering is simple, but effective feature not used often. You should rethink LOV design and apply combo box LOV with filtering where possible. Advantage of such LOV implementation is pretty clear – fast list items access, based on filter criteria. This will reduce number of times required to open LOV popup and improve performance of UI.”

4. Job Frequency with DBMS_SCHEDULER and SQL Developer

Jeff Smith writes, “I was remarking to someone this morning how easy I thought it was to setup a job, say on the first and third Wednesday of the month. The flexibility of DBMS_SCHEDULER, even without taking into account the power of schedules, is really powerful.”

5. Less calls…more performance (part 2)

Connor McDonald blogs, “In the previous post, I mentioned that for a programming environment fetching rows from the database, then the method of

  • open a ref cursor
  • issue a fetch call
  • close the ref cursor

might not be appropriate for those situations where the result set is known be a single row (eg primary key lookup). A better option might be to call a procedure and get those outputs as parameters. And I broke a cardinal rule… I effectively said “Here’s something that I know to be true…so you should just trust me“. Dear oh dear oh dear. That’s not good. So without further ado, it’s time to put together a test case.”

6. Oracle DB12c on OL7 with Virtual Box 5 installation issues

Danny Byrant says, “As usual, I’m goofing off in my home Data Center (riiiiight), and I decide to install Oracle Linux 7 and the 12c Database. While not concerning myself with what’s on the Compatibility Matrix, I go ahead and start up Virtual Box 5 (recently released) and install my O/S.  No problem there so, I’m thinking everything is fine. Time to install the database. At this point, I’m doing a basic install, so we are just going the follow the installation steps … very generic. Then I get this:…”

Read the full blog for details.

7. USING BICS DATASYNC TO LOAD DATA – PART 1

Paul Cannon says, “Today I’m taking a look at installing BICS DataSync  – a utility for loading data into an Oracle BICS (Business Intelligence Cloud Service) database.  In a second blog – to follow –  I’ll use it to transfer up some data.”

Part 2 is also available to view here.

8. Truncate – 2

In the second part of a 2 part blog, Jonathan Lewis says, “Following on from my earlier comments about how a truncate works in Oracle, the second oldest question about truncate (and other DDL) appeared on the OTN database forum – “Why isn’t a commit required for DDL?””

twitterThis week on Twitter

Paul Tudor Oprea tweeted that the mastermind of OBIEE has passed away

Steve Yeung asked What’s new in this OBIEE version?

Kent Graziano shared Microsoft And Salesforce Join In $5.3 Billion Buyout Of Informatica

LinkedinThis week on LinkedIn

Yasmin Morrison shared Your top 15 questions on Oracle PBCS, Answered  and How to Deploy Oracle ARM Without a Hitch 

Lauren Piper asked,  Want to learn from case studies around Oracle Business Analytics & Financials? Check out the UKOUG Business Analytics & Financials SIG

Paul Anderson shared Business Analytics Monthly Index – July 2015

addidici logoPaper.li

Stories from oracleformsinfo.comjonathanlewis.wordpress.com and  thatjeffsmith.com

Videos such as: Using ODI to Load Essbase Data

and Changing gears – boat one mods