Setting a Sort Order Column

Have you ever noticed a property called Sort order column, located in the General tab of the Logical Column Properties Dialog Box? By default, this value is set to “None”, which is usually the desired value, but to demonstrate when this property comes in handy, let’s look at an example. Suppose your repository contains a column called “Month” and a column called “Month Number”. “Month” contains these values: Jan, Feb, Mar, Apr,…Dec. “Month Number” contains these corresponding values: 1,2,3,…12. If you have a Dashboard Prompt or a Report containing the “Month” column, the “Month” values will likely be sorted in this order (alphabetically as opposed to chronologically): Apr, Aug, Dec… Sept.

Sorting month names alphabetically is almost never the desired result; however, this sort order will be the default if the Sort order column for “Month” is set to None. You could try to correct this problem by using SQL in your Dashboard Prompt, or by adding additional sort columns to your reports, but there is a much easier way: simply set the Sort order column for the “Month” logical column in the repository. In this case, you would use the “Month Number” as the Sort order column. After reloading the metadata, the next time you use the “Month” column in a report or Dashboard prompt, OBIEE will automatically apply an ORDER BY Month Number clause in the SQL issued to the database, regardless of whether or not “Month Number” is included in the SELECT statement, or even in the Subject Area for that matter.

First published in :
http://www.biconsultinggroup.com/obiee-tips-and-tricks/setting-a-sort-order-column.html

Mixing Context Text Searching and OBIEE

Using EVALUATE and Context Searching

In OBIEE we are limited to using a LIKE operator in filters when we do text searches. There are more options available in the database like CONTEXT searching. This allows us to use a fuzzy matches operator where best matches are scored with the least likely matches receiving a zero score.

What we need is something where when we prompt for one or more keywords, results are returned even if the words are reversed, or misspelled.

This is not a tutorial in the intricacies of Oracle Text searching, which is a speciality in its own right.

As this functionality is not available nativly we need to get creative, and this solution, I'm sure there are more, relies on using the context searching capabilities of the database and the ability to call functions from OBIEE using EVALUATE. (more on EVALUATE here)

Setting up the example:
We will create a table that holds our text

CREATE TABLE text_test
(
   id     NUMBER,
   text   VARCHAR2 (1000)
);


SET DEFINE OFF;
Insert into CRA.TEXT_TEST
   (ID, TEXT)
 Values
   (1, 'cat');
Insert into CRA.TEXT_TEST
   (ID, TEXT)
 Values
   (2, 'dog');
Insert into CRA.TEXT_TEST
   (ID, TEXT)
 Values
   (3, 'kat');
Insert into CRA.TEXT_TEST
   (ID, TEXT)
 Values
   (4, 'dug');
Insert into CRA.TEXT_TEST
   (ID, TEXT)
 Values
   (5, 'dawg');
COMMIT;

Create a context index.

CREATE INDEX mytextindex ON text_test(text) INDEXTYPE IS CTXSYS.CONTEXT;

Create a function that scores our text against a search term.

CREATE OR REPLACE FUNCTION tsearch (id IN NUMBER, wrd IN VARCHAR2)
   RETURN NUMBER
IS
   retval   NUMBER := 0;
   lsql     VARCHAR2 (1000);
BEGIN
   BEGIN
      lsql   := 'select score(1) from text_test t where contains(t.text,''fuzzy(' || wrd || ',,,weight)'',1)>0 and t.id =' || id;
      EXECUTE IMMEDIATE lsql INTO retval;
   EXCEPTION
      WHEN OTHERS
      THEN
         retval   := 0;
   END;
   RETURN retval;
END;

More details on the FUZZY operator here.

Model the table through as a simple single table dimension and fact.

Lastly call this functionality from answers.

Create a new analysis and drag across the TEXT column twice, create a prompt that sets a presentation variable called p_text and edit the formula in the second TEXT column to be

evaluate('tsearch(%1,%2)',"TEXT_TEST"."ID" , '@{p_text}{''cat''}')

saving and running the report will give you something like

entering the search term cat will give you 

Happy Hacking

Making a Pivot Table Paginate

Wouldn't it be nice if you could have your pivot table split across several 'virtual' pages.

I must admit, it's not an everyday requirement and probably only applicable where your pivot table runs down and off the page.

So, how can we create a paging mechanism, as this is not native to pivot tables, and will only work under a closely defined set of circumstances.

In answers we create a new column, and this will take some fiddling around to get the optimal number, edit the column formula to something like

TRUNCATE((RCOUNT(1)-1)/12, 0) +1

and rename the column to something like page. This will give us an increasing number every 12 rows. I am assuming here that each year has 12 months and we are ordering our report year - month.

In the pivot table drag the 'page' column into the pages section. You should then see a drop down in the pages section that mimics the action of the next/previous buttons on tabular reports.

Now, not all reports - especially those that we will display as pivot tables align themselves so conveniently, where 'pages' can be grouped equally (12 months in a year). In that case you should think about a grouping using the BY clause.

Using a FILTER Function Instead of CASE Statements

Let’s face it; CASE statements are notorious for causing poor query performance. For certain kinds of simple CASE statements, there may be a more performance-friendly OBIEE alternative – the FILTER function. Like CASE statements, you can use the FILTER function to build a logical column expression. In the Expression Builder, this function can be found under Functions > Display Functions > Filter. Here is an example of how to use it:
Suppose you have two Logical Columns derived from the following expressions:

• Southern Region Units:
  CASE WHEN Paint.Markets.Region = ‘SOUTHERN REGION’ THEN Paint. SalesFacts.Units ELSE 0 END
• Western Region Units:
  CASE WHEN Paint.Markets.Region = ‘WESTERN REGION’ THEN Paint. SalesFacts.Units ELSE 0 END

Instead of using CASE statements, try using the following equivalent expressions involving the FILTER function:

• Southern Region Units:
  FILTER(Paint. SalesFacts.Units USING Paint.Markets.Region = ‘SOUTHERN REGION’)
• Western Region Units:
  FILTER(Paint. SalesFacts.Units USING Paint.Markets.Region = ‘WESTERN REGION’)

The SQL generated by the FILTER expressions will typically perform better than the SQL generated from the CASE statements. The FILTER SQL may look something like this (pretending all the columns come from the same physical table):

SELECT Year,
SUM(CASE WHEN Region = ‘SOUTHERN REGION’ THEN Units),
SUM(CASE WHEN product = ‘WESTERN REGION’ THEN Units)
FROM physical_table
WHERE Region = ‘SOUTHERN REGION’ OR Region = ‘WESTERN REGION’
GROUP BY year

The SQL generated from the CASE statements may look more like this:

SELECT Year,
SUM(CASE WHEN Region = ‘SOUTHERN REGION’ THEN Units ELSE 0),
SUM(CASE WHEN product = ‘WESTERN REGION’ THEN Units ELSE 0)
FROM physical_table
GROUP BY year

The major difference is that the FILTER SQL includes the criteria in the WHERE clause. In most cases, this means that the WHERE clause would run first, constraining the result set before the CASE statements are run, hence the improvement in performance.

Article From: http://www.biconsultinggroup.com/obiee-tips-and-tricks/using-a-filter-function-instead-of-case-statements.html

What's it all About OBIEE?

Follow this talk on OBIEE.

Some history around OBIEE and how it fits into the whole BI picture in the Oracle suite.

Alias vs Duplicate

A point of confusion sometimes arises over the difference between a Table Alias and a Duplicate Table.

The Alias Table

A Table Alias is created from a physical table, in the physical model of the repository, and any changes in the physical are immediately reflected in the alias. It is good practice to only use alias tables to build the Business model in the repository. You can rename your alias to be something more meaningful that will help other users understand it's purpose. Another use for alias tables is to resolve circular joins.

Creating an alias creates a copy of the table in metadata that will be referenced in SQL with that alias name. It will have its own ID, distinct from the parent table.

For example:
Parent Table Invoice -- id 1000
Alias Table CancelledInvoice -- id 2021

when the OBIEE engine writes the physical query it resolves the true table names, but looking at the query generated you will see:
.......
FROM INVOICE T1000 ,
INVOICE T2021 /*   CancelledInvoice  */
WHERE ...........

The Duplicate Table

Duplicate Tables on the other hand allow us to create the structure or template of a table, but at the database level you will find no physical structure. When creating an opaque view where the structure is the same as a table in the physical layer then we duplicate the table.

Using the Invoice example from above we can create a duplicate table in the physical layer. We will change the name of the table to CancelledInvoice and then the table type is set to "SELECT", and the details as "select * from Invoice where canc = 'Y'". This gives us an opaque view whic is available to be used but not deployed in the database.

10 principles of good design:

Back in the early 1980s, Dieter Rams (an industrial designer) was becoming increasingly concerned by the state of the world around him — ‘an impenetrable confusion of forms, colours and noises.’ Aware that he was a significant contributor to that world, he asked himself an important question: is my design good design?” In response to this question, he developed the following 10 principles of good design:

Good design is innovative.
The possibilities for innovation are not, by any means, exhausted. Technological development is always offering new opportunities for innovative design. But innovative design always develops in tandem with innovative technology, and can never be an end in itself.
Good design makes a product useful.
A product is bought to be used. It has to satisfy certain criteria, not only functional, but also psychological and aesthetic. Good design emphasises the usefulness of a product whilst disregarding anything that could possibly detract from it.
Good design is aesthetic.
The aesthetic quality of a product is integral to its usefulness because products we use every day affect our person and our well-being. But only well-executed objects can be beautiful.
Good design makes a product understandable.
It clarifies the product’s structure. Better still, it can make the product talk. At best, it is self-explanatory.
Good design is unobtrusive.
Products fulfilling a purpose are like tools. They are neither decorative objects nor works of art. Their design should therefore be both neutral and restrained, to leave room for the user’s self-expression.
Good design is honest.
It does not make a product more innovative, powerful or valuable than it really is. It does not attempt to manipulate the consumer with promises that cannot be kept.
Good design is long-lasting.
It avoids being fashionable and therefore never appears antiquated. Unlike fashionable design, it lasts many years — even in today’s throwaway society.
Good design is thorough, down to the last detail.
Nothing must be arbitrary or left to chance. Care and accuracy in the design process show respect towards the consumer.
Good design is environmentally-friendly.
Design makes an important contribution to the preservation of the environment. It conserves resources and minimises physical and visual pollution throughout the lifecycle of the product.
Good design is as little design as possible.
Less, but better — because it concentrates on the essential aspects, and the products are not burdened with non-essentials. Back to purity, back to simplicity.

Found in an article by Stephen Few from Visual Business Intelligence - really worth spreading around.

Can you honestly say that what you do addresses any of the points above?