Today I’m working on building a new reporting system at my day job. Basically we’re looking at creating tools to budget and forecast results to be reviewed weekly, with a goal of being more on top of results so we can react better. The stuff that every business really wants to do.
I’ve got the reports that we want at the end, and now I’m looking at them trying to figure out where to get the info from, how quickly it will need to be updated, where to source info that isn’t readily available and all that good stuff. By the time we’re done I’m sure we’ll be hitting on Access, SQL Server, Excel, VBA, Power Pivot and Power Query, and possibly some other tools I haven’t thought of yet.
So at the end of the day, I’m building a system here. Something to be used daily to capture, store and report on information. Built of a collection of different pieces and technology, I’m essentially building my own software solution to a business problem.
I started with some sketching on paper, trying to illustrate the overall data collection/storage points and program flow. Then I moved to Visio to try and draw it there. (The pieces needed to move around as I added new dimensions and considerations.) Then I got stuck on some of the considerations… some of them were big enough that they could change the way things were done.
Now I’m in Word trying to state – in English – the goals and non-goals of the overall project, so that I can get an overview on paper.
I’ve got a meeting this afternoon with a key player/driver to talk specifically about:
- What the system should do
- Key points that is absolutely will not be required to do
- Key points that we want to make sure do not happen (different from above)
- Who is going to use the system (each part)
- How it’s going to be updated (each part)
- How often updating will be needed for information drivers
I’m hoping with those stated that I can do two things:
- Get answers to the major design changing considerations I need before I build anything, and
- Generate a list that I can go back to later to ensure I haven’t overlooked or missed anything when I’m designing the system.
While I’m keenly aware that I’m not going to get everything covered, and that some design considerations will need to be made on the fly during development, I want to minimize the chance that any of those will major issues. I hate coming up on a blocking point part way through the implementation that forces design re-work and retrofitting.
Now I’ve done this kind of things many times, but I don’t know that I’ve ever felt that I’ve settled into a piece of software that really works for me when trying to design this stuff. I usually end up with Word to track goals, Visio for the flowcharts, but there’s a lot of iteration to get through it.
That’s the reason I’m reaching out here, is to see what others do in this area. I’m curious as to what tools/processes YOU use to scope and design a BI system or software. Do you start with a goals document, do you start with flowcharts, or something else? Are you happy with your process, or are there pain points and gotchas that you still get hit with?
8 thoughts on “Designing Software – How do YOU start?”
I just reached an important milestone in a project I'm working on: the one where I scrap it and start over. I sketched out my output and my inputs were already clearly defined and not changeable by me so all I had left was the middle bits to convert input to output.
I got a fair piece down the road when I realized that I had been doing it wrong. I decided to store some data in a certain way, then realized that it should be a different way. I pressed on hoping for the best, but ultimately I'm faced with a rewrite. It doesn't bother me though. My best writing is usually a rewrite.
If Picasso can scrape paint off a canvas and start over, who am I to hold my code sacred? And it's not like I'm starting from scratch. I figured out a ton of stuff along the way that I won't have to figure out again. A lot of code will have to change, but then again a lot won't.
That's not to say you shouldn't plan. I think you're doing it just right. But don't lament the changes. Rather embrace them as an opportunity to revisit what you've done and do it better.
As for tools, I make notes in simplenote and that's it. I don't flowchart or anything like that. I start by listing my outputs. Then I list the inputs for each output and where it lives, how I get it, how I have to store it (if I do). Finally, I think through any tricky implementation parts and make notes about it. I don't get too detailed on the middle part because as long as it turns the inputs to outputs, it shouldn't really matter.
Planning is essential, but Napoleon was right when he said that the battle plan rarely survives first contact with the enemy.
At the very least, you need at least a couple of definable and measurable goals. If you're really lucky, those will be coupled with success criteria.
Remember, if you don't know where you're going, then any direction will do 🙂
Hi Ken,
Great topic! I'm working on a similar project that involves multiple data sources from different CRMs and financial reporting software. Since we lack a data warehouse, I'm combining it all in a database and using PowerPivot to combine the data and create reports and analysis. It's a beast of a project, but PowerPivot seems to be handling it well so far. There is some VBA mixed in as well, and I will probably start using Power Query as well.
In regards to your question about organizing the project, I use OneNote for all my notes. It is incredibly useful and keeps me well organized with multiple projects. It also integrates with Outlook really well. You can quickly create a new notes page based on any meetings you have in your Outlook calendar, and it will pull in all the meeting details including the list of participants.
You can also email any notes page with the click of a button.
It also allows you to add tags to any list. These tags can include checkboxes (to check off completed items), stars for important bullet points, question marks, etc.
OneNote also has apps for iOS and Android, and everything syncs. This is great if you think of any idea for your project while sitting on the couch. Just pull out the iPad and add it. I use this all the time.
It does have a Shapes menu so you could sketch a basic flowchart, but I tend to use Excel for more complex ones. Visio is probably the best but I haven't spent much time using it.
Good luck with your project! And let me know if you have any questions. Would like to know what you learn from your experiences...
Pingback: Excel Roundup 20140210 | Contextures Blog
I start by trying to work out what users want. Then I just start building it. Then I tear it down, and restart by trying to work out what users need. I never learn.
Jeff, that used to be my usual method. I've found I've had a bit more success with asking them what they want, then drilling into that until they figure out what they actually really need. After that, I've been able to avoid some of the re-writes. 🙂
Jon, interesting comments on OneNote. I do use it, but I actually didn't realize that it can integrate with Outlook, and haven't used the tagging stuff. I'll have to look at bit deeper at the feature set.
Dick, interesting on the inputs/outputs. Very similar to the process I usually follow, but I'll admit, I can't program without at least trying to build a flowchart. 😉
We're starting a project with SharePoint 2013. Ultimately we hope (!) the SP dashboards will be able to extract what we need from the various sources (Project, Excel, ...) into a standard template, but for now I'm using Excel as a mockup. We'll distribute the program status as a workbook, but with users able to click and call up detail from the main overview page.
There have been lots of "suggestions" for changes already, and there's been no point in keeping a flowchart as the stakeholders who would want to see something from this report keep increasing. Time frames are also in flux - executive committees want to see 90-180 days out while the sub-committees want current status, risk, etc. Some people want the individual projects reported by department, some by status, by owner, risk type, ...
Excel has been useful because (a) it handles the numeric info like burndown rates, paid invoices etc natively, (b) everyone has it on their computer, (c) it has enough flexibility that I can add or change sections or add another radio button to handle the latest sort request (as long as the data has the sortable element in it). Once we get SharePoint running, Excel is probably the default mode for the dashboards anyway so conversion won't be as much of a hassle.
Ultimately I'm not sure if this is what you were asking. We've thrown something out there from the start that's flexible enough to repeatedly change, then changed it as circumstances require. As one of my program managers said a long time ago, when you go in front of executives they need something to tear to pieces. If you don't bring them something, they'll tear *you* to pieces.
Great article and I could not agree more. Software design can yield limitless possibilities so it is best to start by understanding requirements and constraints to help direct the design process and to understand how well your design solves the problem.
I wrote an article that compares modern design process to the process Einstein and Michelangelo had with their own work.
Design like Einstein and Michelangelo
https://medium.com/@dave.b.kaplan/design-like-einstein-and-michelangelo-fadf2c86e3f7