Advice on data modeling

Hi!

I’d appreciate some advice regarding my data modeling.
I have 2 issues here I guess and I want to resolve these before I start to build reports.
Consider the data model in the attached image. This is only a part but it serves to illustrate my problems. The data is for a charity org.
Individual are godchildren, Customers are people who donate money to projects or/and godchildren. Projects are the different projects and Area are the different organizations that collect the money.
Customer can have agreements (Agreements table) to a project and/or a child in which case the donate a set amount each month. They can also give a one-time gift (Gift table).

  1. I have relationships between my dimension tables. e.g. An individual is always linked to a project (Individual.ProjectId -> Project.Id). I cannot create an active relationship between Individual and Project as there is already one via the Gift table. Let’s say I want to create a report where I have the Project name in a slicer and the individuals in a table so I can select the project and see what individuals that are linked to it. How can I make that work if I don’t have an active relationship between project and Individual? I could merge the Project name into the Inidividuals table or I could turn on bi-directional relationship between Individual-Gift-Project tables but none of those alternatives seem right. I want to keep the project names in one dimension table and if I go down the road of bi-direnctional relationship I would have to use that between basically all dimension and fact tables.

  2. I have relationships between my fact tables. The Agreement and Gift tables are related through Gift.AgreementId - Agreement.Id. A gift can either be a one-time gift to an individual or a project, in which case the AgreementId column is empty in the Gift table. Or, the gift can be a part of an Agreement where the customer donates a specified amount (Agreement.Amount) each month. In this case the Gift table contains the AgreementId for that transaction. Now let’s say I want to analyze the gifts collected as part of an agreement per area (an area is the organisation that collects the money). I would then need to go from Area to Agreement and then on to Gift to get the amount. A one-time gift does not have an area set (no areaid in Gift table). How should/could I model the information to support this? I have been thinking of merging the Gift and Agreement table but that does not seem “clean” as values in many columns in the resulting table would be valid only if agreementid is populated. The other option is to create some kind of link table consisting of Ids from all dimension and fact tables, but I haven’t tried that yet.

Appreciate any tips on how to model this effectively.
Thanks Fredrik

1 Like

You probably do not want to do that. Bi-directional relationships can have some weird side effects if you are not careful

Any chance you could upload a sample pbix file along with what you would like to see

1 Like

Hi!

Sorry, I did not notice your reply earlier. I guess I could, but the model contain client data that I would have to fake in that case. Can I clarify my question in any way to make it easier to understand what I want to accomplish?

Thanks
Fredrik

A very simple example would be that i want to analyze how much a specific customer has donated per agreement. That could mean 1 row in the customer table (ONE customer), 2 rows in the agreement table (ONE customer has TWO agreements) and 20 rows in the gift table (10 donations per agreement). This is not possible with the current model as there is no relationship between Agreement and Gift (there could be an inactive relationship based on agreementid, but not an active). As I find this (simple) analysis to be quite complex, does that indicate that my model is poorly designed and if so, do you have any idea how to improve it? As far as I can tell my dimensions are good, that is what I want to slice and dice. I cannot see an obvious way to change my fact tables either. Any ideas?

Hi @Frede your data model is not built correctly hence you cant get the answers you require. What you need to do is review Sam’s data modelling videos as I can see you haven’t setup the key parts to report on the data you need.

You normally only want one dimension to represent a contact who could be an individual or entity. The Area table is normally a sub set of the Customer/Contact database like an address table.

The project table looks fine as it is.

The Agreement table is the master control table which contains your fact data.

The gift table is a sub set of the Master Agreement table and could reference multiple agreements so it has a one to many relationship with its master.

Here is a typical model I setup for customers reporting using a MYOB accounting system. This model reports on both customer products, Service based sales (ie time related) which are reported via a Sales Control table in any accounting system.

The date and customer data pass through to the lowest level sub ledgers via the Sales control table.

The sales control table has the unique transaction ID (UID) which is referenced to both sub ledgers in this scenario the products and services fact tables.

Hope that helps you out!

1 Like

Highly recommend working through this particular course here below. If you implement the best practices here you should have no problem with your data model.

Sam

Thanks @GarryA and @sam.mckay.

I have watched the modeling videos before, but have watched them again and yet fail to come up with how I could rearrange my model. I may have explained my issue poorly but here goes again.

@GarryA About area being a subset of the customer table. The area is the org. that collects the donations. One customer can donate through several orgs. so there is a 1-many relationship between customer and area. In the database this is done through the CustomerOrganisation table that I have now added to my model. I have merged the areaid to the agreement table as an agreement is signed with one org. So a customer can donate through several orgs. and can have several agreements. The area/org is the dimension that you will want to filter on most of the times so see e.g. donations via a specific area, or number of customers per area etc.
Gift is not a subset of agreement as many donations are made without an agreement at all. e.g. no link between gift and agreement. The column AgreementId in the gift table is what relates the agreement with the gift and it is blank when it is a one-time gift that is not donated as part of an agreement. Also, there may very well be a number of agreements for which there are no donations (gift) registered (yet).

I have cleaned up my model and removed some data to get the size down and attached it. How can I create an analysis like donated amount per agreement which would get the agreement details from agreement and the donated amount from the gift table.

Link to PBIX file

Thanks again
Fredrik

Hi @Frede
I would separate out the two fact tables (Gifts and Agreements).

It does look like the GIFT table has the unique ID so this would be the control account as you have mentioned.

First remove all the relationships and just keep the gift relationships active.

Then link the ID in the Agreement to the ID in the GIFT. This will create the many (Agreement) to one (Gift) in the relationship.

Then start redoing your formulas and see how you go.

Hi @GarryA

I fail to see how this could solve my issue. The Gift.AgreementId is related to Agreement.Id column (all primary keys in tables are named Id) but this not a one to many relationship. There are many agreements that do not have a gift so arranging in this way would not allow to me e.g. count number of agreements for a specific area.
Thanks Fredrik

@Frede, my suggestion is to work through each set of fact tables, I’m sorry you aren’t able to see how to work through the issue.

When I get stuck I always break down the problem into parts. Maybe someone else can give you some better advice…maybe you need to engage a specialist in data modelling to assist you with the issue?

@Frede Let me try this to expand on what @GarryA said.

Try removing everything from the Data Model. Then add only one Fact table and One Dimension table. Create a relationship between them and see if you can produce any usable results, if so good. Then add in another Dimension table.

If you keep getting usable results “Wash, Rinse, Repeat” adding a Fact table or a Dimension table as required. If something goes South, stop there and take a closer look at the table just added.

Take it slower and in smaller parts. Never jump in with too many tables at the beginning.

Hopes this helps.

Guy

@Frede @GuyJohnson @sam.mckay

I can see why this is a bit hard as the dimensions to me look too complex?

So when I look at the dimensions I think Individuals, Customers, Areas are all part of a contact’s database by referring to fields like names, address, phone etc… Would it be beneficial to consolidate these dimensions to make it easier to work the solution?

I would probably end up with a structure similar to below:

DIMENSIONS
Individuals(People/Contacts)
Customers(People/Businesses/Contacts)
Projects/Jobs (maybe organisation is
Area(Organisation/Contacts)
Agreements (Some Lookup Table data against Customers)

FACT Transactions (many dates) which I break into
Fact 1 - Donate Money (Control Ledger job costed to Project or Individuals )
Fact 2 - Collect Money (Control Ledger job costed to Project and Organisation or receive Gifts under agreement linked to Gift Ledger))
Fact 3 - Agreements Ledger (Control Ledger)
Fact 4 - Gift Ledger (Sub Ledger of Agreements and Collect Money related contact or project which may or maynot be under agreement)

I think the first level is you can either receive money via Donations or Collections from Agreements. Does that sound reasonable?

Agree with this one Guy.

If it’s too confusing with everything then break it down and go step by step.

Yes and agree with Garry here. Think conceptually around what would logically be lookup vs fact. Then you may need to re-organize your tables a little bit in the query editor to make it all work.

Thanks
Sam

Hi @GarryA

Thanks for your reply. I’m sorry but I don’t understand the model you are proposing. Could you draw an simple sketch to illustrate your thoughts? I understand that the columns etc are a bit confusing and some are obsolete so I have created a new schema below, but it has the same problems as I have stated before. i.e. I need relationships between dimensions and also between the fact tables.

About the modelling issue, is the “Agreement” table actually a fact table? Kind of looks like a slowly changing dimension…

Anyway just wanted to add a simple quick tip - don’t forget to hide all helper columns for example [Projectid] you have that in 4 tables: Individual, Project, Agreement and Gift as far as I could see.
Keep only one instance visible (most likely in the Project table) to avoid selection and calculation mistakes in your report.

@Melissa I agree and have thought the same about the Agreement table. It does not seem like a pure fact table, but it is not really a dimension either. I want to for example show the number of agreements per project per country and this requires relationship between agreement/project and agreement/project-country.
I have the ID columns in the image just for reference so that you can see the different key columns in each table. No Id column would be visible to the end users

Thanks
Fredrik

@Frede @Melissa both tables are relating to facts.

When a gift was made and when an agreement took place between entities.

The issue is the facts are competing with the dimensions you want to report on…I’m just trying to find Sam’s video on this explanation about this scenario… @sam.mckay might be able to point out this…

Would this indicate all amounts are in the GIFT table for all transactions you want to capture?

So all GIFT[ID]'s are unique and appear many times as the [ID] in the Agreement table?

Please also see this link regarding relationships for PowerBI

@GarryA

No, as I tried to explain above. Gifts can be either
A) One time gift. In this case, no agreement exist for the gift, i.e. AgreementId is null in the GIFT table and there is no corresponding record in the Agreement table
B) Part of an agreement, in which case the AgreementId is stored in the Gift table and there is a corresponding record in the Agreement table. For a monthly agreement this would for the first year it would be one record in the Agreement table (the agreement itself) and 12 records in the gift table with that AgreementId, one per month.

An agreement can exist before there is any donation given as part of that agreement (until the first payment period has ended).
Both one-time and agreements gifts can be donated to the same project
Gifts can be donated directly to Individuals or only to projects. In both cases the projectid will be populated as an individual is always attached to a project. In this case the combination individualid and projectid will be the same in gift as in the individual. The same goes for Agreement that can be for an individual child, or for a project.

I have thought about splitting the gift table into One-time gifts and agreement gifts but that seems backwards as I for example want to analyze the total amount donated per project (one-time and agreement gifts) and I already have the agreementid in the Gift table that allows me to separate one-time gifts from agreement gifts as all one-time gifts lack an agreement id.

Thanks
Fredrik

@Frede okay but i just noticed that ALL GIFTS have an $ amount in the gift table whether an agreement exists or not?

So I thought this was the main transaction table. If you want two fact tables then you have to adopt the process of a central fact table and two sub-ledgers as I have showed in my example. Think of it as master money received ledger and then one sub-ledger with gifts and one sub-ledger with agreements transactions.

It’s a little bit hard to give advice with this kind of setup as it can be complex if you want to combine multi fact tables. What SQL systems are you using for CRM and transactions?