Dimensional Modeling Video

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. 

Practice Makes Perfect

As with any skill, only so much can be learned from a book (or a blog post).  In this post, I’m hoping to offer some hands-on training that will allow you to play with some data a bit.  If you have an Oracle environment available to you (check the previous post if you do not), you can run the script found here.  If you are unfamiliar with running a script like this, some suggestions are in the previous post as well.  The following tables with data will be created…

This is a very basic star that holds data related to sales transactions.  Here is a quick breakdown of the tables…

Dim_Product (22 rows):  This is a dimension table that contains a product’s overall group (larger grouping) and then the individual product numbers and names within that group (smaller grouping).

Dim_Employee (19 rows):  This is a dimension table that contains information regarding the employees that sold the products. 

Dim_Day (365 rows):  This is a date dimension table that contains one row for each day as well as higher-level date groupings.  In this example, this table only holds data in calendar year 2011 (1/1/2011 – 12/31/2011).

Fact_Transaction (300 rows):  This is a fact table that contains one row for each transaction line item.  So, if an individual purchased three items on the same transaction (i.e., brought three items to the cash register) that transaction will produce three rows in this fact table. 

Data Warehousing is ideal for top-down analysis.  Once the script has been run in Oracle, and the tables have been created, consider the following example…

select b.calendar_year, sum(a.amount)

from fact_transaction a,

dim_day b,

dim_employee c,

dim_product d

where a.key_day = b.key_day

and a.key_sales_person = c.key_employee

and a.key_product = d.key_product

group by b.calendar_year

This select statement shows the dollars sold per fiscal year, although there is only one year in this case.  If you want to throw in the product_group to see the dollars sold per year, per product_group, that will give you an additional level of detail…

select b.calendar_year, d.product_group, sum(a.amount)

from fact_transaction a,

dim_day b,

dim_employee c,

dim_product d

where a.key_day = b.key_day

and a.key_sales_person = c.key_employee

and a.key_product = d.key_product

group by b.calendar_year, d.product_group

Do you see what is happening?  You can continue drilling down until you get to a specific transaction_line if you wish. 

An analyst may examine the data at a high-level and notice that one quarter had an enormous number of sales dollars.  She can keep adding descriptors to the select clause (and group by them) to eventually find where those dollars lie.  Did one sales rep have a stellar quarter?  Did one product group do exceptionally well?  Did sales go through the roof on one particular day out of the quarter?  Did one product alone do exceptionally well?  Once you have the information you can turn that into action.  Does somebody deserve a raise for their extraordinary work?   Did one marketing strategy work remarkably well, meaning that we need to consider implementing it across the entire organization? 

This type of structure can turn data into valuable data.  Valuable data can enable great decisions.  Have fun playing with the data.  See how many ways you can slice and dice it.  We will do some more with this star in future posts.

Star Schema

In a prior post we looked at the value-add of a data warehouse using the following example.  These relationships may seem a bit unusual to somebody who is not familiar with data warehousing, so I wanted to touch on this just a bit.  When tables relate to each other in this way, they are a part of a star schema.  Each star schema has two types of tables – Fact tables and Dimension tables.

Fact Tables: A Fact table contains the data that is to be measured.  In the example above (a simple example) the fact_transaction table is the fact table.  The actual_sales_price is the item that is measured.  In this case, only one measureable item is included.  If we wanted to add another measureable item (i.e., sales_price_before_discount) we could do that.  This table can contain any number of additive items that fit the grain.  What is grain?  A fact table’s grain is basically the answer to this question: What does each row in the fact table represent?  In this case, the grain is one row for each transaction line item.  So, if a customer purchases three items, then that will result in three rows (among all of the others that pertain to other transactions) in this fact table.  Generally speaking (of course, there are a few exceptions) the fact table itself does not contain a description of this measurable data.  Another kind of table fulfills this role.

Dimension Tables: A Dimension table describes the data that is contained in the fact tables.  The idea is that you aggregate the measureable objects (i.e., sum the actual sales price) and then group by the dimension objects.  You can analyze the actual sales prices for each product, for each year, for each month, and so on.  Notice that the dim_product table, for example, contains the lower-level product data and the higher-level product group data.  A developer who is creating tables in third-normal form would probably place this lower-level data and higher-level data into two different tables.  In the data warehousing world, this may not happen.  Looking at the raw data in this table may show something like…

Product Department Name	Product Group Name	Product Name
Men’s Dept	Shoes	Brand A Sneakers
Men’s Dept	Shoes	Brand B Dress Shoes
Women’s Dept	Accessories	Wrist Watch
Women’s Dept	Accessories	Beaded Necklace
Children’s Dept	Athletic Wear	Sweat Pants

Notice that some of the data repeats.  This allows you to easily see the actual sales price at a high level (i.e., price per department) and then as you add additional descriptive data to the select statement and group by it you can drill down to a more granular level (i.e., price per department per product group).  In some cases, a descriptive element belongs by itself.  Notice the dd_transaction_no field in the fact_transaction table.  This describes the fact in terms of the transaction.  In other words, which transaction did this refer to?  This doesn’t belong in any of the existing dimension tables.  Rather than just create a dimension table with a key and this transaction number, we can place it directly into the fact table.  It is known as a degenerate dimension in this case.

Fact tables and dimension tables are the two main types of tables used in data warehousing.  Others exist which we may examine in the future.  For now, these are the fundamentals of this type of system.

Technical Value Add

In the last post we looked a bit at the theory of data warehousing and the value that it adds.  Now I would like to examine the value add from a more technical perspective.  Suppose you have the following tables in an operational system.

Now, suppose that an executive asks the following question: Which employees sold products in the summer or fall from the “Apparel” product group? 

From that conversation you are tasked with providing a report that shows…

1.)    Employee Name

2.)    Employee Status

3.)    Product Name

4.)    Product Status

5.)    Season

6.)    Actual Sales Price

Since this data has not been warehoused, a SQL statement similar to the following will probably be required (assuming Oracle syntax).

SELECT                    a.last_name,

                                a.first_name,

                                decode(a.termination_date, null, ‘Active’,’Inactive’) as “Employee Status”,

                                e.name,

                                decode(e.discontinued_date, null, ‘Active’,’Inactive’) as “Product Status”,

                                case        when extract(month from b.date) in (11,12,1,2) then ‘Winter’

                                                when extract(month from b.date) in (3,4,5) then ‘Spring’

                                                when extract(month from b.date) in (6,7,8) then ‘Summer’

                                                when extract(month from b.date) in (9,10) then ‘Fall’

                                end as “Season”,

                                e.price – c.discount_applied as “Actual Sales Price”                         

FROM                     employee a,

                                transaction_header b,

                                transaction_detail c,

                                product_group d,

                                product e

WHERE                   a.id = b.sales_person_id

                                AND b.id = c.header_id

                                AND e.id = c.product_id

                                AND d.id = e.product_group_id

                                AND case                when extract(month from b.date) in (11,12,1,2) then ‘Winter’

                                                                when extract(month from b.date) in (3,4,5) then ‘Spring’

                                                                when extract(month from b.date) in (6,7,8) then ‘Summer’

                                                                when extract(month from b.date) in (9,10) then ‘Fall’

                                                end in (‘Summer’,’Fall’)

                                AND d.name = ‘Apparel’

While this is not the longest SQL statement ever written, it is pretty verbose.  The employee status, product status, actual sales price, and season fields need to be derived or calculated.  That derivation or calculation must happen within the SQL statement for each row that is returned, which will eat up resources.  If the business rules for the definition of a season change, then they will need to be changed in both the select clause and the where clause. 

Suppose that beginning in 2011, management decided that November would be considered a “Fall” month but wanted to keep history.  In other words, November transactions that occurred in 2010 or earlier would fall into the “Winter” bucket but those in 2011 and later will fall into the “Fall” bucket.  Imagine the SQL statement that would need to be written for that!

In reality, this example may be a bit simplistic.  In fact, if this requirement came through and it was the only requirement of its kind, it may be worth it to just write the SQL.  However, once decision makers start to see data in a slightly different way they tend to want more and more and more…so the SQL statements get more complex.  Once the analytical requirements reach a certain point, the data needs to be restructured.

Now, consider if the data from the tables is denormalized and placed into a data warehouse (see below).

In this case, the SQL statement to produce the same report will be something like this…

SELECT                    b.employee_last_name,

                                b.employee_first_name,

                                b.employee_status,

                                c.product_name,

                                c.product_status,

                                d.season,

                                sum(a.actual_sales_price)

FROM                     fact_transaction a,

                                dim_employee b,

                                dim_product c,

                                dim_date d

WHERE                   a.key_date = d.key_date

                                AND a.key_product = c.key_product

                                AND a.key_sales_person = b.key_employee

                                AND d.season in (‘Summer’,’Fall’)

                                AND c.product_group_name = ‘Apparel’

GROUP BY              b.employee_last_name,

                                b.employee_first_name,

                                b.employee_status,

                                c.product_name,

                                c.product_status,

                                d.season

That is much simpler.  Also, the SQL statement does not contain any of the business rules.  Each night, an ETL job (ETL is an acronym for extract, transform, and load) essentially assigns dates to the appropriate quarter, year, season, etc., assigns products to the appropriate product group, derives statuses, and performs other tasks that would be tedious within a select statement.  If the business rules that define a season, for example, change then that can be taken care of in the ETL job.  The SQL statement can remain the same.  This makes pulling data for the purposes of analysis much simpler.  If you are used to thinking about data in third-normal form then this will require a slightly different perspective.  However, the value add can be great in helping executives analyze their data quickly.