Stop me if you’ve heard this before, but self-service BI is all the rage these days. I honestly can’t remember the last time I had a conversation with a customer that didn’t involve self-service.
I mean, it makes perfect sense. Business users are the ones who know their data the best. They’re the ones saying “why do I have to keep waiting weeks or months for IT to provide me insights on a topic I know better than anyone in my organization?” The answer is simple and one we all know: for years we’ve had to rely on IT because of the technical knowledge required to access, clean, model, explore, visualize, and distribute data insights.
That’s what we call corporate BI, and those days are behind us. Enter self-service and end user BI.
With all the buzz around making data-driven decisions, I’m going to make a bold and unverifiable statement, but one that I wholeheartedly believe to be true:
Every organization on the planet is considering or executing an initiative to empower business users to discover their own insights without the cost and dependence of having to rely on IT.
The concept is simple, but going from “this is how we’ve always done it” to “let’s try something new” is a massive change in culture, and change, as we all know, is difficult. It costs money. It takes time. People have to adapt. Processes must be redefined.
Adoption increases through empathy, education, and empowerment.
And that’s where we in the BI community want to help.
We’ve all talked to those fancy consultants who come into a room and blindly throw around terms like “dimensional model”, “star schema”, “data warehouse”, and so on. Hell I’ve been one of those people…and probably still am more often than I’m going to admit here.
I get it. Everybody is busy and taking on additional reporting responsibilities as well as learning new skills takes time, and nobody has any free time these days. No worries, we’re going to keep it simple.
To start, a data model is simply a collection of data elements, organized in a manner based on how they relate to one another in the real world.
There are always multiple ways to skin a cat, but in the BI world the dimensional model is the king of data models. It’s the easiest to understand and provides the best reporting experience, so it’s the one typically recommended for analytics needs. You don’t need to know or follow all dimensional modeling best practices – just the basics, as listed below:
- A dimension is a table or entity that typically answers a who, what, where, when, or how question of a business process. Each row represents a unique set of descriptive attributes of the dimension, with each attribute being expressed in a column.
- A fact is an individual measure, a single, numeric representation of an event that occurred as part of a business process – they often answer the how many or how often questions.
- A fact table represents the actual business process. It’s simply a grouping of all associated facts, with each row representing an event in the process and each non-key column representing an individual fact.
- Each table has a primary key (PK) column(s) that uniquely identifies each row and, ideally, never changes.
- A foreign key (FK) column represents a primary key of a different table, and is used to create relationships between multiple tables
Let’s take, for example, a simple financial business process such as stock purchases. Anytime a customer purchases stock, the investment firm must be able to answer simple questions about the transaction.
- Who was the customer?
- What company’s stock was purchased?
- Where was the stock purchased?
- How did the customer pay?
- When did the transaction occur?
- Who was the broker?
- How much did each share cost?
- How many shares were purchased?
In this example, Stock Purchases is our fact table, and our dimensions (and attributes) are based on the above questions, such as: Customer, Company, Location, Payment Type, Date, and Broker. We model our data (as shown below) so that we capture this information every time an event in our business process occurs.
The whole purpose of business intelligence is to answer questions regarding the collective past, present, or future health of a business. In this context, think of facts as what you group, and dimensional attributes as what you group by. Knowing this concept and utilizing the above model is all you need to start answering business questions, such as:
- How much stock did we sell last year? (Date)
- Who were the top ten highest performing brokers? (Broker)
- In which states did we have the most transactions? (Location)
- Which customer purchased the most shares of Microsoft stock? (Customer, Company)
In terms of concepts, that’s really all you need to know. Your data set may be simple enough as a single Excel table, or it might be massive and spread across multiple, disconnected data sources. No matter, I’d encourage you to keep thinking of the questions you want to answer, and model appropriately.
So how do we get data into the model? This is where technology is making that process easier. Let’s revisit the quote from the beginning of this article:
For years we’ve had to rely on IT because of the technical knowledge required to access, clean, model, explore, visualize, and distribute data insights.
With tools like Power BI, every user now has the ability to follow the entire BI process in a single platform.
If you’re new to business intelligence and Power BI, or you want to learn more about how to apply the concepts we learned above, take a look at my Helpful Links page. I’d recommend beginning with the Avi Singh or Guy in a Cube YouTube channels.
And if you’re looking to increase Power BI adoption within your organization, I’d strongly consider looking into Microsoft’s Dashboard in a Day program. These are one day training workshops hosted by Microsoft employees and partners that are incredibly valuable to end users, business analysts, and BI developers. Oh yeah, and they’re free! See upcoming events that I’m hosting here.
Have a comment to share or a question to ask? Please don’t hesitate to reach out.
And as always, Go Dawgs.