cancel
Showing results for 
Search instead for 
Did you mean: 
Community_Admin
Community Team Member
Community Team Member

Question

A relatively common database design strategy is to have a tag column with an arbitrary number of tags.

Here is an example in which we look at numbers between 1 and 10 and tag them as even, odd, perfect, or parts of the Fibonacci sequence:

 

Community_Admin_0-1634389875348.png

Now we want to be able to select rows based on the tags.

Answer

 

Option 1 is to create filters using contains. But this will quickly become unworkable for large numbers of tags as new filters would have to be created for every tag.

Option 2 is a more flexible solution, doing this work on the ElastiCube side.

Step 1 (The Combo Tags) table :

Create a table of unique tag combinations in the fact table. For example:

SELECT DISTINCT tags

FROM [Tags.csv]

Step 2 (The Tag Mapper):

Here we need to create a table where every individual tag links to every tag combination it is in. In SQL this is more straightforward so if you can do this in your database look up STRING SPLIT and UNROLL functions. In Sisense we have to be more round about. For Sisense we will need to check the first tag in every tag combo, then the second, then the third, etc. etc. for whatever the maximum number of tags in a record is. The provided example supports max three tags per row:

SELECT DISTINCT combo_tag, tag FROM (

SELECT tags AS combo_tag, tags AS tag

FROM [Combo_Tags]

WHERE STRPARTS(tags, ',', 1) IS NULL OR STRPARTS(tags, ',', 1) = ''

UNION ALL

SELECT tags AS combo_tag, STRPARTS(tags, ',', 1) AS tag

FROM [Combo_Tags]

WHERE STRPARTS(tags, ',', 1) IS NOT NULL AND STRPARTS(tags, ',', 1) != ''

UNION ALL

SELECT tags AS combo_tag, STRPARTS(tags, ',', 2) AS tag

FROM [Combo_Tags]

WHERE STRPARTS(tags, ',', 2) IS NOT NULL AND STRPARTS(tags, ',', 2) != ''

UNION ALL

SELECT tags AS combo_tag, STRPARTS(tags, ',', 3) AS tag

FROM [Combo_Tags]

WHERE STRPARTS(tags, ',', 3) IS NOT NULL AND STRPARTS(tags, ',', 3) != ''

) AS tmp

When done, the table will look like this, with each combo tag linking individually to each component. So the combo_tag Even,Prime,Fib appears three times, once with Even, once with Prime, and once with Fib.

Community_Admin_1-1634389886856.png

Note: These results were ordered for display reasons, do not use an order by in production environments as it unnecessarily slows down the cube. You can order your table while testing to ensure correctness, but remove when done.

Step 3 (Dim Tag):

Now we just need to make a unique collection of individual tags to create our dim.

SELECT DISTINCT tag

FROM [Tag_Mapper]

Step 4 (Make the joins)

Join DIM_Tag.tag to Tag_Mapper.tag

Join Tag_Mapper.combo_tags to Combo_Tags.tags

Join Combo_Tags.tags to FactTable.tags

Let's review what happens in this example when the tags "Odd" and "Perfect" are selected.

Dim_Tag joins to Tag Mapper and grabs the following combo_tags

- Odd

- Odd, Fib

- Odd, Prime, Fib

- Even, Perfect

- Odd, Prime

When that table is joined to combo tags and then to our fact table, we see that the following ID's are revealed

1,3,5,6,7,9

The Combo_tags table is needed between the Tag_Mappper and the fact table to prevent Many to Many joins.

This approach will run into space issues as the tag lists become larger. The best recommendation I can give here is to ensure that the tags are always in the same order (alphabetical etc.) just so that we don't have Tag1,Tag2 as well as Tag2,Tag1. This will help constrain the data size.

My ElastiCube:

Community_Admin_2-1634389886818.png

And a dashboard:

Community_Admin_3-1634389886833.png

 

Version history
Last update:
‎10-16-2021 06:13 AM
Updated by:
Contributors
Community Toolbox

Recommended quick links to assist you in optimizing your community experience:

Need additional support?:

Community Support Request