BI & Warehousing

FSG Reporting and BIP

Tim Dexter - Fri, 2013-12-20 11:30

This is a great overview of the Financial Statement Generator (FSG) engine from GL in EBS and how Publisher fits into the picture.Thanks to Helle Hellings on the Financials PM team.


Categories: BI & Warehousing

Meet the Oracle ACE Directors Panel - January 9 - Seattle

Tim Tow - Thu, 2013-12-19 08:27

I will be in Seattle on Thursday, January 9th for the Meet the Oracle ACE Directors Panel.  It is at the Sheraton Seattle from 4 - 6 pm and will feature several other ACE Directors including Martin D'Souza, Kellyn Pot'Vin, Tim Gorman, and my longtime friend and collaborator, Cameron Lackpour.  

Come see and the panel and stay for the Happy Hour; the beer will be on me!


Categories: BI & Warehousing

My Friend, Mike Riley, Has Cancer

Look Smarter Than You Are - Mon, 2013-12-16 08:18
I found out this summer that one of my best friends - one of the entire Hyperion community's best friends - has cancer. This is his story.

But first, a mea culpa:
In 2008, I Was An IdiotBack in early 2008, I wrote a blog entry comparing Collaborate, Kaleidoscope, and OpenWorld.  In this entry, I said that Collaborate was the obvious successor to the Hyperion Solutions conference and I wasn't terribly nice to Kaleidoscope.  Here's me answering which of the three conferences I think the Hyperion community should attend (I dare you to hold in the laughter):
Now which one would I attend if I could only go to one?Collaborate. Without reservation. If I'm going to a conference, it's primarily to learn. As such, content is key.I actually got asked a very similar question on Network 54's Essbase discussion board just yesterday (apparently, it's a popular question these days). To parrot what I said there, OpenWorld was very, very marketing-oriented. 80% of the fewer than 100 presentations in the Hyperion track were delivered by Oracle (in some cases, with clients/partners as co-speakers). COLLABORATE is supposed to have 100-150 presentations with 100+ of those delivered by clients and partners.In the interest of full-disclosure, my company, interRel, is paying to be a 4-star partner of COLLABORATE. Why? Because we're hoping that COLLABORATE becomes the successor to the Solutions conference. Solutions was a great opportunity to learn (partying was always secondary) and I refuse to believe it's dead with nothing to take it's mantle. We're investing a great deal of money with the assumption that something has to take the place of Hyperion Solutions conference, and it certainly isn't OpenWorld.Is OpenWorld completely bad? Absolutely not. In addition to the great bribes, it's a much larger conference than COLLABORATE or ODTUG's Kaleidoscope, so if your thing is networking, by all means, go to OpenWorld. OpenWorld is the best place to get the official Oracle party line on upcoming releases and what not. OpenWorld is also the place to hear better keynotes (well, at least by More Famous People like Larry Ellison, himself). OpenWorld has better parties too. OpenWorld is also in San Francisco which is just a generally cooler town. In short, OpenWorld was very well organized, but since it's being put on by Oracle, it's about them getting out their message to their existing and prospective client base.So why aren't I recommending Kaleidoscope (since I haven't been to that either)? Size, mostly. Their entire conference will have around 100 presentations, so their Hyperion track will most likely be fewer than 10 presentations. I've been to regional Hyperion User Group meetings that have more than that (well, the one interRel hosted in August of 2007 had 9, but close enough). While Kaleidoscope may one day grow their Hyperion track, it's going to be a long time until they equal the 100-150 presentations that COLLABORATE is supposed to have on Hyperion alone.If you're only going to one Hyperion-oriented conference this year, register for COLLABORATE. If you've got money in the budget for two conferences, also go to OpenWorld. If you're a developer that finds both COLLABORATE and OpenWorld to be too much high-level fluff, then go to Kaleidoscope.


So, ya, that entry may live in infamy.  [Editor's Note: Find out a way to delete prior blog posts without anyone noticing.]  Notice that of the three conferences, I recommended Kaleidoscope last and dared to say that it would take them a long time until they had 100-150 sessions like Collaborate.  Interestingly, Collaborate peaked that year at 84 Hyperion sessions, and Kaleidoscope is well over 150 Business Analytics sessions, but I'm getting ahead of myself.


In 2008, Mike Riley Luckily Wasn't An Idiot
I had never met Mike Riley, but he commented directly on my blog.  He was gracious even though I was slamming his tiny little conference in New Orleans:
Hyperion users are blessed with many training opportunities. I agree with Edward, the primary reason for going to a conference is to learn, but I disagree that Collaborate is the best place to do that. ODTUG Kaleidoscope, Collaborate, and OpenWorld all have unique offerings. 

It’s true that ODTUG is a smaller conference, however that is by choice. At every ODTUG conference, the majority of the content is by a user, not by Oracle or even another vendor. And even though Collaborate might seem like the better buy because of its scale, for developers and true technologists ODTUG offers a much more targeted and efficient conference experience. Relevant tracks in your experience level are typically consecutive, rather than side-by-side so you don’t miss sessions you want to attend. The networking is also one of the most valuable pieces. The people that come to ODTUG are the doers, so everyone you meet will be a valuable contact in the future.

It’s true, COLLABORATE will have many presentations with a number of those delivered by clients and partners, but what difference does that make? You can’t attend all of them. ODTUG’s Kaleidoscope will have 17 Hyperion sessions that are all technical. 

In the interest of full disclosure, I have been a member of ODTUG for eight years and this is my second year as a board member. What attracted me to ODTUG from the start was the quality of the content delivered, and the networking opportunities. This remains true today.

I won’t censor or disparage any of the other conferences. We are lucky to have so many choices available to us. My personal choice and my highest recommendation goes to Kaleidoscope for all the reasons I mentioned above (and I have attended all three of the above mentioned conferences).

One last thing; New Orleans holds its own against San Francisco or Denver. All of the cities are wonderful, but when it comes to food, fun, and great entertainment there’s nothing like the Big Easy. 
Mike was only in his second year as a board member of ODTUG, but he was willing to put himself out there, so I wrote him an e-mail back.  In that e-mail, dated February 10, 2008, I said that for Kaleidoscope to become a conference that Hyperion users would love, it would require a few key components: keynote(s) by headliner(s), panels of experts, high-quality presentations, a narrow focus that wasn't all things to all people, and a critical mass of attendees.

At the end of the e-mail, I said "If Kaleidoscope becomes that, I'll shout it from the rooftops.  I want to help Kaleidoscope be successful, and I'm willing to invest the time and effort to help out.  Regarding your question below, I would be more than happy to work with Mark [Rittman] and Kent [Graziano] to come up with a workable concept and I think I'm safe in saying that Tim [Tow] would be happy to contribute as well.  For that matter, if you're looking for two people to head up your Hyperion track (and enact some of the suggestions above), Tim and I would be willing (again, I'm speaking on Tim's behalf, but he's one of the most helpful people on planet Hyperion)."


K(aleido)scope
Kaleidoscope 2008 ended up being the best Hyperion conference I ever attended (at the time).  It was a mix of Hyperion Solutions, Arbor Dimensions, and Hyperion Top Gun.  With only 4 months prep time, we had 175 attendees in what then was only an Essbase track.  Though it was only one conference room there in New Orleans, the attendees sat in their seats for most of a week and learned more than many of us had learned in years.

After the conference, Mike and the ODTUG board offered Tim Tow a spot on the ODTUG board (a spot to which he was later elected by the community) to represent the interests of Hyperion.  I founded the ODTUG Hyperion SIG along with several attendees from that Kaleidoscope 2008. I eventually became Hyperion Content Chair for Kaleidoscope and passed my Hyperion SIG presidency on to the awesome Gary Crisci.  In 2010, Mike talked me into being Conference Chair for Kaleidoscope (which I promptly renamed Kscope since I never could handle how "kaleidoscope" violated the whole "i before e" rule).  Or maybe I talked him into it.  Either way, I was Conference Chair for Kscope11 and Kscope12.

During those years, Mike worked closely with the Kscope conference committee in his role as President of ODTUG.  Mike rather good-naturedly ("good-natured" is, I expect, the most commonly used phrase to describe Mike) put up with whatever crazy thing I wanted him to do. In 2011, he was featured during the general session in several reality show parodies (including his final, climactic race with John King to see who got to pick the location for Kscope12).  I decided to up the ante in 2012 by making the entire general session about him in a "Mike Riley, This Is Your Life" hour and we found ourselves laughing not at Mike, but near him.  It included Mike having to dance with the Village Persons (a Village People tribute band) and concluded with Mike stepping down as President of ODTUG...

... to focus his ODTUG time on being the new Conference Chair for Kscope.  Kscope13 returned to New Orleans and Mike did a fabulous job with what I consider to be Hyperion's 5 year anniversary with Kscope.  Mike was preparing Kscope14 when I got a phone call from him.  I expected him to talk over Kscope, ODTUG, or just to say hi, but I'll never forget when Mike told me he had stage 3 rectal cancer.  My father died in 2002 of colorectal cancer, and the thought that one of my best friends was going to face this was terrifying... and I wasn't the one with cancer.

I feel that the Hyperion community was saved by Mike (what would have happened if we had all just given up after Collaborate 2008 was a major letdown?) and now it's time for us to do our part.  Whether you've attended Kscope in the past or just been envious of those of us who have, you know that it's the one place per year that you can meet and learn from some of the greatest minds in the industry.


Mike Helped Us, Let's Help Him
Kscope is now the best conference for Oracle Business Analytics (EPM and BI) in the world, and Mike, I'm shouting it from every rooftop I can find (although I wish when I climbed up there people would stop yelling "Jump!  You have nothing else to live for!").  I tell everyone I know how much I love Kscope, and on behalf of all the help you've given the Hyperion community over the last 5 years, Mike, it's now time for us to help you.

After many weeks of chemo, Mike goes into surgery tomorrow to hopefully have the tumor removed.  Then he has many more weeks of chemo after that. He's a fighter, but getting rid of cancer is expensive, so we've set up a Go Fund Me campaign to help offset his medical bills.  If you love Kscope, there is no one on Earth more responsible for its current state than Mike Riley.  If you love ODTUG, no one has more fundamentally changed the organization in the last millennium than Mike Riley.  If you love Hyperion, no one has done more to save the community than Mike Riley.  

And if after reading this entry, you love Mike for all he's done, go to http://bit.ly/HelpMike and donate generously, because we want Mike to be there at the opening of Kscope14 in Seattle on June 22.  Please share this entry, and even if you can't donate, send Mike an e-mail at mriley@odtug.com letting him know you appreciate everything he's done.
Categories: BI & Warehousing

Smart View Internals: Exploring the Plumbing of Smart View

Tim Tow - Fri, 2013-12-13 17:46
Ever wonder how Smart View stores the information it needs inside an Excel file?  I thought I would take a look to see what I could learn.  First, I created this simple multi-grid retrieve in Smart View.



Note that the file was saved in the Excel 2007 and higher format (with an .xlsx file extension).  Did you know that the xlsx format is really just a specialized zip file?  Seriously.  It is a zip file containing various files that are primarily in xml format.  I saved the workbook, added the .zip extension to the filename, and opened it in WinRar.  Here is what I found.

I opened the xl folder to find a series of files and folders.


Next, I opened the worksheets folder to see what was in there.  Of course, it is a directory of xml files containing the contents of the worksheets.


My Essbase retrieves were on the sheet named Sheet1, so let’s take a look at what is in the sheet1.xml file.   The xml is quite large, so I can’t show all of it here, but needless to say, there is a bunch of information in the file.  The cell contents are only one of the things in the file.  Here is an excerpt that shows the contents of row 5 of the spreadsheet.



This is interesting as it shows the numbers but not the member name.  What is the deal with that?  I noticed there is an attribute, ‘t’, on that node.  I am guessing that the attribute t=”s” means the cell type is a string.  I had noticed that in one of the zip file screenshots, there was a file named sharedStrings.xml.  Hmm...  I took a look at that file and guess what I found?




That’s right!  The 5th item, assuming you start counting at zero like all good programmers do, is Profit.   That number corresponds perfectly with the value specified in the xml for cell B5, which was five (circled in blue in the xml file above).   OK, so when are we going to get to Smart View stuff?  The answer is pretty quick.  I continued looking at sheet1.xml and found these nodes near the bottom.

Hmm, custom properties that contain the name Hyperion?  Bingo!  There were a number of custom property files in the xml file.  Let’s focus on those.

Custom property #1 is identified by the name CellIDs.  The corresponding file, customProperty1.bin, contained only the empty xml node <root />.  Apparently there aren’t any CellIDs in this workbook.

Custom property #2 is identified by the name ConnName.  The file customProperty2.bin contains the string ‘Sample Basic’ which is the name of my connection.

Custom property #3 is named ConnPOV but it appears to contain the connection details in xml format.  Here is an excerpt of the xml.


Custom property #4 is named HyperionPOVXML and the corresponding file contains xml which lines up with the page fields I have in my worksheet.



What is interesting about the POV xml is that I have two different retrieves that both have working POV selectors which are both implemented as list-type data validations in Excel.  I don’t know what happens internally if I save different values for the POV.

Custom property #5 is labeled HyperionXML.  It appears to contain the information about the Essbase retrieval, but it doesn't appear to be the actual retrieval xml because it doesn't contain the numeric data.  My guess is that this xml is used to track what is on the worksheet from a Hyperion standpoint.



There is a lot of information in this simple xml stream, but the most interesting information is contained in the slice element.  Below is a close-up of contents in the slice.



The slice covers 6 rows and 7 columns for a total of 42 cells.  It is interesting that the Smart View team chose to serialize their XML in this manner for a couple of reasons.  First, the pipe delimited format means that every cell must be represented regardless of whether it has a value or not.  This really isn’t too much of a problem unless your spreadsheet range is pretty sparse.  The second thing about this format is that the xml itself is easy and fast to parse, but the resulting strings need to be parsed again to be usable.  For example, the vals node will get split into an array containing 42 elements.  The code must then loop the 42 elements and process them individually.  The other nodes, such as the status, contain other pieces of information about the grid.  The status codes appear to be cell attributes returned by Essbase; these attributes are used to apply formatting to cells in the same way the Excel add-in UseStyles would apply formatting.  There are a couple of things to take away:

  1. In addition to the data on the worksheet itself, there is potentially *a lot* of information stored under the covers in a Smart View file.
  2. String parsing is a computation-intensive operation and can hurt performance.  Multiply that workload by 8 because, depending on the operation and perhaps the provider, all 8 xml nodes above may need to be parsed.

In addition, the number of rows and columns shown in the slice may be important when you are looking at performance.  Smart View must look at the worksheet to determine the size of the range to read in order to send it to Essbase.  In the case of a non-multi-grid retrieve, the range may not be known and, as a result, the grid may be sized based on the UsedRange of the worksheet.  In our work with Dodeca, we have found that workbooks converted from the older xls format to the newer xlsx format, which support a larger number of cells, may have the UsedRange flagged internally to be 65,536 rows by 256 columns.  One culprit appears to be formatting applied to the sheet in a haphazard fashion.  In Dodeca, this resulted in a minor issue which resulted in a larger memory allocation on the server.   Based on the format of the Smart View xml, as compared to the more efficient design of the Dodeca xml format, if this were to happen in Smart View it may cause a larger issue due to the number of cells that would need to be parsed and processed.  Disclaimer: I did not attempt to replicate this issue in Smart View but rather is an educated guess based on my experience with spreadsheet behavior.

Note: The Dodeca xml format does not need to contain information for cells that are blank.  This format reduces the size and the processing cycles necessary to complete the task.  In addition, when we originally designed Dodeca, we tested a format similar to the one used today by Smart View and found it to be slower and less efficient.

Considering all of this information, I believe the xml format would be difficult for the Smart View team to change at this point as it would cause compatibility issues with previously created workbooks.  Further, this discussion should give some visibility to the fact that the Smart View team faces an on-going challenge to maintain compatibility between different versions of Smart View considering that different versions distributed on desktops and different versions of the internal formats that customers may have stored in their existing Excel files.  I don’t envy their job there.

After looking at all of this, I was curious to see what the xml string would look like on a large retrieve, so I opened up Smart View, connected to Sample Basic and drilled to the bottom of the 4 largest dimensions.  The resulting sheet contained nearly 159,000 rows of data.  Interestingly enough, when I looked at the contents of customProperty5.bin inside that xlsx file, the contents were compressed.  It occurred to be a bit strange to me as the xlsx file format is already compressed, but after thinking about it for a minute it makes sense as the old xls file format probably did not automatically compress content, so compression was there primarily to compress the content when saved in the xls file format.

Custom property #6 is labeled NameConnectionMap.  The corresponding property file contains xml that appears to map the range names in the workbook to the actual grid and the connection.


Custom property #7 is labeled POVPosition. The file customProperty7.bin contains the number 4 followed by a NUL character.  Frankly, I have no idea what position 4 means.

Moving on to custom property #8 which is labeled SheetHasParityContent.  This file contains the number 1 followed by a NUL character.  This is obviously a boolean flag that tells the Smart View code that new features, such as support for multiple grids, are present in this file.

Custom property #9 is labeled SheetOptions.  The corresponding file, customProperty9.bin, contains an xml stream that (obviously) contains the Hyperion options for the sheet.


Custom property #10 is labeled ShowPOV and appears to contain a simple Boolean flag much like that in custom property #8.

Finally, custom property #11 is labeled USER_FORMATTING and may not be related to Smart View.

I did look through some of the other files in the .zip and found a few other references to Smart View, but I did not see anything significant.

So, now that we have completed an overview of what is contained in one, very simple, multi-grid file, what have we learned?

  1. There is a bunch of stuff stored under the covers when you save a Smart View retrieve as an Excel file.
  2. With the reported performance issues in certain situations with Smart View, you should now have an idea of where to look to resolve Smart View issues in your environment.

There are a number of files I did not cover in this overview that could also cause performance issues.  For example, Oracle support handled one case where they found over 60,000 Excel styles in the file.  Smart View uses Excel Styles when it applies automatic formatting to member and data cells.  When there are that many styles in the workbook, however, it is logical that Excel would have a lot of overhead searching through its internal list of Style objects to find the right one.  Accordingly, there is a styles.xml file that contains custom styles.  If you have a bunch of Style objects, you could delete the internal styles.xml file.

Note: Be sure to make a copy of your original workbook before you mess with the internal structures.  There is a possibility that you may mess it up and lose everything you have in the workbook. Further, Oracle does not support people going under-the-covers and messing with the workbook, so don’t even bring it up to support if you mess something up.

Wow, that should give you some idea of what may be going on behind the scenes with Smart View.  Even with the experience I have designing and writing the Dodeca web services that talk to Essbase, I wouldn't say that I have a deep understanding of how the information in a Smart View workbook really works.  However, one thing is for certain;  Dodeca does not put stuff like this in your Excel files.  It may be interesting to hear what you find when you explore the internals of your workbooks.
Categories: BI & Warehousing

Dodeca 6.6.0.4194 Now Available for Download!

Tim Tow - Mon, 2013-11-25 18:23
This past Friday, November 22nd, we completed our work on the newest version of the Dodeca Spreadsheet Management System and made Dodeca 6.6.0.4194 available for download from our website.  This blog entry is a sneak peek at some of the new features in version 6.6, as well as 6.5, which was released to select customers with specific functionality requests.  There are a few features that are particularly useful for end users, so let’s start there.
More Excel Support
Dodeca has always been strong on Excel version support and this version delivers even more Excel functionality.  Internally, we use the SpreadsheetGear control, which does a very good job with Excel compatibility.  This version of Dodeca integrates a new version of SpreadsheetGear that now has support for 398 Excel functions including the new SUMIFS, COUNTIFS, and CELL functions.
Excel Page Setup Dialog
The new version of Dodeca includes our implementation of the Excel Page Setup Dialog which makes it easy for users to customize the printing of Dodeca views that are based on Excel templates.  Note that for report developers, the Excel Page Setup has also been included in the Dodeca Template Designer.


New PDF View Type
Customers who use PDF files in their environments will like the new PDF View Type.  In previous releases of Dodeca, PDF documents displayed in Dodeca opened in an embedded web browser control.  Beginning in this version, Dodeca includes a dedicated PDF View type that uses a specialized PDF control.


View Selector Tooltips
Finally, users will like the new View Selector tooltips which optionally display the name and the description of a report as a tooltip.


Performance
Performance is one of those things that users always appreciate, so we have added a new setting that can significantly improve performance in some circumstances.  Dodeca has a well-defined set of configuration objects that are stored on the server and we were even awarded a patent recently for the unique aspects of our metadata design.  That being said, depending on how you implement reports and templates, there is the possibility of having many queries issued to the server to check for configuration updates.  In a few instances, we saw that optimizing the query traffic could be beneficial, so we have implemented the new CheckForMetadataUpdatesFrequencyPolicy property.  This property, which is controlled by the Dodeca administrator, tells Dodeca whether we should check the server for updates before any object is used, as was previously the case, only when a view opens, or only when the Dodeca session begins.  We believe the latter case will be very useful when Dodeca is deployed in production as objects configured in production often do not change during the workday and, thus, network traffic can be optimized using this setting.  The screenshot below shows where the administrator can control the update frequency.


Though users will like these features, we have put a lot of new things in for the people who create Dodeca views and those who administer the system.  Let’s start with something that we think all Dodeca admins will use frequently.
Metadata Property Search Utility
As our customers continue to expand their use of Dodeca, the number of objects they create in the Dodeca environment continues to grow.  In fact, we now have customers who have thousands of different objects that they manage in their Dodeca environments.  The Metadata Property Search Utility will help these users tremendously.

This utility allows the administrator to enter a search string and locate every object in our system that contains that string.  Once a property is located, there is a hyperlink that will navigate to the given object and automatically select the relevant property.  This dialog is modeless, which means you can navigate to any of the located items without closing the dialog.

Note: this version does not search the contents of Excel files in the system.
Essbase Authentication Services
In the past, when administrators wished to use an Essbase Authentication service to validate a login against Essbase and automatically obtain Dodeca roles based on the Essbase user’s group memberships, they had to use an Essbase connection where all users had access to the Essbase application and database.  The new ValidateCredentialsOnly property on both of the built-in Essbase Authentication services now flags the service to check login credentials at the server-level only, eliminating the need for users to have access to a specific Essbase database.
New Template Designer Tools
Prior to Dodeca 6.x, all template editing was performed directly in Excel.  Since that time, however, most template design functionality has been replicated in the Dodeca Template Designer, and we think it is preferable due to the speed and ease of use with which users can update templates stored in the Dodeca repository.  We have added a couple of new features to the Template Designer in this version.  The first tool is the Group/Ungroup tool that allows designers to easily apply Excel grouping to rows and/or columns within the template.   The second new tool is the Freeze/Unfreeze tool that is used to freeze rows and/or columns in place for scrolling.
Parameterized SQL Select Statements
Since we introduced the SQLPassthroughDataSet object in the Dodeca 5.x series, we have always supported the idea of tokenized select statements.  In other words, the SQL could be written so that point-of-view selections made by users could be used directly in the select statement.  In a related fashion, we introduced the concept of parameterized insert, update, and delete statements in the same version.  While parameterized statements are similar in concept to tokenized statements, there is one important distinction under the covers.  In Dodeca, parameterized statements are parsed and converted into prepared statements that can be used multiple times and results in more efficient use of server resources.  The parameterized select statement was introduced in this version of Dodeca in order for customers using certain databases that cache the prepared statement to realize improved server efficiency on their select statements.
Workbook Script Formula Editor Improvements
We have also been working hard to improve extensibility for developers using Workbook Scripts within Dodeca.  In this release, our work focused on the Workbook Script Formula Editor.  The first thing we added here is color coding that automatically detects and distinguishes Excel functions, Workbook Script functions, and Dodeca tokens.  In the new version, Excel functions are displayed in green, Dodeca functions and parentheses are displayed in blue, and tokens are displayed in ochre.   Here is an example.



In addition, we have implemented auto-complete for both Excel and Dodeca functions.


New SQLException Event
Version 6.6 of Dodeca introduces a new SQLException event that provides the ability for application developers to customize the behavior when a SQL Exception is encountered.
XCopy Release Directory
Beginning in version 6.6, the Dodeca Framework installation includes a pre-configured directory intended for customers who prefer to distribute their client via XCopy deployment instead using Microsoft ClickOnce distribution.  The XCopy deployment directory is also for use by those customers who use Citrix for deployment.
Mac OS X Release Directory
The Dodeca Framework installation now includes a pre-compiled Dodeca.app deployment for customers who wish to run the Dodeca Smart Client on Mac OS X operating systems.  What that means is that Dodeca now runs on a Mac without the need for any special Windows emulators.  Dodeca does not require Excel to run on the Mac (nor does it require Excel to run on Windows for that matter), so you can certainly save your company significant licensing fees by choosing Dodeca for your solution. 

In short, you can see we continue to work hard to deliver functionality for Dodeca customers.  As always, the Dodeca Release Notes provide detailed explanations of all new and updated Dodeca features.  As of today, we have decided to make the Release Notes and other technical documents available for download to non-Dodeca customers.  If you are curious about all of the things Dodeca can do, and if you aren't afraid to dig into the details, you can now download our 389 page cumulative Release Notes document from the Dodeca Technical Documents section of our website.  



Categories: BI & Warehousing

Conditional Borders

Tim Dexter - Mon, 2013-11-25 11:57

How can you conditionally turn cells borders on and off in Publishers RTF/XSLFO templates? With a little digging you'll find what appears to be the appropriate attributes to update in your template. You would logically come up with using the various border styling options:

 

border-top|bottom|left|right-width
border-top|bottom|left|right-style
border-top|bottom|left|right-color

 

Buuuut, that doesnt work. Updating them individually does not make a difference to the output. Not sure why and I will ask but for now here's the solution. Use the compound border formatter border-top|bottom|left|right. This takes the form ' border-bottom="0.5pt solid #000000". You set all three options at once rather than individually. In a BIP template you use:

<?if:DEPT='Accounting'?>
<?attribute@incontext:border-bottom;'3.0pt solid #000000'?>
<?attribute@incontext:border-top;'3.0pt solid #000000'?>
<?attribute@incontext:border-left;'3.0pt solid #000000'?>
<?attribute@incontext:border-right;'3.0pt solid #000000'?>
<?end if?>

3pt borders is a little excessive but you get the idea. This approach can be used with the if@row option too to get the complete row borders to update. If your template will need to be run in left to right languages e.g. Arabic or Hebrew, then you will need to use start and end in place of left and right.

For the inquisitive reader, you're maybe wondering how, did this guy know that? And why the heck is this not in the user docs?
Other than my all knowing BIP guru status ;0) I hit the web for info on XSLFO cell border attributes and then the Template Builder for Word. Particularly the export option; I generated the XSLFO output from a test RTF template and took a look at the attributes. Then I started trying stuff out, Im a hacker and proud me!  For the users doc updates, I'll log a request for an update.


Categories: BI & Warehousing

Desktop Testing XSL

Tim Dexter - Thu, 2013-11-21 23:05

Bit of a corner case this week but I wanted to park this as much for my reference as yours. Need to be able to test a pure XSL template against some sample data? Thats an XSL template that is going to generate HTML, Text or HTML. The Template Viewer app in the BI Publisher Desktop group does not offer that as an option. It does offer XSL-FO proccesing thou.

A few minutes digging around in the java libraries and I came up with a command line solution that is easy to set up and use.

1. Place your sample XML data and the XSL template in a directory
2. Open the lib directory where the TemplateViewer is installed. On my machine that is d:\Oracle\BIPDesktop\TemplateViewer\lib
3. Copy the xmlparserv2.jar file into the directory created in step 1.
4. Use the following command in a DOS/Shell window to process the XSL template against the XML data.

java -cp ./xmlparserv2.jar oracle.xml.parser.v2.oraxsl fileX.xml fileY.xsl > fileX.xls


The file generated will depend on your XSL. For an Excel output, you would instruct the process to generate fileX.xls in the same folder. You can then test the file with Excel, a browser or a text editor. Now you can test on the desktop until you get it right without the overhead of having to load it to the server each time.

To be completely clear, this approach is for pure XSL templates that are designed to generate text, html or xml. Its not for the XSLFO templates that might be used at runtime to generate PDF, PPT, etc. For those you should use the Template Viewer application, it supports the XSLFO templates but not the pure XSL templates.

If your template still falls into the pure XSL template category. This will be down to you using some BIP functionality in the templates. To get it to work you'll need to add in the Publisher libraries that contain the function e.g. xdo-core.jar, i18nAPI_v3.jar, etc to the classpath argument (-cp.)

So a new command including the required libraries might look like:

java -cp ./xmlparserv2.jar;./xdo-core.jar;./i18nAPI_v3.jar 
                            oracle.xml.parser.v2.oraxsl fileX.xml fileY.xsl > fileX.xls

 You will need to either move the libraries to the local directory, my assumption above or include the full path to them. More info here on setting the -cp attribute.

Categories: BI & Warehousing

Save $250 on Kscope14 Registration Now!

Tim Tow - Thu, 2013-11-21 17:17
If you work in the Essbase, Oracle EPM, or Oracle BI world, *the* place to be every June is the annual Kscope conference.  Registration is open for the next conference, Kscope14, coming next June in Seattle, WA.  If you are not currently a full ODTUG member, let me tell you how you can save $250 on the $1650 registration fee.

There are two steps you have to take to "save big".  First, become a full member of ODTUG for $99 and enjoy all of the benefits, including access to a members-only presentations library, throughout the year.  Next, register for Kscope14 and you are eligible for the members-only price of $1500 for a savings of $150.  While you are registering, simply use the code AOLAP to get an additional $100 discount!

My company, Applied OLAP, is one of top-tier Platinum Sponsors of Kscope14 and I will be there.  I hope to see you at the conference and, if you were able to save some money by using our exclusive AOLAP code, be sure to stop our booth, say hello, and learn how the Dodeca Spreadsheet Management System can help your company reduce spreadsheet risk, increase spreadsheet accuracy, and reduce costs.
Categories: BI & Warehousing

The Riley Family, Part II

Chet Justice - Tue, 2013-11-12 22:13
You didn't miss Part I, at least not here you didn't.

Many of you know Mike Riley. If you don't, here's a little history. He's the past president of ODTUG (for like 37 years or something) and for the last two years, he's served as Conference Chair for Kscope. Yeah, that doesn't really follow, but you know I'm a bit...scattered.

Did you read the link above? OK, well, here's the skinny. Mike has rectal cancer. Stage III. If it weren't for the stupid cancer part, the jokes would abound. Oh wait, they do anyway. Mike was diagnosed shortly after #kscope13, right around his 50th birthday (Happy Birthday Mike, Love, Cancer!). Ugh. (I want to say, "are you shittin' me?" see what I mean about the jokes? I can't help myself, I'm 14). Needless to say, cancer isn't really a joke. We all know someone affected by it. It is...well, it's not fun.

Go read his post if you haven't already. I'm going to give my version of that story. I'll wait...

So, Sunday morning, Game 3 of the World Series went to the Cardinals in a very bizarre way. I was watching highlights that morning as I had missed the end of the game (doesn't everyone know that I'm old and can't stay up that late to watch baseball?). Highlights. Mike lives in St. Louis. He's a Cardinal's fan. Wouldn't it be cool if he and his family could go to the game (mostly just his family, I don't like Mike that much). So I make some phone calls to see what people think of my idea. My idea is met with resistance. OK, I'll skip the people. Let's call Lisa (Mike's wife).

Apparently Sunday's are technology free days in the Riley household, no response. I go for a bike ride, but I take my phone, just in case Lisa calls me back. After the halfway point, my phone rings, I jump off the bike to answer.

So I talked to Lisa about my idea, can Mike handle the chaos of a World Series game?

We hang up and she goes to work. BTW, I asked her to keep my name out of it, but she didn't. We'll have words about that in the future.

She calls back (I think, it may have been over text, 2 weeks is an eternity to me). "He doesn't think he can do it."

So I call Mike directly (Lisa had already spoiled the surprise.)

"What about Box seats? You know, where the people with top hats and monocles sit? Away from the rift-raft, much more comfortable and free food and beer."

Backstory. Mike had finished his first round of chemo less than a week before Sunday. To make things worse, he decided it was a good time to throw out his back. He wasn't in the best of shape.

Mike said he thought he could do it.

OK, nay-sayers aside, let's see what we can do. I emailed approximately 50 people, mostly ODTUG people; board members, content leads, anyone I had in my address book. "Hey, wouldn't it be great to send Mike and his family to Game 5 of the World Series? We need to do this quick, tickets will probably double in price tonight especially if the Cardinals win." (that would mean Game 5 would be a clincher for the Cardinals, at home, muy expensive).

Within about 20 minutes, a couple of people pledged $600.

Holy shit!

At the prices I had seen, I was hoping to get between $50 and $100 from 50 people, hoping. I had $600 already. Game starts. Now it's up to $1100 in pledges. Holy shit, Part II. This might just be possible. Another 30 minutes and were about an hour into Game 4. Ticket prices have already gone up by $250 a ticket. Given that maybe 4 people have responded and I have $1600 in pledges, I pull the trigger. I bought 4 box seat tickets for the Riley family. (I had to have a couple of beers because I was about to drop a significant chunk of change without actual cash in hand, I could be out a lot of money, liquid courage is awesome).

Tickets sent to the Riley family. Pretty good feeling.

Like I said, I was confident, but I was scared. Before the end of the night though, there was over $5K pledged to get Mike and family to Game 5. Holy shit, Part III.

By midday Monday, pledges were well over $7K. I'll refer you back to Mike's post for more details. Shorter: jerseys for the family and a limo to the game.

Here's the breakdown: 35 people pledged, and paid, $8,080. Holy shit, Part IV. Average donation was $230.86. Median was $200. Low was $30 and high was $1000. Six people gave $500 or more. Nineteen people gave $200 or more. The list is a veritable Who's Who in the Oracle community.

Tickets + Jerseys + Limo = $6027.76

Riley family memory = Priceless.

So, what happened to the rest? Well, they have bills. Lots of bills. With the remainder, $2052.24, we paid off some hospital bills of $1220.63. There is currently $831.61 that will be sent shortly. It doesn't stop there though. Cancer treatment is effing expensive. Mike has surgery in December. He'll be on bed rest for some time. His bed is 17 years old. He needs a new one. After that, more chemo and more bills.

"Hey Chet, I'd love to help the Riley family out, can I give you my money for them?"

Yes, absolutely. Help me help them. I started a GoFundMe campaign. Goal is $10K. Any and all donations are welcome. Gifts include a thank you card from the Riley family and the knowledge that you helped out a fellow Oracle (nerd, definitely a nerd) in need. You can find the campaign here.

If you can't donate money, I've also created a hashtag so that we can show support for Mike and his family. It's #fmcuta (I'll let you figure out what it means). Words of encouragement are welcome and appreciated.

Thank you to the 35 who have already so generously given. Thank you to the rest of you who will donate or send out (rude) tweets.
Categories: BI & Warehousing

Fun with SQL - My Birthday

Chet Justice - Wed, 2013-11-06 10:26
This year is kind of fun, my birthday is on November 12th (next Tuesday, if you want to send gifts). That means it will fall on 11/12/13. Even better perhaps, katezilla's birthday is December 13th. 12/13/14. What does this have to do with SQL?

Someone mentioned to me last night that this wouldn't happen again for 990 years. I was thinking, "wow, I'm super special now (along with the other 1/365 * 6 billion people)!" Or am I? I had to do the math. Since date math is hard, and math is hard, and I'm good at neither, SQL to the rescue.

select 
to_number( to_char( sysdate + ( rownum - 1 ), 'mm' ) ) month_of,
to_number( to_char( sysdate + ( rownum - 1 ), 'dd' ) ) day_of,
to_number( to_char( sysdate + ( rownum - 1 ), 'yy' ) ) year_of,
sysdate + ( rownum - 1 ) actual
from dual
connect by level <= 100000
(In case you were wondering, 100,000 days is just shy of 274 years. 273.972602739726027397260273972602739726 to be more precise.)

That query gives me this:
MONTH_OF DAY_OF YEAR_OF ACTUAL   
-------- ------ ------- ----------
11 06 13 2013/11/06
11 07 13 2013/11/07
11 08 13 2013/11/08
11 09 13 2013/11/09
11 10 13 2013/11/10
11 11 13 2013/11/11
...
So how can I figure out where DAY_OF is equal to MONTH_OF + 1 and YEAR_OF is equal to DAY_OF + 1? In my head, I thought it would be far more complicated, but it's not.
select *
from
(
select
to_number( to_char( sysdate + ( rownum - 1 ), 'mm' ) ) month_of,
to_number( to_char( sysdate + ( rownum - 1 ), 'dd' ) ) day_of,
to_number( to_char( sysdate + ( rownum - 1 ), 'yy' ) ) year_of,
sysdate + ( rownum - 1 ) actual
from dual
connect by level <= 100000
)
where month_of + 1 = day_of
and day_of + 1 = year_of

order by actual asc
Which gives me:
MONTH_OF DAY_OF YEAR_OF ACTUAL   
-------- ------ ------- ----------
11 12 13 2013/11/12
12 13 14 2014/12/13
01 02 03 2103/01/02
02 03 04 2104/02/03
03 04 05 2105/03/04
04 05 06 2106/04/05
05 06 07 2107/05/06
...
OK, so it looks closer to 100 years, not 990. Let's subtract. LAG to the rescue.
select
actual,
lag( actual, 1 ) over ( partition by 1 order by 2 ) previous_actual,
actual - ( lag( actual, 1 ) over ( partition by 1 order by 2 ) ) time_between
from
(
select
to_number( to_char( sysdate + ( rownum - 1 ), 'mm' ) ) month_of,
to_number( to_char( sysdate + ( rownum - 1 ), 'dd' ) ) day_of,
to_number( to_char( sysdate + ( rownum - 1 ), 'yy' ) ) year_of,
sysdate + ( rownum - 1 ) actual
from dual
connect by level <= 100000
)
where month_of + 1 = day_of
and day_of + 1 = year_of
order by actual asc
Which gives me:
ACTUAL     PREVIOUS_ACTUAL TIME_BETWEEN
---------- --------------- ------------
2013/11/12
2014/12/13 396
2103/01/02 32161
2104/02/03 397
2105/03/04 395
2106/04/05 397
2107/05/06 396
2108/06/07 398
2109/07/08 396
2110/08/09 397
2111/09/10 397
2112/10/11 397
2113/11/12 397
2114/12/13 396
2203/01/02 32161
So, it looks like every 88 years it occurs and is followed by 11 consecutive years of matching numbers. The next time 11/12/13 and 12/13/14 will appear is in 2113 and 2114. Yay for SQL!
Categories: BI & Warehousing

Comb Over

Tim Dexter - Tue, 2013-11-05 17:14

Being some what follicly challenged, and to my wife's utter relief, the comb over is not something I have ever considered. The title is a tenuous reference to a formatting feature that Adobe offers in their PDF documents.

The comb provides the ability to equally space a string of characters on a pre-defined form layout so that it fits neatly in the area. See the numbers above are being spaced correctly. Its not a function of the font but a property of the form field.

For the first time, in a long time I had the chance to build a PDF template today to help out a colleague. I spotted the property and thought, hey, lets give it a whirl and see in Publisher supports it? Low and behold, Publisher handles the comb spacing in its PDF outputs. Exciting eh? OK, maybe not that exciting but I was very pleasantly surprise to see it working.

I am reliably informed, by Leslie, BIP Evangelist and Tech Writer that, this feature was introduced from version 10.1.3.4.2 onwards.

Official docs and no mention of comb overs here.


Happy Combing!

Categories: BI & Warehousing

Hyperion 11.1.2.3 Update - Kscope14 Street Team Meetup in Seattle - November 8th

Tim Tow - Fri, 2013-11-01 20:54
If you are in or near Seattle on Friday, November 8th, don't miss the opportunity to see the new features of the Hyperion 11.1.2.3 release.  Oracle representatives are teaming together with the Kscope14 Street Team to bring you this 2-hour mini-session at the Fred Hutchison Cancer Research Center.  The session starts at 3 pm sharp and concludes with networking and a happy hour.  For details and to RSVP, visit http://kscope14.com/events/seattle-street-team.

I won't be able to make it to this event, but I wish I *could* be there.  I can't think of a better way to kick off a weekend!

Categories: BI & Warehousing

Competitors, Welcome to Our Webcasts

Look Smarter Than You Are - Wed, 2013-10-30 13:35
I was happy to be a part of Oracle's EPM Showcase yesterday in New York City.  It was a half-day event (plus a happy hour) that had Oracle giving a keynote followed by two 90-minute breakout session timeslots (with two sessions happening concurrently).  I was speaking during the first timeslot on Hyperion DRM (Data Relationship Management) along with Nikki from Verizon and Erin Lineberry from interRel.  In my talk, I described how companies need a single system of record for hierarchies and explained how DRM was a really good fit particularly with the new data governance module in 11.1.2.3.
There were partners in my session, and I had no problem at all with it.  After all, this was a conference open to anyone and I am a firm believer that when people learn more, the whole community benefits.  This is what motivates me to write all of my books, cause believe me, it's not for the money (Google "Starving Authors" before you ever think to make money writing).  I also speak at way too many events around the world each year from tiny user groups to massive conferences like Kscope, Collaborate, and OpenWorld with no concerns that my sessions are primarily filled with Oracle partners looking to improve.

After my session was over, there was one more timeslot for the day and since I didn't want to sit in the hall for 90 minutes, I went to Huron Consulting Group's (they're the company that bought Blue Stone) session on the future of Planning.  It sounded more interesting than Hackett's session which was my other possibility and I saw that one of the speakers was Mike Nader who is a great presenter.  If nothing else, I would get to hear Mike's engaging take on the world since he joined Blue Stone.  I sat in the room in the back row (there were plenty of extra seats, but I wanted to leave the good seats for potential customers).

Right as the session was about to start, Rick Schmitt from Huron (Blue Stone) came over to me and asked me to leave.  I was curious why since I was an official attendee at the event and he said that they were going to be talking over "some proprietary stuff."  I assumed he meant his slides at the beginning on the Blue Stone acquisition or "why Blue Stone is the best at XYZ," so I offered to leave for the first few slides.  I don't need nor want competitive info and I certainly didn't want to make him nervous during his sales pitch.  He said that no, they were going to share lots of proprietary info throughout the session and he didn't want competitors in the room during their session at all.

Rather embarrassed but more bemused, I smiled, gathered my things, and walked out of the room.  I sat in the hall for a while wondering what cool things I was missing and feeling jealous of the 50 clients that got to hear from Blue Stone.  (There's nothing like being excluded from something to make you want it more.)  As I sat there, I pondered my own stance on information sharing.  Personally, I believe that if the community as a whole gets better - if the community learns more - the quality of Hyperion implementations will rise.  Satisfaction with Oracle EPM will rise, and as the reputation of Hyperion gets better, the Hyperion market will grow which benefits the entire community: customers, Oracle, and partners.

And it made me ask what I could be doing better.

So starting effective immediately, all of the public webcasts interRel does (and we did over 100 webcasts last year) will be open to everyone.  That's right: competitors, please come join our webcasts and we'll share all the information that we spend months putting together with you.  You've always had access to our books, our sessions at user groups, our presentations at conferences, and now you have access to our webcasts too.  I hope that this starts a trend: I strongly encourage our competition to open up their sessions and webcasts to anyone who wants to attend.  Don't be afraid: if you're good at what you do, you shouldn't be afraid to help the competition get better too.  Information is meant to be free and to point out the obvious, if the Hyperion market gets bigger from happier clients telling everyone they know to buy Hyperion, your potential customer base gets bigger too.

Our next webcast is Thursday, October 31.  It's on how Smart View is finally an awesome replacement for the Essbase Excel Add-In and I hope to see a ton of our competition on the webcast.  Visit http://bit.ly/iRWebcasts to register.
Categories: BI & Warehousing

Why I'm voting for Danny Bryant and You Should Too

Chet Justice - Tue, 2013-10-29 13:36
I'm talking about the ODTUG Board of Directors.

This.



That's really all you need isn't it?

Fine.

Today wraps up the voting period for the ODTUG Board of Directors. If you're asking me what ODTUG is, stop reading now. If you are a member of ODTUG, then please give me a few minutes to pontificate (that's a word I heard Jeff Smith use once, hopefully it makes sense here).

Your favorite Oracle conference, KScope, is largely successful based on the efforts of the Board, along with the expert advice of the YCC group. In addition, if you think ODTUG should "do more with Essbase" or "charge more for memberships" these decisions are made and carried out by the board.

So if you like being in ODTUG, and you want to help it get better and grow, and be as awesome as possible, you only need to do one thing today. Go vote. Midnight tonight (10/29) is the deadline. Do it.

You get to vote for several people. I suggest you read their bios. I'll save you the time for at least one vote, and that's for Danny Bryant.

Besides that awesome photo (#kscope12 in San Antonio) up above, here are several more reasons.

1. He's into everything. OBIEE. EBS. Essbase. SQL Developer. Database. Not very many people have their hands in everything, he does. He will be able to represent the entire spectrum of ODTUG members.
2. He's a fantastic human being. It's not just because he takes pictures of himself wearing ORACLENERD gear everywhere (doesn't hurt though), he's just, awesome.
3. This (Part II)
4. He also always answers the phone, tweets, and emails I send him. He might be sick, or he might just be that responsive. The ODTUG Board member responsibilities will fit nicely on his shoulders I believe.

So go vote. Now.
Categories: BI & Warehousing

BIP 11g Dynamic SQL

Tim Dexter - Wed, 2013-10-23 23:38

Back in the 10g release, if you wanted something beyond the standard query for your report extract; you needed to break out your favorite text editor. You gotta love 'vi' and hate emacs, am I right? And get to building a data template, they were/are lovely to write, such fun ... not! Its not fun writing them by hand but, you do get to do some cool stuff around the data extract including dynamic SQL. By that I mean the ability to add content dynamically to your your query at runtime.

With 11g, we spoiled you with a visual builder, no more vi or notepad sessions, a friendly drag and drop interface allowing you to build hierarchical data sets, calculated columns, summary columns, etc. You can still create the dynamic SQL statements, its not so well documented right now, in lieu of doc updates here's the skinny.

If you check out the 10g process to create dynamic sql in the docs. You need to create a data trigger function where you assign the dynamic sql to a global variable that's matched in your report SQL. In 11g, the process is really the same, BI Publisher just provides a bit more help to define what trigger code needs to be called. You still need to create the function and place it inside a package in the db.

Here's a simple plsql package with the 'beforedata' function trigger.

Spec

create or replace PACKAGE BIREPORTS AS 

 whereCols varchar2(2000);
 FUNCTION beforeReportTrig return boolean;

end BIREPORTS;

Body

create or replace PACKAGE BODY BIREPORTS AS

  FUNCTION beforeReportTrig return boolean AS 
   BEGIN
      whereCols := ' and d.department_id = 100';
      RETURN true;
   END beforeReportTrig;

END BIREPORTS;

you'll notice the additional where clause (whereCols - declared as a public variable) is hard coded. I'll cover parameterizing that in my next post. If you can not wait, check the 10g docs for an example.

I have my package compiling successfully in the db. Now, onto the BIP data model definition.

1. Create a new data model and go ahead and create your query(s) as you would normally.

2. In the query dialog box, add in the variables you want replaced at runtime using an ampersand rather than a colon e.g. &whereCols.

 

select     d.DEPARTMENT_NAME,
...
 from    "OE"."EMPLOYEES" e,
    "OE"."DEPARTMENTS" d 
 where   d."DEPARTMENT_ID"= e."DEPARTMENT_ID" 
&whereCols

 

Note that 'whereCols' matches the global variable name in our package. When you click OK to clear the dialog, you'll be asked for a default value for the variable, just use ' and 1=1' That leading space is important to keep the SQL valid ie required whitespace. This value will be used for the where clause if case its not set by the function code.

3. Now click on the Event Triggers tree node and create a new trigger of the type Before Data. Type in the default package name, in my example, 'BIREPORTS'. Then hit the update button to get BIP to fetch the valid functions.
In my case I get to see the following:


Select the BEFOREREPORTTRIG function (or your name) and shuttle it across.

4. Save your data model and now test it. For now, you can update the where clause via the plsql package.

Next time ... parametrizing the dynamic clause.

Categories: BI & Warehousing

Excel VS BI in Financial Analysis: why the fight was over before it started

Chet Justice - Mon, 2013-10-21 19:50
by Victor Fagundo

The argument over why Businesses should abandon Excel in favor of more structured tools has been raging for as long as I have had more than a casual exposure to Oracle products. From the standpoint of an IT user Excel appears to be a simplistic, flat-file-based, error-prone tool that careless people use, despite its obvious flaws. Petabytes of duplicative Excel spreadsheets clog network drives across the globe; we as IT users know it, and it drives us crazy. Why, oh why, can’t these analysts, project managers, and accountants not grasp the elegant beauty of a centralized database solution that ensures data integrity, security, and has the chops to handle gobs of data, and abandon their silly Excel sheets?

I’ll tell you why: Excel is better. Excel the most flexible and feature-rich tool for organizing and analyzing data. Ever. Period.

For the past few years I have lived in a hybrid Finance/IT role, and in coming from IT, I was shocked at how much Excel was used, for everything. But after working with Excel on a daily basis for several years, I am a convert. An adept Excel user can out-develop any tool ( BI, Apex, Hyperion, Crystal Reports ) handily. (when dataset size is not an issue). Microsoft has done too much work on Excel, made it too extendable, too intuitive, built in so much, that no structured tool like BI, APEX, SAP, Hyperion will EVER catch up to its usability/flexibility.

Take this real-world example that came across my desk a few months ago: for a retail chain define a by-week, by-unit sales target, and create a report that compares actual sales to this target. Oh, and the weekly sales targets get adjusted each quarter based on current financial outlook.

How quickly could you turn around a DW/BI solution to this problem? What would it involve?
• Create table to house targets
• Create ETL process to load new targets
• Define BMM/Presentation Layers to expose targets
• Develop / test / publish report.

A day? Maybe? If one person handled all steps (unlikely, since the DB layers and RPD layers are probably handled by different people.)

I can tell you how long it took me in Excel: 3 hours (OBIEE driven data-dump, married with target sheet supplied to me). I love OBIEE, but Excel was still miles faster/more efficient for this task. And I could regurgitate 6 other examples like this one off at a moment’s notice.

Case in point: 95% of the data that C-level executives use to make strategic decisions is Excel based.

If you’ve ever sat in on a presentation to a CEO or other C-level executive at any medium to large sized company, you know that people are not bringing up dashboards, or any other applications. They are presenting PowerPoints with a few (less than 7) carefully massaged facts on them. If you trace the source of these numbers back down the rabbit hole, your first stop is always Excel. Within these Excel workbooks you will find “guesses” and “plugs” that fill gaps in solid data, to arrive at an actionable bit of information. It’s these “guesses” and “plugs” that are very hard to code for in an environment like OBIEE  (or any other application). Can it be done? Yes, of course, with gobs of time and money. And during the fitful and tense development, the creditably of the application is going to take major hits.

Given the above, the usefulness of OBIEE might seem bleak. But I strongly feel that applications such as OBIEE do have a proper place in the upper organizational layers of modern business: Facilitating the Tactical business layers, and providing data-dumps to the Strategic Business layers.

Since this post is mainly about Excel, I will focus on how OBIEE can support the analyses that are inevitably going to be done in Excel.

Data Formatting, Data Formatting, FORMATTING!! I can’t stress this enough. For an analyst, having to re-format numbers that come out of an export so that you can properly display them or drive calcs off them in Excel is infuriating, and wasteful.  My favorite examples: in a BI environment I worked with percentages were exported as TEXT, so while they looked fine in the application, as soon as you exported them to Excel and built calcs off them, your answer was overstated by a factor of 100 (Excel understood “75%” to be the number 75 with a text character appended, not the number 0.75).

Ask your users how they would like to SEE a fact in Excel: with decimals or not?  With commas or not? Ensure that when exported to Excel, facts and attributes function correctly.

“Pull” refreshes of information sources in Excel. In the finance world, most Excel workbooks are low to medium complexity financial models, based off a data-dump from a reporting system. When the user wants to refresh the model, they refresh the data-dump, and the Excel calculations do the rest. OBIEE currently forces a user to “push” a new data-dump by manually running/exporting from OBIEE and then pasting the data into the data-dump tab in the workbook. What an Excel user really desires is to have a data dump that can be refreshed automatedly, using values that exist on other parts of the workbook to define filters  of data-dump. Then all the user needs to do is trigger a “pull” and everything else is automated. Currently OBIEE has no solution to this problem that is elegant enough for the common Excel user. (Smartview must have its filters defined explicitly in the Smartview UI each time an analysis is pulled.)

The important part to take away from these 2 suggestions, and this entire post, is that to maximize the audience of OBIEE, we must acknowledge that Excel is the preferred tool of the Finance department, due to its flexibility, and support friendly exports to Excel as a best practice. We must also understand that accounting for this flexibility in OBIEE is daunting, and probably not the best use of the tool. If your users are asking for a highly complex attribute or fact, that is fraught with exceptions and estimations, chances are they are going to be much happier if what you give them is reliable information in a data-dump form, and allow them to handle the exceptions and estimations in Excel.
Categories: BI & Warehousing

Mobile App Designer

Tim Dexter - Thu, 2013-10-10 14:45

Back in August a new Oracle mobile solution jumped out of the gate, the Mobile App Designer (MAD). I seem to have been on the road every week for the last, goodness knows how many weeks. I have finally found some time this week to get down and work with it. Its pretty cool and above all, its capable of providing a mobile platform independent reporting solution.

But you already have a mobile application! Yep, and I think they both sit quite comfortably together. The Oracle BI Mobile Application is available from the App Store for Apple users. Its a great app, build reports, dashboards and BIP reports for your browser based users and your Apple app users can take advantage of them immediately.

MAD takes the next step forward. Maybe you don't use or can not use Apple mobile devices? Maybe you need to build something more specific around a business area that provides users with a richer experience, beyond what Answers and Dashboards can offer. However, you do not want to have to rely of the tech folks to build the mobile application, thats just piling more work on them. You also want to be platform agnostic, you might have a mix of mobile platforms. MAD can help.

For those of you that have already used the Online Template layout editor with BI Publisher, you already know how to build a mobile application. The MAD interface is essentially the online template builder user interface, tweaked for a mobile destination ie a phone or tablet.

You build your data model as you would normally including the newer direct data model build on a subject area from OBIEE.

Then start to create the 'pages' of your application and the content to sit on those pages. All the normal stuff, tables, pivot tables, charts, images plus accordians, filters and repeating objects. On top of that is the ability to then extend the visual objects that are available to users. Maps (google or oracle), D3 visuals, gantt charts, org charts, if you can either write the code or leverage an existing javascript library, MAD has the extension framework to support it.

You can build and test in a browser and then deploy to your own BI App Store. Users, on their mobile devices, can then subscribe to an application. They can open and interact with your app using their phone or tablet's interactive features just as they would with a native application.  As you update your app and add new features the changes will be picked up the next time your users open the application.

Interested? Want to know more? The Oracle MAD home page has a ton of content including tutorials, etc. We are planning to dig into MAD in forthcoming posts. The geek in me wanted to be able to build plugins using the D3 and other visuals. I have been working with Leslie on some of the documentation and we'll be sharing some of that 'plugin' doc and how tos in the coming weeks.

Categories: BI & Warehousing

The New AppliedOLAP.com / Free Tools Access

Tim Tow - Sat, 2013-09-28 18:53
My team has been hard at work getting our new website out the door, and we feel that it will make for a better experience all-around for our users and the users of the free OLAPUnderground utilities.  To help make the experience better, we have implemented a new download process.  Previously, users who wished to download files selected the files they wished to download and then provided their email address.  Our server then sent an email with temporary links for the download.  The email process sometimes caused issues with email filters or incorrect emails, so we decided to work to provide a better experience by using an off-the-shelf software release component.  As a result of this change, we are now requiring users to have a user account to download software.  Fortunately, the user account is free and you can create your own account in about a minute.

On the new site, all of the free downloads are available from the 'Downloads' section under 'Resources.'


If you are not logged in and you attempt to access the 'Downloads' section of the site, you will be prompted to login or register:


If you already have a www.appliedolap.com account, you can go ahead and login using your existing credentials, but if you don't, simply click the 'Register' button and fill out the registration form that pops up.


Once you have filled out the required information and pressed 'Register', a confirmation e-mail will be sent to the address provided on the registration form.


Click 'OK' and check your inbox for the confirmation e-mail.  Once you click the activation link, all you need to do is login to the site and you will have full access to the 'Downloads' section.  If you are a Dodeca user, we will provide you access to the 'Dodeca' downloads page, but as a free registered user, you will have access to the OLAPUnderground tools, as well as the Next Generation Outline Extractor.

Note:  Your Applied OLAP account username will always be the
e-mail address you used when you registered your account.


Go back to the 'Downloads' section after you log in to access the OLAPUnderground Utilities, as well as the Next Generation Outline Extractor.


We hope you find the new Applied OLAP website informative and easy-to-use.  While you are there, be sure to check out all of the new information about the Dodeca Spreadsheet Management System and sign up for a free, personalized web demo so you can learn how Dodeca can help you.
Categories: BI & Warehousing

Oracle Open World 2013 General Session: Empowering Modern Enterprise Performance Management

Tim Tow - Wed, 2013-09-25 16:23
Here at Oracle Open World 2013, I attended the general session entitled "Empowering Modern Enterprise Performance Management" which was led by Balaji Yelamanchili, Oracle Senior Vice President for Analytics and Performance Management Products.  His keynote focused on the development priorities within the Oracle EPM group.  Those priorities are:

  • Continued Investment in Existing Modules
  • EPM Applications in the Cloud
  • Continued Innovation in New Modules

There are a number of things planned in the area of continued investment in existing modules.  The investment areas include:

  • HFM platform work to make HFM cross platform and to improve performance.  In addition, these changes allow HFM to run on Exalytics which puts massive computing power under HFM.  For example, testing of HFM on Exalytics has shown that virtual close becomes a possibility even in complex consolidations.  One test that had 31,000 accounts, 2,000 legal entities and 66 currencies showed a reduction of consolidation time from 7+ hours down to less than 30 minutes.  This type of performance increase, if borne out in the real world, could have a real effect on how and when companies do their consolidations.
  • Optimization of Hyperion Planning and Hyperion Profitability and Cost Management for Exalytics.  This scalability allows for implementations that spread further in the company (i.e. more users), but also allow companies to consolidate their applications onto fewer physical machines.
  • Packaged OBI Analytics for Hyperion Planning.  The packaged analytics make it easier and faster to deploy Hyperion Planning with an added benefit of reduced consulting costs.

Oracle is continuing its move into the cloud arena with 2 services.

  • The Oracle Planning and Budgeting Cloud Service ("OPBCS") has been in customer and partner preview for a few months now and is nearly general availability.  Oracle is stressing that this isn't just moving the software to a managed service running on the cloud, but rather is a fundamental new paradigm for planning that focuses on self-serve models that have not existing since the inception of the classic, on-premise Hyperion Planning.  Because OPBCS must run without the need for IT interaction, users must be able to quickly and easily configure what they need from the service.  That being said, Oracle is taking a very conservative strategic approach with their Cloud Planning application as they are not trying to displace traditional on-premise Planning applications and move them to the cloud.  Rather, they are emphasizing the cloud for use in quick-to-deploy departmental applications or as a development platform that can be used to build an application for on-premise deployment.  Oracle expects to make the OPBCS available later this calendar year.
  • A new service that was announced at Open World 2013 is the new Financial Reporting Cloud Service.  Based on the slides and the comments made during the presentation, this isn't simply moving the classic Hyperion Financial Reporting application to the cloud, but rather they have imagined and created an application that appears to be targeted for statutory reporting.  How it appears to work is that the developer/administrator creates a framework for the reporting package which may be, for example, a corporate annual report and can assign responsibilities for different parts of the report to team members.  The editing of different components can be completed either via refreshing of data from the corporate data stores for items like a balance sheet or income statement, or edited in Word for things like the management discussion.  The product tracks workflow and approvals and, when complete, produces a PDF of the completed document.  It looks quite different than what we see today in HFR.
For innovation in new modules, Oracle is focused pretty heavily on mobile.  As of now, the focus on mobile has been on information consumption but Oracle plans to work towards full user functionality in the future.  They are using the 90/90 rule as a guide for their mobile effort by focusing on the functionality used by 90% of users 90% of the time.  In addition, they have created some purpose-specific applications such as the new EPM Workflow Mobile App that was created specifically for approval workflows for Planning and Financial Close.

Oracle has been working hard on the EPM Suite, but notice I didn't really say anything about my beloved Essbase.  Balaji did not say much about Essbase in his session, but rather left that for the Essbase Roadmap session that I also attended.  I will work on a blog post on that session soon.

Categories: BI & Warehousing

Essbase Meetup in San Francisco This Tuesday!

Tim Tow - Sun, 2013-09-22 12:08
As you may or may not know, this week is the annual mega-conference Oracle Open World and, in conjunction with the conference, I am hosting, with fellow Oracle ACE Director Cameron Lackpour, an Essbase Meetup this Tuesday evening in San Francisco.  Unlike most events during the week of Open World, our Essbase meetup is open to both Open World attendees and non-attendees, so if you are in the San Francisco area, make your plans to attend.  Last year, we had several Oracle ACEs, ACE Directors, and Oracle product managers who attended; several of the same people have confirmed their attendance this year as well.

The meetup is at Specchio, a great Italian restaurant in the Mission District.  Specchio is co-owned by one of my friends, chef Gino Assaf, and has had some notable guests in the past couple of years including numerous NBA teams and one of my favorite rock bands, The Who.  Specchio is located at 2331 Mission Street in San Francisco.

If possible, please RSVP for the meetup at http://www.meetup.com/Oracle-Open-World-Essbase-EPM-meetup/events/138157192/ so we can get a count for the restaurant.  I hope to see you there!


Categories: BI & Warehousing

Pages

Subscribe to Oracle FAQ aggregator - BI &amp; Warehousing