0

I'm quite new to data warehousing and dimensional modelling and I need clarification on a few things. I currently have the following dimensions:

  • DimProducts - Information about a product.
  • DimMaterials - Information about materials that go into a product.
  • DimLocation - Different store locations
  • DimTime - Standard time dimension with year, quarter, month, week, day.

Now comes the confusion with regards to the fact table. Currently it has the following measures:

  • Revenue
  • Expenses

Questions:

  1. I would like to have net profit as a measure as well, but since it is a calculated measure, should it be a column in the fact table or should it be calculated at report-level? I'm a little unclear about the conventions with regards to calculated measures.
  2. I would also like to know how many raw materials are available at a point in time so that I may conduct calculations about how much product I can make (For example, 2 wheels for 1 bike means 50 wheels available in March would yield 25 bikes for month of March). Should I add a fact called raw material quantity?

I have a feeling I'm approaching question #2 wrong and that I need to create separate fact table to deal with the problem. Would really appreciate any advice/tips on whether or not I'm on the right track. Thanks!

Flynn
  • 215
  • 3
  • 5
  • 13

1 Answers1

0

think on your q1) if the net_profit=rev-exp , i believe this could just be a reporting logic unless otherwise you have a different calc that I dont see it here..

Q2) if i get this right, currently your fact only gets a rows only where you sell the material outside, in other words if you have stock in hand and sales not made there there is not entry, if this is what it is then in this case you may require to built Event based fact based out of your product/materia/Date or time

Bhaskerbobby Jsglkp

user2514207
  • 11
  • 4