I'm working on building a database to aggregate various data sources and feed a set of Tableau dashboards.

The data is all employee data, the problem is, I only have one file of seven with a unique Employee ID, and the format of Employee_Name is inconsistent across about half the files, so I can't join on Employee_Name (capitalization, commas, and initials are all inconsistent). Do I need to write macros to create consistency in Employee_Name so I can write the lookups to create the proper primary/foreign key relationships, or is there a more succinct way to do this?

My first choice is to go back to the client, and ask for new data exports with a common Employee_ID field across all of them, but I don't know if or when that's feasible.

I should also note, I already wrote a bunch of code in R to solve this problem and perform the joins, but the client needs a solution that doesn't rely on me running an R script every time they want to update their data.

I'm also all ears if there's a better solution with SQLite or something like that.

  • There is nothing succinct about scrubbing name values. :) Even if you do have macros that force "consistency" on names, it is still a bad idea to use names as a primary key. Names are not unique in the real world and can be misspelled, etc. Best case the client provides unique ID's. Worst case, establish your own unique ID's with perhaps some other key values (time data received, name of file?) that helps distinguish the data. I would recommend this even if the client cleans up the data, just to ensure you have your own unique, reliable data trail. – C Perkins Jul 26 '17 at 22:24

You are building a basic BI/DW environment. You need to get:

  1. Cleaner data, as it is generally more efficient to fix the problem upstream.
  2. A real database. I would not use Access for anything important like that. It gets corrupt and can only grow to a certain size. Ask the client if they can host a DB to hold this data or can spin something up in the cloud.
  3. Use some form of tool like ETL to extract and load the data into your database.

If you want to go really cheap, Microsoft's Tableau competitor (Power BI) has basic ETL functionality built into it, can source directly from flat files, can call R scripts for clean-up before presenting data, and can store a limited amount of data internally.

  • @Mako212 Good advice, but if your client can't rely on you running an R script to do the work, what does Access do to solved the concern? Would installing another set of database/BI services be any better than just automating a way for the client to run the R scripts? For what it's worth, I have a love/hate relationship with Access and I'm not even offended that CalZ does think it's real. But it can be reliable enough and very convenient and if it's only for migrating data between systems, corruption is not a significant issue in my experience. – C Perkins Jul 26 '17 at 22:24
  • @CPerkins The idea is to build an "update-able" data source for Tableau, because we can't connect directly to the source data.This happens to be government data, and we'll likely never be able to write SQL queries against any primary database due to security constraints, we only have access to the data through UI export tools. So Access becomes a repository for the 8-10 exported files, and the single source DB for Tableau. With good primary/foreign keys, I think this is a pretty compact solution, but it's a disaster trying to do it with names and no IDs. – Mako212 Jul 26 '17 at 23:05
  • Yeah, you're really being asked to do a classic data warehouse, except with no data warehouse. If Access can handle it, it means it is well within the capabilities of MySQL and PostgreSQL which would prove more reliable and are still free. They also have procedural languages where you could automate lots of the data clean-up / loading. – CalZ Jul 27 '17 at 11:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.