Oracle SQL Update Statement with INNER JOIN

Oracle (the database) does not support joins in the UPDATE statement. This is one of many tiny annoying fact about Oracle. Here are 2 different ways to overcome that limitation.

The subqueries

 

The updatable view

 

MySQL provides, in my opinion, a  clearer syntax to update

 

I can now refer to my blog as an extension of my brain :)

Is BI about content or container ?

What is business intelligence in everyday life? Is the BI team create content, or create the container that distributes content?

Certain teams develop performance reports using data from specific business applications. They receive requests from every department of the company. They play a role of data centralization. The interpretation is left to power-users and reports are limited to exactly what is required. These teams develop a container. They give views on information in the application.

Some teams are mandated to develop analysis and to provide discoveries. They centralize data from multiple sources with a common goal. Business analysts and power-users may be part of the team. These teams create new content. This is especially true if they make predictive analytics.

Have you noticed that I used the word “report” for the teams that develop the container and “analysis” for those who develop content? It can give a good hint about the kind of team you are.

However, this observation is insufficient to answer the question. We must look at the team operating model rather than the result it produces. Whether the team develops reports or analysis, it must pass through a development process: analysis, documentation, development, test / quality assurance and deployment. A project management model has to be adopted to avoid total chaos. The development itself is not limited to create a simple SQL query to display the result. There is a lot of architecture activities (the schema, the etl process, etc..). These activities are the same as a traditional software development team.

With that in mind, is BI is about content or container ? Sometime, BI teams have to create big containers to produce tiny, but so valuable content.

I still don’t know the answer…

Calling a (sub)transformation inside a transformation with Pentaho PDI

I recently discovered a great and powerful feature inside Pentaho Kettle. It’s the Mapping step. It allows you to call a transformation inside another transformation.

It is not intended to call a transformation the same way you do with a Job. The way I see it is like a way to create a new Step. If you have a developer background, I could refer to a function. If you have a sequence of steps you repeat often, create a mapping with that sequence! It’s a really nice shortcut.

Let’s take a closer look at how it works.

Suppose you have a transformation to retrieve the total and free ram of the server running the transformation, calculate the quantity of used ram (total – free = used) and write it to log. The transformation should look like this:

getRamTransformation

To create the mapping, you have to create a new transformation with 2 specific steps: the Mapping Input Specification and the Mapping Output Specification. These steps allow the parent transformation to pass values to the sub-transformation (the mapping) and get the results as output fields. In the example, I will create a mapping that gets the total and free ram as input, calculate the used memory and convert it to MB and send it back to the parent transformation. The transformation should look like this:

calcUsedRamTransformation

As you can see, the exact same 2 steps from the first transformation have been duplicated in the sub-transformation. The Mapping Input specifies the 2 fields that will receive the values from the parent transformation. The name of the fields don’t have to be the same from parent transformation.

mappingInput

In the first transformation, the 2 steps can be replaced by the mapping step.

getRamTransformationMapping

In the Input Tab of the step, the fields from parent transformation have to be mapped to the fields declared in the sub-transformation.

mappingSubTransformation

There we go ! It’s that easy !

You can download the transformation used in the screenshot

 

Don’t try to fool the transformation execution.

All the rules regarding the execution sequence still apply!

  • It is not possible to set a variable in the sub-transformation and get it in the parent transformation.
  • All the input steps start at the same time, regardless if the input is in the parent or sub transformation

Since I discovered the mapping, i’m using it a lot to make my etl processes way more modular.

Bash Script to easily start or stop OBIEE 11G on Linux

On linux, starting OBIEE requires to start

  1. Weblogic Server Admin Server
  2. Weblogic Server Node Manager
  3. Weblogic Managed Server
  4. Oracle Instance

All of the servers have to be running before starting the others. Looking at the log waiting for the running state to know when to start another service is a painful and time consuming task. So I wrote a little script. Laziness is sometimes a good thing :)

All you have to do is to modify the configuration variables section with your own values.

Enjoy !

Review: The Data Warehouse Toolkit; The Complete Guide to Dimensional Modeling

dimensionalmodeling

Last year I bought The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling by the Kimball Group. Ralph Kimball is known as the father of dimensional modeling. The guy really knows what he is talking about. If dimensional modeling is NOT something you are familiar with, then this book is for you. If you are familiar with dimensional modeling, you should read it anyway! It shows how to build a good data warehouse by using the best patterns.

Each chapters use a different business area including:

  • Retail sales and e-commerce
  • Inventory management
  • Procurement
  • Order management
  • Customer relationship management (CRM)
  • Human resources management
  • Accounting
  • Financial services
  • Telecommunications and utilities
  • Education
  • Transportation
  • Health care and insurance

It covers almost every situation you can face!

The second edition has been released in April 2002, so it’s a bit old.  Some examples have clearly been written a long time  ago (probably in the first edition) but most of the concepts still apply.

The next book in list is from the same authors: The Data Warehouse Lifecycle Toolkit

Download and setup OBIEE 11G Usage Tracking (11.1.1.7)

Activating USAGE TRACKING with OBIEE 11G (11.1.1.7) is not an easy thing to do. The tutorial provided by Oracle is incomplete and contains a lot of errors. So to make your life easier, you can download a fully compatible Usage Tracking repository for OBIEE 11.1.1.7, the Usage Tracking Catalog and the Installation SQL Script.

  

Quick Notes on these files:

To create the repository, I downloaded one from http://total-bi.com/2011/09/obiee-11g-usage-tracking-rpd/ but I had to tweak it a bit to make it compatible with 11.1.1.7.

  • A new Column (ID VARCHAR(50) NOT NULLABLE) in table S_NQ_ACCT
  • All columns in S_NQ_ACCT are now NULLABLE (except for the colums ID ans CACHE_IND_FLG)
  • Many foreign keys were broken so i recreated it
  • The join in the “Measure” logical table have been rebuilt

The SQL Script is the 4 scripts you have to run on your [prefixed]_BIPLATFORM schema (the one created with the rcu setup) t. I have appended them in the good order and I

  • removed some statements that cause errors (2 drop table on tables that don’t exist)
  • added 1 missing CREATE VIEW.

I found the catalog on my server (OBIEE_HOME/instances/instance1/bifoundation/OracleBIServerComponent/coreapplication_obis1/sample/usagetracking). This is an archive you can import with the Unarchive feature in Catalog Manager.

  • I had to rewrite the filters of every analysis
  • The first page name “Dashboard” is the only dashboard page provided; I created the others..
  • I wrote a few analysis and added it to the pages I made

I created the new dashboard pages because I don’t like to have just a bunch of links.. I recommend to create your own analysis and add it to a new page like I did.

The last thing to do is to activate Usage Tracking within Oracle Enterprise Manager. You can follow the step by step procedure provided by Oracle at http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/bi/bi1116/ut/ut.html  (section Configuring Usage Tracking in Enterprise Manager)

Hope this help.

References:
http://obiee101.blogspot.ca/2008/08/obiee-setting-up-usage-tracking.html
http://paulcannon-bi.blogspot.ca/2012/11/how-to-install-usage-tracking-in-obiee.html
http://adventuresinobiee.blogspot.ca/2013/02/how-to-enable-usage-tracking-on-obiee.html
http://total-bi.com/2011/09/obiee-11g-usage-tracking-rpd/
http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/bi/bi1116/ut/ut.html