In an earlier entry, I talked about using Excel as an integral part of a budgeting and planning application. In that example, Excel was connected to the database of record and presented the users with the ‘actuals’ data (YTD #s) they needed to enter the budget for the upcoming cycle. Although this is a simple use case for Excel in a BI application, it opens the doors to many more sophisticated deployment strategies.
One way of categorizing an application deployment is persistence, defining the way users are actively attached to your application at any one time. A purely browser-based application must keep track of all the users attached to the system at all times. While this isn’t as big a deal in a reporting environment, it becomes resource-intensive when the application allows write-back (e.g. budgeting and planning systems). One possible solution is to bulk up on hardware or create server farms that can balance the increased demand for both application and DBMS connections. This type of persistent connection will eventually become too expensive. Persistence doesn’t always pay off.
An alternative solution is to implement non-persistent connections to the system which allow users to only take up space on the application when they need the resource and disconnect when finished. In fact, many budgeting and planning users prefer to massage their budget and come up with optimal numbers before they submit them to the system. Many of these users would rather not be stuck at their desks through the entire budgeting process and they are more comfortable navigating and crunching their numbers in Excel. On the other hand, their organizations would like to keep the central and consistent management of these users’ activities.
Of course, I wouldn’t be thinking about the advantages of integrating persistent and non-persistent connections in a business intelligence system if arcplan didn’t have a product to meet this need. To keep it short, arcplan Edge allows both types of connections to allow users to do their budgeting and planning ‘offline’ in Excel and then connect back to the system when they’re ready to submit their numbers.
I bet when you were building your Business Intelligence application, it never occurred to you that your users would be so demanding. After all, you’ve managed to provide them reports and charts containing all the data they entered into the system. Now, in addition to the data in your internal systems, they’re asking you to add in data from other sources that you have no control over. This is actually the latest trend and I’m convinced it’ll get worse before it gets better. In the old days, you could just tell them you don’t have the data and it would cost a lot of money to bring the information feed in-house. But like I said in my previous article on user expectations, your users already have access to this data on their home computers and they expect the same type of information access from the BI systems they use to get their job done.
The good news is that those applications are using something that’s readily available to you — Web Services (WS). The reason this data used to be unavailable in the past is because the producers had no way of giving it to the consumers. They could dump the data into delimited files and email them to you, but what good would that have been?
Awhile back, a bunch of bright people came up with a way to simplify the process and the concept of Web Services was born. By defining the structure of their feeds, producers of information on the internet can give you instant access to all of their data. Generally, Web Services are a combination of Data Feeds and the Data Definition Language (DDL) that describes them called WSDL. WSDL is a standard way of telling another program what the data feed looks like by defining the input parameters, their format, and the output of the Web Service. To the right is an example of a Stock Quote service provided by www.webservicx.net
Yes, it’s the ubiquitous “examination of Excel” article that every BI blog posts at some point. But I think it’s important to discuss the limitations of Excel, especially for readers who are just beginning to realize that they cannot continue with manual data collection and reporting forever.
Question: What is the most popular database and what’s the most popular BI tool?
Answer: Excel and Excel
This isn’t surprising. Everybody knows how to use Excel and it’s installed on just about every Windows machine out there. Excel is intuitive and allows users to input data and output it into simple reports and charts – stuff we all need to do our jobs. You can even do some pretty complex stuff with it if you’re willing to learn how to write macros. But just because you can do something, should you? Obvious answer: No.
For one thing, Excel is not multi-user software. It’s only meant to be used by you on your machine. Translation: It’s not a database like SQL Server or Oracle. You can store a lot of data in it, but as soon as you have to share this data with other stakeholders in your organization, things start to unravel. You realize that not everybody is entitled to see the data you’ve put into the spreadsheet (creating authorization/ authentication issues) so you’ll have to extract just their part. Then you have to get it to them via email or FTP, and any interaction they have with the data will need to make its way back to your original version for reconciliation (creating update management and versioning issues).
One of the variants of Ockham’s Razor is the theory that “it is pointless to do with more what can be done with fewer.” I bring this up to make the following point: Your boss will think you’re a hero if you just give him or her some of the basic information he needs to do his job. Sometimes that basic information could be a single data point such as “sales are down in Florida but up in all other regions.”
Now picture your boss while he’s running on the treadmill at the Holiday Inn gym where he’s refused to pay the $10 internet service charge. He looks at his iPhone and notices an email with the subject “Sales Performance” with a URL inside. He clicks the URL and sees a pie chart neatly set up to fit the display on his iPhone that clearly states where sales stand as of that morning. In fact, he’s so excited about what he sees that without even thinking of it, he’ll click on the red slice representing Florida and drills down into another simple chart that shows the top 5 products bringing Florida’s number down. He even proudly displays his iPhone to the sweaty guy running next to him to show off.
And that, my friend, is how you become his iPhone Hero. Next time you’re dreaming up ways to ask for that raise, all you have to do is set up a reporting system that is simple to use, gives the users the biggest bang for the buck, and doesn’t require a PHD in physics to decipher. And then make sure it runs on your boss’ iPhone (or Blackberry). Simplicity is actually more effective than you think especially because there are just too many distractions. A large majority of your BI audience is on the road, has only a G3 or maybe G4 bandwidth, and doesn’t have time to look at the details or open a laptop to get them. Make their lives easy by providing them with a mobile BI application that helps them get their job done. Provide them the significant information from a very high level and let them interact with the application in the same way they do with Facebook or iTunes. It’s not that hard when your BI infrastructure plays nicely with all of your mobile devices.
On Cyber Monday morning at 7 AM, I found a good deal on a piece of electronics that one of our teenagers wants for Christmas. By noon, I was itching to find out if it was shipped and if so where it was in its journey to our home. I checked again at 5:30 PM and then again at 11:00 PM. Each time I looked, I was amused by the fact that the gift was one step closer to its destination.
The fact that you can track every time a package gets handed off to someone else within seconds of that hand-off is no longer anything special. This is what everyone expects – and rightly so. After all, the hand-offs are simply time stamps plus a couple of other pieces of data associated with information about your package. It all adds up to about 5 or 6 rows of data that shows the position of the package from the source to the destination. What makes this significant is that it’s available at any time of day and I don’t even have to pick up the phone and call anybody to access it.
Here’s my point: Like it or not, what’s happening in cyberspace affects your company systems by raising the expectations of all users. When you track a package from your home laptop and see details of every move it makes, you’d have a hard time going to work the next day and putting up with a green screen CICS application on your office desktop. Your expectations for your company’s information system cannot be any lower than what you can accomplish on your laptop at home.