This is the third part in my series where I am making the case for when a database is superior to a spreadsheet. I will reiterate: spreadsheets are a great tool for presentation of data but databases offer advantages when you need to transform your data or analyze large amounts of data from different sources. Most people are not as familiar with databases as they are spreadsheets. Once you fully understand what you can do with a database, you may want to invest time in
There’s nothing like going out to your favorite restaurant and getting a great table with a view. Well, with a database, you can get a great view to a table. One of the most powerful features of databases is that it allows you to create views of data. If you need to combine information from several spreadsheets, it might be easier to convert those spreadsheets into tables and then create views that combine columns from different tables into a single view.
Creating views or queries allows you to order your information exactly the way you want to see it. Do you sometimes need to see your data from multiple perspectives? By creating multiple views, it allows you to see the data in different ways without actually changing the underlying data in the tables. When the data in the tables is refreshed, your views automatically update without having to re-write it. Once the view is created, all you need to do is run it.
Are you downloading information from your billing system or other subsidiary system, transforming data and then uploading it to your general ledger? You can transform your data within a view. For example, let’s say you download customer transactions from your billing system and the transaction numbers contain dashes but you need to remove those dashes to upload the information to your general ledger. In
With a database, you would simply upload the raw data from the billing system (assuming it comes out in database format) into a database. Then you create a query that lists all the columns in the exact order that you want to see them. For the column containing the transaction number, you write a similar Substitute function that will remove the dashes from the transaction number. You save the query (view) and execute. This accomplishes two things: 1). It automatically removes the dash from the transaction number. 2). It preserved the original data so you can still see it in that format if needed. Each time you need to refresh the data, you would merely overwrite the data in the table and re-run the query. To see it in the original format, you would simply copy the same query and delete the “Substitute” function from the transaction column. You now have two views of the same data.
The setup of a database is unquestionably more labor intensive but the flexibility you can achieve once it is setup is well worth it. In my next article, I will further explore the power of queries and give you a few suggestions on how you could start to use them to transform your finance and