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

Wednesday, June 25, 2014

How to update report and automatically store updated by and updated time through writeback in OBIEE

Today I am going to talk about writeback in OBIEE. As you know, writeback is a feature in OBIEE that allows users to update reports that they see in OBIEE without having to deal with the ETL process and reloading table. 

There can be reasons why users would want to be able to update the records they are seeing in the report. There could be a known error that users are aware of in their accounting system that is producing the wrong Journal Entries that they are seeing, long term solution will be to correct the data from the system, but for auditing purposes at quarter-end, they are just going to manually update the record through OBIEE report so that the numbers will balance out. 

Another user case can be to have users manually update their username and password in OBIEE on their own. I know that in big enterprises, the user credentials are typically synchronized with their other credentials so it would not likely to have such requirement to let users change their passwords, but in some cases where you may have external users who are not part of the company, they may want to have their own password that can be changed. They can do so by using writeback against the user table to have username and password fields updated.

Anyways, the set up of writeback is pretty standard, which I am not going to get in too much details. Google around and you will find many articles that shows you how to do so. Basically, it comes down to the following steps:

1.you update the instanceconfig.xml file to enable writeback feature. 

2. Enable writeback on the columns you want in rpd (BMM Layer)

3. Give permissions to the application roles to have writeback, done in RPD. (Check permission identity manager and presentation column)

4. Create writeback template and save it as writeback.xml file and put it in following directory in the obiee server machine  /opt/app/OBIEE/instances/instance1/bifoundation/OracleBIPresentationServicesComponent/coreapplication_obips1/analyticsRes/customMessages/  
5. Unable writeback in presentation service by checking the setting in column property

6. Define writeback template name in table properties (Only Table view setting)

So here I am only going to talk about step 4, how to create writeback template so that not only it updates the columns, but also it automatically update the username and timestamp when any user executes the writeback.

In my case I have 7 update-able columns, and I have created 3 columns to help with the writeback: last_updated_by, last_updated_date, record_ID. Last_updated_by will store a default system generated value initally and Last_Updated_Date will store the system timestamp of the day that data was loaded to the table through ETL. Record_ID is like Row_ID, it is a unique identifier of each row of the table. 

With the requirement, I am first creating my report in OBIEE 

Noticed I added a new column called 'User'. The expression of this column is 'VALUEOF(NQ_SESSION.USER)', this gives you the user ID of the user who is using the report (and of course, who is likely going to update record next). If you have successfully configured user authentications in rpd, you will have this session variable, it could be named differently in your environment. 

In Advance Tab, you will find the XML code for this report, there you will pick up the column ID for each of the columns you are interested. I will show you an easy way to identify the column IDs:

So here is a part of the entire XML code:

Now if I want to find the column ID of Record ID, I will then do control+F and type 'Record ID' in my search box. You will find the column name highlighted by the computer search, then find the column_ID right above this column name, it will be the column ID of this column name:

Note: Record_ID is the physical column name and Record ID is the displayed column name that I made:

Using this method, I have found all of the column IDs of the columns that I am interested. 

Now let's start writing the writeback template:


 ------ The name of your template, you name it however

 ---- This is the name of connection pool that your physical table 

SELECT NULL from DUAL                                                        
UPDATE fa_table SET JE_CATEGORY = '@{c4532bb3c8bf3fa07}', COST_CENTER = '@{c2133b77cfebd0a7c}', PRODUCT = '@{cfd5c9816d8ca43fa}' ,
PROJECT = '@{c06bafe4ddddd622e}', GEOGRAPHY = '@{c55fdf58fcd9b827e}', FUTURE2 = '@{c44356274c12d521f}', YES_NO = '@{c1e7217420772545b}', LAST_UPDATED_BY = '@{cf5dd00aa4eeabdf3}',
WHERE RECORD_ID= @{c70c57d49eba128a6}  

Let's talk about this part:

UPDATE fa_table SET JE_CATEGORY = '@{c4532bb3c8bf3fa07}', COST_CENTER = '@{c2133b77cfebd0a7c}', PRODUCT = '@{cfd5c9816d8ca43fa}' ,
PROJECT = '@{c06bafe4ddddd622e}', GEOGRAPHY = '@{c55fdf58fcd9b827e}', FUTURE2 = '@{c44356274c12d521f}', YES_NO = '@{c1e7217420772545b}', LAST_UPDATED_BY = '@{cf5dd00aa4eeabdf3}',
WHERE RECORD_ID= @{c70c57d49eba128a6}

fa_table is the physical table name. The following 7 columns are to be update-able by users:


So each of these columns are equal it's column ID.

Now column LAST_UPDATED_BY = cf5dd00aa4eeabdf3. This column ID is not for last_updated_by, it's actually the column ID for column USER. The column ID of last_updated_by is never used.

The idea here is that last_updated_by will be updated with the value of column USER, which is the username of the session. 

Now last_Updated_Date = sysdate. This means whenever user is updating the record, it's happening at the present moment, therefore this column will be updated with whichever date the update happens.

In the where clause I am using record_id as the criteria because I want to ensure that when user makes an update, the update is only applied to 1 row, not a whole bunch of rows. Without record_id in the where clause, then when user updates cost center record, then all of the rows with the same cost center name that the user is trying to modify will get updated. 

After putting this writeback.xml in the right place and restarting OBIEE, let's go back to the report. Here it is import to uncheck the writeback on column last updated by and last updated on:

Define the template name in the table view properties:

Now let's run the report and make some updates:

Updating JE Catogory with random stuff:

After done and the record is updated. Moreover the last_updated_by column is automatically updated with my username.

Now it is important to sort the report based on record ID column. Because every time you update a text field with different value, the row will be re-sorted because text field is always going alphabetically. To avoid having your updated record 'disappeared' on you after you save, just sort your report by record ID.

Thanks and until next time.

Tuesday, June 3, 2014

About Merging 2 rpd in OBIEE -- The only article that matters

Hello All

About merging rpds. You all know this merge button under 'file' in Admin tool right?

This launches the merge repository wizard, which is easy.

The confusing part is, how to determine original master rpd, Modified rpd, Current Rpd?

This is what I do to remember:

Let's say there is a scenario that I have RPD1 and RPD2. I want to merge these 2 rpds so that there will be a new RPD that has both RPD1 and RPD2 objects. 

In this case (or any case), you need to create a new rpd with nothing in it. basically a dummy rpd, call it RPD(Dummy)3.

Now open either RPD1 or RPD2, which one doesn't matter, but once it's opened, use the merge button from that rpd to start merging. Therefore, the rpd that is currently opened (so that you can click 'merge' button from it) will automatically be your 'Current' RPD.

Pick RPD(Dummie)3 as your original master rpd and the other RPD (RPD 2 or RPD1) as your modified RPD, then merge..

To make it simple, when merging RPD1 and RPD2 into RPD3, this is the identification in the merging wizard:

Dummy RPD (empty RPD made by you ) = Original Master RPD

RPD1 or RPD2 = Current RPD

RPD2 or RPD1 = Modified RPD

Save Modified RPD AS: Give the resultant RPD a name and location as you wish =  RPD4.

RPD4 becomes the finishing product of this merge and you can leave Dummy rpd there for your next merge.

In the merging strategy step, if you are ever in doubt whether to select 'Current' or 'Modified', just see which rpd did these red objection come from.
In my case, all of the are from 'Current Repository', so in decision list, I pick current for all.

Let the application take care of the rest after you hit 'Finish' when you are finished selecting decisions.

The merging of rpds can take a while. If one of the RPD is 20MB, the whole merging process can take up to 3 hours.

Try to have 2 rpds that are completely unrelated with each other when merging. A lot of time, merging is unreliable, specially in this version, you never know what has gone missing after merging. If RPD1 and RPD2 have absolutely nothing in common, then the merging of the two might minimize the risk of conflict during merge. If both rpds have a lot in common, I don't recommend merging. Its easier to manually reconcile the difference than to merge.


Until next time

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
Related Posts Plugin for WordPress, Blogger...