Hey, look! A post about OBIEE with no Javascript!!

OBIEE lets you have either actual values or percent in the data labels in pie charts. I've found a trick to show both: add a new column (I called it concat) in the criteria tab which is the concatenation of the entity (shop name, for example) and its measure value. This is a string column, so we'll need to convert the measure into string. It'll look something like that: LOGICAL_TABLE.ENTITY_NAME || ' ' || cast(FACT_TABLE.MY_MEASURE as char). Edit the graph/pie view and make the concat column be the legend axis and the measure be the pie values axis. In the "Chart Type Special" menu (the button with the star) choose "Show values as Percentage of total". The result will be that the pie values will have both entity name, actual value (because now, for OBIEE, its part of the enitity name) and the percent.

Enjoy!

I can see that many developers asked on the forums how to dynamically hide column in OBIEE answers. You can find some answers there but they're obviously not elegant. In this post I'll show my implementation to solve this problem. As always, I must mention that I use version 10.1.3.4 of OBIEE and that I strongly recommend first reading my previous posts regarding OBIEE, especially the post where I'm explaining how to add Javascript to your OBIEE answers.

Read carefully the attached JS file and understand it. You must edit the numberOfResultTablesInPage variable and add your custom logic which will define which columns need to be hidden. I added my example there. The code is very simple: every time our logic determined that a columns need to be hidden, call the function hideColumn(colNumber, resultTable). Pay attention to the order of the calls to this function. Always start from the higher-numbered column.

Enjoy.

dynamicColumnHiding.js (1.77 KB)

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)

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.

Note: In order to understand and implement this you need to know how to add JavaScript to OBIEE. I strongly suggest you to read my older posts about OBIEE manipulation. this feature has been tested on version 10.1.3.4

I've been asked how to create radio-buttons prompt in OBIEE dashboards. The purpose of this is to make the dashboard more beautiful, becasue radio buttons looks better than drop down list (aka ddl). Another nice thing you can do with this is to add description to every option (as I hope to implement in the near future). So, this is how you'll do it:

• In the OBIEE Administration application, I have a session variable called MyProjectWorkingMode (replace MyProject with your project name). This variable controls the mode (or whatever) of the user (let's say it can be red, green or blue). The default value for this variable is red. This variable is important for us because we'll use it to "remember" what the user selected.
• In OBIEE Answers, I created a dashboard prompt which is a drop-down-list. This prompt has the same default value as the variable (red, in our example) and it sets our variable. important: the caption of this prompt must be MyProjectWorkingMode.
• In order for us (or the JS code) to know in which mode we are we'll create a report called MyProjectCurrentWorkingMode. Put there one column which will show our variable's value and one column from the presentation layer (all reports must go to the underlying DB). The expression in the first column will be ValueOf(NQ_SESSION.MyProjectWorkingMode). In the results tab, create a narrative view which will have a div with the id="MyProjectWorkingModeDiv" and inside it write @1. This will give us the current working mode inside the div. In the compound view, make that the report will only contain the narrative view. Save the report.
• Create a new dashboard page. Add Text object and inside it put the javascript code from the file attached to this post and replace anywhere the "MyProjectWorkingMode" with the name you entered. Make sure the name is the same as in the prompt's caption and the div. Don't forget to mark the "contains HTML markup" checkbox. Add the report we created in the previous step in a seperate Section and hide that section (Properties -> Format Section -> Use Custom CSS style = display:none). Add the dashboard prompt.
• That's it. If you did everything right, the JS code will make the ddl as radio buttons.

I suggest that you'll get over the JS code in the file attached in order to understand what I did. That will let you change the implementation if something is not as you wanted.

Last thing: Before you ask me why it doesn't work in your application, make sure you did everything right and that you have the same version as mine. If it still refuse to work, ask for help from a web/JS developer who can debug the code and see what failed. In my previous posts my features didn't work for some and they thought that this ain't working, where it worked for a lot of people who thanked me.

Enjoy.

obieeRadioButtons.txt (4.12 KB)

If you want to join fact and dimension table based on non-equality operator, such as between statement or <= and >=, you can't do it using simple join. It will throw you the error "All operators must be equality". To do it, you must use the complex join (the one with the yellow button).

From time to time I find myself writing a hierarchial query in Oracle DB. It helps me a lot when I need to understand hierarchial data, mostly resides in the source systems of the Datawarehouse. Yesterday I've seen that a lot of my colleagues haven't heard about these kind of queries, even the most experienced ones. So here is a tiny tutorial for this subject which you can go back to any time. The important thing here is that you'll know that these kind of queries exists even if you'll google it the next time you'll need it (search for "start with" or "connect by").
Let's say you have a table which looks like this:

Key	Father	Description
1	10	Renault
2	20	Jambo
3	20	Airbus
4	10	Hyundai
5	10	Toyota
6	10	Honda
7	20	F16
8	20	F15
9	10	Alpha Romeo
10	-1	Cars
20	-1	Airplanes
-1	-99	Transportation

You want to see the data in a tree form, meaning that you'll see Renault, its parent, its grandparent and so on. All you need to write is the following query:

select * from MyTable
start with KEY = 1
connect by prior FATHER = KEY

That's it. The result is:

Key	Father	Description
1	10	Renault
10	-1	Cars
-1	-99	Transportation

Note that the recursion stops only when it cannot find the next parent. In addition, many times you'll want only one row from the tree (the root, its son or some other criteria). In this case you can simply add a where clause to the query.
Unfortunately, doing the same with SQL Server is much more difficult (tell me if I'm wrong).

Enjoy.

 

"Tony Stark once saved his heart - and his life - with the Iron Man suit. Now he's found his heart and forged new life into his company. Stark Industries, by abandoning munitions manufacture for cutting-edge communications. Learn how Oracle helped with the transformation"

Have they lost their minds? Don't they have better ways to increase their revenues?

Do you know how to add JS code to your OBIEE site?

It all started with a bug in OBIEE (version 10.1.3.4) and ended with a wonderful solution that can help us in future problems. Look at the following report table:

Customer	Day	Shop
A	1/1/2000	EBay
B	15/2/2002	AdventureWorks
C	28/5/2005	Office Depot

Building the Business Model layer and the report correctly will take care that clicking on the EBay link will take us to the Ebay shop page (in OBIEE, not ebay.com) which is prompted on 1/1/2000 and customer A. This happens because the Shop report is also prompted on Customer and Day. The problem starts when we sort the table by the shop name:

Customer	Day	Shop
B	15/2/2002	AdventureWorks
A	1/1/2000	EBay
C	28/5/2005	Office Depot

I would expect that clicking on the EBay link will make the same thing as before, but actually it prompts the Shop page on Customer B and 15/2/2002. It seems that the report saves the original location of the record instead of prompting on the actual record! In order to solve that I written a small JS code that enables us to navigate based on previous fields in the report (See the attached file). In order to use it we need to change the link. Edit the column properties, go to the Data Format tab, check "Override Default Data Format", Pick Treat Text As Custom Text Format and in the editbox enter the new link. A link example can be found in the attached file. Note that the third parameter for every field is how much fields to go back.
The great thing about this solution is that it doesn't only solves this particular issue. It enables you to prompt on every logical column, no matter on which column the original value was shown.
BIPortalPageNav.js (1.04 KB)

Note: all my OBIEE posts are based one upon the other. If you want to implement this, please read and do everything I mentioned in my previous posts.

I think that this is the most impressive development that we did over OBIEE. OBIEE is very lack in paging abilities. You can navigate only forward and backwards in the report. Here is how our google-like paging looks like:

In order to implement this in your environment, you should be familiar with how to add javascript into OBIEE . Just call the addPageNumbers() function in every page you'd like. Note that this is heavily based on the current OBIEE's DOM (version 10.1.3.4). In addition, this will only work for one report per dashboard page (this was the original requirement in our dashboard site).
The JS is attached below. Enjoy.

addPageNumbers.js (6.44 KB)

Our users had a weird requirement: They wanted to search all the columns in one action. We thought how to do it. In this post I'll describe the solution. Note that this solution is not good in terms of performance, but this is the best we've got.

1. In every report (/answer/request) you develop, add a column and call it SearchColumn. Define its formula as the concatenation of all the columns in the report. You also need to cast the numeric columns to char in order to concatenate them. For example: FirstColumn || cast(NumericColumn as char) || LastColumn. To make things easier, I written a little C# program that takes the request XML and outputs this column's formula. It's a very simple program so I won't put it here (unless someone will ask for it). You can write this simple program in every language you know.
2. Hide the column (Column Properties -> Column Format -> Hide).
3. Create a filter based on this column. In the operator drop-down-list, choose "contains any". Then, click on Add -> Variable -> Presentation. Enter Term in the "Variable Expr" box (Term will be our variable name) and enter space in the (default) box.
4. Now, we'll let the user to enter the search term. Create a new Dashboard Prompt based on any column you'll like. In the operator, choose "is equal to / is in". Control - "Edit Box". Show Default To - "Variable Expression" and enter Term. Set Variable - "Presentation Variable" and enter Term here too. Label - "Search", for example.
5. All you have to do is to add the prompt in every page you want to let the user search.

Note that the search term will stay until the user will clear the prompt field. As I said, this solution is problematic for number of reason (performance, "why the other page filters too?" and maintenance).

This is quite simple, so I'll let the JavaScript talk by itself (attached). I assume that you know how to add your JS to OBIEE. You need to call the function hideTabs from every dashboard page and edit the hiddenTabs array.

This was written on version 10.1.3.4 of OBIEE. Note that if the DOM will be changed in a future version it won't work.

hideDashboardPages.txt (2.03 KB)

This is a basic post which I'll assume you know in the posts to come.
Basically, There are two ways of adding JS into the OBIEE dashboard:

If the script is for one page only - Enter the dashboard editor and edit your page. Add a new Text object from the bar on the left. It doesn't matter where you add it in the page. Click on its Properties button and write your script in the text box. Don't forget to start it with <script> and end it with </script>. Check the "Contains HTML Markup" box. There is no need to hide this object because it doesn't contain anything visual. Rename the text object and give it a descriptive name.

If the script is common to more than one page or may be in the future - Create a new request which contains only one column. It can be any column from any table, but I recommend you to use a column which doesn't have many rows (you can use a small dimension if you have one). Click on the Results tab and enter the Narrative view. Write your script in the Narrative text box. Like before, don't forget to start it with <script> and end it with </script>. Check the "Contains HTML Markup" box and enter zero in the "Rows to display" text box. You can save your request now. Every dashboard page that contains this request will run the JS you just written.

How to develop correctly with JS in OBIEE
Don't enter bunch of code in the request. It'll only get you messy once the length of the code will grow. Your code inside the dashboard should only call functions. For example, it can look like this:
<script>
if (myFunction)
{
  myFunction();
}
if (mySecondFunction)
{
  mySecondFunction();
}
</script>

You will write and maintain these functions in external JS file. Take the common.js file (search it under the OBIEE dir in the server) which is called in every page in OBIEE dashboards (don't forget to backup first!!) and add your code in the bottom of the file. Now you have a convenient place where you can edit your JS code. There is one last thing you need to know before proceeding - the browser caches the JS files so you'll need to clear the browser's cache every time you change your code. In Internet Explorer, you can do it in Tools -> Internet Options -> Delete Files -> Delete all offline content. You'll do it a lot while developing, so having a button that does that will be great. You can use Microsoft's IE developer toolbar or another developer toolbar.

Now we can apply new features to OBIEE.

While developing reports in OBIEE, I found myself making the same action over and over again: Styling a column, clicking on "Copy Cell Format", going through all other columns and pasting the format. A better way to do this kind of actions in one shot is by changing the Request XML. Let's say you applied style to the first column and you want to copy it to the other columns. This is how you'll do this:

1. In the report editor, click on the Advanced tab.
2. Copy the Request XML.
3. You'll need to edit the xml in a text editor which supports macros. My favorite is Notepad++. Paste the xml in the text editor.
4. Now you'll have to learn by yourself how the XML is built. Don't worry, it's very simple. Note that each column is a xml element called saw:column and the style is stored in its child element called saw:displayFormat.
5. Start recording the macro and copy the desired elements to the next column. After that, stop the recording. Pay attention to where the keyboard cursor is before and after the recording.
6. Place the keyboard cursor in the desired place and run the macro several times as much as the columns you need to update. The shortcut to do it in Notepad++ is Ctrl+Shift+P.
7. Paste the xml back in the report: Go back to the page where you copied the xml from, paste the new xml and click on "Set XML". That's it.

Learning the schema of the Request XML, you can do many other customizations to your report in one click. You'll see another example in my future post about searching all columns in one click. Thinking about this approach, you may find that it can help you to make your work shorter and cleaner.

One of the (many) missing features in OBIEE is record/row count. In this post I'll show how to implement record count and show it below the table, or report in the OBIEE dashboard. I know that you can find this in other blogs and forums, but I intend to show more sophisticated methods in my next posts that will be heavily based on this, so I recommend you to read it first.

1. Create the record count column in your report: click on any column in the sidebar to add a new column in your report. Edit its formula using the fx button. In the formula text area, enter Max(RCount(XXX || YYY)), where XXX and YYY are two fields which define the key, or lowest granuality of the report. In other words: The combination of XXX and YYY will not show more than once in the report. You can add more columns if you need (column adding is made using the Column button in the "Edit Column Formula" window). If the type of the column is not char, wrap the column with cast(XXX as char).
2. Check yourself: Preview the report. The last column should contain the record count. The value is the same in every row. If it's not working, go back and fix the formula.
3. Hide the formula: Click the "Column Properties" button in the column (the button with the hand), go to the "Column Format" tab and check the Hide checkbox. Now, check and remember the location of the column in the report. In other words, remember if it's the fifth column or the tenth column, for example.
4. Create the narrative: Click the Results tab and in the drop-down list choose Narrative. In the prefix, enter: "Row Count:" or something like that, and in the narrative itself, enter: <div id="RecordCount">@XX</div>, where XX is the location of the new column we created. In rows to display, enter 1. Return to the compound layout and look how the narrative below the table looks like. You can return to the narrative and change its style if you don't like it. Just leave the RecordCount div as it is. We'll use it a lot in the upcoming posts.

Problems with this implementation:

1. The count is made in the BI Server after all records are coming from the database. If you'll check query time with and without the record count, you'll see a big difference.
2. The record count is getting scrambled if the user sorts the table.

I'll post here any updates, if there will be any, about these problems.

It's been a while since I last posted here. It's because it was the end of the year with annoying deadline and a new reporting tool we needed to learn - OBIEE. We learned a lot about it and even extended it, so the next posts will be about that. I'll start with a collection of tips & tricks we collected over the development process. Some tips will be helpful only to newbies, where the others will be also relevant to experienced developers. I divided it to two sections: The administration program and the webby part. Have fun.

The Administration program

• There is no way to auto arrange to ERDs of the physical and the Business model (aka logical) layers. The best approach to make things more simple is to finish the tables import in the physical layer and arrange it by yourself: choose to show all tables, zoom to 50% and move the tables so it will create a nice and clear star schema. after that, just print it out and sent it to your co-developers.
• Naming standards are, as always, very helpful, especially when working as a team. A foreign-key column (non measure) name should end the same for all columns in the project. Measure field name should end with _sum, _count, etc.
• In order to develop in the administration program, the developer should be defined as administrator...
• The "check out objects" dialog box will get annoying after few hours of development. To get rid of it, go to Tools -> Options -> General, and select "check out objects automatically". Pay attention to what you do from now on, because even viewing a table's properties will check it out.
• You can always convert a table to a view in the physical layer. Right click on it, properties, and choose "Select" in the table type. Then, write the SQL query.
• When working online against the repository and making an action against the DB (such as row count or view data), the connection to the DB is made using the BI server's data source. Only when working in offline mode the DB connection is made using the local ODBC.
• When dragging a physical table into the logical layer it won't save the tables relationships. Only when dragging a number of tables into the logical layer the relationships will also be imported.
• Circular relationships between the tables are not permitted in the logical layer but permitted in the physical layer.
• Always use complex join in the logical layer. The BI Server supposes to understand in which physical relationship to use. Change it only when it doesn't.
• When defining one-to-many join in the ERD you drag the line from the single to the many table.
• In a dimension with a hierarchy (not flat one), the lowest level should contain the table's key column. Every column which won't be associated to a level will be automatically associated to the lowest level.
• In 90% of the cases, all the measures will have sum as aggregation rule. You can define it in a single shot by selecting all the measure columns, right click on them and choose "Set aggregation".
• The best practice is to build a dimension over any dimension-table, even if it's a flat hierarchy. Also, you should connect each fact table to its levels in the dimensions. You enter into the fact's source's properties and in the content tab you select the logical level for every dimension which is connected to the fact table.
• You can quickly build a dimension by right-clicking the dimension-table and choose "Create dimension". It won't always be good enough, but it's not so bad.

The Webby part (Answers & Dashboard)

• The error "Incorrectly defined logical table source (for fact table XXX) does not contain mapping for YYY" means that the BI Server cannot reach from table XXX to table YYY. It can happen because of two reasons: The first one is that there are no relationships defined in the logical or the physical layers (or maybe both). Note that the connections can be indirect. The second one is that the relationships doesn't makes sense. For example, going from a fact table to a dimension table through many-to-many table cannot happen. You'll see this error many times as a beginner and it can be quite frustrating at first. You need to understand that the BI Server can only understand simple and classic star schemas. More complex ERDs need to be split by using alias tables.
• While developing a complex report (answer, request, you name it) with many columns, save it from time to time. Don't forget that you're working with a webby application. Closing the window won't save your work and won't alert you if you didn't.
• After styling a column in the report, you can copy the style to other columns. In the styling window there's a "copy format" button. You can click on paste in the other columns' style windows.
• In order to make a customized report, you can use the Narrative view. To insert a column's value, use @ and the column's location. For example, @2 is the placeholder for the second column. You can also apply javascript in the narrative to make dynamic columns and effects.
• When you add a column in a fact-based report, bring it from the dimension table and not from the fact table. This will help you when you'll make navigation from this report to the dimension-based report.
• The last button () in the navigation toolbar won't go to the last page as expected but will bring all the rows. In a case of a big table, this operation will take a long time and will make the browser freeze.
• After a change in the RPD you need to update the answers site: Click on "Reload Server Metadata" below to side bar. Then, Settings -> Administrator -> Manage Sessions -> Close all cursors.

This is definitely not the last post about OBIEE. The next posts will be much more interesting and helpful. I promise!

We're now starting to work with OBIEE (aka Siebel Analytics) as our main reporting tool. Our vision is to completely replace it with the old Oracle Discoverer, who will be soon out of Oracle's support. As I'll go on and on with OBIEE, I'll post here things you might want to know. Even if you don't work with this tool you can open your mind and get familiar with new stuff.

My first experience with OBIEE - the installation, was not a great fun. The main problem was the error: Oracle Business Intelligence is not supported on this Windows version. Oracle Business Intelligence is only supported on Windows XP x86, Windows 2003 x86, Windows 2003 AMD64, Windows 2003 EM64T, Windows Vista x86, Windows Vista AMD64, Windows Vista EM64T, and Windows 2000 x86.

The problem is that the installation can't find the windows version because systeminfo is not working. Try to run c:\windows\system32\systeminfo.exe. If you get an error about framedyn.dll, this is exactly the problem. Copy this file from the wbem sub-directory to the system32 directory. Try to run again systeminfo and you'll see it's running. Make sure the PATH environment variable contains the system32 directory and that's it.

Very easy. In Sql Server:
Select top n * from my_table
In Oracle:
Select * from my_table where rownum <= n

It can be very useful in many many cases. For example, you're designing DWH over a system and you're looking at a certain field in one of its tables. You want to know which values this field contains, but fetching "select distinct my_field from my_table" takes too much time. Instead, if you believe know that the data is well distributed, you can use "select distinct my_field from my_table where rownum <= n". Use 1000 for n in the first trial and add one zero in the end of the number n every time until you got a query that takes too much time than you want to wait. after you got the n you can live with, can use the values you have in your query result.