Do you currently follow any prototyping strategy?
In order to prototype dashboards, you need some sample data and the dashboard tool available that you can report against.
Now where do you get the sample data from? Remember, you just need to create a prototype so you can instantly (within couple of hours) go back to the user and show the dashboard output.
Usually, users can provide the sample or Mock-up data in a flat file or Excel format. Excel is convenient in lot of mock-up activity for following reasons
- If your data is in a different system which is currently not integrated with your dashboard environment, you can simply get an export of data from that system in Excel
- If the data is not yet available in any of your applications, users can create some dummy data in Excel
- It is easier to create and change data scenarios within Excel pretty quickly.
Now once you have your data in Excel, you can connect your dashboard tool to this file and query individual worksheets
For e.g. let us assume that your current project is to monitor the financial health of your exisiting customers. You are a very large corporation and have lot of customers who are big in size (it is always good to have fat customers 
). So how do you get their financial health information? Do you call them and say ” Hi, How are you doing this quarter?” or do you call their insurance company and the check the health records (is this legal?)
Most of the fat customers are usually publicly traded companies so it is possible to get their financial information (legal too) from some financial services websites like Yahoo Finance. Let say you get a flat file containing the company name, stock ticker, current stock price, market cap, and other company information. Now see, this is a fresh requirement which your CFO just made up in his mind and decided to toss it out to his sub-ordinates and find out the feasibility or what it would take to make it to the finals.
Now as a smart consultant, you figure out the high level details and some of them would be as below
- Need to get the daily, weekly or monthly feeds from the Yahoo finance in flat file or XML
- Create an interface to get this data into your data-warehouse
- The customer name from the external feed may not match the way you store the customer name in your ERP and hence in your datawarehouse.
- So you need some kind of logic to map the external customer to the customer name in your system
- You could use DUNS number to do the matching (external feed needs to include DUNS for this matching)
- And then somehow get this information on the dashboard as a Drill through or as a seperate portlet (Top 10 worst performers? Top 10 best performers?)
So as you figure out the details, you realize there are some black boxes (unknowns or open issues) and some white boxes and the project is several man months to the quarter finals.
Before committing any time and dollars, one of the sane thing to do would be to create a quick prototype and simulate the dashboard. You could present this dashboard to the CFO within couple of days and let him decide to give a green or red flag.
For the prototype effort, you can extract customer information from your data-warehouse or ERP system in an Excel file. Lets call this excel file as “customer.xls”.
You can create a mockup data sample for your customer’s financial health records or just go to these finance websites and get an extract. Lets call this file as “customer_finance_health.xls”
Here is a step-by-step tutorial on how to build dashboard using Excel Data
Using InfoCaptor, you can now connect to both the excel files as if you are connecting two SQL databases. You can then issue SQL commands like “select * from excel file” and create a tabular portlet or a chart (bar, pie, line etc). You can drill from one excel file to another excel file.
So within few hours you can create a working prototype and present it to your users for immediate feedback.
Now some sneaky marketing – My company’s product “InfoCaptor” is an excellent Rapid Dashboard development tool and you can easily create dashboards using any data-source including Excel.
It is also possible to mix data sources for e.g You can have a dashboard that has one data window sourced from a Access database, second window from an ERP system (based on Oracle, SQL Server or DB2) and the third set of window pointing to Excel file (ofcourse for prototyping)
NOTE: Whenever possible, use proper database for reporting. Use Excel only for presentation or prototyping purpose.
