1. TARGIT Community
  2. Community
  3. Data Discovery
Knowledge Base

ID.cnt.dst returns more than 1 for each ID

Niels Kristian Dyrelund ·

My goal is to cross check two sources to see if they contain the same, in this case simply the same IDs.

For this purpose, I have created a data model with two fact tables (one for each source, with one or more rows for each ID) and a dimension table (a list of all IDs, without duplicates). The relations have been drawn from the fact tables to the dimension table, on the ID as the key, of the same datatype.

In my analysis, I select the ID as a dimension from the dimension table; and I select the measure ID.cnt.dst from each fact table.

Now, I would expect the analysis to return 1 or 0 depending on whether the ID exists in the respective fact table. However, the analysis returns more than 1 for each ID.

How can ID.cnt.dst return more than 1 for each ID? And how to fix it?

1

Comments

3 comments
Sort by DateVotes
  • Ole Dyring

    Hi Niels Kristian,

    Hmmm... I would try something like this:

    • Expand your data model with an extra fact table - which happens to be a copy of your dimension table (just make a new data source to the origin of your dimension table). 
    • Relate it to your dimension table the same way as you did with the other two fact tables.
    • When you next create a crosstab in the TARGIT client, use the Source*.cnt.dst measure (find it in a folder with the same name as the cube) together ID from your dimension table on the vertical axis and the Source* dimension on the horizontal axis.

    This should give you a nice picture of all the available dimensions - and whether they are part of one fact table or the other.

    I have used "Number format" to show blanks as "0" in screenshot.

    BR / Ole

    0
  • Niels Kristian Dyrelund

    Thank you, it did the trick!

    What if I would want to cross check more than one dimension, e.g. "person ID" as well as "task ID"?

    And may I ask, what it is logic behind using Source*?

     

    0
  • Ole Dyring

    To check "task ID" just follow the same principles:

    1. Create "task ID" dimension table
    2. Create the same table as a fact table also
    3. Relate this new fact table - together with the original two fact tables - to the "task ID" dimension table

    Source* in itself is a auto-generated dimension with names of all sources (tables) that it used to design your cube. The Source*.cnt is just a handy way of counting " how many times does the source contain/relate to the chosen dimension member". In my example, I could have used No.3.cnt.dst to achieve the same result by counting "how many times does a dimension member appear for each source".

    BR / Ole

    0
Please sign in to leave a comment.