April, 2010

What has ODI encryption got to do with clearing the OBIEE cache???

As part of a data mart refresh I am using nqcmd to clear the cache for the corresponding subject area in OBIEE. Thanks to John Minkjan for his post on properly documenting how the OBIEE cache can be cleared.

Basically I am executing nqcmd -d AnalyticsWeb -u Hans -p Zipfel -s d:\obiee\scripts\Purge_XXX_Cache.txt in an ODI procedure with technology set to Operating System.

I have currently installed an agent on the box for the BI server to launch nqcmd. However, I think that alternatively you can perform a custom install of the BI ODBC driver and the System Management component on the server where your ODI agent is running.

The major problem I have with the above solution is that the password for the Administrator password is sent across as clear text. Any ODI Operator module user would be able to view this. A gaping security hole. My first instinct when looking for a solution was to look for an encryption mechanism for this in OBIEE itself. Unfortunately no such thing is provided (as far as I know).

ODI to the rescue

I was rather frustated and ready to give up when I remembered that ODI has an encryption mechanism of its own.

Below are the steps I took to encrypt the password.

Right click on the procedure and select Encrypt. In the popup select Get a new encryption key


Save the key in a secure location as you will need it should you ever wish to decrypt the procedure (yes it can be easily lost…).


When you now execute the procedure only gobbledygook (what a great word) will show up in the Operator module.

In order to master scripting in ODI I recommend the following books.

Java BeanShell

Scripting in Java: Languages, Frameworks, and Patterns


The Definitive Guide to Jython: Python for the Java Platform.

Jython Essentials (O’Reilly Scripting)

Oracle naming conventions and coding guidelines for SQL and PL/SQL

Reading the German database pro magazine this morning I saw that Swiss consultancy Trivadis has published a very useful document on Oracle naming conventions and Oracle coding guidelines for SQL and PL/SQL. Visit the Trivadis website for other useful stuff such as their blog and technology center.

Data warehousing and business intelligence books for project managers and business analysts

In other parts of this series we have had a look at data warehousing books that cover the design and architecture of a business intelligence solution. I have also covered data warehousing books in the world of Oracle. Today we will have a look at data warehousing and business intelligence books for project management and business analysis.

The Profit Impact of Business Intelligence

By far the best book written on the subject and a must read for anyone embarking on a BI mission. In other parts of this blog I have written a comprehensive review on the book.


Agile Data Warehousing: Delivering World-Class Business Intelligence Systems Using Scrum and XP

For agile DW methodologies have a look at Agile Data Warehousing: Delivering World-Class Business Intelligence Systems Using Scrum and XP. Most if not all data warehouse practitioners agree that traditional waterfall methodologies are not very well suited to managing a DW/BI project. This book looks at how agile methodologies and scrum can be leveraged to successfully implement a data warehouse project. This book offers a detailed step by step guide at a bargain price. While you won’t take everything on board covered in this book there are some very practical pieces of advice.


Successful Business Intelligence: Secrets to Making BI a Killer App

If you are new to business intelligence then Cindi Howson’s book is for you. It covers all the relevant DW/BI aspects in an easy to read manner. Topics include: BI architectures, how to measure success, agile development, Business Intelligence Competency Center (BICC), choosing a BI tool etc. This book is mainly written for business users and is relatively light on the technical side of things.


A Manager’s Guide to Data Warehousing

Laura Reeves’ book Data Warehouse for Project Managers is a fairly recent addition to the growing number of DW/BI books on project management. It is a very easy read particularly aimed at those of you who are tasked with setting up a data warehouse. A lot of the stuff in the book only scratches at the surface, however. What makes this book quite valuable is that it covers all aspects of the DW life cycle such as ETL, dimensional modelling, business requirements, data governance etc. from a business angle. However, a lot of areas in the book just rehash Kimball’s lifecycle book. Still a valuable book aimed at DW beginners. It can be easily read over a weekend (should you wish to do so).


Strategic Data Warehousing: Achieving Alignment with Business

In my opinion this book is just a rehash of what other books have covered better and in more detail. If you want to really know about achieving alignment between the data warehouse and business strategy I recommend The Profit Impact of Business Intelligence, which is truly the best book on the subject. The only two interesting chapters of the book cover two case studies on data warehousing. The first one is on Nielsen Media Research and the other one covers the data warehouse implementation at Raymond James Financial.


Kimball Data Warehouse Lifecycle Toolkit

This is one of the classics of course. As the title suggests it covers the full lifecycle of the data warehouse. In comparison to other books that cover the same stuff Kimball’s book is written from a more technical angle and also covers a crash course for business folks on dimensional modelling and ETL workflows. Apart from this the book covers the usual stuff on business requirements, BI tool selection etc. If you are following the Kimball methodology in your data warehouse implementation then this is a must have book.


Business Intelligence Roadmap: The Complete Project Lifecycle for Decision-Support Applications

This is one of the first books on project managing a data warehouse/business intelligence program. It describes the various stages of a DW/BI project such as justification, planning, business analysis, design, construction, and deployment. It covers the full data warehouse lifecycle. Very much focused on aspects of project management.


Other books

Data Warehouse Project Management

Impossible Data Warehouse Solutions

What I am really missing in the DW/BI literature is a book on usability aspects in the realm of Business Intelligence. This should include report and dashboard design, templates for a solid security architecture, best practices around version control and deployment, query and data governance etc.