This is part of a series of articles in which I’m trying to teach myself about BI. Any related articles I write are preceded with “#BI101” in the title.
As a speaker on the SQL Saturday circuit, I’ve had the honor and privilege of having met, connected with, and even befriended a number of experts in SQL, data, and BI. If you can get to get to a SQL Saturday, you can also have that opportunity.
In a couple of weeks (July 28), we will be hosting SQL Saturday here in Albany, NY. I was going through the schedule, and noticed a number of speakers on the docket who will be talking about various BI topics. I’ve attended a lot of their sessions, and I recommend these speakers highly!
(Note: for purposes of this article, I am limiting this list to BI topics, although these speakers may be giving other presentations as well.)
SQL Saturday is a great free learning resource, a great opportunity to network, and is always a good time! If you’re looking to learn about BI or other data-related or professional topics, go check out a SQL Saturday event near you!
This is part of a series of articles in which I’m trying to teach myself about BI. Any related articles I write are preceded with “#BI101” in the title.
Because this is a new (to me) topic, it’s possible that what I write might be inaccurate. I invite you to correct me in the comments, and I will make it a point to edit this article for accuracy.
As part of my personal education about business intelligence, I kicked off a SkillSoft course made available to me through my employer. My strategy is to take the course, perform some supportive research, and write about what I learn. It turned out that BI was one of the training options available. So, I kicked off the course and began my training.
The initial topic discussed the concept of data warehousing. The course began with a pre-test — a proverbial “how much do I really know?” There were a few subtopics that were familiar to me — normalization and denormalization, for one — so my initial thought was, how much do I have to learn? As it turned out, the answer was, a lot.
What is a data warehouse, and what does it do?
The short and simple answer is that a data warehouse is, as the name implies, a storage repository for data.
That’s the short answer. The longer answer gets a little more complicated.
I learned that a lot of the concepts behind a data warehouse pretty much breaks a lot of what I thought I knew about relational databases. For starters, I’ve always been under the impression that all relational databases needed to be normalized. However, I learned that, in the case of a data warehouse, that might not necessarily be the case. While a data warehouse could be normalized, it might not necessarily be. While a normalized database is designed to minimize data redundancy, a denormalized data table might have redundant data. Although it occupies more space, having redundant data reduces the number of table joins, thus reducing query time (as the SkillSoft lesson put it, sacrificing storage space for speed). When a data warehouse is storing millions of rows of data, reducing the number of query joins could be significant.
Populating a data warehouse
How does data get into a data warehouse? It turns out that a data warehouse can have multiple data sources — SQL Server, Oracle, Access, Excel, flat files, and so on. The trick is, how does this data get into a data warehouse? This is where the integration layer comes in.
Because the SkillSoft course I took was SQL Server-specific, it focused on SSIS. SSIS provides tools that allow it to connect to multiple data sources, as well as tools for ETL (Extract, Transform, Load). ETL involves a process that includes obtaining data from the various sources and processing it for data warehouse storage. A big part of ETL is data cleansing — formatting data so it is usable and consistent. For example, imagine that several data sources use different formats for the same Boolean data field. One uses “1” and “0”, another uses “T” and “F”, another uses “Yes” and “No”, and so on. Data cleansing formats these fields into a single, consistent format so that it is usable by the data warehouse. As there are many such fields, ETL is often a very long and involved process.
Just the facts, ma’am…
I had heard of fact and dimension tables before, but I wasn’t entirely sure about their application until I started diving into BI. In a nutshell, facts are raw data, while dimensions provide context to the facts.
To illustrate facts and dimensions, let me go back to one of my favorite subjects: baseball. How about Derek Jeter’s hitting statistics? Clicking “Game Log” displays a list of game-by-game statistics for a given season (the link provided defaults to the 2014 season, Jeter’s last season). Looking at his last home game on Sept. 25, Jeter accumulated two hits, including a double, driving in three runs, scoring once, and striking out once in five at-bats. These single-game numbers are the facts. The facts are these raw numbers that Jeter generated in that single game. A fact table stores these individual game statistics in a single table. Dimensions provide context to these numbers. Some dimensions might include total accumulated statistics for a season, batting average, and some (non-statistical) information about Jeter himself (name, hometown, birth date, etc.). These derived statistics would be stored in dimension tables.
If you’re still confused about fact and dimension tables, I found this question on StackOverflow that does a pretty good job of answering the question. I also came across this article that also does a good job of describing fact and dimension tables.
Stars and snowflakes
A fact table usually maintains a relationship with a number of dimension tables. The fact table connects to the dimension tables through a foreign key relationship. The visual table design usually resembles a star, in which the fact table is at the center and the dimension tables branch out from the center. For this reason, this structure is called a star schema.
A snowflake schema is related to the star schema. The main difference is that the dimension tables are further normalized. The resulting foreign key relationships result in a schema that visually resembles a snowflake.
Attention, data-mart shoppers…
A data mart can probably be considered either a smaller version of a data warehouse, or a subset of a data warehouse (a “dependent” data mart, according to Wikipedia). As I understand it, a data warehouse stores data for an entire corporation, organization, or enterprise, whereas a data mart stores data for a specific business unit, division, or department. Each business unit utilizes data marts for various information purposes, such as reporting, forecasting, and data mining. (I’ll likely talk about these functions in a separate article; for our purposes, they go outside the scope of this article.)
So, this winds up what I’ve learned about data warehousing (so far). Hopefully, you’ll have learned as much reading this article as I have writing it. And hopefully, you’ll keep reading along as I continue my own education into BI. Enjoy the ride.
Edit: This is the first of a series of articles (I hope!) in which I’m trying to teach myself about BI. Any articles I write that are related to this, starting with this one, will be preceded with “#BI101” in the title.
As I stated in a previous article, one topic about which I’m interested in learning more is business intelligence (BI). For those of you who are new to BI, it is a broad topic. In a nutshell, it can probably be described as “consuming and interpreting data so it can be used for business decisions and/or applications.”
I’ll admit that I don’t know a lot about BI (at least the fine details, anyway). I did work a previous job where I touched upon it; I was tasked with performing some data analysis, and I was introduced to concepts such as OLAP cubes and pivot tables. I’ve gotten better at creating pivot tables — I’ve done a few of them using MS Excel — but I’ll admit that I’m still not completely comfortable with building cubes. I suppose that’ll come as I delve further into this.
A while back, my friend, Paresh Motiwala, suggested that I submit a presentation for Boston SQL Saturday BI edition. At the time, I said to him, “the only thing I know about BI is how to spell it!” He said to me (something like), “hey, you know how to spell SQL, don’t you?” Looking back at the link, I might have been able to submit (I didn’t realize, at the time, that they were running a professional development track). That said, Paresh did indeed had a point. As I often tell people, I am not necessarily a SQL expert — I know enough SQL to be dangerous — nevertheless, that does not stop me from applying to speak at SQL Saturday. Likewise, as I dive further into this topic, I’m finding that I probably know more about BI than I’ve led myself to believe. Still, there is always room for improvement.
To tackle this endeavor, once again, I decided to jump into this using a subject that I enjoy profusely: baseball. Baseball is my favorite sport, and it is a great source of data for stat-heads, mathematicians, and data geeks. I’ve always been of the opinion that if I’m going to learn something new, I should make it fun!
Besides, the use of statistical analysis in baseball has exploded. Baseball analytics is a big deal, ever since Bill James introduced sabermetrics (there is some debate as to whether James has enhanced or ruined baseball). So what better way to introduce myself to BI concepts?
For starters, I came across some articles (listed below, for my own reference as much as anything else):
Since I’m using baseball to drive this concept, let’s use a baseball example to illustrate this.
Let’s say you’re (NY Yankees manager) Aaron Boone. You’re down by a run with two outs in the bottom of the 9th. You have Brett Gardner on first, Aaron Judge at bat, and you’re facing Craig Kimbrel on the mound.
What do you do? How does BI come into play here?
Let’s talk a little about what BI is. You have all these statistics available — Judge’s batting average, Kimbrel’s earned run average, Gardner’s stolen base percentage, and so on. In years BS — “before sabermetrics” — a manager likely would have “gone with his gut,” decided that Judge is your best bet to hit the game-winning home run, and let him swing away. But is this the best decision to make?
Let’s put this another way. You have a plethora of data available at your fingertips. BI represents the ability to analyze all this data and provide information that allows you to make a good decision.
If Aaron Boone (theoretically) had this data available at his fingertips (to my knowledge, Major League Baseball bans the use of electronic devices in the dugout during games), he could use the data to consider Kimbrel’s pitching tendencies, Judge’s career numbers against Kimbrel, and so on. BI enables Boone to make the best possible decision based upon the information he has at hand.
I do want to make one important distinction. In the above paragraphs, I used the words data and information. These two words are not interchangeable. Data refers to the raw numbers that are generated by the players. Information refers to the interpretation of that data. Therein lies the heart of what BI is — it is the process of generating information based upon data.
What’s there to know about BI?
I’ve already mentioned some buzzwords, including OLAP, cubes, and pivot tables. That’s just scratching the surface. There’s also KPIs, reporting services, decision support systems, data mining, data warehousing, and a number of others that I haven’t thought of at this point (if you have any suggestions, please feel free to add them in the comments section below). Other than including the Wikipedia definition links, I won’t delve too deeply into them now, especially when I’m trying to learn about these myself.
So why bother learning about BI?
I have my reasons for learning more about BI. Among other things…
It is a way to keep myself technically relevant.I’ve written before about how difficult it is to stay up-to-date with technology. (For further reading regarding this, I highly recommend Eugene Meidinger’s article about keeping up with technology; he also has a related SQL Saturday presentation that I also highly recommend.) I feel that BI is a subject I’m able to grasp, learn about, and contribute. By learning about BI, I can continue making myself technically valuable, even as my other technical skills become increasingly obsolete. Speaking of which…
It is a subject that interests me. I’m sure that many of you, as kids, had “imaginary friends.” (I’ll bet some adults have, too — just look at Lieutenant Kije and Captain Tuttle.) When I was a kid, I actually had an imaginary baseball team. I went as far as to create an entire roster full of fictitious ballplayers, even coming up with full batting and pitching statistics for them. My star player was a power-hitting second baseman who had won MVP awards in both the National and American leagues, winning several batting titles (including a Triple Crown) and leading my imaginary team to three World Series championships. I figured, if my interest in statistics went that far back, there must be something behind it. Granted, now that I’ve grown up older, I’m not as passionate about baseball statistics as I was as a kid, but some level of interest still remains, nevertheless.
It is a baseline for learning new things. I’ve seen an increasing number of SQL Saturday presentations related to BI, such as PowerBI, reporting services, and R. I’m recognizing that these potentially have value for my workplace. But before I learn more about them, I also need to understand the fundamental baseline that they support. I feel that I need to learn the “language” of BI before I can learn about the tools that support it.
So, hopefully, this article makes a good introduction (for both you and myself) for talking about BI. I’ll try to write more as I learn new things. We’ll see where this journey goes, and I hope you enjoy coming along for the ride.
Every now and then, I’ll peruse the forums on SSC. In addition to people answering questions about SQL Server, there also tends to be some banter, which is probably not unusual in many forums of this nature. One of the comments I’ve seen time and again is something like, “I learn more about subjects by answering people’s comments on these forums.”
There is more truth to this statement than people realize. In my experience in writing about technology, I often find that I end up learning about the technology about which I’m writing — sometimes to the point of becoming a subject matter expert.
Several years ago, I taught part-time at a local business school (roughly equivalent to community college level). I taught primarily general mathematics and a few computer classes. I was once asked to fill in for another instructor who taught statistics. My problem: I didn’t know much about statistics. So, I read up on it (along with the syllabus that I would be teaching that day). I wanted to at least be able to sound like I knew what I was talking about. As it turned out, by teaching that class, I actually learned something about it. The students were not the only ones who got an education that day.
The other day, I began writing a draft article regarding a subject about which I’d like to learn more: BI (edit: the now-finished article can be seen here.). I’ve dabbled in BI a little bit; I worked a previous job where I was asked to perform some data analysis (which is how I learned about cubes and pivot tables), and I took a course in decision-support systems in grad school. I’m seeing more SQL Saturday presentations about BI; indeed, there are even SQL Saturday conferences dedicated to BI topics (usually indicated by the words “BI Edition”). It is a topic about which I do have some interest, and it’s something about which I’d like to learn more.
My friend, Paresh Motiwala, who was one of the organizers for SQL Saturday Boston BI Edition a while back, encouraged me to apply to speak at the event. I said to him at the time, “the only thing I know about BI is how to spell it!” (His response: “Hey, you know how to spell SQL!”) On hindsight, I probably should have applied; it turns out that even BI Edition conferences accept professional development topics, under which nearly all of my presentations (so far) are categorized.
So if I claim to know so little about BI, why did I decide to start writing about it? Well, I’m trying to learn about it, and I’d like to pass along what I learn. But, I want to place a greater emphasis on the first part of that statement: I’m trying to learn about it. Writing about it makes me learn something about it a little more in-depth. And by doing so, I discover that I have a better grasp of the topic.
Hopefully, relatively soon, you’ll see an article from me about BI. Hopefully, I’ll have learned enough writing about it that you’ll be able to learn something from me. And hopefully, I’ll have demonstrated that I’m learning something new, and improving myself in the process.
If you want to learn something new, try teaching it or writing about it. You’ll be surprised how much you, yourself, learn in the process.