Fact Table Re-naming with new business process

Our fact table, fact_case, contains transactions relating to work that is performed by field interviewers. Now we are adding similar transactions for our call centers and considering a fact table named something like, fact_case_phone. Would you consider re-naming fact_case to fact_case_visit or leaving as fact_case? Just wondering what others were thinking? Of course, I know what developers would say!

As long as the dimensionality is 90%+ the same, I'd probably have just one fact table. You can add a transaction type dimension to distinguish them. If you go high enough up your org chart there is probably someone interested in a consolidated analysis. You can use their name to make your case (npi).

The only issue with one table is if there were attributes in the other mechanisms that that were unique to those mechanisms.

Let's say you already had a lot of dimensions for the Call Center that you wanted on the Case. If you had one Fact Table, you'd have a lot of columns with the default value. And if you added other "channels" later, this would get multiplied.

You could create seperate fact tables for each "channel". If you needed to query the cases for all channels, you could create a Union View across the individual fact tables, including the the common dimensions.

Either method is OK.

mr_neal wrote:Our fact table, fact_case, contains transactions relating to work that is performed by field interviewers. Now we are adding similar transactions for our call centers and considering a fact table named something like, fact_case_phone. Would you consider re-naming fact_case to fact_case_visit or leaving as fact_case? Just wondering what others were thinking? Of course, I know what developers would say!

Yeah, the developers are going to say "What? Why? We are going to need to change a bunch of stuff...".

So, why? It would seem to me, just updating you data dictionary with a new description of what the table contains should suffice. Changing the name of the physical table doesn't buy you anything other than extra work... Besides, if you have BI tools, end users are never going to see it.

Mike Honey wrote:As long as the dimensionality is 90%+ the same, I'd probably have just one fact table. You can add a transaction type dimension to distinguish them. If you go high enough up your org chart there is probably someone interested in a consolidated analysis. You can use their name to make your case (npi).

Mike, actually the dimensionality is about 90% when comparing Field and call centers, and I could justify adding a transaction type. However, as we add email and internet as a process for interacting with our customers, they would run more like 20%! Also, after a recent class on data warehousing, and some other feedback on this topic, it was preferred that fact tables be broken up by process, and these are certainly very different processes! I just felt like now was a better time to split them up rather than trying to make them all fit the "mold".

ngalemmo wrote:

Yeah, the developers are going to say "What? Why? We are going to need to change a bunch of stuff...".

So, why? It would seem to me, just updating you data dictionary with a new description of what the table contains should suffice. Changing the name of the physical table doesn't buy you anything other than extra work... Besides, if you have BI tools, end users are never going to see it.

Thanks ngalemmo! To my own demise, I am a bit of a purist! I guess I could just rename the table and create a synonym! Do you agree that it is appropriate to split up the fact table?