Microsoft acquires MDM vendor Stratature

Microsoft recently purchased a Master Data Management vendor Stratature. After I looked at their web site, I guess that Stratature’s offerings seems falling into the BI related MDM , like what Oracle - Hyperion’s product. Their webinar is co-presented with Joy Mundy from the Kimbal Group.

Stratature provides the “Dimension Manager” as the solution for Customer Data Integration. Based on the name of the product, we can also know their solution is BI centric.

Here are several requirements I guessed they tried to target:

Data Warehouse Design Tip: How to model the many to many relationship between the fact and the dimension table?

Sometime we have a many to many relationship from a fact to its dimension and the dimension contains multiple elements which can be considered as a group or a set. How do we model this?

The above statement must be confusing. Let’s use an example. A people can have multiple skills and a skill can be held by multiple people.

One way is to treat this as a fact with both the employee and the skill dimension. You will be able to know how many employee with a given skill and how many skill an employee may have.

The above approach works fine if the business question is just that simple. However, let’s look at other related fact tables. You may have an employee capacity table, which include two dimensions, the employee dimension and date dimension. Each record of this fact table includes the number of hours available on a given day. So from this fact, you will know who are available for a given date or a range of dates. You will also be able to know what the period a employee are available and when will be the next available date.

The tricky question now comes. How can we know all employees with a given set of skills available in a given range of dates? How can we design our schema so it can be done very efficiently?

Aggregation for Data Warehouse, Part 5

This is the final posting about the aggregation for data warehouse. In the prior posts, I described the following approaches of providing aggregation for data warehouse.

1. ETL and aggregated tables

2. Hand code summarization processes and aggregated tables

3. Materialized Views

I think that for a large scale data warehouse project, a two tiered approach may be favorable. When I say a two tiered approach, I mean that you can have a data warehouse, and …