Cubegeek

Gap Cubes

Today we looked at one of the functional groups that was responsible for the SG&A for our client. I came up with an interesting problem.

Our sponsor said that a critical cuccess factor for the project was our ability to get people to actually use the system. In my experience, it is necessary to get the end users buy-in in terms of the workflow for which they are responsible.

One of the reasons that SAP and other ERP systems are successful is because they materially impact the daily work of the people involved. No matter how rarely a new item is created in the accounting system, even if there is one person involved in entering a number, there will be some user-facing screen that the employee will be forced to use. Therefore their paradigm shifts to 'SAP' even though they have no concept of which module they have in the system or what its capabilities are. They simply know that the numbers of record are 'in SAP'.

With most BI projects, our aim is to liberate the user from the ERP data jail. However it is th limited scope that is given to such projects that limit their success. It is not so often a question of the capability of the toolset, rather with the scope of the build that determines whether or not a particular BI or OLAP project will be a success. All too often, implementors associate bigger with better. I say this is wrong - where a tool like Essbase works best is with the small and detailed.

What we are seeing in our project is a great deal of the system being refered to as 'Hyperion'. This is certainly a success in marketing by the company. That is becuase it is now accepted that the corporation can trust their data to 'Hyperion'. So Hyperion has become the defacto reporting system of record.

The issue arises over the question of usability. If we are to fulfill part of our critical success factor of 'getting people to use the new system', then we have to overcome the reluctance of people to getting off of Excel.

In many ways this is a great opportunity, because Hyperion is widely acknowledged as the best integrator to Excel that there is, bar none. And that bar has actually been raised with the introduction of SmartView. However, there is still some resistance to the idea of getting data 'into Hyperion' when it comes to the detailed data that supports the planning process. We need to avoid the complication that arises in thinking about the performance of the overall planning application that is automatically associated with the detailed data used. And the only way to do that is to kill the idea that there will be one cube that can and will be all things to all people.

What I am proposing is to do some gap analysis between the usability of reports created using a combination of Excel and downloaded SAP data and the creation of Essbase cubes which would obviate that process. There will be several drivers in this.

Complexity of Spreadsheets

Need for Standardization

Need for What-If

Frequency of Update & Reporting

Size of Audience

Master Data Availability

The greater the need on those six criteria, the more it makes sense to formalize this adhoc process of using Excel and SAP downloads to facilitate the budgeting and planning process. It is at this level of 'small' that Essbase can really prove its value. These will be 'gap cubes' which allow for fully functional OLAP at some intersection of departments and sections of the COA.

One example might be, CAPEX planning within SG&A complete with all capital projects in the cube. We could provide calculations for depreciation on a monthly basis across all different depreciation schedules including what-if analysis which allows one to change the service life. We could account for new projects and end-of-life on a rolling basis so that reserve levels could be more accurately forecast in the middle of a year, or month by month.

This can be done for fringe benefits against headcounts, or earned value or any segment of planning specialization that financial analysts get into on a periodic basis.

These 'gap cubes' will be part of the systems design as we proceed, but only some closer evaluation will determine if they should be in or out of scope for the implementation phase. Needless to say a one-time effort to integrate such cubes into the standard flow of data in and out of SAP is required. However, there is a great opportunity here for variance analysis which may not be available at a detailed level. This, in my opinion, is where the great value add is possible.

Right now I percieve that 'nobody cares' about variance analysis for Capex Planning, because the current system makes it so difficult to provide. As well, we understand that there are different flows of data into these analyses that happen at different times. These matters can be reconciled with a 'gap' cube, as well, historical data can be maintained. The results will be a stable and clear picture at an analysts-eye view of the corporate business and well documented detail that integrates seamlessly into the annual budget cycles as well as periodic forecasts and variance reports.

Small is beautiful.

An Offshoring Story

The politics of offshoring are interesting in my line of work. I haven't had to deal with them for a while, but now that I'm out of the midmarket, they are popping up again. Daniel Drezner raises the topic and I'd like to offer a bit of personal experience that I'm dealing with now.

I'm working at a big company that like many other companies, has in place a procurement process for IT that involves outsourcing and offshoring. Basically, they bought a huge package of services from IBM and IBM gets to decide if it's worth it for their consultants and staff to do the work or if they'd rather not bid, and therefore offshore the less profitable tasks. Essentially, everyone who builds new applications under this agreement is a subcontractor to IBM who is obligated to consider offshore bidders.

When it comes to complicated systems of the type I design and build, it is generally accepted that you need local expertise. People who know what they are doing have to be the ones to draw up the project plans and oversee design. Ever mindful of the bottom line, it has been decided that 'Phase Zero' of this project will be a thoroughgoing exercise in requirements gathering and systems design. The design team will create a 'Bible' whose implementation will be left to bidders.

It would be injudicious for me to say this is absurd, because although it is, it just might work. We are bargaining on a theory that is very much like the ideas which made mass production possible in the early days, which is that there will be interchangeable parts.

As scary as the idea is of interchangeability of labor, it is a possibility that has fairly profound implications.

However software design is nothing like manufacturing, and our desire for software products is infinitely more complex and customized than our demand for other engineered products. Writing code is writing, and never in the history of mankind has writing been successfully split up among dozens of people distributed around the planet to create the best product. So while the absurd notion continues to be funded, we will in the short term, suffer from implementations which will have all the hallmarks of products designed by committee.

What will be lost inevitably, will be the genius of end to end design. We are reducing the ability for individuals and small groups to be responsible for applications systems projects. We are therefore inevitably building bureacracy for the sake of efficiency. And it won't be long before we are doing time & motion studies on $15/hour coders on the international assembly line of software production.

This is very much like code bloat and it is destroying all of the productivity that could be gained by the workings of Moore's Law. So what if machines get faster and faster if the code run on them has become so standardized and commodified that most of the application is management of resources instead of actual algorithms doing work?

At some point, wisdom will prevail, not to mention some of us old heads who, with compute resources available to us as individuals, will demonstrate the beauty of elegant design built by small, tightly knit onsite teams and individuals.

In the meantime, I will report on progress and our efforts to keep this puppy in our backyard.

Looking for Talent

Speaking for myself, I am looking to bring some talent on board.

I've got a dilemma. I have lots of work and not enough bodies. Here in Southern California, we are pulling down so much business, that I need a clone army. Unfortunately I don't have an emissary to send to planet Camino.

I am looking for four profiles of candidate to work in major accounts using Hyperion technology in Southern California for the Western Region. If you are somebody or know somebody who fits one of these general profiles, I can get you started quickly.

Profile #1. Oracle Old Head
You are a master of PLSQL and you use Toad. You're plenty comfortable on UNIX and have done your share of VLDB. You've been around since Oracle 7 and you've built all kinds of DW implementations. You may even have a passing familiarity with Express. People take you for granted and don't use half of your knowledge and quite frankly the projects are getting boring. You've been thinking about getting into a new technology or two, like Informatica just because the pay is good. You don't want to waste your time with upstarts.

Salesrep Visualization

I created this particular visual with Tableau OEM'd as Hyperion Visual Explorer. The data comes from a semiconductor company I did a proof of concept for back in '98. (I told you I'm a cube geek.)

These are the top 20 sales reps monthly sales figures on the top selling product. The weakest rep is on top, the best rep is at the bottom. This visualization gives some very cool insights into the reps. Not every good rep is really good.

Take for example, our 'worst' rep at the top. He's a producer but he just had a bad month. I'd rather keep him around than the next three. His pattern is just like Rep 22222, which suggests I'm just having a bad January. That might be manufacturing's fault.

Rep 22922 must have just up and quit after his first bad month. Forget him. Now I've got three consistently good reps in {15528, 15128 and 30517} They hit a positive number almost every month. 21216 hits big once a quarter but he stung me last March. I'll take consistency, thanks.

Of my top three reps, one of them simply hit the jackpot, but other than that he appears mediocre. The jury's out on that guy. 85232 is a big performer and so is the top rep, even though he lost a lot one month.

A picture is worth 1000 words.

I built this app with Essbase 7.1.3 using block storage. It's a six dimensional bookings database with daily orders for 2800 products across 6 geos and 50 reps. There are about 20,000 line items in 5500 orders. It took about 4 hours to build, but I already had nice data. and the schema was done. If I remember correctly, it was a 4 day proof of concept. So, no big deal.

Hyperion Technology Acquisition

I'm ramping up some of my comments over at the HYSL Yahoo board and bringing some exclusive details here. There has been some speculation there that Oracle might acquire Hyperion. I think the HYSL board is doing everything it can to avoid that fate and may be looking to do some acquisition itself. With their recent OEM announcement about Tableau, i think they're being pretty wise about making the Hyperion platform very robust. As I have said and others have speculated, Tableau is very exciting stuff.

On the acquisition side, there are only two pieces missing from the Hyperion platform puzzle. One would be an application API and toolkit that would be widely acceptable to the development community and the second would be an ETL tool.

There might be a third, depending on how closely Hyperion integrates with Teradata's Active DW strategy, which would be some links to EAI and messaging streams, but there's not a real demand for that in Hyperion's marketplace - certainly little the HYSL sales guys might pitch.

Right now all HYSL needs to do is concentrate on getting Essbase covering all the bases that MSAS will cover, and I'm pretty confident that they'll be able to pull it off materially.

The buzz had been that the ETL acquisition would be Ascential. Way back in the day, I did some joint presentations with them when they were Data Stage. But IBM bought them recently and are rebranding it under Websphere. The strange thing about that is that IBM purchased the other half of the company before and made Ascential in the first place. But ETL is ETL and in 18 months, Hyperion engineering could bolt whatever goodies it has to EIS and integrate the whole thing nicely. Hyperion would be a full service DB company and with the next generation of MySQL do most everything with no outside assistance. So let's put two and two together shall we? The VP of sales at an upstart ETL company called Pervasive is Chip Harmon, who used to run Hyperion Sales in the US. Pervasive's stock is just above 4 bucks and they have fewer than 300 employees. Their specialty is Midcap accounting. This could be a match made in heaven.

Hell on earth is getting data out of the little accounting packages like MAS, Solomon and Great Plains. That's where I lived for two years, mucking around with SQL Server, MSAS and ProClarity. If Hyperion can hit the pricepoints of that whole sector, there's only one word for the result: Domination. That's what I'm hoping for.

Visualization Comes to BI

Anyway the reason for the excitement at the current moment is my discovery of Tableau Software. This is the sweetest front-end I've seen since I first laid eyes on Wired for OLAP. It's a pure visualization tool that has about as much wow factor as anything in BI. It could singlehandedly destroy the concept of prefab dashboards, however chances are that's not going to happen for some time.

What's best about it is that it is pretty simple to use, give dramatic presentations which are very rich and informative and it uses a the single best realtime fat client on the planet as its backbone, Microsoft Excel. Tableau hasn't wasted a bunch of time and energy on the n-tier fantasy, instead they have made use of both corporate inertia and Moore's Law. Guess what ladies and gentlemen, PCs can handle big fat clients, and this one delivers.

What I'm doing these days is ramping up my own data warehouse on the home network, which has about 9 boxes right now in various states of function. I've managed to keep various scraps of master data from a zillion clients over the years and I have a fake data generator that I built from scratch. So there's about to be a world of research I'm going to be able to accomplish here at Lab 107, (a wholly owned subsidiary of Metro Decisions).

Hell, I might even snarf some interesting data from blogs and other public data sources just to show off. Stay tuned, the geekfest is about to begin.

Master Data Management

One of the most exciting pieces of software to come down the pike in many years is one picked up in a recent acquisition by Hyperion Solutions. It's one of the reasons I have to be fairly jazzed about the kinds of systems I'll be able to build in the coming months. Formerly called Razza, it's Master Data Manager.

If you had asked me a month ago what was the best way to make money in the Enterprise Computing business, I would have told you Master Data Management. I wouldn't have used that precise term, I would have probably said something like this:

One of the biggest problems for me, in building systems with the tools I have is always the political problem of getting all the people talking the same language. A significant reason why DW initiatives fail is because the metadata is all over the place and everybody spends too much time chasing the data down rather than analyzing it. All I need are my tools (speaking of Essbase outlines) and then I get functional people and technical people speaking the same language, because everybody can see how the numbers and entities roll up. The reason Informatica is making all kinds of money in this space is because they promise to solve this problem.

Well here's what IDC says.

Master data management is a challenging, long-standing problem. But recent attention to business performance management and compliance represent a new opportunity to deal with the issue in a way that can improve both information accuracy and organizational agility.

With Hyperion's MDM, I believe the problem has been solved. As soon as I get a copy I'll get deep into the details, but basically this is a collaborative tool that will allow enterprises to manage all of their dimensions, whether they change slowly or quickly, back through history.

Imagining the worst spaghetti possible, a partial migration between ERP systems without the benefit of ETL, a MDM Server would get everyone on the same page. How many times have I had people squawk about the complexity of Peoplesoft Trees and complain that their reporting systems use one drill down and their interal reporting systems use another and that the Business Objects Universe was painstakingly coded with another? And how many times have I had to be the one to reverse engineer all that rot and put into my systems? Too many to count.

I'm going to have a field day with this tool. Believe that.

Read My Lips, HAL

The locus of my discontent which has overcome my otherwise sunny disposition is a particularly gimpy data pump created by Vignette that the mother company has decided to OEM. Why? Because you don't have to write code! Which means it's a perfectly idiotic tool for those of us who do write code.

So I have come to discover that this gnarled piece of caca between an AS/400 and a SQL Server doesn't pay much attention to SQL code if you don't also draw the little field connector lines in it's idiot proof visual drag and drop fecal-torium. Instead of something simple like just writing a spool phrase as one would in Oracle's SQL dialect, this crap collector makes you grab a little text object from a toolbar. Try to imagine a DTS connection object with about 50 properties. IT'S A FLAT FILE FOR CHRISSAKE! I connect this monstrosity to my query object but not before I have to manually click the 'add field' button as many times as my query is bringing over columns.

Now here's the killer. Imagine you determine, an hour and 3GB later that you'd like to make your query results a little smaller. It's not enough that you remove fields from your select statement in the gawdawful query object, you also have to pretty much destroy your flat file object and recreate it from scratch. This is something it takes a veteran like me all day to figure out.

What a waste.

I take that back. What's so absolutely perfect about this product is that it allows you to generate executable code that you can drop into obscure directories on your customer's servers. Only you know what they do and only you can fix them. That's evil genius.

SQL Server Log Jam

Yesterday we ran into a jam. This is the second time I've seen it in the midmarket. The lone SQL Server machine ran out of disk space and suddenly, mysteriously a couple key applications stopped working. Fortunately, I was using DTS to dump a text file into a new DB and got a wierd error. Aha.

Here's the solution from my business diary:

OK here's how to shrink MSSQL logs. Remember that you should check the database properties first. This shows where the logs go and if there are rollover files as well as the rule about their growth.

The interesting thing to remember is that the system knows the minimal size of the database log needed for recovery to the last backup (which I believe is the date on the .ldf file itself). So when truncate the log it will never make it smaller than it needs to be minimally. That is unless you have created a special kind of recovery profile for the database. This is in the Transact-SQL online help so I'm just putting it into English.

Here's the code I used at SCR for some fairly massive logs.

use master;
backup log projectserver with truncate_only;
use ProjectServer;
dbcc shrinkfile (ProjectServer_log, 800);
exec sp_helpdb projectserver;

use master;
backup log db_ControlManager with truncate_only;
use db_ControlManager;
dbcc shrinkfile (db_ControlManager_log, 5000);
exec sp_helpdb db_ControlManager;

Note that the 800 and 5000 are in MB as the target size for the logs. In our case we needed a more severe trim down to a couple hundred each.

The Speed of Business

I met Scott today and he raised the question of real-time analytics. Hmm.

I should take the opportunity to talk in general terms about what I know and think as regards this matter. The first is my theory of Industry Speed. I basically say, that depending upon which business you are in, you need and more importantly, your imagination for analytics is limited what you do. This sounds like a common sense observation, but it should be noted that this is the first idea that is ignored in a sales situation.

Successful people like successful people, and nobody wants to be limited by technology. So it is likely that a technical person is going to want no limits, on sure-fire way to kick OLAP to the curb is to raise the question of real-time processing, or as Scott put it, why can't we just get Essbase to query directly into JDE?

This is only a reasonable request in the context of demand for real-time analysis. As a nice to have, it's too expensive. But I want it. Why shouldn't I want it? Why should I stick a second tier of databases between myself and my operational real-time data? The answer is materiality.

How much is your management willing to pay to know now? And how long are they willing to wait for you to build this thing that will allow them to know now? How critical is it to the direction of your company that you provide this service. Like the old chestnut goes, sometimes working smarter to avoid working harder is just too much hard work. The last thing you want to do is spend money that doesn't augment human intelligence. You need to find a sweet spot.

Here's my ramp on Industry Speed for Analytic & BI Apps. The lower the tier, the more demanding they are.

Tier 1
Scientific Visualization
Telemetry & Remote Sensing
Securities Trading
---
Tier 2
Banking & Finance
Aerospace & Engineering
Retail
Energy
---
Tier 3
Pharma
Manufacturing
Insurance
Health Care
---
Tier 4
Construction
Restaurant & Hotel Mgmt
Real Estate
Government

The cost of analytical applications is going down, but that is the technology factor. What's keeping the price high is the amount of specilization is inherent in the way the tools are maturing. I find now, that in order to get the kind of job done that one person did in 1995, at least three people are doing now. In any case, the technology is not the hardest nut to crack, nor is staffing. It is the ability to comprehend and capture the initiative from management about clear definitions of the business, the domain to be studied and the speed and accuracy necessary for process improvement.