In our last episode, the Acme team talked to Forkleworks, a Microsoft Power Platform partner, and several discoveries were unearthed. It was agreed that pursuing a relational database approach would provide a better solution, and the team was waiting on cost estimates from Forkleworks.
Art received the email from Scott at Forkleworks with the estimates. He set up a meeting to go over it with Sally and Rupert.
“Good morning!” said Sally entering Rupert’s office smiling, “It’s a lovely day, isn’t it?.”
“Why are you so chipper this morning?” asked Art.
“Just hopeful about getting my problem solved,” said Sally, “We’re going to solve it, right?”
“That’s what we’re here to decide,” said Art, looking at Rupert, who was reading something on his monitor. “You ready boss?”
“Yes…sorry,” said Rupert, “I was just reviewing the email you sent over from Scott.”
“Annnnd?” said Sally.
“The cost is a little more than I would ordinarily spend to solve a problem like this,” said Rupert, “the ROI isn’t there.”
“How much would you ‘ordinarily’ spend?” asked Sally, “on a ‘problem like this’?”
Rupert chuckled, “‘Ordinarily… nothing.”
“I think that’s what Microsoft is trying to tap into here,” said Art, “a way for people like Sally to solve a problem for which there would never be a budget.”
“Agreed,” said Rupert, “unfortunately, what we learned was that even if Sally had been successful in making something, it would have been the wrong thing.”
“Yeah,” said Art, “not scalable.”
“Sally, can you show me that Master Spreadsheet?” asked Rupert.
“Sure.” said Sally, “give me a minute.” as she headed back to get her laptop.
Rupert turned to Art, “Sally is one of the most capable HR people I have ever worked with,” said Rupert, “but I don’t know why Microsoft thinks she can or should build an App… I couldn’t. They really should have pointed this technology at people like you.”
“Oh yeah,” said Art, “I got all kinds of time”. They were both laughing as Sally re-entered.
“Something funny?” Sally asked.
“Just Microsoft’s strategy,” said Rupert, “but I have spent the past two days exploring the Power Platform. It seems much more extensive and capable than what we had been thinking.”
The three of them moved into the small side conference room and Sally opened the Master Spreadsheet on the wall monitor. Rupert and Art studied it.
After a few moments, Rupert asked Art, “Have you seen this before?”
“Nope,” said Art, “no reason for me to.”
“Can you scroll to the right?” Rupert asked Sally.
“Sure,” said Sally, “How far?”
“How far does it go?” asked Rupert.
“Oh it goes quite a ways,” said Sally, “each row contains all of the HR information about our employees. And this other tab here has all of Bob’s calculations, I can explain them to you if you like.”
“No, I think I’ve seen enough,” said Rupert, “Art, call Forkleworks and give them the green light”.
“Sure,” said Art, as he and Sally headed back to their offices.
“Hi Scott,” said Art, when Scott answered his call, “It’s Art”
“Hi Art,” Scott said, “Let me grab Iliad.”
“That won’t be necessary,” said Art, “just calling to give you the go-ahead.”
“Great news!” said Scott, “We’ll get a team mobilized and we’ll start sketching out a plan. Give us a few days and we’ll share our thinking.”
“Sounds good,” said Art “I’m available most anytime next week.”
Tuesday morning, Art calls Rupert. “Hey Rupert,” says Art when Rupert answers, “we have a call in an hour with the Forkleworks team, I was checking to see if you wanted to join.”
“Ordinarily,” said Rupert, “my other duties would be more pressing than Sally’s app, but as I said, I have a bigger picture in mind, so yeah, I want to sit in on it.”
“I’ll forward the invitation to you.” said Art as he hung up and walked down to Sally’s office.
“Hey Art,” said Sally as he walked in, “what’s up?”
“We got that call with Forkleworks in about 45 minutes,” said Art.
“Yup, I’m ready,” said Sally, “I’m a little surprised that Rupert went along with it. Particularly with you not sounding very supportive.”
“What do you mean?” asked Art.
“Let’s just say your nose has gotten a lot more brown since we started this,” Sally replied.
Art chuckled, “Hey, he signs my check. If he wants to run down this rabbit hole, who am I to stop him? I just hope he doesn’t dump all of this crap on my desk later.”
“Umhm,” said Rupert from the door behind Art, “Let’s meet in my conference room for this call.” And Rupert turned and left.
Art did not turn around, he just whispered, “Yup.”
“I wanted to introduce Itor and Marlik from our team,” said Scott as the call began, “and of course, Iliad”.
“Nice to meet you two,” said Rupert, “Art, why don’t you take the lead for our side of the call?”
“Sure,” said Art sheepishly.
“So I had a call with Sally the other day,” said Iliad, “to dive into this Master Spreadsheet a little more. It seems about 20 people have access to this spreadsheet, basically the whole HR department, from Sally the head, all the way down to some interns.”
“Yes,” said Sally, “like I said, it is the primary tool our department uses.”
“Understood,” continued Iliad, “however there is quite a bit of, what we would consider, confidential information in it.”
“Like what?” said Rupert.
“Well,” said Scott, “I know that you received a score of 84% on your last performance review, and you only have three vacation days left… and all of your interns know this too.”
Art smiled to himself, and Sally said, “Yeah, that is not good”.
“There are a lot of factors that go into a Performance review,” said Rupert, “but clearly this is not data that should be accessible to interns! Sally, Until this spreadsheet is replaced, you need to lock it down!”
“Sorry if I upset you, Rupert,” said Scott, “I just needed to drive a point home quickly.”
“Mission accomplished!” said Rupert, “so show us your proposed solution.”
“Sure,” said Itor, “First we propose moving the spreadsheet, the whole process really, to a model-driven Power App. This will run on Dataverse, which among other things, provides role-based security, so you can decide who sees what.”
“Okay,” said Rupert, “wait a minute. Does this mean our other spreadsheets have this same issue?”
“It’s hard to say,” said Scott, “without knowing what data lives in them and who has access to them.”
“Jesus H Christ!,” said Rupert, “Art, you need to do an audit of every spreadsheet in the organization… ASAP. How’s that for dumping crap on your desk?”
“Sure thing boss!” said Art, hiding behind his laptop screen.
“Sorry Itor,” said Rupert calmly, “please continue.”
“No worries,” said Itor, “Marlik will go over the proposed data model”.
“Thanks, Itor,” said Marlik, “Basically, we propose breaking these long rows into separate tables, all related back to an Employee record. For example, you see these twelve columns here; there are three columns for each of four certifications; one for the name, one for the date obtained, and one for the date it is to be renewed. That will become a small table called certifications with these columns, related back to the employee record. So each certification will be a record.”
“So,” said Sally, “That means we could actually have more than four certifications?”
“Absolutely,” said Marlik, “you could have as many as you want.”
“That’s great,” said Sally, “we have some people that have more, and I was afraid to just add columns to Bob’s spreadsheet and possibly break it; so I have been using the notes column to record these.”
“This is one advantage of a relational database,” said Marlik, “We also noticed that Bob had created a macro to change the background color of the renewal date cells when they become due. However, if you don’t scroll far enough to the right, or up and down the entire spreadsheet, they could easily be missed. There’s over a thousand rows in this.”
“Yeah,” said Sally, “we sort by the status column, and so most of those at the bottom are former employees. We need to keep the data for a while. Also, staying on top of the certification renewals has been a continuous challenge.”
“We plan to solve those with the platform,” continued Marlik, “first we will be creating filtered views, so you can more easily focus on active employees. We will also create a workflow to automatically delete old employee records when the retention time arrives. We’ll also create a view of expiring certifications, but just to be sure, since this is key for your compliance, we will create a workflow to alert the employees, and their supervisors, 30 days before the renewal.”
“That sounds awesome,” said Sally, “what will my team need to do to make these alerts happen?”
“Nothing,” said Marlik, “it’ll be automated”.
“Ha!” shouted Sally, “I knew automation would help!”
Marlik and Itor went on to describe the rest of the proposed solution, and then Rupert asked, “How long will this take? I would really like to have something in place within four or five months, or sooner if possible.”
“Would three weeks be too soon?” said Scott.
Rupert smiled broadly and said, “I’m starting to really like you guys. Please proceed,” and the call ended.
Check out for the next exciting episode of “Sally in HR has a Business Problem”.