The BI Journey: The Journey Begins

The series so far:

1. The BI Journey: The Analyst
2. The BI Journey: The Expert’s Advice – Part 1
3. The BI Journey: The Expert’s Advice – Part 2

Stephen, the sales manager, was impressed with the work of his intern, Ruthie, the Business Analyst. She had taken a seemingly off-hand conversation about how Stephen’s friend was looking at using analytics to help drive business, and within a couple of weeks churned out her solution for Stephen’s department. Stephen, who heads Sales for AdventureWorks had always had a problem trying to figure out how well various parts of the department were functioning. With the business intelligence solution that Ruthie came up with, Stephen now had some idea about how the department was faring. There was a lot more he wanted to know and had ideas that he could implement if he had the right information. However, from what Ruthie had given him, despite it being a small report, Stephen could see a lot of value – and that was the key. He wanted to capitalize on it.

From Value to Ideas

He felt that Ruthie had put in a lot of genuine effort into the solution that she had built. She had taken initiatives, even as far as consulting with folk from the technical community, while doing much research of her own. More than anything, he found that she had a passion for what she had been doing all along. Since the sales department had never had a business analyst before, Stephen decided he would hire Ruthie as the department’s permanent business analyst.

For now, Stephen wanted to give Ruthie more responsibility. After seeing the capabilities that Power BI provided, and how they could utilize its various features, he wanted a complete solution so that the entire sales department would be data-driven. This included Stephen being able to build reports on his own and the sales team running the quarterly sales meeting from a single dashboard. Ruthie would manage the solution including development, standards and governance, and routinely enhancing and improving the solution. She would also train the sales personnel on how to use the solution and create reports.

Ruthie was overwhelmed and overjoyed at the same time when Stephen told what he had in store for her. She accepted the responsibilities without giving it a second of thought.

Going Big

One of the things she learned about during conversations with George, her mentor, was data warehouses. Data warehouses are large databases that consolidate information from different systems and make the data available for business intelligence purposes. Much like a Power BI data model thought Ruthie. A data warehouse is considered the single source of truth across the organization for business intelligence. She had attended a meetup of the local data community and had been quite taken by the presenter that week. Lisa, a data architect at a consulting firm, had talked about data warehousing and business intelligence in great depth.

Ruthie was even more convinced about the need for a data warehouse when she received a text message from Stephen while she was at the meetup: “Ruthie, I’ll need you to give marketing access to our BI solution. I’m getting some funding from them 😊”

She had spoken to Lisa after the meetup and inquired about getting started with a data warehouse at her workplace. She told her about her new responsibilities, and that the talk had inspired her to build a data warehouse for the sales department.

Lisa had loved Ruthie’s enthusiasm and had been impressed when she heard about the responsibilities given to her and even more at the level of attention Ruthie had paid to what she had presented. But she had told Ruthie to hold on and had proceeded to explain why she should not be looking at a data warehouse at this stage.

Data Warehouse – Not Now

Lisa had explained to Ruthie that a data warehouse needed a lot of effort to build, which required a considerable amount of time spent on gathering requirements for the right set of dimensions and facts, for the measures and KPIs that the users needed, the calculations for these measures and KPIs, and the reports that users wanted.

A data warehouse also needs a data integration component that routinely brings in data and transforms it to fit the data warehouse. Building a data integration component to satisfy all the requirements takes quite a lot of effort. Data integration is considered to be the most significant component of a data warehousing solution in terms of effort and complexity to build. It’s so much effort that teams often segregate it as a separate project in the data warehousing solution.

Talking from experience, Lisa had explained that more often than not, requirements keep changing, especially when users are looking at reports and suddenly want something that is not there. If the requirements for the dimensions and facts were not correctly defined, or if the users came up with a change to the logic, you would find yourself repeatedly modifying the data warehouse, and then modifying the data integration process to match the changes you did on the data warehouse. All this would then elongate the time taken to build your data warehouse.

Therefore, Lisa had told Ruthie, if you were even looking at building a data warehouse incrementally, you would need to be sure of all the requirements of at least one logical chunk of the data warehouse. This way you could develop it, test it, ship it, and then move on to the next chunk. However, it’s not feasible to develop this one chunk iteratively as a data warehouse. Micro-iterations are a no-no. In Ruthie’s case, said Lisa, since it was just Ruthie, and a bunch of salespeople, a data warehouse was out of the question. Micro-iterations were part of the deal here.

Finally, Ruthie would have to learn at least a couple of new technologies. The most straightforward implementation of a data warehouse would require Ruthie to learn a database management system such as SQL Server, querying basics, a querying language such as T-SQL, an integration technology such as Integration Services, and configuring all of these technologies, explained Lisa. Alternatively, if she were looking at the cloud, Ruthie would need to understand how things worked on the cloud, pricing for the technology services that she was planning on using, a database management system such as Azure SQL Database, integration technologies such as Azure Data Factory and Integration Services, along with a querying language like T-SQL. Either that or Ruthie needed to have at least a couple of developers who had the experience to do the work. This was not a luxury that Ruthie or Stephen had.

Lisa advised Ruthie that a data warehouse was a long way from what was needed for the Sales Department. What she needed was a solution that provided value fast. To do that, she needed a familiar tool that allowed quick development and allowed her to make updates fast. She also reminded Ruthie about technology costs which were something Ruthie had not considered.

Lisa’s advice to Ruthie was:

• Leverage Power BI and sketch out an outline of a solution
• Figure out the costs for the solution, and get approval
• Set up a list of tasks in a backlog
• Get started!

Preparation

Ruthie was pleased about her conversation with Lisa. Lisa had volunteered to help, and Ruthie was ecstatic that she had another mentor. Being part of a technical community had many benefits. She hoped one day to give back.

She met George (who was good friends with her by now) after the meetup for coffee and to run through the notes she had jotted down.

The first thing that Ruthie had done was to put down a backlog of high-level steps:

1. Solution outline
2. Calculate cost
3. Plan the development
4. Develop solution

Solution Outline

Looking at the requirements that came from Stephen, Ruthie had already understood that creating a separate data model each for each report was not going to work. That’s why she had been thinking of a data warehouse in the first place. However, after the “consultation” she had with Lisa, she now looked at replicating the same concept using the tools she was quite familiar with, in this case, Power BI. Ruthie’s idea was to build a data model that encompassed all the data required by the sales department. Currently, she had one data model built off three dimensions and a couple of fact tables. The facts on these tables had measures built on top of them, and Ruthie planned on extending this data model further. She had a plan for the flow of the solution, so she put that down.

Figure 1: Solution outline

The flow of the solution was quite simple:

1. The solution administrator will develop data models and deploy to the data model store.
2. Sales and marketing users will build reports using their respective data models and publish these reports in the trial workspace.
3. After reviewing the published reports, either on a scheduled or as requested, selected reports will be published to the main workspace of the respective departments.
4. Any newly identified measures, metrics, and even dimensions modifications will be pushed to the next iteration of the data model.

The solution administrator runs the show, and Ruthie decided to assume that role.

She then put down the components of the solution in a diagram. George liked it the moment she showed it to him. He called it an architectural diagram. Ruthie beamed. Grasshopper had learned fast.

Figure 2: Architecture

Cost

When she thought about the cost, Ruthie cringed. She had always been uncomfortable when it came to handling others’ money, including telling others how and how much they should be spending. Figuring out the cost of technology was something new to Ruthie. However, since she was looking at a simple solution and one that was made up of a tool she knew quite well, she decided that she could figure it out.

She used the architectural diagram to list the technologies that were needed, counted the number of users that required the software, and performed some basic math. Ruthie chose Power BI since she had already started using it and was quite the expert with it now. She ran it by George, and he said that it looked good.

Figure 3: Technology cost

Plan

With the new responsibilities that Stephen had given Ruthie, he expected visibility on the progress. Hence why he wanted her to give him a plan. Ruthie was not sure how to plan for the work since all the work Ruthie had done previously had been ad hoc.

She decided that the most sensible thing she could do was to take a logical approach and list the high-level items she planned on doing. She then did a guestimate of the time it might take for each task. She then grouped up these tasks with a 15% buffer to fit into three weeks. She called each three weeks an iteration, and came up with a plan for the next two months:

Iteration 1:

1. Orders (with Customer, Product, Salesperson, Date)
   • Configure the current solution to use the sales database instead of files
   • Port the solution to fit the new architecture
2. Train pilot sales personnel to build reports from the published data model
   • Pilot sales personnel will start building reports after this
3. Complete Customers, Product, Salesperson dimensions with all possible attributes and hierarchies
4. Sales personnel to provide feedback

Iteration 2:

1. Add three new complete dimensions
   • Dimensions to be identified based on sales personnel priority
2. Build measures that make use of new dimensions (if any)
3. Deploy changes to sales and marketing workspaces
4. Review sales personnel’s reports and target reports for promotion to the main workspaces from trial workspaces
5. Plan new measures, metrics and dimension modifications to include in the next iteration

Iteration 3:

1. Add three further complete dimensions
   • Dimension to be based on sales personnel priority
2. Build measures that make use of new dimensions (if any)
3. Add new measures, metrics and dimension modifications identified from reports built in Iteration 2
4. Deploy changes to sales and marketing workspaces
5. Review sales personnel’s reports and target reports for promotion to main workspaces from trial workspaces
6. Plan new measures, metrics and dimension modifications to include in the next iteration

The next day she presented the solution overview, cost matrix and plan to Stephen. Stephen liked what he saw and gave Ruthie the go-ahead to start implementing the solution the following week. By then he would have the budget organised for the project and the technology licenses in hand.

An Exciting Beginning

While a data warehouse is an excellent solution for an organisation’s business intelligence platform, it almost always requires a lot of effort to build and requires a significant cost. However, a data warehouse, a central repository of analytical data, should be an organisation’s goal if they are serious about analytics and business intelligence. A great way to get there would be to capitalise on the success of self-service business intelligence due to its agile and rapid development capabilities and channel the value it generates as input to build the data warehouse.

Ruthie had now proven herself capable and was given the responsibility of implementing the division’s business intelligence solution. Though small in scale, the scope for growth was quite big, Stephen, her boss, could see his business vision coming together, and he was glad that he had someone bright and passionate like Ruthie by his side. Something that had started as a lunch table conversation a few weeks ago had materialised into this: First a couple of reports, now the beginning of a solution. AdventureWorks’ BI journey had begun.

Note: To discuss the technical aspect of the ideas laid out in this article, await a companion article to be published soon.

The post The BI Journey: The Journey Begins appeared first on Simple Talk.

from Simple Talk http://bit.ly/2J90gM3
via