April 26, 2019 at 9:22 am
The fact table as of now 100 + string fields. These are mostly fields like reference numbers. For example , in case of a shipment , the fields product and order reference numbers, BL numbers and references , vessel , voyage, seal numbers etc.
These would repeat for a few shipments but majorly do not repeat . All these values are at the SHIPMENT grain which is also the grain of the fact table. In other words, 100+ degenerate dimensions sitting in the FACT table.
Users wants to have these fields for Reporting .
What is the best solution for this ?
I am thinking of separating these fields out to a separate FACTLESS FACT table , or a SHIPMENT ATTRIBUTE DIM table. and have a surrogate key or the natural key be used as a join between these 2 tables.
Does that sound right ? Any other ideas ?
April 30, 2019 at 11:13 am
Hi Koustav, you could indeed have the solution you suggest, but I am not sure what the relative benefits would be. In this sort of unique situation, I would honestly look at just leaving the table as is with just the standard: date, customer, warehouse, product, etc. surrogate keys for their 'enrichment' purposes, and leaving the 'almost unique' shipment descriptive fields just as they are, i.e. not necessarily doing the 'theoretically ideal' solution. You could do 'vertical partitioning' for performance purposes if there is a primary key, and there are other uses for the facts apart from the descriptive fields.
I would strongly recommend that you try the alternatives and measure their relative performance and space usage for: loading, storage and query/reporting; although I have a gut feel that my suggestion will be relatively the quickest, smallest and simplest to maintain.
I am misquoting, but to paraphrase something I learned from Ralph Kimball: there is a rationale behind the star schema patterns and theory, but if the circumstances do not fit the rationale precepts or bases of the theory, then the patterns might not be most appropriate and creativity is required. Experiment....
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply