The Oracle Business Intelligence Developers Guide, written by Mark Rittman

The definitive guide to developing BI applications on the Oracle Business Intelligence 11g platform. More »

With Oracle Exadata into the highest gears of speed!

Everything needs to get faster, but how fast ? (Dutch) More »

Share knowledge

Intelligence is all about knowledge. This website is dedicated sharing expertise on Oracle BI. More »

Meet the experts

Meet worlds best OBIEE experts during the BI Brighton event by Rittman Mead More »

 

Change first column or insert column into column selector

Problem:

It is much work to change the first column of a column selector or insert a column into a column selector, when it is not at the end of the list. When you want to insert a column at position m of n, then the columns at positions (m+1, …, n) have to be deleted and have to be inserted after the new column is inserted.

Solution:

It is possible to do this by editing the XML.

  • create a backup of the report in the catalog manager
  • edit report
  • go to the tab Advanced

Advanced

  • copy text in Analysis XML
  • paste the text in notepad

You can insert a column or change the first column.

Insert new column:

  • search for the column where you want to insert the new column before or after
  • copy the part that starts with <saw:choice> and ends with </saw:choice>
  • <saw:choice>
    <saw:column xsi:type="saw:regularColumn" columnID="d2c6892c517fd1b18">
    <saw:columnFormula>
    <sawx:expr xsi:type="sawx:sqlExpression">[TABLE_NAME].[COLUMN_NAME]</sawx:expr></saw:columnFormula>
    <saw:displayFormat>
    <saw:formatSpec suppress="suppress" wrapText="true" interaction="action">
    <saw:actionLinks showPopupMenuForOneLink="false" appliesToTotal="false">
    <saw:actionLink actionLinkID="d2c6892c517fd1b18_ald3c36d8180dbcc8b">
    <saw:actionPath>[PATH]</saw:actionPath>
    <saw:caption>
    <saw:text>[ACTIONLINK]</saw:text></saw:caption></saw:actionLink></saw:actionLinks></saw:formatSpec></saw:displayFormat><saw:tableHeading/>
    <saw:columnHeading>
    <saw:displayFormat>
    <saw:formatSpec interaction="none"/></saw:displayFormat>
    <saw:caption fmt="text">
    <saw:text>[CAPTION]</saw:text></saw:caption></saw:columnHeading></saw:column>
    </saw:choice>
  • change [TABLE_NAME].[COLUMN_NAME]
  • change all occurences of columnID (for example; change the first character)
  • change all occurences of actionlinkID (for example; change the first character)
  • change [CAPTION]
  • copy the XML
  • paste the XML in the report
  • save report
  • go to the Results tab

The new column is added to the column selector.


Change first column:

  • search for the first occurence of <saw:choice>
  • <saw:columnFormula><sawx:expr xsi:type="sawx:sqlExpression">[TABLE_NAME].[COLUMN_NAME]</sawx:expr></saw:columnFormula>
  • change [TABLE_NAME].[COLUMN_NAME]
  • copy the XML
  • paste the XML in the report
  • save report
  • go to the Results tab

The new column is added to the column selector.

Het bericht Change first column or insert column into column selector verscheen eerst op OBIEE 24/7 | Oracle Business Intelligence.

User Engagement – Why Does it Matter?

The value of any system can be measured by how often you interact with it. The perfect example is Slack, which is now seen as the most successful collaboration app ever and is valued at $1.2bn. The key metric for this is DAU/MAU (the ratio of daily users over monthly users), which scales from 0 (no visits) to 1 (every user visits daily) based on how active your users are.

How does this relate to your BI or analytics system? We believe that the same concept applies. The more you interact with your BI/analytics system, the more valuable it is to your organisation.

Return on investment (ROI) of BI/analytics systems has always been hard to measure. There is widespread belief that the battle was either won or lost during the development stage of its lifecycle so, agile project approaches, stakeholder involvement, and effective change management programs are employed to closer align the system to users’ true requirements. However, even that may not drive users to use the system.

What can you do if your system is already live?

Focus on user engagement. The BI Scorecard describes typical BI/analytics systems as having only 22% user adoption. Gartner, in its The Consumerization of BI Drives Greater Adoption paper, puts the typical user adoption rate at 30%. This leaves a lot of room for improvement.

We believe that there are 5 drivers for user engagement:

  • User interface and user experience
  • Quality, relevance, and confidence in data
  • Performance
  • Ability to use the system
  • Accessibility – is the system available in the right way, at the right time?

Addressing these can drive low cost and low risk improvements to the user engagement of your system and, in turn, increase its value.

Over the next few months, we are focusing on user engagement and will be launching a set of articles and initiatives to assist organisations. We will also launch a User Engagement Service that baselines your current user engagement levels, makes a series of recommendations for improvements based on the drivers above, implements them and then measures their impact on the system. The results are a demonstrable increased ROI on your BI/analytics system.

Over the next few months we will be putting a lot of focus into user engagement, plus developing a set of tools to extend OBIEE to use technologies like D3. If you are interested in hearing more, please sign up to our mailing list below.


Preventing Download/Export for Specific Users in OBIEE

by Antony Heljula

We have seen a requirement from time to time where a customer would like to configure a dashboard to allow certain users to download/export data whilst preventing all others from doing so.

Here is one method of achieving such a requirement.   In summary, we will introduce a piece of CSS styling onto the dashboard page that hides the Export/Print buttons.   This CSS styling will appear conditionally so that it will only apply for certain users (the users who we wish to prevent from downloading data).

On your dashboard, add a new Section and set its width and height to be zero (so that it is invisible on the dashboard):


Then add a Text box into the Section:


Add the following CSS code into the Text box and make sure you enable the "Contains HTML Markup" option.   This CSS code will hide the Print/Export links underneath all Analyses:

<STYLE>

.ResultLinksCell { display:none !important }
</STYLE><STYLE>

.ResultLinksCell { display:none !important }

</STYLE>


Finally, you need to configure the Section so that it appears conditionally.   You can do this in two ways:

1) Use a "Condition"

For example: Build an Answers report that queries the ROLES session variable and will return records for anyone who is not an Administrator

WHERE LOCATE('BIAdministrator', VALUEOF(NQ_SESSION.ROLES)) = 0

2) Use "Permissions"

In this example, BI Consumers will see the Section whereas Administrators will not:


The disadvantage with the "Permissions" option is that the Administrator will no longer be able to configure the dashboard section!   The easy workaround is to simply remove the Section and add it in again.

 

Restricting Download Limits in BI Answers

by Antony Heljula

It is possible to configure OBIEE so that a system-wide download limit is in place for all users (or users with specific roles), you can apply this in the RPD.

However in some instances applying a system-wide download limit is not appropriate, for example, because you only want the download limit to apply for specific reports.

The simplest way to restrict the number of records within an Answers report is to add a filter such as "RSUM(1) <= 50":

However, there is a performance disadvantage with this approach - it is a "logical" filter not a "physical" filter.   This means that it is the BI Server that applies the filter once all the records have been extracted from the database.   So you might extract 500K records from the database even though you only want to return 50 to the user.   It can add 10s of seconds to your queries.

An alternative approach is to push the row limit function down to the database - so that it is a "physical" filter.   There could be a number of ways to do it, but in this example we an EVALUATE function that uses the Oralce Database's ROWNUM function to count and restrict the number of records on the database.

The only complexity with this approach is that you have to pass in a Logical Dimension column into the EVALUATE function just so that the BI Server can parse it without throwing a modelling error.     So what we do is just pass in a Logical Column into a comment /* */ which is ignored by the database when the SQL query is generated.

For example, here is an EVALUATE filter which passes in a "Customer Id" as a dummy Logical Column:

EVALUATE('ROWNUM /* %1 */' AS INTEGER, "Customer"."Customer Id") <= 50


The Physical SQL generated will include the following filter (with the dummy Logical Column in /* */ comments) and the query returns in 0 seconds compared to the previous RSUM(1) method which returned in 25 seconds.

and ROWNUM /* T18.CUSTOMER_ID */ <= 50


Finally, just as a security precaution we should make sure that the filter is "Protected" so that it is impossible to override the download limit via a Dashboard Prompt!

 

OBIEE 11.1.1.9: New Features for Front-End Developers and Business Users

OBIEE 11.1.1.9 was released this May and Robin and Mark wrote great posts about New Features from System Administrators and the new support for HiveServer2 and Cloudera Impala respectively. In this post, we will see some useful new features for Front-End development. Some of these features like tree map, new styles, etc. were included in the OBIEE 11.1.1.7.10 version, but I’m including them here anyway because if you are moving from an earlier release of 11.1.1.7 you probably haven’t seen them yet.

Search & Sort options inside the Subject Area

There is a new option to search inside the subject area when we create an analysis. If we press the magnifying glass icon in the Subject Areas pane, a search box appears and OBIEE will return all the objects that contains the text that we entered. We can also use the % wildcard.

search_option

In addition there is also the option to order the objects inside the subject area sorting the folders and the presentation columns inside them. The default value is showing the objects in the way that are saved in the repository.

sort_subj_area

Save Calculated Items in the Web Catalog

A very useful feature for business users is the possibility to create calculated items in an analysis and save it in the catalog in order to reuse it in other analyses for the same subject area.

In the Results tab there is a new icon for creating a new measure. The calculation is created in the same way the column formulas are created.

new_calc_measure

After the new measure is created you can go to the Criteria tab and in the column pop-up menu select the Save Column As option. In this manner you save this new measure as a new object in the catalog to be reused in other analyses. The feature of creating a new calculated measure for tables and pivot tables was included in the 11.1.1.7.10 version but the possibility to save the column in the catalog is a new feature of the OBIEE 11.1.1.9.

savenew_colcalculated_measure4

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.

editnew_measure

To be able to save new measures as columns you should have the Save Column privilege.

calculatedmeasure_priv

Global Variables

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.

To create a new global variable, select the Edit Formula option for the required column. When you press the Variable button in the Formula Editor you will see the new Global option. The Insert Global Variable dialog appears and you can select an existing global variable to be used in the formula, or you can create a new one. To create a new one you need to enter a name, type and value. If you want to use an expression like in the example (Min(Revenue)) as value, the data type should be Text.

global_variable

To reference a global variable you need to use the fully qualified name including the context: @{global.variables.variablename}.

global_var_result

Also to manage global variables you should have the Manage Global Variables privilege.

New Visualisation: Treemap

There is a new visualisation called Treemap since OBIEE 11.1.1.7.10. Treemap groups the dimensional data that you selected in the analysis in tiles. By default, the tiles size is based in the content of first measure of the analysis and the tiles colour is based in the content of the second measure. If you have only one measure in your analysis, is used for both size and colour.

treemap

You can edit it as any other view and change the group by information as well as the measures which affects the size and colour of the tile and the range of colours that you want to use. Also you can choose the style between seeing the information in coloured bins or using continuous colour fill. If you selected the first one you can also select the amount of different coloured bins do you want to use.

treemap2

New Preview Styles

When you want to preview an analysis in the Results tab, instead of showing immediately how the analysis will look, OBIEE offers you different Styles to see your analysis. These feature was also included since the 11.1.1.7.10 version. In case you want to create a custom style, Oracle recommends to use the new Skyros style as a starting point.

preview

Horizontal Layout for Radio Button and Check Box Dashboard Prompts

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.

horizontal_prompts

horizontal_prompt1

Enhancements in Export & Print Options

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.

dashboardprintoptions

In summary, these features are simple but quite useful for business users and front-end developers, and give more flexibility to create better data visualisations.