OBIEE - Clear Cache -SAPurgeAllCache()

Warning !!!!
What follows is technical (geeky) and should be approached only by those personnel wearing appropriate safety equipment!

One command to remember

Call SAPurgeAllCache()

Login to dashboards as normal, and go to Administration and then Issue SQL

In the box that appears input the command from above.
Call SAPurgeAllCache()

Click Issue SQL

Server Cache cleared! I’ll leave you to clear the browser cache.

Here’s what the server cache looks like.

You can also clear the cache of specific databases.
Call SAPurgeCacheByDatabase( ‘MYDB’ );

Will clear just the MYDB database cache i.e. the OBIEE physical database in the right column.

There are other similar commands to clear specific tables / queries. That I’ll leave to you.

OBIEE MUDE

It's been a while since I last put up any substantial post so I thought maybe it was time to leave you with a brain dump of what I've (been attempting to) achieved over the past month.

Two major things stand out;
1. MUDE - Multi User Development Environment.
2. Integration with SVN Subversion.

The first was a no-brainer really, a few too many developers in the repository at once, lots of half finished code, the nightmare of promoting the repository to production based on what was complete or mostly complete. Definately time for MUDE.

For those of you unaware of MUDE and what it is, not many I'm sure, it is a method of segregating the repository into various projects, where each project is self contained and has all the component parts required to function. The projects can be checked out to a local install by developers where they work, in isolation, before merging their changes back into a central repository.

MUDE

To setup MUDE you need three things; a bi server, a shared directory on that server and a full local install of OBIEE.

On the server create a shared directory and ensure that all your users have sufficient priveleges to merge changes back.
The MUD Administrator then copies in the Repository required and this will become the MUD master against which all changes are published.
The repository is opened offline and divided into projects - again by the MUD Administrator.

On the client side the users open the OBIEE administration tool and in the Tools --> Options --> Multiuser Tab, point the where the MUD master shared directory (\\server\muddir) is and describe the environment (eg DEV Machine)

When a developer wants to work they will start the OBIEE administration tool on their local installation. From the File menu they will then select Multiuser and then checkout. Select the project, as setup by the MUD Administrator, on which you want to work and open.

The local repository consists of only those details required to make the top level components in the repository work. The project may not have specified the physical layer to include and this will be added at checkout by the MUD process.

The developer, using their local install, will make changes and test outputs against their local answers queries for that project.

Once changes are made the operator has several options;
1. Discard changes - revert to how things were.
2. Refresh subset - pick up any changes in the master MUD repository that have occured since the checkout, that affect this project.
3. Compare to original - show what has changed.
4. Publish to Network - promote changes back to the MUD Master repository.

When you publish back to the MUD Master repository, a merge takes place and if conflicts arise, you need to resolve them by selecting what goes through for publication.

The MUD Master repository will then be uploaded through EM and changes promoted up through your environments.

Just bear in mind the following;

You may require named user plus licensing to be financially viable
MUD is more complex than online development, but makes sense when you know how it works
MUD Administrator divides main repository into projects; self-contained RPD subsets
Master repository is then published to a network share
Projects are then worked on independently, and then merged back into the master RPD
Uses the repository compare and merge features under the covers
Works best when each developer has a full OBIEE “Sandbox” environment to develop with and unit test their work License considerations

Integration with SVN follows.

Decimal difference to hours and minutes in OBIEE

Just has one of those issues of different interpretations of the same item by different people in different ways.

Actually, if I'm honest, I'm glad someone's out there manually checking all the hard work we put in here.

And what is this magical value that causes so much confusion - the difference between two time values displayed as a number of hours. Does the value 5.08 mean a. five hours and eight hundredths of an hour, or b. five hours and eight minutes.

Turns out it's we thought it was the first - five hours and eight hundredths of an hour and (some of) the users thought it was five hours and eight minutes.

In the interests of sanity it was decided that a disclaimer should be put against the report in question to clarify the issue and all metadata altered to definitively reflect that this was an decimal representation of hours and fractions thereof.

But the question arose - how difficult is it to show the value 5.08 as hours and minutes.

To derive 5.08 in the database we had subtracted the start date from the end date and multiplied the result by 24 giving us the difference in hours. Oracle does offer us the opportunity to do this calculation and have the result as an interval (day-to-second), but that gets stuck in the database hence the numeric difference in hours.

Thinking the solution through it is immediately apparent the first number is the hours regardless of what comes after the decimal point. For this we can use the FLOOR function which returns 5.

FLOOR(5.08) = 5

calculating minutes is working with what's left after we remove the hours.

5.08 - FLOOR(5.08) = 0.08

which in minutes is

60*0.08 = 4.8

Which is very different to the 8 minutes assumed by some users. We could carry on and work out the seconds implied by the 0.8 of a minute, but for the purpose of this discussion, rounding the result is sufficient. Putting the last piece together gives

ROUND(60*(5.08 - FLOOR(5.08)))

so sticking it all together to make it look pretty means we'll have to cast our numbers to CHAR

CAST(FLOOR(5.08) AS CHAR) || ' hours and ' || CAST(ROUND(60*(5.08 - FLOOR(5.08))) AS CHAR) || ' minutes'

Problem solved.