BI Excel and browsers

Theres a bit of a debate on one of the others posts about this so I thought I would pull it out into a main post. 

Heres what I remember of the recent history of BI.

In the 90′s all the BI vendors were tripping over themselves to get us off Excel and onto their proprietary client software. (*note)

In the 00′s they finally realised we had no plans to dump Excel and get tied into their client. They then tripped over themselves to deliver us Excel add-ins. Suddenly BI uptake improved.

In the last couple of years the BI vendors have started trying to move us onto their browser based proprietary clients. Are they succeeding where they failed in the 90′s? I have no idea, do you?

* note : Arbor swam against this tide with their Extended SpreadSheet dataBASE, in my view this is a key factor in the success of Essbase over the competition.

Personally I think the march to browser based clients may be misguided. To get the power of Excel to the users, these browser apps will need a fair bit of calculation grunt. I accept that deployment may be slightly easier, although I think most enterprises have got MS office deployment and maintenance pretty much nailed by now.

I struggle to see how providing a browser client with the same power as Excel, is any better than Excel? If its trying to save the licence cost, and the maintenance cost, I’d say the reduced productivity of multiple unusual interface will more than outweigh any savings. The big benefit of spreadsheets (you can get Essbase add-ins for OpenOffice Calc) is they can be used for many many things. One investment in learning the interface and functionality pays back many times.

Maybe there is a market for a BI provider that only has a spreadsheet add-in as a client, and then passes on the savings in development costs to clients?

Business intelligence is only useful to people who have the influence to affect corporate activity. I’d say these people are analyst and management types, not a massive number in any one organisation. Browser based apps seem better suited to wide scale deployment, lower functionality apps target towards administration activities (the classic example seems to be submitting expenses). Maybe its a fashion thing?

I’m not saying the browser thing won’t happen, what I’m saying is that I’m not clear on what the benefits will be for the users, the admins, and even the BI companies. If you can see the benefits then post a comment.

cheers

Simon

Productivity

I wondered about calling this x to avoid preconceptions, but decided that would be too cryptic.

What proportion of your working week is spent:

• a. directly on your projects – analysing coding testing etc
• b. indirectly on projects discussing issues (talking about work rather than doing it)
• c. bonding with co-workers (chatting about non work stuff)
• d. totally tossing it off doing nothing to do with paid work at all (dont feel you have to answer this one!), (include pointless meetings in here)

The reason I ask is we had this discussion a while ago in the office and one of the guys was clearly aiming for 99% in a. To me that is unattainable and actually counter productive.

I’d have thought something like

• a: 70,
• b: 10 – 20,
• c: 10 – 20,
• d: 0 of course ;-), maybe 5% for resting, as many do at least that in free overtime anyway.

would make for a fairly well balanced workday/worker, what about you?

What do you reckon you do? And what do you think is the ideal? (Is there an ideal? is it a personal thing?, a project by project thing?) Does it depend on job role?

cheers

Simon

Web apps

I’m using a web application for recording time with certain clients. I can’t believe how pathetic it is. To do the same job using any rich client would take a fraction of the time and be much more flexible.

I know there are many great web apps out there, and many more waiting to be built. I also recognise that the multiple user/ geographically dispersed user story is much better with server based stuff. And of course installation/administration goes away.

But I can’t help thinking the browser has an awful long way to go to come close to the richness of office. Makes me see the sense of where MS are going with Excel services. More native server friendliness directly in the Excel client might be nice  (for a better information sharing story).

Have you go any web app horror stories – something that is trivial in Office but mind numbingly painful via browser interface?

It reminds me of the project where the guy next to me had 5,000 lines of javascript to do sorting and filtering. Hello? thats like 3 or 4 lines of code in Excel/VBA.

cheers

Simon

Maintenance v new build

What proportion of your time do you spend maintaining/fixing other peoples stuff compared to the amount of time creating new stuff?

Don’t include working on stuff you built years ago as maintenance, even though you have forgotten it. (you must have had a reasonable understanding of it at one time)

I probably spend half my time on each. Maintaining other peoples work is much harder than creating my own. Its obvious why: it includes all the same problem solving/creativity/accuracy type stuff, but also includes trying to get your head around the tortured logic of the original dev. And they were probably rushing to meet some unrealistic deadline.

One day I’d like to work on a system that had not been rushed out under immense pressure, that maybe has some useful up-t0 date documentation. I never have and I suspect I never will, certainly not in the Office dev space. (Of course I wouln’t want to write the docs myself, I’d rather be coding ;-) ). What about you?

Cheers

Simon

DAO

Anyone [still] using DAO?

I’m importing a text file into a jet database (without using MS Access). I have had a few adventures for sure but I have finally managed to get it work. I had to add a querydef to the .mdb, and then .execute it from C#. That means ditching ADO and going back to DAO, unless I’m missing something?

Its proven to be a bit of a PITA as my SQL worked fine from the QBE, but would not run from my exe. Hence adding the querydef, which has fixed it. I read somewhere that the QBE actually uses DAO rather than ADO, does anyone know if thats true of Access 2003? I also read that DAO was faster than ADO for jet databases, anyone like to confirm (or not)?

Anyone found a better way to work with text files and .mdbs (without using Access directly)?

(I’m writing a server component (in C#) so want to avoid automating Access if poss)

Cheers

Simon

Google programming

Not the programming of google, but programming with google.

I’m doing some C# at the moment (plain .net not VSTO/VSTA), and its fair to say I’m a little rusty. No problem thanks to t’internet, just google “C# jet” and everything I need is there. (‘other search providers may offer similar services’)

I used to use mainly help, but I am finding more and more, Google often provides more useful content. Do you find that? You need to know what to search for of course, and what not to search for.

Do you have another favourite resource?

cheers

Simon

Spreadsheet security

Do you think spreadsheets should be more or less secure?

I know a few people who think that increased spreadsheet security would be a good thing. I’m not so sure.

Personally, I think I would like to see:

• continue workbook open passwords, and encrypted workbooks
• remove workbook structure protection
• remove worksheet protection
• remove VBA protection
• increase macros security options
• enable some limited (‘safe’) macro features even with high security
• maybe be able to digitally sign worksheet stuff to warn of changes

I think increasing security would give the impression that spreadsheets are appropriate for sensitive production systems. I don’t think they are in most cases. And I think if features were added so they were, then the features that make them great for rapid prototyping would suffer.

It’s easy to get improved security – just use a platform that provides it, eg VB6 and a grid control, or .net (lets not discuss reflector!). And these are great migration paths once you have worked out how everything should work in a nice quick easy open spreadsheet.

In a sentence, my preferred solution would be: optimise spreadsheets on ease of use, make migration to more robust/secure/scalable/etc systems easier. What would be your one sentence? and why?

Whenever I see worksheet protection in Excel

• a. I remove it
• b. I think spreadsheet abuse. If it needs protecting then Excel isn’t the right tool.

What do you think?

Where do you sit on the ease of use v security lock down continuum?

Can you see a way to have both with no conflicts?

cheers

Simon

VSTA info

Ross has done an excellent post on VSTA, what it is, how it works etc. Well worth a look if you are interested in VBA and what the future may holdfor us Office Devs.

Ross on VSTA

He has also listed lots of useful resources.

Nice one Ross.

For those unfamiliar, VSTA (Visual Studio Tools for Applications) is the .net equivalent of VBA. It is technology that MS licence to other vendors to enable them to include powerful macro features in their apps, much easier than writing from scratch. I mistakenly thought it would be in Office 2007 (technically it is, but its one of the new products not the ‘classics’). I think I would like it in the next Office, although I can also see reasons why they may be better separate.

I suspect this is to sort of thing we will be talking about when we meet up in June(?).

Cheers

Simon

Are spreadsheets the second best tool for everything?

One of my mates used to be a hotel handyman. That wasn’t his main job, but as he generally fixed things no-one else could, he got the jobs by default. His secret weapon was a standard cutlery knife. Its was equally poor at everything, but with enough effort he could prise things open, use it at a screw-driver, clean things etc etc.

Spreadsheets are often called a ‘hammer – and all problems look like nail’, I think they are more like Micks knife. Better tools were available for every job, but they were hard to find, he could always find a knife. And with a bit of imagination and enthusiasm could solve many a problem.

A few people have suggested spreadsheets are more like a multi-tool, would you agree with that?

In their book,The Art of Modeling with Spreadsheets, (worth a read btw), Powell and Baker suggest that the spreadsheet is the second best tool for any job. What do you reckon?

Maybe its third or fourth in some cases but I think they are spot on. There is nearly always a better specialised tool for any project. But there is often a significant learning curve, or excessive form filling, or cost, or other barriers to use.

Spreadsheets are ubiquitous and flexible. Often that is more useful than being the ultimately best tool for the job.

I think with decent spreadsheet skills and a bit of coding you can cobble together a solution for most business problems. It may not be perfect, but many times its good enough, at least temporarily. And almost without fail it will be quicker and cheaper than the ‘best’, especially if you are less experienced in the best option. Would you agree?

Can you think of any other software that is as widely used (/abused?) as spreadsheets?

cheers

Simon

bye bye TurboExcel

And hello Calc4Web.

SavvySoft have changed the name of TurboExcel to Calc4Web. (I hope OpenOffice and Computer Associates (do they still exist) don’t mind).

SavvySoft view here

Microsoft view here

This has been an ongoing trademark dispute, that should worry anyone who uses ‘Excel’ as part of their product name. Interestingly based on the MikeRoweSoft case I wonder if the use of ‘XL’ is a bit of a potential target too? (I think he got an Xbox as settlement – I hope he got some games bundled!)

The blurb says amicable, I hope it was.

I’m not convinced its in Microsofts best interest to go after members of the ecosystem that surrounds their products, maybe I’m missing something. In a way I think the law kind of forces the hand of trademark holders – if you don’t vigorously defend your trademarks, you may lose them. What other *Excel or Excel* (or *Excel*) products do you know of that might be at risk?

‘Calc4Web’ converts spreadsheets into C++, giving you the options of compiling into an xll, or a dll, or even a .net assembly. This is a great migration path, have your business user prototype in Excel then click the button to convert into a compiled component. To me that would probably encourage use of Excel. And from a risk/quality POV, it makes loads of sense, use Excel for what its good at, then convert to something more secure/robust for production.

I’m hoping to get a play around with this product over the summer, I’ve heard good reports. Anyone here using it? (you might need to get a patch to fix up those dialog box titles!)

Cheers

Simon