Combining Factless Facts and Bridges - sensible or not?

View previous topic View next topic Go down

Combining Factless Facts and Bridges - sensible or not?

Post  D_Pons on Wed Feb 11, 2009 5:21 am

We are working in a MS SQL Server 2005 environment using SSIS to populate our DW DB and SSAS to build our OLAP database from that DW DB.
It is not a huge database in the scheme of things - 28 million payments, 6 million customers, 2 million membership rows (after type 2 changes applied).

We have a scenario where our 'Customers' can have one or more 'Memberships'.
Each of these are created as Dimensions in our DW DB.
The 'Customer' dimension is a large dimension with lots of type 2 changes happening, so we have used mini-dimensions and a factless fact table to track our type 2 changes.
Ref Kimball tip #50
In our case this then leaves the customer dimension with only type 1 or type 0 attributes in it.

The 'Membership' dimension is a typical dimension with some type 2 attributes.

We have a transaction fact recording payments made by our customers.

In order to show the Membership details of a paying customer, we need to join 'Customer' to 'Membership'.
To join 'Customer' with 'Membership', we have a bridge table.
This has one row per 'customer'-'membership' relationship.
There is a 'role' dimension to describe the type of that relationship.

The bridge table needs to be updated every time there is a type 2 change in 'Customer' or 'Membership'.
However all our type 2 changes for 'Customer' are tracked in the Factless Fact table.
So, strictly, the bridge table ought to have keys to the mini-dimensions and a new row get written every time we need to key to a different mini-dimension.
This will be at the same time as new rows are written to the factless fact table.
So for changes due to 'Customer' changes, the bridge and factless fact will have new rows at the same rate. The bridge will also have new rows due to changes on the 'Membership' side.

So here is question one.

Does it make sense for the bridge table and factless fact table to be merged in to a single table?


On to the second part.

If we want to count the number of 'customer'-'membership' relationships of a particular type or if we want to count the number of changes in relationship of a particular type, an option is to have counts in our bridge table. For example, to count the number of recruited members, we could have a count set to 1 each time the change row is representing when a new customer-membership relationship is created.

At this point, our bridge table / factless fact table has become a normal 'transaction' fact.

So question two is:

Is it sensible to have facts / measures in a bridge table?
If so, does this mean that generally, factless fact tables and/or bridge tables, are just normal transaction fact tables where we have chosen not to 'measure' the transaction event that they are recording?

I hope I have made sense and, if so, look forward to your thoughts.

D_Pons

Posts : 16
Join date : 2009-02-10
Location : UK

View user profile

Back to top Go down

Re: Combining Factless Facts and Bridges - sensible or not?

Post  BrianJarrett on Wed Feb 11, 2009 12:58 pm

Ponzie,

I'll try to take a stab at this but I don't really know your systems nor your exact requirements. I'll probably need some clarification.

I have a couple questions about your current design. First, you mention you have a customer and a customer minidimension. You also mentioned that you were joining these together with a factless fact table. I'm curious why you chose to use the minidimension rather than just doing type 2 changes in the customer dimension.

I've used a minidimension before but I did it because the rapidly changing attributes in the minidimension were associated with an actual fact record. The attribute in this situation was a child's age at the time of a mailing list extraction. Since the child was always going to be older on the next mailing list a type 2 change would have forced a new dimension record each time a mailing list was generated. With so many mailing lists it would have caused the dimension to grow tremendously, and only to modify one field to acocunt for the child's age at the time of the mailing. In this case the minidimension was tied directly to the mailing list fact table, not the full child dimension.

Anyway, it seems your minidimension, since it's associated with a factless fact table, might be better represented by a simple type 2 change in your customer dimension. That's how it seems on the surface, at least. Please feel free to elaborate.

Second, I have a question about the "Membership" dimension. It would appear that this dimension isn't really associated with any fact records. It looks more like a group of attributes about a customer. I think you mentioned you join this to the customer with a bridge table but the relationship is a one to one. On the surface this seems like a candidate for an outrigger or maybe just merging the membership information right into the customer table.

You mentioned however that in your bridge table you're storing a "role". This would lead me to believe that the relationship isn't really one to one but rather many to many; hence the bridge table. If I understand correctly, each intersection between a membership and a customer is differentiated with a "role" moniker. It almost seems as if the bridge table is acting more like a factless fact table here (see below).

Is membership a true dimension? Is it joined to any fact tables? Can the same membership entity be assigned to multiple customers (one to many)? If so then it would appear to be correctly modeled as a dimension. If membership is more of an attribute then maybe it belongs in the dimension table. (If that's true then you could still pre-count the number of customers belonging to a particular membership and store them in another metrics fact table.)

I'm also curious if a membership is counted at the time of a payment transaction (the true fact table you mentioned) or can a customer's membership change at any time? If it's a true dimension and the intersect is at the time of payment then I'd join it to the payment transaction fact table.

Those are my questions for your first question.

As for your second question about having facts in a bridge table; I think I mentioned that above. Facts in a bridge table make it sound more like a fact table. Even in a factless fact table there is an implicit measure of "1" representing the intersection of the foreign composite primary key.

I hope this was clear; this looks like a non-trivial modeling exercise. Please let me know if I've made any bad assumptions. Once I know a bit more maybe I could throw together a quick visual mock-up of what I'm talking about here.
avatar
BrianJarrett

Posts : 61
Join date : 2009-02-03
Age : 43
Location : St. Louis, MO

View user profile

Back to top Go down

Re: Combining Factless Facts and Bridges - sensible or not?

Post  D_Pons on Wed Feb 11, 2009 1:55 pm

Brian,

thanks for comprehensive response.
Despite taking time over my original post, and even getting a colleague to check it over, there are some aspects that I did not made clear enough.
I did find it hard to ask a general question, with sufficient information by way of example, without it seeming like a major consultancy exercise ;-)

Anyway, I will clarify where I can...

The Factless Fact table and Mini-Dimensions for the Customer dimension are to enable us to handle type 2 changes that we considered were taking place rapidly.
c300,000 changes in 2 months.

The relationship between 'Customer' and 'Membership' is many-to-many. Customers can buy their own membership or pay for someone else’s.
A 'Membership' may give entitlements to one or more people.
The Role denotes whether the relationship is between the customer and their own membership or the customer and someone else's membership.

The 'Membership' dimension serves as a Fact table itself. The users can ‘Profile’ the Memberships according to the various characteristics of that membership - (how it is paid for, how it was sold, how long it has been active etc.

The 'Payment Fact' currently captures all payments - whether they were made for a Membership or some other Product. We do join from this transaction fact to the Membership dimension for the Payer, thus allowing the users to see if any Products tend to be bought be people in a particular type of Membership.

We also have a snapshot fact joining to the Membership dimension and this allows us to show Membership counts over time for different Membership characteristics at the time of the snapshot.

Some of the Membership attributes change at the time of a payment. Other attributes may change for other reasons.

All that said, and thinking around what you have said, I wonder (not for the first time) whether we actually have other Transaction Facts that we ought to be recognising and explicitly building tables for. For example a Membership Recruitment Fact. And may be I'm trying to be too clever (?) by considering adding facts to the bridge table instead of making it clear what the real transaction facts are.

I hope that has clarified rather than confused further.

D_Pons

Posts : 16
Join date : 2009-02-10
Location : UK

View user profile

Back to top Go down

Re: Combining Factless Facts and Bridges - sensible or not?

Post  BrianJarrett on Wed Feb 11, 2009 2:45 pm

Unfortunately designs such as this are difficult to glaze lightly over and still get the answer, at least for me.

To get back to your general question I think we're on the same page as far as the bridge table goes. I typically think of a bridge table serving one primary function; joining a dimension table to a fact table where the relationship is many to many. This table has two keys in it, along with a divisor (and maybe some housekeeping fields).

I think when it becomes more than that it's a fact table in and of itself.

So I think what I'm talking about here is a payment fact table joined to customer and membership (which you already have). Given what I know that should be a standard one to many as far as membership goes; only one membership can be joined to a single line of your payment fact table.

I'm also thinking about another fact table, this one based on a customer's membership(s). This table is joined to a date dimension, a customer dimension, and a membership dimension. (It might be joined to your customer minidimension as well.) In this the facts are a count of 1 for each intersection (like a factless fact table) but also a "role" indicator. I think you've already identified this one as a "Membership Recruitment" fact table.

Speaking of the minidimension; I wonder if that table is better suited to be an outrigger? Here's an example article: http://www.intelligententerprise.com/020812/513warehouse1_1.jhtml

I don't know your data well enough to say for sure; just a thought.

So, in summary, I think of a bridge table existing primarily to satisfy a many to many relationship between a dimension and fact record. I don't see the customer to membership relationship this way. I see this as its own fact table. You should then be able to drill across both fact tables to determine not only who purchased a membership (payment fact) but also what role that membership fills for that customer (new "membership recruitment" fact table). That would require a multi-pass SQL approach but most reporting tools should handle that automatically.

I hope this is helpful and that I've understood everything. It sounds like we're on the same page here. If you have any questions or I can be of any help please let me know.
avatar
BrianJarrett

Posts : 61
Join date : 2009-02-03
Age : 43
Location : St. Louis, MO

View user profile

Back to top Go down

Re: Combining Factless Facts and Bridges - sensible or not?

Post  D_Pons on Wed Feb 18, 2009 5:55 am

Brian,
thanks for taking the time to post on this.
With reference to the 'What Changed' outrigger idea. As I understand it, this outrigger would be used to track changes in a normal SCD2 dimension table. It does not avoid the creation of type 2 rows in the dimension itself. In order to achieve the effect of the 'What Changed' outrigger, we would need to join this to our factless fact table that is tracking the type 2 changes for us.

D_Pons

Posts : 16
Join date : 2009-02-10
Location : UK

View user profile

Back to top Go down

Re: Combining Factless Facts and Bridges - sensible or not?

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