Data modeling architecture

[oll]

Hey,

I am trying to create a dimensional model using the techniques of Kimball, and I have come across a few challenges that I was hoping someone could help me with.

1.

I have a facttable that includes data of the sales process.

The table is linked to the department dimension, and the department dimension is linked to the Employees/Salespeople dimension.

However, there are no records of the employees with regards to who made the sale, so it can't be placed through a FK in the factSales table. So I wonder if it is an acceptable solution to have the employee dimension connected with DimDepartment in that way, or if it is a better way to model it.

2.

The ProductDim is also connected to factSales. Each product, (cakes), however consists of several ingredients. Therefore I don't consider it a good solution to have product key, product name, product price, along with ingredient1, ingredient2, 3,4,5; name ingredient1, name ingredient2,3,4,5, price ingredient1, price ingredient 2,3,4,5 and so on... in the product dimension.

I wonder if the best solution is to have the Ingredients referenced in a outrigger/snowflake (don't know which one it would be) dimension, or if there is another way to model it.

3.

For the sales process, there is data for date and time. For the order data, only data for the date. Do I need to include two seperate time dimensions, because of the difference in granularity, or could I use the same with a time attribute, and have 'blanks' for all the order rows in the dimension?

Thanks in advance!

[CahabaData]

1. If as you say there are no records of the employees with regards to who made the sale.  I don't see that there is anything to do or can be done.  If there is department info in sale - all that can be done is link/report all employees of that department.

2. You will have a Product table.  It is a matter of design choice whether the Product table includes ingredient info - or - whether you have ProductIngredient table with a relationship join.  Usually the tables come from database applications and so the Power BI developer begins with what is given to them.  So it is a little unusual to confront this issue in Power BI.

3. While I don't quite understand the difference between sales process data versus order data; generically speaking if your reporting need is only to the day - and not to the minute - then one just ignores the time info;  if it is to the minute then one would have to invent & use a default time where none exists.