Fact Explosion Part 2

In the last post, Fact Explosion Part 1, I presented the problem of delivering fact (measure) information that is at a different grain than the surrounding dimensions due to such things as – multivalued dimensions or repeating attributes. Although from a modeling perspective, these two scenarios are different by definition, they often present the same front end reporting challenges.

Normally we aim for a nice one-to-many relationship between our dimension and fact tables.  Let’s say we have a Course Enrollment Fact table at the grain of 1 row per student, course, term. Of course we inevitably want to know who taught the course (instructor) but each Course Section can have more than 1 instructor so if we were to join an Instructor Dimension directly to the fact table it would be in a many-to-many relationship. Or, perhaps our Student Dimension must accommodate the fact that a student can, and often does, have double majors or more, resulting in a multivalued, repeating attribute design of our dimension.

So what are our options?

1.    Don’t allow the scenario to occur

This may be the best option for the masses. Create a “view” or Universe or semantic layer that keeps everything in check so the average user doesn’t report out incorrect numbers. Of course, this is not always possible….

Pros: Prevents incorrect aggregations and can be delivered to a larger audience without fear of incorrect reporting

Cons: Doesn’t always address real world reporting needs

2.    Deliver as is and push the challenge to the report developer

This option should only be considered if working with very experienced data and report writers, otherwise incorrect data aggregations could result.

Table 1: Incorrect Aggregation

Name	Major	FinancialAidAmt
Catherine	English	$10,000
Catherine	History	$10,000
Catherine Subtotal		$20,000
Joe	Poli Sci	$25,000
Joe Subtotal		$25,000
Gran Total		$45,000

Again, Catherine received $10,000 of Financial Aid total and is a double major. The Aid amount is not at the major grain. Simply using basic SUM aggregation may result in it appearing that she has $20,000 in aid. In order to prevent this we often need to rely on custom functions available to us in our BI tools – partition by student, break on student, section by student, creative use of AVG and SUM across rows, in BO such functions as FOR EACH, FOR ALL, IN, - all to prevent incorrect aggregation.

Table 2: Correct Aggregation

Name	Major	FinancialAidAmt
Catherine	English	$10,000
Catherine	History	$10,000
Catherine Subtotal		$10,000
Joe	Poli Sci	$25,000
Joe Subtotal		$25,000
Gran Total		$35,000

Pros: Users have more data elements to explore against various metrics

Cons: Greater likelihood of incorrect reporting

3.    Rows to Columns – Flatten the repeating attributes

This is a classic approach that works well in the right circumstances especially considering its limitations. When it works OK is when there are a relatively small and known number of possible values to pivot. For example, if our institution limits area of study to a max of two majors we have a small and know number of rows to pivot to column format. When it doesn’t work is if the number of values in a particular domain is large and hugely variable. This method also presents other challenges such as easily filtering all students of a particular major since the majors are now spread across multiple columns. Not impossible to solve, but more complicated.

Table 3:

Name	Major1	Major2	FinancialAidAmt
Catherine	English	History	$10,000
Catherine Subtotal			$10,000
Joe	Poli Sci	NA	$25,000
Joe Subtotal			$25,000
Gran Total			$35,000

Pros: Flattens the student dimensional attributes into a single row and decreases likelihood of incorrect aggregations at the reporting layers

Cons: Introduces difficulty in exploring data by major

4.    Concatenation of multivalued attributes

This is similar to our previous solution but it combines both major values into a single column rather than two.

Table 4:

Name	AllMajors	FinancialAidAmt
Catherine	English; History	$10,000
Catherine Subtotal		$10,000
Joe	Poli Sci	$5,000
Joe Subtotal		$25,000
Gran Total		$35,000

Pros: Similar to our previous example in that it flattens the student attributes into a single row minimizing fact explosion WHILE ALSO retaining the major information into a single column perhaps conducive to wild card filtering

Cons: Limits analysis by major due to concatenation

5.    Allocation

We can always allocate the total aid amount by student across the appropriate number of rows. This is a modeling technique using a weighting factor to determine the allocation percentage. For example, where Catherine has 2 rows (majors) and a total of $10,000 in aid we assign each of the two rows a value of $5,000.

Table 5:

Name	Major	FinancialAidAmt
Catherine	English	$5,000
Catherine	History	$5,000
Catherine Subtotal		$10,000
Joe	Poli Sci	$25,000
Joe Subtotal		$5,000
Gran Total		$35,000

Pros: Addresses the fact explosion issue by allocating the totals across rows so we can now continue to use basic SUM functions across rows.

Cons:  This can be confusing to some users and also becomes more expensive at the ETL layers and possible at run time depending on when/where the allocation calculations are taking place

So those are a few options for addressing these types of data challenges. Many require work from the Data Architect and ETL developers via SQL, creating appropriate data structures and processing, adjustments to the data models, bridge tables, etc.

What’s the best option? It depends on many factors – BI tool capabilities, end user skills, reporting use cases, and requirements. But ultimately, I use them all and often in concert with each other because there are always multiple business requirements that need to be solved for on any given project.

Fact Explosion Part 1

Some of the most frequent modeling challenges in a Higher Ed Data Warehouse has to do with multivalued dimensions and multivalued attributes. The truth is these are not unique to Higher Ed – they occur in every industry and virtually every data mart I’ve ever worked on. The use cases and solutions are also, technically, industry agnostic. What’s interesting in HE is just how pervasive this is in the Ellucian (Datatel) Colleague student system. But again, the modeling techniques are all well documented and fairly common place for a data architect. What I find a bit more challenging is how we represent this at the BI layer and educate our users on the various approaches, their benefits, and their risks.

Let’s start with a simple example report where we want to see ‘Total Financial Aid Amt’ by Student.

Table 1:

Name	FinancialAidAmt
Catherine	$10,000
Total	$10,000

Ok, as simple as it gets, right? 

What happens if we now want to include a student (repeating, multivalued) attribute, perhaps Major? (If we visualize the a potential star schema it may have a Student Dimension and Financial Aid Fact table. If the Student Dim is at the grain of 1 record per student we then have a nice one-to-many relationship between the Dimension and Fact table. If the Student Dimension is not at that grain, or perhaps joins to an outrigger table, and contains repeating rows for each student to accomodate multiple majors, then we now have a many-to-many from Dimension to Fact).

For example:

Table 2:

Name	Major	FinancialAidAmt
Catherine	English	$10,000
Catherine	History	$10,000
Total		$20,000

By simply introducing a multivaued attribute of student we experience a form of ‘fact explosion’ – incorrectly doubling of our Aid Amt measure – and a major reporting challenge. Our ‘Financial Aid Amt’ is not at the grain of student major (ie can’t break down Aid by major, it’s only at the Student, or Person, level).

Aside from the way I have aggregated the Total in Table 2, there is nothing inherently wrong with these data. There are few absolutes in modeling. If asked how one should handle such a scenario the best answer is always the same, “It depends”. Too often we look for the a cookie cutter solution, the “Best Practice”, but what’s more important is understanding what the goals and requirements are and having the tools, techniques, and knowledge base to apply the correct solution to fit the problem.

At a recent TDWI Conference I attended an advanced modeling session given by Chris Adamson (http://www.oaktonsoftware.com/) and he said something I thought was very well stated. He surveyed the audience and asked how many would consider themselves ETL developers, Business Analysts, DBAs, BI Developers, etc. He intentionally omitted a call for ‘Data Architects’ (DA) stating that one of the primary roles of the DA was to balance the shifting burdens of various technical challenges to the right layer (paraphrasing).

In the above example, there are techniques available to us at both the BI and data layers that we can use to fit the use case/s in our environments. Perhaps we’ll need to utilize several as there is rarely if ever only a single use case to solve for.

I’ll discuss the options in a future blog post.