5 ms·

I work for a big organization, and the amount of "wow, you're a data wizard!" comments I get for generating combined keys inside disparate databases and then me

I work for a big organization, and the amount of "wow, you're a data wizard!" comments I get for generating combined keys inside disparate databases and then merging them in Excel is insane. It's making me think that anyone who is not well-versed in at least the basics of database theory is crippled in the information economy. A lot of "tool-based" problems that people complain about are in fact user problems, when certain teams aren't maintaining their data with clear identifiers or internal standards.

randycupertino 6y ago
Umm, I work for a big org and I get called a data wizard for cleaning up and merging two Excel trackers! I just copied your comment into my One Note so I can figure out what you're talking about re: combined keys and level up my wizardry. Any tips to get me started? I work frequently in disparate databases (EPIC, Veeva, Rave, Excel, Suvoda.....). Any big org that is silo'd is gonna have a big problem re: clear identifiers and internal standards. Heck, once we passed 500 headcount even just file naming convention was tough to roll out!
- sjg007 6y ago
  A combined key in this context likely means a compound / composite key. https://en.wikipedia.org/wiki/Compound_key https://en.wikipedia.org/wiki/Compound_key
  - quacked 6y ago
    Yeah, that's right- I just called it the wrong thing.
- brundolf 6y ago
  People at my old company would do a lot of "data archaeology". They would find a table in a T-SQL database named "facilities", and a table in an SAP system called "units", and then would notice that the numeric "facility_id" column's values had a lot of overlap with the string IDs in "unit_id", if you remove the underscore prefix at the front, and then they'd email the client's data people to confirm that these were in fact the same entities, and then they'd associate them in their cross-database-querying tech, and ta-da. A huge amount of new data cross-pollination is available.
  - quacked 6y ago
    I'm starting to wonder if I should quit my job, buff up on SQL, and go into data consulting. Everyone on HackerNews talks a big game about FAANG and their copycats trying to leverage machine learning techniques to increase profit margins, but the bulk of legacy businesses seem to not even have the facility to make sure that client_ID is stored properly across their massive realms of siloed databases.
- moqca 6y ago
  Basically you make keys out of your data: | Week# | clientid | key | | W01 | CLI001 | CLI001W01| You use this newly created key to match data on your databases/files
- quacked 6y ago
  The comments below me elaborate a little more clearly, but it's pretty simple. If you have two databases: DATABASE A Field List: {Temperature, Price, PART NUMBER, Delivery Time, SERIAL NUMBER} DATABASE B: Field List: {Mass, Length, Width, Height, PART NUMBER, Volume, SERIAL NUMBER} You just have to create a new concatenation of PART NUMBER and SERIAL NUMBER, and now you have a unique identifier that will match between the two databases, and you can easily check which unique instances are present in both databases, and which in A are missing from B and vice versa. Not sure what your level of coding proficiency is, but a flowchart of how you might get started increasing data wizardry would be something like this: EXCEL Master the following functions: INDEX/MATCH, Nested IF statements (especially using AND, OR, XOR, and NOT), IF/ISERROR, ISBLANK, CONCATENATE, and figure out how to combine all of these creatively Master the creation and formatting of Pivot Tables POWER QUERY Master Power Query (you'll want to use online tutorials for this) Once you're really, really proficient in Power Query, you may as well just start learning SQL and writing your own queries. Udacity and Udemy have multiple excellent free courses in SQL and Python. Some more keywords you may want to look into: - Relational Database theory - Bryce-Codd Normalization - CRUD applications - essentialsql.com - ExcelJet - MrExcel - Chandoo I am by no means a real master, and my degree is in engineering, not programming, but independent research into basic database management has propelled me far past many coworkers in terms of efficacy. It really is astonishing how little the average person knows about proper database operation, despite the fact that most modern companies live and die by the strength of their data.
  - randycupertino 6y ago
    Thank you for the detailed explanation! Going to try this week and will have fun based off your description. Cheers.

randycupertino 6y ago

Umm, I work for a big org and I get called a data wizard for cleaning up and merging two Excel trackers! I just copied your comment into my One Note so I can figure out what you're talking about re: combined keys and level up my wizardry. Any tips to get me started? I work frequently in disparate databases (EPIC, Veeva, Rave, Excel, Suvoda.....). Any big org that is silo'd is gonna have a big problem re: clear identifiers and internal standards. Heck, once we passed 500 headcount even just file naming convention was tough to roll out!

sjg007 6y ago
A combined key in this context likely means a compound / composite key. https://en.wikipedia.org/wiki/Compound_key https://en.wikipedia.org/wiki/Compound_key
- quacked 6y ago
  Yeah, that's right- I just called it the wrong thing.
brundolf 6y ago
People at my old company would do a lot of "data archaeology". They would find a table in a T-SQL database named "facilities", and a table in an SAP system called "units", and then would notice that the numeric "facility_id" column's values had a lot of overlap with the string IDs in "unit_id", if you remove the underscore prefix at the front, and then they'd email the client's data people to confirm that these were in fact the same entities, and then they'd associate them in their cross-database-querying tech, and ta-da. A huge amount of new data cross-pollination is available.
- quacked 6y ago
  I'm starting to wonder if I should quit my job, buff up on SQL, and go into data consulting. Everyone on HackerNews talks a big game about FAANG and their copycats trying to leverage machine learning techniques to increase profit margins, but the bulk of legacy businesses seem to not even have the facility to make sure that client_ID is stored properly across their massive realms of siloed databases.
moqca 6y ago
Basically you make keys out of your data: | Week# | clientid | key | | W01 | CLI001 | CLI001W01| You use this newly created key to match data on your databases/files
quacked 6y ago
The comments below me elaborate a little more clearly, but it's pretty simple. If you have two databases: DATABASE A Field List: {Temperature, Price, PART NUMBER, Delivery Time, SERIAL NUMBER} DATABASE B: Field List: {Mass, Length, Width, Height, PART NUMBER, Volume, SERIAL NUMBER} You just have to create a new concatenation of PART NUMBER and SERIAL NUMBER, and now you have a unique identifier that will match between the two databases, and you can easily check which unique instances are present in both databases, and which in A are missing from B and vice versa. Not sure what your level of coding proficiency is, but a flowchart of how you might get started increasing data wizardry would be something like this: EXCEL Master the following functions: INDEX/MATCH, Nested IF statements (especially using AND, OR, XOR, and NOT), IF/ISERROR, ISBLANK, CONCATENATE, and figure out how to combine all of these creatively Master the creation and formatting of Pivot Tables POWER QUERY Master Power Query (you'll want to use online tutorials for this) Once you're really, really proficient in Power Query, you may as well just start learning SQL and writing your own queries. Udacity and Udemy have multiple excellent free courses in SQL and Python. Some more keywords you may want to look into: - Relational Database theory - Bryce-Codd Normalization - CRUD applications - essentialsql.com - ExcelJet - MrExcel - Chandoo I am by no means a real master, and my degree is in engineering, not programming, but independent research into basic database management has propelled me far past many coworkers in terms of efficacy. It really is astonishing how little the average person knows about proper database operation, despite the fact that most modern companies live and die by the strength of their data.
- randycupertino 6y ago
  Thank you for the detailed explanation! Going to try this week and will have fun based off your description. Cheers.