My next posts will discuss the project I just finished (at least its first phase) which is about HR (human resources) BI. In this post, I'll describe the data dictionary feature - why and how we did it.

From the first day of this project I've seen that data dictionary is extremely necessary. Different people uses the same term but they mean different things. There is no consensus about most basic term in the HR world - employee. I asked different people the simplest question: "What is an employee" and I've got different answers - Only full time employee; only one that gets the payment from me, meaning no outsourcing or contractors and so on...

The solution I thought about is a simple but strong data dictionary (aka DD) which contains all the terms and their definitions. The DD is maintained only by one person who is the main customer of the BI system. This is because letting it be edited by more than one person will make the mess rise up again. Now, let's continue to implementation:

Step 1 - Creating the DD
I created a new list in MOSS/SharePoint. The list has only the following columns: Term, Definition. That's it! As I mentioned, only one person has the right to edit this list. Using the help of our infrastructure team, I made a small ETL process which takes the DD from MOSS and writes it into an Oracle table in the DWH. This DD-ETL runs in parallel with the BI ETL and won't fail the whole process if it fails.

Step 2 - OBIEE Administration Tool
In the OBIEE Administration program, import the DD table to the physical layer. Now we have a slight problem - the DD table is not connected to any other table in the model because it's not belonging to the star schema and OBIEE doesn't accept it. To solve it, we'll create a new view (new table -> properties -> Table Type: Select) in the physical layer which will only contains the terms from the DD table (select term from DD_Tabel). We'll call this table/view DD_Dummy and connect it (foreign key) to the real DD table using the term column. Take these two tables to the logical layer (aka Business Model) and only pull the real DD table to the presentation layer. Now we have a working model with the DD table in the presentation layer.

Step 3 - OBIEE Dashboard
If you had read my previous OBIEE posts, you should know by now how to add Javascript code to your dashboard. Add the code in the attached js file and add the attached css too (edit the file res/s_oracle10/b_mozilla_4/custom.css in the OBIEE server).  Create a new report which shows exactly the DD table. In every page you want to have the DD feature, do the following:

• Add a new section and hide it (Properties -> Format Section -> Use Custom CSS Style -> display:none)
• Add the DD report into the hidden section
• Below it, add a text element and inside it write:
  Mark the "Contains HTML Markup" checkbox
• Add a new hidden text element anywhere you want which will contain the script (you can use the same hidden section if you want) and put there the following script:

That's it. If you did everything right, the magic will start immediately. Moving the mouse cursor above any of the column headers in every table in your page will popup a little tooltip with the definition of the column header. Note that if you already have my old javascripts in your OBIEE you might have some functions duplicated. Delete the duplicated functions manually.

DataDictionary.css (.36 KB) DataDictionary.js (2.42 KB)

There are two ways to search for texts in mappings. The first one is to use the "Find in workspace" tool inside Informatica (CTRL+F). The problem with this tool is that it's looking for ports and transformation names. What can you do when you need to know in which transformations you used your unconnected lookup? For this sort of questions, you can use the other way: Export the mapping to XML file (Repository -> Export Objects) and search inside the file. There, you can search inside expressions, source qualifiers overrides, lookup filters and so on.

When using the date picker in OBIEE, the date format that it works with is DD/MM/YYYY. In out organization, oracle is tuned to use another date format, so it causes trouble. What we did to solve this is very simple. In the OBIEE Administration tool, go to the physical layer and open the connection pool. In the connection script tab, enter the following into the "Execute before query" section: alter session set nls_date_format = 'DD/MM/YYYY'
This statement will execute before every sql the OBIEE sends to oracle and tell the DB to use the DD/MM/YYYY format.

After a long break from blogging which caused by a lot of work in my organization, it's time to come back. I learned a lot in the last months and I have a lot to write. This time I'll write about working with static parameters in Informatica (version 8.6.0 HotFix 3).

Let's start from the beginning: Why should you work with parameters?
The answer (at least the main answer) is simple: It prevents hardcoding. For example, let's assume you write the following formula in your mapping: IIF(MyPort > 5, TRUE, FALSE). As we know, in the real world everything can change and now the formula needs to have 6 instead of 5. Changing this will make us open the mapping, edit it and worst of all - install it in the production environment, and as we know it can always make a lot of trouble. If the number 5 was stored in parameter file, all we needed to do is to edit the parameters file (in the production, of course) and that's it! Another good reason is that sometimes you can use the same mapping several times, each time with different parameters and that can ease the development for you.

How can we make it? We'll do it step by step (by the way, in order to learn this thing create a small workflow just for training):

Step 1 - Using Parameters in the Mapping
In your mapping, go to the Mappings menu and choose "Parameters and Variables...". There, you can create and edit your parameters (only within this mapping, of course). The name of the parameter should start with $$. (For example: $$MyParam). These are the properties you can edit for every parameter:

• Name - Like I mentioned, should start with $$. Make the name as much descriptive as you can.
• Type - Parameter can't be changed during the session and variable can. In our scenario we'll use parameters which are taken from parameters file, so choose Parameter.
• Datatype - There's no much to explain.
• Precision - If you're using string, make the precision at least 100 chars. It will prevent troubles when your parameters contains expression.
• Scale - comes along with precision, where it relevant.
• Aggregation - Relevant when you use multiple partitions in the pipeline. We'll leave it for now as the default.
• IsExprVar - Very important. It determines if the parameter is an expression or a static value. Practically, if you use it inside an expression transformation, it should be True. Otherwise, it should be false.

After you defined your parameters, you can use it in your mapping in several ways:

• Source Qualifier - you can override the whole SQ or add a source filter from parameter.
• Lookup - you can override the lookup's sql or just its filter.
• Expressions - Using a parameter as a placeholder inside expression is quite difficult because Informatica adds "" before and after the expression. Coming back to the example above, that's why we can't store 5 in the parameter. What we're doing to solve that is to store the whole expression in the parameter (the whole IIF(...)). After doing that, we've find out that this makes the parameter file clearer.

Step 2 - Build your parameter file
The parameters file, as its name suggest, holds the values of the parameters for the whole workflow. There are three types of lines in the param file:

• Comment - starts with #. Very helpful for describing the other lines and for making order in the file.
• Section headers - The first section contains the global parameters which are relevant for all the params in the workflow. The section starts with line contains only: [Global] (this should be the first line in your param file). After this (first) line, write all the global parameters. The other sections are session-specific, meaning that the params in these section will only affect one session. These section headers will look like this: [MyInformaticaFolder.WF:MyWorkflow.WT:MyWorklet.WT:MyInnerWorklet.ST:MySession]. The parameters that will come after this header will only affect the session called MySession which is inside the the worklet MyInnerWorklet and so on.
• Parameters - each line in the file will contain only one parameter. The syntax is: $$MyParam=value. Don't write space between the equality sign (=) and the value because it will enter into the parameter itself.

As you can see, it's quite simple. Now, let's end this with the last step.

Step 3 - Attaching the parameter file to the workflow
Very easy. In the workflow manager, open the workflow and go to Workflows -> Edit -> Properties. Insert the parameter filename (with the path, of course) and that's it.
Now, for some important tips that will save you a lot of time:

• The integration service reads the param file only when you run the workflow. Re-running a session or worklet after changing the file won't affect the results.
• Unline what they write here, you can pass parameters to the mapplet. In order to do so, this is the correct syntax in the param file: MyMappletName.$$MyParam=TheValue. When this line is under the session header, it will only affect the mapplet called MyMappletName which is inside the session/mapping.
• Don't use initial values for parameters. You won't know when the param file is not correctly attached to the session.

There's much more to discuss - using Informatica, you can use dynamic parameters and variables. You can get a value out from a session and use it in other sessions. I'll leave it for now for future posts.