Disclaimer

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:


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}',
LAST_UPDATED_DATE = sysdate
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}',
LAST_UPDATED_DATE = sysdate
WHERE RECORD_ID= @{c70c57d49eba128a6}

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

JE_CATEGORY, COST_CENTER, PRODUCT, PROJECT, GEOGRAPHY, FUTURE2, YES_NO

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.

Note:
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.


Thanks 

Until next time

Related Posts Plugin for WordPress, Blogger...