edtguy
Cloud Apps
Joined 3 years ago
User Profile
User Widgets
Contributions
Is it possible to have an either/or Dashboard filter?
In a Sisense dashboard, is it possible to have an "either/or" filter, based on two column values? The model has two fact tables: Charge and Transaction. Charge can have zero to many associated Transaction rows, with Transaction associated to Charge by chargeID. Charge rows have a date of service and a charge amount. Transaction rows have a posting date and a transaction amount. In Transaction, besides loading actual Transaction rows, I load one "dummy" row for each Charge, with a dummy flag set and a Transaction amount of zero and posting date to date of service. In the dashboard, This way, I can always show correct charge info, without duplication or omission, by setting a filter on the Transaction dummy column to true, as long as a filter on posting date doesn't exclude any actual date of service values. I want to show charge information filtered by the month of the date of service, but I also want to show associated transaction data, filtered on the month of the posting date. If I set the posting date filter to a range that is not inclusive of the date of service dates, I lose some Charge information. If I could set Transactions to be within a range of posting dates OR dummy = true, that would yield correct results for both charges and associated transactions. Is this possible?Solved1.4KViews0likes1CommentImplement column level security out of the box
There are many scenarios where a model contains data for many distinct groups, such as customers, and you want viewers to only have access to the data that pertains to their specific customer. This is easily implemented with row level security, when there is a data column that uniquely identifies each customer. But what if you have a requirement that certain types of viewers should see all columns of data per row, while others should not have access to certain columns? Here is an approach that can be implemented right out of the box in Sisense. The one prerequisite is that any table that contains sensitive data must have a column that uniquely identifies every row, i.e. a primary key column. For every table that contains sensitive data in certain columns, create a new table that contains the original table's primary key column, a binary "switch" column with a name like [Access Allowed] and a column for each of the sensitive columns. Remove the sensitive columns from the original table. Modify the import query to load only the non-sensitive columns in the original table. Modify the import query to load the new table with the primary key and sensitive columns of the original table twice, once with [Access Allowed] set to 1 and the second time with 0 and blank (or null) for all the sensitive columns. Associate the new table to the original table by primary key column Create a dimension table for secure access with the column [Access Allowed]. One row with value 0, one row with value 1. Associate the dimension table with the sensitive data table by the [Access Allowed] column. Set up a data security rule on this column, assigning the secure user group to value 1 and the general group to value 0 If there is a hierarchy greater than two levels of column-level data security, this model can be extended to create additional one-to-one related tables to the original table.596Views3likes0CommentsRe: Associate multiple date columns in a fact table to a date dimension table
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.3.1KViews1like0CommentsRe: Associate multiple date columns in a fact table to a date dimension table
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, Ed3.1KViews0likes0CommentsAssociate 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.3.2KViews0likes4Comments