Category Archives: RPD

RPD Deployment in OBIEE-12c server

RPD Deployment in Obiee 12c server is unlikely different from 11g. In 12c, EM does not have any option for deployments of rpd.We should be using weblogic scripting command “uploadrpd’ to upload repository to Oracle BI Server.

Steps to follow:

  1. Open the command prompt and type “cd \”to change the directory and press Enter
  2. Type “cd /user_projects/domains/bi/bitools/bin”, press Enter
  3. Do “Ls”, to find the utility, on UNIX and data-model-cmd.cmd on Windows.
  4. Run the data-model-cmd.cmd utility along with the upload rpd parameters below:

uploadrpd -I .rpd -W -U -P -SI

uploadrpd -I BI1_SAMPLE.rpd -W Ora234 -U weblogic -P weblogic17-SI ssi

If the operation completes successfully, you will see the following message:

“Operation Successful. RPD upload completed successfully. ”

To understand further these parameters, see below. Along with above parameters, yiu can also use S, N, SSL etc.

  • I specifies the name of the repository that you want to upload.
  • W is the repository’s password. If you do not supply the password, then you will be prompted for the password when the command is run. For security purposes, Oracle recommends that you include a password in the command only if you are using automated scripting to run the command.
  • SI specifies the name of the service instance.
  • U specifies a valid user’s name to be used for Oracle BI EE authentication.
  • P specifies the password corresponding to the user’s name that you specified for U. If you do not supply the password, then you will be prompted for the password when the command is run. For security purposes, Oracle recommends that you include a password in the command only if you are using automated scripting to run the command.
  • S specifies the Oracle BI EE host name. Only include this option when you are running the command from a client installation.
  • N specifies the Oracle BI EE port number. Only include this option when you are running the command from a client installation.
  • SSL specifies to use SSL to connect to the WebLogic Server to run the command. Only include this option when you are running the command from a client installation.
  • H displays the usage information and exits the command.

Example: uploadrpd -I <RepositoryName.rpd> -SI ssi -U weblogic -S -N 8003 -SSL

Steps to configure Write Back Functionality in OBIEE 11g

Write Back is the feature/ability in OBIEE to allow the user to enter a value or values directly into a report and have those values written to an actual physical table in the database.You can use this functionality only in a table view. If you want to use it on a pivot, you must first transform your table view as a pivot view

RPD changes to set Write Back :

Step 1:Physical Layer – Select a table to which you would like to Writeback. Double click it to open the Physical Table Properties.In General tab, uncheck Cache-able

Step 2: Business Model & Mapping Layer -Go to the table column (same as the physical layer.Double click column to open the logical column properties. In General tab, check Writable option.

Step 3: Presentation Layer –Double click the column and click on Permissions. Now in the General Tab -> Permissions -> Set permission -> Read / Write (Radio Button), on the User / Application Role which you want to authenticate for Writeback feature.

Step 4: User/Application Role – In RPD, Go to -> Manage-> Identity Manager -> Identity Management (Left pane) -> Select Application Role (to which you need Write Back Permission) -> Permission -> Query Limits (tab) -> Select Database (to which you need direct database execution rights) -> Change option from ignore to allow to the field named “Execute Direct Database Requests”.

Write Back Template :

Step 1: Check the “LightWriteback” tag within  the server instance tag of instanceconfig.xml file. If it not true, set it to TRUE. If this entry already exists then no modifications required. If modified then restart OracleBI Presentation Services for this change to be effective.


Step 2: Writeback Template. You may give any name for the Writeback.xml file. Here for example,we are using the file name as Writeback_sample.xml

Things to do before you start the Template:

  • We can use the column position or by column id in the XML definition. 
  • We must include both insert and update statements in the template.

Template to be placed in the path: $ORACLE_INSTANCE/bifoundation/OracleBIPresentationServicesComponent/coreapplication_obips1/analyticsRes/customMessages

[ Note -Above insert & update code is only for sample purpose & content within are comments. If we do not want to include SQL commands within the elements, then we must insert a blank space between the opening and closing tags. eg. rather than ]

In the above xml code, we have few important tags –

  • WebMessage name – The name here will be used as Writeback template name in analysis -> Table Properties -> Write Back.
  • Write Back ConnectionPool – Connection pool name for Write Back.
  • insert Based on your requirement or leave blank ( if not required)
  • update Based on your requirement or leave blank ( if not required)

Now lets proceed to know what changes we need to make in analysis for the write back report.

C- Changes in Analytics :

Step 1Give Privilege to the Role for Write Back ( Analytics Home Page ->Administration -> Manage Privilege -> Write Back & add the role for Writeback privilege). 
Step 2Now you need to go to that column, right click, open Column Properties -> Writeback tab-> Check ‘Enable Write Back’ of the field needed for write back, from criteria tab.
Step 3: Now edit & go to Table Properties view -> writeback tab & tick the ‘Enable Write Back’ & mention Template Name as mentioned in Writeback file ( WebMessage name=WriteBack_Template_Example” ). You may also rename Apply/Revert/Done Buttons & set positions.

Now please test the report for writeback features.

Courtesy : Google 🙂 

How to calculate Week Start Date and Week End date using DAY_DT in Obiee 11g

In OBIEE 11g, many times we have to calculate on the fly, the various dates functions like Week_Start_date, Week_End_Date, Previous_Yr_Date etc,  just using the Date column. Few projects have Date Dimensions not properly structured to have all columns. So here we have the logical sql’s to calculate the same:
We can calculate the week start date using the below sql:
TIMESTAMPADD(SQL_TSI_DAY,- DAYOFWEEK(MIN(“Dim – Date”.” Date Column”)) + 1, MIN(“Dim – Date”.” Date Column”))
For Week End Date, use the below Sql:
TIMESTAMPADD(SQL_TSI_DAY, 7-DAYOFWEEK(MAX(“Dim – Date”.” Date Column”)), MAX(“Dim – Date”.”Date Column”))
DAYOFWEEK returns an integer in the range of 1 to 7. A value of 1 represents the Monday.
Courtesy: Google 

New Features in OBIEE release:

 Some more new features have been introduced in latest OBIEE release:-

Analysis Enhancement:

1.     You can initiate a search within the subject area displayed in the subject area pane; the option is “Subject Areas Pane”.
2.     You can save columns to Presentation Catalog and reuse the same for other analysis.You can also change the properties of the new column in the Criteria tab but be aware that conditional formatting and conditional action links for the column are not saved to the catalog. Regular action links are saved for the column as well as the format properties.The saved column can be edited from the catalog pane and catalog page. All the changes that you make in the catalog are applied to every analysis that used this column. If you modify it inside a particular analysis, these changes are valid only for the analysis that you are working on.To be able to save new measures as columns you should have the Save Column privilege.
3. In this version we find a new new type of variables: the Global Variables. Global Variables can be defined in the context of an analysis and can be used in other analysis. Useful to do some intermediate reusable calculations.
4. Radio button and check box dashboards prompts can be showed horizontally. When you create a new dashboard prompt using check box or radio button under Options, you can select between horizontal or Vertical Layout appears under Options.

Export & Print options:

1.     In this version, you will find more options in the Print and Export Options dialog. You can select if you want to include charts, images and formatting or to specify column properties like the column width and to wrap the text in columns.

2.     Export menu no longer includes the Excel 2003 and PowerPoint 2003 options
3.     It is possible to set a limit to the number of rows downloading as CSV format

New RPD checkout option:

1.     If you need to check out an entire repository rather than using projects for multiuser development, you can use the Whole Rpd Checkout option

New Option in Administration Tool:

1.     Display Translation Key in presentation tree and Edit Presentation names options are now available in Administration Tool option for this release. Translation keys have been added to all presentation objects. The translation key is automatically populated and by default it matches the presentation object’s name; however, you can modify the translation key. When you externalize display names in the Presentation layer and run the Externalize String utility, the results contain the translation key. You can right-click any Presentation layer object, such as a subject area, presentation table, or presentation column, and choose Externalize Display Names > Generate Custom Names or Externalize Descriptions > Generate Custom Descriptions to externalize strings.

Report/Dashboard Changes:

1.     Once you are in editing dashboard, you might see the Advanced Page properties option that set the scope of parameters in cases of Go URL, Prompted URL and Navigate to BI content:

 2. In Tables, Pivot and similar you can see Column properties from the edit window as well.


Courtesy :  Google 🙂


Usage of Key Performance Indicators In OBIEE

To assess your business performance, you must define success via Key Performance Indicators (KPIs), benchmark metrics against which current performance can be measured.
OBIEE 11g allows you to define and customize KPIs, and to then integrate them into your dashboard. In this way you can assess current performance at a glance. Your KPI Watchlist can also be used to create analyses and agents that trigger based on certain threshold conditions and actions.

To create the KPIs, take the following steps:

1.    Log in to Oracle Business Intelligence Enterprise Edition 11g, using your username and password. From the home page, select New -> KPI from the common header area. When prompted to select a subject area, for example select any Sales/Revenue related.
2. The KPI Entry screen will then appear. This screen enables you to define the actual value and target value measures for the KPI, as well as an optional time offset to show performance as a trend.

3. Then, you specify the dimensionality of the KPI,by for example making it analyzable by two levels in the store hierarchy, and two in the time hierarchy or
you want users to be able to switch the product category for which the KPI is displayed but you want the year, 2010, to stay constant (or “pinned”). To do this, click the Add button on the Dimensionality screen, select the “Times”.”Year”attribute, and select 2010for Value. Then click Addagain to add the “Stores”.”Product” attribute column, but leave Value as Not Pinned. Click Next to proceed to the next step in the process: defining the thresholds. 
The values used for these dimensions can either be “pinned”,so that they stay static for this KPI, or
they can be dynamic, allowing the user to change them to vary their “point of view”. As well as using
regular  “attribute” columns to define these dimensions, you can also use complete hierarchies in the
form  of hierarchical columns from the semantic model presentation layer.

4. On the Thresholds screen, you can specify values that will determine whether the performance achieved warrants an OK, Warning, or Critical indication. In this example, performance will be considered OK if it is within 90 percent of the target and critical if it falls below 70 percent.


5.  Click Next to proceed to the next step, where you can define related documents for the KPI. This enables users to reference other analyses, external Web pages to support the KPI. For this example, however, there are no related documents, so click Finish to move to the final steps, where you can name and save the KPI.
 You can create more KPI in similar manner.

Displaying KPIs, Using a KPI Watchlist

Now that you have defined one or more KPIs, you can give users a means of displaying these on a dashboard through a KPI watchlist. To create a KPI watchlist, take the following steps:
1.    From the Oracle Business Intelligence home page, select New-> KPI Watchlist.
2.    From the Catalog view on the left-hand side of the screen, drag and drop the  KPIs you created previously onto the watchlist on the right. The selected KPIs will then be displayed in a grid format, showing the actual and target values, along with the variance and the percentage variance.
Courtesy : google 🙂

OBIEE11g Grand Totals with Calculated formulae (divison)

Lately, we ran into a User specific scenario, where they wanted us to display Grand total row also as division of total members and participants, based on region.
We tried a lot to achieve it through aggregation rule, using server complex aggregate, but it was not giving correct result. As you can see in image below:

Scenario was to achieve Grand total for “% Finalized column” as 154.33  instead of summation (sum) 598.86. Since our report was a Union Report , so we tried new functionality of OBIEE 11g , named “Add Result Column”.
We wrote small formula in Edit column as below, where saw_1 is region, saw_2 is Total Members and saw_3 is Participants
(Sum(saw_2 by saw_1)/Sum(saw_3 by saw_1)*100.00)
This gave us the correct answer.
Stay Tuned J