Fact Tables Joining to Factless Facts

[Yolanda Harris]

Just wanted to get some information on pros/cons with regards to joining my fact table with a factless fact. What we are discovering is that if we keep all non-grain defining dimension sk on every fact table, our maintenance increases tremendously. Even when we use the factless fact table to lookup the relationship, we still have to maintain that relationship on every fact table that contains this information. Please let me know your thoughts.

[Vishy]

Please explain in detail what you have and what you want to do.

here we are talking about many to many relationship, if you don't have any other way then surely reporting layer can handle this but wanted to know what exactly you have.

[Yolanda Harris]

We want to keep the relationships all in one table because what we are finding is we have many fact tables that contain the same relationships (such as policy, underwriter, organization, distributor, etc.). Because of this we need to update many of these fact tables when the relationship changes (ie. the policy is moved to a new organization structure). Now each of these fact tables have a different grain, but those relationships I mentioned are common across the majority of our fact tables. So what we'd like to do is keep the relationships on the factless table(s) and create our atomic level fact table only with the grain defining dimensions and measures. We then will join the dimensions to the factless table and the fact to the factless table. This also keeps our Business Objects universe much cleaner. With multiple fact tables in a universe, all relating to the same dimensions, the universe has to introduce "context" so that the sql is generated correctly. To sum, we want to cut down on the etl updates, make our bo universe design simplier, and ensure all our facts contain the same relationship data.

[BoxesAndLines]

I'm not sure I follow exactly what you are trying to accomplish, but I don't think it is a good idea. I like my fact tables to stand independently. I don't want to have to drill across to another fact table at a possible different grain to find the FK to my dimension. What you can do from an ETL perspective is load the factless fact table and use that as a lookup for all other fact tables to populate your dimension FK's. That should simplify the ETL process greatly.

[Yolanda Harris]

Understood, and that's exactly what we've been doing, but that's with a huge cost. It's more of an issue with the way our business users want to see their data and the complexity of using Business Objects in our presentation layer. I'm just trying to get some concrete feedback on why this shouldn't be done. From what I've tested, this will work for us and has no negative impact. I'm trying to figure out if there will be long term reprecussions that I can't think of right now. Currently building the fact relationships from the factless table is what we are doing, but this causes dependencies. We'd like the fact table to be geared towards the measurement event only. We are trying to prevent revisiting the fact row to update non-grain defining foriegn keys. Any reasons anyone can give that we should not do this would be greatly appreciated.

[ngalemmo]

Because of this we need to update many of these fact tables when the relationship changes (ie. the policy is moved to a new organization structure).

It doesn't sound right... you don't want a situation like that, but I don't think what you are describing is a good solution. What are these organizational structures and what are they based on? Under what situation would a policy change organizations?

[Yolanda Harris]

A Policy can change organizations, distributors, underwriters, anything at anytime. This could be a coding mistake (a producer was inadvertanly recorded for the policy), an underwriter no longer works for the company (the new underwriter assumes all the old underwriters policies), or our annual restatement of org structures (a yearly process where our business units decide how they want to organize thier book of business for the upcoming year). Our users always want to see the measures with the most current dimensional data. While we can revisit the fact row to update the dimension, we are trying to figure out a way where each of our Facts won't have to do this recast work for every relational update.

[hang]

I wonder if you have policy number as a degenerate dimension (DD) in most of your fact tables. If you track all the relationships around policy in one factless fact table, a single DD should carry the context (dimension FKs), historical and current, around policy across different fact tables. Why do you need to repeat FKs instead of just one policy number (DD).

If I am not on the point, please provide your fact and dimension tables so that we can pick up if there is any misunderstanding.

[BoxesAndLines]

These might be reasons why it's not a good idea. But then again, if the business processes are so bad that massive updates are required to restate all facts, these may be more of a nitpick.

It makes it more difficult to report outside of BO.
It slows down all queries due to extra joins.
It hides the grain and dimensionality of all the facts making the data model less understandable.

[Vishy]

Organizational structure change,underwriter change ... these events are not generating any measures I guess...
Can't we take all these as an attributes of policy and treat them as SCD2 ?

[hang]

Can't we take all these as an attributes of policy and treat them as SCD2 ?

Along the similar line, I was thinking about transaction dimension. But kimball referred to policy number as DD in a number of fact tables in the insurance case in his book. My take is, you will need the policy accumulating snapshot table anyway, as it's super effective mechanism to work out many measurements in the policy life cycle.

I guess it makes sense to base the policy on an accumulating snapshot fact with all the relevant dimensions around it, as you dont't want to end up with a fact table (accumulatin snapshot) and a dimension of the same size, if not bigger considering SCD2 impact.

I have similar dilemma in my work with work order modeled as a dimension of millions records and there are a number of status and date attributes in it. Obviously you don't want to issue many SCD2 records because of their changes in the life cycle as that will further blow out the dimension with limited usage to measure the work flow. The best way out of this is to start with a far slimmer dimensionalised accumulating snapshot table for work order and use work order number as a DD as conformance point in all other relevant fact tables. In this way, you don't conform on a bulky dimension with all the relvant and highly repeated attributes (and type 2), but rather, on a star with a much leaner fact table with normalised FK's around it.

[Sponsored content]