-1

In My database I have invoice and project some of invoice are related to project some of them are not, It means some Invoice will be created base on project details and milestone some of them are on independent so fore creating a Date warehouse is The following data model is correct? or should I bring project measure in to "Fact Invoice"?

I want to know if my data model is right? is it alright to have more than 1 Fact Table?

sanaz amini
  • 49
  • 1
  • 2
  • Assuming that you want to be able to query invoices regardless of whether they refer to projects, then the data should be in a single fact table. There’s nothing wrong with the fact table FK referencing a “not applicable” row in your project dimension – NickW Apr 29 '23 at 11:47
  • thanks but I have some measure like project total price or tax and .... that will be needed in some KPI Such as expected Income, Delay and .... – sanaz amini Apr 30 '23 at 09:13
  • Going back to basics: 1) Define your reporting requirements 2) define the fact table(s) to support these requirements 3) identify the grain of the fact tables 4) add measures/dimensions to the fact table that are consistent with its grain. If you have measures that aren’t consistent with the grain then either create a new fact table or change the grain of an existing fact table - having done an impact analysis on what changing the grain would affect – NickW Apr 30 '23 at 14:12

1 Answers1

0

Base On What @nickw said, I reconsider and figure out that I will need 2 Fact Table and 8 Dimension Table but is it correct that 2 Fact Table has relationship because it might create loop in data model

sanaz amini
  • 49
  • 1
  • 2
  • What do you mean by a relationship between 2 fact tables? How are you defining a relationship? – NickW May 01 '23 at 11:19
  • I guess there is two way to make this work scenario : I have a project that may have 1 or n Invoice I have a in voice that maybe related to a project and maybe not related implantation A: put FK of project in Invoice that make relationship between 2 Fact and may cause loop implantation B: Create column and put ProjectID in that but don't make relationship and then I can use it on visualization to show future Income amount Which one is better or do I have better Option? – sanaz amini May 03 '23 at 08:47
  • You still seem to be treating this as a technical exercise in deciding whether to add foreign keys to a table. You need to treat this as a business problem: what are you trying to report on, what are the measures needed to support these reports and what are the grains of these measures - which defines the grain(s) of the fact table(s) and therefore their design. If by "relationship" you mean the same dimension table is linked to more than one fact then I'm not sure what the relevancy of this is? You never select from two fact tables in the same (sub)query so there is no "query" relationship – NickW May 03 '23 at 09:21