Forum Discussion

danielrabinovit's avatar
danielrabinovit
Cloud Apps
04-30-2025
Solved

Dimension Table

Hi Everyone, 

I am very used to using PowerBI, where one way to create a dimension table, you would duplicate the table you were looking for, choose the column you wanted and then removed duplicates. This was all done in power query.

I am now wondering if there is an easy way to do this in Sisense? obviously I could just input the data manually, but that seems inefficient and not dynamic.

  • Hey danielrabinovit ,

    It depends on where you are within Sisense. If you are building a dashboard/widget, using the pivot-table and selecting fields that are duplicates of eachother, then you can use the pivot-table widgettype to accomplish this. It is not the most beautifull option , but it is an option.

    Best way for this to do would be in the elasticube. You can create a custom-table and write SQL to create a dimension-table with unique records. You could do something like;

    select distinct * from table_A

    This works under the assumption that every field has the same values for duplicate records. If this is not the case , for example 1 column makes the rows unique, then you can select only the columns that are unique. Something like;

    select distinct column_a, column_b, column_e from table_a

    Besides distinct you can also use group by column_a, column_b, column_e to get the same result. 

    Hamza

2 Replies

  • HamzaJ's avatar
    HamzaJ
    Data Integration

    Hey danielrabinovit ,

    It depends on where you are within Sisense. If you are building a dashboard/widget, using the pivot-table and selecting fields that are duplicates of eachother, then you can use the pivot-table widgettype to accomplish this. It is not the most beautifull option , but it is an option.

    Best way for this to do would be in the elasticube. You can create a custom-table and write SQL to create a dimension-table with unique records. You could do something like;

    select distinct * from table_A

    This works under the assumption that every field has the same values for duplicate records. If this is not the case , for example 1 column makes the rows unique, then you can select only the columns that are unique. Something like;

    select distinct column_a, column_b, column_e from table_a

    Besides distinct you can also use group by column_a, column_b, column_e to get the same result. 

    Hamza

  • Thanks HamzaJ danielrabinovit 

       I would add that if you're looking to create a dim table for the purpose of filtering or creating a widget from a certain dimension (like a row/column in a pivot or a X axis etc) and you would like to filter or include values form multiuple fact tables then you would nmeed top create this dim table from the dimensions in the multiple fact tables and then connect them accordingly.

    In that case, assuming fact_1 and fact_2, the query would look like this:

    SELECT dim

    FROM fact_1

    UNION

    SELECT dim

    FROM fact_2

    Check out these articles for more:

    https://docs.sisense.com/main/SisenseLinux/data-model-building-practices.htm?TocPath=Modeling%20Data%7C_____4

    https://community.sisense.com/t5/data-models/design-a-schema-with-more-than-one-fact-multi-fact-schema/ta-p/8908

    If you have any other questions don't hesitate to reach out,

    We're always here to help 

     

     

    Ido from QBeeQ

    QBeeQ - Gold Sisense Partner

    [email protected]

    Feel free to subscribe to our newsletter to stay up to date with the latest QBeeQ news!