Forum Discussion

edtguy's avatar
edtguy
Cloud Apps
07-13-2023

Associate multiple date columns in a fact table to a date dimension table

For the problem of needing to associate multiple date columns in a fact table to a date dimension table, I have always taken the approach of aliasing the date column in the dimension table with a new column having a descriptive column name for associating to a specific date column in the fact table. For example, fact_sale has quote_date, order_date, ship_date and invoice_date. I alias date in dim_date four times as quote_date_for_sale, order_date_for_sale, ship_date_for_sale and invoice_date_for_sale. I associate each column to the respective column in fact_sale. I seemed to recall a while back a Sisense engineer telling me this is a wrong approch, so I searched for and found this post, https://community.sisense.com/t5/knowledge/multiple-dates-in-a-fact-table/ta-p/8909
and I tried option 1 but it did not work, so I reverted to my approach and it seems to be working fine.
Can someone please provide a detailed rationale for why I should not use my approach? Thanks.

Data Models

4 Replies

Replies have been turned off for this discussion
  • Benji_PaldiTeam's avatar
    Benji_PaldiTeam
    Data Pipeline
    07-16-2023

    Hello edtguy ,

    It appears that the filtered measure plugin might be suitable for your situation.

    In the "Technical Details" tab, you will find a use case where you have a widget with multiple date fields, and changing one date field doesn't affect the other date field.

    Additionally, you can explore Paldi's Date Range Filter plugin, which offers support for such use cases as one of its features.

    Feel free to reach out if you have further questions, we're always happy to help (: 
    [email protected]
    Paldi Solutions - Number #1 Sisense Plugins Developer 
    • edtguy's avatar
      edtguy
      Cloud Apps
      07-16-2023

      Hello Benji_PaldiTeam ,

      Thank you for your response, but my issue is with model design, prior to designing dashboards.
      My question is how best to associate multiple date columns in a fact table to a date dimension. My approach has been to create an alias column in the dimension table, referencing the value in the original date column, for each date column in the fact table, then associating each to the respective date column in the fact table. If this is not a valid approach, I would like to know why.

      Regards,
      Ed

  • Miguel_Blanton's avatar
    Miguel_Blanton
    Data Storage
    07-17-2023

    Using aliases for date columns in a dimension table to associate them with specific date columns in a fact table is not the recommended approach. It can lead to confusion and make the data model less intuitive and maintainable. It's advisable to establish proper relationships between the fact table and the date dimension table using appropriate keys or foreign key relationships for each date column. This ensures data integrity and allows for easier analysis and reporting.

    • edtguy's avatar
      edtguy
      Cloud Apps
      07-18-2023

      Miguel_Blanton Thank you for your response, but since Sisense allows only a single association from a dimension table column to a fact table, I fail to see how "appropriate keys or foreign key relationships for each date column" is possible without providing an example. Otherwise, your statement, "It [aliased columns] can lead to confusion and make the data model less intuitive and maintainable." sounds like unsubstantiated opinion. 
      I'm looking for a demonstration of why my approach might fail to achieve the desired results, and if so, an example of an alternative approach. Inflating a fact table by the number of columns to be associated with a column in a dimension table, or creating multiple copies of a fact table, one for each type of the column to be associated with the column in the dimension table, seems highly inefficient to me and did not work when I tried it.
      The only other option I can think of is to treat each date type in a fact table as requiring a separate dimension and duplicate the dimension table. In my original example, this would be DimQuoteDate, DimOrderDate, DimShipDate and DimInvoiceDate.