This season was created with the beginner in mind. We introduce the core concepts, assuming no prior professional training. Experienced professionals may be tempted to skip this season, but I would encourage you not to – a complete understanding of the foundations will help you grow in the field, much like the deep roots of a tree will help it… grow in the field. Similes aside, the author highly recommends Season 1.
1.1 The Post that Started it All: What is Business Intelligence? In this article we introduce the field of business intelligence, including one way of understanding it as a combination of four broad disciplines. We also introduce the authors and this article series.
1.2 What is a Data Model? In this article we cover how data fits into tables, and how those tables evolve into data models. We introduce the simplest model: a single denormalized table. These single-table models could be all you need to answer your questions, but they can also face certain limitations.
1.3 The Challenges of a Single Table, or: A Star is Born In this article we address the challenges of a single table, introducing the concepts of columnar storage and cardinality. We propose a solution to the challenges: a star schema.
1.4 A Key is Key: Making Relationships Between Data In this article we talk about how relationships connect tables and allow users to filter data. We cover the most common kinds of relationships and how relationships require keys to connect.
1.5 Upstream Solutions to Downstream Problems In this article we cover one of the most fundamentally important concepts in business intelligence. We also introduce the practices of analysis and synthesis, and how they’re used in the field.
Concepts covered in this article: Business Intelligence, Four Broad Disciplines
Whenever I answer the question “what do you do for work?”, the next question I typically get is “what’s business intelligence?” Since those two questions are the very same ones that started me on my journey in this field, I thought it’d be an appropriate place to start this series of articles.
Business intelligence is about having the right information at the right time in order to make informed decisions for your business. The form this information takes can vary widely – ranging from a piece of paper you carry in your pocket, to bank statements, all the way to a set of digital reports that update automatically and answer all sorts of questions. Business intelligence (or, as I’ll refer to it often here, ‘BI’) doesn’t have to apply just to business either; the same tools used by retailers and manufacturers can be used to support public health decisions, research projects, nonprofit causes, or personal goals.
The above definition is what the users experience, and what we at Source to Share work to provide. However, there’s a whole world going on underneath the final reports that the user doesn’t see. That world can be separated into 4 broad disciplines that progress naturally from one to the next.
Four Broad Disciplines of Business Intelligence
Data Prep This is usually the first step in any sort of BI process. Data prep usually starts with literally finding the data (it isn’t always in one place), then looking at it to make sure it’s valid. To prepare the data for modeling, oftentimes it will be changed from one format to another, and tables will be combined, split apart, and/or cleaned up. Then the whole slew will be loaded into reporting software.
Data Modeling This is the practice of organizing data into meaningful groupings (customers, products, sales, etc.) and defining relationships between them. The modeling we engage in is called ‘semantic modeling’, and it enables users to easily locate data (or attributes) and navigate their relationships.
Data Measurement This is the practice of overlaying computations on the dataset. This could be as simple as summing up sales so we can know just how many widgets we’ve sold (and comparing that to our widget budget), or as complicated as forecasting how many widgets we expect to sell next spring.
Data Visualization This is an often-overlooked discipline in BI, but VERY important. Not all visualizations are equally useful, and some, like the common pie chart, are as overused as they are ineffective at conveying information accurately. The reports created in this step are what’s used to drive decision-making, and there is both a science and an art to creating useful, beautiful reports.
It’s important to note that during a BI project, we’ll often jump around from one discipline to the next. After some basic data prep and data modelling, we might start mocking up some crude visualizations. As we run into challenges creating reports, we’ll work to solve them at whatever stage is best. Often it is much more effective to solve problems upstream, which is the topic of an entire article later in this series.
I created this article and the rest of the series as I tried to answer the question ‘what is Business Intelligence?’ in greater and greater depth. They’re designed as a learning tool for myself and others – a way to document my learning and create a space for the rest of the crew at Source to Share to give feedback and fill gaps in my knowledge.
Who am I anyway?
The titular question of this post is what started my journey with business intelligence. I was at a party, and I asked my future coworker Bo what he did for work. He said business intelligence, and I bet you can guess my next question of him. I was drawn in. Several months later I was asking the Source to Share crew if they needed any extra help.
Before I got involved in BI, I had been running operations at an arts nonprofit. During my time there I learned bookkeeping, accounting, finance – I even filed their taxes. My education and experience had been what I’d describe as BI-adjacent. In school I studied psychology, and I focused on study design and quantitative methodology – the how and why of doing statistical analysis. I had always been intensely interested in data, data visualization, and how common errors in either could lead to misinterpretations with often serious consequences.
When I started at Source to Share, I was brought on to learn the BI ropes and help with the increased demand they were experiencing – and then COVID-19 hit. I shifted from learning while looking over the shoulders of our Senior Analysts to learning from afar. We discussed me documenting my BI education as a learning tool, and thus this series was born.
Another facet of my life that’s important to mention – I’m a licensed counselor in Washington State. My training in communication and emotional literacy help me connect with clients both in the counselor’s office and in the BI office, and those skills serve as a scaffold for these articles.
A Note About How Best to Use These Articles
These articles were born out of our collective desire at Source to Share to create learning resources that contribute to the BI community. They track my learning adventure, documenting the topics that were foundational to my growth as an analyst. They provide a sequential pathway to grasping the ‘big picture’ of the field of business intelligence. You can zoom out and take a look at the map here.
It’s important to note that I didn’t learn BI on my own. Self-directed study was a big part of my journey, but throughout the entire process I was aided by my coworkers Bo and Derek. Both are deep wells of knowledge and experience, and I often call them (remember, COVID-19) at all semi-reasonable hours of the day with questions and to check my understanding. These articles are written to reflect that. I cover topics to the best of my knowledge, while Bo and Derek add important technical detail, nuanced corrections, and personal flair. We’ll make sure to highlight who’s talking with text boxes.
Each article starts with three very important lines of text:
The Discipline of Power BI this article (roughly) falls under The disciplines (from above) are Data Prep, Data Modeling, Data Measurement, and Data Visualization. Some articles will span multiple disciplines – in practice they overlap constantly.
Concepts to know before reading this article The concepts that make up the business intelligence world build on one another. Once we’ve covered a topic with an article, we’ll assume understanding of that topic in future articles. If you see terms here that you aren’t familiar with, go back and read the related articles. The learning map can help you find the article you’re looking for.
Concepts covered in this article This line lists which new topics we’ll be covering. I encourage you to organize the notes you take around these headers. Speaking of which, I encourage you to take notes (and I’ll be reminding you to do so throughout).
At the end of each article I’ll Share a list of Sources (see what I did there?) that helped me learn so that you can engage in your own further research. Of course, becoming a professional in the field requires additional study and practice. However, it’s my hope that anyone who really digests these articles will gain a deep understanding of the vocation and carry with them a logic that they can use to reason their way through any BI challenge they face.
I also strongly encourage you to take it slow. These articles weren’t written in a day, and they weren’t designed to be consumed in an afternoon, or even a week. Gaining confidence with business intelligence concepts takes consistent, measured effort. Give yourself time to write notes, integrate what you’ve learned, take stretch breaks, and even share what you’ve learned with others. Bon voyage!
Sign up to receive email notifications when we release new learning content!
Sources
Derek Rickard: Founder, Consultant – Source to Share
We’ve lived for a while now in the ‘how’ of business intelligence. How do we go about organizing our data into a model? What are the pitfalls to avoid, and opportunities to take advantage of? Those are good questions to ask, but it’s actually the ‘why’ of learning that feeds me. Why make a data model? Why do any of this? I want to start with an illustrative story, and then we’ll get to the lesson.
A Personal Story About Alligators – Why Build a Data Model
Remember, when I started at Source to Share I had just left the nonprofit sector, where I had run operations at an arts nonprofit for 7 years. During my time there, I learned many lessons, some of them the hard way. Simply put, our staff was overworked and underpaid. Furthermore, no matter how clearly we dreamed about renovations, updates, and expansions, the day-to-day grind seemed to always get in the way.
I remember when we wanted to switch internet providers to save money, and I had to figure out which of the cables criss-crossing nightmarishly around the building were part of the alarm system, the old internet system, or the CCTV monitoring system. What followed was a herculean effort to categorize, cull, and label the remaining cables. I created a corresponding cable map with the hope that some future arts-lover-turned-administrator would silently thank whoever had come before them.
I watched this story play out again and again while I was there. Dozens of kids were arriving to fill out audition forms for an upcoming show, and there were no pencils. I would run to the store or, if I was too busy with some other emergency, I would call someone to run to the store for me. Then, later, after a long day, we’d try to get down to the business of making sure we never ran out of pencils again. Where do we keep pencils? Who tracks them? Who purchases them?
I remember in the early days when we would put on a show, we’d put a call out to the community for musical directors, choreographers, and stage managers, and when people would ask what the pay was for the job, we’d have to tell them we don’t know. We didn’t know how many students would enroll, and we didn’t have a savings, and we’d only know how much money was left when the stage was struck and the dust settled. We’d try to give the prospective theatre crew an estimate or a range, but we knew in the back of our minds that we couldn’t pay professionals with money we didn’t have.
To quote a saying that’s been used many times in many contexts:
“It’s hard, when you’re up to your armpits in alligators, to remember you came here to drain the swamp.”
We used to talk about this idea a lot at the nonprofit. If only we could get enough help to fend off the alligators, we could finally drain the swamp, eliminating the problems at their source. Rather than constantly dressing alligator-bite wounds, we could build a better arts nonprofit. We eventually did, and I’ll explain how we did that at the end.
Dan’s Book and Stephen’s Blog and the Idea of Synthesis
When I was first starting this article series, I was deep in learning about data visualization. One of the giants of that field is Stephen Few. In my research I came across his blog, and a very powerful idea he was writing about – ‘upstream thinking’. He had just read Dan Heath’s book Upstream, and his review of it turned on a light for me. It’s a concise review, well worth the read. Here’s one quote from Stephen:
“Essentially, Upstream explains and promotes the importance of systems thinking (a.k.a. systems science). Systems thinking involves synthesis, seeing the whole and how its many parts interact to form a system. It’s the flipside of analysis, which focuses on the parts independently and too often gets lost in the trees with little understanding of the forest.”
Much of our work at Source to Share is analysis. We’re analysts after all. However, analysis isn’t everything. What we do is more of an analysis sandwich, with thick slices of synthesis on either end. We’re taking in as much information as we can about the client’s needs and goals, and all the factors that are hindering them. Then we dig in and analyze how to fix each piece. At the end, it’s back to synthesis – seeing how our solutions are fitting their needs in real time.
How to Drain a Swamp
At the arts nonprofit, we were constantly bogged down trying to handle problems when a simple upstream system fix could have eliminated the downstream work altogether. We were running programs as fast as we could in order to make enough of a margin to keep the lights on. We didn’t have time to stop, look at past trends (assuming we even had the data), and build a budget with which to project earnings. If we did, we could tell people exactly what we could pay them, knowing that even if we undersold the house for a particular show, it would balance out in the long run.
Last night, my partner and I were watching an episode of Queer Eye, and I had the realization that Tan, Karamo, Antoni, Bobby, and Jonathan Van Ness are systems analysts. They may not self-identify as such, but they each work on systems, identifying those that are causing downstream problems and creating new supportive systems for the guests on the show. Overhauling systems is a big lift, and the energy that the Fab 5 invest creates efficiencies that result in a massive long-term return.
For a small business, nonprofit, or a research operation, it can be difficult to implement systems overhauls. Training staff (or adding a position) to learn business intelligence or a new program is expensive. You might not have the funds to stop operations for a week and create new systems that will multiply your impact down the road. In reality, you can still make changes even if you don’t have the Fab 5. At the arts nonprofit, with every extra bit of funding or time, we worked on systems. We figured out a supply schedule and got office supplies delivered. We started tracking data and after a year, we built a budget. It wasn’t great, but the next year we had time to build a better budget. The year after that we had enough time to upgrade our budget again, and still had resources left over to make an annual report. There was no single magic fix except diligence. Small changes snowballed over time.
This is exactly how we work at Source to Share. With clients, we meet them where they’re at, with whatever budget they have, and implement as many systems overhauls as we can. I’ve been amazed by what Bo and Derek can accomplish in even an hour or two (I’ll get there someday!). Those overhauls increase efficiency and free up capital, allowing businesses to reinvest in further efficiencies and systems improvements.
Speaking of nonprofits, we do pro bono work at Source to Share as one way to give back to the community. If you’re a nonprofit professional or know of a nonprofit that would benefit from business intelligence services, send us an email or give us a call!
BI Applications
The upstream solutions ideal applies not only to where and when we work, but how we work at Source to Share. We often encounter obstacles while creating reports for clients. By addressing those challenges upstream at the data prep level or with our data model, we’re able to avoid downstream problems in measuring or visualizing our data at the report level. Let’s look at a generalized example of how this plays out…
Having better data models means less complex DAX code (or whatever code you’re using to measure your data), less DAX overall, and less processing load on your computer when you evaluate expressions. Plus, complicated DAX is just harder to maintain. We’ll get into the technical details in our upcoming series on DAX fundamentals. For now, know that measures create CPU load whenever the system is queried (which can happen often as an end user explores a data model), and calculated columns take up space in RAM while also increasing the file size – both finite resources.
By building complexity into the data that’s loaded into the model, rather than into measures performed on the data, the work is being done when Power BI (or whatever BI service you use) refreshes the model, ideally at 3 or 4am each day. Then, when the user is exploring the data, the simplified code is evaluated quickly; the user won’t need to wait as the engine does complicated calculations on 600 million rows. Performance increases and the user can get back to doing the work that they intended to do.
Think of it like trying to build a home with the screwdriver in your pocket rather than going out and buying (or borrowing from the library!) an electric drill. They both work, and even though the screwdriver is immediately at hand, you’re going to save yourself hours and hours of work if you invest a little bit of time or money into the electric drill.
In Conclusion – A Summary of Season One
This brings us to the end of Season 1! In this season, we’ve taken a broad view of business intelligence in five articles:
These articles covered the upstream concepts and principles that will remove downstream problems for you as you grow in this field. I’ve included a summary below, telling the story in brief while highlighting the important concepts in bold:
We talked about the four broad disciplines of BI: Data Prep, Modeling, Measurement, and Visualization. Then, we learned how data fits into tables which evolve into data models, starting with basic denormalized tables. These single-table models could be all you need to answer your questions, but they can also grow past limitations to become star schemas–this process is called normalization. The process separates data into fact tables and dimension tables. In order to understand why tables can grow long (but not wide), we learned about cardinality and columnar storage. Our new star schemas rely on relationships between tables in order to filter the data, and so far we’ve covered directional relationships and many-to-one relationships. These relationships connect tables using keys. We learned the different names for keys we might encounter out in the wild. Finally, we spent some time discussing the why, how, and what of BI, pairing these tools with real-world needs. In doing so, we learned a new way of understanding BI work: as a combination of analysis and synthesis.
We’ll be diving into more of the technical bits in future seasons, and as we do, I encourage you to keep the underlying purpose of BI in your mind:
“Business intelligence is about having the right information at the right time in order to make informed decisions for your business.”
-Me, from “The Post That Started it All”
It’s easy to get lost in maximizing a formula or creating the ‘perfect’ data model (if you ever see one, please send it to me). What’s harder is keeping the purpose of BI close at heart, and doing no more and no less than is necessary to move your endeavor forward (or help your client do so).
Thank you for taking the time to invest in yourself by reading these articles. By participating, you not only broaden and enrich the field, you also honor my contribution. You’ve taken the first steps along a profession that’s having a profound impact on the world. We’re in the business of efficiency, of eliminating wasted effort and resources, and creating clarity. In a literal sense, we’re on this journey together. I’m writing as I learn, and I’ve already started work on season two. I hope you’ll join me.
Sign up to receive email notifications when we release new learning content
