Valuable Data

Data Helping To Fight Crime

2014-04-25T05:00:00.000-07:00

In her role as the District Attorney of New Jersey, Anne Milgram found the crime fighting arena to be horribly inefficient. For example, she recalls a time when an individual was arrested and held on a bail amount of $3,500 dollars and was unable to pay that amount. As a result, he stayed in jail until his case was heard eight months later, which cost the public over $9,000 dollars. Believing that data could help, Ms. Milgram developed a tool to help correct this, making crime fighting a data driven activity. This is a great example of somebody who is taking data and turning it into something that is truly valuable. She describes this experience in this TED talk.

Creating Date Dimensions

2014-04-18T05:00:00.000-07:00

One of the tasks of an architect who is implementing a data warehouse is to create the date dimensions. Because these dimensions have no source system, they are often created in something like Excel and then inserted directly into the dimension tables themselves. Searching the internet will probably reveal some code that will automatically create these dimensions. I've created one such PL/SQL block here that will create three date dimensions, a daily, monthly, and yearly grain. Because this will probably only be run once, there is no error handling nor is there a way to add to the date dimension (it will only create from scratch). Feel free and add those features if you so desire. Here is an explanation of the parameters:

1.) p_begin_year - This is the first year of the range of dates that you wish to create
2.) p_end_year - This is the last year of the range of dates that you wish to create
3.) p_create_tables - This is intended to have a value of YES or NO. Set it to YES if you want the tables to be created and populated. Set it to no if you only want the tables to be populated (e.g., they have already been created).
4.) p_include_fiscal_year - If you would like some data related to your fiscal year in the dim_day and dim_month tables, then set this value to YES. Otherwise, set it to NO.
5.) p_fiscal_year_begin_month_no - If the answer to #4 is YES, then set this to the calendar month number that is the first month in your fiscal year. In other words, if your fiscal year begins in July, set this to 7. If it begins in October, set it to 10, etc.

If nothing else, this will be a starting point for you with regards to creating a date dimension. Examine the code, tweak it, etc.

Note: This has been written in PL/SQL, meaning that it will only run in an Oracle database. If you would like to do this with a system from another database vendor, similar scripts can be written in that vendor's native language.

declare
p_begin_year int := 2001;
p_end_year int := 2014;
p_create_tables varchar2(3) := 'YES';
p_include_fiscal_year varchar2(3) := 'YES';
p_fiscal_year_begin_month_no int := 7;

d_first_day date;
d_last_day date;
d_loop_day date;

i_counter int;

begin
if upper(p_create_tables) = 'YES' then
EXECUTE IMMEDIATE 'CREATE TABLE DIM_DAY '
|| '( '
|| 'KEY_DAY INT, '
|| 'CALENDAR_DAY DATE, '
|| 'CALENDAR_MONTH_NUMBER INT, '
|| 'MONTH_NAME VARCHAR2(50), '
|| 'CALENDAR_QUARTER INT, '
|| 'CALENDAR_YEAR INT '
|| ')';
end if;

select to_date('01-JAN-' || p_begin_year, 'DD-MON-YYYY') into d_first_day from dual;
select to_date('31-DEC-' || p_end_year, 'DD-MON-YYYY') into d_last_day from dual;
d_loop_day := d_first_day;
i_counter := 1;

while d_loop_day <= d_last_day
loop
EXECUTE IMMEDIATE 'insert into dim_day (key_day, calendar_day, calendar_month_number, month_name, calendar_quarter, calendar_year) '
|| 'select ' || i_counter || ', '
|| '''' || to_char(d_loop_day, 'DD-MON-YYYY') || ''', '
|| 'extract(month from to_date(''' || d_loop_day || ''', ''DD-MON-YYYY'')),'
|| 'to_char(to_date(''' || d_loop_day || ''', ''DD-MON-YY''), ''MONTH''), '
|| 'case when extract(month from to_date(''' || d_loop_day || ''', ''DD-MON-YY'')) between 1 and 3 then 1 '
|| 'when extract(month from to_date(''' || d_loop_day || ''', ''DD-MON-YY'')) between 4 and 6 then 2 '
|| 'when extract(month from to_date(''' || d_loop_day || ''', ''DD-MON-YY'')) between 7 and 9 then 3 '
|| 'when extract(month from to_date(''' || d_loop_day || ''', ''DD-MON-YY'')) between 10 and 12 then 4 '
|| 'else -1 '
|| 'end, '
|| 'extract(year from to_date(''' || d_loop_day || ''', ''DD-MON-YY'')) '
|| 'from dual ';

i_counter := i_counter + 1;
d_loop_day := trunc(d_loop_day) + 1;
end loop;

commit;

if p_include_fiscal_year = 'YES' then
if p_create_tables = 'YES' then
EXECUTE IMMEDIATE 'alter table dim_day '
|| 'add '
|| '( '
|| 'FISCAL_MONTH_NUMBER INT, '
|| 'FISCAL_QUARTER INT, '
|| 'FISCAL_YEAR INT '
|| ') ';
end if;

EXECUTE IMMEDIATE 'update dim_day o '
|| 'set (o.fiscal_month_number, o.fiscal_year) = '
|| '( '
|| 'select case when i.calendar_month_number - ' || p_fiscal_year_begin_month_no || ' >= 0 then '
|| '(i.calendar_month_number - ' || p_fiscal_year_begin_month_no || ') + 1 '
|| 'else '
|| '12 + (i.calendar_month_number - ' || p_fiscal_year_begin_month_no || ') + 1 '
|| 'end, '
|| 'case when i.calendar_month_number - ' || p_fiscal_year_begin_month_no || ' >= 0 then '
|| 'i.calendar_year '
|| 'else '
|| 'i.calendar_year + 1 '
|| 'end '
|| 'from dim_day i '
|| 'where i.calendar_day = o.calendar_day '
|| ') ';

EXECUTE IMMEDIATE 'update dim_day o '
|| 'set o.fiscal_quarter = '
|| '( '
|| 'select case when i.fiscal_month_number in (1,2,3) then 1 '
|| 'when i.fiscal_month_number in (4,5,6) then 2 '
|| 'when i.fiscal_month_number in (7,8,9) then 3 '
|| 'when i.fiscal_month_number in (10,11,12) then 4 '
|| 'else -1 '
|| 'end '
|| 'from dim_day i '
|| 'where i.calendar_day = o.calendar_day '
|| ') ';

commit;
end if;

if upper(p_create_tables) = 'YES' then
if p_include_fiscal_year = 'YES' then
EXECUTE IMMEDIATE 'CREATE TABLE DIM_MONTH '
|| '( '
|| 'KEY_MONTH INT, '
|| 'CALENDAR_MONTH_NUMBER INT, '
|| 'CALENDAR_MONTH_NAME VARCHAR2(20), '
|| 'CALENDAR_QUARTER_NUMBER INT, '
|| 'CALENDAR_YEAR INT, '
|| 'FISCAL_MONTH_NUMBER INT, '
|| 'FISCAL_QUARTER_NUMBER INT, '
|| 'FISCAL_YEAR INT, '
|| 'FIRST_DAY DATE, '
|| 'LAST_DAY DATE '
|| ') ';
else
EXECUTE IMMEDIATE 'CREATE TABLE DIM_MONTH '
|| '( '
|| 'KEY_MONTH INT, '
|| 'CALENDAR_MONTH_NUMBER INT, '
|| 'CALENDAR_MONTH_NAME VARCHAR2(20), '
|| 'CALENDAR_QUARTER_NUMBER INT, '
|| 'CALENDAR_YEAR INT, '
|| 'FIRST_DAY DATE, '
|| 'LAST_DAY DATE '
|| ') ';
end if;
end if;

if p_include_fiscal_year = 'YES' then
EXECUTE IMMEDIATE 'insert into dim_month '
|| 'select rownum, a.* '
|| 'from '
|| '( '
|| 'select distinct calendar_month_number, '
|| 'month_name, '
|| 'calendar_quarter, '
|| 'calendar_year, '
|| 'fiscal_month_number, '
|| 'fiscal_quarter, '
|| 'fiscal_year, '
|| 'trunc(calendar_day,''month''), '
|| 'last_day(calendar_day) '
|| 'from dim_day '
|| 'order by calendar_year, '
|| 'calendar_month_number '
|| ') a ';

commit;
else
EXECUTE IMMEDIATE 'insert into dim_month '
|| 'select rownum, a.* '
|| 'from '
|| '( '
|| 'select distinct calendar_month_number, '
|| 'month_name, '
|| 'calendar_quarter, '
|| 'calendar_year, '
|| 'first_day(calendar_day), '
|| 'last_day(calendar_day) '
|| 'from dim_day '
|| ') '
|| 'order by a.calendar_year, '
|| 'a.calendar_month_number ';

commit;
end if;

if upper(p_create_tables) = 'YES' then
EXECUTE IMMEDIATE 'CREATE TABLE DIM_CALENDAR_YEAR '
|| '( '
|| 'KEY_CALENDAR_YEAR NUMBER, '
|| 'CALENDAR_YEAR NUMBER, '
|| 'FIRST_DAY DATE, '
|| 'LAST_DAY DATE '
|| ') ';
end if;

EXECUTE IMMEDIATE 'insert into dim_calendar_year '
|| 'select rownum, a.* '
|| 'from '
|| '( '
|| 'select distinct calendar_year, '
|| '''01-JAN-'' || calendar_year || '''', '
|| '''31-DEC-'' || calendar_year || '''' '
|| 'from dim_month '
|| 'order by calendar_year '
|| ') a ';

commit;
end;

Image courtesy of Anusorn P nachol / FreeDigitalPhotos.net

Slowly Changing Dimensions - Correction

2014-04-11T05:00:00.000-07:00

About two years ago, I wrote this post on slowly changing dimensions. Several months later I learned in a Kimball class that my understanding of a Type 3 Slowly Changing Dimension (SCD) was incorrect. Allow me to use this post to right the ship.

My original explanation (incorrect):

A type 3 SCD is essentially a hybrid of a type 1 and type 2 SCD. In this scenario as history changes, rows are added into the dimension table, consistent with a type 2 SCD. Each row contains a column with the value that pertains to that moment in time, as does a type 2 SCD. Each row also contains a column with the current value. So, continuing with the example above (from the other post), while you will be able to see that the older fact rows pertain to a time in which the employee was a Junior Analyst, you will also be able to see (while looking at this same historical dataset) that the employee is now a Senior Analyst.

My updated explanation (correct):

A type 3 SCD is essentially a hybrid of a type 1 and type 2 SCD. In this scenario as history changes, rows are added into the dimension table, consistent with a type 2 SCD. Each row contains a column with the value that pertains to that moment in time, as does a type 2 SCD. Each row also contains a column with the prior value. So, continuing with the example above, while you will be able to see that the newer fact rows describe this employee as a Senior Analyst, you will be able to see that his/her prior position was Junior Analyst.

So, a type 3 SCD will look something like this:

key_employee	employee_first_name	employee_last_name	position	prior_position
1	John	Smith	Junior Analyst	(null)
2	John	Smith	Senior Analyst	Junior Analyst

My original explanation above describes a type 6 slowly changing dimension, which will look something like this:

key_employee	employee_first_name	employee_last_name	position	current_position
1	John	Smith	Junior Analyst	Senior Analyst
2	John	Smith	Senior Analyst	Senior Analyst

These links to Ralph Kimball's website provide some great information on slowly changing dimensions:

1.) Type 1
2.) Types 2 and 3
3.) Types 0, 4, 5, 6, and 7

Image courtesy of adamr / FreeDigitalPhotos.net

Factless Fact Tables

2014-04-04T05:00:00.000-07:00

Over the past three weeks, we've looked at three ways to design fact tables.

1.) Transaction Fact Table

2.) Period Snapshot Fact Table

3.) Accumulating Snapshot Fact Table

As the term suggests, all of these tables contain facts (measures) that pertain to the business process. There are instances in which a fact table can be designed to contain no measures but only events. This is referred to as a factless fact table. Consider a user who wishes to see the org chart on a particular day. In this case, a factless fact table may be designed to contain one row per day (if that is the grain) and the keys to the Human Resources descriptors that existed on that day. Every column in this table will contain a foreign key to a dimension table or a degenerate dimension value. Consider this plain-english version of this table:

Date	Name	Department	Supervisor	Job Title
1/15/2013	Jason Smith	Human Resources	Allen Wells	HR Associate I
…
5/31/2013	Jason Smith	Human Resources	Allen Wells	HR Benefits Coordinator
…
3/15/2014	Jason Smith	Information Technology	Mike Williams	HR Systems Analyst

This tells us that Jason Smith was hired as an HR Associate I on 1/15/2013. He was promoted to a Benefits Coordinator on 5/31/2013 and then moved to Information Technology on 3/15/2014. FYI, although the table above only shows three rows there will probably be several more. This table will probably contain one row per day with the same data until it changes, indicated by the ellipses in the table above.

In the future we will look at writing some ETL to populate a factless fact table.

Image courtesy of watcharakun / FreeDigitalPhotos.net

Accumulating Snapshot Fact Tables

2014-03-28T05:00:00.000-07:00

We've been looking at the various fact table design options in data warehousing. Two weeks ago we examined the transaction fact table and last week we examined the periodic snapshot fact table. The third and final option is called an accumulating snapshot. This type of fact table is different from the other two in one big way. Each row is often revisited. Consider our banking example from the other posts. When a deposit is added to a transaction fact table, that row is added and then left alone. All of the data needed to add and complete that row is known. The same is true of periodic snapshots. An accumulating snapshot fact table begins each row and then accumulates data until that row is complete. Let's consider an example in a different context. Suppose that a star is built for the purposes of analyzing a help desk ticketing business process. The following descriptors (in addition to any measures) are a part of the business process:

Date Ticket Opened
Date Ticket Assigned
Date Solution Provided To Customer
Date Customer Accepted Solution
Date Ticket Closed
Ticket Number

Suppose that ticket number 10012 is opened on 3/1/14. At that point, this row will exist in the fact table:

Date Ticket Opened	Date Ticket Assigned	Date Solution Provided To Customer	Date Customer Accepted Solution	Date Ticket Closed	Ticket Number
3/1/2014					10012

Now, suppose that the ticket is assigned to a technician on 3/3/14. That same row will be updated to look like this:

Date Ticket Opened	Date Ticket Assigned	Date Solution Provided To Customer	Date Customer Accepted Solution	Date Ticket Closed	Ticket Number
3/1/2014	3/3/2014				10012

If the solution is provided to the customer one day later, the row will be updated to look like this:

Date Ticket Opened	Date Ticket Assigned	Date Solution Provided To Customer	Date Customer Accepted Solution	Date Ticket Closed	Ticket Number
3/1/2014	3/3/2014	3/4/2014			10012

Assuming that the customer accepts the solution on 3/5 and the ticket is closed 3/6, the row will be updated to look like this:

Date Ticket Opened	Date Ticket Assigned	Date Solution Provided To Customer	Date Customer Accepted Solution	Date Ticket Closed	Ticket Number
3/1/2014	3/3/2014	3/4/2014	3/5/2014		10012

and then this:

Date Ticket Opened	Date Ticket Assigned	Date Solution Provided To Customer	Date Customer Accepted Solution	Date Ticket Closed	Ticket Number
3/1/2014	3/3/2014	3/4/2014	3/5/2014	3/6/2014	10012

At this point, the row is left alone. If you so desire, an accumulating snapshot allows you to store some lags between dates to help with analysis. This can help ease the burden of using the database to calculate the various lags.

In the future we will look at writing some ETL to populate an accumulating snapshot fact table.

Image courtesy of Vichaya Kiatying-Angsulee / FreeDigitalPhotos.net

Periodic Snapshot Fact Tables

2014-03-21T05:00:00.000-07:00

In our last post we looked at one of three ways to design a fact table, called a transaction fact table. Today, let's look at a second design, called the periodic snapshot. Remember from Ralph Kimball's teaching (and last week's post) that a transaction fact table gains a row each time that something happens. Using our banking example, from last week, each deposit or withdrawal will result in a record being inserted. Looking at only one record will allow us to see that one event. Adding these records will allow us to see the balance.

A periodic snapshot contains snapshots of the data as it existed at various points in time. Unlike a transaction fact table, selecting one row (or perhaps a subset of rows if it is semi-additive) will display the current value at that point. Our example from last week involved opening a checking account at Acme Bank on 2/1/14 and making an initial deposit of $3,000. Three days later you withdrew $200. Five days after that, you deposited $1,000. If a periodic snapshot were written to show the balance at a daily level, a plain-english version may look something like this:

Date	Amount
2/1/2014	$3,000
2/4/2014	$2,800
2/9/2014	$3,800

Now, let's use this table to answer the same questions that we examined last week:

1.) What was the account balance on 2/4?

Unlike the transaction fact table, in order to find the balance on 2/4, we only need to look at the 2/4 row. The 2/4 row contains a snapshot of the current balance on 2/4, as opposed to only the event that occured on 2/4. By looking at the 2/4 row, we will see that the balance was $2,800.

2.) What was the account balance on 2/9?

Using the same logic that was explained in #1 above, look only at the 2/9 rows. This will give you a value of $3,800.

3.) How much was deposited on 2/9?

This type of question cannot be answered using a periodic snapshot. A periodic snapshot will store the current state of the business process as of the applicable period, but will not store the events leading to the current state.

In the future we will look at writing some ETL to populate a periodic snapshot fact table. We will also take a look at an additional fact table design.

Image courtesy of cooldesign / FreeDigitalPhotos.net

Transaction Fact Tables

2014-03-14T05:00:00.000-07:00

In his book The Data Warehouse Toolkit, Ralph Kimball explains that there are three ways to design a fact table. The first and probably most typical (my opinion) is called a transaction fact table. A transaction fact table is a fact table that contains measures, keys to dimension tables, and degenerate dimensions, if applicable. When using this fact table to examine the current state of something going through the business process, all rows should be summed through the latest time period that is being examined. This is due to the fact that a row is added to this fact table as an event in its respective business process occurs. The most common example involves the banking industry. Suppose you open a checking account at Acme Bank on 2/1/14 and make an initial deposit of $3,000. Three days later you withdraw $200. Five days after that, you deposit $1,000. A "plain-english version" of this fact table (without the descriptors) will look something like this:

Date	Amount
2/1/2014	$3,000
2/4/2014	($200)
2/9/2014	$1,000

Now, let's use this table to answer these very simple questions:

1.) What was the account balance on 2/4?
Notice how a row was added each time that a deposit or withdrawal was made. In order to find the balance on 2/4, we must look at everything that happened through 2/4. If we only look at the 2/4 row, we will only see the $200 withdrawal. However, it is important to know that $3,000 existed in the account before that withdrawal. So, if we sum the 2/1 row and the 2/4 row, we will see that the balance on 2/4 (after that transaction posted) was $2,800.

2.) What was the account balance on 2/9?
Using the same logic that was explained in #1 above, sum the 2/1, the 2/4, and the 2/9 rows. This will give you a value of $3,800.

3.) How much was deposited on 2/9?
When looking at only one of the events that occured as opposed to the sum of everything that occurred, only that one row should be taken into consideration. The 2/9 row by itself will tell us that $1,000 was deposited into the account. Unlike #2 above, we do not need to consider the $2,800 that was in the account prior to 2/9.

In the future we will look at writing some ETL to populate a transaction fact table. We will also take a look at some additional fact table designs.

Image courtesy of twobee / FreeDigitalPhotos.net

Business Intelligence Yesterday, Today, and Tomorrow

2014-03-07T05:00:00.000-08:00

They say that the only thing in life that is guaranteed not to change is change itself. The past few decades have proven this to be the case with technology. Old dial-in modems have given way to broadband access. Land line phones have practically been replaced by cell phones. Original flip phones are old news compared to today's smart phones...you get the idea. The key to navigating these waters is to remember the business that you are in. Both old modems and new modems connect users to an online experience. Land line phones allowed people to remain in touch with each other as do today's smartphones. In each case, the mechanics may have changed, but the goal remains. Companies that have lost themselves in the mechanics of the technology have a hard time moving to a new one. However, companies that are attached to the overall goal and merely see the technology as today's way of achieving that goal are often quicker to embrace change...and are able to survive.

These principles apply to individuals as well. The BI industry is in the midst of some huge change and it is important to remember the goal of BI. The traditional model of ETL jobs running in the middle of the night so that the data in the data warehouse will be available the next day is becoming less and less acceptable. Waiting several minutes for queries to run is becoming less acceptable as well. This has given way to the creation of in-memory database solutions that allow data scientists to analyze large datasets very quickly. The technologies are changing...but the goal is not (check out this Ralph Kimball white paper). When considering business intelligence solutions 10 years ago, today, or 10 years from now, one commonality exists. That commonality is the logical architecture. A business process must be understood in terms of its measures and descriptors so that it can be analyzed. A traditional data warehouse will create a place in which the data can physically reside on disk, based on that architecture. Solutions like SAP's HANA implement that architecture not on disk but in memory. In another decade, or so, another solution may exist.

Those individuals that understand that BI involves presenting the measures and descriptors of the business processes of an organization to its leaders will not only survive but will enjoy these changes. While we BI professionals must learn the mechanics of the best solution of the day in order to practically reach that goal, we also must expect that those mechanics will change.

What's the main objective of a BI professional? Not to write ETL. Not to display data using a certain tool. The main objective is to enable the leaders of the organization to make great decisions by providing good data.

Image courtesy of cooldesign / FreeDigitalPhotos.net

The Bus Matrix

2014-02-28T05:00:00.000-08:00

One of the invaluable tools that Ralph Kimball describes in his book The Data Warehouse Toolkit is the bus matrix. The bus matrix is basically a grid that will ultimately allow you to see the relationships between fact tables and their conformed dimensions.

Recall from prior posts that a fact table contains measures (or events for factless fact tables) that pertain to a business process. A dimension table contains the descriptors of those measures. Dimension tables should be reused by multiple fact tables if more than one business process uses that dimension. For example, the salary fact table may describe the payee using the dim_employee dimension table. The help desk ticketing fact table may describe the person to whom a ticket is/was assigned using that same dim_employee dimension table. That concept is explained in this post.

A bus matrix is a two-dimensional grid that lists the business processes (which will become fact tables) along the left and the descriptors (which will become dimension tables) across the top. In the middle, an X or a check mark is placed at the intersection of a fact and dimension that belong in the same star schema. Consider this example:

		Descriptors
		Time	Employee	Vendor	Department
Business Processes	Payroll	x	x		x
	Shipping	x		x	x
	Accounts Receivable	x			x
	Sales	x	x		x

When an analyst is gathering requirements in an effort to understand what needs to be warehoused, he can easily list the business processes that come from the conversation along the left of a white board. He can also list the descriptors (i.e., day, person, department, product, etc.) along the top. Later, these items can be translated into table names, resulting in a bus matrix.

Creating a bus matrix is a great idea (thanks to Mr. Kimball for that) for the following reasons:

1.) You can easily see the facts and dimensions that reside in your data warehouse. Entity Relationship Diagrams provide some great information, although they can get pretty large for a large data warehouse. If seeing the relationships at a high level is necessary, a bus matrix will allow that to be done very easily.

2.) As you add to your data warehouse you can revisit this document and add to it. Revisiting the bus matrix will help to ensure that you use the conformed dimensions as opposed to inadvertently re-creating one.

3.) We have been treating the bus matrix as a document that can be used to communicate some of the technical relationships of the data warehouse. That is not a bad use, but consider a version of the bus matrix that simply lists the business processes and descriptors (not their respective tables). Such a document will essentially describe the organization. The business processes and the entities that somehow touch those processes are all displayed visually, giving the executives a high level view of the makeup of their organization.

All of the cool BI that provides flashy new toys begins with working through these fundamentals first. The bus matrix helps the leaders of an organization think through the beginnings of their data management strategy.

Business Intelligence is a great industry with a very bright future. Have fun! Are you interested in entering this industry or do you know somebody who is? Consider this.

Image courtesy of ddpavumba / FreeDigitalPhotos.net

Kimball Conference Lessons Learned

2012-10-12T04:00:00.000-07:00

I recently had the distinct privilege of attending Ralph Kimball's Dimensional Modeling In Depth class, as described in this post. Learning directly from icons such as Ralph Kimball and Margy Ross has been a huge blessing and a very enjoyable experience. One of the most eye-opening (and valuable) experiences has involved tweaking my understanding regarding concepts that I thought I understood...but found that I didn't. Some of these misunderstandings have even come out in this blog, so I'll use this post to correct some of those...

1.) Junk Dimensions - The examples of junk dimensions that I have provided included the word "junk" in the name. Margy Ross suggests not naming it as such, which makes a lot of sense. Encountering a table with junk in the name may cause some confusion (perhaps even concern) for an analyst who is not well-versed in dimensional modeling.

2.) Snowflake Schema - The terms portion of my website provides the following definition for a snowflake schema

Occasionally there are reasons to join one dimension table to another dimension table. A schema in which this occurs is referred to as a snowflake schema. The ERD, in this case, will show this “second layer” of dimension tables as being similar in appearance to a snowflake.

This dimensional modeling class proved to me that this definition is a bit misleading. Joining one dimension table to another, such as the one on the terms portion of my website, is referred to as an outrigger. A snowflake schema involves an attempt to completely denormalize a dimension.

3.) Type 3 Slowly Changing Dimension - In this post, I described Type 3 slowly changing dimensions as being a hybrid between type 1 and type 2. In reality, this hybrid is actually referred to as a type 6 (I need to update the other post). So, what is a type 3 slowly changing dimension? I'll save that explanation for a future post; however, the type 3 is not the hybrid that I thought it was.

One of the advantages of attending a course like this is that you get to bounce your knowledge against some of the most brilliant minds in the industry. In some cases they help to affirm what you already know. In other cases they correct what you already "know"...which turns you into a stronger asset for your organization and for the industry.

For more information on data warehousing concepts visit www.brianciampa.com. For data that can be used to practice modeling and/or ETL, click on Career in Data Warehousing and then click Grow. Also, if you need a fresh approach to marketing your data warehousing skillset, consider The Data Warehouse Portfolio.

Image Courtesy of David Castillo Dominici / FreeDigitalPhotos.net

Kimball University

2012-10-06T13:58:00.000-07:00

Just as a young quarterback would be thrilled to meet Peyton Manning or a young cook would jump at the chance to meet Paula Dean, I enjoyed that experience this week in the context of my vocation. I sat at the feet of Margy Ross and Ralph Kimball. When you mention these names amongst non-data warehousing professionals, you are often met with confused looks. However, those in the data warehousing arena know these individuals as some of the most brilliant minds when it comes to modeling data.

Margy Ross spent the first two days polishing our skills on some of the more basic pieces of dimensional modeling. She is a very professional yet light-hearted lady with a true talent for teaching these concepts. I was blessed to learn that I had a few things wrong regarding some concepts that I thought I understand. Some of those have even come out in this blog; the corrections for which I'll save for a future post.

Ralph Kimball spent days three and four going over some advanced concepts with regards to dimensional modeling (and a bit of ETL). He is just as light-hearted, having the ability to explain very complex data warehousing concepts with humor inserted where appropriate. I spent a fair portion of the class laughing, and I still smile as I remember their humor. This was not a dry class, as some would expect (for those who enjoy data warehousing, that is). Both Margy and Ralph are brilliant minds who have the humility to (1) impart some of their knowledge to serious data warehousing students in an interesting way and (2) answer concise questions that apply to your specific organization in a one-on-one setting (assuming you can catch them after class). Here were some of my personal highlights...

1.) Ralph signed my copy of The Data Warehouse Toolkit by writing "Brian, keep to the grain. Ralph Kimball".

2.) I've had a design conundrum at work regarding a many-to-many problem related to this post. I asked him about it after class and he affirmed my suggested solution.

3.) Ralph began his first class on day three by explaining the way in which a data warehouse developer will begin asking a user what needs to exist in that user's data warehouse (which does not involve asking the question in that exact way). As a part of that conversation, Ralph made an example out of yours truly, as though I were a successful account manager looking to implement a data warehouse. Of course he was painting a fictitious scenario to make his point but it was still a cool moment.

I would highly recommend the Dimensionsal Modeling In Depth course to anybody interested in the data warehousing arena. I'm not affiliated with the Kimball Group in any way (other than being a fan) so I will not profit by anybody taking the course. I recommend it because it is simply that good.

Image courtesy of smokedsalmon / FreeDigitalPhotos.net

Data Extraction Techniques

2012-09-28T04:00:00.000-07:00

One of the challenges of writing ETL involves deciding which records to pull from the source system. If a salary star schema is built to contain payroll data, then the ETL job will refresh on a recurring basis to add new data to that star. If some data related to salary dollars already exists in the star and some new data makes it into the source system, how can the ETL job know to load the new data into the star and avoid reloading the records that were previously loaded (since they already exist in the data warehouse)? There is no one way of doing this, but consider these options...

1.) Use the source system's datetime stamps - An ETL developer should consider himself very blessed if his source system contains an insert and update datetime stamp for each record in the system. If this is the case, then he can simply store the date and time of the most recent successful extract and then pull everything that has been inserted or updated since then. It will be important for the ETL developer to understand how those audit columns are populated in each table to make sure that necessary data will not be left out and unnecessary data will not be included due to odd occurances (i.e., a source system table is reloaded for some reason and records that pertain to data that was initially added three years ago have yesterday's timestamp, etc.).

2.) Compare to the prior extract - In his book The Data Warehouse ETL Toolkit : Practical Techniques for Extracting, Cleaning, Conforming, and Delivering Data, Ralph Kimball suggests always saving the prior extract in the staging area. A new extract can be compared to this prior extract and the differences will be uploaded. Finally the new extract will overwrite the prior extract and be used as the baseline for the next update.

3.) Always pull as of a certain date - In this approach, the salary dollars may always be extracted for the current calendar year (for example). So, as the year goes on each extraction will be slightly larger than the last. In April, all salary dollars that date back to January will be extracted from the source. In May, all salary dollars that date back to January will be extracted from the source as well. When using this option in conjunction with loading a fact table, the ETL developer will need to delete all of the applicable year's records from the fact table and then load the extract. Although several records will be deleted and simply reloaded (with no change), this is one way of grabbing what is needed from the source when a simpler solution is not possible. However, any updates made to the data that pertain to a different year will be missed. This will need to be taken into consideration. When using this option in conjunction with loading a dimension table, the ETL developer will need to compare to the dimension table to see if any of the dimensions have changed and only load or update the changes. Deleting records from the dimension table and reloading is not a good strategy since the fact table has foreign keys that reference the dimension table.

4.) Refresh the fact table - In this case all of the data from the source system will always be extracted and the fact table will be truncated and reloaded each time. This will only be an acceptable solution if the fact table can be loaded in a timely fashion. Also, this will only be acceptable with a fact table most of the time. Even if an ETL developer plans to refresh an entire star (fact and dimension tables) she must consider the possibility that some of these dimensions are conformed dimensions, meaning that other fact tables reference them.

There are obviously more options than these four. With whatever option you choose, it is important to build the job so that it can be run multiple times with no negative impact. In other words, running the job twice should not result in duplicate records showing up. Even though the initial extract may place more records than are needed into the staging area, the job should be "smart enough" to know exactly what to load into the data warehouse and what to exclude.

For more information on data warehousing techniques as well as data with which you can practice writing your own ETL, visit www.brianciampa.com/careerindatawarehousing/grow.html. Also, if you need a fresh approach to marketing your skillset, consider The Data Warehouse Portfolio.

Image courtesy of digitalart / FreeDigitalPhotos.net

To Constrain or Not Constrain

2012-09-21T04:00:00.000-07:00

One of the advantages of using a relational database is that you can mandate that certain relationships MUST exist within your data. If somebody tries to enter data into the database that does not relate to other data correctly, the database can be configured to reject that "bad data" until it is corrected. Since many data warehouses reside in relational databases, using this feature is an option for the data warehousing team as well.

Consider this example...suppose that a star contains a DIM_PRODUCT table that contains the products that are used by that business process. If the Men's Raincoat product has a KEY_PRODUCT value of 27 (surrogate key) then the records in the corresponding fact tables that pertain to this product will have a KEY_PRODUCT value of 27 (foreign key). That's a lesson from Database 101. If somebody removes the Men's Raincoat product from the DIM_PRODUCT table while records that point to it exist in the fact table, then those fact table records will point to nothing...and become meaningless.

The advantage of enforcing this constraint within the database itself is that if somebody tries to remove the Men's Raincoat product from the DIM_PRODUCT table the database will not allow it until the "child records" from the fact table have been deleted or repointed. Using this feature sounds like a no-brainer (and it may be) but a data warehouse provides an additional twist to this kind of decision since so much data is being inserted.

Option 1: Enforce Constraints - This will ensure that the relationship between the fact and dimension tables are always valid from a technical perspective. However, each time that the data is loaded via the ETL job, the fact table must look to make sure that a parent record exists in the dimension. If (and only if) it exists, it will load that record into the fact. Doing that for each and every record will ensure good data intregity but it can also slow a job down.

Option 2: Do Not Enforce Constraints - This will probably result in a faster and more efficient ETL job. However, the possiblity of the fact table containing some orphan records exists.

It is up to each data warehousing team to decide which is best for their particular situation. In some cases it is appropriate to physically enforce constraints (option 1). In other cases, it may be appropriate to logically enforce constraints (option 2), meaning that the data warehousing team will need to periodically run SQL statements that specifically look for orphaned records.

If you are looking for more information on data warehousing and/or data that can be used to practice ETL and architecture skills, visit www.brianciampa.com and click on Career in Data Warehousing. Also, if you are looking for a fresh way to market your data warehousing skills, consider The Data Warehouse Portfolio.

Image: FreeDigitalPhotos.net

Bridge Tables

2012-09-14T04:02:00.000-07:00

Each of the examples that we've used thus far in this blog have involved situations in which each fact row was associated with only on dimension row, per dimension table. In other words there has always been a many-to-one relationship between the fact table and its associated dimensions. In the real world, this relationship will not always be the case.

Suppose that a fact table contains employee salary data and each employee can be associated with multiple departments at once. Conceptually, the design would be something like this...

One possible way of resolving this is to use what some people call a bridge table. This is essentially a crosswalk between the fact and dimension designed to resolve the many-to-many problem. The design for such a table is below.

The EMPLOYEE_DEPT_BRIDGE table will not contain any data that will be seen in query results. It will only be used as a link between the FACT_SALARY and the DIM_DEPARTMENT tables. However, there is a rule that must be well understood by any and all analysts that select from this table: You must always group by the department in your query results if your select statement joins to the DIM_DEPARTMENT table (via the bridge, of course) in any way. For example, suppose that John Smith (KEY_EMPLOYEE value is 123) has $1,000 in wages and is associated with both accounting and human resources (part time in each, perhaps). Consider this SQL statement...

select a.key_employee_name,
sum(a.wage_amount)
from fact_salary a,
employee_dept_bridge b,
dim_department c
where a.key_employee = b.key_employee
and b.key_department = c.key_department
and a.key_employee = 123
group by a.key_employee_name

Because the EMPLOYEE_DEPT_BRIDGE table will contain two rows for John Smith (one for accounting and one for human resources), these results will show a salary of $2,000 which will appear incorrect to the user.

KEY_EMPLOYEE	SUM(A.WAGE_AMT)
123	2,000

Now, consider this SQL statement...

select a.key_employee_name,
department_name,
sum(a.wage_amount)
from fact_salary a,
employee_dept_bridge b,
dim_department c
where a.key_employee = b.key_employee
and b.key_department = c.key_department
and a.key_employee = 123
group by a.key_employee_name,
department_name

The results will show his $1,000 salary associated with each department. This will appear to be more correct to the user, assuming they understand that he is associated with both departments.

KEY_EMPLOYEE	DEPARTMENT_NAME	SUM(A.WAGE_AMT)
123	ACCOUNTING	1,000
123	HUMAN RESOURCES	1,000

In this case, educating your analysts and report writers to use this table correctly is a key component to the success of this design.

ETL - Practice Loading A Fact - Solution

2012-09-07T04:00:00.000-07:00

In a prior post we looked at the process by which a fact table is loaded. I need to apologize, since the ERD of the FACT_TRANSACTION table that I posted in that prior post was incorrect. It has since been updated. The four things that an ETL job needs to accomplish are...

1.) Select all necessary data from the source
a. Measures
b. Natural Keys of the dimension objects
c. Degenerate Dimension objects
2.) Transform the data as needed
a. Aggregate measures and group by the appropriate objects
b. Add foreign keys to dimension tables
3.) Delete any existing rows from the fact table that will be replaced by the new data
4.) Load the fact table

Consider this PL/SQL procedure as a procedure that will accomplish these things.

Extract

The STAGE_TRANSACTION_DATA table pulls all of the necessary elements from the source system into the staging area. I am treating one schema as both the staging area and the presentation area. This is probably unrealistic in a production environment but it keeps things simple for these purposes.

Transform

1.) The STAGE_TRANS_DATA_PROD_KEY table is used to grab the KEY_PRODUCT from the DIM_PRODUCT dimension. Notice that this is done by joining the product's natural key, which is the PRODUCT_NO (product number), to the DIM_PRODUCT table. An outerjoin is used so that if a record comes into the staging area for a product that does not exist in the DIM_PRODUCT table, that record will not be excluded from the fact table. It will exist with a value of -1 and will need to be addressed after the ETL has run.

2.) The STAGE_TRANS_DATA_DATE_KEY table is used to transform the TRANSACTION_DATE into a key that will join to a date dimension. I created the date dimension on my own, so I'll let you try and tackle that one. Those dimensions typically have no source and are created as needed by the ETL developer. Again, an outer join is used so that if a date does not exist in that dimension a value of -1 is placed into the fact table.

Load

1.) This fact table, because it is so small, is truncated and then repopulated each time that the ETL runs. Date logic can be written so that only a certain time period will be refreshed with each run, but for simplicity's sake I did it this way.

2.) Finally, the data is loaded into the FACT_TRANSACTION table.

Prior posts have talked about turning data into valuable data. This post shows you how to accomplish that in a hands-on way.

Remember to take a look at www.brianciampa.com (specifically, click on Career In Data Warehousing and then Grow) for larger datasets that can be used for this kind of practice.

Image: FreeDigitalPhotos.net

Tales of Work

2012-09-05T03:38:00.000-07:00

I typically do not blog in the middle of the week, but this week I'll make an exception. Kimanzi Constable is a career coach who maintains a great blog, called Tales of Work, on discovering your talents and finding work that you enjoy. He has been gracious enough to run a guest post that I wrote on the portfolio concept. You can check out the guest post here.

My blog is built on the premise that data warehousing and business intelligence are about helping decision makers to see the true value in their data. Those decision makers are best helped by those who are passionate about data. That best describes me, which is why I maintain the Valuable Data blog in hopes of helping other passionate people to hone that skill. If you find yourself needing some help in finding your passion, consider the Tales of Work blog.

Image: Courtesy of FreeDigitalPhotos.net

ETL - Practice Loading A Fact

2012-09-02T04:37:00.000-07:00

We looked recently at the process used to load a dimension table. Consider this post as a way to practice loading a fact table. An ETL job that is designed to load a fact table will accomplish these things…

1.) Select all necessary data from the source

a. Measures

b. Natural Keys of the dimension objects

c. Degenerate Dimension objects

2.) Transform the data as needed

a. Aggregate measures and group by the appropriate objects

b. Add foreign keys to dimension tables

3.) Delete any existing rows from the fact table that will be replaced by the new data

4.) Load the fact table

As with the dimension example, no two developers will do this the same way, which is perfectly fine. Consider this example which is to be used in conjunction with the two tables used in the ETL – Practice Loading a Dimension post (click on the image to enlarge it)…

…and this fact table needs to be populated.

Run the following scripts in Oracle to create the source data

1.) Product data – This is the same script from the ETL – Practice Loading a Dimension post. If you have already run this one, you are fine.

2.) Transaction data - This script will create and populate the TRANSACTION_HEADER and TRANSACTION_DETAIL tables above.

The TRANSACTION_DETAIL.PRODUCT_ID is a foreign key to the PRODUCT.ID field from the ETL – Practice Loading a Dimension post. If you wish, try to write an ETL job that will populate the FACT_TRANSACTION table in PL/SQL or another language of your choosing. We will examine a possible solution in a future post.

For more practice data and additional information on dimensional modeling visit www.brianciampa.com. Also, if you need a fresh approach to marketing your data warehousing skillset consider The Data Warehouse Portfolio.

Dimensional Modeling Video

2012-08-25T04:08:00.001-07:00

Dimensional modeling is a very powerful technique in helping to enable excellent decision making. This is a video that explains the business value that can be derived from using this technique. For more information on dimensional modeling, including free data with which you can practice, look at the Learn, Grow, and Succeed links at http://www.brianciampa.com/careerindatawarehousing.html.

Also, for those looking for a fresh way to market your skillset consider The Data Warehouse Portfolio at http://www.brianciampa.com.

The Data Warehouse Portfolio

2012-08-17T04:19:00.000-07:00

You've spent weeks in data warehousing classes, days in training sessions, hours reading books on the subject, and perhaps years warehousing data for other organizations. However, right now you want to work for this one. You know that you have the experience as well as the ambition. You have a list of ideas to take to this new potential employer that you know will provide immense value. So, what's the next step? All of that is placed onto a very professionally-looking resume and submitted. The only problem is that everybody else has done that same thing. You are not known as "Bob, who will add huge value to the business intelligence team," you are known as applicant 27...just another resume in the stack.

Perhaps you are on the other end of the spectrum. You are a novice looking for your first data warehousing position with no prior experience outside of class. You need a way to actually show that despite your lack of work experience you can do this...and do it well.

Do you need a fresh approach to marketing your skillset in hopes of standing out in the crowd? It is interesting that those in the event industry are expected to provide samples of their work for potential clients. Cake decorators, wedding photographers, and other similar vendors would probably be passed over if they only provided a resume that explained what they had done before or what their education claims that they are capable of doing.

What if you placed a sample of your data warehousing skillset onto the web specifically for a potential employer to browse? In other words, you would have a resume to provide the details of your experience and education and a portfolio to provide an example of what you can actually do. The Data Warehouse Portfolio is a book that suggests a way to accomplish this. More information is available at www.brianciampa.com. You may also want to revisit this prior blog post for a bit more information as well.

Your job is to turn data into valuable data. However, showing the value in yourself is the first step in getting that job.

ETL - Practice Loading a Dimension - Solution

2012-08-10T04:29:00.001-07:00

In the previous post we looked at the process used to write a basic ETL job to populate a dimension table. As discussed previously, while no two developers will write one exactly the same way, an ETL job that populates a dimension table will need to accomplish the following...

1.) Extract all of the dimension attributes from the source.
2.) Transform the data according to the requirements (i.e., concatenate first name and last name to create a full name column, etc.)
3.) Identify those records that are new to the table as opposed to those records that already exist in the table and may need to be updated due to updates in the source system
4.) Generate new surrogate key values and add them to the new records
5.) Load the records into the dimension table

Consider this example of an Oracle PL/SQL procedure that will populate the DIM_PRODUCT table. Before running the procedure keep these things in mind...

1.) I made an error in the SQL file from the previous post, so you may want to re-download that and run it again. The DIM_PRODUCT.PRODUCT_EFFECTIVE_FLAG should be named DIM_PRODUCT.PRODUCT_ACTIVE_FLAG.
2.) This job depends on an Oracle sequence to create the surrogate keys. Before running the job, run this statement in your Oracle environment...

CREATE SEQUENCE
SEQ_DIM_PRODUCT
MINVALUE 0
INCREMENT BY 1
START WITH 1;

3.) I'll encourage you not to get too lost in the Oracle syntax. The point is to examine the logical flow of an ETL job. If you have a better way of structuring the Oracle code (or want to use something other than Oracle), then by all means do that.

Run the procedure (after you have created the sample data provided in the previous post, of course) to populate the DIM_PRODUCT dimension. Notice what is happening...

1.) Extract all of the dimension attributes from the source - Everything is pulled from the source system and placed into the STAGE_PRODUCT_EXTRACT table.
2.) Transform the data according to the requirements (i.e., concatenate first name and last name to create a full name column, etc.) - The PRODUCT_ACTIVE_FLAG is derived and that data is placed into the STAGE_PRODUCT_TRANSFORM table.
3.) Identify those records that are new to the table as opposed to those records that already exist in the table and may need to be updated due to updates in the source system - New records are identified (via an outer join) and placed into the STAGE_PRODUCT_LOAD table.
4.) Generate new surrogate key values and add them to the new records - Surrogate keys are created in the STAGE_PRODUCT_LOAD table using the Oracle sequence mentioned earlier.
5.) Load the records into the dimension table - Existing records are updated in the DIM_PRODUCT table and the new records from the STAGE_PRODUCT_LOAD table are loaded.

This job is designed to be run as many times as necessary. Running one time or multiple times should still result in 25 records being placed into the DIM_PRODUCT table. This is a simple example for a few reasons, one of which is that we are working with a very small amount of data. A more complex ETL job may examine the source system's data and somehow determine which records are new and/or have been updated before pulling them into the staging area.

Also, more complex ETL jobs may not have five simple steps, as this one does, to accomplish the five things listed above. It may take several steps to accomplish those five things due to the complexity of the data.

If you wish, change some of the source data to see the updates occur or tweak the ETL job to your liking. By running this ETL job, you have just turned ordinary data into something that is truly valuable.

Image: FreeDigitalPhotos.net

ETL - Practice Loading a Dimension

2012-08-03T10:46:00.001-07:00

We've been looking recently at the concept of extract, transform, and load (ETL) jobs. This post will begin to examine some of the mechanics. When loading a dimension table the ETL job should accomplish the following things...

1.) Extract all of the dimension attributes from the source.
2.) Transform the data according to the requirements (i.e., concatenate first name and last name to create a full name column, etc.)
3.) Identify those records that are new to the table as opposed to those records that already exist in the table and may need to be updated due to updates in the source system
4.) Generate new surrogate key values and add them to the new records
5.) Load the records into the dimension table

Each ETL job that populates a dimension table will need to accomplish these five things, although no two developers will develop a job exactly the same way. To get a feel for this type of job, consider this very simple example. A source system contains these two tables among others (click the picture to enlarge)...

...and this dimension needs to be populated...

If you have an Oracle environment available to you, run this sql file to create the two source system tables, populate them with data, and create the DIM_PRODUCT table. Each product, as you can see, is grouped under a single product group. The PRODUCT_EFFECTIVE_FLAG indicates whether or not a product is currently effective (current at the time of the ETL job). If you wish, try to write an ETL job that will populate the DIM_PRODUCT table in PL/SQL or another language of your choosing. We will examine a possible solution in a future post.

Extract, Transform, and Load Video

2012-07-21T12:31:00.001-07:00

As a continuation of our discussion of ETL, consider this YouTube video, which is also available at http://www.youtube.com/brianciampa. Designing the star or snowflake schema is a great start. However, the ETL populates that star, thus transforming your data into something that is truly valuable. In a future post we will look at an example of a basic ETL job in Oracle.

Extract, Transform, and Load (ETL)

2012-07-13T04:01:00.000-07:00

In prior posts, we’ve looked a bit at the basic structure of a star schema but we have not looked at populating a star schema. Populating a data warehouse is accomplished using an extract, transform, and load (ETL) job. This is a job that pulls data from the source system, transforms it into a structure appropriate for the data warehouse, and then loads that data into the data warehouse. The concept of ETL is not unlike any other craft or trade. Consider the following…

1.) A math student reads and understands the math problem from the textbook (extract), uses a piece of scrap paper, if calculators are not allowed, to find the solution (transform), and places the answer onto the final answer sheet (load).

2.) A carpenter purchases wood from a supplier (extract), uses his workshop to design, measure, sand, cut, etc. (transform), and then delivers the final product to the customer (load).

Usually, the transformation piece is accomplished inside of a separate schema in the database, called a staging area. The data from the source system is placed into this area and then restructured so that it is appropriate for the star schema, much like the math student uses the scrap paper and the carpenter uses the workshop. It is generally accepted that this area is for the ETL developer’s eyes only. End users do not have access to data in the staging area, much like a customer is not involved in the carpenter’s workshop and the math student does not place his chicken scratch on the sheet with the final answer. Once that data has been transformed, it is placed into the star schema and is ready for analysis.

It may be worth noting that some variations of ETL, such as ELT (extract, load, and transform), ETLT (extract, transform, load, and transform), and others, are used to describe methods by which jobs will populate a data warehouse using a different order than the standard extract then transform then load order. In order to encompass all of these terms, the term data integration (or something similar) is often used to describe data warehouse population in a general sense.

For more information on data warehousing concepts, remember to visit http://www.brianciampa.com/.

Image: FreeDigitalPhotos.net

Big Data Defined

2012-06-29T04:14:00.001-07:00

A typical question asked of business intelligence professionals is - what is big data? In a previous post I provided some explanation of the big data challenge. As I've learned more about this very interesting piece of business intelligence, I believe that a more complete explanation is available. Consider this...

Suppose you own a day care, meaning that you are in the business of caring for children each day. You have determined that in order for you to have a truly challenging day, three things have to occur. First, you must have a large number of kids to care for on that given day. How large is large? There are no hard and fast rules...large enough that your current resources are strained at best and inadequate at worst. Second, let's assume that you have no help, meaning that you need to know what each child is doing right now. Not knowing what one of them was doing for an hour and then finding out later that he was painting the refrigerator is not acceptable. Third, you have a very diverse group of kids. This does not refer to diversity with regards to race or ethnicity but diversity with regards to personality. Some of the kids love to play outside and some inside. Some are into puzzles and others are into riding bicycles. As a result, keeping the kids engaged in activities that they enjoy and in which they are gifted can be challenging.

If two of the three challenges exist, the day is still challenging but not to the same extent. You can know what each child is doing in a diverse group if you have a small number of them. You can deal with a large number of kids if they are ALL sitting in the same room, doing the same thing. It's the combination of the three problems that presents a challenge. Now, consider this in terms of big data. Big data is characterized as having three V's.

1.) Volume - In order to data to be considered big data it must be large. How large is large? I'm not so sure that there is a hard and fast boundary between "normal" data and big data in terms of size. However, I would assume that if managing the data for the purposes of business intelligence presents problems because of its size, then this V applies.

2.) Velocity - Part of the big data problem involves dealing with the speed at which the data comes in. Decision makers want to know what is happening in their business and in the marketplace now, as opposed to experiencing a lag. For example, if an announcement was just made with regards to a new line of business, what are people saying about that on Twitter now? If there is a need to stream data in some way so that it can be analyzed in real time, then this V applies.

3.) Variety - Part of the big data problem involves dealing with various types of data. Relational databases are good at storing structured data. Structured data is data in which each element is placed into a fixed area (such as columns and rows in a database or an XML schema) that was created for that specific element's characteristics. Dates belong here, integers belong there, etc. Unstructured data, such as a tweet or the body of an email message, are more free form. In other words, there is nothing governing the type of data that is stored in those environments. If decision makers want to analyze various types of data (such as both structured and unstructured data) then this V applies.

While challenges may exist when experiencing only one or two of the above mentioned V's, the industry generally agrees that if all three characteristics apply to your data, then you have big data.

Also, remember to visit http://www.brianciampa.com/ for more information on data warehousing and business intelligience.

Image: FreeDigitalPhotos.net

Snowflake Schema

2012-06-22T07:06:00.001-07:00

Going back to the piggy bank example from this blog's first post, we know that the value of a data warehouse lies with allowing a user to analyze data easily. This is mainly achieved through denormalization. This often differs from the value of a source system which lies with getting data into the system quickly. This is mainly achieved through normalization.

So, if we were to run a select statement against a source system that is designed to return (among other things) an employee name as well as his department, those two things may be stored in two tables. The employee table may simply store a foreign key to the department table, which stores the names of the departments. The query may find 'John Smith' in the employee table and then the number 6 in the department field. The database will have to go to the department table, look up the row with the number 6 to see that the value is 'Human Resources' and then return those two values, along with any other values that were specified in the select statement, in the query results. Doing this for several rows and for several different kinds of values will take some time.

In a typical data warehouse design, however, all of these values may exist in the same table and be repeated. This way, the query can go to one table and find 'John Smith' and 'Human Resources' without having to translate a foreign key. This keeps things quick.

However, there are some instances in which some normalization is appropriate in a star schema. This normalization is not to the extent that it exists in the source system, which is often third normal form, but it is normalization none the less. Consider the ERD below (click on it to expand)...

This is yet another addition to the star schema that we have been using for the past several weeks. Notice the DIM_EXECUTIVE table at the bottom. It is a dimension table that is joined directly to the DIM_EMPLOYEE dimension table. It is not joined to the fact table. Why would we not add the four non-key values directly to the DIM_EMPLOYEE table? We could and that would not be incorrect, but what if there is not much executive information compared to the number of employees?

So, if the DIM_EXECUTIVE table contains 10 rows and the DIM_EMPLOYEE table contains 4,000,000 rows, then placing this executive information into the DIM_EMPLOYEE table will result in 3,000,990 rows of empty space. In this case, it might make sense.

When a dimension table joins to another dimension table the star schema is now referred to as a snowflake schema. The "second layer" of dimension tables causes the tables, when they are depicted in an ERD, to resemble a snowflake.

I will add that when in doubt, it is probably best not to snowflake. Snowflaking causes some additional complexity on the part of the reporting tools when it comes to interpreting the data.

To experiment with analyzing this kind of data, you can run the script found here in an Oracle environment. Rather than continue adding to the script little by little, this script will create ALL of the tables and data depicted in the ERD. As with last week's post on junk dimensions, you have some options with regards to snowflaking or not. Remember that your number one goal is to turn your organization's data into something that is truly valuable, thus enabling your leaders to make excellent decisions. Make your decision with that in mind.

More on snowflaking can be found in the books listed in the additional reading section to the right as well as by visiting http://www.brianciampa.com/ and, under the Concepts menu, clicking Terms or Advanced.

Update: The Kimball training that I received taught me that I was incorrect in classifying this type of design as a snowflake schema. A snowflake schema contains a completely normalized version of the dimension tables. This post describes an outrigger table. The example above should still be considered a star schema. Sorry for my mistake.

Image(s): FreeDigitalPhotos.net