Multiple Many-To-Many relationships

View previous topic View next topic Go down

Multiple Many-To-Many relationships

Post  Zen on Wed Aug 18, 2010 10:22 pm

I've read quite a bit on dimensional modeling but this would be my first go in reality. Any help would be greatly appreicated. Here's what I've got...

An existing subset of our DW would be:
Fact_tx
Dim_Customer
Dim_Product

One of our sources is from an ERP system with the following

Product
ProductCategoryMember
ProductCategory
ProductCategoryType
ProductCategoryRole
Customer (thereís actually another wonderful layer of abstraction with this guy too but Iíve simplified)

Product to ProductCategory is many-to-many through ProductCategoryMember
ProductCategory to Customer is many-to-many through ProductCategoryRole
ProductCategoryType is hopefully self-explanitory

All very nice for a dynamic ERP system but I canít figure out the best way to implement a Product Category Dimension for the DW.

So for a given Product in a transaction, there would be multiple Product Categories of varying types each having one or more associated Customers. Someone suggested flattening out the following fields into a Product Category dimension but it just confused me more.

(No surrogate key)
PRODUCT_CATEGORY_ID (business key)
PRODUCT_KEY (references dim_product)
PRODUCT_CATEGORY_TYPE
PRODUCT_CATEGORY_DESCRIPTION
CUSTOMER_KEY (references dim_customer)

I thought all of this information should be included in the Product dimension as it seems to be a hierarchy but the many-to-many-to-manys and customer reference throws me off from what I've read thus far.


Zen

Posts : 10
Join date : 2010-08-18

View user profile

Back to top Go down

Re: Multiple Many-To-Many relationships

Post  John Simon on Thu Aug 19, 2010 2:31 am

Why do you need to join the product, product category and customer dimensions together in a single dimension?
The fact table is where the many-to-many is resolved.

John Simon

Posts : 186
Join date : 2009-05-29
Location : Melbourne, Australia

View user profile http://jsimonbi.wordpress.com

Back to top Go down

Re: Multiple Many-To-Many relationships

Post  Zen on Thu Aug 19, 2010 8:20 am

Good question but I don't know the answer, which would be the reason for the post. Could you elaborate on how the fact would handle such relationships. I currently have a product key in the fact. For that product, there are multiple associated product categories. Those categories can be shared among multiple customers.

This information would be used in queries to filter output. For example, Give me a set of facts by products which are associated with Customer A's type B Category and Customer Z's type C Category. A product category is unique based on what type of category it is and what customer(s) the category is/are associated with.

Zen

Posts : 10
Join date : 2010-08-18

View user profile

Back to top Go down

Re: Multiple Many-To-Many relationships

Post  BoxesAndLines on Thu Aug 19, 2010 9:50 am

What does a transaction in your fact table represent?
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Multiple Many-To-Many relationships

Post  Zen on Thu Aug 19, 2010 9:53 am

The particular fact I'm working with now has POS transactions but there are other facts that will use these dimensions.

Zen

Posts : 10
Join date : 2010-08-18

View user profile

Back to top Go down

Re: Multiple Many-To-Many relationships

Post  ngalemmo on Thu Aug 19, 2010 1:03 pm

B& L's question is right on the mark.

ERP systems in general contain a lot of complex data relationships that may or may not exist in reality. And, even if they do, chances are that one of many potential states are assigned when the transaction occurs.

So, in this example, one would expect to see a product category code as part of the sales transaction. In other words, the ERP system, based on whatever business rules were established, figures out which category applies in the particular situation and assigns it to the sale.

If you want to be able to report all the potential relationships as some kind of dimensional report, fine. Factless fact tables can handle that. What you need to resolve from the business is, given a sale, can their be multiple product categories for the line?
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Multiple Many-To-Many relationships

Post  Zen on Thu Aug 19, 2010 2:13 pm

I guess the key phrase you used would be "potential relationships" because in reality, no one category is assigned when the transaction occurs. These product groupings would be used purely for analysis. Customer A and Product A may be assigned to the transaction but during analysis Customer B may want to pull a report for all Product transactions belonging to any number of their custom Product Categories.

So a factless fact table between dimProduct and dimProductCategory and another between dimProductCategory and dimCustomer is the way to go? Won't that be a performance nightmare? They're currently pulling reports at the individual transaction level in T-SQL against this dimensional model.

Thanks for your responses. I definitely want to reconcile the concepts I've read and this strange reality that the business has put us in.

NOTE: I just noticed that the suggested table I mentioned in the 1st post is actually a combination of the 2 bridge tables and the dimProductCategory table. If that is, in fact, the design you were suggesting.

Zen

Posts : 10
Join date : 2010-08-18

View user profile

Back to top Go down

Re: Multiple Many-To-Many relationships

Post  hang on Fri Aug 20, 2010 8:01 am

Zen wrote:Product to ProductCategory is many-to-many through ProductCategoryMember
Obviously in your case, there is no predefined merchandise hierarchy relationship between Product and ProductCategory. Product grouping may be more appropriate name for the relationship. It's interesting you have not mentioned any numeric metrics like quantity and dollar amount in your fact. I guess they are in the POS transaction fact you talked about.

Looks like you want to model an analysis fact table that is populated by the base POS transaction fact. I wonder if it is something to do with Market Basket Analysis. If yes, you are getting into data mining area. Maybe your ERP system has some sort of mining model to work out your product grouping plan based on the POS transactions. You may want to find out which product would a customer most likely buy if he or she has purchased a group of other products so that you can further fine-tune the product grouping.

As ngalemmo suggested and you worked out, your two factless fact tables is good start. In DW term, they are called coverage table. The real fact is your POS transaction fact that contains relationship between product and customer. With these three facts in place, you could conduct a number of analysis's to verify the product grouping strategy coming out of ERP system.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Multiple Many-To-Many relationships

Post  Zen on Fri Aug 20, 2010 8:46 am

I knew I should have renamed customer when posting this. Sorry for the confusion. Instead of customer, think Merchants, Vendors, etc.. Customer would be a poorly named role-playing dimension in this case.

So a merchant sells a vendor's product and a transaction is recorded. That merchant and vendor are obviously associated directly to the fact. On the reporting side of things vendors may want to look at subsets of merchant's transactions based on product groupings (I agree that this would be a better name) and vice versa. These product groupings can and do overlap in many instances.

A vendor may run a report that returns transactions based on one of their product groupings or even one of the merchant's product groupings. To get the appropriate product groupings to select from, the bridge between DimProductCategory and DimCustomer obviously must be present.

I'll see if I can convince someone to go down the path with these factless fact tables. I appreciate the feedback

I've never used a dimensional model for non-aggregate, transaction level reporting before. I've always seen it as a means to an end for OLAP cubes. So I worry about using this sort of structure for this purpose. Would a more normalized structure be better for this type of reporting? Any suggestions or assurances?

Zen

Posts : 10
Join date : 2010-08-18

View user profile

Back to top Go down

Re: Multiple Many-To-Many relationships

Post  hang on Fri Aug 20, 2010 10:08 am

Although factless fact does not have explicit numeric metrics, what we are really interested is the count which can be represented by adding an additive integer field with 1 or 0 into the table. Sometimes you may need to add explicit rows to the fact with 0 value to represent the event that didn't occur in the transaction.

Dimensionally modeled data is the most suitable and efficient source for OLAP cubes. I can't see normalised structure can simplify the process any better. I suggest to take close look at Kimball's book "The Complete Guide to Dimensional Modeling" second edition, particularly on page 49, 62 and 251, where he elaborated on how to leverage factless fact in dimensional modeling.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Multiple Many-To-Many relationships

Post  Zen on Fri Aug 20, 2010 10:21 am

Dimensionally modeled data is the most suitable and efficient source for OLAP cubes. I can't see normalised structure can simplify the process any better. I suggest to take close look at Kimball's book "The Complete Guide to Dimensional Modeling" second edition, particularly on page 49, 62 and 251, where he elaborated on how to leverage factless fact in dimensional modeling.

I am aware that the dimensional model is more efficient for OLAP cubes. As I said, this is being used for non-aggregate, transaction-level reporting. Queries for these reports would be accessing the tables directly in T-SQL. My question was which would be more suitable for this type of reporting.

I will definitely be investing in several of Kimball's books.

Zen

Posts : 10
Join date : 2010-08-18

View user profile

Back to top Go down

Re: Multiple Many-To-Many relationships

Post  ngalemmo on Fri Aug 20, 2010 1:27 pm

Ok, so these are reporting relationships that the users (i.e. Merchants) invent to produce reports.

Put the dimensional model aside for a moment. What drives the definition of these groupings? I assume you have some data feed coming in that says, for this merchant, if this customer (or customer group) bought this product (product group), call it "X". In the ER modeling world, this would be called an associative entity. In the dimensional modeling world, it would be called a bridge. A bridge is a table that sits between a fact table and a dimension table to associate facts with a dimension.

Usually bridges are used to handle hierarchies and multivalued dimensions, but it can also be used to handle complex categorizations such as this.

So, you have a dimension table that contains the text and whatever else you need for these groupings, an a bridge table that contains customer, product, and merchant FKs (which correspond to the FKs in the facts) and an FK to the category dimension. The user would select which set of categories they wish to use (based on attributes in the category dimension) and you would join to the facts through the bridge.

If these categorizations are defined based on groupings of customers and products, you have to make some choices as to how you build the bridge. The bridge must contain FKs that can be found in the fact table. So, you either create a customer and/or product group dimension and place FKs in the fact and use them in the bridge, or you compile the bridge table by including all customers and/or product keys based on which customers/products belong to the respective groups (you do not what to do this at query time). The latter has maintenance issues that will require updating the bridge as customer or product categorizations change.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Multiple Many-To-Many relationships

Post  Zen on Thu Aug 26, 2010 2:37 pm

Thank you all for your input. After a few design sessions it was agreed upon to create a Product Category Dimension and 2 bridges, one between Product Category and Product and another between Product Category and the Parties (Merchant,Vendor,etc). For reporting performance, a consolidated table or indexed view may also be created to combine these tables into 1. So in the end, we actually decided to implement both design considerations.

Zen

Posts : 10
Join date : 2010-08-18

View user profile

Back to top Go down

Re: Multiple Many-To-Many relationships

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum