Is Oracle AWM 11.1.0.7.0A Better?

So, I'm about to present at a workshop tomorrow on Oracle 11g's Interval Partitioning and on the OLAP option and in preparation for the event I have had to create a demo. A while back I patched our 11.1.0.6.0 relational database to patch 8 but although both patch 10 and 11.1.0.7.0 have since been released, I decided not to risk upsetting things too much by installing those and have thus gone with the status quo. (Note that we have lots of Oracle software on that machine for demos and breaking things at any time is just not on!)

Incidentally, I just have to ask, does anyone actually know what each number in Oracle's version numbers stand for? I searched a while back but to no avail. But I digress...

Searching around on 11g versions and patches I came across an article that said that in order to patch the OLAP option you need to install either Patch 'A' or Patch 'B' but in order to patch the relational side of the engine you use the Patches 6, 8, 10 etc. Huh? When I installed patch 8 it had to back out patch 'A' (which is what the person who posted on the forum also found) so does that mean that the OLAP patches are mutually exclusive from the relational patches? That's a question I have yet to answer 'cos maybe you are meant to re-apply Patch 'A' after patch 8 etc. but that's a subject for a later post. (Especially since 11.1.0.7.0 has been released which appears to be for both relational and OLAP with patch #6890831. See the OLAP Certification page.)

Anyhoo, my big grumble with the Oracle Analytic Workspace Manager is that it seems to contain more bugs than an anthill. Seriously, does anyone actually test this thing? Within 2 minutes of using it I can get it to break. Lol, now I appreciate that my ineptitude is clearly a culprit in this issue in that if I did everything correctly it wouldn't break. However, exceptions are just that, exceptions and they need to be catered for.

Documentation seems to indicate that the preferred method of creating analytic workspaces etc. is to use Oracle Warehouse Builder and that the Analytic Workspace Manager is for "end users" who don't have access to the ETL/ELT process. It can also be used where OWB is not the ETL/ELT tool being employed.

Really? Whenever an exception is thrown I seem to get some Java exception ramblings about not being able to create a cursor or transaction or something equally inane. I'm sorry. It's coding like this that gives Java a bad name! Issues such as not being able to switch between a "star-schema" dimension and "snowflake" dimension after it has been built. Sure, you click on the drop down box and select it but sometimes the relevant member is selected but you don't get the correct representation in the mapping pane (i.e. visible/invisible "parent" member) or it just won't select.

Other irritations include:

• After defining a cube you can click on the cube name in the schema tree and even though the dimensions are greyed out, you can double click an available dimension and it will appear to be added to the cube's dimension list. It hasn't really, click off the cube and come back and the model is back as expected.
• Try creating a dimension over the "Sales Territory" dimension as available in the Microsoft AdventureWorks database and it will build. You can even browse it. However, join that dimension to a cube built off of the Internet Sales fact table and whoa... nasty exception.
• Why does the above nasty exception terminate my connection and cause random stuff to happen?
• How come building cubes off a database using the WE8MSWIN1252 characterset causes ORA-12704 characterset mismatch errors seemingly at random? Sometimes when I merely process something but always when trying to create cube organized materialized views. Sure I should be using UTF8 but the default for the "warehouse" database created by dbca is to use WE8MSWIN1252. What gives with that?
  
• ...

It is probably worth pointing out that the version of AWM that I was using was 11.1.0.7.0A and apparently that is expected to work against an 11.1.0.6.0 database. Maybe I should log these as bugs for Oracle to fix, I get that. My complaint though, is how did some of the issues I experience (maybe not listed here) even get through the door?

Does anyone on the development team actually use the product in a way that is not based on contrived samples? We all know that clinical test cases will work but what about the real world?

After all that, is the new version of AWM better? Yes. The fact that you can now create dimension levels at the time when you create a dimension (which I always thought was odd that you couldn't) is accepted with open arms. Despite all the ramblings above, it does seem a little more stable though that really isn't saying much. The default way of mapping columns to OLAP structures has changed from the graphical mapping view to the table mapping view which I think is for the better.

Bottom line, if you haven't switched yet, switch now. It's better for sure but don't expect an "Analysis Services development environment (i.e. BIDS)" type experience or you'll be sorely disappointed!

Hyperion EPM on Windows Server 2008

For an upcoming workshop we are running, covering the Oracle BI offering, I had to upgrade the relevant Oracle software to later versions. This included upgrading Oracle 11g to 11.1.0.6 patch 8 (11.1.0.7 not available for Windows at the time), upgrading Hyperion 9.3 to EPM Fusion edition and upgrading OBIEE plus. As an aside, the upgrade to Oracle 11g wasn't exactly seamless due to some Hyperion 9.3 service mainting a reference to the Oracle client dlls despite the fact that I had "uninstalled" Hyperion prior to the 11g patching. However, the use of ProcessExplorer soon highlighted the offending process and I was able to complete the patching in due course.

Installing Oracle/Hyperion Enterprise Performance Management Fusion Edition on Windows Server 2008 however, seemed to pose some interesting challenges. I'm not sure if this is as a result of running the Windows Server 2008 as the OS, the fact that the OS is actually running in Hyper-V, the fact that I uninstalled Hyperion 9.3 first and then installed EPM or something else but the net result was that it took me almost 2 days to solve an issue with OpenLDAP.

Attempts to start the Hyperion OpenLDAP service were unsuccessful and thus none of the other services were able to register with Shared Services. (i.e. The Shared Services service requires the OpenLDAP service to be up and running before it can start.) The failure to start the service provided little in the way of messages that would help debug the issue but consulting the EPM installation documentation (and Google) showed that you can start the OpenLDAP service from the command line using the "-d" switch in order to get some logging to sdtout. A switch of "-d 1" provides copious volumes of logging information and "-d -1" even more so. ;-)

Although this did produce some logging, in the end it was of little benefit as I got mixed results. Those mixed results were a combination of getting the OpenLDAP service to start from the command line (after tweaking some command line arguments to the "slapd" exe by adding a reference to the config file) to the service no longer starting thereafter. When the service would no longer start, I got the dreaded "slapd starting" message in the console whereupon the service would do little else. According to Google searches, the solution appeared to be to "download a later version of OpenLDAP" which clearly wasn't an option as I needed to use the Oracle implementation of OpenLDAP.

At this point I figured that a re-install was probably on the cards so I uninstalled EPM, deleted the directory into which it had been installed and then reinstalled the EPM software. Guess what... The OpenLDAP service started! oooOOOOOooooo. So a quick config of the services and... D'OH! still no joy! None of the services were able to register with Shared Services. Grumble, grumble, grumble. What now?

Even more debugging and looking at the Shared Service log files showed messages such as "Native Directory is not reachable. Attempting to re-connect" with references to the server's hostname and a port number of 28089. This seemed to indicate that services were attempting to connect to OpenLDAP on port 28089 and couldn't get through. Further investigations showed that OpenLDAP does not use that port number by default, so it needs to be set at startup.

Now that's where the fun and games started. Rather than go through all the possible places where they can be set depending on which article you read (which I unfortunately tried), I'll just tell you the right place. It would appear (and I'm guessing here) that the installation of EPM on Windows Server 2008 does not install some much needed registry entries. A colleague of mine installed EPM on Windows XP and it all went well. A quick check of the registry on that machine confirmed that this is what was missing. So...

What you need to do, is to ensure that the following registry entries (and structure) exists in the registry:

[HKEY_LOCAL_MACHINE\SOFTWARE\OpenLDAP\Parameters]

with keys:

ConfigFile PathToOpenLDAPDirectory\slapd.conf
DebugLevel 0
Urls ldap://:28089

Note that the "ConfigFile" and "Urls" parameters are String settings whereas "DebugLevel" is a DWORD32

And that's it. A restart of the OpenLDAP service and Shared Services service meant that I could register all the other Hyperion services. The "Urls" parameter is the main setting, as that dictates that OpenLDAP is to listen on port 28089 which is what is required by the rest of the Hyperion services.

It may also be worth noting that a quick glance at the default slapd.conf file supplied by Oracle lists that a "pid" file and "arg" file be created in the ".../var/run" directory. Clearly a *nix setting but rather amusingly there is no "run" directory under the "var" directory in the Windows structure. If you create it, those files will be created. Also, it may well be worth you adding in an entry for the creation of a logfile. I added an entry in the "slapd.conf" file, immediately below the entries for the "run" and "arg" files to do this. The key looks like:

logfile PathToOpenLDAPDirectory\logs\slapd.log

To get logging to appear in this file you will need to change the value in the registry entry "DebugLevel" created earlier to a value other than zero. The number 4 is probably a good value to go for or 1 if you wish to debug issues.

And there you have it. I hope that this helps others to have more time to do things other than the configuration of EPM.

Good luck.

The ProClarity API - Programmatic changes to ProClarity briefing books

We have a client who is using Analysis Services 2000 and ProClarity Analytics Server 6.3 in order to provide balance sheet and financial line information to their business. The system that I designed had to be able to aggregate financial data up a customer hierarchy but be able to aggregate the data into parents as they stood at the time the data was loaded and against the current parent. (i.e. A slowly changing dimension.) However, this dimension consists of over 1.5 million members and there are effectively two versions of this in the database.

Now this on its own is fine-ish, in that with the 3GB switch enabled and some tweaks to the cleaner thread interval the AS solution is stable and performs reasonably well. However, the original requirement did not specify security on these dimensions. As you can guess, there is now a requirement to place security on these dimensions, partially to limit access to the data but more so to provide a way for a given user's reports to open, defaulted to the customers for which they are responsible.

Now trying to add security using a "security cube" isn't really conducive to performance and adding 440 roles to AS, each with a different security context, isn't really good for maintenance or memory. (i.e. Think replica dimensions.) So, what else?

Well, as an approach, I wondered if it is possible to take a template ProClarity briefing book located in a PAS Store, load it and customise it for a given user and write it back to the PAS store. From previous experiences I know that just updating the XML in the PAS database for a given briefing book and pages causes problems down the line, to the point that they may no longer work. So how else? Can you make programmatic changes? I know that there is an API for ProClarity but the documentation is all for VB etc. and not .Net.

In the end I decided to give it a try and started to write an application that would retrieve a result set from a database specifying the template book and pages etc. that are to be customised and the customisations to be made. It also specifies the PAS store into which the book should be ultimately published and security assignments for the new book.

After contacting Microsoft on TechNet for some hints and tips (whose responses are pretty quick and impressive actually) I was on my way. See Hints and Tips for that thread.

The essentials to know, when using C#, are effectively as follows:

1. In order to have a briefing book loaded within a ProClarity application in memory, use the KProClarityClass.StartupBook method.
2. Once you have a KProClarityClass object, use the following objects, properties or methods as shown below...
   
   Application (Object)
   |
   CurrentView (Property)
   |
   PageCtrl (Object)
   |
   KPCommand (Object)
   |
   GetAxisMemberSet (Method)
   |
   KMemberSet
   
   As an example, use the KProClarityClass's Application property to retrieve a reference to the Application object. Now use the Application object's CurrentView property to retrieve a reference to the the ICurrentView interface etc. etc.
   
3. If you make any changes to a KMemberSet, you must use that object's associated KCommand object's execute method in order update all associated entities within the ProClarity object model.
4. If you copy elements etc. be sure to update the copied element's ID otherwise you will get errors when trying to publish the briefing book. (i.e. You will have two elements with the same ID which understandably is not allowed.) To do this, set the ID for the given element to: "{00000000-0000-0000-0000-000000000000}"
5. In order to commit changes you have made to a given briefing book page, you have to copy the "page" within the book, using the CurrentView's AddPageToBook method and then delete the original page. Please don't shout at me, I know, that sucks but that's just how it is okay?
   

In the end I changed my application to run as a custom SSIS task and to be multi-threaded using a blocking-queue in order to hand out template briefing books for updates to consumer threads etc. That all seemed to work although I did experience exceptions when running the task in debug mode. As can be seen by the following thread with Microsoft here, this was related to my code not being aware of the COM aspects of the ProClarity API.

All in all it was quite fun to develop, creating mini-caches for PStore connections etc. and to implement a solution that runs within SSIS. I added in the ability to set properties such as the number of threads to use and the ability to set the table and column names that store the relevant updates to be made.

Using simple test cases it appears to work quite nicely. Reports remain static, such that reports with both grids, totals and graphs etc. retain that layout when the member selections are changed. The task allows the ability to either update members by axis or to simply replace the MDX for the report. (Actually you could replace the MDX for a report and then customise the member selection immediately thereafter if you so wished but why?)

Before it's a finished beta v0.0.0.0.0.0.1 "product" (Oracle style versioning there) the task needs to accommodate pages within nested folders (i.e. currently only handles pages at the root or one level below) and to complete the security aspect. I also need to normalise the table structures that store the updates to be made against the template briefing book etc.

Anyhoo, if anyone out there is interested in using this please add a comment to this blog and I'll see if I can add it to our company's website for download. That naturally assumes anyone actually reads my blog...