@GarryA correct, all donations are in the Gifts table. The Value-column in the Agreement table is only the contracted value that the customers signs off to donate, but the customer can decide to increase the gift and e.g. pay $110 one month instead of the contracted $100. So to sum up the value donated per area/project/date/ (whatever dimension) you only need the gifts table. Everything is stored in a SQL-server database. The tables are more or less a copy of these but I have merged eg. AreaId to the gift and agreement table.
So does the SQL tables have a Money received table that refers to either the GIFT or AGREEMENT table for the fact transactions?
Is this an accounting system maybe someone here is familiar with your package or there is documentation on the internet regarding how the fact tables are configured for the solution you are using?
Hi!
This is no accounting system but a custom made system for this client. The Gift and Agreement tables are both from the systems database. Money received is what is in the Gift table.
So your above statement suggests that the GIFT table is the main transaction table. (All Money Received) regardless of whether an agreement exists or not is shown here in this table right?
Correct. All analysis regarding donated amounts can be found here regardless whether and agreeement exist or not and this is key information for the client to analyze.
@Frede, look I think I can help but this is a fairly big job to clean up your model queries and reorganise the dimensions. Your data looks incomplete too…The solution is beyond what I can really give in a forum via messaging…but try and get your model to something similar to this…
Do some counts and then dump them into tables to check the output and agree results…Again try and cut down and consolidate the Entities like Customer, Individuals, Organisations if possible. Once it’s streamlined the results should be easier to obtain.
I do provide Power BI Consulting assistance to the general public so please private message me if you want direct guidance as a client.
Cheers Garry
Thank you for your suggestions, but the only real difference from your model and mine is that the relationships to the date and individual dimensions are removed from the Agreement fact table and I don’t see how that will help. Those dimensions are needed to be able to do time analysis on e.g. active agreements (ClosedDate vs RegistrationDate) and also Individuals with agreements etc.
I understand that it is hard to give concrete advice in this form and appreciate all the answers and suggestions.
Thanks
Fredrik
https://forum.enterprisedna.co/uploads/db7134/original/2X/1/18e9899c5cf28c3f29db3a0ced74e1ea60bac67e.png
This is great.
Personally the only other update I would make Is to move the agreement table down to the fact table layer.
Then to me this is done, and would reflect my personal best practice.
Sam
@frede you can probably connect a date table to the agreements and also the individuals then use like Lookups for closed and open agreements…play around and see how you go with your data. Good Luck.
Again, thanks all for your help and tips.