Cubegeek

A Clever Excel Drill Trick

My colleague SC came up with a neat little trick that enables a rabbit hole of functionality. Imagine that your customers want to drill through in Excel a second time (Use a fake French accent and shout it as if you were guarding a castle and throwing cows at a troop of Englishmen). You can embed a URL in a field retrieved from your SQL database and have Excel interpret that so users can click on that cell and go somewhere else.

The trick is to get Excel to recognize the field as a link. Embed the formula in your SQL '=hyperlink("Url","Label")' and construct the parameters as you like. Sweet. 

Essbase Studio Foibles

A few  interesting notes about Essbase Studio v11.1.1.3.

MAXL Deploy Documentation
It's wrong. There is an important clause that is left out of the railroad diagrams.  The proper syntax for an auto deployment goes a little something like this, the bolded part is left out of the 11.1.1.3 documentation:

deploy outline from model 'Standard9Model' with option
incremental_load update all
preserve no data
login 'admin' identified by 'password' on host 'essServer'
to application 'Demo' database 'DB' using connection 'EssDev'
keep 500 errors
on error write 'd_S9.err'; 

 

Messy Messages
There's another interesting bug in the product. It's hard to tell if it's Smartview or Smartview with Studio. Either way, it's very deceptive. The message reads 'Internal Provider Server Error' or 'cannot perform cube view operation. null'. If you get either of these that's Smartview's idiot way of saying "Please turn of suppress missing, I can't see anything." I know. That one had me scratching my head for a while after I finally got the automation for deployment working.

Last but not least, there's a little piece of documentation in Essbase studio that says MaxL formulas imported into the cube cannot exceed 255 characters. Your common sense would say to ignore that piece of documentation and your common sense would be right, even though it raises your hinky level. I have imported bigger formulas successfully. We'll see if Studio intervenes somehow after the cube is built to say no go. I've seen crazier.

Next time I'll attempt to explain how one can do some trickery to avoid Studio's requirement of using Generational hierarchies instead of parent child hierarchies when you want to do dirll through reports. 

I Heart Vihart

I don't generally give hat tips because of Cobb's Rule #13, but today I'm giving one to my daughter for finding something she rightly know I would find marvelously entertaining.  It's the Vihart blog, made up of YouTubery. 

The Quadrotor

The quadrotor is one of those ideas that  just sounds obvious now that we've seen it. I've always been a fan of the V22 Osprey and I cannot imagine why helicopter design is still basically stuck in the same configuration as it was 60 years ago, especially given the number of fatal accidents military transports have. 

The OG Yahoo Archive

Once upon a time, I was known to the world (that cared) as 'olap genius'. On Yahoo, a forum was maintained in which I engaged in outsized bloviation and championship (as opposed to FUD) about the Arbor/Hyperion products, industry and technology. I've read about five or six entries of this bloviation and I must say that I'm rather shocked. I now see quite clearly why people called me arrogant. But, I did know what I was talking about for the most part, and it was very reasonable for my old boss Dan Druker to have me turn off the spigot about the new stuff we were doing in eCRM at Hyperion. 

I see now in retrospect how well I should have fit into what was then the burgeoning open source movement. As I have read the book, it's clear how my communication style was influenced by the spirit of the times - this is just the kind of fractious and fanatic attention to detail and passion that generates top quality stuff from Type A personalities. This was precisely the kind of spirit that energizes software technology on the cutting edge, which was where we were during the OLAP wars. I don't miss, nor do I want to romanticize those days. That was then. But I did want to put all of this stuff out there on the record. I mean, I said it in public then, there's no reason for me to not republish it now. That said, I haven't really reviewed it and I have no idea how embarassing it might be to me now.

2600 Dash 1

At around 7pm I arrived at Phillipes in downtown LA, right on time. I began to search the area for people who might be part of the illusive and storied membership of 2600. I made certain assumptions about what the arriving group might look like. The lines were long so I had time to speculate for 30 minutes before the counterman took my quick order, lamb, slaw, lemonade. I had a ten dollar bill and it was a cash only counter. I wanted cheese on my sandwich but I couldn't afford it without going first to the ATM for more ducats. I didn't dare to leave the queue and wolfed down what I could afford only having identified one sure suspect. As soon as I saw him, I knew. He looked familiar and the only name that jumped into mind was RU Sirius, but he was too young to be him or to be Bruce Sterling. But he had the classic hacker look, a studied monastic perfection coupled with bohemian flair. Black circular glasses, dark clothing with many pockets, long hair pulled off the face, attentive posture and that combination of nonchalance and awareness of a mind idling and musing in casual surroundings that at can any moment be brought to laserlike focus on a subject of interest. He was my white rabbit.

It turned out that we never spoke through the evening although we exchanged glances and I could tell that at least one time he managed to hear and get a joke of mine. It reminded me, this kind of nervously energetic conversation which proceded through the evening in the company of the two dozen, of the way I spoke in highschool. Excited and not excited about complex arcanities, and drawing divergent kinds of thinking and ribald humor into conversations that studiously avoid the pedantic. You want to be a know-it-all, entertain people into your sphere, but not offputting without a smile. It's the way geeks talk, depending on their attitude. Tonight there were multiple flavors of geeks. It was a stew I didn't expect.

So the evening began after my meal, in the back room of Phillipes at a long table. The man to my left was Jose. The man to my right was Orange. I introduced myself as Michael to several whom I still don't properly know but recall as Checker, Orb, Orange, Middle, Santa, the previously known Rabbit, The Adjunct, and two or three others.

Jose was visiting from down south where he is involved in a project involving microcontrollers. He had a very dense understanding and immediately reminded me of the sort of programmer's dyslexia which is axiomatic of a chaotically creative mind. When you are full of *do*, and this is especially notable in young people who are not yet responsible for communicating the import and details of their work, words collide in the output queue. It's an interface problem that it took me many years to build when it came to my technical passions. I had but one question which after a few minutes of exchange between Jose and Orange seemed ever more trivial. I picked up one key term which I have only seen written down the past few days. Arduino. Jose might be interested in checking out my old associate at Henry atRacemanager as regards the finer points of telemetrics and capturing sensing data. 

Orange was the first person I asked, along with his disappearing friend who was billing an hour of consulting on his Trio tethered ThinkPad, if I might find Emmanuel Goldstein around here. And he was also one of the first to greet me on the 2.6K IRC and whose profile I was able to find relatively easily. If Rabbit gave the visual clue, Orange was the one that immediately made me feel welcome, now in two dimensions of presence.  Although it was probably more brownish, Orange wore that color sweater. He sported a fine watch and had an excellent leather satchel out of which he periodically presented a wonderous assortment of gadgets and tools, including an iPad and a set of Wiha precision screwdrivers. I also noticed via a quick glance that he had three identical pens at the ready. 

I described to him at perhaps a boring length, during which he kindly provided the iPad as a whiteboard, the architecture of data warehousing and the prospects held by new technologies like Hadoop, columnar databases, new open source products  and new devops methods. He in turn described some of his successes in the thin client map business. It might not have been an exciting discussion but it does provide grist for the mills of us geeks. So I expect that there is much for me to learn by my new associations at 2600.

Orb reminded me of an old friend of mine who was a huge fan of New Order, The KLF and of course, The Orb. He could have been his big brother. My old friend was a geek of the first order whose company I often abandoned for the sake of partying with people I thought I'd rather be around. He was an excellent keyboardist and shamed me in many ways with skill carved out of time I would have spent in the streets pursuing lighthearted adventure. Although I wasn't, being around him always made me feel like a spoiled, rich playboy. I didn't get a chance to speak much with Orb, and only heard him sideways, but I did spend a lot of time with Middle who was in awe of what Orb knew. When we got to Denny's later on in the evening, it seemed that Orb was going to speak and I would get a chance to hear what's new, but there were no speeches at Denny's. But I know to listen next time.

Checker was the boisterous kid who thought up the idea of hacking pixels for low level video drivers. If you were the user of such a driver and you looked close enough to their screens or have Superman's vision you would see that your graphics consisted of interlaced pornographic icons in place of what you would expect a normal pixel to be. Simply imagine at the lowest level of resolution that a single pixel looks something like a plus sign. Instead of a plus sign, it could be an inverted T that without much imagination one coulds see (ahem). Instead of pixels, dixels. Checker brought his iPad and regaled us with many videos, including some machinima staring Transformers. I didn't quite catch the orginal joke, but I was a little shy all evening.

Hanging out with The Adjunct allowed me to speak a little about my purpose for attending 2600 meeting. The Adjunct had been around for over 7 years, but had missed a few meetings recently. I was informed about whether or not this was a decent turnout or relatively small. (It was decent, but there were larger), and learned some of the more senior members. The Adjunct was the second member to smoke Camels. Apparently, the world is not dominated by Marlboro Lights.

Most of the evening, however, I spent talking with Middle, the middle child of seven children who turns out to be a literate scholar of Faulkner. Middle was there to figure out what other machine he should buy, not that his Macbook had been stolen and needed a replacement. I suggested an iPad. I was more fascinated, however, with his trips to Cuba and his impression of the people and system there. We both sought to avoid a political discussion and it took a bit for me not to be philosophically judgmental, but that's how I think. We talked a bit about writing and I'm hoping that he starts a blog once he gets his new machine. I definitely want to send him some traffic. Next month I hope to see him again and maybe I can even talk him over into a philosophy meetup.

At Denny's we hung out and ate and smoked and snarfed some free *old* books that were brought over by one of the senior members by the box load. I got a copy of a speaker building manual from Radio Shack. It happened to have a receipt in it from a pizza joint in Fullerton that was dated 1999. It turned out that the joint still had the same phone number. Thanks Google.

That's about it. Can't wait until the next meetup. I'll unleash a little more personality, and maybe I'll figure out Santa's story.

Cringely Predicts Apple Will Buy...

I forgot Cringely, and I've been listening too much to Dvorak. Dvorak and Adam Curry got on my nerves after a while, and the best thing about Cranky Geeks was Enderle and Dibona. I can still read Enderle but I haven't bumped into Dibona anywhere else.

So Cringely has a set of predictions which hinge around a central, and probably inevitable technical problem that affects the entirety of the web, which he calls bufferbloat. Simply stated, bufferbloat is a to the internet what too many ninja loans were to the financial system. Or actually the analogy is flawed, but the context is properly systemic. Bufferbloat is the ability for fast clients to suck up data so quickly that it makes the senders of data believe that there are no problems. In otherwords, client buffers are too quick to fail. Since TCP/IP expects failure and hears that there is no failure, the protocol forces data senders to exhale all of their bits at high rates of speed without stopping to listen to see that everything is OK in case a packet needs retransmission. So everything is going great until a burst makes everything go horrible. The protocol is out of touch with reality because all these buffers are faking good conditions. So TCP/IP is inefficient for the size and speed of memory buffers at the last mile.

Since Cringely predicts that your average ISPs and cable companies aren't going to to jack about it, (and whatever happened to IPV6?), that leaves one man on the planet to come up with an end to end solution. Steve Jobs, of course.

What is he to do?

Let’s guess that Apple will use that new data center to serve audio and video streams carrying music and TV and movies maybe for a flat subscription fee.  I predict there will be price tiers based on resolution and some content and/or earlier access will go for higher prices.  Apple trailers, for example, come in 480p, 720p, and 1080p, so those tiers become no-brainers ($59.95, $69.95, $79.95 per month possibly — remember this is Apple). And 1080p-60/7.1? That’s $99.95.

And how does Steve introduce this service?  I think he takes some of that $50 billion in cash, buys an existing cable TV company and essentially shuts it down.  He only has to buy one to make the splash he likes — a splash that is also a kick in the face to Google with its metro gigabit networks.  Once the people of North Carolina get the real Apple TV, how long before other cable subscribers simply give up their TV services, forcing their cable companies to become pure bit-schlepping ISPs for Apple?

Apple’s data center is in Maiden, NC, which is served by Comcast. No luck there.  But it isn’t far from Time Warner, which I believe Steve would see as an acceptable purchase costing around $30 billion.

I like it.  I really like it.

This Week's Geekery

It may turn out that the way I geek out may be blogable, but it will require some changes of habit. I'm on my way to those changes. 

The past couple months have been quite a revolution in my own thinking, and in many ways, I haven't been so excited about my profession in a very long time. The essential deal is that I'm finally moving towards what I should have been moving towards - virtualization, cloud services, elastic BI and all that crazy infrastructure. Now that I have begun spending a lot more time in Ubuntu and off traditional Windows, it gives me a lot more opportunity to break navigation habits. Plus, I'm exposing myself to things and people that I ordinarily would not, and that's all good.

What I'm likely to do is come across a number of new technologies and packages and whatnot and just catalog it here. The vistas are very large and that may be all the time I have to do it without looking ridiculous. It's refreshing to poke my head in places where I'm a complete newb... So anyway, here goes.

First. 
Apatar / Talend / PDI
These are the three open source ETL tools that I'm likely to be playing with. Apatar seems the simplest and most straight forward. Talend, I haven't gotten open yet, although I'm sure to and the PDI has so many bells and whistles it's hard to know where to start. Chances are, however, that I'm going to have to find some very specific reasons to use them, depending on a couple things.

1. I have yet to build a datamart out of ERP that sent data into an EPM system that was too big for MSSQL and DTS to handle. That may change, but as facile as I am with T/SQL (and PL/SQL) I'm really thinking long and hard about the test cases I will require.

2. The chances are good that I will become familiar with Amazon's Virtual Private Cloud, and that essentially means that (maybe) wherever I am, I can spin up a server or three which is instantly ready to handle my ETL requirements. As fast as I can push data through a pipe, I can send it to an AMI with the ETL of my choice and not neet to install either of the tools. So while it's clear that ETL becomes even more important in cloudy ways, what is in your toolkit is even less dependent on customer requirements. 

3. Essbase Studio is much improved from the EIS of old, and it actually seems to perform in the ways that SQL heads prefer. I can remember their zero tolerance for whatever kind of pig EIS used to be, so that's a wonderful thing. Less need, I percieve, for ETL packages to take a central role. 

Its still a bit weird to take all this data at arms' length through all the interfaces now available - but it the package is reliable and performs well, why not abstract? As we used to say of disk being cheap, now environment is cheap.

--

Hackerspaces

There are six hackerspaces in LA and Orange County. Continuing my efforts to make acquaintances in the Open Source world, this is my next step.  So I first found some guys at Nullspace Labs via IRC, and that's quite a wakeup call. I haven't used IRC in a decade, and forgot what kind of heads are hanging out here. There still isn't much going on in the EPM arena, but in the noSQL area there are plenty. It's just a matter of bending tech to see where the markets overlap technically, and technically is what I care about these days. So I'm going to be able to work with folks who hack their way through everything, and I'm attracted to the physical hacking as well. This is part and parcel of my getting to understand the new levels of security that will become increasingly important. 

--

Speaking of security, the last time I started thinking about it, I kept wondering why it wasn't taken quite so seriously in the BI world. Back in 2001 I had only two customers who bothered to get to what I believe was called C1 level security by the DOD's red book or blue book, or whatever that was. That was Boeing Huntington Beach, and what they did was simply anonymize the Essbase server name, cube names and user names. So one couldn't casually know what was going on when ZLKN00, sent a request to ONDK23 on server UFNS39, although I imagine a clever person could suss out the port mapping. 

--

Anyway, I got XChat running on my Ubuntu plus Colloquy on my Mac and iPhone. Really good resources out there. Completely forgot about IRC, I did. It remains superior to Twitter in form and content. I'll try to keep that in mind again.

--

Tor

Thinking about Tor. My first guess is that I don't need it, of course, but I'm trying to learn everything I don't know, which includes a lot. So I'll try that out.

I also crashed my Iomega again, but am very well Backblazed. I'm also going Western Digital and considering the flaws of Dell hardware.  It turns out anyway that I'm going to make my compute hardware light on the fat client side, have all my data duped in the cloud, and use more of AWS on demand. Which means at long last, I'm going to have the server power to keep all of my accumulated Master Data up and spinning. 

I'm sure I have several scores of dimensions I've keep in outlines over the years. As soon as I get my Essbase AMI up again, I'll reverse them down into denormalized tables and then peruse the library. That's where the ETL tools will come in handy - to have redundancy in multiple formats. JSON, xml and the xml flavors, plus simple old Access, which I'm finding is a really good ubiquitous format.  But we'll see more about that later. 

--

There's also something cool called Bitcoin I'm going to check out, sucker that I am. Yeah I contributed to SETI online back in the day. Why not get some bitcoin? 

--

Did a bit of flexing the old infrastructure muscles. I did pretty much a full install of EPM 11.1.1.3 on my home server in half a day. Couldn't get EAS working but did get MSSQL 2K8 Express, Oracle 10g, Essbase Studio, Foundation, Shared Services up. I had to dump some old oracle homes which never seem to die as services, and I've got some legacy 9.3.1 stuff lying around that complicated things, but it was nice to at least move stuff from place to place. Interesting how the MSSQL data wizard makes it easy to move stuff into Oracle, but I know I'm over-relying on that tool. But it does work...

--

The Python's coming along slowly, I am on vacation...