All of the topics discussed here in this blog comes from my real life encounters. They serve as references for future research. All of the data, contents and information presented in my entries have been altered and edited to protect the confidentiality and privacy of the clients.

Various scenarios of designing RPD and data modeling

Find the easiest and most straightforward way of designing RPD and data models that are dynamic and robust

Countless examples of dashboard and report design cases

Making the dashboard truly interactive

The concept of Business Intelligence

The most important concept ever need to understand to implement any successful OBIEE projects

Making it easy for beginners and business users

The perfect place for beginners to learn and get educated with Oracle Business Intelligence

Tuesday, March 4, 2014

Evaluting BI tools: Compare OBIEE with open source reporting tools

Having worked for various BI projects, often it comes to time to make decision on what tool or technology to use to implement the requirements. Often, the company has a great vision knowing where they want to go and what roadmap they want to follow, but they don't know what technology will be the best for them to achieve their goals and meet their budget. So many OBIEE projects are having difficulties and issues because from the beginning, OBIEE simply wasn't the most ideal tool for what the clients want to do. Not saying OBIEE can't do certain things (actually, there are certain things they can't), it's just that the complexity, resources and time that would involve don't always match the expectation that the clients had in mind during POCs. Therefore, It is very important to first understand that POC and real life implementation are too different things, I am just going to be as honest as I can be for the benefit of all.

Any tools can look great during POC where you don't have real data and real scalability issues. Every problem can be solved 'in theory' if the simple POC works. Look at T.V commercials, advertisements, these are the work of the best marketing genius of the world. They know how to make things look great on T.V, in ads and in demo, which is just a variation of the 'prove of concept' for general consumers. What happen after you blow your wallet for some magical weight-loss pills or some total package home care system, or even a weekend self-help workshops that promises to change your life? Then you realize that things aren't as good as what you see at first site. The bottom-line, POC is just a simple sales demo, which is not restricted to any specific environments against specific wants and needs. Now it is the buyer's responsibility to know how to apply that to their specific requirements and making it work. Anyways, enough of that.

Now lets look at what are some of the things that OBIEE isn't very efficient at doing compared to some other open source java reporting tools:

1. The implementation of localization and internationalization of OBIEE reports isn't very straightforward. There are blogs and documents out there that explains how to do such things in OBIEE, but go through them and try to implement it yourself. You will see that it does take a lot of work. Now integrating it with other home-grown java applications while maintaining the OBIEE local languages and time zones in Synch with that application is another thing. I am not saying it can't work, but it is complicated and it does take time and resources (Money.) The same thing can be done fairly easily in tools like Jasper.

2. New reporting requirements from the business users can't always be fulfilled right away. Oftenly, the requirements are identified from the OLTP side, but it is going to go through an ETL process and OBIEE configuration process before it can be made available for reporting. Several places can cause issues and there are many interdependencies that can cause data issues or performance issues. Most of all, this is not something you can get back to the user in 2 hours with new reports up and running. It requires development cycle. At the same time, in reporting tool like Jasper, you can develop these requirements directly by writing PL/SQL packages against the OLTP system, it can be a quick thing if you have a good & healthy framework

3. OBIEE has a high cost. Here, we are not just talking about the cost of OBIEE application itself (which is already pretty costly). You have to decide what ETL tool to use to work with OBIEE, if you happen to pick Informatica, then great, that's another high expenses. On top of that, you are going to need more resources, data architects, ETL developers, OBIEE developers, DBA and maybe more guys. That can be an expensive team right there. On top of that, you are not just going to have one machine that takes care of all these projects, you are going to need more machines, more hardware & software resources. That's another expense. Now, how do you feel after spending all these money but have to wait for development cycles (with high probability of delays and issues) to get job 1 and 2 done, while you see some other projects that use less than 1/50 of sources than yours but can get the requirement more agilely and dynamically done in much less time?

4. OBIEE is less flexible, less customizable (graphs, look and feels and other programmable integration features). Requirements can come in various ways. Not all of the OBIEE features are created to satisfy them. That's why there are always new enhancement request being made against the product, but it's up to the future release of the product to decide which ER can be fulfilled. If you are currently implementing your project, don't count on future releases. Open source reporting tools are much more flexible. These tools are good for people that knows how to program, how to mess around. It's like Iphone vs Android (oops I said it again) .

5. OBIEE works strictly on relational data models. In order for OBIEE to work, your BMM layer needs to have your logic tables set up and arranged according to standard star-schema. This can be a big restriction if your requirement is very complex that doesn't translate to standard star schema. Often, you will have to take the data fields from the source and design your pre-computed aggregate tables in order to make OBIEE's job easier, in other words, the complexity goes to ETL layer. Then again, why would I spend too much money to have OBIEE does easy stuffs where pre-computations are spoon fed to you? In open source tools or some other BI tools like Business Object, it is much more flexible in terms of what data model can be worked with directly with reporting.

The above are some of the most common areas where the OBIEE project has difficult challenges, specifically when expectation is not met. So before you get into OBIEE, you have to know where you are getting into to best avoid the difficult situations later on.

In my opinion, the value of OBIEE lays in the solution being delivered to you. Take BI Apps for instance, it is the best example of how OBIEE can be advantageous when the entire framework of solution is already provided to you after years of research of various business scenarios. The package, which includes DB schemas, ETL framework, reports and dashboards are already designed and engineered with the best solution in the most optimized way, can save clients tons of money and time for getting the information they desire. Imagine how it's going to be if you have to do your own research, requirement gathering, design and engineering of BI solution? Well, unless you are a genius :)

Another advantage of OBIEE is that it is easier to get into. Since the programming aspect of it is minimum, a lot of the education are conceptual based. Therefore, OBIEE can be favored by those who wants to rely on some object oriented tools to get the job done, instead of doing their own coding on a black screen. Jasper is more for programmers, it requires good understanding and design of the DB model to help optimize the report queries, or it can run very slow. For OBIEE, the performance can be much faster with standard star-schema designs (the price to pay is the time spent on ETL development cycle).

There is no perfect tool, it's up to the individuals, from company visionaries to architects, from engineers and project managers to make things work perfectly. But knowing the limitation of each tools, I hope it can drive future improvement of these technologies, but more importantly, help the decision makers to make the right decision from the beginning.


Tuesday, February 18, 2014

Interview for OBIEE position and how to succeed

Hello All

Today's topic is everybody's favorite, that is about interviews. Interview is a pain in the ass and it is for a lot of people. The more senior you are (or you think you are), the more you dislike interviews. Doing the work and talking about your work are completely two different things. When you are at work, you may run into challenges, but at least you don't have to come up with a working solution in the next 30 minutes if you can't. However in interviews, you are expected to provide proper responses in 5 mins. Therefore, if you want to excel at interviews, you should prepare yourself on your emotional intelligence, not so much on memorizing the things you can google.

Some of the mostly overlooked questions are:

Tell me a little bit about yourself, in particular, your experience related to OBIEE, BI Apps, Informatica, project management etc.

What is the most challenging problem you have encountered in your past experience with OBIEE, BI Apps, Informatica, project management etc, and how you solved that problem.

What is your approach to _____?

See, these are open-ended questions, depending on how you respond, the interviewer can throw anything at you. But first and foremost, it is important to prepare a well structured response to all these questions before any interviews. You don't have to memorize your speech word for word, but you do need to follow your structure. Over time, based on your growing experience of interviews, you might refine your speech.

Oftenly, interviewers aren't much better than you either. However, they are in the position to make decision, therefore, you need to learn to interact with them properly.

Sometimes, interviewers make random comments at your responses that may affect you emotionally. You were describing how great your previous project was, or how complex your design was and then here he comes with 'That's not a big deal.' or 'Aren't you over-complicating things? ' or even 'How is that going to work?'. Suddenly, you feel you have been disrespected, looked down on or misunderstood. This is very common among experienced job candidates.  The more experience you have, the more likely you will resist other's criticism, especially in the engineering fields where people rarely worked on people skills. You have been through so many rounds of technical discussions at work, you are so used to arguing with your coworker and you always win, therefore, you can't handle when people don't show appreciation of how great you think you are during interviews. Your subconscious will look at this interaction just as another technical discussion or even a 'political fight' in the office. You start raising your voice and so does the interviewer. At the end, you may win the argument, but lose the interview; or this verbal exchange will leave a significant mark on your psyche that after that all you are doing is mumbling to yourself with 'this guy is an asshole' and you totally forget about the fact that the interview isn't over.

Over the years of interacting with engineers and managers, I have realized something when it comes to technical discussions. If it occurs during the interview, it's easy. Just answer the question with whatever you know, don't worry about trying to convince the other person. If the interviewer is saying something you don't like, just take it easy. Reply with 'ok, that's interesting you said it', 'ok, I see your point' or 'What you said also make sense, I think we can definitely discuss this in more details off line.'. For God sake, just get over with this question and go to the next, if the interviewer wants to show off his knowledge, that's even better, let him do all the talking, he might enjoy so much that he would hire you based on that. Now of course, if you are applying for management, sales or customer service jobs, that's different. For engineering jobs, the less you talk the better it is.

In today's world, the competition in IT is getting fierce, especially in niche markets like OBIEE, Hadoop, Salesforce, Tibco where there are plenty of candidates, the interviewers rarely ask questions where you can google the answer anymore. They want to find out whether you really have experience or not. Therefore, a lot of questions are open-ended, opinion based questions. While it is important for you to prepare your speeches, it is just as important to realize that there is nothing wrong with you if you don't get the job after.

Everything happens for a reason. How you perform during your interview is always an accurate reflection of where you stand in the job market and your relationship with this particular interviewer. There is no surprise no matter what the outcome is. Take it easy. Try to learn something from the interview if there is anything to be learned, remember the questions that you couldn't answer and research on it afterwards. Do what you can to improve your chance for the next interview. If you fail, don't take it too seriously. I have worked in this field for long enough time that I personally can tell you that you can rejected for all kinds of reasons that it might even sound ridiculous to you. Just chill out, don't think about the interviews after it's done.

Hopefully, after working in the field for long enough time, you build up your personal network well enough that opportunities will get referred to you rather than you having to apply and compete for it. Building contact is one of the most important things to do in life and that's how you can minimize your chance of getting interviews in the future.

Until then:

Saturday, February 1, 2014

Create Pixel Perfect Report using BI Publisher that includes charts, tables and dynamic SQL query


This time I want to share with you how to create a pixel perfect BI Publisher report out of an existing dashboard that includes different type of reports.

When it comes to reporting, the BI Dashboard is highly interactive, it allows navigation, ad-hoc query and other dynamic features. That's the value of OBIEE. However, sometimes when you want to send the results with pdf or other formats, it is not so pixel perfect. In some areas people don't mind, but in a lot of inter-company communications, the version has to be pixel perfect. I mean, have you ever seen an invoice letter from your bank that's components are out of proportion? This is why BI Publisher has been integrated into the OBIEE platform.

Take a look at the following dashboard:

It has multiple charts, tables and texts arranged in a articulated way. There are even reports created using direct SQL query:

So reports like the following are regular reports displayed in charts or tables:

Some reports like the following are created using direct SQL with presentation variables passed from the dashboard prompt:

So lets see how we can create the same version using BI Publisher which is pixel perfect when you print it out.

We will start by creating new data model. In the data model windows, go to diagram and select Oracle BI Analysis for the reports that are created using regular approach. I then locate all of the saved BI analysis that I want to create, just select them one by one.

For the one that are created using SQL query with presentation variable, you need to change the data set to using SQL Query and paste the SQL query into the windows:
As shown in the screenshot, the syntax of the presentation variable will need to be changed from @{PV} to :PV:

After these are done, close the box and it will automatically ask you to create parameters. Just create the parameters with the same Presentation variable name and give default value:

After some times, all of the data sets (each one corresponds a BI Dashboard analysis) are create as shown:

Now save the data model:

Now create new BI Report using the data model that just created as the data source. While creating and going through each windows, it will ask you for layout templates or add tables, just click next and go through them without doing anything:

Then save it:

Now open the saved report. It is going to run and most likely it will not return anything. But it will allow you to edit report, so do just that:

It will open up a blank template, this is where you can feel free to design your own report arrangements:

One the left side, all of the data set that you created earlier for that data model will be available to be put on the blank sheet.

The insert type on the top of the sheet allows you the insert all kinds of objects, from gauages to tables to charts. I used layout grids to break this blank sheet into different smaller subdivisions and then insert each of the reports into those subdivisions:

The properties on the left side of the sheet allows you to adjust it's size, color and arrangement to make it look good:

After it's done (these are not technical work, it is more tedious art work), save the report. 

Now we can add this report to the original BI Dashboard:

The BI came be launched here:

Now after launching the BIP report, it has to pass the value of the BI dashboard:

The result is working:


Until next time

Friday, January 24, 2014

Case Study: Emailing OBIEE dashboards with ad-hoc agent that dynamically passes dashboard prompt values based on user request -- Part 3

After rpd work is done, let's go to the dashboard.

Well before that, I am manually creating these users with '_1' and so on in weblogics and make sure they all have their OBIEE account set up with the proper emails.

Now go to dashboard and we will copy the existing dashboard and reports and create another version for emailing agents.

First, modify the date and month prompt and manufacturer prompt

Here in the default section, we are adding session variable as filters:

The idea is to use timestampadd function here to add a new year and month value based on the integers of the session variable of each 'startmonth' and 'endmonth', then add 'yyyy' with 'mm' to make it 'yyyymm' as numbers. This new value then will be filtered against the year and month column of the date dimension.
If the dashboard is at date level, then for the same logic, we use timestampadd to compute a new date using the integers from session variable  'startmonth' and 'endmonth'.

For the other prompts, I realized they are variable prompts, and there is no computation like Date and Month prompt does, so we will use variable expression here to call session variable:
Syntax is below:

After getting done with the prompts, we will make necessary changes of the reports and dashboard structure.

Now take a look at the dashboard. Initially the prompt values will mostly be empty because it is run by admin user who doesn't have anything defined in the security table.

Now create an agent and set it as 'run as recipient':

Recipients of the agent would be all those test users I created:

Save and manually trigger this agent and see what we got:

Now I got a few emails since all of these users have the same email account, which is mine:

Now I have to open each up to make sure the contents are different and the values should be different. The first 3 emails are all for user 'charles', which is recipient charles_1, charles_2,charles_3:

Ok, 1 for manufacturer 'HMA', customer pay, dealer cost $300 between July 2013 and Sept 2013

For manufacturer 'AUDIUSA' and different other prompt values:

For Manufacturer 'PORSCHEUSA' and different prompt values:

This report is for user 'dchadsey' and all of the parameters defined for this user in the table are passed along

Now let's test for error handling. I will manually update dchasey's MFG_HIERARCHY value to 'BMW'. This will be a wrong value for this user because in the security table, this user is not scoped to that manufacturer.

Now rerun the agent and i should be getting an email with no data. It would be a breach of confidentiality if this user indeed gets data for BMW that he is not scoped to.


By changing the value to BMW, the query defined in the session initialization block should return nothing, therefore the 'Manufacturer prompt would be empty.' Of course, I then played some tricks by adding conditions of each dashboard section to hide or display them based on whether manufacturer prompt is blank or not. There are many ways to do that, which I am going to skip for now.

So, the prove of concept works for a few test users. However, this is still not enough if to implement this at a larger scale. There are two main pieces of the puzzles according to me:

1. users will probably document their request in the form of spreadsheet and upload it somewhere. For each request of delivering a different dashboard, there will be a separate table like the one I designed. The user defined parameters will then be loaded into our DB table and adding '_' and sequence number behind each user name will need to be done. Managing this process of communicating between users inputs and DB tables will need to be in places. A lot of decision makings need to happen. User could even request an UI for their updating of request, which will need to be engineered to communicate with the DB Table systematically.

2. Manually creating of all these user_1s in OBIEE will be a nightmare. I know OBIEE has a project called 'SA SYSTEM Subject area', which once implemented, will automatically populate users into OBIEE and associating it with emails. However, the requirement for this to work is pretty rigid. If I can automatically populate columns for 'user_name' (all of the _1s values) not the actual 'users' column using SA Subject Area, then this would be a huge win.

3. Different dashboards are created differently. Some dashboards are more complex than others. For each specific dashboard to be delivered, it will have to be modified differently to pass the session variables correctly. There is no one size fits all solution to this approach.

Having said all that, I think I have come to as far as I can come with this solution. It's not perfect solution, in fact, it may be a terrible solution in someone's eyes, but it does work to some extend. Maybe oracle has plans for this type of requirements in their future products.

For now, I am opened to see other solutions.

Until then 

Thursday, January 23, 2014

Case Study: Emailing OBIEE dashboards with ad-hoc agent that dynamically passes dashboard prompt values based on user request -- Part 2

From the previous time, I have finalized the DB table design, now let's check out what I did in the rpd.

Below are the session initialization blocks. 'Security' and 'GROUPS' where created for the existing data level and object level security implemented before.
Now I am adding a new one 'Security_Agent':

In Security Agent, I have created session variables for each columns of the table. These session variables will be passed on to the dashboard later.

The initialization string is the following query:

USERS in (select USERNAME     
from table(Ret_User_Details(substr(':USER', 1,  instr(':USER' , '_' , 1) -1 ) ))  ) 
and MFG_HIERARCHY in (select company_name     
from table(Ret_User_Details(substr(':USER', 1,  instr(':USER' , '_' , 1) -1 ) ))  ) 
USERS in (select USERNAME     
from table(Ret_User_Details(substr(':USER', 1,  instr(':USER' , '_' , 1) -1 ) ))  ) 
AND USER_TYPE in (select SCOPE_NAME FROM table(Ret_User_Details(substr(':USER', 1,  instr(':USER' , '_' , 1) -1 ) ))  )

The blue part of the query takes the input of (:user), which is going to be in the form of 'user_1', strip everything after '_' in order to match in the security user table that the client company already has, then it returns the values of the same user in my new DB table with corresponding dashboard parameters. The user in my table must exist in the security user table, not only that, but the corresponding manufacturer of that user must also match the same in the security table. This takes care of the error handling of man made mistakes. 

The red part of the query is identical to the blue part. However, this part is only for users who are company's internal users. If you followed my previous implementation of data and row level security, you will realize that for company user, there is no corresponding company name of that user in the security table. It is only flagged by 'HAS_GLOBAL_APP_SCOPE' = 1 in that table. So the blue part of the query takes care of external users, but it will leave out all of the company's internal user, which is not good. Therefore, the blue and the red are both needed and it will be unioned. 

All variables are set with default values:

This is all I need for the rpd.

Next time, the most tedious work will be begin, which is modifying dashboards to receive these session variables.

Stay tuned.

Related Posts Plugin for WordPress, Blogger...