Practice Using Degenerate Dimensions

In the previous post, we looked at degenerate dimensions.  Rather than create a new SQL script that you can use to practice we can revisit the transaction data star that we used in the February 25th post.  The script is here and the ERD is below. 

Notice that the TRANSACTION_NUMBER and TRANSACTION_LINE columns are in the FACT_TRANSACTION table.  If we created a new dimension table, called DIM_TRANSACTION, with this structure (in Oracle)...

CREATE TABLE DIM_TRANSACTION
(
 KEY_TRANSACTION NUMBER,
 TRANSACTION_NUMBER NUMBER,
 TRANSACTION_LINE NUMBER
)

...it would contain as many rows as the FACT_TRANSACTION table.  This is not wrong, but the effort involved in creating the surrogate keys which will only be used by one fact row may not be necessary.  If you wish, run the create table statement that I provided above, add some data, and create some surrogate keys so that it can be joined to the fact table.  You will see that the reporting capabilities from one design to the other are the same, with a bit more effort being required to maintain the DIM_TRANSACTION table.  This is why I opted to place these columns directly in the fact table as degenerate dimensions.  When faced with this design question, consider which is best for your organization and move forward with turning data into valuable data.

Degenerate Dimensions

One of the purposes of dimensional modeling is to identify what needs to be measured (facts) and identify the context needed to make those measures meaningful (dimensions).  So, for example, if an organization wishes to measure revenue from direct sales, the fact in this model is the revenue amount.  The context (or dimensions) may include things like sales person, date of sale, item sold, quantity, and/or a host of other things. 

As discussed previously, a typical design is to place the facts of a business process that are measured at the same grain into one table (fact table) and to group the contextual pieces into other tables (dimension tables).  Occasionally, a piece of context will exist at the same grain as the fact table.  For example, using the same scenario as we used above, suppose that the sales revenue needs to be stored at the sales transaction level.  If a transaction number exists for each individual transaction, then a dimension table that contains this value will contain as many rows as the fact table.  While there is nothing wrong with this approach, it may seem like extra work to create an additional table with a surrogate key that will only be referenced by one row of the associated fact table. 

As a result, some designers may opt to make this value a degenerate dimension (mentioned briefly at the end of a previous post).  A degenerate dimension is a dimension value that exists directly in the fact table, as opposed to the fact table containing a foreign key that points to that record in a dimension table.  The database will not need to allocate any additional space for the surrogate keys in this case, since they add little value anyway.  Designing a dimension object like this to be a degenerate dimension is not a must in this situation.  Using the standard approach of creating a separate table is fine too.

As with everything that we do as Business Intelligence experts, when faced with this design question it is best to consider the value that each option brings to the table.  Asking some of the following questions may be a good start...

1.) Do I have too many dimension tables included in this star already? 
2.) How will the reporting tools react to this design? 
3.) Will the report writers become confused if I design it this way?

If needed, spend some time absorbing the concept of degenerate dimensions.  We will look at some examples later.

Image: photostock / FreeDigitalPhotos.net

What Does Big Data Mean For Us?

In the previous post we looked at the problem of Big Data and the industry's move toward more efficient relational database products (or even non-relational database products) that may remove the need for a data warehouse.  Some may be inclined to wonder what this means for those who have skills in data modeling and ETL development.  In order to answer this question I'm going to point to another blog that dealt with this question recently.  It is the Star Schema Central blog (on my blog list on the right side of the screen) which is maintained by Chris Adamson.  The link to that particular post is here.  He does a great job of explaining why dimensional modeling (the logical design of a data warehouse) will still be important in the world of Big Data.  This is an area that is still being defined, so the possibilities are still being explored.
 
While so much is still up in the air regarding the ways in which data will be modeled or ETL'd in the world of Big Data, consider that as you move forward in your career your overall mission is not to model data a certain way or even to write ETL.  Those are just a few techniques that can be used today and those techniques may become outdated.  The techniques may change over time but the main goal is to provide meaningful data to decision makers quickly and efficiently.  By focusing on this, no matter what the technique or technology of the day is, you will be turning your data into something of true value.

Image: Idea go / FreeDigitalPhotos.net

Big Data

One item that comes up often in discussions regarding Business Intelligence is Big Data.  Big Data refers to a data set that is so large that using traditional relational database technology to interact with it is very difficult and perhaps impractical.  So, let's say that a star schema is designed in hopes of helping to increase business intelligence in some area and an ETL job is written to populate that star.  If that ETL job takes days to run due to the size and/or complexity of the data (not due to the inefficiency of the code within the ETL job, that can be corrected by rewriting it) then that data set may be referred to as Big Data...and it can be quite frustrating. 

There are new technologies being developed that help to deal with this "thorn in the side" of data warehousing professionals.  Some of these technologies allow an analyst to view data in a source system in the same way that she would in a data warehouse without needing a data warehouse.  One of these is an SAP product called HANA.  Remember from this blog's first post that the point of a data warehouse is to create a new environment for data (apart from the source system), in which it is restructured so that it is optimal for analysis.  A product like HANA can process records much faster than other relational database products on the market. 

So, revisiting the piggy bank example from this blog's first post, suppose that Rain Man (Dustin Hoffman's character in the movie of the same name) peered inside the piggy bank.  Someone with his talent would be able to quickly determine the amount of money inside the piggy bank without the need to place the coins into money rolls.  In this case, the same business intelligence can be gained even though the coins remain in the source system (the piggy bank).  So, what does this mean for skills in dimensional modeling and ETL development?  I would imagine that there is some debate on that.  I'll leave that for a future post.   

I'll say parenthetically that I'm not trying to endorse or not endorse HANA.  I'm simply mentioning it as an example of a piece of technology that is designed to deal with Big Data.

Image: digitalart / FreeDigitalPhotos.net 

Slowly Changing Dimensions Solutions To Practice

In the last post I provided a script that allowed you to practice interacting with slowly changing dimensions.  The answers to the three questions that I proposed are...


1.) John Farmer earned $62,000 in salary (wages, benefits, and bonus) from Information Systems and $12,200 from Human Resources.  A SQL statement like this (although variations of it will answer the question as well) will answer this question...



select a.last_name,
a.first_name,
a.employee_number,
a.department,
a.hire_date,
a.title,
a.appointment_begin_date,
a.appointment_end_date,
sum(b.actual_wages_paid),
sum(b.actual_benefits_paid),
sum(b.actual_bonus_paid)
from dim_employee_scd a,
fact_salary_scd b
where a.key_employee_scd = b.key_employee_scd
and a.employee_number = 2546
group by a.last_name, a.first_name, a.employee_number, a.department, a.hire_date, a.title, a.appointment_begin_date, a.appointment_end_date
order by 3,7

2.) James Couch's salary dropped 36.6% from the first to the second position.  It dropped 36.4% from the second to the third position.  From a business perspective this seems odd, but the point is to see there there was a change of some sort.  A SQL statement like this will answer this question...

select a.last_name,
a.first_name,
a.employee_number,
a.department,
a.hire_date,
a.title,
a.appointment_begin_date,
a.appointment_end_date,
sum(b.actual_wages_paid) + sum(b.actual_benefits_paid) + sum(b.actual_bonus_paid)
from dim_employee_scd a,
fact_salary_scd b
where a.key_employee_scd = b.key_employee_scd
and a.employee_number = 2547
group by a.last_name, a.first_name, a.employee_number, a.department, a.hire_date, a.title, a.appointment_begin_date, a.appointment_end_date
order by 3,7

3.) Assuming that a current appointment is defined as a record with a null value for the appointment_end_date (in reality, a developer would need to verify this with a subject matter expert) the following SQL statement will display salary dollars for active positions only...

select a.last_name,
a.first_name,
a.employee_number,
a.department,
a.hire_date,
a.title,
a.appointment_begin_date,
a.appointment_end_date,
sum(b.actual_wages_paid),
sum(b.actual_benefits_paid),
sum(b.actual_bonus_paid)
from dim_employee_scd a,
fact_salary_scd b
where a.key_employee_scd = b.key_employee_scd
and a.appointment_end_date is null
group by a.last_name, a.first_name, a.employee_number, a.department, a.hire_date, a.title, a.appointment_begin_date, a.appointment_end_date
order by 3,7

As the dimensions (descriptors of measures) move through time, a type 2 slowly changing dimension allows the measures to be grouped by each of those changes.  For example, the salary dollars can not only be displayed per person.  As the person progresses through the organization by moving to new positions, the salary dollars can be grouped by each stop along the way. 

From a technical perspective this amounts to the dimension's natural key expanding from the employee number to the employee number, position title, department, and appointment begin date (or whatever defines an appointment in your organization).  From a business perspective this example allows an analyst to view an individual's effect on the organization as she progresses through that organization.

Image: digitalart / FreeDigitalPhotos.net

Practice Using Slowly Changing Dimensions

A few weeks ago we looked at slowly changing dimensions.  I've provided a script that will create a table called dim_employee_scd and fact_salary_scd.  These are two slightly altered versions of the same tables in the star that we have been using (if you are just joining us, don't worry, they can stand alone too). 

In this case, the dim_employee_scd acts as a slowly changing dimension.  As always, don't read too much into the dates and/or salary amounts.  This data is entirely fictitious.  Run the script (here are instructions if needed), play with the data and see if you can answer these questions...

1.) How much did John Farmer receive in salary from Information Systems as opposed to Human Resources?
2.) As a percentage, how many more (or fewer) dollars did James Couch receive in salary when he moved from the first to the second and then from the second to the third position?  Remember, even if the numbers decrease although it appears that he was promoted, that's ok.  This is fictitious data and the point is to see there there was a change of some sort.
3.) Try to display salary dollars for active only positions, which may require that you make an assumption about the business rule.

We'll look at some solutions in the future.

Analysis Using Conformed Dimensions

In the last post we practiced examining data from different fact tables via conformed dimensions.  While the syntax may be different from solution to solution, something like the select statement below will allow you to compare salary dollars from the fact_salary table to sales dollars from the fact_transaction table per month for each person.

SELECT                    decode(paid.calendar_year, null, sales.calendar_year, paid.calendar_year) Year,

                                decode (paid.calendar_month_number, null, sales.calendar_month_number, paid.calendar_month_number) Month,

                                decode(paid.last_name, null, sales.last_name, paid.last_name) LastName,

                                decode(paid.first_name, null, sales.first_name, paid.first_name) FirstName,

                                paid.amtpaid,

                                sales.salesamt

FROM

(SELECT                   d.calendar_year,

                                d.calendar_month_number,

                                c.last_name,

                                c.first_name,

                                sum(b.actual_wages_paid) amtpaid

FROM                      fact_salary b,

                                dim_employee c,

                                dim_day d

WHERE                    b.key_date_paid = d.key_day

                                AND b.key_employee = c.key_employee

GROUP BY d.calendar_year, d.calendar_month_number, c.last_name, c.first_name) paid

FULL OUTER JOIN

(SELECT                   d.calendar_year,

                                d.calendar_month_number,

                                c.last_name,

                                c.first_name,

                                sum(a.amount) salesamt

FROM                      fact_transaction a,

                                dim_employee c,

                                dim_day d

WHERE                    a.key_day = d.key_day

                                AND a.key_sales_person = c.key_employee

GROUP BY d.calendar_year, d.calendar_month_number, c.last_name, c.first_name) sales

                ON                           paid.calendar_year = sales.calendar_year

                                                AND paid.calendar_month_number = sales.calendar_month_number

                                                AND paid.last_name = sales.last_name

                                                AND paid.first_name = sales.first_name

In order to take advantage of this approach, an analyst must view the data from each fact table at the same grain.  In the select statement above, notice that each of the inner selects group by the same dimension attributes (calendar_year, calendar_month_number, last_name, and first_name) and then the outer select statement joins on those attributes.  Since there may be some instances in which an individual has sales dollars but no salary dollars and vice versa, an Oracle full outer join is used.  If you are confident that one of the select statements will return all of the possible individuals for all possible time periods for that person, then a left or right outer join may be used.  In reality a reporting tool will probably take care of this on the back-end, so that a data analyst will not need to be familiar with this exact syntax (although it may be beneficial to conceptually understand how the data is being pulled).

From a business perspective, this kind of analysis will allow a decision maker to view productivity vs. cost for each instance of a business entity (i.e., individual, department, region, line of business, etc.).  This is yet another way in which data warehousing can help data become truly valuable.

Image: vichie81 / FreeDigitalPhotos.net