Dimension Table

[danielrabinovit]

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.

[HamzaJ · Accepted solution]

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

[Ido_QBeeQ]

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...

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

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!