The way to create a classic snowflake, let us remind ourselves, is to remove low cardinality attributes from a dimension table and place these attributes in a secondary dimension table connected by a snowflake key. In cases where a set of attributes form a multilevel hierarchy, the resulting string of tables looks a little like a snowflake - hence the name.
The customer dimension is probably the most challenging dimension in a data warehouse. In a large organization, the customer dimension can be huge, with millions of records, and wide, with dozens of attributes.
To make matters worse, the biggest customer dimensions commonly contain two categories of customers, which I will call "visitor" and "customer." Visitors are anonymous. You may see them more than once, but you don't know their names or anything else about them. On a Web site, the only knowledge you have about visitors is a cookie indicating they have returned. In a retail operation, a visitor engages in an anonymous transaction. Customers, conversely, are reliably registered with your company. You know customers' names, addresses, and as much demographic and historical data as you care to elicit directly from them or purchase from third parties.
Let us assume that at the most granular level of your data collection, 80 percent of the fact table measurements involve visitors and 20 percent involve customers. You accumulate just two simple behavior scores for visitors consisting only of recency (when they last visited you) and frequency (how many times they have visited).
On the other hand, let us assume you have 50 attributes and measures for a customer, covering all the components of location, payment behavior, credit behavior, directly elicited demographic attributes, and purchased demographic attributes.
Now you combine visitors and customers into a single logical dimension called shopper. You give the visitor or customer a single, permanent shopper ID, but make the key to the table a surrogate key so that you can track changes to the shopper over time. Logically, the shopper dimension has the following attributes.
The attributes for both visitors and customers are:
- Shopper surrogate key
- Shopper ID (fixed ID for each physical shopper)
Attributes for customers only are:
- Five name attributes
- 10 location attributes
- 10 behavior attributes
- 25 demographic attributes.
This is a clear case where, depending on the database, you want to introduce a snowflake. You should break the dimension into a base dimension and a snowflake subdimension. All the visitors share a single record in the subdimension, which contains special null attribute values. (See FIGURE Below)
FINANCIAL CUSTOMER DIMENSIONS
Assume you are modelling for a bank which processes credit applications for corporate customers. Though it calculates the risk based on many factors to satisfy regulatory requirements, One of the important factors it also considers is the Audited Accounts of the customer for past few years. Now the customers submit their financial statement yearly / Semi-Yearly / Quarterly / Monthly based on the covenants & conditions that they agree with the bank at the time of their credit approval
So the model should accomadate mutliple financial statements for a single customer. Here you can model a snowflake dimension CUSTOMER & CUSTOMER_FINANACIALS having 1-N relationship
FINANCIAL PRODUCT DIMENSIONS
Banks, brokerage houses, and insurance companies all have trouble modeling their product dimensions because each of the individual products has a host of special attributes not shared by other products. Except for a set of common "core" attributes, a checking account doesn't look very much like a mortgage or certificate of deposit. They even have different numbers of attributes. If you try to build a single product dimension with the union of all possible attributes, you end up with hundreds of attributes, most of which are empty in a given record.
The answer in this case is to build a context-dependent snowflake. You isolate the core attributes in a base product dimension table, and include a snowflake key in each base record that points to its proper extended product subdimension. (See Figure below.)
This solution is not a conventional relational join! The snowflake key must connect to the particular subdimension table that a specific product type defines. Usually you can accomplish this task by constructing a relational view for each product type that hardwires the correct join path.
MULTINATIONAL RETAILER CALENDAR DIMENSIONS
Unlike the financial products dimensions, each of the separate calendars can have the same number of attributes describing fiscal periods, seasons, and holidays. But there may be hundreds of separate calendars. An international retailer may have to deal with a calendar for each foreign country.
In this case you modify the snowflake design to let the snowflake key join to a single calendar subdimension. (See Figure Below.) But the subdimension has higher cardinality than the base dimension! The key for the subdimension is both the snowflake key and the organization key.
Fig: A Calendar dimension with a higher cardinality subdimension.
In this situation, you must specify a single organization in the subdimension before evaluating the join between the tables. When done correctly, the subdimension has a one-to-one relationship with the base dimension as if the two tables were a single entity. Now the entire multienterprise data warehouse can be queried through the calendar of any constituent organization.
These three examples show how variations of snowflake designs can be very useful. I hope you feel more confident about answering the question, "When can I use a snowflake?" When you are thinking about design alternatives, you should separate the issues of physical design from those of logical design. Physical design drives performance. Logical design drives understandability. You can certainly use snowflake designs if you maximize both of these goals.
Courtesy: Ralph Kimball