Friday, December 26, 2008

Nice Christmas Present

Christmas is over and I received a very nice Christmas present. I was doing a quick Google search to check on our website (after the recent outage) and found an interesting blog post on Dodeca. Jason Jones describes how they are using our Dodeca product to replace what they had previously done in Essbase with Excel VBA and instead create a cohesive work environment. Here is the link to the blog entry.

In the blog post, Jason describes one of their alternatives is to write 'a little home brew VBA action' to meet their needs. That statement reminded me of a conversation I had with one of Jason's coworkers a while back. He was showing me one of the reports they generated with Dodeca. Previous to Dodeca, this certain report was generated 'by hand' by someone working overtime *every* Saturday to have it ready for the executive team on Monday morning. Once the report was implemented in Dodeca, it is now generated in Dodeca in about 35 seconds. No more overtime!

"The report is pretty complex. It has 68 different retrieve ranges and pulls data from 3 different Essbase databases." stated the coworker.

"68 retrieve ranges???" I asked.

"Yes, and 60 of them are sorted after the retrieval." he replied. "That is nothing... We have one report with over 250 retrieve ranges."

What is the best feature of Dodeca used in this case? The report described here was created entirely with out-of-the-box functionality by the coworker who had just a few hours of on-line training in how to use Dodeca. You know, visioning the Dodeca product and leading the team that has built it is the real joy of being an entrepreneur.

The best part of the story is the value it provides to the company. Although the coworker could tell me about the mechanics of the report, he couldn't discuss the nature of the content due to the competitive advantage they enjoy. I am happy they allowed us to help.

Thursday, December 25, 2008

More to Life than Essbase

Occasionally, even I have to take a bit of time away from Essbase. Here are a couple of snapshots of one of the *other* things I do.

The first shot is a photo taken by a passenger in my Cessna 210 while I was undergoing recurrent instrument flight training (note the funny googles I am wearing to restrict vision outside the airplane). Don't worry, the instrument instructor in the right seat is watching for traffic and I am talking with ATC.



I fly the plane primarily for business but I also fly at least a couple of trips a year to visit family in Michigan. Here is a shot my Mom took when we were departing Michigan heading south for Alabama.



The funny thing about this picture is that she was planning to take the picture as we were passing directly in front of the terminal at the small airport near my hometown but she missed because we were going too fast!

In any case, Essbase is fun to work with but there are other fun things besides just Essbase. If you can, take some time to unwind and enjoy the holidays.

Wednesday, December 24, 2008

Essbase XML/A Update

Following up on an earlier post, I got SQL Server 2005 Service Pack 3 installed to test the fix to XML/A and SQL Server Reporting Services. I am happy to report that it works although I did have to recreate the report. I decided to take a quick look to see if I could figure out what was different and I discovered the query was slightly different. The original query was:

SELECT { [COGS], [Sales], [Margin] } ON COLUMNS, {CROSSJOIN( [Market].Levels(1).ALLMEMBERS, [Year].Levels(1).ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME, PARENT_UNIQUE_NAME, LEVEL_NUMBER ON ROWS FROM Sample.Basic CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING

The updated query is:

SELECT NON EMPTY { [COGS], [Sales], [Margin] } ON COLUMNS, NON EMPTY {CROSSJOIN( [Market].Levels(1).ALLMEMBERS, [Year].Levels(1).ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM Sample.Basic CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING

I highlighted the differences. Once I removed PARENT_UNIQUE_NAME from the dimension properties, the old report ran. I don't know where the NON EMPTY statement comes from except it may be a tweak added by the Microsoft developers to improve the XML/A for Essbase reports. Here is the finished report (from the preview screen in Visual Studio):

Tuesday, December 23, 2008

Hyperion Diagnostics Tool

I don't remember seeing this tool before; I think it is new to 11.1.1. It essentially tests the different Hyperion services and gives you a quick picture on the status of the services.

Based on the output that prints in the command window, it appears to go further than just checking to see if the services are running. Here is the command window during the generation process. Click on the graphic below to get a larger (more readable) version.



When the process is done checking all of the services, it summarizes it in a nicely formatted dashboard that gives you a very fast way to monitor your system.



As you can see in my screenshot, both my Essbase Studio server and my HAB.Net server are having problems. I shot this when I was working on getting Essbase Studio server running on Oracle and thus it was not running. I don't normally start up the HAB.Net server as I am running our Dodeca-Essbase service on my machine; it is a more up-to-date version of the HAB.Net server anyway.

Monday, December 22, 2008

Website outage (The Joys of Entrepreneurship)

For much of the last week, our website was down affecting both access to the information on our website and access to the Olap Underground tools we distribute on our site. Fortunately, we distribute our commercial products on a private website so access for our customers was not interrupted.

Our website is hosted by a web hosting company and they had some type of problem in the software they use to host our site. Unfortunately for us, they were not very helpful during this time and I got pretty @#$%^&* frustrated at times. In the end, they referred us to some of their former employees that we could contract to fix the software used to host the site and apply patches to prevent the same problem from recurring. It seems like we pay a good bit of money to them every year to make sure the site is up and running 24/7; they don't seem to be up to the task.

Unfortunately, one of the side effects of the problem was that Google listed our site as 'harmful to your computer' which prevented many people for being able to access it at all. About an hour ago, I received an email from the contractor telling me the site was back up and that they were working with Google to get the 'harmful site' status reversed.

Thank you to the dozen or so individuals who took the time over the past few days to send me email letting me know there was a problem with the site.

Update 12.23.2008: Our website is back online, has been tested by Google and found to be clean. Hopefully things can get back to normal now.

Friday, December 19, 2008

Tentative ODTUG Hyperion Schedule

A team of Hyperion practitioners have been toiling away for the past several months on putting together the ultimate Hyperion technical conference and finally, we are ready to share the agenda. A couple of notes:
  • This schedule may change but we don't expect significant changes.
  • Not all speakers have been notified or accepted their speaking slots. Further, several of the sessions are combinations of multiple sessions submitted and we have tried, when possible, to have the different people who submitted these sessions agree to team together to give the attendees the best possible content.
  • The Monday morning sessions highlighted in a bluish color are reserved vendor sessions. This timeslot is reserved conference-wide for presentations paid for by software companies, vendors, etc and is supposed to be the only time you hear a sales pitch. There is a session entitled "Throw Excel Under the Bus and Hop on the Dodeca Train" that was submitted by one of our customers; we decided our company would pay for the session and make it our 'vendor presentation'. After all, who to speak better about your product than your customers (and we may have a few customers participate in the session).

Click on the graphic below to get a larger (more readable) version.

Hopefully everyone who sees this schedule will not want to miss this conference. I hope to see you there!

Thursday, December 18, 2008

Using XML/A with Essbase

There has been a number of questions asked recently on the forums about the use of XML for Analysis (XML/A) with Essbase. This functionality has been supported in Essbase for quite some time. In fact, I was at the charter meeting of the XML for Analysis Advisory Council held on the Microsoft campus on 9/11 (yes, that 9/11). I was asked to be there for my expertise writing web services that accessed Essbase and they thought I had something to add. Since that time, I have only dabbled in XML/A as it will never have the same functionality as the Grid API that is contained in the Java API; that has been where I keep my focus. That being said, it doesn't hurt to know how to do it so I have done a small bit. This post will talk about how to connect and use XML/A to connect to Essbase using SQL Server Reporting Services ("SSRS").

XML/A underwent a major overhaul in the 9.3.1 timeframe to be more compliant with the Microsoft flavor of XML/A. This happened before the Oracle merger when Hyperion and Microsoft were getting a bit cozy and there was some cross-breeding that allowed Hyperion Reporting tools to connect to SQL Server Analysis Services ("SSAS") and allowed SSRS to connect to Essbase. For this to happen, you must have at least Essbase and APS 9.3.1 and SQL Server 2005 SP2 or higher; I am using Essbase/APS 11.1.1 and SQL Server 2005 SP2.

To begin, select Start, All Programs, Microsoft SQL Server 2005, SQL Server Business Intelligence Development Studio to start the development environment. The SSRS development environment is actually a version of Visual Studio 2005 so, for those of you new to this, welcome to the Microsoft .NET programming experience! After Microsoft Visual Studio starts, select File, New Project from the menu to display the New Project dialog. Select the Report Server Project template and give the new project a name and directory.

Press the OK button to continue and the new project will be created. In the Solution Explorer, select Shared Data Sources, right mouse click and select Add New Data Source.

In the Shared Data Source dialog, add a Name and select Hyperion Essbase from the Type dropdown.

Press the Edit button to configure the connection. Fill in the relevent properties on the dialog. If you have filled everything in properly, you should get a list of applications to choose from in the appropriate dropdown.

Next, select the Reports node in the Solution Explorer, right mouse click and click on Add New Report. This will launch the Report Wizard.


Hit the Next button to process past the wizard introduction step to the Select Data Source step. Select the Shared Data Source you just created.



Hit the Next button to proceed to the Query Builder.



I found that when I dragged any members not in the Measures dimension into the grid, Essbase threw an error "OLAP_error (1260046): Unknown Member PARENT_UNIQUE_NAME used in query". It seems to me to be related to outlines enabled with non-unique member names but I don't know for sure; perhaps there is an option somewhere that I missed. Note that the Query Builder does have an Auto Execute button that is turned on by default. I ended up turning off Auto Execute in my Query Builder.

In any case, make sure you select enough members/hierarchies/etc that you can have different dimensions to define the rows, columns and data (details) areas of a matrix report.

Hit OK to close the Query Builder and the MDX query string will be displayed in the wizard.

Hit the Next button and select the report type.



In my example, I added the Measures dimension members to the 'Details', the Years hierarchy to the Columns and the Market hierarchy to the Rows.


Hit the Finish button, give the report a Name and save it.

At this point, I was ready to test it and see data in the SSRS report. Guess what? The error I saw earlier came back to bite me. It seems the new provider changed enough in 9.3.1 and higher to cause a problem. Microsoft has fixed the issue in SQL Server 2005 SP3. The following line is found in the fixed buglist for SP3 at http://support.microsoft.com/kb/955706:

FIX: Error message when you use the new Essbase provider name: "OLAP error (1260046): Unknown Member PARENT_UNIQUE_NAME used in query"

OK, I will upgrade to the new service pack and see what happens.

Wednesday, December 17, 2008

Advanced Security Manager Update uploaded

We have uploaded a minor update to the Advanced Security Manager. This update contains the following changes:
  1. Support for exporting external authentication information (for versions 7x and higher). The new information is appended to the end of the User Access string in the export.
  2. Fixed a bug where command line functionality did not work with the version 6.x runtime client.

The new version is available on our website. Don't hesitate to contact us at support@appliedolap.com if and when you find issues in either of the OlapUnderground utilities that we support.

Tuesday, December 16, 2008

Kaleidoscope 2009 Planning

We have completed the tentative agenda for upcoming Kaleidoscope Conference. The Hyperion Special Interest Group board has put in quite a number of hours making sure the sessions will be highly valuable and help you justify your trip to beautiful Monterey, California next summer.

Though we aren't ready to release the complete agenda yet, let me tell you a little bit about it. It is separated into 4 separate tracks:

  • Essbase - Deep-dive from the experts

  • Applications - Focuse on Hyperion Planning and HFM

  • Hyperion and Oracle EPM - Reporting and other products

  • Hands-on Labs - Just Do It!


  • Just like last year, we will kick-off Sunday, June 21 with an Essbase Symposium featuring content from the Oracle Essbase development team.

    The conference will again have a community service day. Last year, the 'ODTUG Brigade' painted a school in New Orleans. I even got my family involved as my wife, kids and even my Mom, on her first trip to New Orleans, gave a day to help out. Here is a link to a video of last years ODTUG Brigade and, if you look closely, you will see me and my family along with the faces of some other people you see on the Hyperion forums including my friends Cameron Lackpour and Joe Aultman.

    Finally, while companies are cutting back in these tough economic times, we worked hard to make the Hyperion content of Kaleidoscope 2009 very deep to help you justify your attendance. Start planning now to join us in Monterey. Visit the Kaleidoscope 2009 website for the latest information.

    Sunday, December 14, 2008

    Oracle eDelivery Observations

    I got a comment on an installation post asking where to find Essbase Server and Client 11.1.1. I went to eDelivery and took a look and, sure enough, it looks like it appears the 11.1.1.0.0 Essbase builds are no longer available. The 11.1.1.1.0 builds are, however, available.

    I don't know exactly what the standard procedure is for Oracle for availability of older builds, but it seems somewhat consistent with what Oracle did by removing older versions of the software right after they acquired Hyperion (which used to leave many, many versions available for download.

    My advice is to download all of the available files in a specific version and archive it on your own local machines in case you need them in the future. We do exactly that at our company and have stored away all the versions (and their supporting files) that we support with our Dodeca product in the future.

    Note: Please do not send me email or comment on this blog asking if I can send you x.y.z version. Those emails/comments will absolutely and unequivocally ignored due to potential legal ramifications.

    Thursday, December 4, 2008

    Feedback on Installation Instructions

    A few weeks ago, I got an email from someone who used my instructions and were still having some issues. In the spirit of helping others who may be facing similar issues, I decided to post it with my responses/comments; my comments are in blue. Thank you Andre for your feedback!

    Email from Andre:

    A) I tried the installation on a Work Station WinXP Professional (OS German) SP2 4GB RAM.

    1. And this is a really important thing. NEVER install anything into the SYSTEM Schema of an Oracle DB. This is an absolute No-go. Before you start installing the EPM with a Oracle repository back end make sure that you have created an empty schema at the target DB, e.g. schema name (call it user as well) : EPMREP. This Schema must have some privileges needed to fulfill it's purpose. You can use the DDL-Script at the bottom of this text (You have to execute it as SYS (DBA)). Now you can use this schema as container for your EPM repository. To do this in the config tool “Shared Services and Registry database Configuration” enter for “Username” the value “EPMREP” and your Password (in my script this is the same as the User “EPMREP”).

    I absolutely agree. I think what I did when I worked with our Dodeca product on Oracle was to create an Oracle tablespace, created a user and assigned the user to the tablespace. You could also use SQL Server and just create a database to hold the tables.

    2. The Oracle DB must have some special setting as noticed in the install hints . It is important to follow these rules. If you break these rules, shared services are unable to use the repository DB and you will get very strange (and misleading) error messages. If you are unexperienced with Oracle DB you have less chances to find out what is wrong. If you need help from a DBA (there are a lot of reasons) don't hesitate; to be sure that you will get what you need give him/her a paper copy from the appropriate page of the install manual.

    Definitely work with an experienced DBA on your production system if you don't have relational database skills. I remember back in the early days of Essbase, often the Essbase admin had absolutely no idea what SQL was all about; it seems to me that is becoming less the case (but remember, when I started doing Essbase, nobody had ever heard of 'the web' ).


    3. Run the installation and configuration of shared services in one round. When you are done with that EXIT (close) the install tool. Reboot the system. This is important, since it seems there are internal problems that causes problems in the following install steps. Start the installer again and now you can install all the other components in another single round.

    I didn't experience this issue in any of the installs I did prepping for the blog entry nor did my assistant, who has done probably about a dozen 11.1.1 installs now, report this problem.


    4. It is absolutely likely that you will fail with your first attempt of installation. Make a backup (image) of your system before you start. In my attempts I found that a lot of things remain on the system even if you use the uninstall. The system path for example becomes longer and longer because you will find the settings of every installation one after the other. If you do not have a backup make sure that you delete the Hyperion directory from you system, clean the registry (I used the cleaner tool “ccsetup213.exe”), clean all system variables relating to Hyperion and reboot the system before you start another try.

    If possible, practice the install before you attempt it on a production system. VMWare is your friend here! I absolutely agree on doing the final cleanup steps.


    5. In none of my attempts I have set the option “Deploy Essbase in standalone mode”, because I wanted to have the authentication with ssh. However so far it does not work, although the config tool stated to me that all tasks (in particular Register with Shared Services) have been successful. At this point I'm still investigating.

    I don't think I ran an install where security was automatically externalized. I will ask my assistant if he ran any in that mode.


    6. I have no idea why, but none of the known sample apps have been installed during the installation round. I found no option for that, so I assume it should have been installed by default as it was in all older versions of essbase. Only one App “$_DM...” is there but not visible in the AAS. Very strange.

    I agree that is strange but it sounds familiar; I think I saw this behavior but, unfortunately, don't remember what I did to fix it (if anything).


    7. Be absolutely sure that your account has FULL admin privileges on you system. I run into problems because my account had only “derived” admin privileges from a domain. It seems to be best if you logon to your system as a local admin user. Before I realized that, I was wondering why after a successful uninstall the Hyperion directory was still filled with a lot of sub-directories that should have been deleted. Please note that you get no error or warning in such a case.

    Agreed!

    B) I tried the installation on a Win2003 Server (OS English) 4GB RAM.

    B1) First try:

    1. The installation failed. Non of the components could be registered with ssh. I have tested the install process without a reboot after the installation and configuration of ssh components – although she installer and the check stated that everything was OK. So it seems that an reboot is essential after the install of ssh is done.

    2. I was connected NOT as the (local) user “administrator”. This username is forbidden in our environment. Renaming the “administrator” to a different name is the first task after setting up a machine.

    3. Then sample apps has been installed. This has been (and still is) an issue on my PC.

    4. Uninstall failed with the same issue as in 7. above (directories and files still there).

    B2) Second try.

    1. I have NOT deleted anything manually (like I did it after the latest uninstalls on my PC), but rather started the install tool like I did for the first try.

    2. During the install of the ssh I was not asked (like I woild expected it) to specify the repository DB. I started the ssh console and the site opened correctly. At this point nothing else to do; but to reboot the system …

    3. Enter the install tool and installed the rest of the foundation components (these that are not installed in the same round with ssh), Essbase, Web Analysis, HPS.

    4. Stopped all running EPM services and Reboot.

    5. Want to start the config utility, however it is not longer in the program’s directories path.

    6. Well, this is the point where I stop, since this indicates (at least to me) that the EPM system is no longer in a consistent condition. So it makes no more sense to make any further effort in trying to get the system work properly.

    7. I will restore the backup image on next Monday and try it again.

    So fare it would like to conclude my experiences in the following:

    1. Maybe there is at least one way (or even more ways) to install the above mentioned components properly, however I did not found one.

    2. There seems to be a lot of critical / important conditions to be considered, but sadly enough there are no hints relating to these things.

    3. It is very easy to bring the system into an inconsistent (or at least not understandingly) condition.

    4. Most queerly it seem to be a mission critical issue if the installer is not connected as user “administrator” at the local machine. In particular this seems to be critical for the uninstall procedure that is not working properly if you install and uninstall as a different user.

    5. A reboot between the single steps of installation and/or configuration seem to be needed, but is not forced (or at least advised) by the installer/config tool.

    6. If you don’t have a clean before image you will likely end up in a mess. So never make a try without such a fallback solution.

    Well, this is not my first installation with Hyperion.
    I’m working with Essbase / System 9 for more then 7 years now.
    However, it has never been that frustrating and messy.
    Best Regards!
    Andre

    DDL-Script for creating the EPM-Repository-Schema in a Oracle 11gR1 DB:
    Connect as SYS (DBA) and run this script:
    CREATE USER EPMREP
    IDENTIFIED BY EPMREP
    DEFAULT TABLESPACE USERS
    TEMPORARY TABLESPACE TEMP
    PROFILE DEFAULT
    ACCOUNT UNLOCK;
    -- 1 Role for EPMREP
    GRANT CONNECT TO EPMREP;
    ALTER USER EPMREP DEFAULT ROLE ALL;
    -- 12 System Privileges for EPMREP
    GRANT CREATE SYNONYM TO EPMREP;
    GRANT ALTER SESSION TO EPMREP;
    GRANT CREATE VIEW TO EPMREP;
    GRANT CREATE SESSION TO EPMREP;
    GRANT CREATE SEQUENCE TO EPMREP;
    GRANT CREATE PROCEDURE TO EPMREP;
    GRANT ALTER USER TO EPMREP;
    GRANT CREATE TRIGGER TO EPMREP;
    GRANT CREATE TABLE TO EPMREP;
    GRANT SELECT ANY DICTIONARY TO EPMREP;
    GRANT CREATE TYPE TO EPMREP;
    GRANT ALTER SYSTEM TO EPMREP;
    -- should be revoked after Installation of EPM
    -- REVOKE ALTER SYSTEM TO EPMREP;
    -- 1 Tablespace Quota for EPMREP
    ALTER USER EPMREP QUOTA UNLIMITED ON USERS;

    To sum it up, despite improvements in the installation process with 11.1.1, it still isn't a process for the faint-of-heart.

    Tim

    Wednesday, December 3, 2008

    Pushing Essbase Beyond the Limits of Excel webcast

    I am doing webcast tomorrow with fellow Oracle ACE Glenn Schwartzberg. The webcast, "Pushing Essbase Beyond the Limits of Excel", is part of the interRel Consulting webcast series. You can signup for the webcast at the interRel website.

    In the webcast, Glenn will show you how to setup a budget template in Excel including the necessary VBA code, dropdown controls, etc to login the user, allow them to make selections, send data to the database and run a specified calc. I will then build and deploy the same functionality in Dodeca from scratch in about 5 minutes. BTW, Glenn was impressed when I did the same in our planning for the webcast.

    I hope you can join us tomorrow!