March 12, 2025 at 1:51 pm
hi we have an army implementing a mfg erp migration to netsuite. When they were in the sandbox, their customer, ship to, bill to and product codes were "external" columns. One or two might have been substrings of external columns but that was fine with me.
When they went to their more qa like environment they decided to make their bill to and ship to codes internal database keys. Not sure what they are planning for their product codes. They left their customer code external. One of their designers was heard saying "we'll try to keep the values the same". I think that person meant we'll try not to repurpose those key values.
I did my best to show folks what (and why) a star schema is and how repurposing just one db key can trigger millions of unnecessary updates in a warehouse that also uses planned redundancy. One of the answers im hearing is that "we'll need to get some assurances from them" . i shared a nightmare contract i was on once where the wh keeper had a medical issue and while she was out her peers made this same mistake.
i dont believe im being heard. has anyone in the community had the opportunity to and actually stopped something like this in its tracks? How did you explain it?
March 12, 2025 at 2:10 pm
Just for clarity, are you saying that they are generating INTs (or whatever) as keys in dim tables as part of a DW load and that those INT values can change from one load to the next?
Can you give an example of the 'repurposing' you refer to? Are you saying that an 'X' in column A, which, for example, previously meant 'Blue Cheese' now means 'Halibut'?
March 12, 2025 at 7:52 pm
kind of phil. their decisions impact their oltp directly. they dont really think about or understand the impact on a warehouse.
for example, say they have a customer hierarchy. various bill to and ship to records reference the parent customer record via foreign key. the database key to those bill tos and ship to''s is randomly assigned by the underlying rdms when the bill to and ship to records are inserted. not unlike an identity on a pk column. i believe records like these bill to's and ship to's are inserted en masse from a "template" (spreadsheet or whatever) that shows the business relationship but could care less about the key that will be generated. The designers are planning on providing me with XREF's of old perm codes to new db keys. It isnt clear yet what they plan to do if/when one of those xrefs gets broken by normal erp activity. As you probably already see, there is no way to maintain assigned db keys between prod and dev instances of the netsuite erp.
so db key 201 might have once meant/represented (via attributes like address etc) acme's brick and mortar ship to, but now is deleted or instead represents a bill to instead, or maybe now represents a brick and mortar of microsoft's or is gone altogether. the only thing i believe is that the key itself is immutable, at least from the perspective of being changed. i know netsuite has "merge customer" functionality.
March 13, 2025 at 1:45 am
I'm curious... what does the lowest level of management think of what you said. Has anyone even told them? And how about the next level or two above that? Have you been given the opportunity to explain it to them?
You already know this but I have to say it out loud because I went through this type of thing once before... This sucks because you're just one voice against a "consensus" and, as I've witnessed before, the "consensus", no matter how illy prepared it is and no matter how well "right" has been suggested or designed, "consensus" will always win unless you have some really smart people in charge at the management levels.
That's "HR speak" for the best advice I can give you... do your best and protect yourself in the process. Do nothing outside of their plans. To make that advice even more blunt.... protect yourself because they're not letting you protect the company and if things go south, they'll be looking for someone to blame. Continue to try to sway them for the sake of the company but protect yourself. Heh... and if you want your ideas to win because you truly want to help the company, let them or help them think that your good ideas are theirs. Just make damned sure that you're right if you do that or you'll find out just how quickly even allies will turn against you.
I make it all sound a bit morbid but this type of thing does happen.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 13, 2025 at 5:11 pm
thx jeff there is one lower level manager that understands it from an edi standpoint. Generally speaking edi is more of an immediate gratification app where the hassle of xref lookups is less intrusive. His question was "Why?". That and one director are the closest i come to people who understand. The director asked the best question which we'll get an answer to shortly ..."under what conditions would they repurpose a db key being used in what is traditionally a permanent code scenario".
More importantly, does the community have an opinion on using a db key for what is traditionally a permanent code? I already get that the erp will be fine. I'm asking what the community thinks about the decision when there is a warehouse in the picture.
March 13, 2025 at 10:03 pm
How do you explain anything to the chiefs? Use their language.
MONEY
There are costs associated with both your approach and the one taken.
There are financial benefits to be realized from both your approach and the one taken.
There are financial risks associated with your approach and the one taken.
You have identified potential risks with the current implementation path. Detail those risks, along with a realistic expectation of the chances each risk will convert to actual disaster ("under what conditions would they repurpose a db key being used in what is traditionally a permanent code scenario" - answer that and include frequency). What could cause a key change, and what is the cost of the damage (business lost before the damage is corrected) and the costs of cleaning up after it, including the opportunity cost of committing staff to cleaning up a mess instead of moving the business forward?
For example, switching suppliers of certain products may lead to breaking keys in one feature of the system; the downstream pollution causes inaccurate financial reporting to regulators.
- What is the chance/frequency of this event? Ex: 'We have done this at least once a year for the last six years'. Is this a 'may happen', a 'will happen', or a 'will happen A LOT'?
- List the Time To Detect (TTD): how long can the bad data live and influence data output before it's caught? What would it cost to put a proper bad-data-detection system in place?
- List potential costs of the damage: Will it make a system unusable until fixed? Any regulatory issues? Could it cost you business or force you to write credits?
- List potential costs to clean things up after a key-busting event. Time and people. Will you need to pull engineers from billable work to help with systems work, or pay contractors?
Changing plans now will cost money. Justify the cost. If it's a 3rd party doing the implementation, then your goal is to get them to cost-justify their approach, and may drive further language into the agreement: if they continue down this path, they will have to provide up to some big number contractor hours to clean up messes caused by this decision.
Leaders warm to your ideas when they benefit the business; they become champions of ideas that affect them personally - if someone smells a bonus fading away due to a disaster, they'll help you prevent it.
Be ready with varying 'levels' of switching from their approach to yours. Work has started. Conversations will be more negotiation than technical. What is the minimally-acceptable solution, even if you don't like it? Could this be satisfied with rules coded into a database? If not, why?
Money will likely be the ultimate factor. Spending an additional $20k now to prevent $25k a year in clean-up and lost business is an easy decision. If you're looking at $10k-$20 in costs every 18 months vs. a $50k charge up-front to prevent it, you'll be thanked for your enthusiasm and told to be ready to make the eventual clean-up fast.
Eddie Wuerch
MCM: SQL
March 14, 2025 at 6:13 pm
appreciated eddie. we do it "my way" everywhere else at my company at no extra cost and without any problems. "my way" is really best practice across the industry. to get certified you have to read about this sin. in my case you may also have had to live thru the nightmare somewhere else so you can be credible giving the warning i gave.
at this time, one chief who understands the problem is telling a much higher chief that if there is fallout from the decision we wont be there to pick up the pieces. that higher chief has 19 or so direct reports who rely daily on the accuracy of his dashboard. i may not win this argument but i feel i did whatever i can. it would still be nice to hear from someone who has an opinion on the risk of using a database key as a surrogate for a permanent code.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply