BI & Warehousing

Next Generation Outline Extractor Webcast - Oct 8th!

Tim Tow - Fri, 2014-09-12 17:07
I am doing a repeat of my Kscope15 'Best Speaker' award-winning presentation as part of the ODTUG webcast series. Here is the official announcement from ODTUG:

Wednesday, October 8, 2014 12:00 PM - 1:00 PM EDT

Next Generation Essbase Outline Extractor Tips and Tricks
Tim Tow, Applied OLAP

The Next Generation Outline Extractor is the follow-up to the classic OlapUnderground Essbase Outline Extractor used by thousands of Essbase customers. This session, which was the highest-rated session at Kscope15 in Seattle, explores some of the new capabilities of the Next Generation Outline Extractor, including command line operations exported directly to a relational database. Attend this session to learn how to leverage this free utility in your company.
Make you you sign up to join my on October 8th; you can register here!.
Categories: BI & Warehousing

Oracle Tours Africa and the Middle East

Look Smarter Than You Are - Sun, 2014-06-15 11:33
Happy Father's Day, everyone!  I got up early this morning to write about my recent experience traveling the world on Oracle's behalf.  I got to attend the first annual Oracle Technology Network tour of Africa and the Middle East.  It made 2 stops in North Africa (both in Tunisia), 2 stops in Saudi Arabia, and the final stop was in Dubai, UAE.

Tariq Farooq first mentioned the idea of doing a MENA (Middle East & North Africa) tour to me in Beijing last fall.  He asked if I'd be willing to travel half-way around the world to speak to people in English that primarily spoke French and Arabic, and I - of course - said "yes."  Here's Tariq being interviewed by Lillian Buziak at Collaborate 2014 (audio is a bit difficult to hear):


I had two reasons for wanting to go: I do love educating/evangelizing for Oracle EPM, BI, and Business Analytics.  The possibility of reaching new audiences for the first time was exciting. My other reason for going was that I wanted to experience totally different cultures than I ever have before.  I've spoken on 5 continents (now 6 after this tour and I'm anxiously awaiting the OTN Tour to Antarctica) before and have seen presented everywhere from a women's college in Mumbai that was 95F with no air conditioning in the presentation room to a ballroom in the Philippines that had 3 simultaneous English sessions going on (in one room!) all happily observed by smiling Filipinos.  From China to India to Australia to Germany, I have seen some amazing slices of life, but nothing prepared me for the differences I saw on this tour.

In each of the sections below, I have linked the header to a blog from my new best German friend, Bjoern Rost.  He blogged after every stop and unlike me, he actually understood all the Oracle RDBMS sessions on the tour.  Visit http://portrix-systems.de/blog/author/brost/ to see his entertaining blog posts.  (Warning: though I think Bjoern is hilarious, being German, you may find his posts to be 'not funny.'  German humor is an acquired taste.)

I left for the first stop, Tunisia, on Memorial Day (in the USA), May 26, 2014...




Tunisia, May 27To get to Tunisia in time for my session, I left Dallas. Texas at 10AM on Monday, flew to JFK (New York City), flew to Rome, flew to Tunis, and had a nice car waiting for me at the airport compliments of our hosts in Tunisia.  I landed at 10:30AM on Tuesday and considering I was flying to Africa, I felt that the trip went by quickly.  I made it through customs in Tunis in about 15 minutes, walked out the front door of the hotel, and was in an entirely different world.

Speaking in North America, South America, Europe, Asia, and Australia had done absolutely nothing to prepare me for Africa.  The closest thing I could compare it to in my life (but the comparison does not do it justice) was the cities of India: chaotic, dirty, cramped, foreign, and chaotic (worth mentioning again).  Now take all that, remove the cows, and make it Muslim.

My host picked me up in a nice car and we began the hour drive to Beja where the conference was being held.  We passed mounds of trash piled up in the center of the roads though thankfully the heat (high was ~75F when I arrived) didn't make the place smell horribly.  Traffic laws seemed to be non-existent, but it moved fairly quickly being in the middle of the day.  I loved looking out the window at the shops along the road and carts selling watermelon approximately every 100 feet (I was told by my host that it was watermelon season).

We left the city about 20 minutes after I got in the car and I was suddenly in Tuscany.  At least, it looked like Tuscany: fields of amber waves of grass, wide open spaces, wildflowers, lakes, olive groves, country life, gorgeous hills... truly one of the most beautiful countrysides I've seen in my entire life.  Since I had been traveling for over a day, the pleasant scenery soon lulled me to sleep.


My driver woke me up when we got to the technical university in Beja. I walked in to find Tariq trying to explain Oracle Enterprise Manager to a bunch of college students who didn't seem to understand databases let alone Oracle.  I tried to hide in one of the back seats, but Tariq immediately called me up on stage to answer a question about how to develop optimal databases.

Not long after I got there, we broke for lunch.  Our hosts took us to a traditional Tunisian restaurant in downtown Beja.  They were kind enough to make me vegetarian food.  Lunch is apparently a sacred event not-to-be-hurried in Tunisia, so we made it back to the university over 2 hours after we left, fully satiated.

I volunteered to give a session introducing Big Data and Analytics to the college kids, because I felt that it required little technical background.  It seemed to go over well.  My favorite part was when I told a joke about the differences in social media sites and only 10 people laughed.  The people on either side of those 10 then asked them to repeat in Arabic and French what I had said, which caused those people to laugh.  They then shared it around the room and it was like a disease vector of laughter that took 2 minutes to make it around to the 150+ students in the room.  In case you haven't seen it, here's essentially what I said out-loud:
After the sessions were over, I asked one of the professors why everyone listened so intently if they had no background in Oracle.  I mentally wondered if it was because I was an awesome presenter bringing the gospel of Oracle to the future of Africa.  I was told "they didn't understand a lot of what you were saying, but they love listening to people speak English."  So much for my future African disciples.
Our hosts offered to drive us to Dougga, the so-called "best preserved Roman ruins outside of Italy."  It sounded like an exaggeration, but it was actually an understatement. Dougga was once a "small" Roman town on the fringes of the empire... and the miles of town are for the most part still there.  We arrived shortly after they closed the gates for the day.  Our hosts got out of the front car in our 4-car caravan to talk to the guards.  I was in the last car and saw an interesting polite dialog when our hosts started pointing to my car.  I waved back.  The guard smiled and raised the gates for our caravan to enter.  I wondered if bribing had occurred, so I asked how we got in after hours.  Our gracious hosts explained that I was renowned historian, Edward Roske, a visiting professor from the United States of America whose sole purpose for being in Tunisia was to see the Dougga ruins.  They said I should take lots of pictures and walk around looking officially important.  I discovered later that they weren't kidding: they really did tell this to the guard, so I took at least 50 pictures and took some very official selfies to help sell my renowned historian status.




The ruins were truly majestic.  Every time I came around a bend, there was another temple, theatre, circus, road, market, tunnel, column, arch, statue, or something else 2,000 years old to be seen.  It is all in a semi-wild state with no borders separating the ruins from the countryside.  There were even wildflowers growing in the central square:
My favorite moment of the entire trip occurred when I broke away from the rest of our group to go explore some arches on the edge of the ruins.  I went to take a picture of one of the doorways, and I got photobombed:
I went through the doorway to discover a local sheepherder grazing his sheep right in the ruins:

They started on the edge of the ruins but eventually the herder marched his sheep right down the center of the 2,000 year old road leading through Dougga.  Tariq decided to join their herd:
I can't stress enough how amazing this site is.  I would encourage people to visit Tunisia if for no other reason than to see Dougga and Carthage.  You have never felt Roman society like you can wandering around the ancient town with only sheep to keep you company.

Eventually, the guards at the entrance (the only guards in the place, so far as we could tell) came to find our renowned historian group because they wanted to go home.  We stayed the night in the Golden Tulip Hotel in Carthage which was a 4-star hotel for under $200 USD per night.  I recommend it to anyone.  The next morning, the OTN MENA 5 (Tariq, Mike Ault, Bjoern, me, and Jim Czuprynski) headed for the flight to Cairo then on the Riyadh.


Saudi Arabia, May 29-31While the Islam is a part of the culture in Tunisia, Islam is the culture in Saudi Arabia.  I have never seen a country more dominated by a single religion than Saudi Arabia.  It is one of the most difficult places in the world to get a visa (they suspended tourist visas 5 years ago) and it's even harder for a non-Muslim like me.  I spent 4 hours clearing customs which gave me a lot of time to study up on what I was in for.

My guidebook (and several websites) told me about all the things I wasn't allowed to do, say, or maybe even think when I got to Saudi.  Here's what I was told versus what actually happened:
- Muslims everywhere.  Yes, 100% true.  They have calls to prayers everywhere and we had to stop presenting when it was time for prayer.  The whole city stops, for that matter, when it's prayer time.  I was lucky enough to be in a public park for evening prayer one day.  The sounds of the call to prayers across the city were beautiful.

- Traffic fatalities.  90% true.  Saudi apparently has the highest incidence of traffic fatalities in the world because traffic laws are more like traffic vague suggestions only to be followed if everyone has plenty of time and sort of feels like it.  I was prepared to almost die every time I got in a car, and while I saw no deaths, I saw multiple car accidents of the fender bender type each day I was in Saudi.  At one point, we were stopped at a red-light to make a left turn.  With traffic coming from both directions in front of us, a car behind us who wanted to make a left-hand turn felt that our stopping was delaying his day.  He didn't honk or behave rudely in any way: he just drove around us and made the left-turn on the red into oncoming traffic.  No one seemed annoyed at the man for doing it.
- Pornography.  100% nonexistent in Saudi.  They even go through the magazines in the shops and black out with a sharpie anything that's considered too revealing (shoulders, waists, knees, etc.).  They also sharply monitor the web and block out any site deemed inappropriate (including Bing.com with safe search set to anything but strict).
- Pictures of other people.  0% true.  I was told before I went that Muslims do not believe in having images taken of people.  What I actually found was the most selfie-absorbed culture I've ever seen, and I live in America with a teenage child.  I couldn't walk 10 feet at the Riyadh or Jeddah events without someone taking a picture.  I was also told that you couldn't take pictures of public buildings or in public buildings (like the national museum).  Totally untrue: people were taking pictures of just about anything except women.
- Women being covered.  100% true.  All the women wore black abbeyahs at all times.  You're not allowed to film them or talk to them.  That said, I didn't see that many.  Both the events in Riyadh and Jeddah were male-only.  We did see women in the public places particularly near retail outlets and in the city parks & museums.
- Men wearing suits.  25% true.  For the most part, the men wore traditional dress shirts and head coverings.  I wore a suit (no tie) which considering it was 110F+, was quite a sacrifice.  My fellow presenters wore ties in addition to their suits which proves they're more willing to sacrifice for the cause of Oracle.
- No alcohol. 100% true but weird.  The first night I got to Riyadh, I opened my minibar to find... a Budweiser.  Closer examination revealed it was a "Budweiser NA" signifying no alcohol.  Every restaurant we went to offered us "Saudi champagne" or "Saudi wine" which apparently means alcohol-taste without any actual alcohol.  Since I hate the taste of alcohol, I didn't think non-alcoholic alcohol would taste any better, so I avoided it.
- No narcotics.  100% true so far as I was willing to test it.  I actually panicked during customs at the thought that maybe I had some prescription drug in my laptop bag that had a narcotic in it.  Narcotics are a capital crime in Saudi, and I spent most of my 4 hours wondering if Ambien counts as a narcotic.  Luckily for me, I wasn't executed for sleep aid smuggling.

After the worst customs experience of my entire life at the airport in Riyadh, I got to the Marriott hotel for about 4 hours sleep before the day was to begin.  I got to go up to the concierge floor for an elaborate breakfast and a great view of the Riyadh skyline.

The Riyadh venue was spectacular.  The hosts (eSolutions) put on one of the best events I've ever attended on an OTN tour.  From the venue to the signage to the elaborate Lebanese food lunch to the photographer to the videographer to the speaker gifts, it was all top-notch.  Like in Beja, I presented on "Taming Big Data with Analytics" to an enthusiastic audience.  There were about 75 (all male) people there including several male children of the attendees.  They were some of the best behaved children I have ever seen even though some were elementary school age.
I got introduced at one point as "Edward Roske from the United States, a rich man who did not inherit his business from his father."  That was unique and though it left me speechless, the audience seemed very impressed at my ability to become head of a business without my father having to die first.

After the conference, our hosts took us to the National Museum.  Like much of Riyadh, it looks like it was built in the last 5 years, and in the case of a museum, this worked very well.  Since they built it all at once (versus many amazing museums around the world that were created over hundreds of years), it was able to tell a complete story from the beginning of the universe up through today (as opposed to just having rooms of collections).  It was a very Muslim-centric view of history, but I found the educational aspects fascinating.  The sign on the way in said no photography, but since the massive museum apparently only had 4 people working in it (all at the front desk), everyone ignored it.  I found a cube in the first hall that seemed Essbase-like.

My favorite exhibit was a scale model of Mecca and Medina as viewed at night.  Being non-Muslim, I will never see Mecca, so this is as close as I will ever get:
After the museum, our hosts took us to Kingdom Centre, the tallest building in Saudi Arabia (for now).  It has 30+ open stories at the top with a skybridge connecting them.  Here's a view from the ground of the building (notice the necklace like architecture with the bridge on top) and a view from the building of the ground:

Our hosts took us to dinner (which was unfortunately a meat-on-a-stick restaurant, unfortunately for me since I'm a vegetarian).  Since I wasn't able to eat much, I got to talk at length to the CEO of eSolutions.  He was a fascinating man who told me all about how Saudi Arabia and the Gulf Coast Community is ready for analytics and reporting.  He was a former Pakistani military man, but his love of country and family shined through in everything he said.

The following morning, we journeyed to Jeddah over on the Red Sea coast of Saudi near Mecca.  The weather was like Riyadh except with crazy humidity.  It reminded me of Houston on the hottest day of summer.  We were in Jeddah for less than 24 hours so I don't admittedly have a ton to say about it.  I saw nothing more than the airport, the drive to/from the hotel, the venue where we presented, and a fast casual restaurant (more tasty Lebanese food!) where we had dinner.

The venue was small since it was a half-day event and lightly attended.  I did ask if I could present on a different topic since I had gotten a bit bored talking Big Data all the time.  This time, I spoke on "In-Memory Databases" which is a hot topic these days thanks to the SAP guys saying "Hana" at least once every sentence.  After the morning event, the OTN MENA 5-1 (Bjoern went straight to Dubai since there weren't enough speaker slots in Jeddah) headed for the airport for the flight to the United Arab Emirates.  Getting out of Saudi took 2 minutes at Customs which goes to show, I guess, that they're a lot happier to get rid of you than let you in.

Dubai UAE, June 1I honestly don't know where to begin with Dubai.  It is truly one of the most amazing cities on Earth and almost indescribable to anyone who hasn't actually witnessed it.  My flight landed on Saturday evening in the largest airport I think I've ever seen.  Clearing customs took minutes then I headed for the cleanest (and probably newest since it just opened in 2009) train system in the world.  The view from the Dubai Metro was stunning.  If Riyadh looks like it was built in the last 10 years, Dubai looks like it was built in the last 10 minutes.  It reminds me of New York City if they took out all the advertising, 90% of the people, and any building under 1,000 feet tall.


I was lucky enough to stay at a hotel in the Burj Khalifa, the tallest building in the world (by far: the Burj is over 200 stories tall at over 2,500 feet).  The Burj sets all kinds of "tallest" records including the tallest outdoor observation deck.  Here's a view of several buildings below that are all over 1,000 feet tall (with an Edward in it for perspective).

My room had a view of the Dubai Fountains.  No offense, Vegas, but these fountains put the Bellagio fountains to shame. They're more agile, faster, better lit, larger, and frankly, classier. And if you're staying in the hotel in the Burj Khalifa, you can listen to the fountain music through the TV:


At the base of the Burj Khalifa is the Dubai Mall, the largest mall in the world (you hear a lot in Dubai of "that's the largest _____ in the world").  It's 3-4 times the size of Mall of America if that puts it in perspective.  It has over 1,200 shops, over 150 restaurants, and some of the strangest (yet up-scale) stores you've ever seen.  There was one selling full-size metal camels, for instance (these were the only camels I saw on my trip).  I ate at the Dubai Mall every day I was in Dubai and felt I could eat there every day for a year without repeating an entree.  I am normally not a fan of malls, but I loved the Dubai Mall.  It wasn't crowded and the people that were there kept to themselves.  This could actually be said about everywhere in the Middle East: the people are nice, but they keep to themselves.  For an introvert like me, it's heaven.  You can be alone in a crowd.


The first full day I was there, I presented in the morning, burned my feet on the sands of a beautiful beach at lunch, and was skiing in the afternoon.  Yes, skiing.  The Mall of the Emirates (another mall that dwarfs anything we have in the USA) is not only massive, it has its own indoor ski resort inside.  It's not a tiny ski hill either: it's 1,200 feet long with three separate runs (a blue/intermediate run, a green/beginner run, and a small terrain park).  They also have lots of other fun activities to do including a penguin exhibit, ziplining over the ski hill, sledding, and large transparent hamster balls to roll down the hill in.

For around $50 USD, you get a lift ticket, ski pants, ski jacket, boots, skis, and poles for 2 hours.  A full-day pass is only around $15 USD more but I opted for 2 hours.  (You can only ski indoors in Dubai so much, obviously.)  There were at most 15 people skiing, but there were hundreds of people from the Middle East paying their $50 to ride up and down the ski lift basking in the glory of being cold.  The whole place is chilled to around 23F and the snow is glorious: it's soft and velvety because they actually make it snow indoors every night (unlike the ice blowers we use for man-made "snow" in the USA).  I skied for my full 2 hours without a break and I loved that I was so cold by the end that I had to go back into the mall and get a soy hot chocolate at Starbucks.

I had too many amazing experience to recount (and this is already seeming a bit like an advertisement for Dubai) but the most fundamentally changing experience of my trip was a visit to a mosque in Dubai.  Hosted by an eloquent British Muslim woman in a black abbeyah, she spent an hour educating a group of Westerners all about Islam.  I was taken aback by how... peaceful their religion is.  She covered the 5 pillars of Islam in a way that made me understand 1,000,000,000+ Muslims far better than I ever have.  I think that if everyone in the Western world could attend that one hour I did, we would have a level of cultural understanding that would ease a ton of our current fears.  They let us take lots of pictures and even let us video them doing 5 minutes of prayers.  It was moving and if you are ever in Dubai, make sure you visit the Jumeirah Mosque during one of their visitation hours (it's free unlike most everything else in Dubai).

My final presentation was in Dubai and it was a bittersweet end to a whirlwind week.  Every single day was spent traveling, speaking, or both, so it was nice to finally have a break.  That said, I will consider the other members of the OTN MENA 5 to be friends for life and I miss them already.  As I finished my presentation on Big Data (for the third time on the trip), I looked out at the anxious faces in the audience and realized that I would miss the Muslim world far more than I ever expected to.  

If there's ever a 2nd OTN tour of the Middle East, Africa, or both, sign me up.  Until then, thank you for letting me be a part of the most inspirational, educational tour I've ever experienced.

Categories: BI & Warehousing

Fun with SQL - Silver Pockets Full

Chet Justice - Wed, 2014-06-04 17:13
Silver Pockets Full, send this message to your friends and in four days the money will surprise you. If you don't, well, a pox on your house. Or something like that. I didn't know what it was, I just saw this in my FB feed:



Back in November, I checked to see the frequency of having incremental numbers in the date, like 11/12/13 (my birthday) and 12/13/14 (kate's birthday). I don't want to hear how the rest of the world does their dates either, I know (I now write my dates like YYYY/MM/DD on everything, just so you know, that way I can sort it...or something).

Anyway, SQL to test out the claim of once every 823 years. Yay SQL.

OK, I'm not going to go into the steps necessary because I'm lazy (and I'm just lucky to be writing here), so here it is:
select *
from
(
select
to_char( d, 'yyyymm' ) year_month,
count( case
when to_char( d, 'fmDay' ) = 'Saturday' then 1
else null
end ) sats,
count( case
when to_char( d, 'fmDay' ) = 'Sunday' then 1
else null
end ) suns,
count( case
when to_char( d, 'fmDay' ) = 'Friday' then 1
else null
end ) fris
from
(
select to_date( 20131231, 'yyyymmdd' ) + rownum d
from dual
connect by level <= 50000
)
group by
to_char( d, 'yyyymm' )
)
where fris = 5
and sats = 5
and suns = 5
So over the next 50,000 days, this happens 138 times. I'm fairly certain that doesn't rise to the once every 823 years claim. But it's cool, maybe.
YEAR_MONTH       SATS       SUNS       FRIS
---------- ---------- ---------- ----------
201408 5 5 5
201505 5 5 5
201601 5 5 5
201607 5 5 5
201712 5 5 5
128 more occurrences...
214607 5 5 5
214712 5 5 5
214803 5 5 5
214908 5 5 5
215005 5 5 5

138 rows selected
I'm not the only dork that does this either, here's one in perl. I'm sure there are others, but again, I'm lazy.
Categories: BI & Warehousing

The Riley Family, Part III

Chet Justice - Thu, 2014-04-10 21:44


That's Mike and Lisa, hanging out at the hospital. Mike's in his awesome cookie monster pajamas and robe...must be nice, right? Oh wait, it's not. You probably remember why he's there, Stage 3 cancer. The joys.

In October, we helped to send the entire family to Game 5 of the World Series (Cards lost, thanks Red Sox for ruining their night).

In November I started a GoFundMe campaign, to date, with your help, we've raised $10,999. We've paid over 9 thousand dollars to the Riley family (another check to be cut shortly).

In December, Mike had surgery. Details can be found here. Shorter: things went fairly well, then they didn't. Mike spent 22 days in the hospital and lost 40 lbs. He missed Christmas and New Years at home with his family. But, as I've learned over the last 6 months, the Riley family really knows how to take things in stride.

About 6 weeks ago Mike started round 2 of chemo, he's halfway through that one now. He complains (daily, ugh) about numbness, dizziness, feeling cold (he lives in St. Louis, are you sure it's not the weather?), and priapism (that's a lie...I hope).

Mike being Mike though, barely a complaint (I'll let you figure out where I'm telling a lie).

Four weeks ago, a chilly (65) Saturday night, Mike and Lisa call. "Hey, I've got some news for you."

"Sweet," I think to myself. Gotta be good news.

"Lisa was just diagnosed with breast cancer."

WTF?

ARE YOU KIDDING ME? (Given Mike's gallows humor, it's possible).

"Nope. Stage 1. Surgery on April 2nd."

FFS

(Surgery was last week. It went well. No news on that front yet.)

Talking to them two of them that evening you would have no idea they BOTH have cancer. Actually, one of my favorite stories of the year...the hashtag for Riley Family campaign was #fmcuta. Fuck Mike's Cancer (up the ass). I thought that was hilarious, but I didn't think the Riley's would appreciate it. They did. They loved it. I still remember Lisa's laugh when I first suggested it. They've dropped the latest bad news and Lisa is like, "Oh, wait until you hear this. I have a hashtag for you."

"What is it?" (I'm thinking something very...conservative. Not sure why, I should know better by now).

#tna

I think about that for about .06 seconds. Holy shit! Did you just say tna? Like "tits and ass?"

(sounds of Lisa howling in the background).

Awesome. See what I mean? Handling it in stride.

"We're going to need a bigger boat." All I can think about now is, "what can we do now?"

First, I raised the campaign goal to 50k. This might be ambitious, that's OK, cancer treatments are expensive enough for one person, and 10K (the original amount) was on the low side. So...50K.

Second, Scott Spendolini created a very cool APEX app, ostensibly called the Riley Support Group (website? gah). It's a calendar/scheduling app that allows friends and family coordinate things like meals, young human (children) care and other things that most of us probably take for granted. Pretty cool stuff. For instance, Tim Gorman provides pizza on Monday nights (Dinner from pizza hut...1 - large hand-tossed cheese lovers, 1 - large thin-crispy pepperoni, 1 - 4xpepperoni rolls, 1 - cheesesticks).

Third. There is no third.

So many of you have donated your hard earned cash to the Riley family, they are incredibly humbled by, and grateful for, everyone's generosity. They aren't out of the woods yet. Donate more. Please. If you can't donate, see if there's something you can help out with (hit me up for details, Tim lives in CO, he's not really close). If you can't do either of those things, send them your prayers or your good thoughts. Any and all help will be greatly appreciated.
Categories: BI & Warehousing

What's New in Dodeca 6.7.1?

Tim Tow - Mon, 2014-03-24 11:28
Last week, we released Dodeca version 6.7.1.4340 which focuses on some new relational functionality. The major new features in 6.7.1 are:
  • Concurrent SQL Query Execution
  • Detailed SQL Timed Logging
  • Query and Display PDF format
  • Ability to Launch Local External Processes from Within Dodeca
Concurrent SQL Query ExecutionDodeca has a built-in SQLPassthroughDataSet object that supports queries to a relational database.  The SQLPassthroughDataSet functionality was engineered such that a SQLPassthroughDataSet object can include multiple queries that get executed and returned on a single trip to the server and several of our customers have taken great advantage of that functionality.  We have at least one customer, in fact, that has some SQLPassthroughDataSets that execute up to 20 queries in a single trip to the server.  The functionality was originally designed to run the queries sequentially, but in some cases it would be better to run the queries concurrently.  Because this is Dodeca, of course concurrent query execution is configurable at the SQLPassthroughDataSet level.

Detailed SQL Timed LoggingIn Dodeca version 6.0, we added detailed timed logging for Essbase transactions.  In this version, we have added similar functionality for SQL transactions and have formatted the logs in pipe-delimited format so they can easily be loaded into Excel or into a database for further analysis.  The columns of the log include the log message level, timestamp, sequential transaction number, number of active threads, transaction GUID, username, action, description, and time to execute in milliseconds.

Below is an example of the log message.

Click to enlarge

Query and Display PDF formatThe PDF View type now supports the ability to load the PDF directly from a relational table via a tokenized SQL statement.  This functionality will be very useful for those customers who have contextual information, such as invoice images, stored relationally and need a way to display that information.  We frequently see this requirement as the end result of a drill-through operation from either Essbase or relational reports.

Ability to Launch Local External Processes from Within DodecaCertain Dodeca customers store data files for non-financial systems in relational data stores and use Dodeca as a central access point.  The new ability to launch a local process from within Dodeca is implemented as a Dodeca Workbook Script method which provides a great deal of flexibility in how the process is launched.

The new 6.7.1 functionality follows closely on the 6.7.0 release that introduces proxy server support and new MSAD and LDAP authentication services.  If you are interested in seeing all of the changes in Dodeca, highly detailed Dodeca release notes are available on our website at http://www.appliedolap.com/resources/downloads/dodeca-technical-docs.

Categories: BI & Warehousing

Internal Links

Tim Dexter - Wed, 2014-03-05 20:06

Another great question today, this time, from friend and colleague, Jerry the master house re-fitter. I think we are competing on who can completely rip and replace their entire house in the shortest time on their own. Every conversation we have starts with 'so what are you working on?' He's in the midst of a kitchen re-fit, Im finishing off odds and ends before I re-build our stair well and start work on my hidden man cave under said stairs. Anyhoo, his question!

Can you create a PDF document that shows a summary on the first page and provides links to more detailed sections further down in the document?

Why yes you can Jerry. Something like this? Click on the department names in the first table and the return to top links in the detail sections. Pretty neat huh? Dynamic internal links based on the data, in this case the department names.

Its not that hard to do either. Here's the template, RTF only right now.


The important fields in this case are the ones in red, heres their contents.

TopLink

<fo:block id="doctop" />

Just think of it as an anchor to the top of the page called doctop

Back to Top

<fo:basic-link internal-destination="doctop" text-decoration="underline">Back to Top</fo:basic-link>

Just a live link 'Back to Top' if you will, that takes the user to the doc top location i.e. to the top of the page.

DeptLink

<fo:block id="{DEPARTMENT_NAME}"/>

Just like the TopLink above, this just creates an anchor in the document. The neat thing here is that we dynamically name it the actual value of the DEPARTMENT_NAME. Note that this link is inside the for-each:G_DEPT loop so the {DEPARTMENT_NAME} is evaluated each time the loop iterates. The curly braces force the engine to fetch the DEPARTMENT_NAME value before creating the anchor.

DEPARTMENT_NAME

<fo:basic-link  internal-destination="{DEPARTMENT_NAME}" ><?DEPARTMENT_NAME?></fo:basic-link>

This is the link for the user to be able to navigate to the detail for that department. It does not use a regular MSWord URL, we have to create a field in the template to hold the department name value and apply the link. Note, no text decoration this time i.e. no underline.

You can add a dynamic link on to anything in the summary section. You just need to remember to keep link 'names' as unique as needed for source and destination. You can combine multiple data values into the link name using the concat function.

Template and data available here. Tested with 10 and 11g, will work with all BIP flavors.

Categories: BI & Warehousing

The OLAP Extension is now available in SQL Developer 4.0

Keith Laker - Tue, 2014-03-04 14:57


The OLAP Extension is now in SQL Developer 4.0.

See
http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/sqldev-releasenotes-v4-1925251.html for the details.

The OLAP functionality is mentioned toward the bottom of the web page.
You will still need AWM 12.1.0.1.0 to
  • Manage and enable cube and dimension MV's.
  • Manage data security.
  • Create and edit nested measure folders (i.e. measure folders that are children of other measure folders)
  • Create and edit Maintenance Scripts
  • Manage multilingual support for OLAP Metadata objects
  • Use the OBIEE plugin or the Data Validation plugin
What is new or improved:
  • New Calculation Expression editor for calculated measures.  This allows the user to nest different types to calculated measures easily.  For instance a user can now create a Moving Total of a Prior Period as one calculated measure.  In AWM, it would have required a user to create a Prior Period first and then create a Moving Total calculated measure which referred to the Prior Period measure.  Also the new Calculation Expression editor displays hypertext helper templates when the user selects the OLAP API syntax in the editor.
  • Support for OLAP DML command execution in the SQL Worksheet.  Simply prefix OLAP DML commands by a '~' and then select the execute button to execute them on the SQL Worksheet.  The output of the command will appear in the DBMS Output Window if it is opened, or the Script Output Window if the user has executed 'set serveroutput on' before executing the DML command.
  • Improved OLAP DML Program Editor integrated within the SQL Developer framework.
  • New diagnostic reports in the SQL Developer Report navigator.
  • Ability to create a fact view with a measure dimension (i.e. "pivot cube").  This functionality is accessible from the SQL Developer Tools-OLAP menu option.
  • Cube scripts have been renamed to Build Specifications and are now accessible within the Create/Edit Cube dialog.  The Build Specifications editor there, is similar to the calculation expression editor as far as functionality.
Categories: BI & Warehousing

Waterfall Charts

Tim Dexter - Fri, 2014-02-28 19:35

Great question came through the ether from Holger on waterfall charts last night.

"I know that Answers supports waterfall charts and BI Publisher does not.
Do you have a different solution approach for waterfall charts with BI Publisher (perhaps stacked bars with white areas)?
Maybe you have already implemented something similar in the past and you can send me an example."

I didnt have one to hand, but I do now. Little known fact, the Publisher chart engine is based on the Oracle Reports chart engine. Therefore, this document came straight to mind. Its awesome for chart tips and tricks. Will you have to get your hands dirty in the chart code? Yep. Will you get the chart you want with a little effort? Yep. Now, I know, I know, in this day and age, you should get waterfalls with no effort but then you'd be bored right?

First things first, for the uninitiated, what is a waterfall chart? From some kind person at Wikipedia, "The waterfall chart is normally used for understanding how an initial value is affected by a series of intermediate positive or negative values. Usually the initial and the final values are represented by whole columns, while the intermediate values are denoted by floating columns. The columns are color-coded for distinguishing between positive and negative values."

We'll get back to that last sentence later, for now lets get the basic chart working.

Checking out the Oracle Report charting doc, search for 'floating' their term for 'waterfall' and it will get you to the section on building a 'floating column chart' or in more modern parlance, a waterfall chart. If you have already got your feet wet in the dark arts world of Publisher chart XML, get on with it and get your waterfall working.

If not, read on.

When I first starting looking at this chart, I decided to ignore the 'negative values' in the definition above. Being a glass half full kind of guy I dont see negatives right :)

Without them its a pretty simple job of rendering a stacked bar chart with 4 series for the colors. One for the starting value, one for the ending value, one for the diffs (steps) and one for the base values. The base values color could be set to white but that obscures any tick lines in the chart. Better to use the transparency option from the Oracle Reports doc.

<Series id="0" borderTransparent="true" transparent="true"/> 

Pretty simple, even the data structure is reasonably easy to get working. But, the negative values was nagging at me and Holger, who I pointed at the Oracle Reports doc had come back and could not get negative values to show correctly. So I took another look. What a pain in the butt!

In the chart above (thats my first BIP waterfall maybe the first ever BIP waterfall.) I have lime green, start and finish bars; red for negative and green for positive values. Look a little closer at the hidden bar values where we transition from red to green, ah man, royal pain in the butt! Not because of anything tough in the chart definition, thats pretty straightforward. I just need the following columns START, BASE, DOWN, UP and FINISH. 

START 200
BASE 0
UP 0
DOWN 0
FINISH 0
START 0
BASE 180
UP 0
DOWN 20
FINISH 0
START 0
BASE 150
UP 0
DOWN 30
FINISH 0
 Bar 1 - Start Value
 Bar 2 - PROD1
 Bar 3 - PROD2

and so on. The start, up, down and finish values are reasonably easy to get. The real trick is calculating that hidden BASE value correctly for that transition from -ve >> + ve and vice versa. Hitting Google, I found the key to that calculation in a great page on building a waterfall chart in Excel from the folks at Contextures.  Excel is great at referencing previous cell values to create complex calculations and I guess I could have fudged this article and used an Excel sheet as my data source. I could even have used an Excel template against my database table to create the data for the chart and fed the resulting Excel output back into the report as the data source for the chart. But, I digress, that would be tres cool thou, gotta look at that.
On that page is the formula to get the hidden base bar values and I adapted that into some sql to get the same result.

Lets assume I have the following data in a table:

PRODUCT_NAME SALES PROD1 -20 PROD2 -30 PROD3 50 PROD4 60

The sales values are versus the same period last year i.e. a delta value.  I have a starting value of 200 total sales, lets assume this is pulled from another table.
I have spent the majority of my time on generating the data, the actual chart definition is pretty straight forward. Getting that BASE value has been most tricksy!

I need to generate the following for each column:

PRODUCT_NAME

STRT

BASE_VAL

DOWN

UP

END_TOTAL

START
200
0
0
0
0
PROD1
0
180
20
0
0
PROD2
0
150 30 0
0
PROD3
0 150 0 50 0 PROD4
0 200
0 60 0 END
0 0 0 0 260

Ignoring the START and END values for a second. Here's the query for the PRODx columns:

 SELECT 2 SORT_KEY 
, PRODUCT_NAME
, STRT
, SALES
, UP
, DOWN
, 0 END_TOTAL
, 200 + (SUM(LAG_UP - DOWN) OVER (ORDER BY PRODUCT_NAME)) AS BASE_VAL
FROM
(SELECT P.PRODUCT_NAME
,  0 AS STRT
, P.SALES
, CASE WHEN P.SALES > 0 THEN P.SALES ELSE 0 END AS UP  
, CASE WHEN P.SALES < 0 THEN ABS(P.SALES) ELSE 0 END AS DOWN
, LAG(CASE WHEN P.SALES > 0 THEN P.SALES ELSE 0 END,1,0) 
      OVER (ORDER BY P.PRODUCT_NAME) AS LAG_UP
FROM PRODUCTS P
)

The inner query is breaking the UP and DOWN values into their own columns based on the SALES value. The LAG function is the cool bit to fetch the UP value in the previous row. That column is the key to getting the BASE values correctly.

The outer query just has a calculation for the BASE_VAL.

200 + (SUM(LAG_UP - DOWN) OVER (ORDER BY PRODUCT_NAME))

The SUM..OVER allows me to iterate over the rows to get the calculation I need ie starting value (200) + the running sum of LAG_UP - DOWN. Remember the LAG_UP value is fetching the value from the previous row.
Is there a neater way to do this? Im most sure there is, I could probably eliminate the inner query with a little effort but for the purposes of this post, its quite handy to be able to break things down.

For the start and end values I used more queries and then just UNIONed the three together. Once note on that union; the sorting. For the chart to work, I need START, PRODx, FINISH, in that order. The easiest way to get that was to add a SORT_KEY value to each query and then sort by it. So my total query for the chart was:

SELECT 1 SORT_KEY
, 'START' PRODUCT_NAME
, 200 STRT
, 0 SALES
, 0 UP
, 0 DOWN
, 0 END_TOTAL
, 0 BASE_VAL
FROM PRODUCTS
UNION
SELECT 2 SORT_KEY 
, PRODUCT_NAME
, STRT
, SALES
, UP
, DOWN
, 0 END_TOTAL
, 200 + (SUM(LAG_UP - DOWN) 
      OVER (ORDER BY PRODUCT_NAME)) AS BASE_VAL
FROM
(SELECT P.PRODUCT_NAME
,  0 AS STRT
, P.SALES
, CASE WHEN P.SALES > 0 THEN P.SALES ELSE 0 END AS UP  
, CASE WHEN P.SALES < 0 THEN ABS(P.SALES) ELSE 0 END AS DOWN
, LAG(CASE WHEN P.SALES > 0 THEN P.SALES ELSE 0 END,1,0) 
       OVER (ORDER BY P.PRODUCT_NAME) AS LAG_UP
FROM PRODUCTS P
)
UNION
SELECT 3 SORT_KEY 
, 'END' PRODUCT_NAME
, 0 STRT
, 0 SALES
, 0 UP
, 0 DOWN
, SUM(SALES) + 200 END_TOTAL
, 0 BASE_VAL
FROM PRODUCTS
GROUP BY 1,2,3,4,6
ORDER BY 1 

A lot of effort for a dinky chart but now its done once, doing it again will be easier. Of course no one will want just a single chart in their report, there will be other data, tables, charts, etc. I think if I was doing this in anger I would just break out this query as a separate item in the data model ie a query just for the chart. It will make life much simpler.
Another option that I considered was to build a sub template in XSL to generate the XML tree to support the chart and assign that to a variable. Im sure it can be done with a little effort, I'll save it for another time.

On the last leg, we have the data; now to build the chart. This is actually the easy bit. Sadly I have found an issue in the online template builder that precludes using the chart builder in those templates. However, RTF templates to the rescue!

Insert a chart and in the dialog set up the data like this (click the image to see it full scale.)

Its just a vertical stacked bar with the BASE_VAL color set to white.You can still see the 'hidden' bars and they are over writing the tick lines but if you are happy with it, leave it as is. You can double click the chart and the dialog box can read it no problem. If however, you want those 'hidden' bars truly hidden then click on the Advanced tab of the chart dialog and replace:

<Series id="1" color="#FFFFFF" />

with

<Series id="1" borderTransparent="true" transparent="true" />

and the bars will become completely transparent. You can do the #D and gradient thang if you want and play with colors and themes. You'll then be done with your waterfall masterpiece!

Alot of work? Not really, more than out of the box for sure but hopefully, I have given you enough to decipher the data needs and how to do it at least with an Oracle db. If you need all my files, including table definition, sample XML, BIP DM, Report and templates, you can get them here.

Categories: BI & Warehousing

Wildcard Filtering continued

Tim Dexter - Mon, 2014-02-24 13:27

I wrote up a method for using wildcard filtering in your layouts a while back here. I spotted a followup question on that blog post last week and thought I would try and address it using another wildcard method. 

I want to use the bi publisher to look for several conditions using a wild card. For example if I was sql it would look like this:

if name in ('%Wst','%Grt')

How can I utilize bi publisher to look for the same conditions.

This, in XPATH speak is an OR condition and we can treat it as such. In the last article I used the 'starts-with' function, its a little limiting, there is a better one, 'contains'. This is a much more powerful function that allows you to look for any string within another string. Its case insensitive so you do not need to do upper or lowering of the string you are searching to get the desired results.
Here it is in action:

For the clerks filter I use :

<?for-each-group:G_1[contains(JOB_TITLE,'Clerk')];./JOB_TITLE?>

and to find all clerks and managers, I use:

<?for-each-group:G_1[contains(JOB_TITLE,'Clerk') or contains(JOB_TITLE,'Manager')];./JOB_TITLE?>

Note that Im using re-grouping here, you can use the same XPATH with a regular for-each. Also note the lower case 'or' in the second expression. You can also use an 'and' too.

This works in 10 and 11g flavors of BIP. Sample files available here.

Categories: BI & Warehousing

Filtered Charts

Tim Dexter - Tue, 2014-02-11 17:19

A customer question this week regarding filtering a chart. They have a report with a bunch of criteria with monetary values but, rather than show all of the criteria in a pie chart, they just want to show a few. For example:

 This ...
 rather than this

 There are a couple of ways to tackle this:

1. Filter the chart data in the chart definition. Using an XPATH expression you can filter out all of the criteria you do not want to see. Open the chart definition and update the definition. You will need to update the RowCount, RowLabels and DataValues attributes in the chart definition. Adding in the following XPATH expression:

    [DEPARTMENT_NAME='Accounting' or DEPARTMENT_NAME='Marketing' or DEPARTMENT_NAME='Executive']

so the DataValues value becomes:

    <DataValues><xsl:for-each-group select=".//G_1[DEPARTMENT_NAME='Accounting' or 
                                                     DEPARTMENT_NAME='Marketing' or 
                                                        DEPARTMENT_NAME='Executive']" ...

2. Create a variable in the template to hold just the values you want to chart.

    <?variable: filterDepts; /DATA_DS/LIST_G_1/G_1[DEPARTMENT_NAME='Accounting' or 
                                                     DEPARTMENT_NAME='Marketing' or 
                                                       DEPARTMENT_NAME='Executive']?>

Then update the chart definition with the variable for the same three attributes above, the RowCount, RowLabels and DataValues. For example:

    <DataValues><xsl:for-each-group select="$filterDepts" ...

These both work admirably, but they both require some manual updating of the chart definition which can get fiddly and a pain to maintain. I'm also just filtering for three departments, when you get up to 5 or 6 then the XPATH starts to become a pain to maintain. Option 2 alleviates this somewhat because you only need to define the filter once to create the filtered variable.
A better option may be ...

3. Force the effort down into the data layer. Create another query in the report that just pulls the data for the chart.

LIST_G2/G_2 holds the data for the chart. Then all you need do is create a vanilla chart on that particular section of the data.

Yes, there is some overhead to re-fetch the data but this is going to be about the same if not less than the extra processing required in the template with options 1 and 2. This has another advantage, you can parametrize the criteria for the user. You can create a parameter to allow the user to select, in my case, the department(s) they want to chart.


Its simple enough to create the multi-select parameter and modify the query to filter based on the values chosen by the user.

Sample report (including data model and layout template here) just un-archive into your catalog.
RTF Template plus sample data available here.




Categories: BI & Warehousing

Alternate Tray Printing

Tim Dexter - Mon, 2014-02-10 17:18

Since we introduced support for check printing PCL escape sequences in 11.1.1.7 i.e. being able to set the micr font or change the print cartridge to the magnetic ink for that string. I have wanted to test out other PCL commands, particularly, changing print trays. Say you have letter headed paper or pre-printed or colored paper in tray 2 but only want to use it for the first page or specific or for a separator page, the rest can come out of plain ol Tray 1 with its copier paper.

I have had a couple of inquiries recently and so, I finally took some time to test out the theory. I should add here, that the dev team thought it would work but were not 100%. The feature was built for the check printing requirements alone so they could not support any other commands. I was hopeful thou!
In short, it works!



I can generate a document and print it with embedded PCL commands to change from Tray 1 (&l4H) to Tray 2 (&l1H ) - yep, makes no sense to me either. I got the codes from here, useful site with a host of other possibilities to test.

For the test, I just created a department-employee listing that broke the page when the department changed. Just inside the first grouping loop I included the PCL string to set Tray 1.

<pcl><control><esc/>&l4H </control> </pcl>

Note, this has to be in clear text, you can not use a formfield.
I then created a dummy insert page using a template and called it from just within the closing department group field (InsertPAGE field.) At the beginning of the dummy page I included the PCL string to get the paper from Tray 2:

<pcl><control><esc/>&l1H</control> </pcl>

When you run this to PDF you will see the PCL string. I played with this and hid it using a white font and it worked great, assuming you have white paper :)

When you set up the printer in the BIP admin console, you need to ensure you have picked the 'PDF to PCL Filter' for the printer.



If you dont want to have PCL enabled all the time, you can have multiple definitions for the same printer with/with out the PCL filter. Users just need to pick the appropriate printer instance. Using this filter ensures that those PCL strings will be preserved into the final PCL that gets sent to the printer.

Example files here. Official documentation on the PCL string here.

Happy Printing!





Categories: BI & Warehousing

Memory Guard

Tim Dexter - Mon, 2014-02-03 12:38

Happy New ... err .. Chinese Year! Yeah, its been a while, its also been danged busy and we're only in February, just! A question came up on one of our internal mailing lists concerning out of memory errors. Pieter, support guru extraordinaire jumped on it with reference to a support note covering the relatively new 'BI Publisher Memory Guard'. Sounds grand eh?

As many a BIP user knows, at BIP's heart lives an XSLT engine. XSLT engines are notoriously memory hungry. Oracle's wee beastie has come a long way in terms of taming its appetite for bits and bytes since we started using it. BIP allows you to take advantage of this 'scalable mode.' Its a check box on the data model which essentially says 'XSLT engine, stop stuffing your face with memory doughnuts and get on with the salad and chicken train for this job' i.e. it gets a limited memory stack within which to work and makes use of disk, if needed, think Windows' 'virtual memory'.

Now that switch is all well and good, for a known big report that you would typically mark as 'schedule only.' You do not want users sitting in front of their screen waiting for a 10,000 page document to appear, right? How about those reports that are borderline 'big' or you have a potentially big report but expect users to filter the heck out of it and they choose not to? It would be nice to be able to set some limits on reports in case a user kicks off a monster donut binge session. Enter 'BI Publisher Memory Guard'!

It essentially lets you set those limits on memory and report size so that users can not run a report that brings the server to its knees. More information on the support web site, search for 'BI Publisher Memory Guard a New Feature to Prevent out-of-memory Errors (Doc ID 1599935.1)' or you can get Leslie's white paper covering the same here.

Categories: 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

Pages

Subscribe to Oracle FAQ aggregator - BI &amp; Warehousing