The star schema is at the heart of a good business intelligence system. This course will teach you the foundations you need to build a star schema and deliver powerful business intelligence to your users.
At the core of business intelligence is a thorough knowledge of star schema design and architecture. In Star Schema Foundations, you will learn how to model a star schema after a business process and convert your transactional database into an analytics powerhouse. You will learn dimensional modeling, the Kimball Method, dimension table design, and finally fact table design. When you're finished with this course, you will have a foundational knowledge of star schema design and architecture that will help you as you move forward towards developing business intelligence solutions.
I work as a feedback mechanism for organizations and teams to help them understand what’s going on with their products and processes. I do this by collecting and organizing their data, visually exploring it, enriching it with other data and metrics, then presenting my findings using creative information design techniques. This leads to improved business performance and often sparks a data-driven culture throughout my clients organizations.
Course Overview Hi everyone, my name is Ben Sullins, and welcome to my course, Star Schema Foundations. Providing data for analytics to improve your business can be a daunting task. Our application databases just aren't designed for easy access by analysts. This is why you need a star schema. By creating a star schema, modeled after a business process, maintenance will be low because it'll be representing how the business person wants to think about data, and how to use it for their business process. And it'll eliminate the complex sequel they need to write in order to get the answers that they seek. Now in this course I get you started with understanding how to build start schemas, as well as the context behind them, and what types of problems they can really help with. Some of the major topics that we'll cover include: the process of modeling our transactional databases into a star schema, building fact and dimension tables for easy analysis, and querying our star schema to make it easy for our analysist to use. By the end of this course you'll have a fully functioning star schema built on top a of real world example. This method has been tried and tested for over 30 years, and is still today the most popular way to deliver analytics to our business users. I hope you'll join me on this journey to learn the foundations of star schemas.
Getting Started Hi, this is Ben with bensullins. com, and welcome to this course, Start Schema Foundations. In this module, we're going to talk about our course goals, the flow of the course, how we're going to actually walk through each section, the story we'll follow, and we'll do a sneak peak of our final project so you can see where we're headed, and then we'll take a look where we can find more information. Let's get going. The goals of the course are to understand the theory behind star schemas, to practice making them, actually building a star schema, and to understand the real world context. Some of this is great in theory, but doesn't always work in practice. So I want to cover the whole spectrum here, so you understand what's realistic, what's not, and how you can actually use this new skill to improve your business, and deliver greater insights with a good foundation of a star schema. The flow of the course is like this. We're going to start talking about the BI ecosystem, the Business Intelligence system, and all the components that are needed to actually deliver this. Star Schema is the foundation of the data warehouse, I would say, and that's not just me, that's a lot of folks that would agree with that, but also there's other components, really important things to give your users something they can actually use to deliver insights, and improve their business. We'll talk about the design of a star schema, and how to actually build that. We'll go through dimensional modeling and the process of dimensional modeling, which is to say the process of building star schemas. And lastly we'll look at querying star schemas. In the end I just want to give you some functional examples of how to use what you've then built.
Understanding the Business Intelligence Ecosystem Hey everyone, this is Ben with bensullins. com. Welcome to this module, Understanding the Business Intelligence Ecosystem. What we're going to cover today, first we're going to talk about the architecture of the overall system. Then we'll talk about data warehouse architecture specifically, and this is where our star schema really comes into play. And then we'll talk about the front end architecture. So, the things that are going to be using our star schema to deliver insights. Let's get started.
Understanding Star Schema Design Hi, I'm Ben with bensullins. com. And welcome to this module on Understanding Star Schema Design. First, we're going to take a look at just what is the star schema, get the overview of it, so we can see a lay of the land, and figure out how to dive deeper. Then we'll try to understand why we would use dimensional modeling. Star schemas are the product of dimensional modeling. So we'll understand what that is, and why we would choose that method. We'll go into the history here. I think it's always important to understand the background of how we got here, so that way we don't repeat those same mistakes again, and we understand why this is sound thinking, and why we would want to pursue this avenue. Then we'll look at how to join facts and dimensions. Fact and dimensions are the two critical parts of our star schema, and it's important to understand how we actually join them. We'll actually get to write some SQL here, so you can see, kind of, in action what it would be like to use the star schema. Let's get going.
Modeling Your Data into a Star Schema Hi this is Ben, with bensullins. com, and welcome to this module on, Modeling Your Data into a Star Schema. Here we're going to start by taking a look at the Kimball Method. Kimball is the one who came up with the star schema, and his method is how you actually take your business process and your data, you combine those into a star schema, or a dimensional model. We'll look at the overview of the actual process of dimensional modeling. We just took a look at it in a previous example, here we'll get into more details about that. And then we'll actually build a simple star schema. We'll just take a look at one example, and see how easily and quickly we can build a start schema.
Choosing a Business Process Hey, this Ben with bensullins. com. Now let's take a look at Choosing a Business Process. The first thing, I just want to put out a simple definition of what is a business process, and what do we mean by that when we're talking about it in terms of a star schema design. I'll go through the object-action model, this is often the way we discuss or talk about business processes. If you're building a website too, this is also a great way to think about how you track events on a website. We'll talk about a subject area map, and this is a really interesting way to have a holistic view of your business and how it actually functions. And then we'll talk about prioritization. I have a portfolio approach I've been using for years, and I'll present that here, as well as the context behind it. Let's get going.
Defining the Grain Hey, this is Ben, with bensullins. com, and welcome to this module on, Defining the Grain. We've got, kind of, three things we're going to do here. We're going to talk about what defining the grain means, figuring out what the atomic grain is, and then we'll actually go do it. We're going to go find the grain in our Adventure Works database. Let's get started.
Identifying Facts Hi, this is Ben, with bensullins. com, and welcome to this module on Identifying Facts. What you're going to learn in this module is first an overview of fact tables. We'll talk about the transaction fact tables, periodic snapshot fact tables, accumulating snapshot fact tables, and then we'll go through the exercises of actually building a fact table, building on the previous example we worked on in the previous module. Let's get going.
Identifying Dimensions Welcome to this module on Identifying Dimensions. I'm Ben with bensullins. com, and let's get started. First, we're going to take a look at an overview of dimension tables. We'll take a look at the architecture of a dimension table, what are the key bits that you need in it for it to work properly. Then we'll take a look at dimension hierarchies, and how we can give our users drill down capabilities within our star schema. We'll go through the process of building a date dimension. This is a real special one, and I'll provide some demo examples for you to use in your star schema. Then we'll go through and build our denormalized dimensions, taking our example we've been working on throughout this course, and we'll expand upon it by adding all the dimensions we need to answer the questions for our business process. Let's get going.
Querying a Star Schema Hi, this is Ben with bensullins. com, and welcome to this module on Querying a Star Schema. This is a pretty simple module. What we're going to cover is querying one dimension in a star schema, querying multiple dimensions, and creating a view for ease of use afterwards. Let's get going.