How a BI developer implements basic, yet solid BI strategies.
A finance manager and database administrator (DBA) experience common woes, as they consistently struggle to deliver a basic, daily sales report, to the entire company. Learn how the new BI developer, steps in to save the day, by implementing a basic, yet solid BI strategy that eliminates their woes and sets them up for data and reporting success.
Part 1
Finance Manager, Joe woke up and checked his phone. He hadn’t received the email. His heart sank, as he reflected that he shouldn’t be surprised. He knew a busy morning was coming and wanted to get ahead of the coming fire, so he skipped breakfast to get to work, as quickly as possible. Besides, Joe kept a box of instant oatmeal at his desk for quick mornings! On his way in to the office, he got a text from the vice president of marketing, “So, no sales yesterday, huh? Just kidding!” Joe knew that she was joking, but he wondered if there was a subtext.
The daily sales report had not gone out. Sales had been down slightly the last two years. The board of directors were worried that the declining sales were an indication of stagnation for the company’s products. The company had started changing the design in some of the key products to better appeal to a different demographic. If successful, there would be a shift in the company’s customer-base. The daily sales report was emailed out to every employee in the company. Paul, the relatively new CEO, had been transparent with all of the employees about what these pivots could mean. He wanted everyone to know how the company was doing in regards to the sales targets.
Joe pulled into the parking lot and knew exactly who to talk to. He walked straight over to Emma’s desk, a DBA, but she wasn’t in yet. He decided to sit and wait for her until she did. Typically, she was in the office early. Emma was the best DBA that Joe had ever worked with. He was convinced that there were few, if any, data problems that she couldn’t solve. There were more than a few times that she had come through in a pinch, to make him look like a hero. However, he often wondered, “Why can’t she just make this work?”
Joe waited. He answered emails, got some coffee and then back to more emails. A few people had contacted him, but he was trying to put off his response of “I don’t know what is wrong.” An intern he had never met poked his head out of his cube and told him that Emma had been here until 3:00 AM last night and had turned off her phone, so she could get some sleep. She wouldn’t be in until 2:00 PM at the soonest and might not come in at all.
“Great…now what?” Joe asked himself. Joe was a finance manager, but over the last 12 months or so, he had become more like a de facto manager of reporting and analytics. Whenever there was a question, he would find the answer. This had taken him to places deep in the company’s data. Joe gathered his team and started on Plan B. They would export the data from the EPR and the CRM to piece it together to populate the report. It was a messy time consuming process, but what choice did he have?
The team had done this process a few times and they were getting better at it. Everyone volunteered for a late lunch. Joe hadn’t asked them to skip lunch, but they all knew that this had to get done. Finally at 1:30 PM the daily sales report was sent out.
Joe couldn’t help but wonder, “There has to be a better way!”
Part 2
The effects of Emma’s energy drink had started wearing off. She normally worked late, but not this late. Unfortunately, this was becoming a habit. She really liked working for this company. She had been working there for six years, but certainly didn’t have to work there. Plenty of recruiters had tried to recruit her, but she knew the company’s data so well that she didn’t want to start over.
Her watch had just buzzed and let her know it was 2:00 AM. The SQL for the report was almost done, but she still needed to QA it. She wasn’t sure what it was for, but the financial analyst said it was important and had to be done ASAP. There were three other reports that reported on the same information, but the analyst said that the logic was all wrong on the other reports. To be fair, the number did vary up to 15%, but how big of a difference would that really make?
She promised that the data would be ready by end of day. She reckoned that 2:00 AM was still end of day. She would have gotten it done earlier, but another fire needed attention. There was a scare that the sales system had stopped recording sales. It turned out to be nothing, but her whole day had been spent confirming that nothing was wrong.
As she was QAing her work, she realized that she had written this query before. After looking through a bunch of old tickets there it was. Nine months ago to the day, the same analyst had asked for the same data. She had given him the data eight months ago. She smiled wryly to herself. The last 10 hours had not been wasted. She hadn’t saved the original query and would have had to do it anyways.
This wasn’t an easy query to write. The company’s data was full of subtle little things that can easily mess up even the most carefully written query. At one point, she had started to document all of her knowledge in a shared OneNote. She had even tried to have the finance team write their own queries, but they just don’t know what they are doing. They clearly didn’t read the documentation she had provided. On top of that, the SQL they wrote was messy. It was just easier for her to do everything by herself. The bottom line was that it takes someone with years of experience, working with the company’s data, to get all of the logic right. She is the only one who fits the bill.
She finally finished the data and sent it off to the financial analyst. Emma had hoped that he would see the timestamp and appreciate what it took to get this important data to him, even if she didn’t see why it was so important. She sent off another email to her intern saying she wouldn’t be in tomorrow until the afternoon at the soonest. She had just worked an 18-hour day. To be sure that she got some well-deserved rest, she turned off her phone.
As she drifted off to sleep, she thought, “There has to be a better way!”
Part 3
Joe finally sent off the report to the entire company. He suspected there would be problems. There usually were. The concern was whether the problems would be bad enough that he would get called out on them. He decided that he would stretch his legs before finding out.
He saw Emma, in the lobby, as she was coming in. Joe teetered on whether he should say something now or wait. After all, they had gotten the report off, but it had cost his team 15 people hours. While trying not to sound sarcastic, he said, “Good afternoon Emma.” Emma could sense he was frustrated about something and decided to probe. “Hi Joe! Busy day?” Joe responded, “Yeah, you could say that. Could we go up to your cube and talk about it?”
Once they got to her cube, Joe told her how the daily sales report didn’t go out again and how she really needs to make sure this critical report is always working. Emma took one look at the error code and knew exactly what the problem was. A financial analyst had renamed the folder of the Excel file containing the information on sales targets. The Excel file contains the master list of sales targets. It was also being used for other purposes. As diplomatically as possible, Emma told Joe that this was the fifth time, in the last year, that one of his people had done something like this.
Just when things looked like they were really about to boil over, Emma’s intern came over to save the day. The intern introduced the new Business Intelligence (BI) Developer, Pete, whom the CEO had just hired. No one was sure what BI was; they just knew that Pete was going to do “data stuff.”
A tense discussion followed, as Pete asked Emma and Joe question after question about the current process. What were their challenges and what had they already tried.
As Pete walked away from the discussion, he thought, “I know a better way!”
Part 4
Many of the problems came from having people with the wrong skill set, retrofitting old processes, using tools that were not designed for reporting and analytics. Pete rolled up his sleeves and got to work.
He convinced the CEO to invest in a data warehouse. Convincing the CEO to increase IT spend, during a time of uncertainty, was a tall order. Ultimately, the promise of quicker speed to insight was what convinced him.
The next step was to look at the processes. Many of the processes in place were never meant for supporting the company’s most important report. The company had experienced rapid growth in their early stages. This resulted in a great deal of the systems being “cobbled-together.” Pete looked at all the fail points and manual processes and designed a data pipeline.
The company now had the right people, tools, and processes.
A few months had passed. The daily sales report no longer pulls data directly from the transactional system each time someone opens it. The sales data is batched into the data warehouse. Finance puts a CSV with sales target data into a controlled folder. Pete wrote some code that pulls the CSV into the data warehouse, only when there are changes. If finance ever makes a mistake with the format of the file, notifications are sent out and the changes are not processed.
Pete transformed the data into a star schema. Now, when an analyst wants to create a report, he/she uses the same sales data model that all the other reports use. If a report does not use the data warehouse, then the report is considered wrong.
Getting everyone to agree took a lot of work. Pete got Emma, Joe, and other stakeholders to sit together and hash out common business definitions, including what they mean. After a few, two-hour meetings and many doughnuts, everyone could agree on what the logic should be.
Whenever a new reporting request comes in, Pete now points people to the data warehouse. He confidently knows that all the subtle things that could mess up a query have been ironed out. If the requested data doesn’t already exist in the data warehouse, then he transforms the data and adds it to the data warehouse.
Joe, Emma, and Pete now often reflect, “I knew there was a better way!”
Click here to read this in Spanish.
