how to Connect all fields in the data model

Comments

5 comments

  • Avatar
    Malinda Jepsen

    This article might help:

    https://support.sisense.com/hc/en-us/articles/230644408-Key-Table-Store-data-relationships-between-primary-key-values

    That approach did not work for us, so I added a “N/A” entry for the other dimensions and then connected them to the dimension. You need to add a new row to your dimensions (we use -1 for the key) and then add 6 columns in your fact to join to each of the dimensions (their values are -1 each).

    0
    Comment actions Permalink
  • Avatar
    Siva

    Thanks you very much for quick suggestion.

    I am using this type of data model. So, you are suggesting to add 6 other dimensions in Fact2 as well with values as "N/A"? Can you please explain -1 part again?

    0
    Comment actions Permalink
  • Avatar
    Malinda Jepsen

    In each of your dimension tables, add a row that means "N/A" (not applicable). In our case, our keys are INTs, so we always use -1. If your keys in your dimensions are strings, you can use "N/A" or anything else you want.

    In your fact, add 6 custom columns, each with the value of "N/A" (either -1 if it's an INT or "N/A" if it's a string). Then connect each of those new "NA keys" to the dimensions. We named ours NARepKey or NAProductKey or NACustomerKey.... whatever dimensions you are missing so it was clear what they were for.

    One thing to be aware, if you are using filters, make sure your filters are pulling the correct data (not just "N/A"). If that is an issue, there's another layer you need to add.  Hopefully your model isn't that complex, so you don't have to do that. We had to add the next layer because we are using datasecurity on one of our tables.

    1
    Comment actions Permalink
  • Avatar
    Siva

    I have made all the required changes. I am facing the issue what you mentioned in the last point. I am able to filter only by "N/A", i am not able to filter with other values of the dimension. 

    0
    Comment actions Permalink
  • Avatar
    Malinda Jepsen

    Hopefully this image will show you what you need to do.  In general, you need to create an intermediary table that controls the route that the query processor takes (make it a longer path for tables that don't have all the data). In this case, I'm showing it with "Representative". The "Index History" table does not have any representatives (hence the NARepKey), but the "Household Performance" does. Here's the code for the intermediary custom table "NARepKey: "

    SELECT RepresentativeKey AS NARepresentativeKey1,
    RepresentativeKey AS NARepresentativeKey2
    FROM [Representative]

    (where RepresentativeKey is the key in the Representative dimension.  You then link up the Representative to the first key and the second key to Index History.  The link to Household Performance is direct because that table does have all the RepresentativeKeys.  If you need more assistance than this,  i would recommend opening up a support case to have them help with your specific data model.  They're great at it!

    1
    Comment actions Permalink

Please sign in to leave a comment.