I am struggling through a data model issue. I have three tables, a fact table F with transactions at the person, transaction number grain and amount, a dimension table P with person attributes where the grain is one row per person and lastly a table S that displays various services tickets that a person may have so the grain is person and service ticket number.
I joined P and F as a regular join with a cardinality of one to many join. Initially I joined S as a snowflake dimension to P pulling it in a another logical source in the Person logical object. However when I write a query against the three tables, my F.Amount column multiplies by the number of times a person has service tickets. This happens because the generated database sql has all three tables in the same select statement along with the aggregation for amount.
I then tried to model table S in as a sparse lookup with P. Now the db sql writes a select statement with P and F and a separate one for S and then does a left outer join between the first result-set and results from S. This produces the desired affect of amounts not being multiplied. However I also have a need in some cases to count the number of services or some times pivot the service tickets numbers by person. I am unable to apply any aggregate functions on lookup columns.
I also tried to model S as another fact table by creating a dummy measure, however although my amounts from F don't multiply, I do get all rows from the P and S query result-set that I do not want. There is no time period filter in S that I could apply and the table has millions of rows. So a full outer join between the resultset of P and S and result set of P and F would not be sustainable.
My goal is to make it easy for end users to use these 3 tables without having to a lot of workaround in the reports. I am ok with doing more work in the rpd in the beginning to mkae the model more robust. I would appreciate any suggestions.