Design a fact table in efficient way

Question

Hi,

I need some advise on designing a fact table (pic 2). I have 5 tables as below (pic 1). Each table has first two common columns (BranchNames being unique) which I think would go under a dimension table. Table 2 & 5 has one column (middle ones) (It has unique values) could be used as a degenerate dimension (Dont want to create a dimension again to just generate a key for one column) so those will be gothe fact table) and finally, table 3 & 4 has product detail columns(middle ones again) which will come under a different dimension.

The problem I am facing is the records are getting duplicated at the very last three fields in the fact table (coming from table 5) as there is one entry in every column of the three for each unique areacode. So overall the table is becoming a very large one with millions of records. Multipied for each entry.

Note : I want to design the fact table at branch level so I have used the branch key.

Could someone suggest a better design to avoid the issues mentioned above , please?

Thank you in advance!

Answer 1

Do distinguish between a large number of records and duplicated records, these are two very different problems. For the only issue you actually mention, the number of rows, this is solely determined by the grain of your fact table, it is the number of existing unique combinations of values in your key column(s). When modeling your data, you start with the analyses it will have to support, which should determine the required grain, and next you model the data at least at that grain, maybe finer. If this means millions of unique rows, then you need a BI solution that can handle them, that's all. FYI, millions of rows is peanuts for Tabular.