See, Even Other People do it.

Catch a glimpse of the video here.

Ignoring that fact that OBIEE is not mentioned, this covers the general theory behind designing for data extraction. All the main idea are covered from extraction to start schemas.

A nice touch is the chalk board, so old school that no batteries are required.

OBIEE Migrating Users and Security

So you have built your repository in development, tested how users interact with the data in the presentation layer, and now is the time to promote your work to the live environment.

If you have done your homework, you'll have put some effort into security while building the repository in development (it's always more difficult to bolt on security than it is to pause and design in security from the start).

So here you are in development, you've created your roles and some representative users. You could unpick the security behind the roles and users, or you could export them (users and roles) from your development environment and import them up the line into a pre-production environment. (Probably best to test that it all works before you do it for real.)

So, how does this all work; the security model is maintained in the weblogic console side of OBIEE, and the whole process of importing settings is additive only. By this I mean, that existing settings remain untouched, if a user is in one group prior to the new security import, and the import has that user in another group, the user will end up in two groups - test it for yourself - probably the best way to learn.

Exporting Weblogic OBIEE Users and Groups

So to export:
Login to your console and in the left hand side click on "Security Realms", then, when the screen refreshes, click on "myrealm" in the right panel. Select the Migration tab.

The process is the same for both export and import. For export select the export sub-tab. Designate a directory ON THE SERVER, not your machine, the server. This directory must exist. Click the button and within a second or two the job's done.

Weblogic Security Files

If you navigate to the server directory you designated you'll see 5 files.

Importing Weblogic OBIEE Users and Groups

Move the directory and files to the server where they are to be imported and open the console for that server, and navigate to the Migration tab, just as you did when exporting the security realm.

This time select the "Import" sub-tab, fill in the directory details and click "save". You should then be presented with a screen like below.

Now, the actual testing of permissions I'll leave to you but you will immediately be able to see the new users and groups by select from the tabs on the screen.

OBIEE Performance Monitoring

Here's a little something thats hidden under the covers.

You need to alter the usual URL that you call the dashboard with

http://mydashboard:7001/answers/saw.dll

to

http://mydashboard:7001/answers/saw.dll?perfmon

If you are not logged in you will be prompted to login, then you have a huge page of realtime server stats. The data updates dynamically and once I get a handle on the meanings behind the numbers I'll update you.

Patching 11.1.1.6 to 11.1.1.6.2

The patch for upgrading to 11.1.1.6.2 is available. Details here.

Pretty much a straightforward upgrade, though a few things to watch out for:
1. Set your environment variables correctly

       set ORACLE_HOME=C:\mwhome\Oracle_BI1
       set PATH=%ORACLE_HOME%\bin;%PATH%
       set JAVA_HOME=%ORACLE_HOME%\jdk
       set PATH=%JAVA_HOME%\bin;%PATH%
       set PATH=%ORACLE_HOME%\OPatch;%PATH%

2. Make sure you run opatch from the correct directory

Open a command window (with the correct environment variables set - see 1) in the Oracle_BI1 directory.

3. When you come to Part 2. JDeveloper patch 13952743

Unpack the patch into oracle_common.
Change directory to oracle_common
Using the environment variables set in step 1 run

opatch apply 13952743 -OH C:\mwhome\oracle_common

This will patch the oracle_common directory, otherwise the patch reports nothing to upgrade.

Informatica - How to Manage Explosive Data Growth in your Data Warehouse

Best Practices and New Solutions for Lean Data Warehouses

An Informatica Webinar Replay (available here.)

Join Informatica for a webinar replay to discuss the latest best practices for managing explosive data growth in your data warehouse.
With exploding data volumes and increasing analytic complexity, IT organisations are under siege to respond to business needs while reducing the costs associated with data delivery. With an estimated 85% of production data infrequently used, finding ways to better analyse data usage and archive data is essential.
Join Informatica experts and find out how to manage the explosion of data growth in your data warehouse while reducing infrastructure costs, shrinking maintenance windows, and improving application performance.
In this webinar replay, you will learn how to:

• Gain better visibility into data usage and business activity so unused and dormant data can be identified
• Efficiently archive dormant data, achieving extreme compression while maintaining access
• Significantly improve data warehouse and application performance
• Ensure users are able to access the data they need in a timely and efficient manner
• Locate hot spots of data so tuning and optimisation can be focused to increase operational efficiency
• Build a successful data governance program with database archiving as a core component
• Implement Lean Data Warehouse principles
• Articulate the business benefit of the latest best practices and new solutions for data warehouse archiving

OBIEE backups

There will come a time when you drop that important report / dashboard or make that configuration change or edit joins in the repository and regret it as soon as you hit save.

A partial solution is to backup some of those important files and directories.

Backing up OBIEE.

So what would you need to recreate your OBI install.

1. Repository obviously
2. A catalog of dashboards and reports
3. The instanceconfig.xml file
4. The NQSConfig.ini file

This should be enough to get you back up and running. I have excluded settings like map server settings and usage tracking (now a bean setting in 11.1.1.6), though some of the settings are visible in the configuration files.

The following script assumes the following

• Your middleware home is D:\OBIMW
• Your backup destination is E:\BIbackups

The script creates a directory dynamically in the format YYYYMMDD and saves the backup to that location.

echo off
for /F "tokens=1-4 delims=/ " %%i in ('date /t') do (
set Day=%%i
set Month=%%j
set Year=%%k
)

echo making directory E:\BIbackups\%Year%%Month%%Day%
mkdir E:\BIbackups\%Year%%Month%%Day%
cd E:\BIbackups\%Year%%Month%%Day%
echo copying catalog.....
xcopy /e/y/h/c/k/q/i "D:\OBIMW\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\catalog" E:\BIbackups\%Year%%Month%%Day%
echo copying repository....
mkdir E:\BIbackups\%Year%%Month%%Day%\repository
xcopy /e/y/h/c/k/q/i "D:\OBIMW\instances\instance1\bifoundation\OracleBIServerComponent\coreapplication_obis1\repository" E:\BIbackups\%Year%%Month%%Day%\repository
echo copying presentation services config....
mkdir E:\BIbackups\%Year%%Month%%Day%\presentationservicesconfig
xcopy /y/h/c/k/q/i "D:\OBIMW\instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1" E:\BIbackups\%Year%%Month%%Day%\presentationservicesconfig
echo copying server config....
mkdir E:\BIbackups\%Year%%Month%%Day%\serverconfig
xcopy /y/h/c/k/q/i "D:\OBIMW\instances\instance1\config\OracleBIServerComponent\coreapplication_obis1" E:\BIbackups\%Year%%Month%%Day%\serverconfig
echo on

I'll leave the housekeeping and archival and removal of expired backups to you.

A list of OBIEE functions

Avg     Aggregate
AvgDistinct     Aggregate
BottomN     Aggregate
Count     Aggregate
CountDistinct     Aggregate
CountStar     Aggregate
First     Aggregate
GroupByColumn     Aggregate
GroupByLevel     Aggregate
Last     Aggregate
Max     Aggregate
Median     Aggregate
Min     Aggregate
Ntile     Aggregate
Percentile     Aggregate
PeriodAgo     Aggregate
PeriodToDate     Aggregate
Rank     Aggregate
StdDev     Aggregate
Sum     Aggregate
SumDistinct     Aggregate
TopN     Aggregate
Mavg     RunningAggregate
Msum     RunningAggregate
Rsum     RunningAggregate
Rcount     RunningAggregate
Rmax     RunningAggregate
Rmin     RunningAggregate
Ascii     String
Bit_length     String
Char     String
Char_Length     String
Concat     String
Insert     String
Left     String
Length     String
Locate     String
LocateN     String
Lower     String
OctetLength     String
Position     String
Repeat     String
Replace     String
Right     String
Substring     String
TrimBoth     String
TrimLeading     String
TrimTrailing     String
Upper     String
Abs     Math
Acos     Math
Asin     Math
Atan     Math
Atan2     Math
Ceiling     Math
Cos     Math
Cot     Math
Degrees     Math
Exp     Math
Floor     Math
Log     Math
Log10     Math
Mod     Math
Pi     Math
Power     Math
Radians     Math
Rand     Math
RandFromSeed     Math
Round     Math
Sign     Math
Sin     Math
Sqrt     Math
Tan     Math
Truncate     Math
Current_date     Calendar/Date/Time
Current_time     Calendar/Date/Time
Current_TimeStamp     Calendar/Date/Time
Day_Of_Quarter     Calendar/Date/Time
DayName     Calendar/Date/Time
DayOfMonth     Calendar/Date/Time
DayOfWeek     Calendar/Date/Time
DayOfYear     Calendar/Date/Time
Hour     Calendar/Date/Time
Minute     Calendar/Date/Time
Month     Calendar/Date/Time
Month_Of_Quarter     Calendar/Date/Time
MonthName     Calendar/Date/Time
Now     Calendar/Date/Time
Quarter_Of_Year     Calendar/Date/Time
Second     Calendar/Date/Time
TimeStampAdd     Calendar/Date/Time
TimeStampDiff     Calendar/Date/Time
Week_Of_Quarter     Calendar/Date/Time
Week_Of_Year     Calendar/Date/Time
Year     Calendar/Date/Time
Cast     Conversion
Choose     Conversion
IfNull     Conversion
IndexCol     Conversion
ValueOf     Conversion
User     Conversion
DateBase     Conversion
Case(Switch)     Expression
Case(If)     Expression

OBIEE Mobile now available

Obiee 11.1.1.6.2 mobile now available.

http://itunes.apple.com/gb/app/oracle-business-intelligence/id534035015?mt=8&ls=1

Description

- Oracle Business Intelligence Mobile for Apple iPad is a mobile analytics app that allows you to view, analyze and act on Oracle Business Intelligence 11g content

- Using Oracle Business Intelligence Mobile, you can view, analyze and act on all your analyses, dashboards, scorecards, reports, alerts and notifications on the go

- Oracle Business Intelligence Mobile allows you to drill down reports, apply prompts to filter your data, view interactive formats on geo-spatial visualizations, view and interact with Dashboards, KPIs and Scorecards

- You can save your analyses and Dashboards for offline viewing, and refresh them when online again; thus providing always-available access to the data you need

- This app is compatible with Oracle Business Intelligence 11g, version 11.1.1.6.2BP1 and above

Operational Intelligence: Real-Time Business Analytics for Big Data

Webinar Abstract

Link to webinar here.

Most business intelligence (BI) implementations query data that is a few hours or minutes old, representing business processes that have already completed. Even so called “real-time” BI reports are generated using historical data. While this historical view of data has some value, it does not represent the complete picture. Fully-informed business decisions require real-time insight into high-volume streaming data sources, current events, and ongoing business processes.
Operational intelligence (OI) correlates the real-time analysis of big data in motion with historical data, delivering complete business insight in milliseconds. By continuously analyzing big data in motion, events, and business processes as they are happening, OI enables fully informed decisions that maximize business impact. Real-world implementations of OI are available for energy utilities, mobile telecommunications, cyber security, merchandising or pricing, e-commerce, corporate performance management, and social media.
In this Webinar, you will learn:

• What operational intelligence is and does, plus what technologies enable it
• Business and technology requirements that are driving user organizations toward true real-time analytics and reporting with big data
• Real-world applications for operational intelligence

bicontentserver(11.1.1) down - an update and fix

Following on from a previous post here.

It turns out that all you have to do is create a new environment variable

BI_ORACLE_HOME and give the value of your middleware install ..\Oracle_BI1 directory.

You then need to restart the service and ..... yay.

Explaining OBIEE to Your Boss!

To explain all the details behind OBIEE would take a long time, probably bore a non-technical person half-way to death, yet still not provide them with any details they can conceptually grasp.

There is tons of documentation - the problem is wading through it all, and all your boss wants is-

"OBIEE in a Nutshell".

The OBIEE server exposes to an end user their business focussed measures and dimensions that can be used to assemble analyses of interest.

Assuming that your boss wants to find out how many cars were sold last month and whether they were new or used would assembling a theoretical query of:

"Calendar.Month", "Car.Condition", "Sales.UnitsSold"

with a filter of last month on "Calendar.Month"

The OBIEE server will now organise how the raw data sources are to be queried. It generates one, or more, queries against the data source(s). retreives the results, applies some post processing, and presents back to the user a result set that can be used to generate not only tables, but graphs, pivot tables and maps.

The OBIEE server has modelled physical raw data, applied calculations and transformations, all interwoven with a consistent security model, ensuring data access is seamless and controlled.

In discoverer or reports and to some extent in publisher you design a query. In OBIEE we no longer hand-craft individual queries, rather we model relationships and the ineractions between heirarchical behaviours. This allows for any possible random query entered by a user in the presentation layer to be transformed from a logical to a physical query, transparent to the user, and the results returned and displayed in the manner requested.

Is traditional BI enough for today

Found in ZDNET

Some of the items that stand out are

"In 2011, business users continued to exert significant influence over BI decisions, often choosing data discovery products in addition to/as alternatives to traditional BI tools."

I see business intelligence becoming much more problem centric vs. technology centric. Organizations are realizing that a one size fits all model for business intelligence simply won’t work. Not all problems are the same and not all problems can be solved by the same technology architecture.

 Very thought provoking article - captures exactly what I see day to day. The IT department fighting a rearguard battle to retain control of BI, and the tighter they (the IT depatrment) grip the more the business gets frustrated with them.

Waterfall Chart in OBIEE11.1.1.6.0

Waterfall Chart in OBIEE

As several blogs have mentioned there is an unactivated graph in OBIEE 11.1.1.6.0. This is the waterfall chart.

A waterfall chart is a special type of floating-column chart. A typical waterfall chart shows how an initial value is increased and decreased by a series of intermediate values, leading to a final value. An invisible column keeps the increases and decreases linked to the heights of the previous columns.

It used to be possible to mimic this type of behaviour using a stacked bar graph and setting the 'running total' to the same colour as your background, but this is so much easier.

By default this is not visible in the graph list and we need to enable it manually.

Update: Please note this is not supported by Oracle in this version so if you uncover a bug, you're on your own.

Configuration Changes

Edit your instanceconfig.xml file between the <Views>....</Views> element.

Just before the closing tag add the following (leave off the </Views> which I have left in for clarity).

<Charts>
<EnableWaterfall>true</EnableWaterfall>
</Charts>
</Views>
Save the instanceconfig.xml file and restart OPMN.

WaterFall Graph Results

Now when you add a graph view to your query an option for Waterfall appears in the dropdown.

From playing around it is apparent that the four coloured squares (green, green, red and blue) to the right of the graph relate to
1. First (top) coloured square is the start value or initial column.
2. The second coloured square is used to represent increasing or positive values.
3. The third coloured square is used to represent decreasing or negative values.
4. The bottom coloured square is used to show the final balance.

It is possible to change the colours and representation, but, like I said, I'll need to play a bit more.

For Fun - Are you smarter than an RPD Administrator?

How well do you know the OBIEE 11g Administration tool?   Are you ready to put your knowledge to the test?   How about to three tests?   Below you will find links to three online exams to test your RPD skills.   Tests 1 and 2 are 50 questions each.  Test 3 has 33 questions.     There is no time limit for taking the test and you can take it as many times as you would like.   The passing score has been set to 80%.

The only requirement for taking the exams is to create an account with quizegg.com.  

Visit the original here.

Free Guide: The Top 10 Business Intelligence Vendors Reviewed

This is a Free White Paper that provides a review on 10 BI vendors. This is a useful comparison analysis for buyers and vendors.

“The Top 10 Business Intelligence Vendors Reviewed“
The BI marketplace has seen steady expansion for the last few years and is now one of the fastest growing sectors in the IT industry.
A good BI solution will add considerable value to your business and is also a major investment. Selecting your vendor and the solution is not a trivial decision, but one that has to be taken after a detailed analysis and study.

Format: Download. You will receive a link to download this guide after registration.
Please Click Here To Request and Download This Free Guide

Just What is BEASVC?

When you install WebLogic as a service, you're really setting up beasvc.exe as the service. This is a tool that BEA/Oracle supplies with your WebLogic installation, and all it does is runs WebLogic using parameters stored in the Windows Registry.

For a list of all the parameters in beasvc use the -help switch
Note: Some of the parameters are only to be used on install.

D:\OBIEE11G\wlserver_10.3\server\bin>beasvc -help
Usage: beasvc <options>
OPTIONS:
Options should be separated by spaces.  '/' may be substituted for '-'.
        -install | -remove | -edit | -dump
                Install, remove or edit the specified service.
                -dump will issue Ctrl-Break signal to trigger threaddumps
        -svcname:<user_specified_service_name>
                Service name associated with this service.
Use the following options only when installing the service.
        -cmdline:<java_cmdline_parameters>
                Required java command line parameters.
                This should not include specification of the java command.
        -javahome:<java_home_directory>
                Root directory of targeted java distribution.
                The java command will be formed by appending \bin\java.
        -execdir:<base_directory_for_execution>
                Directory out of which this command will be executed.
        -extrapath:<additional_path_environment_settings>
                Additional path settings that will be prepended to the path
                applicable to this command execution only.
        -depend:<dependent_service_list>
                List of services seperated by ',' that this service
                depends on.
        -delay:<delay_milliseconds>
                Milliseconds the service will be in 'starting' status.
        -stopclass:<java_class>
                Java class, whose 'public static void stop()' is invoked
                during service stop.
        -log:<log_file_path>
                Log file, into which all stdout/stderr will be redirected
        -password:<system_password>
                System password to start WebLogic Server.
        -acctname:<windows_username>
                Windows user to run this service as. If not specified service
                runs as the user LOCALSYSTEM.
        -acctpass:<windows_password>
                Password for the windows user.
        -host:<host_to_ping>
                Host address to ping for status during startup.
        -port:<port_to_ping>
                Port to ping for status during startup.
        -maxconnectretries:<max_connect_attempts>
                Number of times to ping during startup for status.

BEASVC windows services.

For each server instance that you set up as a Windows service, WebLogic Server creates a key in the Windows Registry under HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services. The registry entry contains such information as the name of the server and other startup arguments.

Debugging BEASVC.

If you are encountering errors with your service it may be worthwhile running in "debug" mode for a while.

To do this;
Firstly, navigate to the WL_HOME\server\bin directory
Then, assuming the service is called "beasvc AdminServer_AdminServer" issue the following
net stop “beasvc AdminServer_AdminServer”

Now our service is gracefully stopped we can debug the service.
D:\OBIEE11G\wlserver_10.3\server\bin>beasvc -debug "beasvc AdminServer_AdminServer"
This will give a lot more information than is available in the logs and console output.

Reference: http://docs.oracle.com/cd/E17904_01/web.1111/e13708/winservice.htm

OBIEE 11g certification

It looks like OBIEE 11g certification is headed our way. See here.

From the blurb....

Oracle Business Intelligence Foundation Suite 11 Essentials

The Oracle Business Intelligence Foundation Suite 11 Essentials identifies professionals that are skilled in implementing solutions based on Oracle Business Intelligence Suite. The certification covers skills such as: installing OBIEE, building the BI Server metadata repository, building BI dashboards, constructing ad hoc queries, defining security settings and configuring and managing cache files. The exam targets the intermediate-level implementation team member. Up-to-date training and field experience are recommended.

This certification is available to all candidates, but it is geared toward members of the Oracle PartnerNetwork. OPN Members earning this certification will be recognized as Oracle Business Intelligence Foundation Suite 11 Specialization.

Have a look at the Exam Topics, no great surprises there

Oracle BI and EPM System Overview

• Describe how OBIEE addresses business intelligence challenges
• Describe the Oracle Business Intelligence Enterprise Edition (OBIEE) products

OBIEE Architecture

• Describe major components of the OBIEE architecture
• Describe the function of each component

Oracle BI Installation

• Describe the Installation Architecture for OBIEE
• Explain system requirements, supported platforms and installation pre-requisites
• Install OBIEE

OBIEE User Interface Overview

• Describe the components of OBIEE User Interface
• Perform Oracle Analysis to construct, modify, and save BI requests
• Build Dashboards
• Create BI Delivers

Dimensional Modeling

• Describe the differences between Transactional and Analytical Systems
• Describe Data Warehousing
• Build Dimensions, Facts and Hierarchies
• Explain Dimensional Modeling

Oracle BI Repository Overview

• Describe the three layers of the OBI Repository
• Use the Administration Tool to view and modify the repository

Build Basic Repository

• Define a System Data Source Name (DSN) for a data source
• Build the Physical layer of the Repository
• Build the Logical layer of the Repository
• Build the Presentation layer of the repository

Repository Testing and Deployment

• Describe techniques for testing a repository
• Validate and test a repository

Complex Repository Modeling

• Describe Derived Metrics and its use
• Configure Derived metrics based on existing logical columns
• Configure Derived metrics based on physical columns

Advanced Repository Modeling

• Create Data Partitioning
• Create Segmentation of Data
• Create Time Series Data

Building a Repository with Multiple Sources

• Add multiple sources to an existing logical table source for a dimension in the business model
• Add a second table source to a dimension in the business model

Hierarchies and Level Based Measures

• Create a dimensional hierarchy
• Use level-based measures
• Create a rank measure
• Describe Aggregate tables and the purpose they serve
• Create Aggregate tables in the OBI repository

Using Variables

• Explain session variables
• Describe repository variables
• Describe initialization blocks
• Implement a dynamic repository variable

Implementation Methodology and Best Practices

• Describe the implementation phases

Oracle Business Intelligence Enterprise Edition Security

• Describe authentication and authorization
• Describe the different levels at which security is enforced
• Configure security using Security Manager
• Configure Query Governing controls

Cache Management & Performance Tuning

• Explain the purpose of cache in OBIEE
• Configure caching and manage cache files
• Develop caching strategies
• Describe the request and data flow in OBIEE

MS Office Integration

• Use MS Office Plug-In for OBIEE
• Describe the required components to enable office integration
• Install and configure MS office server and client for OBIEE

Spatial Integration

• Explain Spatial Intelligence and Integration
• Add Map based visualization to OBIEE

Write Back within OBIEE

• Describe Write Back capabilities
• Configure Write Back with OBIEE

Essbase Integration

• Implement Essbase as a Data Source
• Configure BI Server Metadata for Essbase
• Explain Essbase Integration

Cool vendors - Gartner

Gartner Cool Vendors 2012

Gartner have identified the next, new way to group and evaluate BI vendors. This is probably on the money, those of us who move on the leading edge of what is available and want more. We recognize the direction BI is moving, the way data will be analysed, and how mashing up of data is now within the reach of the end users (well nearly).

According to them:
"Cool Vendors in 2012 exist on a backdrop of what Gartner terms the nexus of four IT forces – cloud, social, mobile, and information. Those forces, along with the continued impact of consumerization, have essentially set the stage for the next generation of capabilities"

Read more here.

OBIEE Remove Windows Service

In my previous post I dealt with creating services to start the weblogic components under windows services.

Apart from the risky editing of the registry (not usually recommended) you need a way to remove any services created using the beasvc utility.

You need to create a small file called removesvc.cmd

and edit the contents to
echo on
d:
cd D:\OBIEE11G\wlserver_10.3\server\bin
beasvc.exe -remove -svcname:"beasvc AdminServer_AdminServer"

this assumes of course that your WL_HOME is D:\OBIEE11G\wlserver_10.3 and that the service you wish to remove is called "beasvc AdminServer_AdminServer"

Webinar on Big Data Analytics

Fast Answers to New Questions Across All Data Using Oracle Endeca Information Discovery

In this live webinar, see how leading companies are using Oracle Endeca Information Discovery, the first enterprise platform to quickly transform Big Data into actionable business insights to improve sales, customer service,...

 View the wecast here.