Power BI and Gift Table Column Expansions

Options
Mark Zigadlo
Mark Zigadlo
Name Dropper Photogenic First Anniversary First Comment
in SKY Developer General Discussions

I have been experimenting with expanding all possible Gift table columns to see what other data is available to me and so I can dump my overly complicated gift_splits table (my own fault).

So far so good, with the biggest discovery is that gift_splits.id is not unique so I had to update a few revenue measures to make sure I did not sum up duplicate amounts.

Has anyone else looked into this and run across any other caveats?

Tagged:

Comments

  • Alex Wong
    Alex Wong Community All-Star
    Ninth Anniversary 1,500 Likes 2500 Comments Name Dropper

    @Mark Zigadlo
    not sure what you mean by “gift_splits.id is not unique". It is unique to the gift split. So the issue likely has to do with what other one-to-many relationship you may have “expanded”.

    a single gift with 5 gift splits will have 5 rows and each with its own unique gift splits id, however, if you also expanded fundraisers (for example, as it is another one-to-many relationship), and the single gift has 2 fundraisers, then you are going to get 10 rows of data for the combination of 5 splits times 2 fundraisers, there by making gifts split id showing twice (once for each fundraiser).

  • Mark Zigadlo
    Mark Zigadlo
    Name Dropper Photogenic First Anniversary First Comment

    @Alex Wong

    Yes, it is because I expanded fundraisers so I now have rows with the same split id

  • Alex Wong
    Alex Wong Community All-Star
    Ninth Anniversary 1,500 Likes 2500 Comments Name Dropper

    @Mark Zigadlo
    expanding more than one-to-many relationship in a single data table is not a good idea, at most you should only expand one of such relationship, and keeping in mind that expanding that will cause your data table to duplicate columns that would (1) waste space in the power bi storage (local and cloud) and (2) potential double counting if not careful, such as the amount column that represent amount on the gift level

    it is much better to have separate data table, where one is the main gift table that has all the one-to-one field (i.e. gift date, gift amount, gift id, gift type/subtype, etc) and then one table for each one-to-many relationship keeping the gift id for relationship linking (i.e. gift split table - gift id, campaign id, fund id, appeal id, package id, split amount) and (i.e. gift fundraiser - gift id, constituent id, amount credit to fundraiser) and (i.e. soft credit - gift id, constituent id, amount soft credited)

Categories