Thursday, July 15, 2010

The outer join challenge solved

I am done with my philo trip, and straight back into OBIEE. And with this change, hope to see some more followers

 
The case:
  • I am looking at spend by supplier, and as a user, I would like to have the flexibility of grouping the suppliers
  • I have a thousand over suppliers, but I am interested in grouping only a hundred or so
  • This is a very dynamic grouping that could potential change 2-3 times a week

 
The approach:
  • Define a lookup table that will have a list of suppliers, and the group to which the supplier belongs to. Lets assume type 1 to keep it simple
  • Either
    • Write ETL logic to bring all the suppliers over into this lookup table with a default value that can be overwritten by the user
    • Use a left outer join (keep all in fact) on the lookup table – this will require the user to maintain the table, and no ETL logic
The problem:
When we use the left outer join, there are 2 options in OBIEE that come to mind. 1, define an LTS to bring the 2 tables together with a left outer join, and 2, define a left outer between the logical dim and fact tables.I am not a fan of the 1st as that would use the left outer join always (whether I use the grouping or not). And the 2nd approach works except when you use a NOT filter criteria on the grouping. Consider this:
I want to get the spend by supplier group where the supplier group does not equal SG_TECH. When I fetch the results in OBIEE, all the unmapped suppliers will be missed out.
 
The solution:
Simple as always. When you map the group into the logical layer, use the ifnull(, ‘UNMAPPED’) function to set a default value. This way all the suppliers that are not mapped will not be treated as null values but as ‘UNMAPPED’, and therefore will not be excluded from the report
 
What’s next:
The “materialize” (Oracle) database hint

 
I will indulge in some “related” rambling though but will limit myself to a sentence or two, and right at the end of my blogs.
Thank god I don’t have to fill timesheets at home!

 

Monday, April 12, 2010

The world as my son sees it

Now what does this have to do with BI. Well, actually, I dont think it does...or do I!!

Each individual has a different way of looking at things around him. I was having a conversation with one of my colleagues about how we should highlight exceptions. The scenario is simple - source systems provide file feeds, and once the files are in, the transformations (ETL) kick in. And so I used my favorite BI tool to set up a complicated color scheme:
Files in time, ETL in time: Green
Files in time, but ETL not in time: Yellow
Files not in time, yet ETL in time: Light Green
Files not in time, ETL not in time: Red
The above was the user's perspective, but the colleague I was talking to was the one who had to meet the ETL SLA, and he quickly pointed out to the 2nd scenario, and said, that's what I need to know. That's what should be Red.

Now, was I talking about BI? What is BI anyway? How would you react if someone stole the stereo in your car? Would you have thought that maybe the guy who stole it needed it more than you did? And would you think the same way of all things around you?

My posts in this blogspot are going to move away from BI and I am going to start to talk a lot of nonsense. I am convinced that I will have one fan, and to the day that I am proved wrong, I will write!!! Sholong.

For people who are looking for serious tips on BI with the O in front and the EE that follows optionally, please visit http://howtobi.wordpress.com/. Btw, whatever be the path you chose to get to the end of my blog, I am glad you got this far ;)

Purging the cache in OBIEE!

I was going to write about how to delete a user in the catalog as my next post. The critical aspect, and the difference therefore, of that tip is the ability to do so without bringing down any of the services.


But then there was more than one person that was impressed with my first blog here. My friend, Hemanta, thought we should blog together, and so it happened.

So, what's the big deal about purging the cache? I am not able to recollect where I saw this, but the concept of using a logical column placed in the Fact logical table to take the value of a repository variable was a brilliant suggestion. So easily done, and so "logical". Question: what if I wanted to clear the cache ad-hoc, and without having access to the server or the admin tool?

Simple, given you know how to use a logical column to purge the cache. The cache of all tables related to a logical table is purged when the value of a variable attached to a logical column of that logical table changes:
1. Create a table with one field of type date. This table will have only one row
2. Use a direct database request to update the value in this table with the current timestamp
3. Create a repository variable to point to this value in the table, and set it to refresh every n minutes
4. Add a logical column to the logical table who's cache you need to clear, and assign this variable to this column

Should work :)

For those who have been here, and seen the one post that I had, and now this, please go to http://howtobi.wordpress.com/ and I will promise that I will not redirect you again!

Thursday, March 25, 2010

OBIEE Timestamp issue

There has a lot of talk about the OBIEE timestamp issue, and I remember a blog by Mark Rittman that suggests a cast of the retrieved date value into a DD-Mon-YYYY format as the default value in the Dashboard prompt.A Sure's a cool idea, but then I realised that when I try to constrain the values in the other prompts that accompany the date prompt, we would run into issues.
Having lived in Singapore for over 3 years now, I am tempted to use the phrase "so how?". So How? Well, the good news is there is a way out. But for those who are a little confused as to what I am talking about, here's a quickie:
You have a dashboard page in OBIEE that has a set of prompts, one of which is a reporting date field. To make it convenient for users, you would like to set the default to the most current date for which data is available. This is an easy one - assign the latest date to a variable, and default to the value of that variable. One problem though - the value is treated as data of type Timestamp, and when the reports use this value as a filter (timestamp instead of date), the Oracle DB does not use any indexes on this field causing performance issues.
Like many other things in life, the solution is very simple - in Answers, bring the reporting date field into the criteria pane, filter on the reporting date and set the value to the variable. Move to the advanced tab, copy the logical SQL and use that to default the value for the prompt in the Dashboard. The result - Magic!
Like many other things in life, the solution is very simple, but only after someone has discovered it. Watch this space out for another important tip on how to delete a catalog user. The last I saw, which wasn't a very long time ago, Kurt said we have to live with it. Now what am I talking about!!! :D